ODBC ERROR

We have a DTS package to upload data from SQL to PARADOX. We are using the
Microsoft Paradox- treiber driver. Data is uploaded from 2 sql tables to 2
paradox tables. Upload for one table works, but for the other one, we get
the following error message.
Microsoft [ODBC Paradox Driver]
Could not decrypt file.
Help!Hello,
As you mentioned in other post, you may want to enable odbc tracing
tempoarily to see if you could find any clue.
Control Panel->Administrative tools->Tracing
Based on my experience, this kind of issue is usually caused by some
special data type that cannot be converted properly by ODBC driver. You may
also want to check suspect datatype and try to use a different one to work
around the problem.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
| From: "docsql" <docsql@.noemail.nospam>
| Subject: ODBC ERROR
| Date: Wed, 10 Aug 2005 09:25:33 -0700
| Lines: 11
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
| X-RFC2646: Format=Flowed; Original
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
| Message-ID: <#AovigcnFHA.3316@.tk2msftngp13.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.odbc
| NNTP-Posting-Host: 206.170.189.88
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp13.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.odbc:2576
| X-Tomcat-NG: microsoft.public.sqlserver.odbc
|
| We have a DTS package to upload data from SQL to PARADOX. We are using
the
| Microsoft Paradox- treiber driver. Data is uploaded from 2 sql tables to
2
| paradox tables. Upload for one table works, but for the other one, we
get
| the following error message.
|
| Microsoft [ODBC Paradox Driver]
| Could not decrypt file.
|
| Help!
|
|
||||Following is the exact error message.
--
Package Error
--
Error Source : Microsoft OLE DB Provider for ODBC Drivers
Error Description : [Microsoft][ODBC Paradox Driver] Could not decry
pt file.
OK
--
I tried turning on the ODBC tracing, but the log file is empty.
Help!
"Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message news:NW0TJ%23hnFHA.36
72@.TK2MSFTNGXA01.phx.gbl...
> Hello,
>
> As you mentioned in other post, you may want to enable odbc tracing
> tempoarily to see if you could find any clue.
>
> Control Panel->Administrative tools->Tracing
>
> Based on my experience, this kind of issue is usually caused by some
> special data type that cannot be converted properly by ODBC driver. You ma
y
> also want to check suspect datatype and try to use a different one to work
> around the problem.
>
> Best Regards,
>
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
>
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
>
> ========================================
=============
>
>
>
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>
> --
> | From: "docsql" <docsql@.noemail.nospam>
> | Subject: ODBC ERROR
> | Date: Wed, 10 Aug 2005 09:25:33 -0700
> | Lines: 11
> | X-Priority: 3
> | X-MSMail-Priority: Normal
> | X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
> | X-RFC2646: Format=Flowed; Original
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
> | Message-ID: <#AovigcnFHA.3316@.tk2msftngp13.phx.gbl>
> | Newsgroups: microsoft.public.sqlserver.odbc
> | NNTP-Posting-Host: 206.170.189.88
> | Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp13.phx.gbl
> | Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.odbc:2576
> | X-Tomcat-NG: microsoft.public.sqlserver.odbc
> |
> | We have a DTS package to upload data from SQL to PARADOX. We are using
> the
> | Microsoft Paradox- treiber driver. Data is uploaded from 2 sql tables t
o
> 2
> | paradox tables. Upload for one table works, but for the other one, we
> get
> | the following error message.
> |
> | Microsoft [ODBC Paradox Driver]
> | Could not decrypt file.
> |
> | Help!
> |
> |
> |
>|||Hello,
Since the issue only occurs on a specific table, live debug/user dump
analysis might be the efficient method to find clues. I recommend that you
open a Support incident with Microsoft Product Support Services so that a
dedicated Support Professional can assist with this case. If you need any
help in this regard, please let me know.
For a complete list of Microsoft Product Support Services phone numbers,
please go to the following address on the World Wide Web:
http://support.microsoft.com/directory/overview.asp
If MS confirmed this is issue inside the product, they may consider a fix
or workaround for you. Also, support incident will be refunded.
Have a great day!
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
| From: "docsql" <docsql@.noemail.nospam>
| References: <#AovigcnFHA.3316@.tk2msftngp13.phx.gbl>
<NW0TJ#hnFHA.3672@.TK2MSFTNGXA01.phx.gbl>
| Subject: Re: ODBC ERROR
| Date: Tue, 16 Aug 2005 11:20:30 -0700
| Lines: 208
| MIME-Version: 1.0
| Content-Type: multipart/alternative;
| boundary="--=_NextPart_000_0008_01C5A254.86DC1510"
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
| Message-ID: <ed9c08ooFHA.3996@.TK2MSFTNGP12.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.odbc
| NNTP-Posting-Host: 206.170.189.88
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP12.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.odbc:2593
| X-Tomcat-NG: microsoft.public.sqlserver.odbc
|
| Following is the exact error message.
| --
| Package Error
| --
| Error Source : Microsoft OLE DB Provider for ODBC Drivers
| Error Description : [Microsoft][ODBC Paradox Driver] Could not dec
rypt
file.
| --
| OK
| --
| I tried turning on the ODBC tracing, but the log file is empty.
| Help!
| "Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
news:NW0TJ%23hnFHA.3672@.TK2MSFTNGXA01.phx.gbl...
| > Hello,
| >
| > As you mentioned in other post, you may want to enable odbc tracing
| > tempoarily to see if you could find any clue.
| >
| > Control Panel->Administrative tools->Tracing
| >
| > Based on my experience, this kind of issue is usually caused by some
| > special data type that cannot be converted properly by ODBC driver. You
may
| > also want to check suspect datatype and try to use a different one to
work
| > around the problem.
| >
| > Best Regards,
| >
| > Peter Yang
| > MCSE2000/2003, MCSA, MCDBA
| > Microsoft Online Partner Support
| >
| > When responding to posts, please "Reply to Group" via your newsreader
so
| > that others may learn and benefit from your issue.
| >
| > ========================================
=============
| >
| >
| >
| > This posting is provided "AS IS" with no warranties, and confers no
rights.
| >
| > --
| > | From: "docsql" <docsql@.noemail.nospam>
| > | Subject: ODBC ERROR
| > | Date: Wed, 10 Aug 2005 09:25:33 -0700
| > | Lines: 11
| > | X-Priority: 3
| > | X-MSMail-Priority: Normal
| > | X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
| > | X-RFC2646: Format=Flowed; Original
| > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
| > | Message-ID: <#AovigcnFHA.3316@.tk2msftngp13.phx.gbl>
| > | Newsgroups: microsoft.public.sqlserver.odbc
| > | NNTP-Posting-Host: 206.170.189.88
| > | Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!tk2msftngp13.phx.gbl
| > | Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.odbc:2576
| > | X-Tomcat-NG: microsoft.public.sqlserver.odbc
| > |
| > | We have a DTS package to upload data from SQL to PARADOX. We are
using
| > the
| > | Microsoft Paradox- treiber driver. Data is uploaded from 2 sql
tables to
| > 2
| > | paradox tables. Upload for one table works, but for the other one,
we
| > get
| > | the following error message.
| > |
| > | Microsoft [ODBC Paradox Driver]
| > | Could not decrypt file.
| > |
| > | Help!
| > |
| > |
| > |
| >
|

ODBC Data Source

I'm trying to import tables from COBOL thru Relativity client. With SQL Server 2000, I used Other (ODBC Data Source) to do this. I cannot find a comparible data source in SQL Server 2005. What do I do?

Where are you looking to find this data source?

Mike

|||In the Import wizard.|||

Thanks,

The Import wizard is not supported in SQL Express so this isn't the best place to get information about it. I'm going to move this thread into a forum where the folks who should be able to answer this question hang out.

Mike

|||Well, great! We use SQL Express. Won't my boss love this! Thanks for the info.|||

There's some very good reasons why SQLExpress is free. You don't get much with it

-Jamie

ODBC connection prompting for 'sa' password

We use Pivot Tables in Excel to provide users with reports. The Pivot Tables connect to SQL Server via an ODBC connection. In the ODBC connection, SQL Server authentication is specified and the 'sa' user name and password are entered. In prior versions
of Excel, the password would be saved in the Pivot Table. However, in Excel 2003 the user is prompted to enter the 'sa' password in order for the pivot table to update. Is there a way to have Excel 2003 pivot tables function as they did in prior versio
ns so that the password in the ODBC connection does not have to be re-entered?
Don't know much about Excel. But can you create another account (not sa)
to access the DB? This way you can keep your sa password secret.
Eric
Mike wrote:

> We use Pivot Tables in Excel to provide users with reports. The Pivot Tables connect to SQL Server via an ODBC connection. In the ODBC connection, SQL Server authentication is specified and the 'sa' user name and password are entered. In prior versio
ns of Excel, the password would be saved in the Pivot Table. However, in Excel 2003 the user is prompted to enter the 'sa' password in order for the pivot table to update. Is there a way to have Excel 2003 pivot tables function as they did in prior vers
ions so that the password in the ODBC connection does not have to be re-entered?
Eric Li
SQL DBA
MCDBA

ODBC connection - lost tables

I have a SQL Server database that I connect a front end to using an ODBC connection. Our LAN folks upgraded the server recently and now I can no longer see any of the tables through the ODBC connection that the user used for login has permission in SQL Server Enterprise Manager to see - throught the ODBC connection the user can only see things like:

dbo.spt_datatype_info
dbo.spt_datatype_info_ext
dbo.spt_fallback_db
dbo.spt_fallback_dev
dbo.spt_fallback_usg
.
.
.
INFORMATION_SCHEMA.CHECK_CONSTRAINTS
INFORMATION_SCHEMA.COLUMN_DOMAIN_USAGE
etc.

I've tried deleting the user for the connection and re-establishing it with owner permissions. I've tried deleting the dsn and re-establishing that as well but nothing so far.

Please help!!!Sounds like your default database for the user is master...

is it?

Can you edit the connection and look?|||Do you mean throught the ODBC Administrator or through SQL Server Enterprise manager?|||If you're connecting a client to sql server...it'll be on the client...

And since you're seeing the INFORMATION_SCHEMA views...they live in master...

You'll need to change the default database to where your data is, I think (only sometimes, mind you)|||I'm not sure how to change the master on my client machine - could you walk me through that?

I sit next to some Oracle folks and from what they know of SQL Server there is a master dbase with sub dbases - the one I'm accessing being a sub dbase. The master dbase peers into the subs through views. It sounds to them as though the master dbase doesn't have the proper permission to see all the tables or perhaps the view is incorrect. Is this close to what you are talking about?|||You control the default database when you create the dsn - look at your dsn settings for default database (it defaults to master).|||Hold on...don't listen to the Oracle guys...different world

been there...

there are no "Sub" databases..there are instances (a word they should know) in sql2k...

Hold on...I'm on win2k ok?

go to start>Settings>control panel

2xclick on data sources

Find your dsn...2x click on or configure...

Go 2 or 3 clicks on NEXT...you should see the default db...

btw...how do you distribute the odbc to the clients?|||You are the man!!! I found the default database and that was it. Thank you very much!

For those of you reading and don't know where that is on Windows 2K and XP open your ODBC Administrator -> add a User DSN -> select a driver (in this case SQL) -> type in a name and a server, description is optional -> set up authentication as needed -> at the top of the next screen is where you change your default database -> leave default settings on the next screen -> test and you're done.|||Great...and good luck...

But how do you plan to distribute the connection?|||Actually what I have is a document in a shared folder that walks the user through the ODBC setup. All users login to the dbase with the same user info and their data access is controlled through the front end. Not the best solution but the data isn't sensative and the cost was zero since me and another guy set it up in-house.

Thanks again!sql

ODBC call failed - weird behavior

Hello,
I have been working on updating an Access 2000 application that uses linked
ODBC tables to connect to an SQL Server 2000. My development machine is
connected to the server over a Microsoft LAN, at 100Mbits.
Both the Office 2000 on my development machine and the SQL server has all
the latest SP's
Everything worked fine for months until today. I am not aware of any changes
to ether machine since yesterday.
Now, there is one table that can not be opened any more.
I can access all the other tables ecxept that one. When I try to open it I
get the message "ODBC call failed". The table is displayed but with #Name?
in all columns for all records.
Once that has happened I get the same message for all tables. In addition to
"ODBC call failed" there is also
"[Microsoft][ODBC SQL Server Driver][Communication link failure
(#0)".
When I close down and reopen the app I can again access all tables except
the bad one.
At first, I thought the table had gotten corrupted, but I can open the table
just fine on the server. If I set up an ODBC on the server with the same
name I and run my application there. the problem table opens fine.
I removed the ODBC and set it up again with the same properties, client uses
TCP/IP network library, and I am using SQL Server authentication.
I am using MDAC 2.6 SP2, and I did a refresh, but it did not solve problem.
I ran
I am wondering if I should update to 2.8
If anyone has a suggestion I would appreciate it very much.
RagnarIt could be an MDAC issue. You could run the MDAC component
checker on a machine where the app works and another machine
where the app gets the ODBC error and see if there are any
differences in MDAC versions. You can get component checker
from:
http://msdn.microsoft.com/data/down...ls/default.aspx
You could also turn on ODBC tracing then reproduce your
error and then turn tracing off again. You could open up the
ODBC trace log, look for specifics of what's failing and try
to track down the issue from there.
-Sue
On Wed, 10 Mar 2004 19:05:29 -0500, "Ragnar Midtskogen"
<ragnar_ng@.optonline.net> wrote:

>Hello,
>I have been working on updating an Access 2000 application that uses linked
>ODBC tables to connect to an SQL Server 2000. My development machine is
>connected to the server over a Microsoft LAN, at 100Mbits.
>Both the Office 2000 on my development machine and the SQL server has all
>the latest SP's
>Everything worked fine for months until today. I am not aware of any change
s
>to ether machine since yesterday.
>Now, there is one table that can not be opened any more.
>I can access all the other tables ecxept that one. When I try to open it I
>get the message "ODBC call failed". The table is displayed but with #Name?
>in all columns for all records.
>Once that has happened I get the same message for all tables. In addition t
o
>"ODBC call failed" there is also
>"[Microsoft][ODBC SQL Server Driver][Communication link failure
(#0)".
>When I close down and reopen the app I can again access all tables except
>the bad one.
>At first, I thought the table had gotten corrupted, but I can open the tabl
e
>just fine on the server. If I set up an ODBC on the server with the same
>name I and run my application there. the problem table opens fine.
>I removed the ODBC and set it up again with the same properties, client use
s
>TCP/IP network library, and I am using SQL Server authentication.
>I am using MDAC 2.6 SP2, and I did a refresh, but it did not solve problem.
>I ran
>I am wondering if I should update to 2.8
>If anyone has a suggestion I would appreciate it very much.
>Ragnar
>|||Thank you Sue,
I had run ComponentChecker on my development machine and it now has 2.6 SP2
Refresh wih a couple of files being slightly newer than expected, ODBC32.dll
and ODBCCP32.dll.
The Server had 2.7 SP1 Refresh with about 8 files slightly newer than
expected, including the ODBC32.dll and ODBCCP32.dll.
I thought I had run a trace, but I did not realize I had to switch it on. I
ran a trace and found these error lines toward the end. This trace was only
running while I opened the problem table in the DB window.
MQ_CLEAN 878-58c EXIT SQLExecute with return code -1 (SQL_ERROR)
HSTMT 09022400
DIAG [01000] [Microsoft][ODBC SQL Server Driver][TCP/IP
Sockets]ConnectionRead (recv()). (10054)
DIAG [08S01] [Microsoft][ODBC SQL Server Driver][TCP/IP Sock
ets]General
network error. Check your network documentation. (11)
I am not familiar with ODBC calls, but I see there were two error messages
I have done some more testing and found that I can open a form that displays
the contents of this table in a listbox with no errors. The RowSource for
the listbox is a select query that selects all fields of the table.
Yet, if I create a stand alone query that does the same, it fails.
I am beginning to suspect network problems, possibly the NIC on the server.
We had problems with that before and it was replaced. The connection into
the main network from my machine also goes through a switch, which could be
acting up.
I still think I am going to update MDAC to the same as the server, or do you
think 2.8 would be better?
Ragnar|||You'd definitely want to be on 2.7 SP1 at least due to all
of the bug fixes for it since 2.6 SP2. I'd try to go that
route first being that it works on your server.
2.8 has a lot of security changes and it should be tested in
another environment if possible before installing. It's the
base mdac version for Windows 2003 but I don't think
anything other products, service packs, etc are installing
it at this point.
The list box testing you did is odd, otherwise I'd suspect
network issues as well (or wouldn't rule them out anyway).
Were both of the connections, data source properties for the
list box and the stand alone query exactly the same?
-Sue
On Thu, 11 Mar 2004 13:13:12 -0500, "Ragnar Midtskogen"
<ragnar_ng@.optonline.net> wrote:

>Thank you Sue,
>I had run ComponentChecker on my development machine and it now has 2.6 SP2
>Refresh wih a couple of files being slightly newer than expected, ODBC32.dl
l
>and ODBCCP32.dll.
>The Server had 2.7 SP1 Refresh with about 8 files slightly newer than
>expected, including the ODBC32.dll and ODBCCP32.dll.
>I thought I had run a trace, but I did not realize I had to switch it on. I
>ran a trace and found these error lines toward the end. This trace was only
>running while I opened the problem table in the DB window.
>MQ_CLEAN 878-58c EXIT SQLExecute with return code -1 (SQL_ERROR)
> HSTMT 09022400
> DIAG [01000] [Microsoft][ODBC SQL Server Driver][TCP/IP
>Sockets]ConnectionRead (recv()). (10054)
> DIAG [08S01] [Microsoft][ODBC SQL Server Driver][TCP/IP S
ockets]General
>network error. Check your network documentation. (11)
>I am not familiar with ODBC calls, but I see there were two error messages
>I have done some more testing and found that I can open a form that display
s
>the contents of this table in a listbox with no errors. The RowSource for
>the listbox is a select query that selects all fields of the table.
>Yet, if I create a stand alone query that does the same, it fails.
>I am beginning to suspect network problems, possibly the NIC on the server.
>We had problems with that before and it was replaced. The connection into
>the main network from my machine also goes through a switch, which could be
>acting up.
>I still think I am going to update MDAC to the same as the server, or do yo
u
>think 2.8 would be better?
>Ragnar
>|||Thank you Sue,

> You'd definitely want to be on 2.7 SP1 at least due to all
> of the bug fixes for it since 2.6 SP2. I'd try to go that
> route first being that it works on your server.
That is what I did. Unfortunately the problem is still there.

> The list box testing you did is odd, otherwise I'd suspect
> network issues as well (or wouldn't rule them out anyway).
> Were both of the connections, data source properties for the
> list box and the stand alone query exactly the same?
They both use the same linked table.
I suspect the network connection now, I tried running my app on another
machine on the network and the problem disappeared.
Ragnar|||Hi, I had a similar issue with an Oracle database connection. I got around
it by removing the linked tables, compacting/repairing the Access database a
nd recreating the links. I suspect that Access is maintaining some informat
ion about the back-end tables that is not visible and is corrupt. If so, th
is information is removed by the repair if the link is gone. Anyway, hopefu
lly this will also work for you.
... Bob

quote:

---

Originally posted by Ragnar Midtskogen
Thank you Sue,

> You'd definitely want to be on 2.7 SP1 at least due to all
> of the bug fixes for it since 2.6 SP2. I'd try to go that
> route first being that it works on your server.
That is what I did. Unfortunately the problem is still there.

> The list box testing you did is odd, otherwise I'd suspect
> network issues as well (or wouldn't rule them out anyway).
> Were both of the connections, data source properties for the
> list box and the stand alone query exactly the same?
They both use the same linked table.
I suspect the network connection now, I tried running my app on another
machine on the network and the problem disappeared.
Ragnar

---

ODBC call failed

Hello,
I use Linked Tables to connect to my SQL Server database.
I have a number of reports which worked fine when I was
using test data (few thousand records).
I just loaded in a million records and ran a report, and
received the following error:
ODBC call failed
when running this query:
SELECT regionorder, Regions3, 1 As Quarter, January as
A, February as B, March as C FROM
RegionofResidenceCrosstab
UNION ALL
SELECT regionorder, Regions3, 2 As Quarter, April, May,
June FROM RegionofResidenceCrosstab
UNION ALL
SELECT regionorder, Regions3, 3 As Quarter, July, August,
September FROM RegionofResidenceCrosstab
UNION ALL SELECT regionorder, Regions3, 4 As Quarter,
October, November, December FROM
RegionofResidenceCrosstab;
RegionofResidenceCrosstab looks like this:
TRANSFORM "~" & Count(IIf(DatePart('yyyy',
& #91;F_ARRV_DAT],1,0)=forms!DateRangePrev
iousCurrentYear!
PrevYear,[Final_Clean Tourists Query].[TRAV-KEY])) & "~"
& Count(IIf(DatePart('yyyy',[F_ARRV_DAT],1,0)=forms!
DateRangePreviousCurrentYear!CurrYear,[Final_Clean
Tourists Query].[TRAV-KEY])) AS Expr1
SELECT [Final_Clean Tourists Query].REGIONS3,
[Final_Clean Tourists Query].regionorder
FROM [Final_Clean Tourists Query]
WHERE [Final_Clean Tourists Query].REGIONS3<>''
GROUP BY [Final_Clean Tourists Query].regionorder,
[Final_Clean Tourists Query].REGIONS3
PIVOT Format([F_ARRV_DAT],"mmmm") in
("January", "February", "March", "April","May","June","Jul
y","August","September","October","November","December");
1. I am wondering if the query is not simply timing out.
2. Given that these queries were written for MS Access
maybe I need to convert them into stored procedures to be
called by the report.
Any other suggestions?Your particular query is way too much for Access to handle. The UNION
clauses are causing all rows to be fetched locally into Access for
processing. Then Access tries to run the IIf and other functions on
each row, one row at a time. So the answer is, yes indeedy, you need
to convert this monster query to a stored procedure, which you can
then call from a pass-through query that you can base the report on.
One other point -- Keep all format functions and the like in the
report itself, don't put those in the stored procedure. You'll just
slow down the execution of the stored procedure, and the report has to
process each line in the result set one at a time anyway. What you
want to do is let the stored procedure do all the data processing,
hand the result set back to Access, and let Access do the
presentation.
-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446
On Fri, 19 Mar 2004 09:20:34 -0800, "blinton25"
<blinton25@.hotmail.com> wrote:

>Hello,
>I use Linked Tables to connect to my SQL Server database.
>I have a number of reports which worked fine when I was
>using test data (few thousand records).
>I just loaded in a million records and ran a report, and
>received the following error:
>ODBC call failed
>when running this query:
>SELECT regionorder, Regions3, 1 As Quarter, January as
>A, February as B, March as C FROM
>RegionofResidenceCrosstab
>UNION ALL
>SELECT regionorder, Regions3, 2 As Quarter, April, May,
>June FROM RegionofResidenceCrosstab
>UNION ALL
>SELECT regionorder, Regions3, 3 As Quarter, July, August,
>September FROM RegionofResidenceCrosstab
>UNION ALL SELECT regionorder, Regions3, 4 As Quarter,
>October, November, December FROM
>RegionofResidenceCrosstab;
>RegionofResidenceCrosstab looks like this:
>TRANSFORM "~" & Count(IIf(DatePart('yyyy',
>& #91;F_ARRV_DAT],1,0)=forms!DateRangePrev
iousCurrentYear!
>PrevYear,[Final_Clean Tourists Query].[TRAV-KEY])) & "~"
>& Count(IIf(DatePart('yyyy',[F_ARRV_DAT],1,0)=forms!
>DateRangePreviousCurrentYear!CurrYear,[Final_Clean
>Tourists Query].[TRAV-KEY])) AS Expr1
>SELECT [Final_Clean Tourists Query].REGIONS3,
>[Final_Clean Tourists Query].regionorder
>FROM [Final_Clean Tourists Query]
>WHERE [Final_Clean Tourists Query].REGIONS3<>''
>GROUP BY [Final_Clean Tourists Query].regionorder,
>[Final_Clean Tourists Query].REGIONS3
>PIVOT Format([F_ARRV_DAT],"mmmm") in
>("January", "February", "March", "April","May","June","Jul
>y","August","September","October","November","December");
>
>1. I am wondering if the query is not simply timing out.
>2. Given that these queries were written for MS Access
>maybe I need to convert them into stored procedures to be
>called by the report.
>Any other suggestions?
>|||Hi,
Thanks for the pointers, guess I have my work cut out for
me. I may also try upsizing to an ADP to try to improve
performance.

>--Original Message--
>Your particular query is way too much for Access to
handle. The UNION
>clauses are causing all rows to be fetched locally into
Access for
>processing. Then Access tries to run the IIf and other
functions on
>each row, one row at a time. So the answer is, yes
indeedy, you need
>to convert this monster query to a stored procedure,
which you can
>then call from a pass-through query that you can base the
report on.
>One other point -- Keep all format functions and the like
in the
>report itself, don't put those in the stored procedure.
You'll just
>slow down the execution of the stored procedure, and the
report has to
>process each line in the result set one at a time anyway.
What you
>want to do is let the stored procedure do all the data
processing,
>hand the result set back to Access, and let Access do the
>presentation.
>-- Mary
>Microsoft Access Developer's Guide to SQL Server
>http://www.amazon.com/exec/obidos/ASIN/0672319446
>On Fri, 19 Mar 2004 09:20:34 -0800, "blinton25"
><blinton25@.hotmail.com> wrote:
>
database.
August,
("January", "February", "March", "April","May","June","Jul
be
>.
>|||You will gain nothing in performance simply from upsizing to an ADP.
There's nothing magical about an ADP vs. an mbd with linked tables --
you're going to need to rewrite your app no matter which way you slice
it. You'll still need to re-do that query as a proc (since there's no
such thing as a saved query in an ADP anyway). What you give up in an
ADP is a lot of flexibility (local storage, queries, etc.). Also,
forms work differently by fetching snapshots, not dynasets. There's
simply no easy way to upsize a large, complex Jet app that doesn't
involve a re-write.
-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446
On Sat, 20 Mar 2004 10:24:00 -0800, "blinton25"
<blinton25@.hotmail.com> wrote:
>Hi,
>Thanks for the pointers, guess I have my work cut out for
>me. I may also try upsizing to an ADP to try to improve
>performance.
>
>handle. The UNION
>Access for
>functions on
>indeedy, you need
>which you can
>report on.
>in the
>You'll just
>report has to
>What you
>processing,
>database.
>August,
>("January", "February", "March", "April","May","June","Jul
>be|||Hello,
Sorry, I am looking at performance in terms of moving
processing to the server versus the client and reducing
network traffic. Understood that I will have to rewrite
the queries as stored procedures, and initially will do
this and then pass through to the stored Proc.
Doing some reading at:
http://www.upsizewizard.com/Reference_Page.htm
http://www.granite.ab.ca/access/sqlserverupsizing.htm
to help me understand some approaches to achieving my
goals.
Thanks again for the guidance.

>--Original Message--
>You will gain nothing in performance simply from
upsizing to an ADP.
>There's nothing magical about an ADP vs. an mbd with
linked tables --
>you're going to need to rewrite your app no matter which
way you slice
>it. You'll still need to re-do that query as a proc
(since there's no
>such thing as a saved query in an ADP anyway). What you
give up in an
>ADP is a lot of flexibility (local storage, queries,
etc.). Also,
>forms work differently by fetching snapshots, not
dynasets. There's
>simply no easy way to upsize a large, complex Jet app
that doesn't
>involve a re-write.
>-- Mary
>Microsoft Access Developer's Guide to SQL Server
>http://www.amazon.com/exec/obidos/ASIN/0672319446
>On Sat, 20 Mar 2004 10:24:00 -0800, "blinton25"
><blinton25@.hotmail.com> wrote:
>
for
into
the
like
procedure.
the
anyway.
the
was
and
as
May,
Quarter,
& "~"
("January", "February", "March", "April","May","June","Jul
");
out.
Access
to
>.
>|||I understood that -- I was just saying that you gain nothing in terms
of performance from an ADP that you can't also achieve with an mdb
front-end. The key is fetching only needed data on the client, and an
mdb gives you a lot more options for caching static data locally, etc.
The first link you reference in your post does not answer the question
it poses "why ADP" other than to spout generalities about
client-server being faster. What the Access team itself is saying (at
recent conferences) is that they are recommending mdb over adp because
you give up so much flexibility with an adp. Performance is very much
dependent on factors such as efficient database design, avoidance of
concurrency conflicts, fetching less data, network speed and other
factors that have nothing to do with the client software. It's good
that you're doing reading up on the subject before leaping in, just be
aware that not everything you read is necessarily true or accurate.
-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446
On Sun, 21 Mar 2004 09:12:30 -0800, "blinton25"
<blinton25@.hotmail.com> wrote:
>Hello,
>Sorry, I am looking at performance in terms of moving
>processing to the server versus the client and reducing
>network traffic. Understood that I will have to rewrite
>the queries as stored procedures, and initially will do
>this and then pass through to the stored Proc.
>Doing some reading at:
>http://www.upsizewizard.com/Reference_Page.htm
>http://www.granite.ab.ca/access/sqlserverupsizing.htm
>to help me understand some approaches to achieving my
>goals.
>Thanks again for the guidance.
>
>upsizing to an ADP.
>linked tables --
>way you slice
>(since there's no
>give up in an
>etc.). Also,
>dynasets. There's
>that doesn't
>for
>into
>the
>like
>procedure.
>the
>anyway.
>the
>was
>and
>as
>May,
>Quarter,
>& "~"
>("January", "February", "March", "April","May","June","Jul
>");
>out.
>Access
>to

ODBC Call Fail/Record locked

I have recently moved some native Access tables to SQL Server 7. These tables are updated in code. When the update is done via a SQL statement, I get
"This record is being modified by another user. . . Save, Copy to Clipboard, Drop Changes"

When the record is being updated via DAO code, I get,
"ODBC Call Fail"

Both errors are most irritating and I desperately need to find a way around this. Any suggestions would be greatly appreciated.

CrystalHi there,

Maybe this information helps you on the way...

-- Microsoft Knowledge Base Article - 128809

-- Zoek in de Google nieuwsgroepen naar Onderwerp:
"auto_increment fields and #Deleted in MS Access?"
Nieuwsgroep:
-> mailing.database.myodbc

Greetz,
DePrins
:)

ODBC call fail/ Record Locked

I am in the process of moving native Access tables over to
SQL Server 7. These tables are updated through code.
Some are updated with SQL statements executed through a db
object and others are updated using DAO.
When I update using SQL, I get "This record is being
modified by another user. . . Save, Copy to Clipboard,
Drop Changes."
When I update using DAO, the code crashes on the .Update
command and says, "ODBC call fail."
I can update the tables manually without error.
Is there a way to fix this? What am I doing wrong in the
code?
Crystal
You should seriously consider getting rid of all DAO code that
performs DML against SQL Server tables. It's the slowest, buggiest,
and least-efficient way of performing any task. The reason is that you
are invoking an instance of the Jet engine on every call. The result
is that the call goes through Jet-ODBC-SQL Server. However, if you use
SQL statements in a pass-through query, the statement is passed
directly to SQL Server, where it is executed on the server. This
results in faster, more efficient transactions. Pass-through queries
also give you the capability of calling stored procedures, and can be
used as the basis of reports. If you must use recordsets for some
reason, use ADO, not DAO when going against SQL Server data.
-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446
On Mon, 3 May 2004 06:32:13 -0700, "Crystal"
<anonymous@.discussions.microsoft.com> wrote:

>I am in the process of moving native Access tables over to
>SQL Server 7. These tables are updated through code.
>Some are updated with SQL statements executed through a db
>object and others are updated using DAO.
>When I update using SQL, I get "This record is being
>modified by another user. . . Save, Copy to Clipboard,
>Drop Changes."
>When I update using DAO, the code crashes on the .Update
>command and says, "ODBC call fail."
>I can update the tables manually without error.
>Is there a way to fix this? What am I doing wrong in the
>code?
>Crystal

ODBC call fail/ Record Locked

I am in the process of moving native Access tables over to
SQL Server 7. These tables are updated through code.
Some are updated with SQL statements executed through a db
object and others are updated using DAO.
When I update using SQL, I get "This record is being
modified by another user. . . Save, Copy to Clipboard,
Drop Changes."
When I update using DAO, the code crashes on the .Update
command and says, "ODBC call fail."
I can update the tables manually without error.
Is there a way to fix this? What am I doing wrong in the
code?
CrystalYou should seriously consider getting rid of all DAO code that
performs DML against SQL Server tables. It's the slowest, buggiest,
and least-efficient way of performing any task. The reason is that you
are invoking an instance of the Jet engine on every call. The result
is that the call goes through Jet-ODBC-SQL Server. However, if you use
SQL statements in a pass-through query, the statement is passed
directly to SQL Server, where it is executed on the server. This
results in faster, more efficient transactions. Pass-through queries
also give you the capability of calling stored procedures, and can be
used as the basis of reports. If you must use recordsets for some
reason, use ADO, not DAO when going against SQL Server data.
-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446
On Mon, 3 May 2004 06:32:13 -0700, "Crystal"
<anonymous@.discussions.microsoft.com> wrote:

>I am in the process of moving native Access tables over to
>SQL Server 7. These tables are updated through code.
>Some are updated with SQL statements executed through a db
>object and others are updated using DAO.
>When I update using SQL, I get "This record is being
>modified by another user. . . Save, Copy to Clipboard,
>Drop Changes."
>When I update using DAO, the code crashes on the .Update
>command and says, "ODBC call fail."
>I can update the tables manually without error.
>Is there a way to fix this? What am I doing wrong in the
>code?
>Crystal

ODBC Call fail - Strange behaviour

Hi.
I just installed Sql server (in fact MSDE) and exported my Access 2002
projet to this database instance.
I then linked my Sql server tables within my access project.
I get a strange behaviour for some queries. I receive the message 'ODBC -
Call fail'. It seems to be a timeout pb.
The query involves two tables.
I checked my query. Everithing is ok.
I checked the first table. I can read data and filter it.
I checked the second table. I can read data, but if I want to filter any
column, I get an ODBC call fail. The table contains only 802 records.
Any idea ?
Configuration :
MSDE Rel A on XP home SP1.
Access 2002 client, MDAC 2.8, on XP Pro.
Check your data on the SQL server. You may have some data that is null and
the SQL server table def is not allowing nulls? Just a thought. Usually
these errors are caught during the import process...
I would start by creating a brand new query and only add one column at a
time. See if you can then sort on that column. Carry on like this - adding
new columns each time until the error happens. Then check that columns data
on the SQL server so see if there happens to be null values present...
"Alain" <alain.roghi_nospam@.laposte.net> wrote in message
news:uU5H9BEmEHA.2180@.TK2MSFTNGP12.phx.gbl...
> Hi.
> I just installed Sql server (in fact MSDE) and exported my Access 2002
> projet to this database instance.
> I then linked my Sql server tables within my access project.
> I get a strange behaviour for some queries. I receive the message 'ODBC -
> Call fail'. It seems to be a timeout pb.
> The query involves two tables.
> I checked my query. Everithing is ok.
> I checked the first table. I can read data and filter it.
> I checked the second table. I can read data, but if I want to filter any
> column, I get an ODBC call fail. The table contains only 802 records.
> Any idea ?
> Configuration :
> MSDE Rel A on XP home SP1.
> Access 2002 client, MDAC 2.8, on XP Pro.
>

Odbc access

I have a DB and some tables that I'm moving to Sql2005 machine that just get
query access. People use ACCESS with ODBC link table.
What permissions other than datareader would allow them to only see USER
tables?
Thanks.
db_datareader is enough.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"AHartman" wrote:

> I have a DB and some tables that I'm moving to Sql2005 machine that just get
> query access. People use ACCESS with ODBC link table.
> What permissions other than datareader would allow them to only see USER
> tables?
>
> Thanks.
>
>
|||That's what I gave but when linking the tables the user saw lot of sys
tables also.
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:9602F1EB-8E2D-4B02-85FE-25FC2689E670@.microsoft.com...[vbcol=seagreen]
> db_datareader is enough.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "AHartman" wrote:
|||Do not worry about those tables, they are SQL Server catalog views.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"AHartman" wrote:

> That's what I gave but when linking the tables the user saw lot of sys
> tables also.
>
> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> news:9602F1EB-8E2D-4B02-85FE-25FC2689E670@.microsoft.com...
>
|||Thanks..
Just didn't want to clutter there view of valid tables...
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:410C1840-0476-4606-8BC9-70D2345EC6E2@.microsoft.com...[vbcol=seagreen]
> Do not worry about those tables, they are SQL Server catalog views.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "AHartman" wrote:

Odbc access

I have a DB and some tables that I'm moving to Sql2005 machine that just get
query access. People use ACCESS with ODBC link table.
What permissions other than datareader would allow them to only see USER
tables?
Thanks.db_datareader is enough.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"AHartman" wrote:
> I have a DB and some tables that I'm moving to Sql2005 machine that just get
> query access. People use ACCESS with ODBC link table.
> What permissions other than datareader would allow them to only see USER
> tables?
>
> Thanks.
>
>|||That's what I gave but when linking the tables the user saw lot of sys
tables also.
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:9602F1EB-8E2D-4B02-85FE-25FC2689E670@.microsoft.com...
> db_datareader is enough.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "AHartman" wrote:
>> I have a DB and some tables that I'm moving to Sql2005 machine that just
>> get
>> query access. People use ACCESS with ODBC link table.
>> What permissions other than datareader would allow them to only see USER
>> tables?
>>
>> Thanks.
>>|||Do not worry about those tables, they are SQL Server catalog views.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"AHartman" wrote:
> That's what I gave but when linking the tables the user saw lot of sys
> tables also.
>
> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> news:9602F1EB-8E2D-4B02-85FE-25FC2689E670@.microsoft.com...
> >
> > db_datareader is enough.
> >
> > Hope this helps,
> >
> > Ben Nevarez
> > Senior Database Administrator
> > AIG SunAmerica
> >
> >
> >
> > "AHartman" wrote:
> >
> >> I have a DB and some tables that I'm moving to Sql2005 machine that just
> >> get
> >> query access. People use ACCESS with ODBC link table.
> >> What permissions other than datareader would allow them to only see USER
> >> tables?
> >>
> >>
> >> Thanks.
> >>
> >>
> >>
>|||Thanks..
Just didn't want to clutter there view of valid tables...
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:410C1840-0476-4606-8BC9-70D2345EC6E2@.microsoft.com...
> Do not worry about those tables, they are SQL Server catalog views.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "AHartman" wrote:
>> That's what I gave but when linking the tables the user saw lot of sys
>> tables also.
>>
>> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
>> news:9602F1EB-8E2D-4B02-85FE-25FC2689E670@.microsoft.com...
>> >
>> > db_datareader is enough.
>> >
>> > Hope this helps,
>> >
>> > Ben Nevarez
>> > Senior Database Administrator
>> > AIG SunAmerica
>> >
>> >
>> >
>> > "AHartman" wrote:
>> >
>> >> I have a DB and some tables that I'm moving to Sql2005 machine that
>> >> just
>> >> get
>> >> query access. People use ACCESS with ODBC link table.
>> >> What permissions other than datareader would allow them to only see
>> >> USER
>> >> tables?
>> >>
>> >>
>> >> Thanks.
>> >>
>> >>
>> >>
>>

Odbc access

I have a DB and some tables that I'm moving to Sql2005 machine that just get
query access. People use ACCESS with ODBC link table.
What permissions other than datareader would allow them to only see USER
tables?
Thanks.db_datareader is enough.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"AHartman" wrote:

> I have a DB and some tables that I'm moving to Sql2005 machine that just g
et
> query access. People use ACCESS with ODBC link table.
> What permissions other than datareader would allow them to only see USER
> tables?
>
> Thanks.
>
>|||That's what I gave but when linking the tables the user saw lot of sys
tables also.
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:9602F1EB-8E2D-4B02-85FE-25FC2689E670@.microsoft.com...[vbcol=seagreen]
> db_datareader is enough.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "AHartman" wrote:
>|||Do not worry about those tables, they are SQL Server catalog views.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"AHartman" wrote:

> That's what I gave but when linking the tables the user saw lot of sys
> tables also.
>
> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> news:9602F1EB-8E2D-4B02-85FE-25FC2689E670@.microsoft.com...
>|||Thanks..
Just didn't want to clutter there view of valid tables...
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:410C1840-0476-4606-8BC9-70D2345EC6E2@.microsoft.com...[vbcol=seagreen]
> Do not worry about those tables, they are SQL Server catalog views.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "AHartman" wrote:
>

obtaining the names of triggers within T-SQL

I have a db containing 2 base tables and a view with an INSTEAD OF INSERT
trigger that updates both tables. I want to prevent inserts on the base
tables themselves with an INSTEAD OF INSERT trigger that would raise an erro
r
indicating that the appliation should insert against the view and then
rolling back the insert on the base table.
Is there a way to find out the names of the triggers in play so I could do
this? (I've experimented with trigger_nestlevel() but it doesn't do exactly
what I want.)
This is on SQL Server 2000.Brian,
Deny "insert" permission on the table, grant "insert" on the view.
AMB
"Brian Cobb" wrote:

> I have a db containing 2 base tables and a view with an INSTEAD OF INSERT
> trigger that updates both tables. I want to prevent inserts on the base
> tables themselves with an INSTEAD OF INSERT trigger that would raise an er
ror
> indicating that the appliation should insert against the view and then
> rolling back the insert on the base table.
> Is there a way to find out the names of the triggers in play so I could do
> this? (I've experimented with trigger_nestlevel() but it doesn't do exact
ly
> what I want.)
> This is on SQL Server 2000.
>|||That will work only if one can safely assume one has control over what users
and roles will be permitted in the database, won't it?
"Alejandro Mesa" wrote:
> Brian,
> Deny "insert" permission on the table, grant "insert" on the view.
>
> AMB
> "Brian Cobb" wrote:
>|||> That will work only if one can safely assume one has control over what
> users
> and roles will be permitted in the database, won't it?
Yes - And why wouldn't one make this assumption? Any "solution" can be
circumvented by someone with administrative access to the database. The
suggestion, by far, is the most straightforward one and involves no obscure
code to determine the "triggers in play" (without know specifically what
that means).|||I thought "triggers in play" was obvious given the context in which it was
used in my original post. But let me try to clarify. If a trigger,
trigger1, performs an action that initiates another trigger, trigger2, is it
possible within trigger2 to determine the name of the initiating trigger?
I am only interested in an answer to my question as posed in the original
post, and (hopefully) clarified here. Please feel free not to respond if yo
u
can't answer it.
"Scott Morris" wrote:

> Yes - And why wouldn't one make this assumption? Any "solution" can be
> circumvented by someone with administrative access to the database. The
> suggestion, by far, is the most straightforward one and involves no obscur
e
> code to determine the "triggers in play" (without know specifically what
> that means).
>
>|||On Fri, 17 Mar 2006 12:41:26 -0800, Brian Cobb wrote:

>I thought "triggers in play" was obvious given the context in which it was
>used in my original post. But let me try to clarify. If a trigger,
>trigger1, performs an action that initiates another trigger, trigger2, is i
t
>possible within trigger2 to determine the name of the initiating trigger?
Hi Brian,
There's no easy way to do that. The only method I can think of is to
query TRIGGER_NESTLEVEL(object_id) with the object_id of each trigger
that might be active when trigger2 runs; if it's not 0, trigger2 must
have been fired (either directly or indirectly) by that trigger.

>I am only interested in an answer to my question as posed in the original
>post, and (hopefully) clarified here. Please feel free not to respond if y
ou
>can't answer it.
The only correct answer to your original post would be to dump the idea
of enforcing this with triggers. Use DCL, as suggested by Alejandro.
Hugo Kornelis, SQL Server MVP|||I suspected it probably couldn't be done the way I hoped but wanted some
outside input. I had already considered something similar to what Mr. Mesa
had suggested, prior to my first post, which I will now revisit.
Thank you for your response.
"Hugo Kornelis" wrote:

> On Fri, 17 Mar 2006 12:41:26 -0800, Brian Cobb wrote:
>
> Hi Brian,
> There's no easy way to do that. The only method I can think of is to
> query TRIGGER_NESTLEVEL(object_id) with the object_id of each trigger
> that might be active when trigger2 runs; if it's not 0, trigger2 must
> have been fired (either directly or indirectly) by that trigger.
>
> The only correct answer to your original post would be to dump the idea
> of enforcing this with triggers. Use DCL, as suggested by Alejandro.
> --
> Hugo Kornelis, SQL Server MVP
>

Obtaining the latest record by date

Hi,

I have two tables, one stores items, and another one that stores history for the items from first table. They are related by a foreign key from items table.

In one of my queries, I need to obtain the latest history entry from table 2. For this I use MAX(RecordDate) aggregate. Another way that I know is:

SELECT TOP(1) RecordDate, HistoryID, ....
FROM ProductHistory
WHERE (SerialNumber = '20070101000010')
ORDER BY RecordDate DESC, HistoryID DESC

The query is part of a larger query where I obtain more data from other tables.

The reason I prefer the second option is that, at times I have multiple entries for the same item on the same date (no time info). In such cases, the data returned is the latest entry (HistoryID) in to the system of the rows with same date, thanks to ORDER BY clause. Another reason is that I can select any columns, whereas in the first query type I need to use subquery to obtain other fields after I find my relevant row.

My question is, is there any problem using the second method that I am not aware of? Is this method reliable (TOP(1))? I appreciate if you can guide me to the right method. Thanks!

You're absolutely fine to use TOP in the way that you have done.

The most common mistake when using TOP is to omit an ORDER BY clause where it should really be included, which can make the results unpredicatable and inconsistent. As you have included an ORDER BY clause then you'll be fine.

Chris

|||

Thank you for your answer Chris. My main concern was whether TOP was applied before ORDER BY or to the result of the query at the end. It seems it is applied after all the filters are applied, showing only the portion of the result matching the whole query. Please corrent me if I am wrong. I will mark your response as the answer.

Migrant

Obtaining Primary and Foreign Key information from a table

Hi,
I would like to query the system tables in order to obtain all the Primary
and Foreign keys for any particular table.
I have tried using the syscolumns.colstat field but it only outputs zero. I
understand it should output 1 if the column is a primary key.
Also I do not know I do not know how to obtain Foreign key info on a table.
All help is much appreciated.
Kind regards,
Polly AnnaWhat version of SQL Server are you using?
"Polly Anna" <PollyAnna@.discussions.microsoft.com> wrote in message
news:5C722FFB-15A3-4E9C-81D5-1969AFA77EED@.microsoft.com...
> Hi,
> I would like to query the system tables in order to obtain all the Primary
> and Foreign keys for any particular table.
> I have tried using the syscolumns.colstat field but it only outputs zero.
> I
> understand it should output 1 if the column is a primary key.
> Also I do not know I do not know how to obtain Foreign key info on a
> table.
> All help is much appreciated.
> Kind regards,
> Polly Anna|||Hi Aaron,
I am using my query against both a SQL2000 and SQL2005 db.
I attach the query below.
Many thanks for your help.
Polly Anna
select distinct column_name as Column_Name,
data_type,
c.length,
CASE c.colStat
WHEN 1 THEN 'Y' ELSE ''
END AS PrimaryKey
from information_schema.columns as isc
left join sysobjects o
on isc.table_name = o.name
left join syscolumns c
on o.id = c.id and c.name = isc.column_name
where table_name = 'Dentist'
"Aaron Bertrand [SQL Server MVP]" wrote:
> What version of SQL Server are you using?
>
> "Polly Anna" <PollyAnna@.discussions.microsoft.com> wrote in message
> news:5C722FFB-15A3-4E9C-81D5-1969AFA77EED@.microsoft.com...
> > Hi,
> >
> > I would like to query the system tables in order to obtain all the Primary
> > and Foreign keys for any particular table.
> >
> > I have tried using the syscolumns.colstat field but it only outputs zero.
> > I
> > understand it should output 1 if the column is a primary key.
> >
> > Also I do not know I do not know how to obtain Foreign key info on a
> > table.
> >
> > All help is much appreciated.
> >
> > Kind regards,
> >
> > Polly Anna
>
>|||Polly
SS2005
select s.name as TABLE_SCHEMA, t.name as TABLE_NAME
, k.name as CONSTRAINT_NAME, k.type_desc as CONSTRAINT_TYPE
, c.name as COLUMN_NAME, ic.key_ordinal AS ORDINAL_POSITION
from sys.key_constraints as k
join sys.tables as t
on t.object_id = k.parent_object_id
join sys.schemas as s
on s.schema_id = t.schema_id
join sys.index_columns as ic
on ic.object_id = t.object_id
and ic.index_id = k.unique_index_id
join sys.columns as c
on c.object_id = t.object_id
and c.column_id = ic.column_id
order by TABLE_SCHEMA, TABLE_NAME, CONSTRAINT_TYPE, CONSTRAINT_NAME,
ORDINAL_POSITION;
"Polly Anna" <PollyAnna@.discussions.microsoft.com> wrote in message
news:5EE12A0F-2555-47B1-A143-25E7DA9BA11A@.microsoft.com...
> Hi Aaron,
> I am using my query against both a SQL2000 and SQL2005 db.
> I attach the query below.
> Many thanks for your help.
> Polly Anna
> select distinct column_name as Column_Name,
> data_type,
> c.length,
> CASE c.colStat
> WHEN 1 THEN 'Y' ELSE ''
> END AS PrimaryKey
> from information_schema.columns as isc
> left join sysobjects o
> on isc.table_name = o.name
> left join syscolumns c
> on o.id = c.id and c.name = isc.column_name
> where table_name = 'Dentist'
>
> "Aaron Bertrand [SQL Server MVP]" wrote:
>> What version of SQL Server are you using?
>>
>> "Polly Anna" <PollyAnna@.discussions.microsoft.com> wrote in message
>> news:5C722FFB-15A3-4E9C-81D5-1969AFA77EED@.microsoft.com...
>> > Hi,
>> >
>> > I would like to query the system tables in order to obtain all the
>> > Primary
>> > and Foreign keys for any particular table.
>> >
>> > I have tried using the syscolumns.colstat field but it only outputs
>> > zero.
>> > I
>> > understand it should output 1 if the column is a primary key.
>> >
>> > Also I do not know I do not know how to obtain Foreign key info on a
>> > table.
>> >
>> > All help is much appreciated.
>> >
>> > Kind regards,
>> >
>> > Polly Anna
>>|||Hi Uri,
yes thank you, that is brilliant. It works in SS2005. You don't happen to
have the script for SQL2000?
Thank you very much indeed.
Kind regards,
Polly Anna
"Uri Dimant" wrote:
> Polly
> SS2005
> select s.name as TABLE_SCHEMA, t.name as TABLE_NAME
> , k.name as CONSTRAINT_NAME, k.type_desc as CONSTRAINT_TYPE
> , c.name as COLUMN_NAME, ic.key_ordinal AS ORDINAL_POSITION
> from sys.key_constraints as k
> join sys.tables as t
> on t.object_id = k.parent_object_id
> join sys.schemas as s
> on s.schema_id = t.schema_id
> join sys.index_columns as ic
> on ic.object_id = t.object_id
> and ic.index_id = k.unique_index_id
> join sys.columns as c
> on c.object_id = t.object_id
> and c.column_id = ic.column_id
> order by TABLE_SCHEMA, TABLE_NAME, CONSTRAINT_TYPE, CONSTRAINT_NAME,
> ORDINAL_POSITION;
>
> "Polly Anna" <PollyAnna@.discussions.microsoft.com> wrote in message
> news:5EE12A0F-2555-47B1-A143-25E7DA9BA11A@.microsoft.com...
> > Hi Aaron,
> >
> > I am using my query against both a SQL2000 and SQL2005 db.
> >
> > I attach the query below.
> >
> > Many thanks for your help.
> >
> > Polly Anna
> >
> > select distinct column_name as Column_Name,
> > data_type,
> > c.length,
> > CASE c.colStat
> > WHEN 1 THEN 'Y' ELSE ''
> > END AS PrimaryKey
> >
> > from information_schema.columns as isc
> > left join sysobjects o
> > on isc.table_name = o.name
> > left join syscolumns c
> > on o.id = c.id and c.name = isc.column_name
> > where table_name = 'Dentist'
> >
> >
> > "Aaron Bertrand [SQL Server MVP]" wrote:
> >
> >> What version of SQL Server are you using?
> >>
> >>
> >>
> >> "Polly Anna" <PollyAnna@.discussions.microsoft.com> wrote in message
> >> news:5C722FFB-15A3-4E9C-81D5-1969AFA77EED@.microsoft.com...
> >> > Hi,
> >> >
> >> > I would like to query the system tables in order to obtain all the
> >> > Primary
> >> > and Foreign keys for any particular table.
> >> >
> >> > I have tried using the syscolumns.colstat field but it only outputs
> >> > zero.
> >> > I
> >> > understand it should output 1 if the column is a primary key.
> >> >
> >> > Also I do not know I do not know how to obtain Foreign key info on a
> >> > table.
> >> >
> >> > All help is much appreciated.
> >> >
> >> > Kind regards,
> >> >
> >> > Polly Anna
> >>
> >>
> >>
>
>|||SELECT t.TABLE_SCHEMA,
t.TABLE_NAME, c.CONSTRAINT_NAME,
c.CONSTRAINT_TYPE, k.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLES t
INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS c
ON t.TABLE_SCHEMA = c.TABLE_SCHEMA
AND t.TABLE_NAME = c.TABLE_NAME
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE k
ON c.TABLE_SCHEMA = k.TABLE_SCHEMA
AND c.CONSTRAINT_SCHEMA = k.CONSTRAINT_SCHEMA
AND c.TABLE_NAME = k.TABLE_NAME
WHERE
c.CONSTRAINT_TYPE IN ('PRIMARY KEY', 'FOREIGN KEY')
ORDER BY 1,2,4 DESC,3;
"Polly Anna" <PollyAnna@.discussions.microsoft.com> wrote in message
news:5271BE12-A6E9-4C72-B3FC-17AF8AF1B213@.microsoft.com...
> Hi Uri,
> yes thank you, that is brilliant. It works in SS2005. You don't happen to
> have the script for SQL2000?
> Thank you very much indeed.
> Kind regards,
> Polly Anna
> "Uri Dimant" wrote:
>> Polly
>> SS2005
>> select s.name as TABLE_SCHEMA, t.name as TABLE_NAME
>> , k.name as CONSTRAINT_NAME, k.type_desc as CONSTRAINT_TYPE
>> , c.name as COLUMN_NAME, ic.key_ordinal AS ORDINAL_POSITION
>> from sys.key_constraints as k
>> join sys.tables as t
>> on t.object_id = k.parent_object_id
>> join sys.schemas as s
>> on s.schema_id = t.schema_id
>> join sys.index_columns as ic
>> on ic.object_id = t.object_id
>> and ic.index_id = k.unique_index_id
>> join sys.columns as c
>> on c.object_id = t.object_id
>> and c.column_id = ic.column_id
>> order by TABLE_SCHEMA, TABLE_NAME, CONSTRAINT_TYPE, CONSTRAINT_NAME,
>> ORDINAL_POSITION;
>>
>> "Polly Anna" <PollyAnna@.discussions.microsoft.com> wrote in message
>> news:5EE12A0F-2555-47B1-A143-25E7DA9BA11A@.microsoft.com...
>> > Hi Aaron,
>> >
>> > I am using my query against both a SQL2000 and SQL2005 db.
>> >
>> > I attach the query below.
>> >
>> > Many thanks for your help.
>> >
>> > Polly Anna
>> >
>> > select distinct column_name as Column_Name,
>> > data_type,
>> > c.length,
>> > CASE c.colStat
>> > WHEN 1 THEN 'Y' ELSE ''
>> > END AS PrimaryKey
>> >
>> > from information_schema.columns as isc
>> > left join sysobjects o
>> > on isc.table_name = o.name
>> > left join syscolumns c
>> > on o.id = c.id and c.name = isc.column_name
>> > where table_name = 'Dentist'
>> >
>> >
>> > "Aaron Bertrand [SQL Server MVP]" wrote:
>> >
>> >> What version of SQL Server are you using?
>> >>
>> >>
>> >>
>> >> "Polly Anna" <PollyAnna@.discussions.microsoft.com> wrote in message
>> >> news:5C722FFB-15A3-4E9C-81D5-1969AFA77EED@.microsoft.com...
>> >> > Hi,
>> >> >
>> >> > I would like to query the system tables in order to obtain all the
>> >> > Primary
>> >> > and Foreign keys for any particular table.
>> >> >
>> >> > I have tried using the syscolumns.colstat field but it only outputs
>> >> > zero.
>> >> > I
>> >> > understand it should output 1 if the column is a primary key.
>> >> >
>> >> > Also I do not know I do not know how to obtain Foreign key info on a
>> >> > table.
>> >> >
>> >> > All help is much appreciated.
>> >> >
>> >> > Kind regards,
>> >> >
>> >> > Polly Anna
>> >>
>> >>
>> >>
>>|||Hi Aaron,
it works like a charm. Thank you so much for your help.
Kind regards,
Polly Anna
"Aaron Bertrand [SQL Server MVP]" wrote:
> SELECT t.TABLE_SCHEMA,
> t.TABLE_NAME, c.CONSTRAINT_NAME,
> c.CONSTRAINT_TYPE, k.COLUMN_NAME
> FROM INFORMATION_SCHEMA.TABLES t
> INNER JOIN
> INFORMATION_SCHEMA.TABLE_CONSTRAINTS c
> ON t.TABLE_SCHEMA = c.TABLE_SCHEMA
> AND t.TABLE_NAME = c.TABLE_NAME
> INNER JOIN
> INFORMATION_SCHEMA.KEY_COLUMN_USAGE k
> ON c.TABLE_SCHEMA = k.TABLE_SCHEMA
> AND c.CONSTRAINT_SCHEMA = k.CONSTRAINT_SCHEMA
> AND c.TABLE_NAME = k.TABLE_NAME
> WHERE
> c.CONSTRAINT_TYPE IN ('PRIMARY KEY', 'FOREIGN KEY')
> ORDER BY 1,2,4 DESC,3;
>
> "Polly Anna" <PollyAnna@.discussions.microsoft.com> wrote in message
> news:5271BE12-A6E9-4C72-B3FC-17AF8AF1B213@.microsoft.com...
> > Hi Uri,
> >
> > yes thank you, that is brilliant. It works in SS2005. You don't happen to
> > have the script for SQL2000?
> >
> > Thank you very much indeed.
> >
> > Kind regards,
> >
> > Polly Anna
> >
> > "Uri Dimant" wrote:
> >
> >> Polly
> >> SS2005
> >> select s.name as TABLE_SCHEMA, t.name as TABLE_NAME
> >> , k.name as CONSTRAINT_NAME, k.type_desc as CONSTRAINT_TYPE
> >> , c.name as COLUMN_NAME, ic.key_ordinal AS ORDINAL_POSITION
> >> from sys.key_constraints as k
> >> join sys.tables as t
> >> on t.object_id = k.parent_object_id
> >> join sys.schemas as s
> >> on s.schema_id = t.schema_id
> >> join sys.index_columns as ic
> >> on ic.object_id = t.object_id
> >> and ic.index_id = k.unique_index_id
> >> join sys.columns as c
> >> on c.object_id = t.object_id
> >> and c.column_id = ic.column_id
> >> order by TABLE_SCHEMA, TABLE_NAME, CONSTRAINT_TYPE, CONSTRAINT_NAME,
> >> ORDINAL_POSITION;
> >>
> >>
> >> "Polly Anna" <PollyAnna@.discussions.microsoft.com> wrote in message
> >> news:5EE12A0F-2555-47B1-A143-25E7DA9BA11A@.microsoft.com...
> >> > Hi Aaron,
> >> >
> >> > I am using my query against both a SQL2000 and SQL2005 db.
> >> >
> >> > I attach the query below.
> >> >
> >> > Many thanks for your help.
> >> >
> >> > Polly Anna
> >> >
> >> > select distinct column_name as Column_Name,
> >> > data_type,
> >> > c.length,
> >> > CASE c.colStat
> >> > WHEN 1 THEN 'Y' ELSE ''
> >> > END AS PrimaryKey
> >> >
> >> > from information_schema.columns as isc
> >> > left join sysobjects o
> >> > on isc.table_name = o.name
> >> > left join syscolumns c
> >> > on o.id = c.id and c.name = isc.column_name
> >> > where table_name = 'Dentist'
> >> >
> >> >
> >> > "Aaron Bertrand [SQL Server MVP]" wrote:
> >> >
> >> >> What version of SQL Server are you using?
> >> >>
> >> >>
> >> >>
> >> >> "Polly Anna" <PollyAnna@.discussions.microsoft.com> wrote in message
> >> >> news:5C722FFB-15A3-4E9C-81D5-1969AFA77EED@.microsoft.com...
> >> >> > Hi,
> >> >> >
> >> >> > I would like to query the system tables in order to obtain all the
> >> >> > Primary
> >> >> > and Foreign keys for any particular table.
> >> >> >
> >> >> > I have tried using the syscolumns.colstat field but it only outputs
> >> >> > zero.
> >> >> > I
> >> >> > understand it should output 1 if the column is a primary key.
> >> >> >
> >> >> > Also I do not know I do not know how to obtain Foreign key info on a
> >> >> > table.
> >> >> >
> >> >> > All help is much appreciated.
> >> >> >
> >> >> > Kind regards,
> >> >> >
> >> >> > Polly Anna
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>

Obtaining foreing keys dependences

I want to know how to obtain the relationships of foreing keys when they have diferent names in diferent tables using SQL querys from VB.net in SQL Server 2000.

Example:

Table Person
----
IDPerson
Name
Address

Table Customer
-----
IDCustomer
.
.
.

Table Employee
-----
IDEmployee
.
.
.

The dependencies are:

IDPerson-->IDCustomer
IDPerson-->IDEmployeeI think you need to query sysforiegnkeys, syscolumns and sysobjects|||Thanks, it wasnt easy but it's done.

Rodrigo

Obtaining data from different tables in the same database

Hi

I've been doing a website with ajax controls and I have tables that has foreign key relationships and hence the main page requires the data from the tables from the foreign keys stored in a separate table. Can anyone help me out with the proper way of querying the tables. Am not able to work out the way with the joins that SQL express provides.

Thanks

Here's a sample query:

TableA contains 3 columns: Data1, Data2, ForeignKey
TableB contains 2 columns: RecordId, SomeOtherData

The RecordId value from TableB has been stored in the ForeignKey column of TableA

SELECT TableA.Data1, TableA.Data2, TableB.SomeOtherDataFROM TableAINNERJOIN TableBON TableA.ForeignKey = TableB.RecordId

Each row of data that is retrieved will look like:

Data1,Data2, [ForeignKey] <--> [RecordId],SomeOtherData

Obtaining a report with alters of a table

Thank in advance!
I need to know how Can I obtanin a report or log of alters and deletes in
some tables in a time period?Hi
Use sql profiler
Under the Filters tab
set Object ID equals to the id of the table you want to audit
set text data like 'alter%, 'delete%
Hope this help
Regards
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Nacho" <Nacho@.discussions.microsoft.com> wrote in message
news:93E9178F-6DC9-4C42-9309-760123EB7F78@.microsoft.com...
> Thank in advance!
> I need to know how Can I obtanin a report or log of alters and deletes in
> some tables in a time period?|||Is it for Audit trail? Or you just want to have to monitor it onces in a
while to check what is happening in the system?
If for audit, you can create a new table and populate it with the
information from the table affect with an After Update, Delete trigger on
table in question. This way you can history going far back as possible. As
in new table you can track time the delete/update happened and by whom.
If it's just onces in a while thing then VT's suggestion works ;-).
Thanks!
--
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Nacho" wrote:

> Thank in advance!
> I need to know how Can I obtanin a report or log of alters and deletes in
> some tables in a time period?

Obtaining a report with alters of a table

Thank in advance!
I need to know how Can I obtanin a report or log of alters and deletes in
some tables in a time period?
Is it for Audit trail? Or you just want to have to monitor it onces in a
while to check what is happening in the system?
If for audit, you can create a new table and populate it with the
information from the table affect with an After Update, Delete trigger on
table in question. This way you can history going far back as possible. As
in new table you can track time the delete/update happened and by whom.
If it's just onces in a while thing then VT's suggestion works ;-).
Thanks!
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Nacho" wrote:

> Thank in advance!
> I need to know how Can I obtanin a report or log of alters and deletes in
> some tables in a time period?