Creating Multiple Triggers is same sql script

I'm trying to use Query Analyzer to create several triggers on different files in the same sql script file. It appears to only allow me to create one trigger at a time in Query Analyzer. How do you separate multiple create trigger statements? Here what I'm trying to do:

CREATE TRIGGER PO_BOL_DELETE ON dbo.PO_BOL
FOR DELETE
AS

INSERT into PO_Back
SELECT *, host_name(), suser_name(), getdate()
FROM deleted
GO

CREATE TRIGGER RECEIPT_DELETE ON dbo.receipt
FOR DELETE
AS

INSERT into receipt_Back
SELECT *, host_name(), suser_name(), getdate()
FROM deleted
GOWell that's the way to do it...(except for the SELECT * bit)

http://weblogs.sqlteam.com/brettk/archive/2004/04/22/1272.aspx

Are you getting an error?

select * is dangerous btw.|||It stops at the second create trigger and indicates create trigger not valid. Should the syntax I have work?|||works for me...

USE Northwind
GO

CREATE TABLE PO_BOL(Col1 int)
CREATE TABLE receipt(Col1 int)
GO

CREATE TRIGGER PO_BOL_DELETE ON dbo.PO_BOL
FOR DELETE
AS

INSERT into PO_Back
SELECT *, host_name(), suser_name(), getdate()
FROM deleted
GO

CREATE TRIGGER RECEIPT_DELETE ON dbo.receipt
FOR DELETE
AS

INSERT into receipt_Back
SELECT *, host_name(), suser_name(), getdate()
FROM deleted
GO

DROP TABLE PO_BOL
DROP TABLE receipt
GO

Couple of things...lose SELECT *, Make sure you supply the column list for the insert...other than that it all looks good|||Thanks for the help! I got it to work..... Thanks again.

Another question:

I'm using VB to open a direct connection to SQL SERVER 2000. The AnsiNPW=off in the connection doesn't work. Any ideas? I had to create my tables in SQL server with SET ANSI_PADDING OFF to get the spaces trimmed.

Any thoughts?

JGS|||I'll tell anone who'll listen that I've forgot all my VB...otherwise they might make me build interfaces...

Just talking about it and I feel all dirty...

How is the table defined?

char or varchar??|||Your on to it! The fields are Varchar. I tried changing the fields to Char but AnsiNPW still doesn't seem to do anything in the connection string. Have you heard of any bug that AnsiNPW doesn't work?

Creating multiple temporary databases.

Hello,
I noticed that when we installed SQL 2000 Reporting services that two
databases are created one being ReportServerTempDB. This got me to thinkin
g
about Oracle databases where I can create multiple temporary 'tablespaces'
and then I can assign specific users to specific tablespaces.
In a SQL environment, can I create separate tempdbs that will be used by
separate users and/or applications? How do Reporting Services use the
ReportServerTempDB?
I'm trying to create a shared hosting environment in my organization and
don't want competing applications filling up the tempdb. I'd much prefer
that each application uses it's own temp space.
Thanks!
Art"Art Decker" <ArtDecker@.discussions.microsoft.com> wrote in message
news:E8D4949F-A0C5-4408-8318-F58EF0847085@.microsoft.com...
> Hello,
> I noticed that when we installed SQL 2000 Reporting services that two
> databases are created one being ReportServerTempDB. This got me to
> thinking
> about Oracle databases where I can create multiple temporary 'tablespaces'
> and then I can assign specific users to specific tablespaces.
> In a SQL environment, can I create separate tempdbs that will be used by
> separate users and/or applications?
>How do Reporting Services use the ReportServerTempDB?
It uses it for caching and snapshots. Lots of reads and writes with a low
requirement for backup.
But it's an ordinary user database.

> I'm trying to create a shared hosting environment in my organization and
> don't want competing applications filling up the tempdb. I'd much prefer
> that each application uses it's own temp space.
>
All databases on a server share memory, CPU and TempDB. You can install
multiple instances to segregate these resources.
David

Creating multiple temporary databases.

Hello,
I noticed that when we installed SQL 2000 Reporting services that two
databases are created one being ReportServerTempDB. This got me to thinking
about Oracle databases where I can create multiple temporary 'tablespaces'
and then I can assign specific users to specific tablespaces.
In a SQL environment, can I create separate tempdbs that will be used by
separate users and/or applications? How do Reporting Services use the
ReportServerTempDB?
I'm trying to create a shared hosting environment in my organization and
don't want competing applications filling up the tempdb. I'd much prefer
that each application uses it's own temp space.
Thanks!
Art"Art Decker" <ArtDecker@.discussions.microsoft.com> wrote in message
news:E8D4949F-A0C5-4408-8318-F58EF0847085@.microsoft.com...
> Hello,
> I noticed that when we installed SQL 2000 Reporting services that two
> databases are created one being ReportServerTempDB. This got me to
> thinking
> about Oracle databases where I can create multiple temporary 'tablespaces'
> and then I can assign specific users to specific tablespaces.
> In a SQL environment, can I create separate tempdbs that will be used by
> separate users and/or applications?
>How do Reporting Services use the ReportServerTempDB?
It uses it for caching and snapshots. Lots of reads and writes with a low
requirement for backup.
But it's an ordinary user database.
> I'm trying to create a shared hosting environment in my organization and
> don't want competing applications filling up the tempdb. I'd much prefer
> that each application uses it's own temp space.
>
All databases on a server share memory, CPU and TempDB. You can install
multiple instances to segregate these resources.
David

creating multiple tables?

Hello,
I need to create around 1500 similar tables.

Does anyone know how to create them all at once instead of one-by-one?


thanks

If these tables currently reside in another RDBMS then you MAY be able to generate sql for them. I am a pure MS SQL SERVER geek so I am unsure of, but regardless you would have to convert the scripts to use TSQL's create table statement.

If your tables are similiar enough and you must create these fresh you do some thing like the following (use dynamic sql):

declare @.SQL nvarchar(1000)

declare @.i int

select @.i = 0, @.SQL = ''

while @.i <= 1499

begin

set @.SQL = 'CREATE TABLE ' + [table name algorithm goes here] + [table definition goes here

exec sp_executesql @.SQL]

set @.i = @.i + 1

end

TSQL Create Table Syntax:

CREATE TABLE table_name

( { < column_definition > | < table_constraint > } [ ,...n ]

)

< column_definition > ::=

{ column_name data_type }

[ { DEFAULT constant_expression

| [ IDENTITY [ ( seed , increment ) ]

]

} ]

[ ROWGUIDCOL ]

[ < column_constraint > [ ...n ] ]

< column_constraint > ::=

[ CONSTRAINT constraint_name ]

{ [ NULL | NOT NULL ]

| [ PRIMARY KEY | UNIQUE ]

| REFERENCES ref_table [ ( ref_column ) ]

[ ON DELETE { CASCADE | NO ACTION } ]

[ ON UPDATE { CASCADE | NO ACTION } ]

}

< table_constraint > ::=

[ CONSTRAINT constraint_name ]

{ [ { PRIMARY KEY | UNIQUE }

{ ( column [ ,...n ] ) }

]

| FOREIGN KEY

( column [ ,...n ] )

REFERENCES ref_table [ ( ref_column [ ,...n ] ) ]

[ ON DELETE { CASCADE | NO ACTION } ]

[ ON UPDATE { CASCADE | NO ACTION } ]

}|||thank you very much

I will post again in this thread if I have any troubles|||

Hi I'm having some trouble, I made this query:

declare @.SQL nvarchar(1000)

declare @.i int

SELECT @.i = 0, @.SQL =

WHILE @.i <= 32228

begin

set @.SQL = 'CREATE TABLE' + tbl_i_quotes + (

QuoteDate nchar(20),

QuoteTime nchar(20),

BidPrice float,

AskPrice float,

BidSize float,

AskSize float)

exec sp_executesql @.SQL

set @.i = @.i + 1

end

and i got this as an error message:

Msg 156, Level 15, State 1, Line 7

Incorrect syntax near the keyword 'WHILE'.

Msg 102, Level 15, State 1, Line 12

Incorrect syntax near 'nchar'.

thanks again

|||

this works:

declare @.SQL nvarchar(1000)

declare @.i int

SELECT @.i = 0, @.SQL = ''

WHILE @.i <= 32228

BEGIN

set @.SQL = 'CREATE TABLE' + '[tbl_' + CONVERT(nvarchar(5),@.i) + '_quotes] (

QuoteDate nchar(20),

QuoteTime nchar(20),

BidPrice float,

AskPrice float,

BidSize float,

AskSize float)'

exec sp_executesql @.SQL

set @.i = @.i + 1

end

|||it works! thank you so much!


I'd hate to bother you more, but you seem so informative...

do you know much about bulk inserting a folder full of flat files into select tables?
maybe using integration services? i've tried BCP and have had pretty negative results.


thanks again, yer a life saver.|||your welcome :) glad it helped. I used DTS in sql2000 ALOT, but have had almost 0 exp. with SSIS in 2005 as of yet. However, SSIS (like DTS) has always been a great tool for importing and exporting data and I would look at leveraging it for loading a flat file. BCP/BULK INSERT is good to.

Creating multiple Tab Deliminted Exports

Is there a way to read from a table to get values that will be contained within a "where" clause of another SQL statement that can be ready one by one(meaning the same sql statement will be executed mutliple times) that will export a tab delimted file?

Hi Cheston,

In my case, I was able to use Execute SQL Task to output the final result set using the code something like this

SELEC * FROM (SELECT * FROM Table1) Table2 WHERE (Tablestatus <> 'No Change'), but then the question is how I can export the Result Set in a tab delimitted format.

|||

cheston wrote:

Is there a way to read from a table to get values that will be contained within a "where" clause of another SQL statement that can be ready one by one(meaning the same sql statement will be executed mutliple times) that will export a tab delimted file?

You can select your values using an execute SQL task on the control flow. Then using a foreach loop, you would "shred" the variable populated from the execute SQL task. Inside the foreach loop, you would run your data flow. Build a package level scoped variable, set EvaluateAsExpression = True and build an expression that contains your base SQL statement, and then concatenates to it the value of the foreach loop's variable (which would contain just one iteration's WHERE clause as selected from the table). Then, inside the data flow, use an OLE DB Source which uses a variable as the source for the SQL. Pick the variable you just built with the expression.

That's pretty much it in a nutshell.|||

See is that example helps:

http://rafael-salas.blogspot.com/2006/12/import-header-line-tables-into-dynamic_22.html

Creating multiple Stored procedure on SQL Server through ADO

Hi,
I want to create a set of procedures through my application on SQL Server, t
hrough ADO.
My Scipts is something of the form:
Use DB1
Go
create procedure <procedurename>
as
< procedure text>
Go
Create Procedure <procedurename>
as
<procedure text>
Go
But when I execute this statement it gives error 'Error in line 2 Go'
Is there a way to excute this script in a single call or I have to make mult
iple calls.
Thanks
PushkarInstead of GO use a semicol for this:
Use DB1;
create procedure <procedurename> ...
HTH, Jens Suessmeyer.|||You can't pass GO through ADO, as GO isn't a SQL command. For each GO, do an
.Execute of your
command object.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Pushkar" <pushkartiwari@.gmail.com> wrote in message news:uDUQl6h1FHA.736@.tk
2msftngp13.phx.gbl...
Hi,
I want to create a set of procedures through my application on SQL Server, t
hrough ADO.
My Scipts is something of the form:
Use DB1
Go
create procedure <procedurename>
as
< procedure text>
Go
Create Procedure <procedurename>
as
<procedure text>
Go
But when I execute this statement it gives error 'Error in line 2 Go'
Is there a way to excute this script in a single call or I have to make mult
iple calls.
Thanks
Pushkar|||Jens (Jens@.sqlserver2005.de) writes:

> Instead of GO use a semicol for this:
> Use DB1;
> create procedure <procedurename> ...
> HTH, Jens Suessmeyer.
>
use tempdb;
CREATE PROCEDURE blafs AS
SELECT 'Uhuh'
gives:
Server: Msg 111, Level 15, State 1, Line 2
'CREATE PROCEDURE' must be the first statement in a query batch.
; is a statement terminator, handled by SQL Server.
GO is a batch terminator, handled client-side by some query tools.
GO and ; fills different purposes.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Creating multiple rows from a field with a list

OK. I'm being sent some data with a varchar field called my_elements that contains a 'list' of data items e.g. "4950,1,0%4954,2,0%4955,1,1"
I want to separate this into three records with a my_element field:
"4950,1,0"
"4954,2,0"
"4955,1,1"
How can I do this in SQL? Here's a template for what I want:

create table my_table
(
my_id int,
my_elements varchar(8000)
)
insert into my_table values (1,'4950,1,0%4954,2,0%4955,1,1')

-- Now I need some SQL to produce to create rows like these:
-- 1,'4950,1,0'
-- 1,'4954,2,0'
-- 1,'4955,1,1'
--
-- Or even better, as 4 numeric fields per row:
-- 1,4950,1,0
-- 1,4954,2,0
-- 1,4955,1,1

Also, I'd like to aviod using cursors if I can.

Any help appreciated. Thanks!do you have a fixed or variable number of data items?|||OK So this example Sucks But Hey

It Works for your example

SELECT my_id,SUBSTRING(my_elements,0,9) FROM my_table
UNION
SELECT my_id,SUBSTRING(my_elements,10,8) FROM my_table
UNION
SELECT my_id,SUBSTRING(my_elements,19,9) FROM my_table

tehe

GW|||Originally posted by Paul Young
do you have a fixed or variable number of data items?

Yeah, sorry, I forgot to point out, a variable number of data items|||mmmm

wonder if we could work out the entire length of the field ie. total number of seperate records

Then

Iterate through a loop using local variables to build a dynamic SQL Statement incrementing the substring position as we go and adding the UNIONS then execute that.

It's a thought

Is the physical length of the Data Items Consistent ?|||or:

if object_id('TEMPDB..#my_table') is not null drop table #my_table

create table #my_table (
my_id int
, my_elements varchar(8000))

insert into #my_table values (1,'4950,1,0%4954,2,0%4955,1,1')
insert into #my_table values (2,'4850,1,0%4854,2,0%4855,1,1')
insert into #my_table values (3,'4750,1,0%4754,2,0%4755,1,1')
insert into #my_table values (4,'4650,1,0%4654,2,0%4655,1,1')
insert into #my_table values (5,'4550,1,0%4554,2,0%4555,1,1')

declare @.Tbl table(my_id int, my_element1 int, my_element2 int, my_element3 int)
declare @.my_id int, @.my_elements varchar(8000)
, @.RecordSeperator char(1), @.ItemSeperator char(1)
, @.my_element varchar(12)
, @.RecordPosition int, @.ItemPosition int, @.LastRecordPosition int, @.LastItemPosition int
, @.Int1 int, @.Int2 int, @.Int3 int, @.Int4 int

select @.RecordSeperator = '%'
, @.ItemSeperator = ','

select @.my_id = min(my_id) from #my_table
while (@.my_id is not null) begin
select @.my_elements = my_elements from #my_table where my_id = @.my_id

set @.LastRecordPosition = 1
set @.RecordPosition = charindex(@.RecordSeperator, @.my_elements, @.LastRecordPosition)
while (@.RecordPosition > 0) begin
set @.my_element = substring(@.my_elements,@.LastRecordPosition,@.Record Position-@.LastRecordPosition)

set @.LastItemPosition = 1
set @.ItemPosition = charindex(@.ItemSeperator, @.my_element, @.LastItemPosition)
set @.Int1 = cast(substring(@.my_element,@.LastItemPosition,@.Item Position-@.LastItemPosition) as int)

set @.LastItemPosition = @.ItemPosition + 1
set @.ItemPosition = charindex(@.ItemSeperator, @.my_element, @.LastItemPosition)
set @.Int2 = cast(substring(@.my_element,@.LastItemPosition,@.Item Position-@.LastItemPosition) as int)

set @.LastItemPosition = @.ItemPosition + 1
set @.ItemPosition = charindex(@.ItemSeperator, @.my_element, @.LastItemPosition)
set @.Int3 = cast(substring(@.my_element,@.LastItemPosition,len(@. my_element)) as int)

raiserror('ID: %d ''%s'' %d - %d - %d.',0,1,@.my_id,@.my_element,@.Int1,@.Int2,@.Int3)

insert into @.Tbl values(@.my_id, @.Int1, @.Int2, @.Int3)

set @.LastRecordPosition = @.RecordPosition + 1
set @.RecordPosition = charindex(@.RecordSeperator, @.my_elements, @.LastRecordPosition)

end

select @.my_id = min(my_id) from #my_table where my_id > @.my_id
end

select * from @.Tbl|||Wow! Looks good! Thank you Paul.