Script method parameters

Hi.
Does anyone have details of the parameters that can be provided to the
Script method? SQL Books online lists some of the ScriptType options, but it
is an incomplete list; I have been handed a legacy script that uses
.script(333,<filename> ), where 33 is not listed in Books online.
If someone had a complete list of options for ScriptType and Script2Type, I
would be very grateful.
Cheers,
NeilYou can just add up the values of separate parameters to the script method.
So 33 = 1 + 32 = SQLDMOScript_DatabasePermissions and SQLDMOScript_Drops.
Jacco Schalkwijk
SQL Server MVP
"NeilDJones" <NeilDJones@.discussions.microsoft.com> wrote in message
news:FABE2617-3F56-4DD9-A716-CC73791FAA8F@.microsoft.com...
> Hi.
> Does anyone have details of the parameters that can be provided to the
> Script method? SQL Books online lists some of the ScriptType options, but
> it
> is an incomplete list; I have been handed a legacy script that uses
> .script(333,<filename> ), where 33 is not listed in Books online.
> If someone had a complete list of options for ScriptType and Script2Type,
> I
> would be very grateful.
> Cheers,
> Neil|||Thanks Jacco.
However, the number I have seen used is 333 (apologise for the misleading 33
typo). I cannot see any combination of the options listed in Books Online
making 333 by simple addition, which leads back to the list in Books Online
being incomplete.
What I am attempting to do is reproduce in a script the results of selecting
a database, choosing Generate Script... in Enterprise Manager, selecting all
options from the General tab, not touching the Formatting tab, and selecting
all Table Scripting options from the Options tab.
I am sort of hoping that any of the choices you can make in the GUI can be
replicated in a stored procedure, but this may not in fact be the case..
Cheers,
Neil
"Jacco Schalkwijk" wrote:

> You can just add up the values of separate parameters to the script method
.
> So 33 = 1 + 32 = SQLDMOScript_DatabasePermissions and SQLDMOScript_Drops.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "NeilDJones" <NeilDJones@.discussions.microsoft.com> wrote in message
> news:FABE2617-3F56-4DD9-A716-CC73791FAA8F@.microsoft.com...
>
>|||Hi Neil,
I think that the bits (in the bytes that make up the number) that are not in
the list are set to 1 by default by Enterprise Manager, but are ignored. 333
breaks down to 256 + 64 + 8 + 4 + 1. 256 and 8 do not occur in the list, so
they are ignored, and 333 is effectively 69 ( = 64 + 4 + 1). You can test
this out by calling the script method with both 333 and 69 and see if they
produce the same results. I'll see if I can get confirmation about this
theory from Microsoft.
Jacco Schalkwijk
SQL Server MVP
"NeilDJones" <NeilDJones@.discussions.microsoft.com> wrote in message
news:70364886-5200-46D8-9D4F-95BDCA18C472@.microsoft.com...
> Thanks Jacco.
> However, the number I have seen used is 333 (apologise for the misleading
> 33
> typo). I cannot see any combination of the options listed in Books Online
> making 333 by simple addition, which leads back to the list in Books
> Online
> being incomplete.
> What I am attempting to do is reproduce in a script the results of
> selecting
> a database, choosing Generate Script... in Enterprise Manager, selecting
> all
> options from the General tab, not touching the Formatting tab, and
> selecting
> all Table Scripting options from the Options tab.
> I am sort of hoping that any of the choices you can make in the GUI can be
> replicated in a stored procedure, but this may not in fact be the case..
> Cheers,
> Neil
> "Jacco Schalkwijk" wrote:
>|||Thanks again Jacco. I'll give this a shot.
If anyone weer to find out the exact parameters required to match the
options I describe below, that'd be even better..
"Jacco Schalkwijk" wrote:

> Hi Neil,
> I think that the bits (in the bytes that make up the number) that are not
in
> the list are set to 1 by default by Enterprise Manager, but are ignored. 3
33
> breaks down to 256 + 64 + 8 + 4 + 1. 256 and 8 do not occur in the list, s
o
> they are ignored, and 333 is effectively 69 ( = 64 + 4 + 1). You can test
> this out by calling the script method with both 333 and 69 and see if they
> produce the same results. I'll see if I can get confirmation about this
> theory from Microsoft.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "NeilDJones" <NeilDJones@.discussions.microsoft.com> wrote in message
> news:70364886-5200-46D8-9D4F-95BDCA18C472@.microsoft.com...
>
>|||Jacco,
Just gave it a shot with 69 instead of 333, and the ersults are different.
One of the immediately noticeable differences is that with 333 as the value
for ScriptType, the results of the script method are appended to the target
file rather than replacing the target with a new one. This is not listed as
an option in Books Online.
Thanks for the explanation on deriving a single number from several
parameters. It makes it look even more likely that there are some options
missing from the help, since there is no 8, no 16, and nothing between 64 an
d
4096. The entry for 73736 also states that it is a sum of three others that
are not referenced in the Help (in this case 65536+8192+8).
In fact, I have just run the query again using 333-8, and it has appended
instead of replacing. It looks like 256 is the parameter to introduce this
functionality.
Any idea, then, who would I go to to obtain a complete list of these options
?
Cheers
Neil
"Jacco Schalkwijk" wrote:

> Hi Neil,
> I think that the bits (in the bytes that make up the number) that are not
in
> the list are set to 1 by default by Enterprise Manager, but are ignored. 3
33
> breaks down to 256 + 64 + 8 + 4 + 1. 256 and 8 do not occur in the list, s
o
> they are ignored, and 333 is effectively 69 ( = 64 + 4 + 1). You can test
> this out by calling the script method with both 333 and 69 and see if they
> produce the same results. I'll see if I can get confirmation about this
> theory from Microsoft.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "NeilDJones" <NeilDJones@.discussions.microsoft.com> wrote in message
> news:70364886-5200-46D8-9D4F-95BDCA18C472@.microsoft.com...
>
>|||Hi Neil,
I just did another trawl through BOL, and the values that we didn't find
earlier are actually documented under the Script Method (Table Object)
topic.
256 = SQLDMOScript_AppendToFile
8 = SQLDMOScript_ClusteredIndexes
and
532676608 = SQLDMOScript_DRI_All , which is probably the one you are looking
for.
Jacco Schalkwijk
SQL Server MVP
"NeilDJones" <NeilDJones@.discussions.microsoft.com> wrote in message
news:58357EB4-6AD7-4A7F-9935-B8C1CE27F3EE@.microsoft.com...
> Jacco,
> Just gave it a shot with 69 instead of 333, and the ersults are different.
> One of the immediately noticeable differences is that with 333 as the
> value
> for ScriptType, the results of the script method are appended to the
> target
> file rather than replacing the target with a new one. This is not listed
> as
> an option in Books Online.
> Thanks for the explanation on deriving a single number from several
> parameters. It makes it look even more likely that there are some options
> missing from the help, since there is no 8, no 16, and nothing between 64
> and
> 4096. The entry for 73736 also states that it is a sum of three others
> that
> are not referenced in the Help (in this case 65536+8192+8).
> In fact, I have just run the query again using 333-8, and it has appended
> instead of replacing. It looks like 256 is the parameter to introduce this
> functionality.
> Any idea, then, who would I go to to obtain a complete list of these
> options?
> Cheers
> Neil
> "Jacco Schalkwijk" wrote:
>|||Thanks Jacco. I had found a section under ScriptType that gave a few more,
but still didn't have 256 or 512.
There are still a few missing, eg 2048, and there is none specified to copy
rules across, but this certainly helps a lot.
Cheers,
Neil
"Jacco Schalkwijk" wrote:

> Hi Neil,
> I just did another trawl through BOL, and the values that we didn't find
> earlier are actually documented under the Script Method (Table Object)
> topic.
> 256 = SQLDMOScript_AppendToFile
> 8 = SQLDMOScript_ClusteredIndexes
> and
> 532676608 = SQLDMOScript_DRI_All , which is probably the one you are looki
ng
> for.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "NeilDJones" <NeilDJones@.discussions.microsoft.com> wrote in message
> news:58357EB4-6AD7-4A7F-9935-B8C1CE27F3EE@.microsoft.com...
>
>

Script Execution of Report with parameters

I need to script the execution of reports with input parameter values and with the report output going to PDF in a local file location on the server. I can't use traditional Report Server scheduling because I have to loop through a list of clients and execute the report for each client.

I appreciate any help you can offer.

Hello,

You should take a look at Data-Driven Subscriptions (if you have Enterprise edition) , it will be able to handle what you're trying to do. You can set the render output, parameters, etc.

Jarret

|||

Thanks for the feedback Jarret. We have only had the Standard edition, but our new server will have the Enterprise edition for me to try Data-Driven Subscriptions in the next couple of weeks.

I setup a test server with the Demo of SQL 2005 Enterprise and here are my results for anyone else having similar goals.

We have about 30-40 reports all of which are used by 40-50 clients. The way I accomplish this is by using dynamic Data Source connections in the reports which can connect to all 40-50 client databases. It works fine for on-demand reporting where I pass in the connection information based on the client calling the report. By using these dynamic Data Sources, it saves me from having to maintain between 1200 and 2000 reports and/or linked reports, one for each combination of report and client. We simply don't have the staff to maintain that many reports.

By following the steps in the BOL and using Credentials stored securely in the report server, you can create a New Data Driven Subscription... and report against multiple databases from one schedule by passing in the connection information from the Subscription database.

scope_identity Vs Parameters

Sorry for double posting but I screwed the last one up. The following code
successfully inserts a record in the Sections table, but the
scope_identity() returns DbNull. If I remove the parameter 'pSectionName'
and replace it with dummy value, it works fine. Alternatively, if I use
@.@.IDENTITY with or withour the parameter, that works fine too.
It seems there is a problem with using scope_identity() when parameters are
involved, but I do need to use parameters. Does anyone know why this would
happen and how to circumvent it ?
Dim sqlConnection As New SqlConnection(getConnectionString())
Dim sqlString As String
Dim result As Integer
Dim pSectionName As New SqlParameter("@.pSectionName",
SqlDbType.NVarChar)
pSectionName.Value = sectionRow.SectionName
sqlString = "INSERT INTO SECTIONS " & _
"VALUES (" & _
" '" & sectionRow.ArticleID.ToString & "'," & _
" @.pSectionName ," & _
" '" & sectionRow.SectionNumber.ToString & "'," & _
" '" & sectionRow.SectionFollowing.ToString & "'," & _
" '" & sectionRow.Attachments.ToString & "'," & _
" '" & sectionRow._Text & "'," & _
" ''," & _
" '" & sectionRow.pictureName & "'," & _
" '" & sectionRow.pictureType & "'," & _
" '" & sectionRow.pictureFilePath & "'," & _
" '" & sectionRow.SectionType & "');"
Dim sqlIDQuery As String
sqlIDQuery = "SELECT scope_identity();"
Dim sqlCommand As New SqlCommand(sqlString)
sqlCommand.Connection = sqlConnection
'Add Parameters
sqlCommand.Parameters.Add(pSectionName)
Dim SectionID As Integer
Try
sqlConnection.Open()
sqlCommand.ExecuteNonQuery() '***** THIS WORKS FINE AND
INSERTS RECORD.
sqlCommand.CommandText = sqlIDQuery
SectionID = CType(sqlCommand.ExecuteScalar, Integer) ' ****
FAILS HERE WITH AN EXCEPTION.
Catch ex As Exception
SectionID = 0
Finally
sqlConnection.Close()
End Try
Return SectionID
Best Regards
The Inimitable Mr NewbieHave you considered using a relational design, which will never have
IDENTITY in its tables? These exposed physical locators have nothign to
do with RDBMS or a valid logical model.
Have you considered using a stored procedure inside the database instead
of building a query on the fly at run time? Why do you think that a row
and record are anything alike?
A spec that tells us what you are trying to do, long with some DDL would
be very helpful. Do you program from things this vague and imcomplete?
Us, neither.
--CELKO--
Please post DDL in a human-readable format and not a machine-generated
one. This way people do not have to guess what the keys, constraints,
DRI, datatypes, etc. in your schema are. Sample data is also a good
idea, along with clear specifications.
*** Sent via Developersdex http://www.examnotes.net ***|||If you have nothing useful to say, just save it for someone who might care
that you try and make yourself feel good at the expense of others.
Especially those who are new like me.
Best Regards
The Inimitable Mr Newbie
"--CELKO--" <remove.jcelko212@.earthlink.net> wrote in message
news:OaEY7XgGGHA.2000@.TK2MSFTNGP15.phx.gbl...
> Have you considered using a relational design, which will never have
> IDENTITY in its tables? These exposed physical locators have nothign to
> do with RDBMS or a valid logical model.
> Have you considered using a stored procedure inside the database instead
> of building a query on the fly at run time? Why do you think that a row
> and record are anything alike?
> A spec that tells us what you are trying to do, long with some DDL would
> be very helpful. Do you program from things this vague and imcomplete?
> Us, neither.
> --CELKO--
> Please post DDL in a human-readable format and not a machine-generated
> one. This way people do not have to guess what the keys, constraints,
> DRI, datatypes, etc. in your schema are. Sample data is also a good
> idea, along with clear specifications.
>
> *** Sent via Developersdex http://www.examnotes.net ***|||I see you are reusing the same SqlCommand object but I don't see where you
are clearing the parameters collection from the first statement.
Consequently, the command will still contain the unneeded @.pSectionName
parameter when you execute SELECT SCOPE_IDENTITY(). You might try:
sqlCommand.Parameters.Clear()
sqlCommand.CommandText = sqlIDQuery
Also, consider parameterizing the entire INSERT statement or executing a
proc passing parameters. Parameterized values are more secure and avoid the
need to worry about things like embedded quotes in strings and date formats.
Hope this helps.
Dan Guzman
SQL Server MVP
"Mr Newbie" <here@.now.com> wrote in message
news:uqob9mfGGHA.2012@.TK2MSFTNGP14.phx.gbl...
> Sorry for double posting but I screwed the last one up. The following
> code successfully inserts a record in the Sections table, but the
> scope_identity() returns DbNull. If I remove the parameter
> 'pSectionName' and replace it with dummy value, it works fine.
> Alternatively, if I use @.@.IDENTITY with or withour the parameter, that
> works fine too.
> It seems there is a problem with using scope_identity() when parameters
> are involved, but I do need to use parameters. Does anyone know why this
> would happen and how to circumvent it ?
>
> Dim sqlConnection As New SqlConnection(getConnectionString())
> Dim sqlString As String
> Dim result As Integer
> Dim pSectionName As New SqlParameter("@.pSectionName",
> SqlDbType.NVarChar)
> pSectionName.Value = sectionRow.SectionName
> sqlString = "INSERT INTO SECTIONS " & _
> "VALUES (" & _
> " '" & sectionRow.ArticleID.ToString & "'," & _
> " @.pSectionName ," & _
> " '" & sectionRow.SectionNumber.ToString & "'," & _
> " '" & sectionRow.SectionFollowing.ToString & "'," & _
> " '" & sectionRow.Attachments.ToString & "'," & _
> " '" & sectionRow._Text & "'," & _
> " ''," & _
> " '" & sectionRow.pictureName & "'," & _
> " '" & sectionRow.pictureType & "'," & _
> " '" & sectionRow.pictureFilePath & "'," & _
> " '" & sectionRow.SectionType & "');"
> Dim sqlIDQuery As String
> sqlIDQuery = "SELECT scope_identity();"
> Dim sqlCommand As New SqlCommand(sqlString)
> sqlCommand.Connection = sqlConnection
> 'Add Parameters
> sqlCommand.Parameters.Add(pSectionName)
> Dim SectionID As Integer
> Try
> sqlConnection.Open()
> sqlCommand.ExecuteNonQuery() '***** THIS WORKS FINE AND
> INSERTS RECORD.
> sqlCommand.CommandText = sqlIDQuery
> SectionID = CType(sqlCommand.ExecuteScalar, Integer) ' ****
> FAILS HERE WITH AN EXCEPTION.
> Catch ex As Exception
> SectionID = 0
> Finally
> sqlConnection.Close()
> End Try
> Return SectionID
>
> --
> Best Regards
> The Inimitable Mr Newbie
>|||Thanks for your reply.
I tried clearing the Params, but that had no effect. so I created an
entirely new sqlCommandID object which used the same connection as the other
one for the scope_identity() but this had no effect.
I know I should parameterise the other parts of the sql query, but they dont
really need it because the contain no user input. Only the section title
carries this on creation.
Any other ideas. This is really bugging me.
Best Regards
The Inimitable Mr Newbie
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:OyzuingGGHA.2040@.TK2MSFTNGP14.phx.gbl...
>I see you are reusing the same SqlCommand object but I don't see where you
>are clearing the parameters collection from the first statement.
>Consequently, the command will still contain the unneeded @.pSectionName
>parameter when you execute SELECT SCOPE_IDENTITY(). You might try:
> sqlCommand.Parameters.Clear()
> sqlCommand.CommandText = sqlIDQuery
> Also, consider parameterizing the entire INSERT statement or executing a
> proc passing parameters. Parameterized values are more secure and avoid
> the need to worry about things like embedded quotes in strings and date
> formats.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Mr Newbie" <here@.now.com> wrote in message
> news:uqob9mfGGHA.2012@.TK2MSFTNGP14.phx.gbl...
>|||> Have you considered using a relational design, which will never have
> IDENTITY in its tables? These exposed physical locators have nothign to
> do with RDBMS or a valid logical model.
Have you consider going one step further than the 'logical model' and create
an implementation or do you just deal with theory?
The IDENTITY property is a very useful way to create surrogate keys and gain
good performance and is a solution for the really big problem of when a
primary key value changes.

> Have you considered using a stored procedure inside the database instead
> of building a query on the fly at run time? Why do you think that a row
> and record are anything alike?
How do you think report builders work? Do you have a stored procedure with
1000 parameters and 1000 if else.
Actually, let me bring you up on this point, you posted a w or two ago
that you shouldn't use IF ELSE in the database.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <remove.jcelko212@.earthlink.net> wrote in message
news:OaEY7XgGGHA.2000@.TK2MSFTNGP15.phx.gbl...
> Have you considered using a relational design, which will never have
> IDENTITY in its tables? These exposed physical locators have nothign to
> do with RDBMS or a valid logical model.
> Have you considered using a stored procedure inside the database instead
> of building a query on the fly at run time? Why do you think that a row
> and record are anything alike?
> A spec that tells us what you are trying to do, long with some DDL would
> be very helpful. Do you program from things this vague and imcomplete?
> Us, neither.
> --CELKO--
> Please post DDL in a human-readable format and not a machine-generated
> one. This way people do not have to guess what the keys, constraints,
> DRI, datatypes, etc. in your schema are. Sample data is also a good
> idea, along with clear specifications.
>
> *** Sent via Developersdex http://www.examnotes.net ***|||You need to run it as a single statement, it would be better if you used a
stored procedure if possible.
Anyway, the problem lies in that SCOPE_IDENTITY() is only for the
connection, your connection will have been reset between calls.
You can concatenate sqlString and sqlIDQuery just put a semi column ; after
the first statement.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Mr Newbie" <here@.now.com> wrote in message
news:uqob9mfGGHA.2012@.TK2MSFTNGP14.phx.gbl...
> Sorry for double posting but I screwed the last one up. The following
> code successfully inserts a record in the Sections table, but the
> scope_identity() returns DbNull. If I remove the parameter
> 'pSectionName' and replace it with dummy value, it works fine.
> Alternatively, if I use @.@.IDENTITY with or withour the parameter, that
> works fine too.
> It seems there is a problem with using scope_identity() when parameters
> are involved, but I do need to use parameters. Does anyone know why this
> would happen and how to circumvent it ?
>
> Dim sqlConnection As New SqlConnection(getConnectionString())
> Dim sqlString As String
> Dim result As Integer
> Dim pSectionName As New SqlParameter("@.pSectionName",
> SqlDbType.NVarChar)
> pSectionName.Value = sectionRow.SectionName
> sqlString = "INSERT INTO SECTIONS " & _
> "VALUES (" & _
> " '" & sectionRow.ArticleID.ToString & "'," & _
> " @.pSectionName ," & _
> " '" & sectionRow.SectionNumber.ToString & "'," & _
> " '" & sectionRow.SectionFollowing.ToString & "'," & _
> " '" & sectionRow.Attachments.ToString & "'," & _
> " '" & sectionRow._Text & "'," & _
> " ''," & _
> " '" & sectionRow.pictureName & "'," & _
> " '" & sectionRow.pictureType & "'," & _
> " '" & sectionRow.pictureFilePath & "'," & _
> " '" & sectionRow.SectionType & "');"
> Dim sqlIDQuery As String
> sqlIDQuery = "SELECT scope_identity();"
> Dim sqlCommand As New SqlCommand(sqlString)
> sqlCommand.Connection = sqlConnection
> 'Add Parameters
> sqlCommand.Parameters.Add(pSectionName)
> Dim SectionID As Integer
> Try
> sqlConnection.Open()
> sqlCommand.ExecuteNonQuery() '***** THIS WORKS FINE AND
> INSERTS RECORD.
> sqlCommand.CommandText = sqlIDQuery
> SectionID = CType(sqlCommand.ExecuteScalar, Integer) ' ****
> FAILS HERE WITH AN EXCEPTION.
> Catch ex As Exception
> SectionID = 0
> Finally
> sqlConnection.Close()
> End Try
> Return SectionID
>
> --
> Best Regards
> The Inimitable Mr Newbie
>|||--CELKO-- (remove.jcelko212@.earthlink.net) writes:
> Have you considered using a relational design, which will never have
> IDENTITY in its tables? These exposed physical locators have nothign to
> do with RDBMS or a valid logical model.
> Have you considered using a stored procedure inside the database instead
> of building a query on the fly at run time? Why do you think that a row
> and record are anything alike?
> A spec that tells us what you are trying to do, long with some DDL would
> be very helpful. Do you program from things this vague and imcomplete?
> Us, neither.
>
No, there is no spec needed. Instead of preteding like you are a bad
AI program let loose, learn how ADO .Net works and you will be answer
the question without any further spec.
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|||Mr Newbie (here@.now.com) writes:
> Sorry for double posting but I screwed the last one up. The following
> code successfully inserts a record in the Sections table, but the
> scope_identity() returns DbNull. If I remove the parameter
> 'pSectionName' and replace it with dummy value, it works fine.
> Alternatively, if I use @.@.IDENTITY with or withour the parameter, that
> works fine too.
> It seems there is a problem with using scope_identity() when parameters
> are involved, but I do need to use parameters. Does anyone know why this
> would happen and how to circumvent it ?
scope_idenity() returns the most recently generated identity value
in the current scope. Scope here is a stored procedure, or the top-
level scope. @.@.identity, on the other hand, returns the most recently
generated identity value for the connection, independent on scope.
When you don't use parameters, the INSERT command is submitted as-is,
and thus in the same scope as you later fetch scope_identity().
But when you use parameters, SqlClient submits the command through
sp_executesql. This is because SqlClient does not build the expanded
command string, but instead passes the parameters in an RPC call.
This is usually good for performance. The side effect is that the
INSERT statement no longer is in the top-level scope, and thus you
can get the identity value with scope_identity().
There are two possible ways to do:
o Use @.@.identity. This is fine as long as the table you are inserting
to does not have a trigger which in its turn insert into a table
with an identity column. In this case, @.@.identity will report the
value generated for that table. (It is to avoid this trap, that
scope_identity() was introduced.)
o As Tony suggested, add the SELECT on scope_identity() to the
batch with the INSERT statement.
The latter has the advantage of saving you a network roundtrip, which
is usally good for performance.
In fact, I would like to take you even a step further and do this:
sqlString = "INSERT INTO SECTIONS " & _
"(ArticleID, SectionName, SectionNumber, ... ) " & _
"VALUES (@.ArticleID, @.pSectionName, @.SectionNumber, ...) " & _
"SELECT @.id = scope_identity()"
1) Include the columns you are inserting into. If you leave out the column
list, and the table is later changed, you query will blow up.
2) Pass all values as parameters, don't embed values in the SQL String
(constants are OK). This protects you against SQL injection, and
other problems that could occur if the value includes an '. It also
saves you from hassle when using datetime values.
3) Make the value from scope_identity() an output parameter from
the batch. For this you need to specify the direction as InputOuput.
(SQL Server does not have any output-only parameters.)
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|||>> If you have nothing useful to say, just save it for someone who might car
e that you try and make yourself feel good at the expense of others. Especia
lly those who are new like me. <<
So when I tell you think about stored procedures, it is a bad thing.
But when other people mention stored procedures as an answer, it is
good thing. Interesting.

scope_identity from vwd VB

I have seen plenty of messages about using scope_index by creating parameters using HTML but I would like to do it from my .aspx.vb page.

Does anybody know if this is possible? I have got as far as the code below and get stuck when trying to add a new parameter with direction of output.

Any help would be much appreciated, cheers,

Doug.

Dim

NewPropertyAs SqlDataSource =New SqlDataSource

NewProperty.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings(

"ConnectIt").ToString()

NewProperty.InsertCommand =

"INSERT INTO Test (Name) VALUES (@.Name); SET @.NewID=SCOPE_IDENTITY()"

NewProperty.InsertParameters.Add(NewID, id)

NewProperty.Insert()

Use executescalar instead

NewProperty.InsertCommand ="INSERT INTO Test (Name) VALUES (@.Name); SELECT SCOPE_IDENTITY()"

Response.Write(NewProperty.ExecuteScalar.ToString())

HTH
Regards

|||

Thanks for the rapid response but I get the following error when I try it:

'ExecuteScalar' is not a member of the system.web.UI.WebControls.SqlDataSource

Any idea how I overcome this?

Thanks again,

Doug.

|||

Opps, i'm sorry

I thought you are talking about sqlcommand.

Check the following MSDN

http://msdn2.microsoft.com/en-us/system.web.ui.webcontrols.sqldatasource.inserted.aspx

Regards