SQLCMD - How Do I Pass Parameter At Dos Command Line To Input File?
Mar 23, 2007
I 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! :)
View 3 Replies
ADVERTISEMENT
Jul 20, 2005
I 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 Related
May 23, 2008
Hi,
I have a SQL script file which creates a table and and inserts all data required by the client app into the table.
The table also holds images in a varbinary field. How do I insert an image held on the file system (e.g. "C:ImagesMyImage.gif") into a varbinary field using a script that is run using sqlcmd on the command line?
Any pointers greatly appreciated!
Mike
View 8 Replies
View Related
Apr 7, 2008
hello,
I am new to SQL sever and would like to connect to a particular database on the server using SQL. I have looked at various SQL sites with how to and none mention where I can locate the Input File name.
View 6 Replies
View Related
Jul 31, 2006
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
View 1 Replies
View Related
Sep 13, 2004
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.
View 1 Replies
View Related
Feb 23, 2007
hi,
how to pass Datasource Name as input parameter from command propmt to rdl file in sql server 2005?
Thanks,
shanthi
View 3 Replies
View Related
Oct 2, 2006
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
View 3 Replies
View Related
Mar 27, 2007
I have a stored proc that inserts a customer and it expects a timestamp input parameter. I dont know what a timestamp datatype is for sql 2005 and Ive tried to parse all sorts of data types but the proc errors out saying it needs "Byte[]" which Ive tried. Can anyone help me with this? ThanksRyan
View 2 Replies
View Related
Nov 15, 2007
Hi all,
I want to pass a parameter into my OLE DB source. For eg, I want to set the condition
SELECT * FROM "tablename" where ID = parameter <-----------------
I do not know how to do this? can it be done by using package variable?
View 2 Replies
View Related
Jan 17, 2006
Hello,
I want to use an optional parameter at the command line for my package.
I planned to make the first task a script which simply checks the variable (which is a string), and if it is empty, go one way, and if it is not, go another way. Is the best to go?
Many thnaks in advane
View 4 Replies
View Related
May 6, 2015
i am parameterize my DB connection in sql sever 2012,it showing in agent job as parameter.but when i run job it giving me error:
Argument "" for option "parameter" is not valid. The command line parameters are invalid. The step failed.
what i need to do is pass different environment here while deploying to different env.
View 2 Replies
View Related
Jul 20, 2005
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 Related
Jul 20, 2005
My client will be receiving a .dbf file which needs to be uploadedinto a sql server database table (as an append) every week. They areNOT computer savvy and I would like to automate this process ratherthan go into enterprise manager and run data transformation.Is there any way to write a batch file or a set of command lines whichwill do this?Thanks.Monica
View 3 Replies
View Related
Mar 18, 2006
Hi Everyone,
I tried to pass file name as a parameter in procedure, but it did work. Here are the codes
create procedure spImport
@filename varchar(100) as
bulk insert mytable
from @filename
end
I received following error message
Msg 102, Level 15, State 1, Procedure spImport, Line 4
Incorrect syntax near '@filename'
Could anybody help me to correct it. Thanks in advance.
Kevin
View 5 Replies
View Related
Nov 30, 2005
hi,i have created a stored procedure to read xml dataCREATE PROCEDURE InsertXML(@xml varchar(1000)) AS DECLARE @XmlHandle intEXEC sp_xml_preparedocument @XmlHandle output,@xmlinsert into Employee(Name,ID,Sal,Address) (SELECT Name,ID,Sal,AddressFROM OPENXML (@XmlHandle, 'emp:EmployeeDetails/emp:Employee',2) WITH (Name varchar(30) 'Name', ID int 'ID', Sal int 'sal', Address varchar(30) 'Address'))EXECUTE sp_xml_removedocument @XmlHandlebut it is taking only xml text as input.but i want to send the file name as input.how to do it.
View 2 Replies
View Related
Dec 18, 2006
I have problems with passing strange looking passwords from my win.form application to sqlcmd.
I tried to do this:
sqlcmd -S myServer -U myUser -P 2pQ-""xELfG~fze<= -d "McSpalDB" -i "C:myProcedure.sql" -o "C: empmyOutput.out"
but it fails, so I tried to encapsolate the passwoard in "":
sqlcmd -S myServer -U myUser -P "2pQ-""xELfG~fze<=" -d "McSpalDB" -i "C:myProcedure.sql" -o "C: empmyOutput.out"
but it fails too, because i have "" in my pass, since " isn't a forbiden symbol.
How to pass strange looking passwoards to sqlcmd?
View 3 Replies
View Related
Feb 29, 2008
Each day I receive a file with a different name. For example, the name is filename_mmddyyyy.txt where filename_ stays constant and mmddyyyy is the date of the file. The file is always in the same format.
I want to build an SSIS where I pass it this file name. I can write a script to generate the correct file name. How do I build the SSIS so it can accept the input parameter and find the correct file to process?
Thanks
View 3 Replies
View Related
Sep 17, 2007
SQL Server Compact 3.5 - I've a sdf file created in a .NET windows desktop command line program. How to now consume the data in MS Excel?
I can see that under
C:Program FilesMicrosoft SQL Server Compact Editionv3.5
I've one DLL called
sqlceoledb35.dll
But I don't have any oledb driver listed when creating an UDL file?
How can I consume the data in other apps that are not .NET and developed in house them?
Thanks, AM.
View 13 Replies
View Related
Sep 15, 2007
The MSDN Library topic Using the sqlcmd utility(SQL Server Express) states "To access the sqlcmd utility, click Start, click Run, and type sqlcmd.exe."
A command prompt window opens with "SQLCMD" in the title bar. I cannot enter anything in the command prompt window and the window remains open for only about five or six seconds.
The version I have installed is MS SQL Server 2005 Express with SP2. The cumulative updates package 3 does not list this problem, so I did not install it.
View 1 Replies
View Related
Sep 25, 2006
Hi everyone,
I am new to T-SQL and am trying to do some simple database for fun. Here is my problem and hope you guys can drop me some hint or solution:
OS: Win XP pro
SQL Server 2005 Express
Tool: SQLCMD in command prompt
I created a database and then create a table. Then I tried : select * from contract_Info, it gave me this:
1> CREATE TABLE Contract_Info
2> (
3> Order_No varchar(50) NOT NULL ,
4> Company varchar(255),
5> Product_Name varchar(255) NOT NULL,
6> Discount_Rate varchar(50), --Discount_Rate (%) -> Discount_Rate
7> Status varchar(50) NOT NULL,
8> Quantity varchar(50) NOT NULL,
9> Remainder varchar(50),
10> Deleted bit
11> )
12>
14>
15>
16> INSERT INTO Contract_Info values ('00000', 'DEFAULT','DEFAULT','0','Cancelle
d','0','0',0)
17>
18> go
(1 rows affected)
1> select * from contract_Info
2> go
Order_No Company
Product_Name
Discount_Rate Status
Quantity Rema
inder Deleted
-------------------------------------------------- -----------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------------------------------------------------------ -------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-- -------------------------------------------------- --------------------------
------------------------ -------------------------------------------------- ----
---------------------------------------------- -------
00000 DEFAULT
DEFAULT
0 Cancelled
0 0
0
(1 rows affected)
1>
In SQL Express Management , the output looks perfectly fine
What is missing??
Thanks for your time
View 6 Replies
View Related
Oct 11, 2007
the password of sa account is empt
I use "sqlcmd -S servername -U sa " command but failed
any suggestions?
thanks
View 8 Replies
View Related
Apr 24, 2007
Scenario:
I have installed SQL Express 2005 and I want to create a new DB using sqlcmd.
I am running a command like: sqlcmd -i file.ini (I am logged like Administrator of PC)
the content of the file.ini is:
-- Get the SQL Server data path
DECLARE @data_path nvarchar(256);
SET @data_path = 'c:Program FilesMyTestdatabase'
-- execute the CREATE DATABASE statement
EXECUTE ('CREATE DATABASE DataCollection ON
( NAME = DC_dat,
FILENAME = '''+ @data_path + 'DC.mdf'',
SIZE = 10,
MAXSIZE = 50,
FILEGROWTH = 5 )
LOG ON
( NAME = DC_log,
FILENAME = '''+ @data_path + 'DClog.ldf'',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB )'
) ;
GO
I obtain this error:
Msg 1802, Level 16, State 4, Server TESTQ5SQLEXPRESS, Line 1
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
Msg 5123, Level 16, State 1, Server TESTQ5SQLEXPRESS, Line 1
CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file 'c:Program FilesMyTestdatabaseDC.mdf'.
If I change the security of directory database (I add "modify" permission to Users windows group) the error disappear.
I am logged like Administrator... why is it necessary to change directory permission?
View 9 Replies
View Related
Feb 2, 2007
Hi,
I am planning to develop a single package that will download files from ftp server, move the files to internal file server and upload it in the database. But I want to run this package for multiple ftp file providers. For each provider the ftp server might be different and the transformation to upload the files into a database table might be different.
So can I create a single package and then multiple configuration files (xml), which will contain the details fo the ftp file providers and then pass the xml file as a parameter while executing the package. The reason being that the timings of fetching the files is different for each ftp file provider and hence cannot be combined into one.
Is this possible?
Thanks for your help.
$wapnil
View 6 Replies
View Related
Jan 21, 2008
My query has 9000 characters, so when I run BCP, I get "The input line is too long error". Is there any workaround? If so, can you please explain what it is?
Thanks in advance..
View 3 Replies
View Related
Dec 2, 2004
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!
View 4 Replies
View Related
May 20, 2008
Hello Everybody,
I am sure this has been asked a thousand times before in a thousand different ways, but here goes...
I am in need of the ability to prompt users for a From Date and To Date to insert into a WHERE clause in SQL queries. What is the easiest least painful way of accomplishing this? A great many of our customers utilized SQL Server Express or MSDE, so they do not have the full gambit of SQL tools handy. Believe me when I say that I have searched high and low across the internet for this solution. I was hoping I might be able to accomplish this through VB Script or J Script. Or, anything reasonable.
Thanks in advance for your time and patience,
Rocky Mountain Tech
View 1 Replies
View Related
Dec 7, 2005
(SQL Server Express 2005)
View 4 Replies
View Related
Feb 27, 2007
Hi,
for some AP issue, the file I upload must be without the line feed/carriage return in the last line.
for example:
original fixed-length file (exported from SSIS)
line NO DATA
1 AA123456 50 60
2 BB123456 30 40
3 CC123456 80 90
4 <-- with line feed/carriage return in the last line
The file format that AP request. The file only has 3 records, so it should end in the third line.
line NO DATA
1 AA123456 50 60
2 BB123456 30 40
3 CC123456 80 90
Should I use script component to do it ? I am new for VB . Anyone would help me ?
Thank you all.
View 1 Replies
View Related
Nov 2, 2007
Hi all,
I have the "Northwind" database in my Sql Server Management Studio Express.
In my C:ProSSEAppsSamplesForChapter02Chapter02 folder, I have the following 2 files:
(1) ListColumnValues (MS-DOS Batch File)
sqlcmd -S .sqlexpress -v DBName = "Northwind" CName = "CompanyName" TName =
"Shippers" -i c:prosseappschapter02ListListColumnVales.sql -o
c:prosseappschapter02ColumnValuesOut.rpt
(2) ListColumnValues (Microsoft SQL Server Query File)
USE $(Northwind)
GO
SELECT $(CompanyName) FROM $(Shippers)
GO
When I ran the following SQLcmd:
C:ProSSEAppsSamplesForChapter02Chapter02>ListColumnValues.bat
I got the following "ColumnValuesOut.rpt" with error messages:
'Northwind' scripting variable not defined.
Msg 102, Level 15, State 1, Server L1P2P3SQLEXPRESS, Line 1
Incorrect syntax near '$'.
'CompanyName' scripting variable not defined.
'Shippers' scripting variable not defined.
Msg 102, Level 15, State 1, Server L1P2P3SQLEXPRESS, Line 1
Incorrect syntax near 'CompanyName'.
I copied these T-SQL statements from a book and I do not know how to correct them.
Please help and tell me how to correct these errors.
Thanks in advance,
Scott Chang
View 3 Replies
View Related
Sep 25, 2006
I have a stored procedure which takes an input parm and is supposed to return an output parameter named NewRetVal. I have tested the proc from Query Analyzer and it works fine, however when I run the ASP code and do a quickwatch I see that the parm is being switched to an input parm instead of the output parm I have it defined as...any ideas why this is happening? The update portion works fine, it is the Delete proc that I am having the problems... ASP Code...<asp:SqlDataSource ID="SqlDS_Form" runat="server" ConnectionString="<%$ ConnectionStrings:PTNConnectionString %>" SelectCommand="PTN_sp_getFormDD" SelectCommandType="StoredProcedure" OldValuesParameterFormatString="original_{0}" UpdateCommand="PTN_sp_Form_Update" UpdateCommandType="StoredProcedure" OnUpdated="SqlDS_Form_Updated" OnUpdating="SqlDS_Form_Updating" DeleteCommand="PTN_sp_Form_Del" DeleteCommandType="StoredProcedure" OnDeleting="SqlDS_Form_Updating" OnDeleted="SqlDS_Form_Deleted"><UpdateParameters><asp:ControlParameter ControlID="GridView1" Name="DescID" PropertyName="SelectedValue" Type="Int32" /><asp:ControlParameter ControlID="GridView1" Name="FormNum" PropertyName="SelectedValue" Type="String" /><asp:Parameter Name="original_FormNum" Type="String" /><asp:Parameter Direction="InputOutput" size="25" Name="RetVal" Type="String" /></UpdateParameters><DeleteParameters><asp:Parameter Name="original_FormNum" Type="String" /><asp:Parameter Direction="InputOutput" Size="1" Name="NewRetVal" Type="Int16" /></DeleteParameters></asp:SqlDataSource>Code Behind:protected void SqlDS_Form_Deleted(object sender, SqlDataSourceStatusEventArgs e){ if (e.Exception == null) { string strRetVal = (String)e.Command.Parameters["@NewRetVal"].Value.ToString(); ............................Stored Procedure:CREATE PROCEDURE [dbo].[PTN_sp_Form_Del] (
@original_FormNum nvarchar(20),
@NewRetVal INT OUTPUT )
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @stoptrans varchar(5), @AvailFound int, @AssignedFound int
Set @stoptrans = 'NO'
/* ---------------------- Search PART #1 ----------------------------------------------------- */
SET @AvailFound = ( SELECT COUNT(*) FROM dbo.AvailableNumber WHERE dbo.AvailableNumber.FormNum = @original_FormNum )
SET @AssignedFound = ( SELECT COUNT(*) FROM dbo.AssignedNumber WHERE dbo.AssignedNumber.FormNum=@original_FormNum )
IF @AvailFound > 0 OR @AssignedFound > 0 /* It is ok if no rows found on available table, continue on to Assigned table, otherwise stop the deletion.*/
-----This means the delete can't happen...........
BEGIN
IF @AssignedFound > 0 AND @AvailFound = 0
BEGIN
SET @NewRetVal = 1
END
IF @AssignedFound > 0 AND @AvailFound > 0
BEGIN
SET @NewRetVal = 2
END
IF @AssignedFound = 0 AND @AvailFound > 0
BEGIN
SET @NewRetVal = 3
END
END
ELSE
BEGIN
DELETE FROM dbo.Form
WHERE dbo.Form.FormNum=@original_FormNum
SET @NewRetVal = 0
---Successful deletion
END
GO
-------------------------------------------------------- When I go into the debug mode and do a quickwatch, the NewRetVal is showing as string input.
View 2 Replies
View Related
Feb 23, 2006
Hi there,
I have an OLE DB Command which updates a table. However, the command needs to use the value of an input column more than once.
For example I want to update TableA only if either ColumnA or ColumnB have actually changed: -
update tableA
set columnA = ?,
column B = ?
where columnC = ?
AND (columnA != ? OR columnB != ?)
I can't map the Input column to more than one parameter so I've been forced to create a copy of columnA and ColumnB as input columns so I can map to the extra paramters that the Command shape expects.
I also attempted to modify the command syntax so it set up variables for the 3 values required and then set the values to parameters - but I get a very unhelpful syntax error message: -
declare @ValueA varchar(50),
@ValueB varchar(50),
@ValueC varchar(50)
select @ValueA = ?,
@ValueB = ?,
@ValueC = ?,
update tableA
set columnA = @ValueA ,
column B = @ValueB
where columnC = @ValueC
AND (columnA != @ValueA OR columnB != @ValueB)
Any suggestions?
View 3 Replies
View Related
Jul 23, 2005
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 Related
