EXEC Query Performance

I am running into a situation where a program runs a stored procedure, withi
n that stored procedure a SQL statement is built and then executed using the
EXEC command. It looks like that when the generated statement exceeds a ce
rtain time threshold, the s
tored procedure exits without any warning.
I know there is no warning because I have run profiler and I get a statement
start time but no end time. I have also logged every step in the stored pr
ocedure and it just bails on me. Any ideas?Funny you mention that Peter, I run the stored procedure in QA and it runs f
ine. The funny thing is that when the stored proc fails, the rest of the pro
cess completes. I have not been able to get access to the .Net code to view
how it is executed yet.
"Dan" wrote:

> I am running into a situation where a program runs a stored procedure, within that
stored procedure a SQL statement is built and then executed using the EXEC command.
It looks like that when the generated statement exceeds a certain time threshold,
the
stored procedure exits without any warning.
> I know there is no warning because I have run profiler and I get a statement start
time but no end time. I have also logged every step in the stored procedure and it
just bails on me. Any ideas?|||Your connection timeout setting is probably too low. Try adjusting it.
Andrew J. Kelly SQL MVP
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:DDE31E4A-8E88-4F76-90EE-6D04541D13C5@.microsoft.com...
> I am running into a situation where a program runs a stored procedure,
within that stored procedure a SQL statement is built and then executed
using the EXEC command. It looks like that when the generated statement
exceeds a certain time threshold, the stored procedure exits without any
warning.
> I know there is no warning because I have run profiler and I get a
statement start time but no end time. I have also logged every step in the
stored procedure and it just bails on me. Any ideas?|||Andrew, that is what I am thinking too. I have finally gotten a hold of the
code the developer uses to call this. Can any of you see anything wrong wi
th this code?
Dim cnTemp As Connection
Dim rsTemp As Recordset '--ADODB.Recordset
Dim strSQL As String
Set cnTemp = New ADODB.Connection
Set rsTemp = New ADODB.Recordset
cnTemp.ConnectionString = "Provider=SQLOLEDB;Data Source=PowerWare2000;Initi
al Catalog=PWProd; User ID=*******;Password=********;"
cnTemp.Open
strSQL = "Exec " & SP_CreatePrintJob & " " & JobTicketNr
rsTemp.CursorLocation = adUseClient
rsTemp.CursorType = adOpenStatic
rsTemp.LockType = adLockOptimistic
rsTemp.Open strSQL, cnTemp, , , adCmdText
"Andrew J. Kelly" wrote:

> Your connection timeout setting is probably too low. Try adjusting it.
> --
> Andrew J. Kelly SQL MVP
>
> "Dan" <Dan@.discussions.microsoft.com> wrote in message
> news:DDE31E4A-8E88-4F76-90EE-6D04541D13C5@.microsoft.com...
> within that stored procedure a SQL statement is built and then executed
> using the EXEC command. It looks like that when the generated statement
> exceeds a certain time threshold, the stored procedure exits without any
> warning.
> statement start time but no end time. I have also logged every step in th
e
> stored procedure and it just bails on me. Any ideas?
>
>|||You don't actually set the timeout and by default I believe it is set to 30
seconds. This should help with that:
http://www.aspfaq.com/show.asp?id=2066
But you also want to optimize these queries so they don't take so long to
begin with. Using stored procedures with parameters instead of adhoc sql is
a good start.
Andrew J. Kelly SQL MVP
"Daniel Avsec" <DanielAvsec@.discussions.microsoft.com> wrote in message
news:9F7A3F1B-3868-4B19-9A5F-63462C3D48A4@.microsoft.com...
> Andrew, that is what I am thinking too. I have finally gotten a hold of
the code the developer uses to call this. Can any of you see anything wrong
with this code?
> Dim cnTemp As Connection
> Dim rsTemp As Recordset '--ADODB.Recordset
> Dim strSQL As String
> Set cnTemp = New ADODB.Connection
> Set rsTemp = New ADODB.Recordset
> cnTemp.ConnectionString = "Provider=SQLOLEDB;Data
Source=PowerWare2000;Initial Catalog=PWProd; User
ID=*******;Password=********;"[vbcol=seagreen]
> cnTemp.Open
> strSQL = "Exec " & SP_CreatePrintJob & " " & JobTicketNr
> rsTemp.CursorLocation = adUseClient
> rsTemp.CursorType = adOpenStatic
> rsTemp.LockType = adLockOptimistic
> rsTemp.Open strSQL, cnTemp, , , adCmdText
> "Andrew J. Kelly" wrote:
>
the[vbcol=seagreen]sql

EXEC Query Performance

I am running into a situation where a program runs a stored procedure, within that stored procedure a SQL statement is built and then executed using the EXEC command. It looks like that when the generated statement exceeds a certain time threshold, the stored procedure exits without any warning.
I know there is no warning because I have run profiler and I get a statement start time but no end time. I have also logged every step in the stored procedure and it just bails on me. Any ideas?How are you executing the original SP, and have you tried
it in QA ?
>--Original Message--
>I am running into a situation where a program runs a
stored procedure, within that stored procedure a SQL
statement is built and then executed using the EXEC
command. It looks like that when the generated statement
exceeds a certain time threshold, the stored procedure
exits without any warning.
>I know there is no warning because I have run profiler
and I get a statement start time but no end time. I have
also logged every step in the stored procedure and it just
bails on me. Any ideas?
>.
>|||Your connection timeout setting is probably too low. Try adjusting it.
--
Andrew J. Kelly SQL MVP
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:DDE31E4A-8E88-4F76-90EE-6D04541D13C5@.microsoft.com...
> I am running into a situation where a program runs a stored procedure,
within that stored procedure a SQL statement is built and then executed
using the EXEC command. It looks like that when the generated statement
exceeds a certain time threshold, the stored procedure exits without any
warning.
> I know there is no warning because I have run profiler and I get a
statement start time but no end time. I have also logged every step in the
stored procedure and it just bails on me. Any ideas?|||You don't actually set the timeout and by default I believe it is set to 30
seconds. This should help with that:
http://www.aspfaq.com/show.asp?id=2066
But you also want to optimize these queries so they don't take so long to
begin with. Using stored procedures with parameters instead of adhoc sql is
a good start.
--
Andrew J. Kelly SQL MVP
"Daniel Avsec" <DanielAvsec@.discussions.microsoft.com> wrote in message
news:9F7A3F1B-3868-4B19-9A5F-63462C3D48A4@.microsoft.com...
> Andrew, that is what I am thinking too. I have finally gotten a hold of
the code the developer uses to call this. Can any of you see anything wrong
with this code?
> Dim cnTemp As Connection
> Dim rsTemp As Recordset '--ADODB.Recordset
> Dim strSQL As String
> Set cnTemp = New ADODB.Connection
> Set rsTemp = New ADODB.Recordset
> cnTemp.ConnectionString = "Provider=SQLOLEDB;Data
Source=PowerWare2000;Initial Catalog=PWProd; User
ID=*******;Password=********;"
> cnTemp.Open
> strSQL = "Exec " & SP_CreatePrintJob & " " & JobTicketNr
> rsTemp.CursorLocation = adUseClient
> rsTemp.CursorType = adOpenStatic
> rsTemp.LockType = adLockOptimistic
> rsTemp.Open strSQL, cnTemp, , , adCmdText
> "Andrew J. Kelly" wrote:
> > Your connection timeout setting is probably too low. Try adjusting it.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "Dan" <Dan@.discussions.microsoft.com> wrote in message
> > news:DDE31E4A-8E88-4F76-90EE-6D04541D13C5@.microsoft.com...
> > > I am running into a situation where a program runs a stored procedure,
> > within that stored procedure a SQL statement is built and then executed
> > using the EXEC command. It looks like that when the generated statement
> > exceeds a certain time threshold, the stored procedure exits without any
> > warning.
> > >
> > > I know there is no warning because I have run profiler and I get a
> > statement start time but no end time. I have also logged every step in
the
> > stored procedure and it just bails on me. Any ideas?
> >
> >
> >

EXEC Query Performance

I am running into a situation where a program runs a stored procedure, within that stored procedure a SQL statement is built and then executed using the EXEC command. It looks like that when the generated statement exceeds a certain time threshold, the s
tored procedure exits without any warning.
I know there is no warning because I have run profiler and I get a statement start time but no end time. I have also logged every step in the stored procedure and it just bails on me. Any ideas?
Funny you mention that Peter, I run the stored procedure in QA and it runs fine. The funny thing is that when the stored proc fails, the rest of the process completes. I have not been able to get access to the .Net code to view how it is executed yet.
"Dan" wrote:

> I am running into a situation where a program runs a stored procedure, within that stored procedure a SQL statement is built and then executed using the EXEC command. It looks like that when the generated statement exceeds a certain time threshold, the
stored procedure exits without any warning.
> I know there is no warning because I have run profiler and I get a statement start time but no end time. I have also logged every step in the stored procedure and it just bails on me. Any ideas?
|||Your connection timeout setting is probably too low. Try adjusting it.
Andrew J. Kelly SQL MVP
"Dan" <Dan@.discussions.microsoft.com> wrote in message
news:DDE31E4A-8E88-4F76-90EE-6D04541D13C5@.microsoft.com...
> I am running into a situation where a program runs a stored procedure,
within that stored procedure a SQL statement is built and then executed
using the EXEC command. It looks like that when the generated statement
exceeds a certain time threshold, the stored procedure exits without any
warning.
> I know there is no warning because I have run profiler and I get a
statement start time but no end time. I have also logged every step in the
stored procedure and it just bails on me. Any ideas?
|||Andrew, that is what I am thinking too. I have finally gotten a hold of the code the developer uses to call this. Can any of you see anything wrong with this code?
Dim cnTemp As Connection
Dim rsTemp As Recordset '--ADODB.Recordset
Dim strSQL As String
Set cnTemp = New ADODB.Connection
Set rsTemp = New ADODB.Recordset
cnTemp.ConnectionString = "Provider=SQLOLEDB;Data Source=PowerWare2000;Initial Catalog=PWProd; User ID=*******;Password=********;"
cnTemp.Open
strSQL = "Exec " & SP_CreatePrintJob & " " & JobTicketNr
rsTemp.CursorLocation = adUseClient
rsTemp.CursorType = adOpenStatic
rsTemp.LockType = adLockOptimistic
rsTemp.Open strSQL, cnTemp, , , adCmdText
"Andrew J. Kelly" wrote:

> Your connection timeout setting is probably too low. Try adjusting it.
> --
> Andrew J. Kelly SQL MVP
>
> "Dan" <Dan@.discussions.microsoft.com> wrote in message
> news:DDE31E4A-8E88-4F76-90EE-6D04541D13C5@.microsoft.com...
> within that stored procedure a SQL statement is built and then executed
> using the EXEC command. It looks like that when the generated statement
> exceeds a certain time threshold, the stored procedure exits without any
> warning.
> statement start time but no end time. I have also logged every step in the
> stored procedure and it just bails on me. Any ideas?
>
>
|||You don't actually set the timeout and by default I believe it is set to 30
seconds. This should help with that:
http://www.aspfaq.com/show.asp?id=2066
But you also want to optimize these queries so they don't take so long to
begin with. Using stored procedures with parameters instead of adhoc sql is
a good start.
Andrew J. Kelly SQL MVP
"Daniel Avsec" <DanielAvsec@.discussions.microsoft.com> wrote in message
news:9F7A3F1B-3868-4B19-9A5F-63462C3D48A4@.microsoft.com...
> Andrew, that is what I am thinking too. I have finally gotten a hold of
the code the developer uses to call this. Can any of you see anything wrong
with this code?
> Dim cnTemp As Connection
> Dim rsTemp As Recordset '--ADODB.Recordset
> Dim strSQL As String
> Set cnTemp = New ADODB.Connection
> Set rsTemp = New ADODB.Recordset
> cnTemp.ConnectionString = "Provider=SQLOLEDB;Data
Source=PowerWare2000;Initial Catalog=PWProd; User
ID=*******;Password=********;"[vbcol=seagreen]
> cnTemp.Open
> strSQL = "Exec " & SP_CreatePrintJob & " " & JobTicketNr
> rsTemp.CursorLocation = adUseClient
> rsTemp.CursorType = adOpenStatic
> rsTemp.LockType = adLockOptimistic
> rsTemp.Open strSQL, cnTemp, , , adCmdText
> "Andrew J. Kelly" wrote:
the[vbcol=seagreen]

Exclude Single User From Single Report

Here is my situation. I have a group set up in Active Directory for a group
of people that will share similar reports. The problem is that there is one
report that one particular user in that group doesn't need to see, but
everybody else in the group does need to see the report. Right now I have
the group added so everyone sees the report.
How can I exclude that one user from the report, but allow everyone else to
still see. This of course is without creating a new group of the same users,
but without this particular user. That would work, but seems as if there
should be another way.
Thanks!
BJbjkaledas,
I don't know how well you will like this answer, but I don't see many
other alternatives...
That report is currently inheriting its permissions from the folder it
is in. You can override that inheritance and specify item-level
permissions for that report (without affecting the other reports in
that folder).
Once you override the inheritance, you will have to delete the AD group
and add all of the group members EXCEPT for the user you want to
exclude. Unfortunately, that also means that you will have to manually
maintain those permissions when new members are added to the AD group.
Not optimal, but all I can think of.
Microsoft could solve this problem by allowing administrators to
explicity grant AND explicitly deny permissions to objects. All
permissions would stack, and in case of a conflict, it could default to
the most conservative (or limited) access level.
For example, if a user was in a group that could view and another group
that could execute, then the user could view and execute. However, if
the user was in a group that could execute and another group that could
"not execute", then it could default to the "not execute".
Maybe they will include that in the next release...
-Josh
bjkaledas wrote:
> Here is my situation. I have a group set up in Active Directory for a group
> of people that will share similar reports. The problem is that there is one
> report that one particular user in that group doesn't need to see, but
> everybody else in the group does need to see the report. Right now I have
> the group added so everyone sees the report.
> How can I exclude that one user from the report, but allow everyone else to
> still see. This of course is without creating a new group of the same users,
> but without this particular user. That would work, but seems as if there
> should be another way.
> Thanks!
> BJ|||Josh,
Thank you for your response. It seems easier to get one here than on the
forums. I had feared that was the only way around this issue. That is ok
though. Hopefully they will have exclusions in the next version. Hopefully
they will have exceptions in the Subscription schedules also!
Thanks again!
BJ
"Josh" wrote:
> bjkaledas,
> I don't know how well you will like this answer, but I don't see many
> other alternatives...
> That report is currently inheriting its permissions from the folder it
> is in. You can override that inheritance and specify item-level
> permissions for that report (without affecting the other reports in
> that folder).
> Once you override the inheritance, you will have to delete the AD group
> and add all of the group members EXCEPT for the user you want to
> exclude. Unfortunately, that also means that you will have to manually
> maintain those permissions when new members are added to the AD group.
> Not optimal, but all I can think of.
> Microsoft could solve this problem by allowing administrators to
> explicity grant AND explicitly deny permissions to objects. All
> permissions would stack, and in case of a conflict, it could default to
> the most conservative (or limited) access level.
> For example, if a user was in a group that could view and another group
> that could execute, then the user could view and execute. However, if
> the user was in a group that could execute and another group that could
> "not execute", then it could default to the "not execute".
> Maybe they will include that in the next release...
> -Josh
>
> bjkaledas wrote:
> > Here is my situation. I have a group set up in Active Directory for a group
> > of people that will share similar reports. The problem is that there is one
> > report that one particular user in that group doesn't need to see, but
> > everybody else in the group does need to see the report. Right now I have
> > the group added so everyone sees the report.
> >
> > How can I exclude that one user from the report, but allow everyone else to
> > still see. This of course is without creating a new group of the same users,
> > but without this particular user. That would work, but seems as if there
> > should be another way.
> >
> > Thanks!
> >
> > BJ
>

EXCEPT not working

TIA. Here is my situation: I have two tables that I need to find the perform an EXCEPT op on.

Table1: ToBeAddedCodes
CodeID - varchar(14)

Table2: ExistingCodes
ExistingCodeID - varchar(14)
DateIssued - datetime
Active - bit
...&c

I perform the following command, to no avail:

select CodeID
from ToBeAddedCodes
intersect
select ExistingCodeID
from ExistingCodes

Specifically, the following error appears:

Msg 156, Level 15, State 1, Line 40
Incorrect syntax near the keyword 'intersect'.

I don't understand what the issue is... Please help. Thanks.

The syntax should be valid if you are on SQL Server 2005, any version prior 2005 won′t support the Intersect keyword. I think thats your problem. Seems that you are connected to a SQL Server 2000 instance.

HTH, Jens SUessmeyer.

-
http://www.sqlserver2005.de
-

|||Or the database is running in 8.0 compatibility level.|||The new set operators will work in 80 and other compatibility modes also. So that is not the problem. User is either running on a older version of SQL Server or old CTP releases of SQL Server 2005. The set operators EXCEPT/INTERSECT was added late in the development cycle only.|||Please post the version of SQL Server (@.@.version) that you are running this code against.|||

Thanks for the replies ppl. Here's the requested information:

Information obtained from Help/About (about indicates "MS SQL Server 2005":

Microsoft SQL Server Management Studio 9.00.1399.00
Microsoft Analysis Services Client Tools 2005.090.1399.00
Microsoft Data Access Components (MDAC) 2000.085.1117.00 (xpsp_sp2_rtm.040803-2158)
Microsoft MSXML 2.6 3.0 5.0 6.0
Microsoft Internet Explorer 6.0.2900.2180
Microsoft .NET Framework 2.0.50727.42
Operating System 5.1.2600

Information obtained from "select @.@.version":

Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

|||

>> Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation

>> Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

The server version is 8.* which means it is SQL Server 2000. You are running SQL Server Management Studio though which ships with SQL Server 2005. The client doesn't have anything to do with the server language features. So you need to create your tables on a SQL Server 2005 server and try the EXCEPT query.

|||Did you happen to install SQL Server 2005 on a machine with an existing SQL Server 2000 installed? You might have mistaken the installation to be an upgrade (just like what I did a couple of months back )|||Well, I have no control over the infrastructure or anything in fact... I am just stepping in with the .NET development. Unfortunately, it does seem as if they are a bit out of date. Yes, on my client machine I am using 2005 but the database is in 2000.

Excel to Excel data transfer??Urgent

Hi,
This is my situation:
I have a report that is generated as Excel.The data for this report needs to
come from 2 sources
1.Database
2.Another Excel.
I was able to do the first i.e from the database.
How can I pull out data from another excel into this?
is there any reference material avaible for this.
Any help is appreciated.
Thanks,
SudhaHints: VBA ADO w/in Excel, not too difficult even for a novice. It's kinda like
winForms. When you get stuck, record a maco in Excel, then crack it open in VBA
and it will most likely lead you to many solutions.
If you want a user to initiate the Excel gathered data, then you can add a form
as a dialog and/or command button and/or options.
If you want it to be automated you can initiate on open, further you can even
write methods to pump that data into a subsequent Excel template for final
results were the data is formatted and no longer dynamic as a report should be.
HTH
JeffP....
"Sudha" <Sudha@.discussions.microsoft.com> wrote in message
news:A9154CC8-BE03-4363-9B6B-623FCEA35779@.microsoft.com...
> Hi,
> This is my situation:
> I have a report that is generated as Excel.The data for this report needs to
> come from 2 sources
> 1.Database
> 2.Another Excel.
> I was able to do the first i.e from the database.
> How can I pull out data from another excel into this?
> is there any reference material avaible for this.
> Any help is appreciated.
> Thanks,
> Sudha|||Hi,
Thanks for the reply.But I have to access Input Excel as a part of my report
generation so that the final report gets data from 2 sources database & Excel.
To Clarify further,How can I use Excel as my Datasource for my Report.
How should I do that?
Thanks,
Sudha
"JDP@.Work" wrote:
> Hints: VBA ADO w/in Excel, not too difficult even for a novice. It's kinda like
> winForms. When you get stuck, record a maco in Excel, then crack it open in VBA
> and it will most likely lead you to many solutions.
> If you want a user to initiate the Excel gathered data, then you can add a form
> as a dialog and/or command button and/or options.
> If you want it to be automated you can initiate on open, further you can even
> write methods to pump that data into a subsequent Excel template for final
> results were the data is formatted and no longer dynamic as a report should be.
> HTH
> JeffP....
> "Sudha" <Sudha@.discussions.microsoft.com> wrote in message
> news:A9154CC8-BE03-4363-9B6B-623FCEA35779@.microsoft.com...
> > Hi,
> >
> > This is my situation:
> >
> > I have a report that is generated as Excel.The data for this report needs to
> > come from 2 sources
> > 1.Database
> > 2.Another Excel.
> > I was able to do the first i.e from the database.
> > How can I pull out data from another excel into this?
> > is there any reference material avaible for this.
> >
> > Any help is appreciated.
> >
> > Thanks,
> > Sudha
>
>