Passing Variables To BATCH FILE

Aug 4, 1998

Similar to a previous thread, but not exactly. I have a batch file that takes
two date parameters. I have a SQL script that gets the values I want and assigns
them to variables, but I don`t know how to pass these to the batch.

I`m thinking of something like:

declare @myvar1 datetime, @myvar2 datetime

select @myvar1 = <some date value>
select @myvar2 = <some date value>

exec xp_cmdshell "c:mssqlscriptsMYBATCH.BAT" @myvar1 @myvar2

This just errors out when it reaches the variables in the last line. If I move
the variables inside the quotes, it passes them as literal text strings, not as
the assigned datetime values.

Any ideas? Is this possible?

Thanks,
Robert

View 3 Replies


ADVERTISEMENT

Passing Parameters To Batch File And Executing Batch File Loop

Aug 7, 2007

HELP,

I need to take a variable from a tabel in SQL Server pass to a Batch file and execute the batch file. Right now I can exec the batch file with XP_CMDSHELL but how can I pass the variable to the batch file and loop through all the variables.

Please help

Phil

View 4 Replies View Related

Passing Parameters To Sql Job Thru Batch File

Aug 14, 2001

Hi,
I am pretty new to DBA job, so please help me if you can.
I have to schedule a sql job (in sql 7.0) in sql job schdeduler which contains a batch file (.bat) with parameters.
Right now I have to run this job manually using command prompt.
If anyone can suggest how to automate this job.
Thanks alot,
Jannat

View 3 Replies View Related

SQLCMD Batch File With Script In Batch File

Dec 5, 2006

I am using the following batch file to execute a script that creates a db and all its objects in the local sql express:

sqlcmd -S (local)SQLExpress -i C:CreateDB.sql

This works fine, but I'm wondering if there's an easy way to put the script in the batch file, so users don't have to worry about putting the script in the C drive. I tried getting rid of the i parameter and pasting the script from the sql file into the batch file, but it didn't work.

Thanks,

Dave

View 1 Replies View Related

Passing Batch Variable

Feb 8, 2008

Hi:

I'm not an expert with using batch files, or using them with SSIS, but I am trying to use two batch files in my SSIS package. I want the first one to map the first available drive on the server and pass that drive letter as an output variable to the package, and then to pass this variable to the last batch file and unmap.

I already have execute program tasks set up with a hardcoded drive letter, but that is not going to work as I don't know what drive letters will be mapped on this machine.

I did find sample code for using variables in a batch file that maps drives:




Code Snippet
@ECHO OFF

rem Map \serverfolder to the next available drive letter and save
the driveletter as DRIVELETTER
FOR /F "tokens=2" %%A IN ('net use * \serverfolder) DO IF NOT %
%A.==command. SET DRIVELETTER=%%A

rem Unmap DRIVELETTER
net use %DRIVELETTER% /del

rem Clear your env variables
set DRIVELETTER=





Questions:
1. Is this the type of code I want to use with the SSIS package?
2. How do I use the batch file with SSIS? I see the Execute Process Tasks has a "StandardInputVariable" and "StandardOutputVariable". Will these work?

Thanks,
Kayda

View 1 Replies View Related

SQL 2012 :: How To Add Variables To SSIS Packages Per Batch

Nov 18, 2014

I am using Variables to configure a lot in an SSIS Packages.

Over the years you add new variables that are usefull to your default-package.

However to use those in "older" packages you have to open add them manually.

Any way (e.g. skript) to add a set of variables to every SSIS-package in a folder ?

View 3 Replies View Related

SQL / PHP / Passing Variables

Apr 28, 2008

I am pretty new to this and hope someone can help and I hope I can explain my problem.

I have a php form

<form action="cpesearch.php">
State: <select name="stname">
<option value ="ALL">all states</option>
<option value ="vic">VIC</option>
<option value ="sa">SA</option>etc etc etc

I am then connecting to my datatbase, assign the variables and doing the SQL query.

if ($dbc = mysql_connect ('localhost','root',''))
{
if (@mysql_select_db('database'))
{
$sname = $_GET['stname'];
$pname = $_GET['prname'];
$sql = 'SELECT * FROM `cpe` WHERE eventdate >= CURDATE() AND state = ''.$sname.'' AND presenter = ''.$pname.'' ORDER BY eventdate ASC';
$r = mysql_query($sql) or die("Query failed".mysql_error($dbc));
while ($row = mysql_fetch_array($r))
{
print "<div id="mainContent"> <p>".$row['state']."</p>".
"<p>".$row['eventdate']."</p>".
"<p>".$row['eventtitle']."</p>".
etc etc etc

So my problem is... I have an events table with presenters and state (and other info). I want to be able to select each presenter and state or all presenters and/or states. Not sure then how to pass the variable into my sql statement if they want to select ALL.

View 2 Replies View Related

Passing Variables Between DTSs

Apr 23, 2007

Hi,
I have a DTS (DTS1) that call another DTS(DTS2). DTS2 has a global variable defined, I need to pass a value to that variable from DTS1.
 
Thanks,
 Arturo

View 2 Replies View Related

Passing Variables From Access To SQL

Apr 24, 2001

Hi,
I have created a view. However this view needs a specific variable that a user has inputted in Access. The variable I need to pass into the first view is a DocketID..Similar to a StoreID. I then must run the second view, which also depends on the same DocketID. Finally I must run a final query that takes the result from the second view, which takes the results of the first view.
Basically, I want to pass the variable from my VBA application to the SQL statement. I have been reading, and it seems that like in VB I must declare a local variable in a DECLARE statement. Is that right?
How would I pass the variable that can be held in the VBA application ex. DocketId = 220 and pass that 220 to my SQL statement which is running is SQL 7.0. Most of the statement will be running in SQL, not the VBA application.

View 4 Replies View Related

Global Variables (passing)

Feb 23, 2001

I am executing:
--------------------------------
DECLARE @x AS int

SET @lcQuery = 'UPDATE ... WHERE .... ' + ' SET @x = @@ROWCOUNT'
EXEC (@lcQuery)

PRINT @x
--------------------------------
@x does NOT return a value, because it is "local" to the lcQuery execution. As a matter of fact, to execute it, I have to write:

SET @lcQuery = 'DECLARE @x AS int ' +
'UPDATE ... WHERE .... ' +
'SET @x = @@ROWCOUNT'
EXEC (@lcQuery)

How can I pass the variable @x to the progam from within EXEC (@lcQuery)?
How does EXEC (@lcQuery) execute? In a different space?

Thank you.

View 1 Replies View Related

Passing Variables Within Triggers

Aug 4, 1998

Does any one know how to pass variables to a stroed procedure within a trigger and does anyone know how to capture a value
from a stored procedure and store that value in a variable all within a trigger.

View 2 Replies View Related

Passing Variables Into A SProc

Jan 17, 2007

If anybody could help me with this i would love to hear from you, at the moment this is driving me crazy?

now i have a stored procedure in SQL server 2000, which i need to pass some variables into (pretty straight forward so far!!) I am copying tables from one database to another and i what to be able to choose the spefic database i'm copying to, this is the basic's of my query!

CREATE PROC sp_Copy_Facility
@FacilityID int,

/* Select and append Data tables */

-SELECT testDB.dbo.dt_test.* INTO
-******.dbo.dt_sample
-FROM testDB.dbo.dt_test
-WHERE testDB.dbo.dt_test.Facility_ID = @FacilityID
-option (keep plan)

i have also tried this:

CREATE PROC sp_Copy_Facility
@FacilityID int,
@DBIN nvarchar(40),
@DBOUT nvarchar(40)
AS

DECLARE @SQLString varchar(1000)
DECLARE @S2 nvarchar(1000)

/* Select and append Data tables */

SET @SQLString = 'SELECT ' + @DBIN +'.dbo.dt_test.* INTO '
SET @SQLString = @SQLString + @DBOUT + '.dbo.dt_test
SET @SQLString = @SQLString + 'FROM ' + @DBIN + ' .dbo.dt_test WHERE ' + @DBIN + '.dbo.dt_test.Facility_ID = @FacilityID option (keep plan)'

please help i need to get this done and i posted this on a number of different web sites with as yet no joy!!

View 8 Replies View Related

OpenQuery And Passing Variables

Jul 23, 2005

Anyone,Is this possible?I am connecting to a TeraData server via MS SQL 8.0 using the OpenQuerystatement. I need to pass a list of ever-changing deal numbers Mylist of numbers are stored as a table on MS SQL.So what I want is thisSelect * from OpenQuery(TeraSrvr, "Select Col1, Col2, Col3[color=blue]>From Teradata_Table_1[/color]Where Deal_no in (Select Deal_no from SQLTable)")Now I know that wont work, but How can I pass 184 Deal Numbers from mySQL server to this query before it is sent to the Teradata server to bedone? Do I have to keep re-doing an in statement each month?Anyone can help?Doug

View 3 Replies View Related

Passing Variables In An SQL Command

Apr 29, 2008

I have an SSIS package. In my control flow I have an Execute SQL Task and a data flow task. In my Execute SQL Task in the SQL Statement I have (select dbo.to_date(getdate()) as process_date) its a direct input with a result set. It gets Getdate as (processed_date) is declared. I have set no parameters, but I have set a Result Set. Also, I have set a global variable in the scope where I passes the date (processed_date)

In my Data Flow, I have an OLE DB data source with the following SQL statement in my SQL Command.
I am trying to pass down the variable as the ? .

It works when I pass it only to: lr.processed_date = ?

But I get an error when I pass it down to the : and ? between cav.begin_date and cav.end_date




SELECT distinct
acc.account_num,

FROM
cust_version_slow cvs

inner join cust_acco_version cav
on cus.cust_id = cav.cust_id

and ? between cav.begin_date and cav.end_date

inner join bia.dbo.acct acc
on cav.acco_id = acc.acco_id

inner join bia.dbo.account_version_slow avs
on acc.account_id = avs.account_id
and ? between avs.begin_date and avs.end_date

inner join bia_org ('02','2,3,4,5,6,7,9,10') boh
on cvs.branch_code = boh.branch_code

inner join accou_version_profit accM4
on acc.acco_id = accM4.acco_id
and ? between accM4.begin_date and accM4.end_date

where

lr.processed_date = ?



I need to pass down the same variable (processed_date) which is ? in all the ? in red


Thanks,

Delovan

View 11 Replies View Related

Passing Variables From URL And DDL Combined

Sep 10, 2007

In our project users log in and are assigned a GUID. The GUID is stored as a session variable that is used for filtering what a user sees on a page/report etc.

We have a report in which there are 2 parameters (Drop Downs).

Drop Down 1 lists the Entities a user can see (this is filtered by the GUID that is passed to the backend) and this works fine.
Drop Down 2 lists the products a user can see within Entity (this is filtered by the same GUID and also the selected value from DDL1.)

Here€™s the dilemma, how to we pass 2 variables into DDL2, when one of the variables comes from DDL1, and the other is passed by the URL?

View 4 Replies View Related

Passing Values From Database To Variables.

Nov 12, 2007

Hello.
I need to store 2 values that i retrive from a databse into variables so i can check them towards what is typed into the textboxes for my login script iv built. How can i do this?1 string myConnectionString = @"Data Source=.SQLEXPRESS;AttachDbFilename=C:UsersalhoDocumentsIntrapointWebApp_DataIntrapoint.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True";
2
3 SqlConnection myConnection = new SqlConnection(myConnectionString);
4 SqlCommand cmd = new SqlCommand("SELECT [username], [password] FROM [company] WHERE (([username] = @username) AND ([password] = @password))");
5 cmd.Parameters.AddWithValue("username", TextBoxUsername.Text);
6 cmd.Parameters.AddWithValue("password", TextBoxPassword.Text);
7
8 myConnection.Open();
9
10 //how do i pass the values retrived into variables, like string usrname and string password?
11
12 myConnection.Close();
 

View 13 Replies View Related

Passing Variables To SQL From C Based DB-Library

Aug 18, 1998

Hi!!
If I have declared a variable, such as char variable_name[x], and have assigned it a value, how do I go about passing this value to an SQL query such as:
SELECT * FROM variable_name
where my value replaces variable_name.
Cheers, Marc

View 1 Replies View Related

Passing Variables In Dts From Parent Dts To Child Dts

Dec 24, 2004

Any one have any ideas or links to point me to ???

View 2 Replies View Related

Passing Variables From Access Forms To Sp's

Mar 12, 2006

Hi everyone,

passing variables (values) from a form (MS Access Project) to a stored procedures in order to select records shouldn't be all that difficult. However after searching for hours and hours in various forums and discussions i'm still nowhere. I really hope someone can give me the missing hint.

OK All I whant to do is select data from the sql server via an access interface. the value entered into the access form by the user (for example the client name) should then be transferred to the stored procedure which will then return all the records of the chosen client.

The simple task of transferring this form value to the stored procedure is driving me crazy. Does anyone have an idea.

Thanx in advance

Guido

View 5 Replies View Related

Execute SQL Task - Passing Variables

Aug 28, 2007

how can you pass variables from one 'Execute SQL Task' to another?

View 9 Replies View Related

Passing Sessino Variables From ASP To Report

Jul 28, 2007

I am familure with having a report run based on users inputting variables via DDL or multiselect using SRS. But now want to access my reports a little differently. I have an ASPX page and I want a user to click a button and to have the report use the Session Variable that is stored in ASP. As a result the report will be prefiltered on data the specific user can see.

Any ideas on ow to do this?

View 4 Replies View Related

Passing Variables To A Package From A Website

Feb 28, 2008

I'm developing an SSIS package that will theoretically be run in two different ways. One is as a nightly job, called by a SQL job. The second is from a web application. When run as a job, it will be looking for a flat file in a known location. Run from the web, the file name and location are determined at runtime. I've tried to handle this by using a variable to hold the flatfile connection string, and setting the variable value to the known location. Then, when called to the web, I determine the file location/name at runtime, and pass it to the package. I'd assumed that if I pass the value into the package, it would override the variable's value that I'd set, but this doesn't seem to be happening. I realize this could very well be due to a coding error, but I thought I'd check to see if this is even a viable approach to be taking. If a variable's value is set in the package itself, will it be overwritten by a value passed to the package (assuming no stupid mistakes)? Or is this entirely the wrong approach to be taking for what I'm trying to do?

View 6 Replies View Related

Passing Variables To A Package Before Execution

Mar 13, 2006

I would like to pass variables to the package before it is executed (e.g. I am calling the bcp utility and I need to pass a password to the command line), so that at runtime a variable is set and then used.

Does anyone has some hints for good approaches? Every idea is welcome.

FYI: I do not use the bulk insert task as I need an errorlog file and the command is buggy with the errorfile option, therefore I chose the bcp approach. My connections are dynamic as far as servername and userid is concerned, but I did not find a solution for the password issue.

View 1 Replies View Related

Dtexec And Passing Variables Between Packages

Oct 12, 2007

When executing a package from Business Intelligence Studio variables are well passed between packages but when same is done using dtexec utility variables don't get passed between packages. Does anyone have any idea why this could be happening and what would be possible solutions? Thanks in advance!

View 3 Replies View Related

Passing NULL-value Into Package Variables

Dec 20, 2006

We have a package with a package variable.
This variable is of data-type 'DateTime'.
However, when i try to pass the value 'NULL' the package fails... i use the following statement with 'dtexec.exe'

/SET Package.Variables[MyDate].Value;"NULL"

What's the correct syntax for passing null-values? But maybe (because i cannot find anything on this) i should ask if this is even possible...

View 3 Replies View Related

Passing Variables To Stored Procedure

Mar 21, 2008



I have a stored procedure. Into this stored procedure i need to pass values to a 'IN' statement from asp.net. So when i am passing it , it should b in like a string variable with the ItemIds separated by commas. the procedure i have is :


create procedure SelectDetails
@Id string
as
Select * from DtTable where itemid in(@Id)


Here the itemid field in DtTable is of type int. Now when i execute the produre it is showing error as the Itemid is int and i am passing a string value to it.
How can i solve this problem?

View 5 Replies View Related

Passing Object Properties To Variables

Sep 14, 2006

Hi.

I was wondering if it's possible to pass object properties to variables? For example, if I have a ConnectionString property for a SQL Server connection, would it be possible to pass this value to a User-scoped variable?

Any ideas would be appreciated. Thanks!

View 3 Replies View Related

Passing Variables In OLEDB Source

Jul 31, 2006

Hi,

I have created lastUpdatedDate variable on package level. I have run a sql task and store a date in that variable.

now i am trying to pass that variable as parameter to oledb source connection (using command). it seems that we cant pass parameter in any sub query or derived table in query. its only working in outer query as soon as we place ? in WHERE clause of inner query it start throwing an 'Syntax Error' error saying that connection provider might not support that.

any idea ?????

I dont want to use command variables as my query is going to be quite big.

Note : I have tried Sql Server Native and OLEDB provider for sql server and this behaviour is seems to be constant in both.

Thanks,

Furrukh baig

View 2 Replies View Related

Trouble Passing Variables To Databound Gridview

Jan 3, 2007

im trying to display all of the results from a SQL database where the username is the same as the current user.currently, i have a long way around way of implementing this.  i have a dropdownbox with a static entry (set at the page load) of the users name.  i then tried to databind the gridview to the database and use the dropbox as the variable.  however, for some reason, it doesnt acknowledge the username field and it doesnt return anything.  i think this is because the dropbox is a static entry, but why does that matter? here is my current code:  <asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True"                        AutoGenerateColumns="False" DataSourceID="SqlDataSource3" EmptyDataText="There are no data records to display.">                    </asp:GridView>                    <asp:SqlDataSource ID="SqlDataSource3" runat="server" ConnectionString="<%$ ConnectionStrings:pas_db_connectionstring %>"                        ProviderName="<%$ ConnectionStrings:pas_db_connectionstring.ProviderName %>"                        SelectCommand="SELECT * FROM [files] WHERE ([submitter] = @submitter)">                        <SelectParameters>                            <asp:ControlParameter ControlID="usernameDropBox" DefaultValue="none" Name="submitter"                                PropertyName="SelectedValue" Type="String" />                        </SelectParameters>                    </asp:SqlDataSource> please understand that i am trying to set the @submitter variable equal to the current user's username.   thx 

View 4 Replies View Related

Passing Variables And Column Names To EXEC

Oct 9, 2007

Hello, I'm quite new to T-SQL, but since I'm trying to create a statistics page on database contents (Counting savesets in Enterprise Vault saveset Databases) I prefer to do the coding in the databases.
I create temp tables for the distinct partitions in the saveset table. Then I pass 2 variables to the EXEC function, but it seems unable to pass the ['+@idpartition+']-variable as a value:
Declare @EVBase varchar(20)Declare @IdPartition INTSet @EVBase=(SELECT EVMbxName from Servers) Set @IdPartition=(SELECT TOP 1 Dist_Partitions FROM TEMP_EV1)EXEC('SELECT COUNT (IdPartition)FROM ['+@evbase+']..SAVESET SS LEFT OUTER JOIN SavesetStore SSS ON SS.SavesetIdentity = SSS.SavesetIdentityWHERE [IdPartition] =  ['+@idpartition+'] AND StoreIdentifier IS NULL')
Server: Msg 207, Level 16, State 3, Line 2Invalid column name '0'.
If I change the last line to: WHERE [IdPartition] =  2 AND StoreIdentifier IS NULL')The script runs fine - but I need the value from the table. Any help will be appreciated.
Best regards, Tim 
 
 

View 4 Replies View Related

Passing Variables To EXEC And Stored Prodecure?

Nov 7, 2007

In this line, @BaseName varchar(50) is polulated by a cursor that queries a table for names of other databases. In this first example it works as predicted: 
EXEC('SELECT COUNT (IdPartition) FROM '+@BaseName+'..SAVESET SS LEFT OUTER JOIN SavesetStore SSS ON SS.SavesetIdentity = SSS.SavesetIdentity WHERE [IdPartition] = 0 AND StoreIdentifier IS NULL')
If I create this as an SP (I want the output into another table)
CREATE PROCEDURE GetPArtitionItems @BaseName varchar(50),@IdPartition int, @PartitionItems int OUTPUT
AS
SELECT COUNT (IdPartition) FROM ['+@BaseName+']..SAVESETSS LEFT OUTER JOIN SavesetStore SSS ON SS.SavesetIdentity = SSS.SavesetIdentityWHERE [IdPartition] = @IdPartition AND StoreIdentifier IS NULL
GODeclare @PartitionItems intEXECUTE GetPartitionItems 'evmailboxstore1',0,@PartitionItems OUTPUT      --EvMailboxStore1 is another table in the same database.
I get: Server: Msg 208, Level 16, State 1, Procedure GetPArtitionItems, Line 7Invalid object name ''+@BaseName+'..SAVESET' 
 In this case the value is not passed into the @baseName-variable. What do I do wrong?
Thanks in advance - Tim Kuhnell
 

View 3 Replies View Related

Mapping Of Variables While Passing A Resultset To Foreach

Oct 3, 2006

I have an Execute SQL Task1 that executes an extraction stored proc (say spe). spe returns a rowset that has 25 columns. For each row in the rowset, a load stored proc (say spl) has to be executed (spl is executed using Execute SQL Task2). spl has 25 input parameters that match the 25 columns returned by spe (the column names returned by spe and input parameter names of spl are exactly same). To achieve this, in Execute SQL Task1, I had to specify a variable in the Result Set (say User::resultset). After declaring 25 variables, in the foreach loop editor, I had to specify the Variable Mappings of these 25 variables to the column indices of the rowset returned by spe. After this, in Execute SQL Task 2 I had to specify in the Parameter Mapping the mapping between the 25 variable names and 25 parameter names of spl. You can understand that it is cumbersome to define all these mappings manually, especially when there are a lot of variables involved.
Is there some way of telling SSIS that it has to automatically map the columns returned by spe and the input parameters of spl (given that the column names and parameter names match exactly)? Or is there a totally different and simple way of achieving the above scenario?
One more problem that I am facing :-
In this package we are having a dataflow control which returns a recordset destination which has a column named ID(in the Input/output tab it is showing its datatype as DT_I8).This recordset is passed to a foreach container control through User::ID variable which is defined as Int64.
While running the package we are getting an error like this:
Error: ForEach Variable Mapping number 4 to variable "User::ID" cannot be applied.
Error: The type of the value being assigned to variable "User::ID" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
Actually both are related to mapping. I tried the second issue with just 1 variable as well to make sure there is no mismatch. Still, i face the same.
Can anyone please provide a solution to this ?

View 6 Replies View Related

Passing Variables From SQL Server To Reporting Services

Apr 6, 2007

I have a report that I'm trying to automate. Basically I want to create a cursor of containing authors, I want to run my report for that particular author, save the report, and move to the next author in the cursor. I want to continue this process until there's no more authors left in the table. Is this possible with reporting services 2005? If so how do I communicate the value from my cursor, to my report?

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved