exec statement

hi guys

maybe an easy one for you

in stored procedure I create follving select

@.cmd = 'select ' + @.column_name + 'from ticket_dump_datawarehouse '
execute (@.cmd)

problem is thant I want to gave return value from this select

something like
set @.return = execute(@.cmd)

but I recieve error

Incorrect syntax near the keyword 'execute'

Can I do that some other way?create temporary table out the and insert the result into that table
create table #t(colvalue <datatype>)

@.cmd = 'insert into #t (colvalue)
select ' + @.column_name + 'from ticket_dump_datawarehouse '
execute (@.cmd)

select * From #t

drop table #t

exec statement

hi guys

maybe an easy one for you

in stored procedure I create follving select

@.cmd = 'select ' + @.column_name + 'from ticket_dump_datawarehouse '
execute (@.cmd)

problem is thant I want to gave return value from this select

something like
set @.return = execute(@.cmd)

but I recieve error

Incorrect syntax near the keyword 'execute'

Can I do that some other way?return
which value?
Originally posted by mikosan
hi guys

maybe an easy one for you

in stored procedure I create follving select

@.cmd = 'select ' + @.column_name + 'from ticket_dump_datawarehouse '
execute (@.cmd)

problem is thant I want to gave return value from this select

something like
set @.return = execute(@.cmd)

but I recieve error

Incorrect syntax near the keyword 'execute'

Can I do that some other way?|||this is whole select.

sorry I forgot to finish it

@.cmd = 'select ' + @.column_name + 'from ticket_dump_datawarehouse where id = 10'

execute (@.cmd)

value from select statement = value in column @.column_name|||USE Northwind
GO

DECLARE @.sql varchar(8000), @.OrderId int

SELECT @.SQL = 'SELECT TOP 1 OrderId INTO myTable99 FROM Orders'

EXEC(@.SQL)

SELECT @.OrderId = OrderId FROM myTable99

SELECT @.OrderId
GO

DROP TABLE myTable99
GO

EXEC statement

Hi everyone,
What is EXEC statement ? What is the usage and purpose of it ?
It is really difficult to find ant resource about this keywords that's why I would like you to help me.

Thanks

EXEC is short for EXECUTE.

If you look in BOL (Books On Line) for the topic EXECUTE, you will find the info you're looking for.

/Kenneth

exec statement

Hi all

I used the

create procedure proc1

as

begin try

exec (

begin try

'select * from '+@.tablename1

if @.@.rowcount=0

raiseerror("hello",16,1)

'select * from '+@.tablename2

end try

begin catch

select error_message()

end catch)

end try

begin catch

end catch

in my stored procedure

beacause the name of tables not specified for me now

my questions:

1. Is there beter way than using exec (.... for running variable table name?

2. if i use the try catch in the exe statement with error_procedurename() i can not

get the name of the procedure (name1) and if any error happen in the exec statement the inner catch does not throw the error to the outer catch ?

and wheter i can get the error information of the inner try in the outer catch ?

3.if i want to raise error to catch condition with raiserror which severity and state is the best for my raiserror I know the severity should be between 11 and 19 but i dont know which number ?

thank you

The syntax of the EXEC statement is incorrect. To execute dynamic SQL, you need to pass a string or expression concatenating multiple strings. So if you do that it will work fine and you need just the try...catch at the SP level. However, why are you writing a generic SP like this that will select from any table? This has lot of problems in that the result set of the SP will change depending on the table and you need to account for that in the client-side. Additionally, you will be subjected to SQL injection attacks if you form the statement incorrectly and you need to grant SELECT permission on all the tables that you will potentially use for this to work for all users. Your raiserror statement is fine. You can trap it in the client.|||? What error are you trying to catch? The table not existing? If so, why not check first (SELECT * FROM sys.tables WHERE name=...)? That would probably make your error handling a bit simpler. There is no "better" way to handle having a table name passed in, but the question you might want to ask yourself is why you need to pass in a table name at all? If your db truly has so many tables that can be handled exactly the same way that you need to be able to pass in the name, perhaps you should generalize them into a single table and thereby eliminate the dynamic SQL altogether. -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Asal@.discussions.microsoft..com> wrote in message news:ed6e1c30-e9d8-4145-9634-4490b9e66a30@.discussions.microsoft.com... Hi all I used the create procedure proc1 as begin try exec ( begin try 'select * from '+@.tablename1 if @.@.rowcount=0 raiseerror("hello",16,1) 'select * from '+@.tablename2 end try begin catch select error_message() end catch) end try begin catch end catch in my stored procedure beacause the name of tables not specified for me now my questions: 1. Is there beter way than using exec (.... for running variable table name? 2. if i use the try catch in the exe statement with error_procedurename() i can not get the name of the procedure (name1) and if any error happen in the exec statement the inner catch does not throw the error to the outer catch ? and wheter i can get the error information of the inner try in the outer catch ? 3.if i want to raise error to catch condition with raiserror which severity and state is the best for my raiserror I know the severity should be between 11 and 19 but i dont know which number ? thank you|||

thanks for your replying

but what i want to do is not exactly like this code ,it is only like that structure,I want to execute the exec dynamic statement for 12 table that the statements of inside the exe are same for all 12 tables but the name of the tables are defined at run time out of the exec (in proc1) and the statements in the exec are not only select statement they are multy sql statements that get their variables from proc1.

thanks

EXEC statement

Hi, I am facing a problem here. I am trying to make a stored procedure which accepts an input. The input is a table name within the database. The procedure itself then will make an after update trigger for the table. The purpose of making this stored procedure is because the table keeps changing (columns can be added or deleted) and I don't want to make the trigger manually everytime the table changes, instead I want to execute the stored procedure by passing the table's name and the procedure will create the trigger for me. The problem is sql server 2005 has limited the length of any variable to 8000. The create trigger statement can be longer than that. So using a variable to store the create trigger statement and then executing that variable is not an option. That is why I have inserted the statement to be executed into a column in a temp table. Now how do I execute that statement? I have tried this:

EXEC(SELECT QRY FROM temp_Update)

Qry is the column name which holds the create trigger statement. temp_Update is the temporary table. But if I run it, it will give this error:

Msg 156, Level 15, State 1, Line 123
Incorrect syntax near the keyword 'SELECT'.
Msg 102, Level 15, State 1, Line 123
Incorrect syntax near ')'.

Can anybody tell me how to execute a query which is place in a column in a table? If we can't do this, then what is the workaround, maybe how to have a variable that can hold more than 8000 characters? Any suggestion is greatly appreciate it. Thanks.

This sounds quite 'unwieldy' to me. And smells of a bad data design.

But sometimes you have to live with what you inherit.

Consider having the TRIGGER execute a Stored Procedure.

The TRIGGER never changes.

Then you only have to Alter the Stored Procedure.

And to get past the 8k limit, you can do this:

EXECUTE (@.Variable1 + @.Variable2 + etc.)

|||

I agree with Arnie, since you are using SQL Server 2005, you can change the datatype of the variable from varchar(8000) to varchar(max).

I noticed your example,

Exec(Select Qry From temp_Update)

It is not correct, On exec you have to pass the varchar string or variable as follow as

Exec('Select Qry From temp_Update')

Or

Declare @.SQL as Varchar(max)

Set @.SQL = 'Select Qry From temp_Update'

Exec(@.SQL)

|||Hi Arnie,

Thanks for the quick reply. This is not the case of bad data design. See, we can not predict if in the future we have to add another column to the table. What this stored procedure do is to create a trigger whenever a new column is introduced. The newly created trigger is supposed to detect any changes/update made to that table, i.e finding which column has changed, and then insert the "before update" value and "after update" value to a log table. That way we can have a full history of the table in the log table. It will be a disaster if every time a new column added to the table we have to write the "after update" trigger to incorporate the newly added column. If this can be done then all we need to do is call the stored procedure, pass the name of the table and the trigger will be created automatically.|||

Manivannan.D.Sekaran wrote:

I agree with Arnie, since you are using SQL Server 2005, you can change the datatype of the variable from varchar(8000) to varchar(max).

I noticed your example,

Exec(Select Qry From temp_Update)

It is not correct, On exec you have to pass the varchar string or variable as follow as

Exec('Select Qry From temp_Update')

Or

Declare @.SQL as Varchar(max)

Set @.SQL = 'Select Qry From temp_Update'

Exec(@.SQL)

Hi Manivannan,

If you do it that way, the result will be the content of column Qry. What I want to do is to execute the content of Qry. Can it be done?|||

Yes you can,

Code Snippet

Declare @.SQL as Varchar(max)

Declare @.Qry as Varchar(max)

Set @.Qry = 'Col1, Col2, Col3'

Set @.SQL = 'Select ' + @.Qry + ' From temp_Update'

Exec(@.SQL)

|||

Perhaps a better explication:

DECLARE @.SQL nvarchar(max)

SELECT @.SQL = Qry FROM Temp_Update WHERE {criteria}

EXECUTE( @.SQL )

|||Hey,

Thanks again for the quick reply. I have finally found the answer and now I can make the trigger automatically just by calling the stored procedure. And indeed it is using varchar(max) as the solution. That is why I will give the credit to Manivannan. Thanks again to you and Arnie.sql

exec sql

I need help understanding the syntax of the "exec sql" statement.

i am looking at code that build an sql string such as

sql="exec SOMETHING Session("id")"

or something like that.

then, there is

conn.execute(sql)

My question is the "SOMETHING" in the sql statement...is what? I know it is user defined (object or variable or such), but what exactly is it? i look through the rest of the code and don;'t see SOMETHING defined elsewhere.

i am not sure if i am asking the question right. i don't understand what the SOMETHING is doing, or why it is there.

in particular, the code i am examining is

sql="exec SurveyDelete "&"'" & Session("StudentID") & " ' "
conn.execute(sql)

i understand the this statement will delete a record, but how does it handle "SurveyDelete", how does it know what the is when it is not defined anywhere else in the code?I would wager that SurveyDelete is the name of a stored procedure in the database. Have a look there.|||i bet your right. thanks.

exec sp_executesql vs. sp_executesql and performance

This is a odd problem where a bad plan was chosen again and again, but
then not.

Using the profiler, I identified an application-issued statement that
performed poorly. It took this form:

exec sp_executesql N'SELECT col1, col2 FROM t1 WHERE (t2= @.Parm1)',
N'@.Parm1 int', @.Parm1 = 8609

t2 is a foreign key column, and is indexed.

I took the statement into query analyzer and executed it there. The
query plan showed that it was doing a scan of the primary key index,
which is clustered. That's a bad choice.

I then fiddled with it to see what would result in a good plan.

1) I changed it to hard code the query value (but with the parm
definition still in place. )
It performed well, using the correct index.
Here's how it looked.
exec sp_executesql N'SELECT cbord.cbo1013p_AZItemElement.AZEl_Intid AS
[Oid], cbord.cbo1013p_AZItemElement.incomplete_flag AS [IsIncomplete],
cbord.cbo1013p_AZItemElement.traceflag AS [IsTraceAmount],
cbord.cbo1013p_AZItemElement.standardqty AS [StandardAmount],
cbord.cbo1013p_AZItemElement.Uitem_intid AS [NutritionItemOid],
cbord.cbo1013p_AZItemElement.AZeldef_intid AS [AnalysisElementOid] FROM
cbord.cbo1013p_AZItemElement WHERE (Uitem_intid= 8609)', N'@.Parm1 int',
@.Parm1 = 8609

After doing this, re-executing the original form still gave bad
results.

2) I restored the use of the parm, but removed the 'exec' from the
start.
It performed well.

After that (surprise!) it also performed well in the original form.

What's going on here?elRoyFlynn (lit@.twcny.rr.com) writes:
> t2 is a foreign key column, and is indexed.
> I took the statement into query analyzer and executed it there. The
> query plan showed that it was doing a scan of the primary key index,
> which is clustered. That's a bad choice.

Sometimes it is, sometimes it's not. This is a delicate choice that
the optimizer have to make. Non-clustered index + bookmark lookup, or
clustered index scan? The first strategy fantastic if there are only
a few hits, but disastrous if you hit, say, 30% of the rows. Many page
will be accessed more than once, and it will be a lot slower than a CI
scan.

> 2) I restored the use of the parm, but removed the 'exec' from the
> start.
> It performed well.
> After that (surprise!) it also performed well in the original form.

Probably parameter sniffing. SQL Server caches the query plan for the
query, and the cached plan is built from the parameter value that
query first was run for. That value may have been handled best with
a CI scan.

But it might also be that the statistics were poor initially, and caused
SQL Server to make an incorrect estimate. But SQL Server has auto-
statistics, so it could be that statistics were updated, and the plan
was flushed, and a new plan built.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||In this case, the "bad plan" clearly is: a 6-second response time vs.
a sub-second response when the "best" plan is used.

Problem is, the application generated this query in the "exec sp..."
form hundreds of times, getting the bad result each time. It was while
the app was still running that I worked in query analyzer. I executed
the problem sql multiple times, duplicating the bad result, before
trying it the other way. The first time I did it the other way, it
worked well, which also immediately fixed the application. Coincidence
, unrelated to the different execution form, that just at that moment
mss figured out that the other plan was better? I'm skeptical. I
think that something about 'exec sp_.." vs. plain "sp_..." had an
unintended effect.

But thanks for the response, I'll think about it.|||elRoyFlynn (lit@.twcny.rr.com) writes:
> In this case, the "bad plan" clearly is: a 6-second response time vs.
> a sub-second response when the "best" plan is used.

It should be admitted that this is quite common. The optimizer seems to
be overly conservative with regards to non-clustered indexes.

> Coincidence , unrelated to the different execution form, that just at
> that moment mss figured out that the other plan was better? I'm
> skeptical. I think that something about 'exec sp_.." vs. plain "sp_..."
> had an unintended effect.

It could be that the misisng "exec" triggered a recompile of the query,
but from what I know about how the cache works, I can't really see that
it would matter.

What could matter, though, is whether you changed somehting inside
the query. With regards to single queries, the cache is both case-
and space-sensitive. (But the part "EXEC sp_executesql" is not in
the cache, only the first argument to sp_executesql is.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

Exec SP_Executesql @Selectstring

Hi,
How do I assign the select statement result to a variable?
Select @.Myvariable = Exec SP_Executesql @.Selectstring
It donesn't work.
Any ideas?
Thanksanswered in another newsgroup - please don't post the same message
independently to multiple newsgroups.

Exec SP_Executesql @Selectstring

Hi,
How do I assign the select statement result to a variable?
Select @.Myvariable = Exec SP_Executesql @.Selectstring
It donesn't work.
Any ideas?
Thanksanswered in another newsgroup - please don't post the same message
independently to multiple newsgroups.

Exec SP_Executesql @Selectstring

Hi,
How do I assign the select statement result to a variable?
Select @.Myvariable = Exec SP_Executesql @.Selectstring
It donesn't work.
Any ideas?
Thanks
answered in another newsgroup - please don't post the same message
independently to multiple newsgroups.

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]

EXEC and Error Handling

I am building a Bulk Insert statement dynamically passing in filename location, etc.

I have coded error handling via the @.@.ERROR. To test the error handling, I am forcing the file that is bulk inserted to be missing.

The statement is created in a declared variable, IE @.SQL:

EXEC(@.SQL)

IF @.@.ERROR <> 0

BEGIN
GOTO ErrorHandler
END

Since the file is missing it causes an error, and the Stored Procedure gives the

Server: Msg 4860, Level 16, State 1, Line 1 Could not find the file etc.
and aborts aborts the whole stored proc at that point. In other words the error handling IF @.@.ERROR doesn't trap the error and send the process to the error handler routine.

Is there anyway to get the error back from the EXEC, or maybe something has to be set to trap the error because it is fatal? Because of the way the Bulk Insert statement is created dynamically, it appears you have to EXEC the statement once it is built (EXEC(@.SQL)).

Any help would be appreciated.

Barryyou can do something like :

begin Transaction exec1
-- your sql stmts
If @.@.ERROR > 0
 begin
 RAISERROR('Error in sp',16,1)
 ROLLBACK TRANSACTION exec1
 RETURN 99
END
 COMMIT TRANSACTION exec1

sql

Exec a StoredProc from in a Select statement that returns mult

Tony,
The called sp is too big to put in the SELECT. It does a lot, creates and
used temp tables. . . I don't think I would want to go that route.
Because it uses temp tables, I couldn't make it a udf. I have not used a
scalar function. How does it differ from a udf? Can I create and use temp
tables in it?
Thanks,
Steve
"Tony Sellars" wrote:
> Sounds like you have a couple of options. One would be to incorporate the
> logic from the procedure you are calling into the select statement you
> mentioned. Another would be to create a scalar function which returns the
> output1 value you are looking at and use it in place of the subselect in y
our
> sample. The function option would most likely not perform as fast as
> incorporating the logic directly.
> HTH
> --Tony
> "SteveInBeloit" wrote:
>Scalar functions are simply a type of udf (take a look at bol under 'create
function' for all the details). Given what you are saying here are some
other options. The functions can use table variables so it may be possible
to write your procedure as a function depending on what your code does (ther
e
are some coding limitations within functions). Another option would be to
rewrite your spMyProc to take a set of values (say in an xml parameter) and
return a result set which either matches what you would ultimately select ou
t
(and not write the calling procedure) or that could be used in an INSERT
EXEC statement to trap the results in a temp table and be used in the final
join.
Hope this does more than just muddy the water for you.
--Tony
"SteveInBeloit" wrote:
> Tony,
> The called sp is too big to put in the SELECT. It does a lot, creates and
> used temp tables. . . I don't think I would want to go that route.
> Because it uses temp tables, I couldn't make it a udf. I have not used a
> scalar function. How does it differ from a udf? Can I create and use tem
p
> tables in it?
> Thanks,
> Steve
> "Tony Sellars" wrote:
>

Exec @string

I am trying to do an insert statement utilizing a variable string.

something like:

Set @.cString = 'SELECT top 10 *
FROM OPENDATASOURCE(
' + char(39) + 'SQLOLEDB' + char(39) + ',' + char(39) +
'Data Source=' + @.lServer + ';User ID=' + @.user + ';Password=' + @.pword + char(39) + '
).myServer..

Insert into #Temp_table (field1, field2)
select exec @.cString

--What is the syntax for this?as answered in previous post. you can like so:

insert tb
exec(@.sql)|||When I do something like:

Insert into #tACCOUNT (ACCOUNTID, ACCOUNT, TYPE)
exec (@.cString)

I get:

MSDTC on server 'myServer' is unavailable.|||dtc is needed to ensure data integrity between 2 sites. when you do any dml, the transaction is implicitly promoted to a distributed transaction.

EXEC / Sp_executesql with Dynamic SQL string exceeding 4000 - SQL Server 2000

This is regarding the sp_executesql and the sql statement parameter, in processing a dynamic SQL on SQL Server 2000, in my stored procedure.

I have my SQL string exeeding more than 4000 characters.
The sp_executesql expects its parameters to be declared as nvarchar/ntext.
ntext cannot be declared for a local variable and nvarchar has a maximum limit of 4000 characters.

The following was the original approach.
===============
DECLARE @.sql nvarchar(4000),

--
select @.sql=@.sql+'select.................'
--
--
INSERT INTO #Temp
EXEC sp_executesql @.SQL,@.paramlist

===============================

The above sql was split into two sql variables and tried in the following way.
==============================
DECLARE @.sql nvarchar(4000), @.sql1 nvarchar(4000),@.paramlist nvarchar(4000)

--
select @.sql=@.sql+'select.................'
select @.sql1=@.sql1+'from table1, table2.........'
--
--

INSERT INTO #Temp
exec('EXEC sp_executesql ''' + @.sql + @.sql1 + ''',@.paramlist')
====================

However, it compiles correctly but during the execution, the error indicates as syntax error.
I ran the resulting SQL string (@.sql + @.sql1) separately, and it runs fine without any syntax errors returning rows, but when execute through
sp_executesql, it gives me error

Appreciate if anyone can suggest a workaround for this situation.

Try the other version of executing dynamic SQL the simple one....

EXEC(@.sql + @.Sql1)

Because i have used this 2 one place where my Dynamic SQl even exceeds 1lakh characters....|||

Thanks Mandip. It's working. Appreciate your help.

Excluding rows on a table while importing

I'm using DTSWizard to import a table from my main database to Temp.

This is the SQL statement...

CREATE TABLE [tempdb].[dbo].[xlaANLsubscribers] (
[subscriberid] int NOT NULL,
[pwd] varchar(255),
[name] varchar(255),
[deliveryformat] int,
[email] varchar(255),
[gender] varchar(255),
[phone] varchar(255),
[country] varchar(255),
[city] varchar(255),
[state] varchar(255),
[zip] varchar(255),
[address] varchar(1000),
[dateregistered] varchar(50),
[bounces] int
)

What I'd like to do for example, is exclude the first 5,000 rows, and import the rest.

Should I be using something other than DTSWizard, and it there something that can be added to the statement above telling it to start at a specified row?

This is probably fairly simple, but I'm new at this and I'd sure appreciate the help.

Thanks,

Bill

Do you have to use the DTSWizard?

If not, BULK INSERT has a FIRSTROW parameter.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

Hi Wesley,

I'm really new at this so I'm not sure how to use BULK INSERT, is that a statement that would be used in a Query? Maybe I'm expecting to much from an import/export tool like DTSWizard to get so particular.

Thanks,

Bill

|||

It is indeed a TSQL statement. The Books Online has excellent documentation and examples.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||YOu can also use the DTS wizard with the query option of "skip rows" Normally this is ment for skipping rows that are part of the metadata like column headers etc. But this can be also used in your situation for skipping (non-)relevant data rows.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Hi Jens,

In the DTS wizard I don't see anything relating to "skip rows", is this something that would run in the

Create Table Query?

Thanks,

Bill

|||OK, I assumed that you are using a text provider for the insert, which format is the data of or which data source are you querying ?

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Hi Jens, thanks for getting back with me.

You mentioned "skip rows" using DTS wizard, and now I'm a bit confused... is there a feature in the DTS wizard where I can skip rows in a table when importing to another table?

Thanks,

Bill

|||

Hi Jens,

What I'm doing in this particular step is... I'm using DTS Wizard to copy a table from my Main Database to Temp. I went thru DTS Wizard and I see that if I was importing a FlatFile into SQL Server, on top of the Preview it allows you to Skip Rows, and this is what you were probably talking about.

Being that there isn't a Skip Row feature when copying a regular table from a database to Temp, but it does have the Create Table statement under Edit Mapping/Edit SQL, is there something that can be added to that statement to Skip Rows in the source table?.

Thanks,

Bill

|||Yes, that depends on the version you are using. In Sql Server 005 you can use the ROW_NUMBER() function to filter out appropiate rows. In SQL Server 2000 you would have to use another approach.

Jens K. Suessmeyer

http://www.sqlserver2005.de
|||

Hi Jens, thanks for getting back with me.

I'm using SQL Server 2005 Express. This is what I have currently under Edit Mapping/Edit SQL...

CREATE TABLE [tempdb].[dbo].[xlaANLsubscribers] (
[subscriberid] int NOT NULL,
[pwd] varchar(255),
[name] varchar(255),
[deliveryformat] int,
[email] varchar(255),
[gender] varchar(255),
[phone] varchar(255),
[country] varchar(255),
[city] varchar(255),
[state] varchar(255),
[zip] varchar(255),
[address] varchar(1000),
[dateregistered] varchar(50),
[custom1] varchar(255),
[custom2] varchar(255),
[custom3] varchar(255),
[custom4] varchar(255),
[custom5] varchar(255),
[bounces] int
)

I'm really new at this which you can probably tell... do you think you can let me know the code I need to add to the above to exclude certain rows?

Thanks,

Bill

|||

You can use this as the source of the table (instead of using the table direct)

SELECT

*

FROM

(

SELECT

ROW_NUMBER() OVER (ORDER BY [dateregistered]) AS ROWCOUNTER

[subscriberid] ,

[pwd],

[name] ,

[deliveryformat] ,

[email] ,

[gender] ,

[phone],

[country] ,

[city] ,

[state] ,

[zip] ,

[address] ,

[dateregistered] ,

[custom1] ,

[custom2] ,

[custom3] ,

[custom4] ,

[custom5] ,

[bounces]

FROM SomeTable

) SubQuery

WHERE ROWCOUNTER>5000

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Hi Jens, thanks for responding to this. I ran your code in DTS Wizard/Edit Mapping/Edit SQL and I received the error below.

Also, I'm not sure what you meant when you said... "You can use this as the source of the table (instead of using the table direct)." Was I correct in running this in DTS Wizard?

Operation stopped...

- Initializing Data Flow Task (Success)

- Initializing Connections (Success)

- Setting SQL Command (Success)

- Setting Source Connection (Success)

- Setting Destination Connection (Success)

- Validating (Success)

- Prepare for Execute (Stopped)

- Pre-execute (Stopped)

- Executing (Error)
Messages
* Error 0xc002f210: Preparation SQL Task: Executing the query "SELECT
*
FROM
(
SELECT
ROW_NUMBER() OVER (ORDER BY [dateregistered]) AS ROWCOUNTER
[subscriberid] ,
[pwd],
[name] ,
[deliveryformat] ,
[email] ,
[gender] ,
[phone],
[country] ,
[city] ,
[state] ,
[zip] ,
[address] ,
[dateregistered] ,
[custom1] ,
[custom2] ,
[custom3] ,
[custom4] ,
[custom5] ,
[bounces]
FROM xlaANLsubscribers
) SubQuery
WHERE ROWCOUNTER>90091
" failed with the following error: "Incorrect syntax near 'subscriberid'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
(SQL Server Import and Export Wizard)

- Copying to [tempdb].[dbo].[xlaANLsubscribers] (Stopped)

- Post-execute (Stopped)

- Cleanup (Stopped)

|||Well just a comma missing

SELECT

*

FROM

(

Code Snippet

SELECT

ROW_NUMBER() OVER (ORDER BY [dateregistered]) AS ROWCOUNTER,

[subscriberid] ,

[pwd],

[name] ,

[deliveryformat] ,

[email] ,

[gender] ,

[phone],

[country] ,

[city] ,

[state] ,

[zip] ,

[address] ,

[dateregistered] ,

[custom1] ,

[custom2] ,

[custom3] ,

[custom4] ,

[custom5] ,

[bounces]

FROM xlaANLsubscribers

) SubQuery

WHERE ROWCOUNTER>90091

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks for getting back with me Jens.

I ran your code in DTS Wizard.

This is the error I received...

Operation stopped...

- Initializing Data Flow Task (Success)

- Initializing Connections (Success)

- Setting SQL Command (Success)

- Setting Source Connection (Success)

- Setting Destination Connection (Success)

- Validating (Success)

- Prepare for Execute (Stopped)

- Pre-execute (Stopped)

- Executing (Error)

Messages

Error 0xc002f210: Preparation SQL Task: Executing the query "SELECT
*
FROM
(

SELECT
ROW_NUMBER() OVER (ORDER BY [dateregistered]) AS ROWCOUNTER,
[subscriberid] ,
[pwd],
[name] ,
[deliveryformat] ,
[email] ,
[gender] ,
[phone],
[country] ,
[city] ,
[state] ,
[zip] ,
[address] ,
[dateregistered] ,
[custom1] ,
[custom2] ,
[custom3] ,
[custom4] ,
[custom5] ,
[bounces]
FROM xlaANLsubscribers
) SubQuery
WHERE ROWCOUNTER>90091
" failed with the following error: "Invalid object name 'xlaANLsubscribers'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
(SQL Server Import and Export Wizard)

- Copying to [tempdb].[dbo].[xlaANLsubscribers] (Stopped)

- Post-execute (Stopped)

- Cleanup (Stopped)

Excluding part of select statement if no data is returned in results

I have a query that returns results based on information in several tables. The problem I am having is that is there are no records in the one table it doesn't return any information at all. This table may not have any information initially for the employees so I need to show results whether or not there is anything in this one table.

Here is my select statement:

SELECT employee.emp_id,DATEDIFF(mm, employee.emp_begin_accrual,GETDATE()) * employee.emp_accrual_rate - (SELECTSUM(request_duration)AS daystakenFROM request)AS daysleft, employee.emp_lname +', ' + employee.emp_fname +' ' + employee.emp_minitial +'.'AS emp_name, department.department_name, location.location_nameFROM employeeINNERJOIN requestAS request_1ON employee.emp_id = request_1.emp_idINNERJOIN departmentON employee.emp_department = department.department_idINNERJOIN locationON department.department_location = location.location_idGROUP BY employee.emp_id, employee.emp_begin_accrual, employee.emp_accrual_rate, employee.emp_fname, employee.emp_minitial, employee.emp_lname, department.department_name, location.location_nameORDER BY location.location_name, department.department_name, employee.emp_lname

The section below is the part that may or may not contain information:

SELECT (SELECTSUM(request_duration)AS daystakenFROM request)AS daysleft

So I need it to return results whether this sub query has results or not. Any help would be greatly appreciated!!!

TIA

BUMP... Somebody...|||

Okay, I tried adding the ISNULL to the statement, but I think the problem is because until a request has been put in there is nothing linking the employee table for the JOIN on the request table. When they put in a request it adds an entry to the request table for them. Up till that point, there will be nothing matching the two tables.

Here is my statement as it stands now. Is there anyway to get the results to show if the INNERJOIN isn't finding any results in the request table?

SELECT employee.emp_id,DATEDIFF(mm, employee.emp_begin_accrual,GETDATE()) * employee.emp_accrual_rate - (SELECTSUM(ISNULL(request_duration,'0'))AS daystakenFROM request)AS daysleft, employee.emp_lname +', ' + employee.emp_fname +' ' + employee.emp_minitial +'.'AS emp_name, department.department_name, location.location_nameFROM employeeINNERJOIN requestAS request_1ON employee.emp_id = request_1.emp_idINNERJOIN departmentON employee.emp_department = department.department_idINNERJOIN locationON department.department_location = location.location_idGROUP BY employee.emp_id, employee.emp_begin_accrual, employee.emp_accrual_rate, employee.emp_fname, employee.emp_minitial, employee.emp_lname, department.department_name, location.location_nameORDER BY location.location_name, department.department_name, employee.emp_lname

I know it seems I am just talking to myself at this point, but I would LOVE for someone to join my conversation. Thanks in advance for any help!!!

|||

Okay, I figured it out. Had to switch my query to a LEFT OUTER JOIN.

SELECT employee.emp_id,DATEDIFF(mm, employee.emp_begin_accrual,GETDATE()) * employee.emp_accrual_rate - (SELECTSUM(ISNULL(request_duration,'0'))AS daystakenFROM request)AS daysleft, employee.emp_lname +', ' + employee.emp_fname +' ' + employee.emp_minitial +'.'AS emp_name, department.department_name, location.location_nameFROM employeeLEFTOUTER JOIN requestAS request_1ON employee.emp_id = request_1.emp_idINNERJOIN departmentON employee.emp_department = department.department_idINNERJOIN locationON department.department_location = location.location_idGROUP BY employee.emp_id, employee.emp_begin_accrual, employee.emp_accrual_rate, employee.emp_fname, employee.emp_minitial, employee.emp_lname, department.department_name, location.location_nameORDER BY location.location_name, department.department_name, employee.emp_lname

excluding fields in SELECT statement

Hi,

Is there a way to exclude fields in a query other than just including
the ones you want. If there are 20 fields and you want to see all but
3, it would be a lot easier to exclude the 3.

Thanks"ric" <rtavert@.yahoo.com> wrote in message
news:1130086527.567870.112960@.z14g2000cwz.googlegr oups.com...
> Hi,
> Is there a way to exclude fields in a query other than just including
> the ones you want. If there are 20 fields and you want to see all but
> 3, it would be a lot easier to exclude the 3.
> Thanks

No. It isn't difficult to list just the columns you want. In Query Analyzer
you can drag the column list from the Object Browser pane so no typing is
required.

Note that it is also good practice to avoid using SELECT * in production
code. Using * instead of listing just the required columns is not only
inefficient, it also impacts the reliability and maintainability of your
code.

--
David Portas
SQL Server MVP
--|||No, there is no Transact-SQL syntax for this. However, you can use Query
Analyzer to generate a SELECT statement for the desired view or table and
then remove the unneeded columns.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"ric" <rtavert@.yahoo.com> wrote in message
news:1130086527.567870.112960@.z14g2000cwz.googlegr oups.com...
> Hi,
> Is there a way to exclude fields in a query other than just including
> the ones you want. If there are 20 fields and you want to see all but
> 3, it would be a lot easier to exclude the 3.
> Thanks|||>> Is there a way to exclude fields [sic] in a query other than just including the ones you want. If there are 20 fields [sic] and you want to see all but 3, it would be a lot easier to exclude the 3. <<

Short answer: No. Have you ever seen such a thing in any other
programming language? The closest thing I know is FILLER in Cobol.

Rows are not records; fields are not columns; tables are not files. It
is not easier; it is dangerous. If the base table is altered, your
syntax would not see the changes and would screw up. A list of column
name is easy to generate from the schema information tables with a
tool.|||Great!

Forgot to mention I was looking for a faster way to work in the Query
Analyzer.

>>you can use Query Analyzer to generate a SELECT statement for the desired view...

Is this something other than dragging over columns into the SELECT
statement you're building?

Thanks,
Ric|||ric (rtavert@.yahoo.com) writes:
>>>you can use Query Analyzer to generate a SELECT statement for the desired
>>>view...
> Is this something other than dragging over columns into the SELECT
> statement you're building?

I guess this is what Dan had in mind. Myself, when I need to do this, I
usually do a SELECT * FROM tbl WHERE 1 = 0 in text mode, copy and paste
the headers into Textpad, where I have a macro so I with a keypress can
replace the spaces with commas, and then cut and paste back.

There is a new product PromptSQL which claims to provide intellisense to
Query Analyzer. Since I am not fond of intellisense myself, I have not
tried it. But somehing that expands a * would be a great thing for such a
tool - maybe they have it?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> I guess this is what Dan had in mind.

I used to use that method in the old days (pre- SQL 2000) but I was
referring to the following technique:

1) right-click on the desired table in the QA object browser

2) select Script Object to New Window as--> Select

3) remove unwanted columns

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns96F9F4031F9D6Yazorman@.127.0.0.1...
> ric (rtavert@.yahoo.com) writes:
>>>>you can use Query Analyzer to generate a SELECT statement for the
>>>>desired
>>>>view...
>>
>> Is this something other than dragging over columns into the SELECT
>> statement you're building?
> I guess this is what Dan had in mind. Myself, when I need to do this, I
> usually do a SELECT * FROM tbl WHERE 1 = 0 in text mode, copy and paste
> the headers into Textpad, where I have a macro so I with a keypress can
> replace the spaces with commas, and then cut and paste back.
> There is a new product PromptSQL which claims to provide intellisense to
> Query Analyzer. Since I am not fond of intellisense myself, I have not
> tried it. But somehing that expands a * would be a great thing for such a
> tool - maybe they have it?
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Expanding '*' is something people have asked for, and its on the list,
but not there yet -
http://www.promptsql.com/known_bugs...nhancements.htm

Regards,
Damian (PromptSQL developer)

Erland Sommarskog wrote:
> ric (rtavert@.yahoo.com) writes:
> >>>you can use Query Analyzer to generate a SELECT statement for the desired
> >>>view...
> > Is this something other than dragging over columns into the SELECT
> > statement you're building?
> I guess this is what Dan had in mind. Myself, when I need to do this, I
> usually do a SELECT * FROM tbl WHERE 1 = 0 in text mode, copy and paste
> the headers into Textpad, where I have a macro so I with a keypress can
> replace the spaces with commas, and then cut and paste back.
> There is a new product PromptSQL which claims to provide intellisense to
> Query Analyzer. Since I am not fond of intellisense myself, I have not
> tried it. But somehing that expands a * would be a great thing for such a
> tool - maybe they have it?
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||I said expanding '*' was on the list of things to be implemented --
we've just released PromptSQL 1.2 Beta one, and this was one of the new
features.

You can now type
SELECT *[TAB] FROM Orders, Customers
and the * will be expanded to insert all the columns in Orders and
Customers, prefixed by appropriate aliases. The separator is
customizable.

Or you can type:
SELECT o.*[TAB] FROM Orders o, Customers and the o.* will be replaced
by a list of all Orders columns, prefixed by "o."

Regards,
Damian (PromptSQL developer)

http://www.promptsql.com