Granting db_datareader permissions

Is there a way (Probably a script) to
grant 'db_datareader' permission to a Windows user
(SERVER\username) without the GUI '.
Thanks.You can use sp_addrolemember.
You can find more information on sp_addrolemember in books
online.
-Sue
On Mon, 5 Apr 2004 08:05:51 -0700, "Eric"
<anonymous@.discussions.microsoft.com> wrote:

>Is there a way (Probably a script) to
>grant 'db_datareader' permission to a Windows user
>(SERVER\username) without the GUI '.
>Thanks.|||That is for user defined roles NOT for fixed database
roles.

>--Original Message--
>You can use sp_addrolemember.
>You can find more information on sp_addrolemember in books
>online.
>-Sue
>On Mon, 5 Apr 2004 08:05:51 -0700, "Eric"
><anonymous@.discussions.microsoft.com> wrote:
>
>.
>|||Sorry......My bet. I had a syntax error......

>--Original Message--
>You can use sp_addrolemember.
>You can find more information on sp_addrolemember in books
>online.
>-Sue
>On Mon, 5 Apr 2004 08:05:51 -0700, "Eric"
><anonymous@.discussions.microsoft.com> wrote:
>
>.
>|||No...it's for any existing database role.
-Sue
On Mon, 5 Apr 2004 08:23:27 -0700, "Eric"
<anonymous@.discussions.microsoft.com> wrote:
>That is for user defined roles NOT for fixed database
>roles.
>

Grant Read only access to ALL databases

Let me start off by apologizing for asking such a "noob" question.
I have a SQL2000 server (on my AD domain) with a script that creates a
new database everyday, naming them by the MMDDYY format. For example,
the database for March 25, 2006 would be named "032506". And so on.
I would like to have a group of users that have "READ" only access to
these databases.
I've created a group on Active Director called "DBReader".
I've added the group to the SQL Server Enterprise Manager -> Security
-> Logins
This is were I'm stumped.
When I right click on the group I've added to the SQL Server Enterprise
Manager, I get three tabs, General, Server Roles, and Database Access.
I don't want to use the Server Roles tab because all the roles give too
much access (I only want to give READ access).
The Database Access tab allows me to assign READ access to "existing"
databases, what about new databases that gets created tomorrow and the
next day?
How can I assign "READ" access using group permissions to ALL existing
and NEW databases thats created in the future?
I'm hoping there's a simple solution and don't have to resort to
scheduling a script to add permissions to new databases everyday.
Thx for reading!
"me" <mnkykng@.gmail.com> wrote in message
news:1143504145.539215.309930@.g10g2000cwb.googlegr oups.com...
> Let me start off by apologizing for asking such a "noob" question.
> I have a SQL2000 server (on my AD domain) with a script that creates a
> new database everyday, naming them by the MMDDYY format. For example,
> the database for March 25, 2006 would be named "032506". And so on.
> I would like to have a group of users that have "READ" only access to
> these databases.
> I've created a group on Active Director called "DBReader".
> I've added the group to the SQL Server Enterprise Manager -> Security
> -> Logins
> This is were I'm stumped.
> When I right click on the group I've added to the SQL Server Enterprise
> Manager, I get three tabs, General, Server Roles, and Database Access.
> I don't want to use the Server Roles tab because all the roles give too
> much access (I only want to give READ access).
> The Database Access tab allows me to assign READ access to "existing"
> databases, what about new databases that gets created tomorrow and the
> next day?
> How can I assign "READ" access using group permissions to ALL existing
> and NEW databases thats created in the future?
> I'm hoping there's a simple solution and don't have to resort to
> scheduling a script to add permissions to new databases everyday.
>
New databases are created as copies of the Model database. Make the change
there.
David
|||Thx for replying!
So just to clarify.
If I use the SQL Server Enterprise Manager and add my AD group to
Database -> Model -> Users.
Double click the AD group I just added.
And check the box labeled "db_datareader" under the Database role
membership, any new databases that are created will get read access
permissions assigned automatically to my AD group?
sql

Grant Read only access to ALL databases

Let me start off by apologizing for asking such a "noob" question.
I have a SQL2000 server (on my AD domain) with a script that creates a
new database everyday, naming them by the MMDDYY format. For example,
the database for March 25, 2006 would be named "032506". And so on.
I would like to have a group of users that have "READ" only access to
these databases.
I've created a group on Active Director called "DBReader".
I've added the group to the SQL Server Enterprise Manager -> Security
-> Logins
This is were I'm stumped.
When I right click on the group I've added to the SQL Server Enterprise
Manager, I get three tabs, General, Server Roles, and Database Access.
I don't want to use the Server Roles tab because all the roles give too
much access (I only want to give READ access).
The Database Access tab allows me to assign READ access to "existing"
databases, what about new databases that gets created tomorrow and the
next day?
How can I assign "READ" access using group permissions to ALL existing
and NEW databases thats created in the future?
I'm hoping there's a simple solution and don't have to resort to
scheduling a script to add permissions to new databases everyday.
Thx for reading!"me" <mnkykng@.gmail.com> wrote in message
news:1143504145.539215.309930@.g10g2000cwb.googlegroups.com...
> Let me start off by apologizing for asking such a "noob" question.
> I have a SQL2000 server (on my AD domain) with a script that creates a
> new database everyday, naming them by the MMDDYY format. For example,
> the database for March 25, 2006 would be named "032506". And so on.
> I would like to have a group of users that have "READ" only access to
> these databases.
> I've created a group on Active Director called "DBReader".
> I've added the group to the SQL Server Enterprise Manager -> Security
> -> Logins
> This is were I'm stumped.
> When I right click on the group I've added to the SQL Server Enterprise
> Manager, I get three tabs, General, Server Roles, and Database Access.
> I don't want to use the Server Roles tab because all the roles give too
> much access (I only want to give READ access).
> The Database Access tab allows me to assign READ access to "existing"
> databases, what about new databases that gets created tomorrow and the
> next day?
> How can I assign "READ" access using group permissions to ALL existing
> and NEW databases thats created in the future?
> I'm hoping there's a simple solution and don't have to resort to
> scheduling a script to add permissions to new databases everyday.
>
New databases are created as copies of the Model database. Make the change
there.
David|||Thx for replying!
So just to clarify.
If I use the SQL Server Enterprise Manager and add my AD group to
Database -> Model -> Users.
Double click the AD group I just added.
And check the box labeled "db_datareader" under the Database role
membership, any new databases that are created will get read access
permissions assigned automatically to my AD group?

Grant Read only access to ALL databases

Let me start off by apologizing for asking such a "noob" question.
I have a SQL2000 server (on my AD domain) with a script that creates a
new database everyday, naming them by the MMDDYY format. For example,
the database for March 25, 2006 would be named "032506". And so on.
I would like to have a group of users that have "READ" only access to
these databases.
I've created a group on Active Director called "DBReader".
I've added the group to the SQL Server Enterprise Manager -> Security
-> Logins
This is were I'm stumped.
When I right click on the group I've added to the SQL Server Enterprise
Manager, I get three tabs, General, Server Roles, and Database Access.
I don't want to use the Server Roles tab because all the roles give too
much access (I only want to give READ access).
The Database Access tab allows me to assign READ access to "existing"
databases, what about new databases that gets created tomorrow and the
next day?
How can I assign "READ" access using group permissions to ALL existing
and NEW databases thats created in the future?
I'm hoping there's a simple solution and don't have to resort to
scheduling a script to add permissions to new databases everyday.
Thx for reading!"me" <mnkykng@.gmail.com> wrote in message
news:1143504145.539215.309930@.g10g2000cwb.googlegroups.com...
> Let me start off by apologizing for asking such a "noob" question.
> I have a SQL2000 server (on my AD domain) with a script that creates a
> new database everyday, naming them by the MMDDYY format. For example,
> the database for March 25, 2006 would be named "032506". And so on.
> I would like to have a group of users that have "READ" only access to
> these databases.
> I've created a group on Active Director called "DBReader".
> I've added the group to the SQL Server Enterprise Manager -> Security
> -> Logins
> This is were I'm stumped.
> When I right click on the group I've added to the SQL Server Enterprise
> Manager, I get three tabs, General, Server Roles, and Database Access.
> I don't want to use the Server Roles tab because all the roles give too
> much access (I only want to give READ access).
> The Database Access tab allows me to assign READ access to "existing"
> databases, what about new databases that gets created tomorrow and the
> next day?
> How can I assign "READ" access using group permissions to ALL existing
> and NEW databases thats created in the future?
> I'm hoping there's a simple solution and don't have to resort to
> scheduling a script to add permissions to new databases everyday.
>
New databases are created as copies of the Model database. Make the change
there.
David|||Thx for replying!
So just to clarify.
If I use the SQL Server Enterprise Manager and add my AD group to
Database -> Model -> Users.
Double click the AD group I just added.
And check the box labeled "db_datareader" under the Database role
membership, any new databases that are created will get read access
permissions assigned automatically to my AD group?

Grant login permission on tables

Hi,
In my SQL Server 2000, how do I grant a login account permission for tables
in a particular database, using script, not by checking the checkboxes.
There are many tables in that database, and I am thinking using script, it
will be faster.
Thanks for help.
JasonIf the user already exists on the database try some of these
grant select on mytable to user
grant insert on mytable to user
grant update on mytable to user
grant delete on mytable to user
grant select, insert, update, delete on mytable to user
grant all on mytable to user
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Jason Huang" wrote:
> Hi,
> In my SQL Server 2000, how do I grant a login account permission for tables
> in a particular database, using script, not by checking the checkboxes.
> There are many tables in that database, and I am thinking using script, it
> will be faster.
> Thanks for help.
>
> Jason
>
>|||Hi Jason,
Try this out:
select 'grant Select,Insert,Delete,Update on ' +name+ ' to [Username in
database]' from sysobjects where xtype='U'
Make sure user is already there in database.
Manu
"Jason Huang" wrote:
> Hi,
> In my SQL Server 2000, how do I grant a login account permission for tables
> in a particular database, using script, not by checking the checkboxes.
> There are many tables in that database, and I am thinking using script, it
> will be faster.
> Thanks for help.
>
> Jason
>
>

Grant login permission on tables

Hi,
In my SQL Server 2000, how do I grant a login account permission for tables
in a particular database, using script, not by checking the checkboxes.
There are many tables in that database, and I am thinking using script, it
will be faster.
Thanks for help.
Jason
If the user already exists on the database try some of these
grant select on mytable to user
grant insert on mytable to user
grant update on mytable to user
grant delete on mytable to user
grant select, insert, update, delete on mytable to user
grant all on mytable to user
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Jason Huang" wrote:

> Hi,
> In my SQL Server 2000, how do I grant a login account permission for tables
> in a particular database, using script, not by checking the checkboxes.
> There are many tables in that database, and I am thinking using script, it
> will be faster.
> Thanks for help.
>
> Jason
>
>
|||Hi Jason,
Try this out:
select 'grant Select,Insert,Delete,Update on ' +name+ ' to [Username in
database]' from sysobjects where xtype='U'
Make sure user is already there in database.
Manu
"Jason Huang" wrote:

> Hi,
> In my SQL Server 2000, how do I grant a login account permission for tables
> in a particular database, using script, not by checking the checkboxes.
> There are many tables in that database, and I am thinking using script, it
> will be faster.
> Thanks for help.
>
> Jason
>
>

Grant login permission on tables

Hi,
In my SQL Server 2000, how do I grant a login account permission for tables
in a particular database, using script, not by checking the checkboxes.
There are many tables in that database, and I am thinking using script, it
will be faster.
Thanks for help.
JasonIf the user already exists on the database try some of these
grant select on mytable to user
grant insert on mytable to user
grant update on mytable to user
grant delete on mytable to user
grant select, insert, update, delete on mytable to user
grant all on mytable to user
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Jason Huang" wrote:

> Hi,
> In my SQL Server 2000, how do I grant a login account permission for table
s
> in a particular database, using script, not by checking the checkboxes.
> There are many tables in that database, and I am thinking using script, it
> will be faster.
> Thanks for help.
>
> Jason
>
>|||Hi Jason,
Try this out:
select 'grant Select,Insert,Delete,Update on ' +name+ ' to [Username in
database]' from sysobjects where xtype='U'
Make sure user is already there in database.
Manu
"Jason Huang" wrote:

> Hi,
> In my SQL Server 2000, how do I grant a login account permission for table
s
> in a particular database, using script, not by checking the checkboxes.
> There are many tables in that database, and I am thinking using script, it
> will be faster.
> Thanks for help.
>
> Jason
>
>

Grant EXEC to SPROCS

I want to grant exec permission to a windows account <domain\user> to 150
stored procedures that are prefixed with usp_ . How do I script this so that
i won't have to do them individually?
>I want to grant exec permission to a windows account <domain\user> to 150
> stored procedures that are prefixed with usp_ . How do I script this so
> that
> i won't have to do them individually
Try a script like the one below.
SET NOCOUNT ON
DECLARE @.GrantStatement nvarchar(4000)
DECLARE GrantStatements CURSOR
LOCAL FAST_FORWARD READ_ONLY FOR
SELECT
N'GRANT EXECUTE ON ' +
QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME) +
N' TO SpExecuteRole'
FROM INFORMATION_SCHEMA.ROUTINES
WHERE
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME)),
'IsMSShipped') = 0 AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME)),
'IsProcedure') = 1 AND
ROUTINE_NAME LIKE 'usp[_]'
OPEN GrantStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM GrantStatements
INTO @.GrantStatement
IF @.@.FETCH_STATUS = -1 BREAK
BEGIN
RAISERROR (@.GrantStatement, 0, 1) WITH NOWAIT
EXECUTE sp_ExecuteSQL @.GrantStatement
END
END
CLOSE GrantStatements
DEALLOCATE GrantStatements
Hope this helps.
Dan Guzman
SQL Server MVP
"morphius" <morphius@.discussions.microsoft.com> wrote in message
news:FAD323C1-40A9-46C6-B9EC-F02CFDF3E4B7@.microsoft.com...
>I want to grant exec permission to a windows account <domain\user> to 150
> stored procedures that are prefixed with usp_ . How do I script this so
> that
> i won't have to do them individually?
sql

Grant EXEC to SPROCS

I want to grant exec permission to a windows account <domain\user> to 150
stored procedures that are prefixed with usp_ . How do I script this so that
i won't have to do them individually?>I want to grant exec permission to a windows account <domain\user> to 150
> stored procedures that are prefixed with usp_ . How do I script this so
> that
> i won't have to do them individually
Try a script like the one below.
SET NOCOUNT ON
DECLARE @.GrantStatement nvarchar(4000)
DECLARE GrantStatements CURSOR
LOCAL FAST_FORWARD READ_ONLY FOR
SELECT
N'GRANT EXECUTE ON ' +
QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME) +
N' TO SpExecuteRole'
FROM INFORMATION_SCHEMA.ROUTINES
WHERE
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME)),
'IsMSShipped') = 0 AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(ROUTINE_SCHEMA) +
N'.' +
QUOTENAME(ROUTINE_NAME)),
'IsProcedure') = 1 AND
ROUTINE_NAME LIKE 'usp[_]'
OPEN GrantStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM GrantStatements
INTO @.GrantStatement
IF @.@.FETCH_STATUS = -1 BREAK
BEGIN
RAISERROR (@.GrantStatement, 0, 1) WITH NOWAIT
EXECUTE sp_ExecuteSQL @.GrantStatement
END
END
CLOSE GrantStatements
DEALLOCATE GrantStatements
Hope this helps.
Dan Guzman
SQL Server MVP
"morphius" <morphius@.discussions.microsoft.com> wrote in message
news:FAD323C1-40A9-46C6-B9EC-F02CFDF3E4B7@.microsoft.com...
>I want to grant exec permission to a windows account <domain\user> to 150
> stored procedures that are prefixed with usp_ . How do I script this so
> that
> i won't have to do them individually?

Grant all privileges to a user

Is it possible to grant all privilege for all tables of a specified database through script? Because i have to send the script to user side and i can't do it manually in Enterprise Manager.

regards,

Add the user to the db_owner database role of that database:

use databasename

go

sp_addrolemember db_owner,username

Graceful Exit...

I need to exit from an entire package if a data pump task fails in a sequence container.

I thought I'd use a script task to do this but I'd be glad to learn of any way to do this.

Thanks!

Enantiodrom

Hi Enantiodrom: I had to do exactly this same thing in my packages. I first set up a boolean variable named Go_NoGo and used it as a ReadWriteVariable in a Script Task. Then, within the Script Task I set the variable to either True or False, depending on conditions determined by the Script Task.

Then, on the constraint (the line connecting between tasks) between the Script Task and the next task, I put a "Precedence Constraint" that allows execution to continue to the next task only when Go_NoGo is True.

Double-click on the line connecting your Script Task and the next task in the flow. You should see the "Precedence Constraint Editor" open up. I set "Evaluation operation" to "Expression", and set the Expression to @.[User::Go_NoGo]==True

It took a long time to figure out this syntax. Don't know why the "==" is needed, but it works -- a single "=" won't work.

Also, the reference to the variable ("Go_NoGo") appears to be case-sensitive. Type it into the expression exactly as it appears in the Variables dialog. Also, make sure that the variable is global, and not locally-scoped to some task within the project.

When you set up the constraint this way, execution proceeds to the next task only if Go_NoGo is True. Otherwise, the package just ends without generating an error.

Does this make sense? Once I figured out how to create precedence constraints, I use 'em all the time! There just aren't many good examples in books online. Microsoft really needs to catch up on this one!

|||

Try to look into using failPackageOnFailure property. More details at:

http://msdn2.microsoft.com/zh-cn/library/microsoft.sqlserver.dts.runtime.dtscontainer.failpackageonfailure.aspx

Thanks,
Ovidiu Burlacu

|||

Thank you both very much; this is just what I needed.

Jim/Enantiodrom

good editor tools for script file

Hi, can anyone suggest a good and free editor tools for script file? so it
would be eaiser to tell the begin... end...I don't use any specialized editor. I use Query Analyzer or Textpad, and I
find them more than adequate.
Linchi
"tulip" wrote:
> Hi, can anyone suggest a good and free editor tools for script file? so it
> would be eaiser to tell the begin... end...|||I HIGHLY recommend ApexSQL Edit, although it isn't free. Blocking out
BEGIN/END pairs is just one of hundreds of nifty features.
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"tulip" <tulip@.discussions.microsoft.com> wrote in message
news:B42DFFB2-4CA9-4026-B7A5-DAC37E1391AF@.microsoft.com...
> Hi, can anyone suggest a good and free editor tools for script file? so it
> would be eaiser to tell the begin... end...

good editor tools for script file

Hi, can anyone suggest a good and free editor tools for script file? so it
would be eaiser to tell the begin... end...
I don't use any specialized editor. I use Query Analyzer or Textpad, and I
find them more than adequate.
Linchi
"tulip" wrote:

> Hi, can anyone suggest a good and free editor tools for script file? so it
> would be eaiser to tell the begin... end...
|||I HIGHLY recommend ApexSQL Edit, although it isn't free. Blocking out
BEGIN/END pairs is just one of hundreds of nifty features.
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"tulip" <tulip@.discussions.microsoft.com> wrote in message
news:B42DFFB2-4CA9-4026-B7A5-DAC37E1391AF@.microsoft.com...
> Hi, can anyone suggest a good and free editor tools for script file? so it
> would be eaiser to tell the begin... end...

Good Book on SQL Server Reporting Services Script

Hello, I recently discovered the rs.exe utility and .rss files. I have
looked at and enjoyed the sample scripts, but was wondering if there were any
books on this subject that anyone has found useful?
Thanks in advance!
TimTim,
That is a hard one, as all of the books out there do not talk much to the
rs.exe utility, but there is one site www.sqldbatips.com has a tool that can
help out a ton: Reporting Services Scripter.
I also have a method to debug rss scripts.
In Visual Studio
1. Create a new Visual Basic Console Application Project: name it RSDebug
2. In the Soultion Explorer window - right click on the RSDebug Project and
select the Add Web Reference option.
3. In the URL text box add:
http://servername/reportserver/reportservice2005.asmx
4. Click Go Command Button (This step takes a while)
5. In the Web Reference Name text box add SSRSWebService
6. Click Add Reference Command Button
7. In the Code Window above the Module Module1 add:
Imports RSDebug.SSRSWebService
Imports System.Web.Services.Protocols
8. Below the Module Module1 add: Public rs As New ReportingService2005
9. Paste your script code in the Sub Main() procedure
10. Set your breakpoint and go.
Example: (In this example System.IO is needed due to the use of the
MemoryStream object)
Imports RSDebug.SSRSWebService
Imports System.Web.Services.Protocols
Imports System.IO ' This was added because MemoryStream was used
Module Module1
Public rs As New ReportingService2005
Sub Main()
Dim strObjectName As String = "Report Name"
Dim strObjectPath As String = "/Application"
Dim strObjectFullPath As String = strObjectPath & "/" & strObjectName
Dim strLocalFile As String = "Report Name.rdl"
Dim strLocalPath As String = "C:\"
Dim strLocalFullPath As String = strLocalPath & strLocalFile
Dim objReportDefinition As Byte()
Dim objMemoryStream As MemoryStream
Dim objDocument As New System.Xml.XmlDocument()
Try
rs.Credentials = System.Net.CredentialCache.DefaultCredentials
objReportDefinition = rs.GetReportDefinition(strObjectFullPath)
objMemoryStream = New MemoryStream(objReportDefinition)
objDocument.Load(objMemoryStream)
objDocument.Save(strLocalFullPath)
Console.WriteLine("Standard: Report downloaded successfully.")
Catch e As SoapException
Console.WriteLine("Error: " +
e.Detail.Item("ErrorCode").InnerText + " (" +
e.Detail.Item("Message").InnerText + ")")
End Try
End Sub
End Module|||Thanks very much Reeves, this is great information.
Tim
"Reeves Smith" wrote:
> Tim,
> That is a hard one, as all of the books out there do not talk much to the
> rs.exe utility, but there is one site www.sqldbatips.com has a tool that can
> help out a ton: Reporting Services Scripter.
> I also have a method to debug rss scripts.
> In Visual Studio
> 1. Create a new Visual Basic Console Application Project: name it RSDebug
> 2. In the Soultion Explorer window - right click on the RSDebug Project and
> select the Add Web Reference option.
> 3. In the URL text box add:
> http://servername/reportserver/reportservice2005.asmx
> 4. Click Go Command Button (This step takes a while)
> 5. In the Web Reference Name text box add SSRSWebService
> 6. Click Add Reference Command Button
> 7. In the Code Window above the Module Module1 add:
> Imports RSDebug.SSRSWebService
> Imports System.Web.Services.Protocols
> 8. Below the Module Module1 add: Public rs As New ReportingService2005
> 9. Paste your script code in the Sub Main() procedure
> 10. Set your breakpoint and go.
>
> Example: (In this example System.IO is needed due to the use of the
> MemoryStream object)
>
> Imports RSDebug.SSRSWebService
> Imports System.Web.Services.Protocols
>
> Imports System.IO ' This was added because MemoryStream was used
>
> Module Module1
> Public rs As New ReportingService2005
>
> Sub Main()
> Dim strObjectName As String = "Report Name"
> Dim strObjectPath As String = "/Application"
> Dim strObjectFullPath As String = strObjectPath & "/" & strObjectName
> Dim strLocalFile As String = "Report Name.rdl"
> Dim strLocalPath As String = "C:\"
> Dim strLocalFullPath As String = strLocalPath & strLocalFile
> Dim objReportDefinition As Byte()
> Dim objMemoryStream As MemoryStream
> Dim objDocument As New System.Xml.XmlDocument()
>
> Try
> rs.Credentials = System.Net.CredentialCache.DefaultCredentials
> objReportDefinition = rs.GetReportDefinition(strObjectFullPath)
> objMemoryStream = New MemoryStream(objReportDefinition)
> objDocument.Load(objMemoryStream)
> objDocument.Save(strLocalFullPath)
> Console.WriteLine("Standard: Report downloaded successfully.")
> Catch e As SoapException
> Console.WriteLine("Error: " +
> e.Detail.Item("ErrorCode").InnerText + " (" +
> e.Detail.Item("Message").InnerText + ")")
> End Try
> End Sub
> End Module|||On Aug 7, 7:58 pm, TimS <timsts...@.msn.com(donotspam)> wrote:
> Thanks very much Reeves, this is great information.
> Tim
> "Reeves Smith" wrote:
> > Tim,
> > That is a hard one, as all of the books out there do not talk much to the
> > rs.exe utility, but there is one sitewww.sqldbatips.comhas a tool that can
> > help out a ton: Reporting Services Scripter.
> > I also have a method to debug rss scripts.
> > In Visual Studio
> > 1. Create a new Visual Basic Console Application Project: name it RSDebug
> > 2. In the Soultion Explorer window - right click on the RSDebug Project and
> > select the Add Web Reference option.
> > 3. In the URL text box add:
> >http://servername/reportserver/reportservice2005.asmx
> > 4. Click Go Command Button (This step takes a while)
> > 5. In the Web Reference Name text box add SSRSWebService
> > 6. Click Add Reference Command Button
> > 7. In the Code Window above the Module Module1 add:
> > Imports RSDebug.SSRSWebService
> > Imports System.Web.Services.Protocols
> > 8. Below the Module Module1 add: Public rs As New ReportingService2005
> > 9. Paste your script code in the Sub Main() procedure
> > 10. Set your breakpoint and go.
> > Example: (In this example System.IO is needed due to the use of the
> > MemoryStream object)
> > Imports RSDebug.SSRSWebService
> > Imports System.Web.Services.Protocols
> > Imports System.IO ' This was added because MemoryStream was used
> > Module Module1
> > Public rs As New ReportingService2005
> > Sub Main()
> > Dim strObjectName As String = "Report Name"
> > Dim strObjectPath As String = "/Application"
> > Dim strObjectFullPath As String = strObjectPath & "/" & strObjectName
> > Dim strLocalFile As String = "Report Name.rdl"
> > Dim strLocalPath As String = "C:\"
> > Dim strLocalFullPath As String = strLocalPath & strLocalFile
> > Dim objReportDefinition As Byte()
> > Dim objMemoryStream As MemoryStream
> > Dim objDocument As New System.Xml.XmlDocument()
> > Try
> > rs.Credentials = System.Net.CredentialCache.DefaultCredentials
> > objReportDefinition = rs.GetReportDefinition(strObjectFullPath)
> > objMemoryStream = New MemoryStream(objReportDefinition)
> > objDocument.Load(objMemoryStream)
> > objDocument.Save(strLocalFullPath)
> > Console.WriteLine("Standard: Report downloaded successfully.")
> > Catch e As SoapException
> > Console.WriteLine("Error: " +
> > e.Detail.Item("ErrorCode").InnerText + " (" +
> > e.Detail.Item("Message").InnerText + ")")
> > End Try
> > End Sub
> > End Module
I ordered Microsoft's new books for the MCITP certification program in
Business Intelligence. One covers the 70-445 exam and the other one
the 70-446 exam. I'm sure you'll find them very useful (once they're
published anyways!).
http://www.amazon.com/MCTS-Self-Paced-Training-Exam-70-445/dp/0735623414/ref=pd_bbs_1/102-9922975-8902553?ie=UTF8&s=books&qid=1186575878&sr=8-1
http://www.amazon.com/MCITP-Self-Paced-Training-Exam-70-446/dp/0735623848/ref=pd_bbs_2/102-9922975-8902553?ie=UTF8&s=books&qid=1186575878&sr=8-2

GoalSeek in SQL Server?

Has anyone create a GoalS function similar to that of Excel? I've
been researching this and trying to script one out myself without much
sucess. If someone could tell me that its impossible, that would be
helpful too. TIA.This is not really what SQL is used for.|||You may be able to do it in MDX using multiple-pass calculations to iterate
to the required result:
http://msdn.microsoft.com/library/d...
anced_6jn7.asp
In pure SQL you could perhaps do it without iteration if the range of
possible values is small enough:
SELECT TOP 1 N1.num, N2.num
FROM some_numbers AS N1, some_numbers AS N2
WHERE (N1.num + (N2.num/2) - 123) = 12345
ORDER BY N1.num, N2.num
David Portas
SQL Server MVP
--|||I have used SQL to solve number puzzles, just for fun (eh!?). Try the
following script.
-- Easy example, all combinations of the numbers 1-9 which add up to 11
DECLARE @.control TABLE ( control_no TINYINT PRIMARY KEY, control_str AS CAST
(control_no AS CHAR(1) ) )
DECLARE @.i INT
SET NOCOUNT ON
SET @.i = 0
-- Add control numbers to temp table
WHILE @.i Between 0 And 9
BEGIN
INSERT @.control VALUES( @.i )
SET @.i = @.i + 1
END
SET NOCOUNT OFF
-- List all combinations which add up to 11
SELECT a.control_no, b.control_no, a.control_no + b.control_no
FROM @.control a, @.control b
WHERE a.control_no + b.control_no = 11
-- Harder example; MENSA-type substitution puzzle
-- Mensa's Number Puzzles for Math Geniuses by Harold Gale has this puzzle:
--
-- Number Puzzle 58
-- Place six three digit numbers of 100 plus at the end of 685
-- so that six numbers of six digits are produced [like 685123].
-- When each number is divided by 111 six whole numbers can be found.
SELECT
a.control_str,
b.control_str,
c.control_str,
a.control_str + b.control_str + c.control_str AS result,
'685' + a.control_str + b.control_str + c.control_str AS result2,
CAST( '685' + a.control_str + b.control_str + c.control_str AS DECIMAL ) /
111 AS result2
FROM @.control a, @.control b, @.control c
WHERE a.control_str + b.control_str + c.control_str > 100
AND ( CAST( '685' + a.control_str + b.control_str + c.control_str AS
DECIMAL ) / 111 ) =
CAST ( CAST( '685' + a.control_str + b.control_str + c.control_str AS
DECIMAL ) / 111 AS INT )
ORDER BY 1
-- This puzzle is similar to ones like this:
-- How many three digit numbers are divisible by 17?
-- Well, the first one is greater than 100. 100/17=5.88235. . . So the first
is 6x17=102. We don't need to list these three digit numbers. The last one i
s
less than 1000. 1000/17=58.8235. . . So our three digit numbers are 6x17,
7x17, 8x17, . . ., 58x17. There are 53 such numbers.
SELECT
a.control_str,
b.control_str,
c.control_str,
a.control_str + b.control_str + c.control_str AS result,
CAST( a.control_str + b.control_str + c.control_str AS DECIMAL ) / 17 AS
result2
FROM @.control a, @.control b, @.control c
WHERE a.control_str + b.control_str + c.control_str > 100
AND ( CAST( a.control_str + b.control_str + c.control_str AS DECIMAL ) /
17 ) =
CAST ( CAST( a.control_str + b.control_str + c.control_str AS DECIMAL ) /
17 AS INT )
ORDER BY 1
-- etc
Obviously the code above is quick and dirty, but you see where I'm going.
If not, look up 'Cartesian products'. Don't go using it in production
environment!
Let me know hot you get on.
Damien
"carmaboy@.gmail.com" wrote:

> Has anyone create a GoalS function similar to that of Excel? I've
> been researching this and trying to script one out myself without much
> sucess. If someone could tell me that its impossible, that would be
> helpful too. TIA.
>|||Did you notice that you wind up generating all possible combinations|||Yes, I'm using the cartesian product to get the answers to the puzzles. Is
that what you mean? Presumably there are other ways to do this, but this wa
s
just a demo, and hey it works!
Damien
"--CELKO--" wrote:

> Did you notice that you wind up generating all possible combinations
>|||Perhaps Joe's point was that declarative SQL solutions generally will
produce the total set of results, effort that is highly redundant if
all solutions are equal. What you probably wanted was just the fastest
single solution. That's why SQL is likely a heavily inefficient
solution - ok for solving recreational problems but in a commercial
environment you'll probably be better off with a procedural language or
a math/stats package.
David Portas
SQL Server MVP
--|||>> Perhaps Joe's point was that declarative SQL solutions generally will pro
duce the total set of results, <<
In fact, I just a did a short piece for DBAzine with a simple bin
packing problem (one bin, n-items) to demonstrate how the answer space
keeps doubling.

GO statement in SQL scripts

I have inherited a large script which uses DateFrom and DateTo parameters in 4 separate places in the script. I would like to DECLARE these parameters and put them into 2 variables to use in the 4 instances (instead of having to alter the script every time I run it). My problem is that the GO statement ends a batch of commands and the script loses the value of my variables.

Can anyone tell me exactly what the significance of the GO statement is in a SQL script, what it does and under what circumstances it is really necessary.

Thanks for your help.

itsmarkdavies@.hotmail.combasically you need a go any time you issue a:

CREATE DEFAULT, CREATE FUNCTION, CREATE PROCEDURE, CREATE RULE, CREATE TRIGGER, or CREATE VIEW statment. Also a table can not be altered and then referenced in the same batch.

Check out Books On Line, on the index tab type in the keyword "GO". BOL has a very goo explination of the use of "GO" and batches in general.|||So Paul it sounds like 'GO' is like 'commit' when you create new objects in your script ?|||Not quite, transactions are used to group operations into a logical work unit which you commit or rollback based on some test. A "go" is syntacticaly required to execute some TSQL statments. Consider the following:

Code:
----------------------------
begin transaction
create procedure test_getstuff as select db_name(), user_id()
exec test_getstuff
rollback transaction
if object_id('test_getstuff') is not null
print 'test_getstuff exists'
else
print 'test_getstuff does not exist'
----------------------------
will generate an error, however

Code:
----------------------------
begin transaction
go
create procedure test_getstuff as select db_name(), user_id()
exec test_getstuff
rollback transaction
if object_id('test_getstuff') is not null
print 'test_getstuff exists'
else
print 'test_getstuff does not exist'
go
sp_helptext test_getstuff
go
----------------------------
will work because TSQL requires a create procedure to be the first command in a batch. Notice, however, that the stored procedure does a little more than expected. Lastly:

Code:
----------------------------
begin transaction
go
create procedure test_getstuff as select db_name(), user_id()
go
exec test_getstuff
sp_helptext test_getstuff
rollback transaction
if object_id('test_getstuff') is not null
print 'test_getstuff exists'
else
print 'test_getstuff does not exist'
go
----------------------------
The 2nd go is used to terminate the batch. Now the sp looks correct.

Notice that in all of this I destroyed all my work by wrapping everything in a transaction. I could just ahve easily saved all my work by using a commit.|||Hi,

Use the "GO" statement if you want to end the SQL Statements and want SQL Server to execute till that.

Mr. Paul and Mr.Youngman has explained in a nice way.

You can add these below basic info also along with that.

Basically SQL Server must understand where Your SQL script ends.
So, Microsoft uses "GO" to terminate/end SQL Statements. Oracle uses ";" to terminate/end SQL Statements.

As soon as SQL Server sees "Go" statement it understands that SQL Statement is ended and it can start executing the SQL Statements till that point.

I can put in this way, like a man looking for pebbles. as soon as he finds a pebble, he picks and puts in a bag and looks nearby whether there are some more and keep going. Similarly SQL Server looks for "Go" and as soon as it sees "GO" it thinks, Ok I found a "go" and I have to end there and pick all the SQL Statements I come across and start executing.

Thats it.

Have Fun :)
Varad01

Go and goto in one sql script gives error label not declared

Hi,

I have a problem:
I am writing an update script for a database and want to check for the
version and Goto the wright update script.

So I read the version from a table and if it match I want to "Goto
Versionxxx"

Where Versionxxx: is set in the script with the right update script.

Whenever I have some script which need Go commands I get error in the
output that

A GOTO statement references the label 'Versionxxx' but the label has
not been declared.

But the label is set in the script by 'Versionxxx:'

Is there a way I can solve this easily?

Thanks in advanceHere's the trick with "GO":

It's not actually a part of the T-SQL language. It's a batch
separator. (Don't believe me? Try running "exec('GO')" in Query
Analyzer.)

Think of it like this: Cut up your script into multiple files,
separated by the "GO" statement. Run each of these files individually,
but use the same connection. That's all "GO" does.

So you need to remove the "GO" batch separators in between your
statements that need to be run in the same batch.

-Dave Markle
http://www.markleconsulting.com/blog
BF wrote:

Quote:

Originally Posted by

Hi,
>
I have a problem:
I am writing an update script for a database and want to check for the
version and Goto the wright update script.
>
So I read the version from a table and if it match I want to "Goto
Versionxxx"
>
Where Versionxxx: is set in the script with the right update script.
>
Whenever I have some script which need Go commands I get error in the
output that
>
A GOTO statement references the label 'Versionxxx' but the label has
not been declared.
>
But the label is set in the script by 'Versionxxx:'
>
Is there a way I can solve this easily?
>
Thanks in advance

|||Thanks for the quick respond.

The solution is not quite what I was hoping for.

For each new version I create an update script, We have an app which
does that and there are lots of Go commands.

I want to have one update script for all versions of the app so we have
2.00 to 2.01 to 2.02 to 2.03 etc.

For each version I have a script and I want to lookup the version, if
version is 2.03 I can start updating from 2.03 to 2.04 with the goto I
can jump over all other updates because they are already done in the
past.

When I use different files I cannot easy control which files to
execute, or I have to run them from the main script.

Grtx Bob

dmarkle schreef:

Quote:

Originally Posted by

Here's the trick with "GO":
>
It's not actually a part of the T-SQL language. It's a batch
separator. (Don't believe me? Try running "exec('GO')" in Query
Analyzer.)
>
Think of it like this: Cut up your script into multiple files,
separated by the "GO" statement. Run each of these files individually,
but use the same connection. That's all "GO" does.
>
So you need to remove the "GO" batch separators in between your
statements that need to be run in the same batch.
>
-Dave Markle
http://www.markleconsulting.com/blog
>

|||To be totally honest with you, I think the easiest/best way to solve
this would be to write a batch file that calls OSQL or SQLCMD against
the proper version of the file. Put your version-switching logic in
the batch file, and simply run OSQL on the appropriate files.

Some people execute their batches using sp_executesql, but it's really
messy and I don't really recommend it. Basically, using this method,
you'd be doing things like:

EXEC sp_executesql 'CREATE TABLE dbo.foo'
EXEC sp_executesql 'CREATE INDEX IX_xxx ON dbo.foo'
...

instead of:

CREATE TABLE dbo.foo
GO
CREATE INDEX IX_xxx ON dbo.foo
...

AFAIK, that's the only way to do what you want to do in 100% pure
T-SQL.

-Dave

BF wrote:

Quote:

Originally Posted by

Thanks for the quick respond.
>
The solution is not quite what I was hoping for.
>
For each new version I create an update script, We have an app which
does that and there are lots of Go commands.
>
I want to have one update script for all versions of the app so we have
2.00 to 2.01 to 2.02 to 2.03 etc.
>
For each version I have a script and I want to lookup the version, if
version is 2.03 I can start updating from 2.03 to 2.04 with the goto I
can jump over all other updates because they are already done in the
past.
>
When I use different files I cannot easy control which files to
execute, or I have to run them from the main script.
>
Grtx Bob
>
dmarkle schreef:

Quote:

Originally Posted by

Here's the trick with "GO":

It's not actually a part of the T-SQL language. It's a batch
separator. (Don't believe me? Try running "exec('GO')" in Query
Analyzer.)

Think of it like this: Cut up your script into multiple files,
separated by the "GO" statement. Run each of these files individually,
but use the same connection. That's all "GO" does.

So you need to remove the "GO" batch separators in between your
statements that need to be run in the same batch.

-Dave Markle
http://www.markleconsulting.com/blog

|||BF (bob@.faessen.net) writes:

Quote:

Originally Posted by

For each new version I create an update script, We have an app which
does that and there are lots of Go commands.

No there isn't. There are a lot of GO separators.

Quote:

Originally Posted by

I want to have one update script for all versions of the app so we have
2.00 to 2.01 to 2.02 to 2.03 etc.
>
For each version I have a script and I want to lookup the version, if
version is 2.03 I can start updating from 2.03 to 2.04 with the goto I
can jump over all other updates because they are already done in the
past.
>
When I use different files I cannot easy control which files to
execute, or I have to run them from the main script.

Right. The best way is to solve this is to write a little script runner that
reads a suite of files, and from the file names decudes which version the
file applies to, and then runs the file if needed. Your script would have to
break the script apart on the "go" separator, but this is trivial stuff.
(Hint: don't worry about "go" being entwined in comments ot string literals.
The standard query tools don't do that either. But care about leading and
trailing blanks, and inconsistent use of upper/lowercase.)

You can write this simple script runner in about any language - except for
T-SQK.

--
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|||Ok, great answers:

I build the updates with installshield 12 and I am no programmer so I
will go and try which will fit for me.

Probably I will place all scripts in the support dir from installshield
12 and run the from a vbscript with sqlcmd based on some tests.

I will try some things this week.

Thanks for the replies.

Grtx Bob

Erland Sommarskog schreef:

Quote:

Originally Posted by

BF (bob@.faessen.net) writes:

Quote:

Originally Posted by

For each new version I create an update script, We have an app which
does that and there are lots of Go commands.

>
No there isn't. There are a lot of GO separators.
>

Quote:

Originally Posted by

I want to have one update script for all versions of the app so we have
2.00 to 2.01 to 2.02 to 2.03 etc.

For each version I have a script and I want to lookup the version, if
version is 2.03 I can start updating from 2.03 to 2.04 with the goto I
can jump over all other updates because they are already done in the
past.

When I use different files I cannot easy control which files to
execute, or I have to run them from the main script.

>
Right. The best way is to solve this is to write a little script runner that
reads a suite of files, and from the file names decudes which version the
file applies to, and then runs the file if needed. Your script would have to
break the script apart on the "go" separator, but this is trivial stuff.
(Hint: don't worry about "go" being entwined in comments ot string literals.
The standard query tools don't do that either. But care about leading and
trailing blanks, and inconsistent use of upper/lowercase.)
>
You can write this simple script runner in about any language - except for
T-SQK.
>
--
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

global vars in script files ?

Is it possible to declare a global variable in a SQL script ... or some
workaround ?
[ContentDB].[dbo].[PageTypes].[ptId] IDENTITY(int, 1,1)
In the following script, the local var @.ptId is lost once a "GO" is
executed.
USE [ContentDB]
GO
INSERT INTO [dbo].[PageTypes]
([ptName]
,[ptPath]
,[ptParamName])
VALUES
('unused'
,'/redirect.aspx'
,'url')
DECLARE @.ptId int
SET @.ptId = @.@.IDENTITY
.
.
.
.
<lots and lots of other SQL>
.
.
.
.
GO
.
.
.
.
<lots and lots of other SQL>
.
.
.There are no global variables in TSQL. You can use a temp table for this, or
check out SET
CONTEXT_INFO.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"John A Grandy" <johnagrandy-at-yahoo-dot-com> wrote in message
news:eYtWs8JRGHA.1688@.TK2MSFTNGP11.phx.gbl...
> Is it possible to declare a global variable in a SQL script ... or some wo
rkaround ?
> [ContentDB].[dbo].[PageTypes].[ptId] IDENTITY(int, 1,1)
> In the following script, the local var @.ptId is lost once a "GO" is execut
ed.
> USE [ContentDB]
> GO
> INSERT INTO [dbo].[PageTypes]
> ([ptName]
> ,[ptPath]
> ,[ptParamName])
> VALUES
> ('unused'
> ,'/redirect.aspx'
> ,'url')
> DECLARE @.ptId int
> SET @.ptId = @.@.IDENTITY
> .
> .
> .
> .
> <lots and lots of other SQL>
> .
> .
> .
> .
> GO
> .
> .
> .
> .
> <lots and lots of other SQL>
> .
> .
> .
>

Global Variables And the Script Component in DataFlow

I can't find anything on how to get to a global variable in a script component in the dataflow. I can get to it in a script task with no problem by using dts.variables but i doesn't appear you can do the dts variables in the script component.

I did add it to the readwrite variable list but I haven't been able to access it.

Type "Me.Variables." and upon typing the second period, intellisense will kick in and give you a alist of all the vars that you have put into ReadOnlyVariables/ReadWriteVariables.

-Jamie

Global function for different different task

Hi,

I've a function which I'm using in one of my script task. Then I do some operations and in other script task I'm using same function. If I change a function in one task then I need to do in all other tasks. Is there a way where we can write this function at package level and be able to use in any of the task.

Regards

Munafbhai

You cannot do this with scripts in packages. You can however create a managed dll library (in C-sharp or Vb) and make a reference to this dll in your script package.

Sometimes when you use SQL server or another relational DB, you could use stored procedure, to avoid cutting and pasting code in several package scripts.