EXEC Statements in a sql script

Thanks for the response. Didn't help however can tell you what is
happening now. I do an alter to create a new column. Put it in a
transaction and commit it. Then the next transaction I do an update to
the newly created column and it complains it can't find the column. If
I run all this manually it's fine. Could it be an issue with the speed
that the script is running that SQL Server, even though I committed
between alter and update, still is not done creating the tables or
something?
Thanks.
JRJR (jriker1@.yahoo.com) writes:
> Thanks for the response. Didn't help however can tell you what is
> happening now. I do an alter to create a new column. Put it in a
> transaction and commit it. Then the next transaction I do an update to
> the newly created column and it complains it can't find the column. If
> I run all this manually it's fine. Could it be an issue with the speed
> that the script is running that SQL Server, even though I committed
> between alter and update, still is not done creating the tables or
> something?
No, speed has nothing to do with it.
If you do:
ALTER TABLE tbl ADD newcol int
UPDATE tbl
SET newcol = 91
this will fail, because when SQL Server compiles this batch, it sees
that you references a column that does not exist in tbl, and that is
an error. SQL Server has deferred name resolution, so that if a table
does not exist when the batch is compiled, SQL Server is silent in hope
that the table is created. There is, thankfully, not deferred name
resolution for column names. It is bad as it is.
There are a couple of ways to skin the cat. The best is probably
to wrap the UPDATE into EXEC(), so that it will not be compiled
until after the ALTER TABLE statement has been executed.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Mon, 27 Mar 2006 22:42:08 +0000 (UTC), Erland Sommarskog wrote:
(snip)
>There are a couple of ways to skin the cat. The best is probably
>to wrap the UPDATE into EXEC(), so that it will not be compiled
>until after the ALTER TABLE statement has been executed.
Hi Erland,
In a stored procedure: yes.
But in a SQL script, just adding a "go" between the ALTER TABLE and the
UPDATE is enough.
Hugo Kornelis, SQL Server MVP|||Hugo Kornelis (hugo@.perFact.REMOVETHIS.info.INVALID) writes:
> (snip)
> Hi Erland,
> In a stored procedure: yes.
> But in a SQL script, just adding a "go" between the ALTER TABLE and the
> UPDATE is enough.
I didn't mention that possibility because the hour was late, and there are
some caveates with it. Say that you do:
BEGIN TRANSACTION
-- Do something
go
-- Do something more
go
-- Yet something more
COMMIT TRANSACTION
Now, if there is an error on the line of the kind that aborts the batch,
the transaction will be rolled back, but the remaining batches will be
executed. You will get an error when you reach COMMIT, but then the damage
may already been done.
Of course, in this particular case if ALTER TABLE fails, the UPDATE command
will also fail. However, there can be other commands in other batches that
still can be carried out when they shouldn't.
One way to handles this is to open every batch with IF @.@.trancount > 0,
but I think would be prefer to keep all in one batch, and interleave
problematic statments in dynamic SQL. Not the least on SQL 2005, as I
then can have single CATCH handler at the end. (But note that if you
have:
BEGIN TRY
UPDATE tbl SET missingcolumn = <somevalue>
END TRY
BEGIN CATCH
-- handle error
END CATCH
that the CATCH handler will not be reached, as the error is a compilation
error.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Tue, 28 Mar 2006 10:27:33 +0000 (UTC), Erland Sommarskog wrote:

>Hugo Kornelis (hugo@.perFact.REMOVETHIS.info.INVALID) writes:
>I didn't mention that possibility because the hour was late, and there are
>some caveates with it. Say that you do:
(snip)
Hi Erland,
Good point. Thanks for adding this warning!
Hugo Kornelis, SQL Server MVPsql

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 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 sp_start_job Remote Server

I am trying to create a centralized job monitoring system that can be
managed by some operators to restart a job if it fails. Everything
will be running on one system but I need to invoke remote jobs on
remote system. I am having trouble starting a job on a remote system
even if I have the permissions to do so.
[Code]
DECLARE @.SQLCMD VARCHAR(MAX)
SET @.SQLCMD = ''
DECLARE @.SystemNameVARCHAR(50)
DECLARE @.JobNameVARCHAR(50)
DECLARE @.StepNameVARCHAR(50)
SET @.SystemName = RemoteServer
SET @.JobName = Explode
SET @.StepName = BadStep
SET @.SQLCMD = '' + @.SystemName + '.msdb.dbo.sp_start_job @.job_name =
''' + @.JobName + ''', @.step_name = ''' + @.StepName + ''''
PRINT @.SQLCMD
[/code]
This will generate the following
RemoteServer.msdb.dbo.sp_start_job @.job_name = 'Explode', @.step_name =
'Bad'
If i run this command as an adhoc command, it will execute on the
RemoteServer and start the job at the step. However for the SP i am
writing this does not work
[Code]
EXEC @.SQLCMD
EXEC msdb.dbo.sp_start_job @.job_name = 'Explode', @.step_name = 'Bad'
[/Code]
This error appers.
Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'msdb.dbo.sp_start_job @.job_name =
'Explode', @.step_name = 'Bad''.
If I try to run it this way, I get the following error message.
EXEC msdb.dbo.sp_start_job @.server_name = 'dbdev4', @.job_name =
'Explode', @.step_name = 'Bad'
Msg 14262, Level 16, State 1, Procedure sp_verify_job_identifiers,
Line 67
The specified @.job_name ('Explode') does not exist.
Any ideas
Thanks
-Matt-
Hi Matt
"Matthew" wrote:

> I am trying to create a centralized job monitoring system that can be
> managed by some operators to restart a job if it fails. Everything
> will be running on one system but I need to invoke remote jobs on
> remote system. I am having trouble starting a job on a remote system
> even if I have the permissions to do so.
> [Code]
> DECLARE @.SQLCMD VARCHAR(MAX)
> SET @.SQLCMD = ''
> DECLARE @.SystemNameVARCHAR(50)
> DECLARE @.JobNameVARCHAR(50)
> DECLARE @.StepNameVARCHAR(50)
> SET @.SystemName = RemoteServer
> SET @.JobName = Explode
> SET @.StepName = BadStep
> SET @.SQLCMD = '' + @.SystemName + '.msdb.dbo.sp_start_job @.job_name =
> ''' + @.JobName + ''', @.step_name = ''' + @.StepName + ''''
> PRINT @.SQLCMD
> [/code]
> This will generate the following
> RemoteServer.msdb.dbo.sp_start_job @.job_name = 'Explode', @.step_name =
> 'Bad'
> If i run this command as an adhoc command, it will execute on the
> RemoteServer and start the job at the step. However for the SP i am
> writing this does not work
> [Code]
> EXEC @.SQLCMD
> EXEC msdb.dbo.sp_start_job @.job_name = 'Explode', @.step_name = 'Bad'
> [/Code]
> This error appers.
> Msg 2812, Level 16, State 62, Line 1
> Could not find stored procedure 'msdb.dbo.sp_start_job @.job_name =
> 'Explode', @.step_name = 'Bad''.
Try:
EXEC ( @.SQLCMD )
or
EXEC RemoteServer.msdb.dbo.sp_start_job @.job_name = 'Explode', @.step_name =
'Bad'

> If I try to run it this way, I get the following error message.
> EXEC msdb.dbo.sp_start_job @.server_name = 'dbdev4', @.job_name =
> 'Explode', @.step_name = 'Bad'
> Msg 14262, Level 16, State 1, Procedure sp_verify_job_identifiers,
> Line 67
> The specified @.job_name ('Explode') does not exist.
> Any ideas
> Thanks
> -Matt-
John

EXEC sp_start_job Remote Server

I am trying to create a centralized job monitoring system that can be
managed by some operators to restart a job if it fails. Everything
will be running on one system but I need to invoke remote jobs on
remote system. I am having trouble starting a job on a remote system
even if I have the permissions to do so.
[Code]
DECLARE @.SQLCMD VARCHAR(MAX)
SET @.SQLCMD = ''
DECLARE @.SystemName VARCHAR(50)
DECLARE @.JobName VARCHAR(50)
DECLARE @.StepName VARCHAR(50)
SET @.SystemName = RemoteServer
SET @.JobName = Explode
SET @.StepName = BadStep
SET @.SQLCMD = '' + @.SystemName + '.msdb.dbo.sp_start_job @.job_name =
''' + @.JobName + ''', @.step_name = ''' + @.StepName + ''''
PRINT @.SQLCMD
[/code]
This will generate the following
RemoteServer.msdb.dbo.sp_start_job @.job_name = 'Explode', @.step_name =
'Bad'
If i run this command as an adhoc command, it will execute on the
RemoteServer and start the job at the step. However for the SP i am
writing this does not work
[Code]
EXEC @.SQLCMD
EXEC msdb.dbo.sp_start_job @.job_name = 'Explode', @.step_name = 'Bad'
[/Code]
This error appers.
Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'msdb.dbo.sp_start_job @.job_name =
'Explode', @.step_name = 'Bad''.
If I try to run it this way, I get the following error message.
EXEC msdb.dbo.sp_start_job @.server_name = 'dbdev4', @.job_name =
'Explode', @.step_name = 'Bad'
Msg 14262, Level 16, State 1, Procedure sp_verify_job_identifiers,
Line 67
The specified @.job_name ('Explode') does not exist.
Any ideas
Thanks
-Matt-Hi Matt
"Matthew" wrote:

> I am trying to create a centralized job monitoring system that can be
> managed by some operators to restart a job if it fails. Everything
> will be running on one system but I need to invoke remote jobs on
> remote system. I am having trouble starting a job on a remote system
> even if I have the permissions to do so.
> [Code]
> DECLARE @.SQLCMD VARCHAR(MAX)
> SET @.SQLCMD = ''
> DECLARE @.SystemName VARCHAR(50)
> DECLARE @.JobName VARCHAR(50)
> DECLARE @.StepName VARCHAR(50)
> SET @.SystemName = RemoteServer
> SET @.JobName = Explode
> SET @.StepName = BadStep
> SET @.SQLCMD = '' + @.SystemName + '.msdb.dbo.sp_start_job @.job_name =
> ''' + @.JobName + ''', @.step_name = ''' + @.StepName + ''''
> PRINT @.SQLCMD
> [/code]
> This will generate the following
> RemoteServer.msdb.dbo.sp_start_job @.job_name = 'Explode', @.step_name =
> 'Bad'
> If i run this command as an adhoc command, it will execute on the
> RemoteServer and start the job at the step. However for the SP i am
> writing this does not work
> [Code]
> EXEC @.SQLCMD
> EXEC msdb.dbo.sp_start_job @.job_name = 'Explode', @.step_name = 'Bad'
> [/Code]
> This error appers.
> Msg 2812, Level 16, State 62, Line 1
> Could not find stored procedure 'msdb.dbo.sp_start_job @.job_name =
> 'Explode', @.step_name = 'Bad''.
Try:
EXEC ( @.SQLCMD )
or
EXEC RemoteServer.msdb.dbo.sp_start_job @.job_name = 'Explode', @.step_name =
'Bad'

> If I try to run it this way, I get the following error message.
> EXEC msdb.dbo.sp_start_job @.server_name = 'dbdev4', @.job_name =
> 'Explode', @.step_name = 'Bad'
> Msg 14262, Level 16, State 1, Procedure sp_verify_job_identifiers,
> Line 67
> The specified @.job_name ('Explode') does not exist.
> Any ideas
> Thanks
> -Matt-
Johnsql

EXEC sp_start_job Remote Server

I am trying to create a centralized job monitoring system that can be
managed by some operators to restart a job if it fails. Everything
will be running on one system but I need to invoke remote jobs on
remote system. I am having trouble starting a job on a remote system
even if I have the permissions to do so.
[Code]
DECLARE @.SQLCMD VARCHAR(MAX)
SET @.SQLCMD = ''
DECLARE @.SystemName VARCHAR(50)
DECLARE @.JobName VARCHAR(50)
DECLARE @.StepName VARCHAR(50)
SET @.SystemName = RemoteServer
SET @.JobName = Explode
SET @.StepName = BadStep
SET @.SQLCMD = '' + @.SystemName + '.msdb.dbo.sp_start_job @.job_name = ''' + @.JobName + ''', @.step_name = ''' + @.StepName + ''''
PRINT @.SQLCMD
[/code]
This will generate the following
RemoteServer.msdb.dbo.sp_start_job @.job_name = 'Explode', @.step_name = 'Bad'
If i run this command as an adhoc command, it will execute on the
RemoteServer and start the job at the step. However for the SP i am
writing this does not work
[Code]
EXEC @.SQLCMD
EXEC msdb.dbo.sp_start_job @.job_name = 'Explode', @.step_name = 'Bad'
[/Code]
This error appers.
Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'msdb.dbo.sp_start_job @.job_name = 'Explode', @.step_name = 'Bad''.
If I try to run it this way, I get the following error message.
EXEC msdb.dbo.sp_start_job @.server_name = 'dbdev4', @.job_name = 'Explode', @.step_name = 'Bad'
Msg 14262, Level 16, State 1, Procedure sp_verify_job_identifiers,
Line 67
The specified @.job_name ('Explode') does not exist.
Any ideas
Thanks
-Matt-Hi Matt
"Matthew" wrote:
> I am trying to create a centralized job monitoring system that can be
> managed by some operators to restart a job if it fails. Everything
> will be running on one system but I need to invoke remote jobs on
> remote system. I am having trouble starting a job on a remote system
> even if I have the permissions to do so.
> [Code]
> DECLARE @.SQLCMD VARCHAR(MAX)
> SET @.SQLCMD = ''
> DECLARE @.SystemName VARCHAR(50)
> DECLARE @.JobName VARCHAR(50)
> DECLARE @.StepName VARCHAR(50)
> SET @.SystemName = RemoteServer
> SET @.JobName = Explode
> SET @.StepName = BadStep
> SET @.SQLCMD = '' + @.SystemName + '.msdb.dbo.sp_start_job @.job_name => ''' + @.JobName + ''', @.step_name = ''' + @.StepName + ''''
> PRINT @.SQLCMD
> [/code]
> This will generate the following
> RemoteServer.msdb.dbo.sp_start_job @.job_name = 'Explode', @.step_name => 'Bad'
> If i run this command as an adhoc command, it will execute on the
> RemoteServer and start the job at the step. However for the SP i am
> writing this does not work
> [Code]
> EXEC @.SQLCMD
> EXEC msdb.dbo.sp_start_job @.job_name = 'Explode', @.step_name = 'Bad'
> [/Code]
> This error appers.
> Msg 2812, Level 16, State 62, Line 1
> Could not find stored procedure 'msdb.dbo.sp_start_job @.job_name => 'Explode', @.step_name = 'Bad''.
Try:
EXEC ( @.SQLCMD )
or
EXEC RemoteServer.msdb.dbo.sp_start_job @.job_name = 'Explode', @.step_name ='Bad'
> If I try to run it this way, I get the following error message.
> EXEC msdb.dbo.sp_start_job @.server_name = 'dbdev4', @.job_name => 'Explode', @.step_name = 'Bad'
> Msg 14262, Level 16, State 1, Procedure sp_verify_job_identifiers,
> Line 67
> The specified @.job_name ('Explode') does not exist.
> Any ideas
> Thanks
> -Matt-
John

exec sp_primarykeys and exec sp_foreignkeys

Hi,
I need to check the primary and the foreign keys of
existing user tables in one table to create the
corresponding Data Model.
I execute the following procedure, i see the BOL and i
need to enter one linked server for the procedure execute
fine. Im not doing this because im doing this in the local
machine.
exec sp_primarykeys
How can i generate this Data Model or how can i get this
relations with the minimum effort?
Best RegardsCC&JM
Try put your local sever name and remember you have to enable access data
exec sp_serveroption 'Server','data access','true'
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:bded01c47a14$cec4e000$a601280a@.phx.gbl...
> Hi,
> I need to check the primary and the foreign keys of
> existing user tables in one table to create the
> corresponding Data Model.
> I execute the following procedure, i see the BOL and i
> need to enter one linked server for the procedure execute
> fine. Im not doing this because im doing this in the local
> machine.
> exec sp_primarykeys
> How can i generate this Data Model or how can i get this
> relations with the minimum effort?
> Best Regards
>

Exec SP many times, from select?

Hi All...

if i had the following sp...

*******************************************************
create procedure my_insert (param1 int, param2 int, paramx int)
as
...
complicated insert routine
...
return
*******************************************************

and then i wanted to exec this sp in another procedure i would have

exec my_insert( 1_value, 2_value, 3_value )

My question is how could i exec this will the result set of a select... something like this

exec my_insert (select 1_value, 2_value, 3_value from another_table).

I know i could have this in an insert result type statement ie...

insert into dest_table (select 1_value, 2_value, 3_value from another_table)

but my insert routine is quite complicated and carries out some other functions so I would like to call (exec) a sp rather than repeating the complication in the select statement

Many Thanks
Gary Tyou can get the values into variables and send the variables into the sp.

select @.var1=select1_Value,@.var2=select2_Value,@.var3=select3_Value from YourTable
exec my_insert(@.var1,@.var2,@.var2)

hth|||Thanks for your help....but

This statement would only call the stored procedure once with the params set to the last selected values.

I would like to call the sp as many times as there are records in the select statement, containing the values for each record.

Cheers
Gary T|||Hi All,

If it's any help to describe the problem, I current carry out this by using a cursor, but I thought there must be an easier (better) way of doing this.

*****************************************************
DECLARE my_cursor CURSOR FOR
SELECT 1_value, 2_value, 3_value from other_table where some_condition

OPEN my_cursor
FETCH NEXT FROM my_cursor
INTO
@.var_1, @.var_2, @.var_3
WHILE @.@.FETCH_STATUS = 0
BEGIN
exec my_insert @.var_1, @.var_2, @.var_3
FETCH NEXT FROM my_cursor
INTO
@.var_1, @.var_2, @.var_3
END
CLOSE my_cursor
DEALLOCATE my_cursor
*****************************************************

Cheers
Gary T|||cursors are usually a performance hit, but unless you are processing thousands of rows, it shud be ok. make sure you declare your cursor as :

DECLARE rs CURSOR
LOCAL
FORWARD_ONLY
OPTIMISTIC
TYPE_WARNING
FOR SELECT
OPEN rs
fetch next from rs into
WHILE ( @.@.FETCH_STATUS = 0 )
begin
FETCH NEXT FROM rs INTO
END
close rs
deallocate rs

the local,forward_only etc are optional parameters but will make it run faster. check out BOL for more info abt cursors and other parameters.

hthsql

Exec procedure on database create/rename/delete ?

Hello,

I need to execute certain procedures automatically when databases are create, renamed or deleted. I looked around in this forum and Internet but couldn't find anything. This is for SQL 2000.

Well, in SQL 2000 it's not as easy as in SQL 2005 where you could use DDL triggers to achieve this.

You could configure different Alerts in SQL Server 2000 to fire when certain messages are logged to the error log, in this case you could try possibly firing an alert when the error message # 1805 fires ("The CREATE DATABASE process is allocating 1.00 MB on disk 'blah'), but that would only capture newly created DBs. You could use the same method for databases that are attached, restored, deleted, etc., you'd just have to be sure to get error messages that are fired for each 'event'. Once you have the appropriate Alert configured, just set the alert to execute a SQL Agent job when it fires, and setup the Agent job to run the SQL code you want.

If you don't need the code to run exactly when the DB is created/deleted/attached/etc., you could run a trace that captures the appropriate events, then load the trace file nightly and scan for the appropriate event classes, then executing code appropriately as needed.

HTH,

|||

Thanks a lot Chad. This is very helpful. I didn't think about alerts.

sql

exec an SP to return rows to another SP

Hi,
Any idea how to write a (T-)SQL Stored Procedure which uses a SubQuery calling
a SELECT query from another SP??

Something like...

CREATE procedure spThisSP(@.param varchar(20))
AS
SELECT theColumn FROM theTable WHERE theColumn NOT IN (EXEC spAnotherSP @.param)

CheersYou could re-write the sub proc as a function|||Thanks for the reply pkr but I was hoping that I could re-use stored procedures somehow?
Duplicating the code one way or another seems "wrong"...

Cheers|||You could use a temp table...
select into #temp
exec proc1

select * from #temp

But I HATE temp tables.|||i didnt understand what you are trying to do but i know you can call a stored proc from another stored proc..

hth|||pkr,

But I HATE temp tables.

May I ask why? I'm not being critical, just trying to understand various people's like and dislike of temp tables.

Thanks,
Don|||My main hate is a really irrating bug/feature I've hit in the past where the db is convinced the temp table already exists when you try to create it but says it doesn't exist when you try to use it!!

Besides that, there are too many locking issues with tempDb that make it very difficult to effectively police developer code - IMO. I just try to avoid them wherever possible.|||Cool, thanks.

Don|||Thanks for all the replies. From your discussion I found thisarticle which provides some more helpful pointers in using temp tables.

exec a .sql file

Hi,
I need to create a sql job that exec a .sql file located in c:\.
Anyone knows how to?
ThanksCreate a job step of type: Operating System
Enter a command: OSQL -E -i C:\MyFolder\myscript.sql -o
C:\MyFolder\myscript.txt
Assuming that the account running OSQL has the needed rights, then OSQL will
run the script for you.
What account you run as differs depending on whether you are running SQL
2000 or 2005. Read about SQL Agent proxy accounts.
RLF
"mecn" <mecn2002@.yahoo.com> wrote in message
news:ueohAzIpHHA.4196@.TK2MSFTNGP06.phx.gbl...
> Hi,
> I need to create a sql job that exec a .sql file located in c:\.
> Anyone knows how to?
> Thanks
>|||I'll try. Thanks a lot
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:ORU1z$IpHHA.5052@.TK2MSFTNGP04.phx.gbl...
> Create a job step of type: Operating System
> Enter a command: OSQL -E -i C:\MyFolder\myscript.sql -o
> C:\MyFolder\myscript.txt
> Assuming that the account running OSQL has the needed rights, then OSQL
> will run the script for you.
> What account you run as differs depending on whether you are running SQL
> 2000 or 2005. Read about SQL Agent proxy accounts.
> RLF
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:ueohAzIpHHA.4196@.TK2MSFTNGP06.phx.gbl...
>> Hi,
>> I need to create a sql job that exec a .sql file located in c:\.
>> Anyone knows how to?
>> Thanks
>

exec a .sql file

Hi,
I need to create a sql job that exec a .sql file located in c:\.
Anyone knows how to?
ThanksCreate a job step of type: Operating System
Enter a command: OSQL -E -i C:\MyFolder\myscript.sql -o
C:\MyFolder\myscript.txt
Assuming that the account running OSQL has the needed rights, then OSQL will
run the script for you.
What account you run as differs depending on whether you are running SQL
2000 or 2005. Read about SQL Agent proxy accounts.
RLF
"mecn" <mecn2002@.yahoo.com> wrote in message
news:ueohAzIpHHA.4196@.TK2MSFTNGP06.phx.gbl...
> Hi,
> I need to create a sql job that exec a .sql file located in c:\.
> Anyone knows how to?
> Thanks
>|||I'll try. Thanks a lot
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:ORU1z$IpHHA.5052@.TK2MSFTNGP04.phx.gbl...
> Create a job step of type: Operating System
> Enter a command: OSQL -E -i C:\MyFolder\myscript.sql -o
> C:\MyFolder\myscript.txt
> Assuming that the account running OSQL has the needed rights, then OSQL
> will run the script for you.
> What account you run as differs depending on whether you are running SQL
> 2000 or 2005. Read about SQL Agent proxy accounts.
> RLF
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:ueohAzIpHHA.4196@.TK2MSFTNGP06.phx.gbl...
>

exec a .sql file

Hi,
I need to create a sql job that exec a .sql file located in c:\.
Anyone knows how to?
Thanks
Create a job step of type: Operating System
Enter a command: OSQL -E -i C:\MyFolder\myscript.sql -o
C:\MyFolder\myscript.txt
Assuming that the account running OSQL has the needed rights, then OSQL will
run the script for you.
What account you run as differs depending on whether you are running SQL
2000 or 2005. Read about SQL Agent proxy accounts.
RLF
"mecn" <mecn2002@.yahoo.com> wrote in message
news:ueohAzIpHHA.4196@.TK2MSFTNGP06.phx.gbl...
> Hi,
> I need to create a sql job that exec a .sql file located in c:\.
> Anyone knows how to?
> Thanks
>
|||I'll try. Thanks a lot
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:ORU1z$IpHHA.5052@.TK2MSFTNGP04.phx.gbl...
> Create a job step of type: Operating System
> Enter a command: OSQL -E -i C:\MyFolder\myscript.sql -o
> C:\MyFolder\myscript.txt
> Assuming that the account running OSQL has the needed rights, then OSQL
> will run the script for you.
> What account you run as differs depending on whether you are running SQL
> 2000 or 2005. Read about SQL Agent proxy accounts.
> RLF
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:ueohAzIpHHA.4196@.TK2MSFTNGP06.phx.gbl...
>

exe to export reports to pdf

Hi,
We have a need to create something like a win app that would be scheduled to
run on the web/reporting server, query the db, pass some parameters to
reports and auto-generate them in pdf format (to be stored in db or on the
same box) to make them available for the website visitors.
Have anybody done something like this before or seen an example of? We tried
to do something similar when reporting services when it just came out but
were unable to implement. Would really appreciate any advice on how to
tackle this!Thanks! Are you using data-driven subscription feature for this? Is it only
available with Enterprise SQL edition?
"Wickherm" <Wickherm@.discussions.microsoft.com> wrote in message
news:3CF580B4-0CD1-4C37-8099-2B65FBF846B2@.microsoft.com...
> We use the subscriptons in RS to schedule reports to save to a file share.
> The parameters in the reports call outside dll's to get the values.
> "ilona" wrote:
>> Hi,
>> We have a need to create something like a win app that would be scheduled
>> to
>> run on the web/reporting server, query the db, pass some parameters to
>> reports and auto-generate them in pdf format (to be stored in db or on
>> the
>> same box) to make them available for the website visitors.
>> Have anybody done something like this before or seen an example of? We
>> tried
>> to do something similar when reporting services when it just came out but
>> were unable to implement. Would really appreciate any advice on how to
>> tackle this!
>>

excluding timestamp field in insert

I need to create a lot of simply queries that copy records from one table to
another. The queries are like the one below:
INSERT INTO tblEmp
SELECT tblEmp2003.*
FROM tblEmp2003
WHERE tblEmp2003.EmployeeId='001'
My problem is that all the tables contain a timestamp so the querys fail
because the timestamp is not uptable. Is there an exclusion verb that I can
use to exclude the timestamp field. I know I could simply list all the
fields and not include the timestamp column, but given all the queries I nee
d
to setup that would take forever.
Thanks"MarkT" <MarkT@.discussions.microsoft.com> wrote:

>I need to create a lot of simply queries that copy records from one table t
o
>another. The queries are like the one below:
>INSERT INTO tblEmp
>SELECT tblEmp2003.*
>FROM tblEmp2003
>WHERE tblEmp2003.EmployeeId='001'
>My problem is that all the tables contain a timestamp so the querys fail
>because the timestamp is not uptable. Is there an exclusion verb that I ca
n
>use to exclude the timestamp field. I know I could simply list all the
>fields and not include the timestamp column, but given all the queries I ne
ed
>to setup that would take forever.
>Thanks
No, there is no way around listing the columns. You might make the
job less difficult if you wrote a query to generate the text of the
INSERT commands though. You might get some ideas from the proc below,
which generates a SELECT for a table.
Roy
CREATE proc dbo.sp__select
(@.tblname varchar(50),
@.alias varchar(50) = NULL)
AS
select CASE WHEN C.colid = 1
THEN 'SELECT '
ELSE ' '
END +
CASE WHEN @.alias IS NOT NULL
THEN @.alias + '.'
ELSE ''
END +
C.name +
CASE
WHEN C.colid < (select max(colid) from syscolumns CC
where O.id = CC.id)
THEN ','
ELSE CHAR(13) + CHAR(10) + ' FROM ' + O.name +
CASE WHEN @.alias IS NOT NULL THEN ' as ' + @.alias
ELSE ''
END
END
from sysobjects O, syscolumns C
where O.id = C.id
and O.name = @.tblname
order by C.id, C.colid
GO|||On Mon, 14 Nov 2005 19:31:01 -0800, "MarkT"
<MarkT@.discussions.microsoft.com> wrote:
>I need to create a lot of simply queries that copy records from one table t
o
>another. The queries are like the one below:
>INSERT INTO tblEmp
>SELECT tblEmp2003.*
>FROM tblEmp2003
>WHERE tblEmp2003.EmployeeId='001'
>My problem is that all the tables contain a timestamp so the querys fail
>because the timestamp is not uptable. Is there an exclusion verb that I ca
n
>use to exclude the timestamp field. I know I could simply list all the
>fields and not include the timestamp column, but given all the queries I ne
ed
>to setup that would take forever.
The query analyzer will generate the insert and select skeletons for
you. It is a pity SQLServer can't be smarter about the *.
J.

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)

Exception Has been thrown by the target of an invocation

Getting this error "Exception Has been thrown by the target of an invocation" when trying to create Integration Services Project. Any ideas what can be wrong?

Thanks.

I am receiving the same issue... were you able to solve this?|||In our case the problem was related to the installation image we used to setup our computers (every PC that used that image had the problem).

Exception Has been thrown by the target of an invocation

Getting this error "Exception Has been thrown by the target of an invocation" when trying to create Integration Services Project. Any ideas what can be wrong?

Thanks.

I am receiving the same issue... were you able to solve this?|||In our case the problem was related to the installation image we used to setup our computers (every PC that used that image had the problem).

Exception Has been thrown by the target of an invocation

Getting this error "Exception Has been thrown by the target of an invocation" when trying to create Integration Services Project. Any ideas what can be wrong?

Thanks.

I am receiving the same issue... were you able to solve this?|||In our case the problem was related to the installation image we used to setup our computers (every PC that used that image had the problem).