I want to pass a command line values into an osql run stored procedure.
The commandline values should be are the values to put into the insert stored procedure that writes them to a table.
Is it possible to do this.
Hi,I have dumped a very large database from mysql (using mysqldump program)as a raw sql file. The reason was, convert this database to a MSSQLdatabase. Since mysqldump creates the file as raw sql file with thedatabase-table structures and the data in it, I thought using OSQL commandline utilities should work to out this whole database in MSSQL server.I have run this command from command line:osql -u sa -i mysqldump.sqlIt is going since yesterday. It has been almost 36 hours that it'sstarted. And in the mssql server, I see no database created yet. On thescreen of the command line, I see bunch of numbers are going in order. Iassume they are row numbers of the tables processed. But, if it is doing it,then where is it saving all this data ? I have checked the tempdb, pub db,other dbs, and I see no tables related to the database I am inserting. Willit populate it at the and of the job ? Or, am I doing something wrong here?Regards.Murtix.
View 2 Replies View RelatedI need to execute xp_cmdshell in a trigger and pass a command lineparameter to the .exe programi.e. I have a .exe program c:program filessavedata.exeIn the trigger I have a parameter @Id bigintI need to pass the parameter @Id to savedata.exeIn the trigger, I am tryingEXEC master..xp_cmdshell 'cmd.exe /C "c:program filessavedata.exe "'+ CAST(@Id as varchar)BUT this does not work.Can someone please help me with how to do this?Thanks,db
View 1 Replies View RelatedI am not sure if this has been asked before but I couldn't find any thread talking about this.
Let's say we have a parameter in the .sql input file called @Start_Date, how can we pass the value of a particular date, for example, "02-28-2007" to @Start_Date via SQLCMD? is it possible?
I'm trying to skip the need to write a simple windows application...if things can be achieved via dos command line, that will keep everything simple!
thanks! :)
I have a ForEachLoop parent pkg that reads thru the file system of dtsx to be executed.
I would like to pass CLI parms to the command itself (dtexec). I'm not looking at passing values to the child pkgs but rather additional command line values such as:
/CONFIGFILE "C:myconfig.dtsConfig.
In my expression builder for the PackageName I added the additional string but during the execution, the reference to my dtsconfig is ignored without any errors and the child pkg runs using the default values provided at design time.
@[User::PackageToRun] + " /CONFIGFILE C:\myconfig.dtsConfig ".
How does one add additional CL values during the runtime for Execute Package Task?
Thanks,
Anatole
We are trying to start a server job running an SSIS package and supply some parameters to the package when we start the job using SMO.
What we have now is this:
string cmdLine = job.JobSteps[0].Command;
cmdLine += @" /SET PackageGetGroupRatingYear_Id.Variables[User::RatingId].Value;1";
cmdLine += @" /SET PackageGetGroupRatingYear_Id.Variables[User::GroupId].Value;1";
cmdLine += " /SET \Package.Variables[User::period].Value;"" + periodEndDate + """;
job.JobSteps[0].Command = cmdLine;
job.Start();
It appears that when the job is run, the modified command line is not used.
What is needed to supply runtime parameters to a job step when starting the job via SMO?
Thanks,
I want to run a sql script that creates some views using "osql". The views in turn refer to user defined functions which need a two-part name reference(MyUser.MyFunction). Is there a way to do this without hardcoding the value of the "MyUser" in the script?
Example..
I want to run a script called myscript.sql using OSQL. The file myscript.sql looks like this..
CREATE view V1
AS
SELECT *, UserOwner1.MyFunction1(5)
FROM MyTable
go
CREATE view V11111
AS
SELECT *, UserOwner1.ScalarFunction11111(7)
FROM MyTable
go
-----
I do not want to hardcode the username (UserOwner1) since it varies from database to database. So when I run this script from command prompt using "osql" I want the ability to supply the value for the username (UserOwner1) which owns the user defined function. How to do this?
This problem would go away if SQLServer 2000 was not so adamant about requiring two part reference to a user defined function.
Thanks!
Hi
We already used Oracle Datasatage Server the following Query statement for Source and Lookup.here there is parameter maping in the SQl Statement . How can achive in SSIS the Folowing Querystatment?
Query 1: (source View Query)
SELECT
V_RDP_GOLD_PRICE.GDR_PRODUCT_ID, V_RDP_GOLD_PRICE.ASSET_TYPE, V_RDP_GOLD_PRICE.PREFERENCE_SEQ, V_RDP_GOLD_PRICE.RDP_PRICE_SOURCE, TO_CHAR(V_RDP_GOLD_PRICE.PRICE_DATE_TIME,'YYYY-MM-DD HH24:MI:SS'), TO_CHAR(V_RDP_GOLD_PRICE.REPORT_DATE,'YYYY-MM-DD HH24:MI:SS'), V_RDP_GOLD_PRICE.SOURCE_SYSTEM_ID
FROM
V_RDP_GOLD_PRICE V_RDP_GOLD_PRICE
WHERE
REPORT_DATE = (select max(report_date) from V_RDP_GOLD_PRICE where source_system_id = 'RM' )
Query 2: (look up )
SELECT
GDR_PRODUCT_ID,
TO_CHAR(MAX(PRICE_DATE_TIME),'YYYY-MM-DD HH24:MI:SS') ,
TO_CHAR(REPORT_DATE,'YYYY-MM-DD HH24:MI:SS')
FROM
V_RDP_GOLD_PRICE
where
GDR_PRODUCT_ID = :1 and
report_date = TO_DATE(:2,'YYYY-MM-DD HH24:MI:SS') AND
PRICE_DATE_TIME BETWEEN TO_DATE(:2,'YYYY-MM-DD HH24:MI:SS') - 7) AND TO_DATE(:2,'YYYY-MM-DD HH24:MI:SS')
GROUP BY GDR_PRODUCT_ID, TO_CHAR(REPORT_DATE,'YYYY-MM-DD HH24:MI:SS')
please anyone give the sample control flow and how to pass the parameter?
Thanks & regards
Jeyakumar.M
Hi,
I'm running the osql below from a batch file:
%SQLPath%osql -U%DBUser% -P%DBPswd% -S%SQLServer% -d%Gatewaydb% -w256 -b -n; -c -h-1 -iBatchScriptsSQL_Get_Create_Batch1.sql -oZ:mboxmmis835increate_835_1.bat
My output file comes out fine with one exception, there is a blank space at the beginning of each line. Is there a way to remove this ? One suggestion I have seen is to do this with BCP instaed of osql but I don't know if BCP can "run" my "SQL_Get_Create_Batch1.sql" file above. The ".sql" creates a temporary table and then does a select.
Thanks,
J
Hi,
I would like to design a SSIS package, which have couple of variables. It loads a xls file specified in a variable [varExcelFileFullPath] .
I will run it by commands: exec xp_cmdshell 'dtexec /SQL ....' (pls see an example below).
It seems it does not get the values passed in for those variables. I deployed the package to a sql server.
are there any grammar errors here? I copied it from dtexecui. It worked inside Dtexecui not in dos command.
exec xp_cmdshell 'dtexec /SQL "LoadExcelDB" /SERVER test /USER *** /PASSWORD ****
/MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW
/LOGGER "{6AA833A1-E4B2-4431-831B-DE695049DC61}";"Test.SuperBowl"
/Set Package.Variables[User::varExcelFileName].Properties[Value];"TestAdHocLayer"
/Set Package.Variables[User::varExcelWorkbookName].Value;"Sheet1$"
/Set Package.Variables[User::varExcelFileFullPath].Value;"D: estshareTestAdHocLayer.xls"
/Set Package.Variables[User::varDestinationTableName].Value;"FeaturesTmp"
/Set Package.Variables[User::varPreSQLAction].Value;"delete from FeaturesTmp"
'
thanks,
Guangming
Hi,
How do I execute all the .sql scripts in a folder with OSQL command?
Thanks in advance,
Hari Haran Arulmozhi
I am running the following OSQL command and capturing the return codefor the error .Whenver i have an error like server not exists or uableto login I get a return code of 1 for the %ERRORLEVEL%.Howeverwhenever I have an errorof a wrong dbcompatibility error the retuncode id 1 even though sql returns an iformation message from OSQL thatthe right copatibilty levels are 60,70 and 80.How can i get OSQL toreturn the right return code whenver a error of this type occurrs frombatch mode sql.The OSQL i am running from the batch isosql -S%SrvName% -U%Username% -P%Userpswd% -n -w 132 -d%DBname%-Q%sqlcmd% -o%Dirrpt%\%DBname%_%SPname%.txtECHO %errorlevel% >> %logbatch%IF %ERRORLEVEL% NEQ 0 Goto SQLErrorsqlcms is exec sp_dbcompatibiltylevel srvrname, dbname 80Thanks in anticipation.Ajay
View 3 Replies View RelatedI have a process that is running on the os. This process is picking up FTP files every 5 min, it renames them so not to confuse them with the new files, copies all renamed files into one file to be used by bulk insert, runs the bulk insert to populate a table, and then runs the stored procedure that scrubbing the data and insert it into another table. For every transaction that I do in my stored procedure, I do the error checking as follows:
IF @@error <> 0
BEGIN
ROLLBACK TRANSACTION
RETURN
If my stored procedure encounters an error, return statement will stop it from running. If this happens, I need to stop the process that is running on the os as well.
Questions:
How can that be accomplished?
How to restart the stored procedure ones the error has been corrected?
Thank you for your help.
Hello,
I placed a post regarding this issue previously but no success. So I thought I explain everything properly this time in a new post. Thanks
I have created a stored procedure which passes variables to the ssis package and then executes the package.
The two variables inside the ssis package are @FileName and @ConnectionPath
As you can see from the below stored procedure, xp_cmdshell is used to execute the package.
If only the first variable is used in the package and the @connectionPath variable is hardcoded inside the package then package runs fine.
Problem is in this particular call as you see below because @ConnectionPath is included.
The output of print is:
dtexec /f d:sysapplCEMSSISImportsTradesBaseProfiles2.dtsx /set Package.Variables[User::FileName].Properties[Value];"d:ApplDataCEMWorkingTempprofiles.csv"
/set Package.Variables[User::ConnectionPath].Properties[Value];"Data Source=servername1instancename1, 2025;Initial Catalog=CounterpartyExposure;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;"
Error is:
Error: 2007-08-08 08:46:01.29
Code: 0xC0202009
Source: BaseProfiles2 Connection manager "CounterpartyExposure"
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft OLE DB Provider for ODBC Drivers" Hresult: 0x80004005 Description: "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified".
End Error
if only the output is run in the query analyser then the error is:
The identifier that starts with 'Data Source=gblond088sjyMSQL_curves_DEV1, 2025;Initial Catalog=CounterpartyExposure;Provider=SQLNCLI.1;Integrated Security=SSPI' is too long. Maximum length is 128.
/*********************************************************************************
uspCEMTradeExecutePackage2 'd:sysapplCEMSSISImportsTradesStaticMappingOverride.dtsx',
'StaticMappingOverride.csv',
'Data Source=servername1instancename1, 2025;Initial Catalog=CounterpartyExposure;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;'
*********************************************************************************/
ALTER procedure [dbo].[uspCEMTradeExecutePackage2]
@FullPackagePath varchar(1000),
@FileName varchar(500),
@ConnectionPath varchar(1000)
as
declare @returncode int
declare @cmd varchar(1000)
declare @FilePath varchar(1000)
declare @FullFilePath varchar(1000)
set @FilePath = 'd:ApplDataCEMWorkingTemp'
set @FullFilePath = @FilePath + @FileName
print ' ----------- ' + @FileName
set @cmd = 'dtexec /f ' + @FullPackagePath + ' /set Package.Variables[User::FileName].Properties[Value];"' + @FullFilePath + '"'
set @cmd = 'dtexec /f ' + @FullPackagePath +
' /set Package.Variables[User::FileName].Properties[Value];"' + @FullFilePath + '"
/set Package.Variables[User::ConnectionPath].Properties[Value];"' + @ConnectionPath + '"'
print @cmd
set nocount on
begin try
exec @returncode = master..xp_cmdshell @cmd
end try
begin catch
exec @LastGoodVersionSP
DECLARE @msg nvarchar(200)
SET @msg = ('Error during execute package')
EXECUTE uspErrorReporter @msg
end catch
set nocount off
iam trying to pass variable to a statement to grab data to from one DB table and pitch it in the same table in another DB base on evaluation like a where clause. but its not working what am i doing rong
here is the code
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ClientComment]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
Declare @BDFR varchar(20), @BDTO varchar(20), @EQID varchar(20), @TABLEDESC varchar(20), @DBO varchar(20)
set @TABLEDESC = 'ClientComment'
set @DBO = '.dbo.'
set @BDFR = 'Commander' + @DBO + @TABLEDESC
set @BDTO = 'Test_Commander'+ @DBO + @TABLEDESC
set @EQID = '80_300_113'
insert into @BDTO
select from @BDFR where Eqid = @EQID
Fairly new to triggers and stored procedures and so far I haven't had to pass any variables or even call an SP from a trigger. The problem I have is to pass a 'callid' variable from an insert trigger to a stored procedure.
The SP is to delete any rows that already exist that contain the passed 'callid' and then insert the new inserted data (using variables again hopefully).
Is this at all possible and if so what is the syntax for passing the variable from the trigger and then reading it into the stored procedure ?
TIA
I have a project that uses GUID's througout and I'm completely stumped.
1) I create a "batch" GUID to batch the records I'm about to process.
2) I call a web service on a remote machine, and reserve the batch records by inserting the batch GUID into a string ---works fine
3)I call another web service that returns the rows that I just reserved as XML objects and insert into a string variable
4)I need to use the "batch"GUID variable which is typed as a string (DT_WSTR) as an added column so in a Data Flow Task I do the following:
a) use the XML string variable as the source of a XML Source Task -- works (now that I'm passing custom objects and not a dataset -- curious as to why I can't consume a dataset but thats a different question)
This is where things get tricky:
I've tried to add the BatchGUID as a derived column, as a datatype of DT_WSTR (unicode string) and convert it to uniqueidentifier in the Data Conversion task --- error, cannot convert unicode to uniqueidentifier (I know I can in C# and SQL Server so why not here).
I've tried to CAST the BatchGUID as a uniqueidentifier and pass that to the datasource -- again conversion error.
I've tried using the type Object and Casting to anything and that doesn't work either.
I've tried to pass the unicode all the way to the SQL Server Destination -- and insert into UniqueIdentifier field... again no go.
All help would be appreciated, at this point I can't see any way of using a UniqueIdentifier as a key field, and maintaining it through the package...
Is this a bug?
Oh and if you want to have some real fun, try returning the type of UniqueIdentifier as an output parameter using a ADO.NET connection.
Thanks!
I would like to include some information in the subject line of a report subscription email. Right now the default subject line is something like "@ReportName was executed at @ExecutionTime". is there a way to use one of my report parameters in the subject? I tried something like "Thank you for your order @OrderNum", but that did not work.
Thanks,
Stuart Fox
I’ve got the following piece of ASP code, which updated a
table through the standard edit/update command filed options.
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$
ConnectionStrings:CAT_SYSTEMConnectionString %>"
OldValuesParameterFormatString="original_{0}"
SelectCommand="sp_diplomaViewQualifications"
UpdateCommand="UPDATE
PsnQualifications SET quantity=@quantity WHERE rowstatus=1 and
qualId=@original_qualId"
DeleteCommand="UPDATE
PsnQualifications SET rowStatus=0, lastUpdateOn=getdate(), lastUpdateBy=@createdBy
WHERE rowstatus=1 and qualId=@original_qualId"
SelectCommandType="StoredProcedure">
<UpdateParameters>
<asp:Parameter Name="original_qualId"
/>
<asp:Parameter Name="quantity"
/>
</UpdateParameters>
<DeleteParameters>
<asp:Parameter Name="original_qualId"
/>
<asp:Parameter Name="createdBy" DefaultValue="TEST"/>
</DeleteParameters>
<SelectParameters>
<asp:Parameter Name="masterKey"
/>
</SelectParameters>
</asp:SqlDataSource>
My problem is that I need to pass the contents
of the VB variable ‘createdBy’ into the DeleteParameters option.
This is defined in the code as: Dim createdBy As String = getUserLoginName(Me.Page)
How do I pass this into the update and/or delete part
of the command field on the ASP page?
Can any one please help me. how to pass the database connection through variables to SSIS.
And also calling SSIS from visual studio.net
Regards
Ravi
Hey Guys:
--- Not sure if this should be moved to webforms forum, or if it belongs here ---
Alright, I have been dealing with this issue for a few days now, and have found a few solutions but they all seem to throw different errors so I figured I'd ask here.
What i am trying to do is have a webform where user enter data, and have the data passed across forms, then displayed and inserted into a database on another form. THe first for has an asp:rangevalidator control dymamicly built so I cannot simply take of the tags and use the old style.
Eventually the user will be directed to a paypal form, and upon successful completion be redirected to the page with the insert command within it, but for now, passing it to a second page for review, then inserting it will work.
I am not sure how to accomplish this, a tutorial or a code example would be great!! I have though about panels, creating public objects, etc, but all the solutions I have found have one issue or another when I attempt to create them.
I'm using asp.net 1.1, VB.net and SQL server.
Thanks,
Brian Sierakowski
Hi
Does anybody know how to pass values from asp dot net to SSIS package variables ?
Currently I have an SSIS package for monitoring windows service... for that...
I have to pass the Server-IP Addrress, UserName, Password, Service Name as Parameter.
I would like to pass these parameters through an Interface from RUN TIME.
Please help this problem
Regards
Deepu M.I
Can any one please help me. how to pass the database connection through variables to SSIS.
And also calling SSIS from visual studio.net
Regards
Ravi
Hi,
I want to pass below given query into a variable
"if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ <POS_MONTH>]]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[ <POS_DATE>]
GO
SELECT * INTO [dbo].[<POS_DATE>] ]
FROM SG_POS_Template
WHERE 1 = 0;
GO"
Where [<POS_DATE>] is a parameter by which value will be assigned dymanically.....anybody please help me out....!!
Using Query Analyzer, I can right click on an object and select "scriptobject to new window as create" and I get the text of the object'sdefinition (schema). Can I get same result from command line, i.e., fromosql, I can get text output for the definition of the object (something likedefncopy under Sybase)? If so, what is the command or store procedure name?Thanks in advance.
View 2 Replies View RelatedIs there any command line that I can use to do select from databases (SQL 2005)
View 4 Replies View RelatedHi all, i have a database, the password column is normal format , and i want to update all the data in the column password to md5 format , can any one tell me what the command for me to do it ?
is this command will work ?
update tbl_MEMBERS set clm_password=(md5(clm_pass) where id<100
any one have ideas how to do it?
bbboy
hi I would appreciate if someone demonstrate how to automate ftp in a command line from within a batch file. I do want to move certain files from one server to another via ftp command line in an automatic fashion via running the batch periodically.
thanks
Ali
hi: I can't seem to run BCP thru the command line.
bcp pubs..authors out c:authors.txt -c -Usa -P
error:
Msg 170, Level 15, State 1, Server Y47SA, Procedure , Line 1
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near'.'.
I've been trying to run BCP thru command line all day. I tried OSQL, ISQL. I find this utility very
frustrating. btw - I have installed SP2.
any thoughts would be much appreciated.
TIA
deanna
Anyone know where I can find some command line tools such as RCMD etc.. Thank you.;)
View 1 Replies View RelatedI'm trying to insert into a table 2 values one of which is an
exec master..xp_cmdshell @command where I have assigned @command with a value
this statement gives me the result into a 1 col. table fine:
insert into mytable99(col1) exec master..xp_cmdshell @command
now what I want to do is put col2 in there as well!!
ie. insert into mytable99(col1,col2) values (exec master..xp_cmdshell @command, '123')
I get a sytax error ... on the exec ??
Could anyone help re the proper way of doing this ... thanks in advance
I use a similar command below to insert into a temp table the result of a large command line call to an exectable with many parameters passed in the command of which the result passed back contains many items. I then parse the response string to get my results...
set @command = 'dir'
insert into tsverisign(response) exec master..xp_cmdshell @command
My question is our can I insert two values at the same time to this same table one of which is my "exec master..xp_cmdshell @command"
similar to insert into tables (field_a, feild_b) values ('1','2')
Something like (and I know this does not work):
insert into tsverisign(response,trans_id)
values (exec master..xp_cmdshell @command, '123')
Any help would be greatly appreciated .... PS I'm new to MS SQL 2000 and proper syntax etc. etc. so I need full example so I can try. :rolleyes:
Hi All,
I was given a task of coming up with the script to recreate an existing database using a command line. I would use this script in case when the server is down and I can't get to Query Analyzer or EM to recreate it. I am not sure where to start. Any ideas are greatly appreciated.
Thanks.