Recent Creating View

I know that most of the persons could not get me exactly
so i am again going to clarify my problem.
I want to create tables and views from inside the VB-code
using SQL-script. I can do this by reading a text (script)
file, storing the string in a variable say MYSQLSTR and
running it like CN.EXECUTE MYSQLSTR inside my code. Now I
have created the sql-script from enterprise manager which
includes GO for each create statement of the table. I run
it from inside my VB-Code. error raised which says 'GO' is
not recognizable. OK. I removed 'GO' from the file and run
it again as i described above. It is running fine and
creating tables in the database. This file, after removing
all the 'GOs' runs inside the query analyser too.
Now when I do this with VIEWS, same problems come and I
remove 'GO' but this time it says like (even inside query
analyser):
Server: Msg 156, Level 15, State 1, Procedure View1, Line
10
Incorrect syntax near the keyword 'SET'.
Server: Msg 111, Level 15, State 1, Procedure DmView1,
Line 16
'CREATE VIEW' must be the first statement in a query batch.
Now I have to create views at one go as i create all the
tables at one go by running the SQL-script through my code.
ur suggestions are welcome.
Billi98Please don't re-post. I provided an answer in your prior post.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"billi98" <anonymous@.discussions.microsoft.com> wrote in message
news:4c1001c42c3e$3d1d2050$a301280a@.phx.gbl...
> I know that most of the persons could not get me exactly
> so i am again going to clarify my problem.
> I want to create tables and views from inside the VB-code
> using SQL-script. I can do this by reading a text (script)
> file, storing the string in a variable say MYSQLSTR and
> running it like CN.EXECUTE MYSQLSTR inside my code. Now I
> have created the sql-script from enterprise manager which
> includes GO for each create statement of the table. I run
> it from inside my VB-Code. error raised which says 'GO' is
> not recognizable. OK. I removed 'GO' from the file and run
> it again as i described above. It is running fine and
> creating tables in the database. This file, after removing
> all the 'GOs' runs inside the query analyser too.
> Now when I do this with VIEWS, same problems come and I
> remove 'GO' but this time it says like (even inside query
> analyser):
> Server: Msg 156, Level 15, State 1, Procedure View1, Line
> 10
> Incorrect syntax near the keyword 'SET'.
> Server: Msg 111, Level 15, State 1, Procedure DmView1,
> Line 16
> 'CREATE VIEW' must be the first statement in a query batch.
> Now I have to create views at one go as i create all the
> tables at one go by running the SQL-script through my code.
> ur suggestions are welcome.
> Billi98
>|||Hi Bill,
Ok then 'Why the go statement didn't work'
The reason is that the GO statement only works through
osql and isql (or in this case Query Analyser), as your
running this through VB, the go statement isn't supported
so it doesn't work.
The second error is a standard sql error, which I don't
think has anything to do with the go statement.
Could you possibly post the first 30 lines of your code ?
Thanks
J
>--Original Message--
>I know that most of the persons could not get me exactly
>so i am again going to clarify my problem.
>I want to create tables and views from inside the VB-code
>using SQL-script. I can do this by reading a text
(script)
>file, storing the string in a variable say MYSQLSTR and
>running it like CN.EXECUTE MYSQLSTR inside my code. Now I
>have created the sql-script from enterprise manager which
>includes GO for each create statement of the table. I run
>it from inside my VB-Code. error raised which says 'GO'
is
>not recognizable. OK. I removed 'GO' from the file and
run
>it again as i described above. It is running fine and
>creating tables in the database. This file, after
removing
>all the 'GOs' runs inside the query analyser too.
>Now when I do this with VIEWS, same problems come and I
>remove 'GO' but this time it says like (even inside query
>analyser):
>Server: Msg 156, Level 15, State 1, Procedure View1, Line
>10
>Incorrect syntax near the keyword 'SET'.
>Server: Msg 111, Level 15, State 1, Procedure DmView1,
>Line 16
>'CREATE VIEW' must be the first statement in a query
batch.
>Now I have to create views at one go as i create all the
>tables at one go by running the SQL-script through my
code.
>ur suggestions are welcome.
>Billi98
>.
>

Recent Creating View

I know that most of the persons could not get me exactly
so i am again going to clarify my problem.
I want to create tables and views from inside the VB-code
using SQL-script. I can do this by reading a text (script)
file, storing the string in a variable say MYSQLSTR and
running it like CN.EXECUTE MYSQLSTR inside my code. Now I
have created the sql-script from enterprise manager which
includes GO for each create statement of the table. I run
it from inside my VB-Code. error raised which says 'GO' is
not recognizable. OK. I removed 'GO' from the file and run
it again as i described above. It is running fine and
creating tables in the database. This file, after removing
all the 'GOs' runs inside the query analyser too.
Now when I do this with VIEWS, same problems come and I
remove 'GO' but this time it says like (even inside query
analyser):
Server: Msg 156, Level 15, State 1, Procedure View1, Line
10
Incorrect syntax near the keyword 'SET'.
Server: Msg 111, Level 15, State 1, Procedure DmView1,
Line 16
'CREATE VIEW' must be the first statement in a query batch.
Now I have to create views at one go as i create all the
tables at one go by running the SQL-script through my code.
ur suggestions are welcome.
Billi98
Please don't re-post. I provided an answer in your prior post.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"billi98" <anonymous@.discussions.microsoft.com> wrote in message
news:4c1001c42c3e$3d1d2050$a301280a@.phx.gbl...
> I know that most of the persons could not get me exactly
> so i am again going to clarify my problem.
> I want to create tables and views from inside the VB-code
> using SQL-script. I can do this by reading a text (script)
> file, storing the string in a variable say MYSQLSTR and
> running it like CN.EXECUTE MYSQLSTR inside my code. Now I
> have created the sql-script from enterprise manager which
> includes GO for each create statement of the table. I run
> it from inside my VB-Code. error raised which says 'GO' is
> not recognizable. OK. I removed 'GO' from the file and run
> it again as i described above. It is running fine and
> creating tables in the database. This file, after removing
> all the 'GOs' runs inside the query analyser too.
> Now when I do this with VIEWS, same problems come and I
> remove 'GO' but this time it says like (even inside query
> analyser):
> Server: Msg 156, Level 15, State 1, Procedure View1, Line
> 10
> Incorrect syntax near the keyword 'SET'.
> Server: Msg 111, Level 15, State 1, Procedure DmView1,
> Line 16
> 'CREATE VIEW' must be the first statement in a query batch.
> Now I have to create views at one go as i create all the
> tables at one go by running the SQL-script through my code.
> ur suggestions are welcome.
> Billi98
>

Recent Creating View

I know that most of the persons could not get me exactly
so i am again going to clarify my problem.
I want to create tables and views from inside the VB-code
using SQL-script. I can do this by reading a text (script)
file, storing the string in a variable say MYSQLSTR and
running it like CN.EXECUTE MYSQLSTR inside my code. Now I
have created the sql-script from enterprise manager which
includes GO for each create statement of the table. I run
it from inside my VB-Code. error raised which says 'GO' is
not recognizable. OK. I removed 'GO' from the file and run
it again as i described above. It is running fine and
creating tables in the database. This file, after removing
all the 'GOs' runs inside the query analyser too.
Now when I do this with VIEWS, same problems come and I
remove 'GO' but this time it says like (even inside query
analyser):
Server: Msg 156, Level 15, State 1, Procedure View1, Line
10
Incorrect syntax near the keyword 'SET'.
Server: Msg 111, Level 15, State 1, Procedure DmView1,
Line 16
'CREATE VIEW' must be the first statement in a query batch.
Now I have to create views at one go as i create all the
tables at one go by running the SQL-script through my code.
ur suggestions are welcome.
Billi98Please don't re-post. I provided an answer in your prior post.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"billi98" <anonymous@.discussions.microsoft.com> wrote in message
news:4c1001c42c3e$3d1d2050$a301280a@.phx.gbl...
> I know that most of the persons could not get me exactly
> so i am again going to clarify my problem.
> I want to create tables and views from inside the VB-code
> using SQL-script. I can do this by reading a text (script)
> file, storing the string in a variable say MYSQLSTR and
> running it like CN.EXECUTE MYSQLSTR inside my code. Now I
> have created the sql-script from enterprise manager which
> includes GO for each create statement of the table. I run
> it from inside my VB-Code. error raised which says 'GO' is
> not recognizable. OK. I removed 'GO' from the file and run
> it again as i described above. It is running fine and
> creating tables in the database. This file, after removing
> all the 'GOs' runs inside the query analyser too.
> Now when I do this with VIEWS, same problems come and I
> remove 'GO' but this time it says like (even inside query
> analyser):
> Server: Msg 156, Level 15, State 1, Procedure View1, Line
> 10
> Incorrect syntax near the keyword 'SET'.
> Server: Msg 111, Level 15, State 1, Procedure DmView1,
> Line 16
> 'CREATE VIEW' must be the first statement in a query batch.
> Now I have to create views at one go as i create all the
> tables at one go by running the SQL-script through my code.
> ur suggestions are welcome.
> Billi98
>sql

Receiving this error since migrating to SQL 2000 from SQL 7

Three simple suggestions:
1- Run the DTS package manually and see where (Which step)
you get the error.
2- Create an output file through Package-->Properties--
>Logging in the DTS package and read the output file for
the error.
3- If you upgraded from SQL Server 7.0 to 2000, run
sp_updatestats stored proc for all the databases in the QA.

>--Original Message--
>Ever since migrating to SQL 2000 from SQL 7 I receive
this error at least
>once a day while running jobs through SQL Agent. It may
not be the same job
>having the problem. Any thoughts?
>Executed as user: domain\user. DTSRun: Loading...
Error: -2147467259
>(80004005); Provider Error: 0 (0) Error string:
Timeout expired
>Error source: Microsoft OLE DB Provider for SQL
Server Help file:
> Help context: 0. Process Exit Code 1. The step failed.
>.
>
The job is only failing once or twice a day even though it runs every hour or
in the case of another job, every 1/2 hour. It is never the same time of
day. I can run the DTS job manually and it work without failing. I will
give the logging a try and the sp_updatestats. I didn't upgrade the server -
it was a clean install and a restore from SQL 7. Thanks for your help
"Mark" wrote:

> Three simple suggestions:
> 1- Run the DTS package manually and see where (Which step)
> you get the error.
> 2- Create an output file through Package-->Properties--
> the error.
> 3- If you upgraded from SQL Server 7.0 to 2000, run
> sp_updatestats stored proc for all the databases in the QA.
>
>
> this error at least
> not be the same job
> Error: -2147467259
> Timeout expired
> Server Help file:
>

RECEIVE in transactions

Hello!
I have an an event notifications audit solution that I'm working on. I am
using this activation procedure.
CREATE PROCEDURE dbo.AuditReceive
AS
DECLARE @.AuditMessage TABLE (EventData xml);
RECEIVECAST(message_body AS XML)
FROM AuditQueue
INTO @.AuditMessage
INSERT INTO dbo.Audit (PostTime, EventType, LoginName, UserName, ServerName,
DatabaseName, ObjectType, SchemaName, ObjectName, EventData)
SELECTCAST(EventData.query('data(//PostTime)') AS varchar(23)),
CAST(EventData.query('data(//EventType)') AS sysname),
CAST(EventData.query('data(//LoginName)') AS sysname),
CAST(EventData.query('data(//UserName)') AS sysname),
CAST(EventData.query('data(//ServerName)') AS sysname),
CAST(EventData.query('data(//DatabaseName)') AS sysname),
CAST(EventData.query('data(//ObjectType)') AS sysname),
CAST(EventData.query('data(//SchemaName)') AS sysname),
CAST(EventData.query('data(//ObjectName)') AS sysname),
EventData
FROM @.AuditMessage
It's working nicely, but there is one weakness. I would like to have the
RECEIVE and the INSERT as a transaction, but I can't get it working. Is it
not possible to use RECEIVE in transactions?
Best regards
Ola Hallengren
the books on line tell:
"The RECEIVE statement removes received messages from the queue unless the
queue specifies message retention. When the RETENTION setting for the queue
is ON, the RECEIVE statement updates the status column to 1 and leaves the
messages in the queue. When a transaction that contains a RECEIVE statement
rolls back, all changes to the queue within the transaction are also rolled
back, returning messages to the queue."
Could you expose the source code of the transaction you tried? there's
something wrong but i cannot imagine what without seeing an example.
Gilberto Zampatti
"Ola Hallengren" wrote:

> Hello!
> I have an an event notifications audit solution that I'm working on. I am
> using this activation procedure.
> CREATE PROCEDURE dbo.AuditReceive
> AS
> DECLARE @.AuditMessage TABLE (EventData xml);
> RECEIVECAST(message_body AS XML)
> FROM AuditQueue
> INTO @.AuditMessage
> INSERT INTO dbo.Audit (PostTime, EventType, LoginName, UserName, ServerName,
> DatabaseName, ObjectType, SchemaName, ObjectName, EventData)
> SELECTCAST(EventData.query('data(//PostTime)') AS varchar(23)),
> CAST(EventData.query('data(//EventType)') AS sysname),
> CAST(EventData.query('data(//LoginName)') AS sysname),
> CAST(EventData.query('data(//UserName)') AS sysname),
> CAST(EventData.query('data(//ServerName)') AS sysname),
> CAST(EventData.query('data(//DatabaseName)') AS sysname),
> CAST(EventData.query('data(//ObjectType)') AS sysname),
> CAST(EventData.query('data(//SchemaName)') AS sysname),
> CAST(EventData.query('data(//ObjectName)') AS sysname),
> EventData
> FROM @.AuditMessage
> It's working nicely, but there is one weakness. I would like to have the
> RECEIVE and the INSERT as a transaction, but I can't get it working. Is it
> not possible to use RECEIVE in transactions?
> Best regards
> Ola Hallengren
|||> I would like to have the
> RECEIVE and the INSERT as a transaction, but I can't get it working.
In what way doesn't it work? Error messages? Something else?

> Is it
> not possible to use RECEIVE in transactions?
Yes, but you want to be careful. Say you determine that you can't process this message so you
rollback. Since messages are received in order, you will just read the same message next time.
Essentially, you end up in an endless loop. This is called a "poison message" and after 5 rollbacks
in a row, SB will disable the queue. I strongly suggest you get Roger Wolter's book on SB, this
topic is handled in chapter 8.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Ola Hallengren" <OlaHallengren@.discussions.microsoft.com> wrote in message
news:38D8CD3E-9452-4EAE-A300-DB0F9B3A6F53@.microsoft.com...
> Hello!
> I have an an event notifications audit solution that I'm working on. I am
> using this activation procedure.
> CREATE PROCEDURE dbo.AuditReceive
> AS
> DECLARE @.AuditMessage TABLE (EventData xml);
> RECEIVE CAST(message_body AS XML)
> FROM AuditQueue
> INTO @.AuditMessage
> INSERT INTO dbo.Audit (PostTime, EventType, LoginName, UserName, ServerName,
> DatabaseName, ObjectType, SchemaName, ObjectName, EventData)
> SELECT CAST(EventData.query('data(//PostTime)') AS varchar(23)),
> CAST(EventData.query('data(//EventType)') AS sysname),
> CAST(EventData.query('data(//LoginName)') AS sysname),
> CAST(EventData.query('data(//UserName)') AS sysname),
> CAST(EventData.query('data(//ServerName)') AS sysname),
> CAST(EventData.query('data(//DatabaseName)') AS sysname),
> CAST(EventData.query('data(//ObjectType)') AS sysname),
> CAST(EventData.query('data(//SchemaName)') AS sysname),
> CAST(EventData.query('data(//ObjectName)') AS sysname),
> EventData
> FROM @.AuditMessage
> It's working nicely, but there is one weakness. I would like to have the
> RECEIVE and the INSERT as a transaction, but I can't get it working. Is it
> not possible to use RECEIVE in transactions?
> Best regards
> Ola Hallengren
|||I overlooked one comment in Books Online"
"If the RECEIVE statement is not the first statement in a batch or stored
procedure, the preceding statement must be terminated with a semicolon (;),
the Transact-SQL statement terminator."
Now it's working.
About poisonous messages I have been doing some testing on that by changing
the datatypes in my auditing table, so that the insert fails. The queue then
gets deactivated as you write.
Now it's not so likely that the insert fails, since I have matching
datatypes. I mostly would like to have a transaction in case of a server
crash.
I have ordered the Roger Walters book today.
Thanks for you help.
/Ola
"Tibor Karaszi" wrote:

> In what way doesn't it work? Error messages? Something else?
>
> Yes, but you want to be careful. Say you determine that you can't process this message so you
> rollback. Since messages are received in order, you will just read the same message next time.
> Essentially, you end up in an endless loop. This is called a "poison message" and after 5 rollbacks
> in a row, SB will disable the queue. I strongly suggest you get Roger Wolter's book on SB, this
> topic is handled in chapter 8.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Ola Hallengren" <OlaHallengren@.discussions.microsoft.com> wrote in message
> news:38D8CD3E-9452-4EAE-A300-DB0F9B3A6F53@.microsoft.com...
>

RECEIVE in transactions

Hello!
I have an an event notifications audit solution that I'm working on. I am
using this activation procedure.
CREATE PROCEDURE dbo.AuditReceive
AS
DECLARE @.AuditMessage TABLE (EventData xml);
RECEIVE CAST(message_body AS XML)
FROM AuditQueue
INTO @.AuditMessage
INSERT INTO dbo.Audit (PostTime, EventType, LoginName, UserName, ServerName,
DatabaseName, ObjectType, SchemaName, ObjectName, EventData)
SELECT CAST(EventData.query('data(//PostTime)') AS varchar(23)),
CAST(EventData.query('data(//EventType)') AS sysname),
CAST(EventData.query('data(//LoginName)') AS sysname),
CAST(EventData.query('data(//UserName)') AS sysname),
CAST(EventData.query('data(//ServerName)') AS sysname),
CAST(EventData.query('data(//DatabaseName)') AS sysname),
CAST(EventData.query('data(//ObjectType)') AS sysname),
CAST(EventData.query('data(//SchemaName)') AS sysname),
CAST(EventData.query('data(//ObjectName)') AS sysname),
EventData
FROM @.AuditMessage
It's working nicely, but there is one weakness. I would like to have the
RECEIVE and the INSERT as a transaction, but I can't get it working. Is it
not possible to use RECEIVE in transactions?
Best regards
Ola Hallengrenthe books on line tell:
"The RECEIVE statement removes received messages from the queue unless the
queue specifies message retention. When the RETENTION setting for the queue
is ON, the RECEIVE statement updates the status column to 1 and leaves the
messages in the queue. When a transaction that contains a RECEIVE statement
rolls back, all changes to the queue within the transaction are also rolled
back, returning messages to the queue."
Could you expose the source code of the transaction you tried? there's
something wrong but i cannot imagine what without seeing an example.
Gilberto Zampatti
"Ola Hallengren" wrote:
> Hello!
> I have an an event notifications audit solution that I'm working on. I am
> using this activation procedure.
> CREATE PROCEDURE dbo.AuditReceive
> AS
> DECLARE @.AuditMessage TABLE (EventData xml);
> RECEIVE CAST(message_body AS XML)
> FROM AuditQueue
> INTO @.AuditMessage
> INSERT INTO dbo.Audit (PostTime, EventType, LoginName, UserName, ServerName,
> DatabaseName, ObjectType, SchemaName, ObjectName, EventData)
> SELECT CAST(EventData.query('data(//PostTime)') AS varchar(23)),
> CAST(EventData.query('data(//EventType)') AS sysname),
> CAST(EventData.query('data(//LoginName)') AS sysname),
> CAST(EventData.query('data(//UserName)') AS sysname),
> CAST(EventData.query('data(//ServerName)') AS sysname),
> CAST(EventData.query('data(//DatabaseName)') AS sysname),
> CAST(EventData.query('data(//ObjectType)') AS sysname),
> CAST(EventData.query('data(//SchemaName)') AS sysname),
> CAST(EventData.query('data(//ObjectName)') AS sysname),
> EventData
> FROM @.AuditMessage
> It's working nicely, but there is one weakness. I would like to have the
> RECEIVE and the INSERT as a transaction, but I can't get it working. Is it
> not possible to use RECEIVE in transactions?
> Best regards
> Ola Hallengren|||> I would like to have the
> RECEIVE and the INSERT as a transaction, but I can't get it working.
In what way doesn't it work? Error messages? Something else?
> Is it
> not possible to use RECEIVE in transactions?
Yes, but you want to be careful. Say you determine that you can't process this message so you
rollback. Since messages are received in order, you will just read the same message next time.
Essentially, you end up in an endless loop. This is called a "poison message" and after 5 rollbacks
in a row, SB will disable the queue. I strongly suggest you get Roger Wolter's book on SB, this
topic is handled in chapter 8.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Ola Hallengren" <OlaHallengren@.discussions.microsoft.com> wrote in message
news:38D8CD3E-9452-4EAE-A300-DB0F9B3A6F53@.microsoft.com...
> Hello!
> I have an an event notifications audit solution that I'm working on. I am
> using this activation procedure.
> CREATE PROCEDURE dbo.AuditReceive
> AS
> DECLARE @.AuditMessage TABLE (EventData xml);
> RECEIVE CAST(message_body AS XML)
> FROM AuditQueue
> INTO @.AuditMessage
> INSERT INTO dbo.Audit (PostTime, EventType, LoginName, UserName, ServerName,
> DatabaseName, ObjectType, SchemaName, ObjectName, EventData)
> SELECT CAST(EventData.query('data(//PostTime)') AS varchar(23)),
> CAST(EventData.query('data(//EventType)') AS sysname),
> CAST(EventData.query('data(//LoginName)') AS sysname),
> CAST(EventData.query('data(//UserName)') AS sysname),
> CAST(EventData.query('data(//ServerName)') AS sysname),
> CAST(EventData.query('data(//DatabaseName)') AS sysname),
> CAST(EventData.query('data(//ObjectType)') AS sysname),
> CAST(EventData.query('data(//SchemaName)') AS sysname),
> CAST(EventData.query('data(//ObjectName)') AS sysname),
> EventData
> FROM @.AuditMessage
> It's working nicely, but there is one weakness. I would like to have the
> RECEIVE and the INSERT as a transaction, but I can't get it working. Is it
> not possible to use RECEIVE in transactions?
> Best regards
> Ola Hallengren|||I overlooked one comment in Books Online"
"If the RECEIVE statement is not the first statement in a batch or stored
procedure, the preceding statement must be terminated with a semicolon (;),
the Transact-SQL statement terminator."
Now it's working.
About poisonous messages I have been doing some testing on that by changing
the datatypes in my auditing table, so that the insert fails. The queue then
gets deactivated as you write.
Now it's not so likely that the insert fails, since I have matching
datatypes. I mostly would like to have a transaction in case of a server
crash.
I have ordered the Roger Walters book today.
Thanks for you help.
/Ola
"Tibor Karaszi" wrote:
> > I would like to have the
> > RECEIVE and the INSERT as a transaction, but I can't get it working.
> In what way doesn't it work? Error messages? Something else?
>
> > Is it
> > not possible to use RECEIVE in transactions?
> Yes, but you want to be careful. Say you determine that you can't process this message so you
> rollback. Since messages are received in order, you will just read the same message next time.
> Essentially, you end up in an endless loop. This is called a "poison message" and after 5 rollbacks
> in a row, SB will disable the queue. I strongly suggest you get Roger Wolter's book on SB, this
> topic is handled in chapter 8.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Ola Hallengren" <OlaHallengren@.discussions.microsoft.com> wrote in message
> news:38D8CD3E-9452-4EAE-A300-DB0F9B3A6F53@.microsoft.com...
> > Hello!
> >
> > I have an an event notifications audit solution that I'm working on. I am
> > using this activation procedure.
> >
> > CREATE PROCEDURE dbo.AuditReceive
> > AS
> > DECLARE @.AuditMessage TABLE (EventData xml);
> >
> > RECEIVE CAST(message_body AS XML)
> > FROM AuditQueue
> > INTO @.AuditMessage
> >
> > INSERT INTO dbo.Audit (PostTime, EventType, LoginName, UserName, ServerName,
> > DatabaseName, ObjectType, SchemaName, ObjectName, EventData)
> > SELECT CAST(EventData.query('data(//PostTime)') AS varchar(23)),
> > CAST(EventData.query('data(//EventType)') AS sysname),
> > CAST(EventData.query('data(//LoginName)') AS sysname),
> > CAST(EventData.query('data(//UserName)') AS sysname),
> > CAST(EventData.query('data(//ServerName)') AS sysname),
> > CAST(EventData.query('data(//DatabaseName)') AS sysname),
> > CAST(EventData.query('data(//ObjectType)') AS sysname),
> > CAST(EventData.query('data(//SchemaName)') AS sysname),
> > CAST(EventData.query('data(//ObjectName)') AS sysname),
> > EventData
> > FROM @.AuditMessage
> >
> > It's working nicely, but there is one weakness. I would like to have the
> > RECEIVE and the INSERT as a transaction, but I can't get it working. Is it
> > not possible to use RECEIVE in transactions?
> >
> > Best regards
> >
> > Ola Hallengren
>sql

RECEIVE in transactions

Hello!
I have an an event notifications audit solution that I'm working on. I am
using this activation procedure.
CREATE PROCEDURE dbo.AuditReceive
AS
DECLARE @.AuditMessage TABLE (EventData xml);
RECEIVE CAST(message_body AS XML)
FROM AuditQueue
INTO @.AuditMessage
INSERT INTO dbo.Audit (PostTime, EventType, LoginName, UserName, ServerName,
DatabaseName, ObjectType, SchemaName, ObjectName, EventData)
SELECT CAST(EventData.query('data(//PostTime)') AS varchar(23)),
CAST(EventData.query('data(//EventType)') AS sysname),
CAST(EventData.query('data(//LoginName)') AS sysname),
CAST(EventData.query('data(//UserName)') AS sysname),
CAST(EventData.query('data(//ServerName)') AS sysname),
CAST(EventData.query('data(//DatabaseName)') AS sysname),
CAST(EventData.query('data(//ObjectType)') AS sysname),
CAST(EventData.query('data(//SchemaName)') AS sysname),
CAST(EventData.query('data(//ObjectName)') AS sysname),
EventData
FROM @.AuditMessage
It's working nicely, but there is one weakness. I would like to have the
RECEIVE and the INSERT as a transaction, but I can't get it working. Is it
not possible to use RECEIVE in transactions?
Best regards
Ola Hallengrenthe books on line tell:
"The RECEIVE statement removes received messages from the queue unless the
queue specifies message retention. When the RETENTION setting for the queue
is ON, the RECEIVE statement updates the status column to 1 and leaves the
messages in the queue. When a transaction that contains a RECEIVE statement
rolls back, all changes to the queue within the transaction are also rolled
back, returning messages to the queue."
Could you expose the source code of the transaction you tried? there's
something wrong but i cannot imagine what without seeing an example.
Gilberto Zampatti
"Ola Hallengren" wrote:

> Hello!
> I have an an event notifications audit solution that I'm working on. I am
> using this activation procedure.
> CREATE PROCEDURE dbo.AuditReceive
> AS
> DECLARE @.AuditMessage TABLE (EventData xml);
> RECEIVE CAST(message_body AS XML)
> FROM AuditQueue
> INTO @.AuditMessage
> INSERT INTO dbo.Audit (PostTime, EventType, LoginName, UserName, ServerNam
e,
> DatabaseName, ObjectType, SchemaName, ObjectName, EventData)
> SELECT CAST(EventData.query('data(//PostTime)') AS varchar(23)),
> CAST(EventData.query('data(//EventType)') AS sysname),
> CAST(EventData.query('data(//LoginName)') AS sysname),
> CAST(EventData.query('data(//UserName)') AS sysname),
> CAST(EventData.query('data(//ServerName)') AS sysname),
> CAST(EventData.query('data(//DatabaseName)') AS sysname),
> CAST(EventData.query('data(//ObjectType)') AS sysname),
> CAST(EventData.query('data(//SchemaName)') AS sysname),
> CAST(EventData.query('data(//ObjectName)') AS sysname),
> EventData
> FROM @.AuditMessage
> It's working nicely, but there is one weakness. I would like to have the
> RECEIVE and the INSERT as a transaction, but I can't get it working. Is it
> not possible to use RECEIVE in transactions?
> Best regards
> Ola Hallengren|||> I would like to have the
> RECEIVE and the INSERT as a transaction, but I can't get it working.
In what way doesn't it work? Error messages? Something else?

> Is it
> not possible to use RECEIVE in transactions?
Yes, but you want to be careful. Say you determine that you can't process th
is message so you
rollback. Since messages are received in order, you will just read the same
message next time.
Essentially, you end up in an endless loop. This is called a "poison message
" and after 5 rollbacks
in a row, SB will disable the queue. I strongly suggest you get Roger Wolter
's book on SB, this
topic is handled in chapter 8.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Ola Hallengren" <OlaHallengren@.discussions.microsoft.com> wrote in message
news:38D8CD3E-9452-4EAE-A300-DB0F9B3A6F53@.microsoft.com...
> Hello!
> I have an an event notifications audit solution that I'm working on. I am
> using this activation procedure.
> CREATE PROCEDURE dbo.AuditReceive
> AS
> DECLARE @.AuditMessage TABLE (EventData xml);
> RECEIVE CAST(message_body AS XML)
> FROM AuditQueue
> INTO @.AuditMessage
> INSERT INTO dbo.Audit (PostTime, EventType, LoginName, UserName, ServerNam
e,
> DatabaseName, ObjectType, SchemaName, ObjectName, EventData)
> SELECT CAST(EventData.query('data(//PostTime)') AS varchar(23)),
> CAST(EventData.query('data(//EventType)') AS sysname),
> CAST(EventData.query('data(//LoginName)') AS sysname),
> CAST(EventData.query('data(//UserName)') AS sysname),
> CAST(EventData.query('data(//ServerName)') AS sysname),
> CAST(EventData.query('data(//DatabaseName)') AS sysname),
> CAST(EventData.query('data(//ObjectType)') AS sysname),
> CAST(EventData.query('data(//SchemaName)') AS sysname),
> CAST(EventData.query('data(//ObjectName)') AS sysname),
> EventData
> FROM @.AuditMessage
> It's working nicely, but there is one weakness. I would like to have the
> RECEIVE and the INSERT as a transaction, but I can't get it working. Is it
> not possible to use RECEIVE in transactions?
> Best regards
> Ola Hallengren|||I overlooked one comment in Books Online"
"If the RECEIVE statement is not the first statement in a batch or stored
procedure, the preceding statement must be terminated with a semicolon (;),
the Transact-SQL statement terminator."
Now it's working.
About poisonous messages I have been doing some testing on that by changing
the datatypes in my auditing table, so that the insert fails. The queue then
gets deactivated as you write.
Now it's not so likely that the insert fails, since I have matching
datatypes. I mostly would like to have a transaction in case of a server
crash.
I have ordered the Roger Walters book today.
Thanks for you help.
/Ola
"Tibor Karaszi" wrote:

> In what way doesn't it work? Error messages? Something else?
>
> Yes, but you want to be careful. Say you determine that you can't process
this message so you
> rollback. Since messages are received in order, you will just read the sam
e message next time.
> Essentially, you end up in an endless loop. This is called a "poison messa
ge" and after 5 rollbacks
> in a row, SB will disable the queue. I strongly suggest you get Roger Wolt
er's book on SB, this
> topic is handled in chapter 8.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Ola Hallengren" <OlaHallengren@.discussions.microsoft.com> wrote in messag
e
> news:38D8CD3E-9452-4EAE-A300-DB0F9B3A6F53@.microsoft.com...
>

Receive all messages on queue

Hi i am trying to create a batch process then commit for all messages on the queue. The problem i am having is when i run my query (As below) I only receive the first message and the corresponding end dialog for the message although i have 2000 records sitting in the queue. It is my understanding that receive without any criteria i.e top(1) or where clause should select everything of the queue. I tried receive top(100) expecting 100 records but still only got 2 back.

any help appreciated.

WAITFOR(RECEIVE

queuing_order,

conversation_handle,

message_type_name,

message_body

FROM [RMIS_COMMS_Queue]

INTO @.tableMessages), TIMEOUT 2000;

Each RECEIVE returns only messages belonging to conversation in one conversation group only. If each conversation is its own group (which is true unless you use RELATED_CONVERSATION clause in BEGIN DIALOG or you use MOVE CONVERSATION) then it means you can RECEIVE only one conversation in one call. The TOP clause applies to this resultset (one conversation).

HTH,
~ Remus

|||Thanks for that. Can i just refer to your article on batch processing messages (the example with the cursor) this is the method i am try ing to implement but my scenario is many shops with many tills, where each message/conversation is a complete sale from a till i.e many conversations with one message. Is it safe to say this is not the best method for trying to speed this up, what would be the best way to process these messages, bearing in mind sometimes a shop can lose comms and send down a batch of sales when it reconnects.|||

I'm using triggers to cause an update of a seperate database via service broker, in our activated stored procedure we are seeing similiar situations, Could you give an example of a script that a trigger might use to take advantage of the RELATED_CONVERSATION clause? I looked at the documentation and didn't see how I could use this in our situation. (trigger on update sends inserted table with for xml clause as message to queue)

Thanks,

Bill

Receive all messages on queue

Hi i am trying to create a batch process then commit for all messages on the queue. The problem i am having is when i run my query (As below) I only receive the first message and the corresponding end dialog for the message although i have 2000 records sitting in the queue. It is my understanding that receive without any criteria i.e top(1) or where clause should select everything of the queue. I tried receive top(100) expecting 100 records but still only got 2 back.

any help appreciated.

WAITFOR(RECEIVE

queuing_order,

conversation_handle,

message_type_name,

message_body

FROM [RMIS_COMMS_Queue]

INTO @.tableMessages), TIMEOUT 2000;

Each RECEIVE returns only messages belonging to conversation in one conversation group only. If each conversation is its own group (which is true unless you use RELATED_CONVERSATION clause in BEGIN DIALOG or you use MOVE CONVERSATION) then it means you can RECEIVE only one conversation in one call. The TOP clause applies to this resultset (one conversation).

HTH,
~ Remus

|||Thanks for that. Can i just refer to your article on batch processing messages (the example with the cursor) this is the method i am try ing to implement but my scenario is many shops with many tills, where each message/conversation is a complete sale from a till i.e many conversations with one message. Is it safe to say this is not the best method for trying to speed this up, what would be the best way to process these messages, bearing in mind sometimes a shop can lose comms and send down a batch of sales when it reconnects.|||

I'm using triggers to cause an update of a seperate database via service broker, in our activated stored procedure we are seeing similiar situations, Could you give an example of a script that a trigger might use to take advantage of the RELATED_CONVERSATION clause? I looked at the documentation and didn't see how I could use this in our situation. (trigger on update sends inserted table with for xml clause as message to queue)

Thanks,

Bill

Rebuild Master fails

Hi,
I am trying to test disaster recovery on a test server. I am trying to
create the master as though it has been corrupted. When I run the
Rebuildm.exe, it creates the master.mdf and mastlog.ldf files in the
C:\Program Files\MS SQL SERVER\DATA\MSSQL\Data directory from the
installation CD but it fails with an error -" Rebuild Master failed with
error -1:" and in the windows application event error log it says 17204
:FCB::Open failed: Could not open device C:\Program Files\Microsoft SQL
Server\DATA\MSSQL\data\master.mdf for virtual device number (VDN) 1.
I am not able to start the MSSQLSERVR service. I have tried three times
rebuilding the master but it has failed with the same error. Any help will be
greatly appreciated. Thanks.Hi,
I looked at the file attributes and unchecked the READ ONLY for the data and
log files for master, model and msdb databases. Now when I start the
MSSQLSERVR service it runs for just 1 - 2 seconds and then it stops. What
might be causing this? Thanks.
"sharman" wrote:
> Hi,
> I am trying to test disaster recovery on a test server. I am trying to
> create the master as though it has been corrupted. When I run the
> Rebuildm.exe, it creates the master.mdf and mastlog.ldf files in the
> C:\Program Files\MS SQL SERVER\DATA\MSSQL\Data directory from the
> installation CD but it fails with an error -" Rebuild Master failed with
> error -1:" and in the windows application event error log it says 17204
> :FCB::Open failed: Could not open device C:\Program Files\Microsoft SQL
> Server\DATA\MSSQL\data\master.mdf for virtual device number (VDN) 1.
> I am not able to start the MSSQLSERVR service. I have tried three times
> rebuilding the master but it has failed with the same error. Any help will be
> greatly appreciated. Thanks.|||> Now when I start the
> MSSQLSERVR service it runs for just 1 - 2 seconds and then it stops. What
> might be causing this?
Just about anything. Check the errorlog file for error messages.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"sharman" <sharman@.discussions.microsoft.com> wrote in message
news:1F7F6AB4-A231-4AA3-B4B4-E4807F0E4B7F@.microsoft.com...
> Hi,
> I looked at the file attributes and unchecked the READ ONLY for the data and
> log files for master, model and msdb databases. Now when I start the
> MSSQLSERVR service it runs for just 1 - 2 seconds and then it stops. What
> might be causing this? Thanks.
> "sharman" wrote:
>> Hi,
>> I am trying to test disaster recovery on a test server. I am trying to
>> create the master as though it has been corrupted. When I run the
>> Rebuildm.exe, it creates the master.mdf and mastlog.ldf files in the
>> C:\Program Files\MS SQL SERVER\DATA\MSSQL\Data directory from the
>> installation CD but it fails with an error -" Rebuild Master failed with
>> error -1:" and in the windows application event error log it says 17204
>> :FCB::Open failed: Could not open device C:\Program Files\Microsoft SQL
>> Server\DATA\MSSQL\data\master.mdf for virtual device number (VDN) 1.
>> I am not able to start the MSSQLSERVR service. I have tried three times
>> rebuilding the master but it has failed with the same error. Any help will be
>> greatly appreciated. Thanks.|||Hi,
I was able to figure out the reason. The source file location for the mdf
and ldf files was on a CD with the Read Only attribute checked. I copied all
the files to a local disk, unchecked the Read Only attribute and rerun the
rebuildm.exe and it worked this time. This is in Microsoft KB 273572.
"Tibor Karaszi" wrote:
> > Now when I start the
> > MSSQLSERVR service it runs for just 1 - 2 seconds and then it stops. What
> > might be causing this?
> Just about anything. Check the errorlog file for error messages.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "sharman" <sharman@.discussions.microsoft.com> wrote in message
> news:1F7F6AB4-A231-4AA3-B4B4-E4807F0E4B7F@.microsoft.com...
> > Hi,
> >
> > I looked at the file attributes and unchecked the READ ONLY for the data and
> > log files for master, model and msdb databases. Now when I start the
> > MSSQLSERVR service it runs for just 1 - 2 seconds and then it stops. What
> > might be causing this? Thanks.
> >
> > "sharman" wrote:
> >
> >> Hi,
> >>
> >> I am trying to test disaster recovery on a test server. I am trying to
> >> create the master as though it has been corrupted. When I run the
> >> Rebuildm.exe, it creates the master.mdf and mastlog.ldf files in the
> >> C:\Program Files\MS SQL SERVER\DATA\MSSQL\Data directory from the
> >> installation CD but it fails with an error -" Rebuild Master failed with
> >> error -1:" and in the windows application event error log it says 17204
> >> :FCB::Open failed: Could not open device C:\Program Files\Microsoft SQL
> >> Server\DATA\MSSQL\data\master.mdf for virtual device number (VDN) 1.
> >>
> >> I am not able to start the MSSQLSERVR service. I have tried three times
> >> rebuilding the master but it has failed with the same error. Any help will be
> >> greatly appreciated. Thanks.
>

Rebuilbm not working

Hello
I have:
Windows 2000 server with SP3, SQL standard SP2
After a NT backup restore I want to use REBUILDM.EXE to create the system databases in order to restore the databases.
I get the error “Rebuild Master failed with error -1:”
I am aware of KB 273572 and I have a copy of the original files into a folder on the server. The files are not “Read-only”
But I get the error. Did some research…no luck.
If you experienced this please let me know.
Thanks a lot
Look at the SQL Server errolog and verify that Shared Memory is loaded. If
not, reboot the machine. Rebuildm makes a shared memory connection so if
shared memory is not loaded it will fail.
Rand
This posting is provided "as is" with no warranties and confers no rights.

Rebuilbm not working

Hello
I have:
Windows 2000 server with SP3, SQL standard SP2
After a NT backup restore I want to use REBUILDM.EXE to create the system da
tabases in order to restore the databases.
I get the error “Rebuild Master failed with error -1:”
I am aware of KB 273572 and I have a copy of the original files into a folde
r on the server. The files are not “Read-only”
But I get the error. Did some research…no luck.
If you experienced this please let me know.
Thanks a lotLook at the SQL Server errolog and verify that Shared Memory is loaded. If
not, reboot the machine. Rebuildm makes a shared memory connection so if
shared memory is not loaded it will fail.
Rand
This posting is provided "as is" with no warranties and confers no rights.

Reasonable to assemble FullName W/ A UDF

I break somone's name in a table in fields as First, Last, Middle,
Suffix.

Is it reasonable to use a UDF to create the full name as a string if I
need it, by passing it the First, Last, Middle, & Suffix fields?

Is this really slow to do? It's just sometimes I want to do a GroupBy
Query and I want to include the person's name in the Select of the
query and I don't know if the group by would work if I returned each
column seperately.

Any opinions on this?

Thanks.Using the function might be useful for consistency. I have worked in
databases where we have identical code all over the place: e.g.
LastName + ', ' + FirstName + ' ' + MiddleName.

I function would be nice for that. As far as speed goes, It may have a
negative effect.

Another option is to create a view of your data that has a FullName
column based on the others.|||Actually a computed columns works really nice and is probably easier to
use than a UDF. That way you can change the logic anytime you want and
don't have to change any application code.

if(not exists(
select syscolumns.name from sysobjects INNER JOIN syscolumns ON
sysobjects.id = syscolumns.id
where sysobjects.name = 'Users' AND syscolumns.name = 'DisplayName'
))
begin
alter table Users add DisplayName as FirstName + ' ' + LastName + ' ('
+ Company + ')'
end

if(not exists(
select syscolumns.name from sysobjects INNER JOIN syscolumns ON
sysobjects.id = syscolumns.id
where sysobjects.name = 'Users' AND syscolumns.name =
'SimpleDisplayName'
))
begin
alter table Users add SimpleDisplayName as FirstName + ' ' + LastName
end

if(not exists(
select syscolumns.name from sysobjects INNER JOIN syscolumns ON
sysobjects.id = syscolumns.id
where sysobjects.name = 'Users' AND syscolumns.name =
'ReverseSimpleDisplayName'
))
begin
alter table Users add ReverseSimpleDisplayName as LastName + ', ' +
FirstName
end

Realtional Tables

For starters, please forgive my newbie'ness as SQL is not my strongest area in any sense.

I am trying to create a web application which consists of a DDL which is databound and based on the selection in the 1st DDL, a gridview should be generated and populated with the criteria I'm looking for.

My trouble starts with the database. I have 2 tables that I'm working with - users and messages.

The users table consists of 2 columns; userid and useruri. The messages table consists of 2 tables; userid, body.

The first drop down list is populated from a database call of SELECT useruri, userid FROM users. The output of this DDL is simply each user's name. This works pefectly.

To build the gridview or output (because i'm not sure if gridview is the right word), the second table, messages, needs to be queried but I can't figure out how to take the userid from the users table and match it to the userid of the messages table, based on the selection in the DDL of the useruri, to build the view.

Does that make sense? Any help would be greatly appreciated.

Thanks,

Casey

You could use SqlDataSource controls and do it something like this:

User:<asp:dropdownlist id="ddlUsers" runat="server" autopostback="True" datasourceid="sdsUsers"datatextfield="Name" datavaluefield="Id" /><asp:sqldatasource id="sdsUsers" runat="server"connectionstring="<%$ ConnectionStrings:ed_testConnectionString%>" selectcommand="SELECT * FROM [User]"></asp:sqldatasource>Message:<asp:dropdownlist id="ddlMessages" runat="server" datasourceid="sdsMessages" datatextfield="Message"datavaluefield="msgId" /><asp:sqldatasource id="sdsMessages" runat="server" connectionstring="<%$ ConnectionStrings:ed_testConnectionString%>"selectcommand="SELECT [msgId], [Message] FROM [Message] WHERE ([Id] = @.Id)"><selectparameters><asp:controlparameter controlid="ddlUsers" name="Id" propertyname="SelectedValue"type="Int32" /></selectparameters></asp:sqldatasource>

Really urgent - Data field retrieve and store

Basically i make use of FormView in Web Developer Express Edition to create and store information to the database ...

what i want to do further is to capture the value within the primary key of the newly created recordset that I set it become auto increament ....

this value would be stored within a session string in global condition that allowed any page able to access it ....

If you simply want to return the ID to use within the application, and you are using a stored procedure, do this.

Insert the record as normal in the table, then add a statement such as SELECT SCOPE_IDENETITY() as TheNewId

If your insert fails, this line never executes. If insert is OK, you get teh new ID passed back

In your code, perform an execute scaler method call on the connection, and cast the return (decimal as object) into an integer, and store in session. If the returned value is null, the insert failed

What I often create for projects is a global "Execute Scaler" method that accepts the name of a stored procedure, and a string dictionary of name/value pairs.

IN the method, create the SQL Command object, call the SQLCommandBuilder static method to get the parameters, then iterate through the dictionary to add these by name.

Call the command ExecuteScaler as mentioned and proceed as above

|||Really appreciate ur feedback men... but it looks so technical for me men... i am totally a new beginner but have to complete this project within another week time...

can u demonstrate the code (sorry for the hassle)... i kind of not sure how to achieve what u had mentioned...|||

Example SP

CREATE PROCEDURE

dbo.MyTable_Insert

(

@.Parameter1varchar(100)

)

AS

SET NOCOUNT ON

INSERT INTOMyTable (MyData)VALUES(@.Parameter1)

SELECT SCOPE_IDENTITY()asTheNewId

Example Code

int InsertRecord(string insertValue)

{

// assumes you have set cnn as a suitable connection

SqlCommand cmd =newSqlCommand("MyTable_Insert", cnn);

cmd.CommandType = System.Data.CommandType.StoredProcedure;

cmd.Parameters["@.Parameter1"].Value = insertValue;

object retObject = cmd.ExecuteScalar();

if (retObject !=null)

return (int)retObject;

else

return 0;// no new record

}

Really need help w/ what should be simple question!

Hello,

Basically, all I am trying to do is create an SSIS pkg that will read a list of stored procedures from a table. I set up an EXECUTE SQL TASK to do this. I am saving the result set to a variable (type object).

Next, I call a FOREACHLOOP container, which references the variable from the EXECUTE SQL TASK.

But I'm not sure what to do next? I just want the FOR EACH LOOP to simply execute the stored procedure name as given by the first EXECUTE SQL TASK.

I was thinking to put another EXECUTE SQL TASK inside the FOR EACH LOOP, but I don't know how to reference the stored procedure name correctly, or if this is even allowable.

I am not a scripter :-)

Can you please offer me some advice on what to do next? I am desperately seeking a solution.

Thank you!!!

In the Variable Mappings tab of your For Each container, make sure that you are mapping the necessary columns (like the stored proc name) of your resultset to variables.

Create a new variable to hold the call to the stored procedure. In the properties for the variable, make sure EvaluateAsExpression is true. In the Expression property, construct the string for a call to the stored proc, using the variables you mapped in the For Each container. Add an Execute SQL task in your For Each container, and set the SQLSourceType to variable. Then pick the variable you created as the SourceVariable.

If you wait a bit, I'll post an example on my blog.

|||

Example:

http://agilebi.com/cs/blogs/jwelch/archive/2007/03/21/using-for-each-to-iterate-a-resultset.aspx

The sample package is attached to the post.

Hope this helps.

|||

Hi John,

I looked *everywhere*, and I honestly don't see where to set "EvaluateAsExpression" is true. That is, where in the world do I set the properties for the variable in the FOREACH containter editor? I really don't see this anywhere.

I also looked at your example from your post, and I don't see this anywhere, either?

Thanks

|||

Hi John,

I examined your example very carefully. However, I don't know what you mean by the statement:

There is a variable named "select". This variable uses an expression to build the following select statement: "SELECT ID, ParentID, ChildName FROM ChildTable WHERE ParentID = " + the "parentID" variable. The "select" variable is then used in another Execute SQL task.

Where are you building the expression? I don't see it.

Thanks

|||Select the variable in the variables window (not in the container editor) and hit F4. This should bring up the properties for the variable. EvaluateAsExpression is an option in that window. This is also where the expression is defined.|||

Are you talking about the DataObjectVariable under EnumeratorProperties in the Properties of the ForEachLoop container?

I see my the name of my ADO Object Source Variable (User::Reports) , but not the mapped column (User::report_name) > which contains the name of the stored procedure.

But hitting F4 does not bring up anything.

?

Thanks

|||

I think I may be getting "closer", in spite of things probably not being set correctly at this point.

So far I have an EXEC SQL task >> FOREACHLOOP >> EXEC SQL task.

And it appears it is actually trying to RUN the stored procedure names. But I am getting this error. Do you think this has to do with my not setting the EvaluateAsExpression? Or something else? Thanks!

SSIS package "Migration - Run Report Comparison.dtsx" starting.

Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "MY_STORED_PROC" failed with the following error: "Parameter name is unrecognized.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Task failed: Execute SQL Task

Warning: 0x80019002 at Foreach Loop Container: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

Warning: 0x80019002 at Migration - Run Report Comparison: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

|||No, I'm talking about the window where you originally define the variable. On the menu bar for Visual Studio, choose SSIS..Variables, and you should see a list of variables defined in your package. You may need to click the "Show All Variables" button, as some variables may be scoped to specific items in the package.|||

Yes, I see it now, thanks so much for explaining this. I am very new to SSIS and packages in general.

So I set the variable which contains the stored procedure name EvaluateAsExpression as True.

However, I am still getting aforementioned error.

|||

Ha!

Figured it out. The devil is in the details as they say!

What was missing was that in the Parameter Mapping tab of the 2nd EXEC SQL TASK was that I needed to set the Parameter Name to 0.

I still don't fully understand the meaning of all the 0's, although I think it has something do with how OLEDB does things.

Anyhow, it's working... now onto step 2 - write outputs of sp's to name files.

Thanks again

Your example was very useful!!

|||

Could you mark the question as answered? Makes it easier for others to find answers for similar questions.

Thanks.

Real time Replication with ms sql

hello,

I want to create real time replication of my databaser server.I have two database srevers,one is master which having real time data and another slave,where i want to replicate data from master .

plz help me to configure solution

Thanks,

Chetan S. Raut.

what you need is Transactional Replicaton. Read about this in BOL

http://www.databasejournal.com/features/mssql/article.php/1438201

http://www.mssqlcity.com/Articles/Replic/SetupTR/SetupTR.htm

Madhu

Ready Only Access

Hello,
How do I create a user in a DB and have them Read Only? Is there a QA
script maybe?
TIA
J
You can try adding them to the datareader and denydatawriter roles.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"J Ewing" <nonprivateaddress@.hotmail.com> wrote in message
news:Op7hYSsMEHA.3348@.TK2MSFTNGP09.phx.gbl...
> Hello,
> How do I create a user in a DB and have them Read Only? Is there a QA
> script maybe?
> TIA
> J
>

Ready Only Access

Hello,
How do I create a user in a DB and have them Read Only? Is there a QA
script maybe?
TIA
JYou can try adding them to the datareader and denydatawriter roles.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"J Ewing" <nonprivateaddress@.hotmail.com> wrote in message
news:Op7hYSsMEHA.3348@.TK2MSFTNGP09.phx.gbl...
> Hello,
> How do I create a user in a DB and have them Read Only? Is there a QA
> script maybe?
> TIA
> J
>

Ready Only Access

Hello,
How do I create a user in a DB and have them Read Only? Is there a QA
script maybe?
TIA
JYou can try adding them to the datareader and denydatawriter roles.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"J Ewing" <nonprivateaddress@.hotmail.com> wrote in message
news:Op7hYSsMEHA.3348@.TK2MSFTNGP09.phx.gbl...
> Hello,
> How do I create a user in a DB and have them Read Only? Is there a QA
> script maybe?
> TIA
> J
>