Executing rs utility.

Hi,

I have two doubts :

1. The rs Utility has 2 arguments : -u username & -p password.

Is there any way by which we can use encryption/decryption for these parameters ?

2. Is there any way by which we can specify a url which indicates the machine name where the rs utility should be executed.

As rs utility would be present only on the machine where Reporting Services are installed. A way to specify the machine name where the rs utility should be executed would be helpful.

Thanks in advance.

1. No, unless you have custom security extension that knows how to decrypt them

2. The utility takes serverURL parameter.

|||

Thanks for the replies.

1. Even if we have I have a custom security extension, the rs utility should know how to decrypt the username/password. Can you please explain in some detail of how to use custom security extension ?

2. The serverURL parameter which the utility takes is the reportServerURL. I was searching for scenario where in :The Reporting Services (rs utility) are installed on X machine and I want a way to say that execute the rs utility on the X machine from Y machine. Is there any way to do this.

|||

1. A custom security extension (aka Forms-based Authentication) allows you to replace the default SSRS Windows-based security model, as explained in more details here.

2. So, your question is how to get an exe to execute on another box when I start it locally? I don't think you can do this.

Executing Multiple Scripts

Hey guys,
What is the best way to run multiple sql scripts against a database.
Vendor provided a DBupdate utility but it isn't working properly....
any suggestions...
thanks,
jonathanOSQL with a batch file. DTS/SSIS. Scheduled job.
Many ways to do this...|||do you know of a site or book where I can read how to do so....
a good search string for google....|||just giving it away today...

DECLARE @.SQLServer VARCHAR(100)
DECLARE @.Database VARCHAR(100)
DECLARE @.UserName VARCHAR(100)
DECLARE @.Password VARCHAR(100)
DECLARE @.UseWindowsAuthentication BIT
DECLARE @.Path VARCHAR(1000)
DECLARE @.FilePathToSQLFiles VARCHAR(200)

/*################################################# ###########################
If you are unsure of your sql server name, you can use the following
SELECT @.@.SERVERNAME. This should be the sql server where the database resides
that you are updating.
################################################## ###########################*/
SET @.SQLServer = 'MyServer'

/*################################################# ###
@.Database is the name of the database you are updating.
################################################## ####*/
SET @.Database = 'MyDB'

/*################################################# ###
Is the path to the sql files that you wish to execute.
Example FilePath : C:\SQL Scripts\
################################################## ###*/
SET @.FilePathToSQLFiles = 'C:\SQL Scripts\Create Scripts\'

/*################################################# ###################################
If you choose to use windows auth, you do not have to fill in a user name or password,
but your network account has to be a sysadmin on the sql server.
1 = use windows auth
0 = sql auth
################################################## ##################################*/

SET @.UseWindowsAuthentication = 1
SET @.UserName = ''
SET @.Password = ''

CREATE TABLE #SQLFiles ( SQLFileName VARCHAR(2000))

SET @.Path = 'dir /b "' + @.FilePathToSQLFiles + '*.sql"'

INSERT INTO #SQLFiles
EXECUTE master.dbo.xp_cmdshell @.Path

DECLARE cFiles CURSOR FOR
SELECT DISTINCT [SQLFileName]
FROM #SQLFiles
WHERE [SQLFileName] IS NOT NULL AND
[SQLFileName] <> 'NULL'
ORDER BY [SQLFileName]

DECLARE @.vFileName VARCHAR(200)
DECLARE @.vSQLStmt VARCHAR(4000)

OPEN cFiles

IF @.UseWindowsAuthentication = 0
BEGIN

FETCH NEXT FROM cFiles INTO @.vFileName
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.vSQLStmt = 'master.dbo.xp_cmdshell ''osql -S ' + @.SQLServer + ' -U ' + @.UserName + ' -P ' + @.Password + ' -d ' + @.Database + ' -i "' + @.FilePathToSQLFiles + @.vFileName + '" >>"' + @.FilePathToSQLFiles + 'LogFile_' + CONVERT(VARCHAR,GETDATE(),102) + '_' + @.SQLServer + '_' + @.Database + '.txt"'''
--PRINT @.vSQLStmt
EXECUTE (@.vSQLStmt)
FETCH NEXT FROM cFiles INTO @.vFileName
END

END

IF @.UseWindowsAuthentication = 1
BEGIN

FETCH NEXT FROM cFiles INTO @.vFileName
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.vSQLStmt = 'master.dbo.xp_cmdshell ''osql -S ' + @.SQLServer + ' -E -d ' + @.Database + ' -i "' + @.FilePathToSQLFiles + @.vFileName + '" >>"' + @.FilePathToSQLFiles + 'LogFile_' + CONVERT(VARCHAR,GETDATE(),102) + '_' + @.SQLServer + '_' + @.Database + '.txt"'''
--PRINT @.vSQLStmt
EXECUTE (@.vSQLStmt)
FETCH NEXT FROM cFiles INTO @.vFileName
END

END

CLOSE cFiles
DEALLOCATE cFiles

Print '################################################# ################################################'
Print 'Please review the log file located at ' + @.FilePathToSQLFiles + 'LogFile_' + CONVERT(VARCHAR,GETDATE(),102) + '_' + @.SQLServer + '_' + @.Database + '.txt'
Print '################################################# ################################################'
GO
DROP TABLE #SQLFiles
GO|||Much much appreciated...|||just giving it away today...

Slut

This message is to short

executing DTS Package from stored procedure

There is a DTS package that work when when it is executed directly
from Enterprise Manager or dtsrun utility.
But it does not work when it is executed from a stored procedure.
Do you have any idea?How are you executing the DTS package from the proc? What does "does not
work" mean? Turn on logging (under Package Properties) to see any error
messages.
David Portas
SQL Server MVP
--|||Following is the code of proc that call DTS.
When I run DTS package directly from Enterprise Manager, it works as I want
(185 rows added).
But when I use following procedure, there is no row added.
CREATE PROC [dbo].[Usp_CFDBE13ExecuteDTSPkg]
@.PkgName varchar(255)
AS
SET NOCOUNT ON
/*
Return Values
- 0 Successfull execution of Package
- 1 OLE Error
- 9 Failure of Package
*/
DECLARE @.hr int, @.ret int, @.oPKG int
--1. Create a Pkg Object
EXEC @.hr = sp_OACreate 'DTS.Package', @.oPKG OUTPUT
IF @.hr <> 0
BEGIN
--Create Package object failed
EXEC Usp_CFDBE13DisplayOAErrinfo @.oPKG, @.PkgName
RETURN 1
END
--2. Load the Pkg
EXEC @.hr = sp_OAMethod @.oPKG,'LoadFromSqlServer', NULL,
@.ServerName='', @.PackageName=@.PkgName, @.Flags=256
IF @.hr <> 0
BEGIN
-- LoadFromSQLServer failed'
EXEC Usp_CFDBE13DisplayOAErrinfo @.oPKG,@.PkgName --, @.hr
RETURN 1
END
--3.Execute Pkg
EXEC @.hr = sp_OAMethod @.oPKG, 'Execute'
IF @.hr <> 0
BEGIN
--Execute failed'
EXEC Usp_CFDBE13DisplayOAErrinfo @.oPKG,@.PkgName --, @.hr
RETURN 1
END
--4.Check Pkg Errors
--EXEC @.ret=spDisplayPkgErrors @.oPKG --Will do it later
--5.Unitialize the Pkg
EXEC @.hr = sp_OAMethod @.oPKG, 'UnInitialize'
IF @.hr <> 0
BEGIN
--UnInitialize failed
EXEC Usp_CFDBE13DisplayOAErrinfo @.oPKG,@.PkgName --, @.hr
RETURN 1
END
--6.Clean Up
EXEC @.hr = sp_OADestroy @.oPKG
IF @.hr <> 0
BEGIN
EXEC Usp_CFDBE13DisplayOAErrinfo @.oPKG,@.PkgName --, @.hr
RETURN 1
END
RETURN 0
GO|||If you haven't already done so, turn on logging in the package so that
you can trap errors there and determine that the package does run. The
package execute method alone won't show up package errors.
Using a blank server name in the LoadFromSQLServer method defaults to
the default server instance _not_ to the instance in which your code is
executing. If you are targeting a non-default instance you must specify
the name. Better still, specify the name anyway. You can use
SERVERPROPERTY('SERVERNAME') to retrieve the name of the current
instance if you need to.
Be aware that your package will execute in the security context of the
local or domain account configured for the SQL Server service. That
account must therefore have access to any files or other resources
required by the DTS package.
Finally, make sure that your package uses fully qualified names for any
files that it accesses. The drive mappings available when you execute
locally in Enterprise Manager may not be available on the server.
David Portas
SQL Server MVP
--|||Thank you very much.
I got it.

Execute Package Utility

My SSIS package carries out one data flow task after another e.g.

truncate tables

Copy table 1

cleanup tables

When using the execute package utility in SSMS to run the package , the data flow tasks are listed alphabetically, so it reads:

cleanup tables

Copy table 1

truncate tables

Can I change this so the data flow tasks are listed in the logical order I have specified them in the package as it makes it difficult to work out which step the package is at. And while I'm at it, it keeps telling me every separate task's elapsed time is zero when it's finished. It records the start and end time ok, but it can't seem to work out the elapsed time?!

Thanks

I see your problem. Please go ahead and file a bug for the same.

http://connect.microsoft.com/SQLServer

We will take a look into this.