Creating SQL Command From Variable

Apr 29, 2008

This is a really simple question. When I set my variable (the one will hold my SQL Command) property "EvaluateAsExpression=True", I don't get the option to edit the expression, I was expecting to get a little button that would open the "Property Expressions Editor" if pressed.

Am I doing anything wrong? Should I edit my SQL Command somewhere else and then Copy+Paste on my variable expression?

I need to pass a ServerName as a field to my query and I'm doing that by looping through a list of servers...

View 9 Replies


ADVERTISEMENT

Using A Variable In SSIS - Error - Command Text Was Not Set For The Command Object..

Nov 4, 2006

Hi All,

i am using a OLE DB Source in my dataflow component and want to select rows from the source based on the Name I enter during execution time. I have created two variables,

enterName - String packageLevel (will store the name I enter)

myVar - String packageLevel. (to store the query)

I am assigning this query to the myVar variable, "Select * from db.Users where (UsrName = " + @[User::enterName] + " )"

Now in the OLE Db source, I have selected as Sql Command from Variable, and I am getting the variable, enterName,. I select that and when I click on OK am getting this error.

Error at Data Flow Task [OLE DB Source [1]]: An OLE DB error has occurred. Error code: 0x80040E0C.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E0C Description: "Command text was not set for the command object.".

Can Someone guide me whr am going wrong?

myVar variable, i have set the ExecuteAsExpression Property to true too.

Please let me know where am going wrong?

Thanks in advance.








View 12 Replies View Related

Creating A SQL MAX Command

Oct 8, 2007

Hello All,
 I'm currently in the process of developing a new PO system for work. What I need to accomplish is a SQL MAX command to find the largest PO number and then add 1 to that number. I'm then saving this in a session state so users can create multiple items for that same PO number. Here's what I have so far:
 
 1 protected void Page_Load(object sender, EventArgs e)
2 {
3 // connection string to your database
4 SqlConnection mySqlConnection = new SqlConnection("server=(local)\SQLEXPRESS;database=Purchasing;Integrated Security=SSPI;");
5
6 // create command object to execute query
7 SqlCommand mySqlCommand = mySqlConnection.CreateCommand();
8
9 // set command text to the INSERT sql query
10 mySqlCommand.CommandText = "SELECT MAX(PONumber)FROM ItemMaster;";
11
12 // open connection
13 mySqlConnection.Open();
14
15 // execute query
16
17 int newPO1 = (int) mySqlCommand.ExecuteScalar();
18 int newPO = newPO1 + 1;
19
20 // close connection
21 mySqlConnection.Close();
22
23 //Response.Write(newPO);
24 Session["newPO"] = newPO.ToString();
25
26 }

 
 I copied and modified the ExecuteScalar() command from another thread in another fourm, but continue to receive this error:
 
System.InvalidCastException: Specified cast is not valid.Source Error:



Line 30: // execute query
Line 31:
Line 32: int newPO1 = (int) mySqlCommand.ExecuteScalar();
Line 33: int newPO = newPO1 + 1;
Line 34:
 
I'm not sure what i'm doing wrong, any help to point me in the right direction would be greatly appreciated.
 
Thank you in advance

View 5 Replies View Related

SQL Command From Variable

Mar 2, 2008

I'm having difficulty passing a date parameter in a SSIS OLEDB connection to a stored procedure using SQL command with the error message "Attempted to read or write protected memory ..." and if I create a variable, I get the error "must declare the scalar variable..". Here is the OLEDB command: "EXEC dbo.mic_TGDrop ?" where ? requires a date parameter @ReportDate. Why does the call to the stored procedure fail? What would be the correct syntax of a variable to use SQL Command from variable? The SQL BOL does not cover this; is there another resource that has a simple example? Thanks.

View 2 Replies View Related

Command USE With Variable

Mar 29, 2006



Why I canīt use the command USE with variable?

declare @banco varchar(20)
select @banco='xpto'
USE @banco

I have try in other way without success

DECLARE @banco varchar(50)
declare @SQL varchar(50)
select @banco='xpto'
print @banco
select @SQL='USE '+@banco
print @SQL
exec(@SQL)

Help me, please.



Rodrigo









View 7 Replies View Related

SQL COMMAND FROM VARIABLE

Oct 21, 2007


KEEPS SAYING MISSING EXPRESSION:

IN THE VARIABLE EXPRESSION I'VE WRITTEN THE FOLLOWING COMMAND:

"SELECT * FROM MY_TABLE WHERE T1.DATE >= @[USER:ATE]"


I'VE CLICKED EVALUATE EXPRESSION AND IT ACCEPTS THE EXPRESSION

HOWEVER WHEN I GO TO DATA FLOW TO SELECT SQL COMMAND FROM VARIABLE IT BRINGS BACK MISSING EXPRESSION????

View 13 Replies View Related

Creating DB Using Command Line

Jun 11, 2007

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.

View 5 Replies View Related

Creating Database By Using Command Window

Nov 14, 2007

Hi,

I am in the process of creating a windows installer. I am using SQL Server 2005 for my application. I have generated the script for my database. I want to run that script and create my database on the client machine (client has installed SQL 2005). Do you have any solution for this kind of issue?

When run an installer I can run a bat file. Can I create a bat file and create my database? Does SQL Server2005 provide any tool for this?

Thanks!
CJ

View 3 Replies View Related

Creating A Directory Command Question

Jul 23, 2005

Hi All,What is the SQL command for creatinga direcotry c:mydatadata1 on my server.Thanks in advanceJohn S*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View 1 Replies View Related

Using A Variable In OLE DB Destination SQL Command

Nov 14, 2007

Hi all,
 I have created a global variable and it will read the user input from a web application. How can i pass this variable into OLE DB destination SQL command so that i can retrieve the user specified table? Or is there a better way to do this?

View 4 Replies View Related

How Can A Variable Used In SQL Select Command

Nov 8, 2004

select * from TABLE where user='jacky' ,it can working,but if like this:
dim name as string="jacky"
select * from TABLE where user=name
it won't doing,Can a variable used in SQL select command,if can,how to make it working.

View 2 Replies View Related

Using Variable String In Sql Command

Dec 5, 1999

Using SQL Server 7.0, I am trying to use a variable string called from a form to perform a simple query.

Here's what it looks like:

[after submitting a form on the previous page with a drop down list box titled "fiscal"]

dim strYear

strYear = Request.Form("fiscal")

sql = "SELECT * FROM VENDOR WHERE STATUS = 'P' AND '"& strYear &"';"

[where strYear is the following: PYMNT_DT > ''12/31/98'']

Records exist that meet this criteria. If the PYMNT_DT > '12/31/98' is hardcoded into the sql statement, 12 records are returned. However, when the variable string is used instead, no records are found. Any ideas on how this problem might be fixed?

Thanks in advance.

Sincerely,

Chad Massie

View 1 Replies View Related

Can't Set Variable Through Command Line

Mar 29, 2007

Hello everyone!

Bit of a problem executing a DTS command from a command line.

I have the following variables defined in my package:

UserVarchar1
UserVarchar2
UserVarchar3

All have a scope of Package, all of the mstrings

The command I'm attempting to run in 1 line is:

DTEXEC /FILE "C:SSIS PackagesED-CustomersPackage.dtsx"
/SET Package.Variables[User::UserVarchar1].value;"10"
/SET Package.Variables[User::UserVarchar2].value;"30719"
/SET Package.Variables[User::UserVarchar3].value;"BILLTO"




Description: The package path referenced an object that cannot be found: "Package.Variables[User::UserVarchar1].value". This occurs when an attempt is made to resolve a package path to an object that cannot be found.


DTExec: Could not set Package.Variables[User::UserVarchar1].value value to 10.

Any idea why?

Thanks for the help!

View 10 Replies View Related

SQL Command From Variable - Datetime

Nov 16, 2007

I was not able to find the solution myself so here I am to ask you.

I'm using an Ole DB Source with the SQL command from variable.

My variable expression needs to look like this:

select * from dbo.fx_function (SomeDateVariable)

My sql server fx_function recieves as a parameter datetime, my variable SomeDateVariable is datetime type.

I tried all different types of cast on @User :: SomeDateVariable but I either can't evaluate the expression or I have errors trying to execute the package.

Any ideas how the expression should look like?

I'll keep trying...

View 8 Replies View Related

OLE DB Source, SQL Command From Variable

Apr 15, 2008

I read several posts on this topic and I would like to confirm my understanding.

This question has to do with parameterizing the select statement in the OLE DB Source editor. Initially, I thought I could use Data access mode: SQL command, and somehow use a user variable in there to build my Select statement.

But in researching further, it looks like I need to store the entire SQL command in a one long string variable (let's call it A), where A is built off of another variable that has my parameter. I then use A in Data access mode: SQL command from variable.

Am I correct? And is there not a way to accomplish the samething with only one variable that is your parameter value?

It seems a bit clumsy to store the entire SQL expression in a string variable.

View 10 Replies View Related

SQL Command From Variable In OLE DB Source

Apr 10, 2008

Using Jamie's, John's and Rafae post i have got this far.
My scenario demands me to do an incremental load. So i have followed your article and have created a one variable 'vDate' (Dataflow Task, Datetime, 3/3/2008) and another variable SourceSQL (Dataflow Task, String). I have set this SourceSQL evaluation to True and have added this;
"select * from stagingperfdata where startdatetime = "+ (DT_WSTR,10) @[vDate].

I want to call this within a OLE DB source, so i select the data access method to SQL command from variable. My variable name is visible and i am able to select it and then save and debug. I also have a derived column transformation to increment the variable vDate by one day.
The flow is successful but there is no records written to the target.
I tried the same query (available in the variable's value column) in SSMS and it is retrieving 12 records.
I went back to the Ole Db source and wanted to preview the data, but i get "Query timeout expired (Microsoft SQL Native Client)" error.
Am i missing something fundamental?

View 7 Replies View Related

Error While Creating Table Using Select Command

Jul 20, 2005

Hi All,I am new to MS SQL Server.I am using MS SQL 2000.I have a problem increating a table by using Select command.I have table called "test"and i want to create another table with the same structure and rows.Itried with the following commandcreate Table test1 as select * from test;But it give an syntax error.I have tried the same command in Oraclebut i was working.Does MS SQL 2000 Server supports this kind of Query.Please help me to solve the problem or any other methods to performthis operation.Thanks in AdvanceKevin

View 2 Replies View Related

Sql Update Command With Variable As ColName

May 7, 2008

Hello All
 I m trying to update a table whose col name will be read from another table.
For e.g. Table1 gives the result:
'emp1',   1,   'John'
'emp2',   2,   'Mike'
Now in the second table, i need to update the table with Col name  = 'Emp1' and then from the second row (above), I need to update Col name= 'Emp2'
I need to write one Update Statement which will handle all the cases. I tried
Update Table2 set @VariableName = .......
but didnt work...
How can i do that ?

View 7 Replies View Related

Sql Command From Variable, List Empty

Apr 7, 2008

Hello.

I created a package scope variable to use as a sql command for a data
flow task. But when I click the drop down to select it, nothing
appears. Anybody else have this issue and find a solution?


Thanks,
Rich

View 2 Replies View Related

Use Variable As IN List For OleDB Command

Sep 27, 2007

Group,

First let me say that I'm new to SSIS, so be gentle with me.

I need to extract a limited set of data from a very large view in Oracle (I know, yuk!). The view contains millions of rows, but I only need the child matches of 343 unique keys in a one to many relationship. In pure SQL the query would look something like this.

Select proj, tn, rn, total FROM Oracle.view WHERE proj in (select distinct proj from MSSQL.dbo.projlist)

As you can see, this is an impossible query on many levels.

My first thought was to get the runtime list into a variable and use that variable as a parameter in the Oracle OleDb Source. Alas, the Oracle source will not allow me to add a parameter.

My second thought was to use a script component and build a SQL_Command string into a variable with all of my keys included. Then use the read_write variable as the SQL Command from variable in the Oracle Source. My attempts to construct such a variable expression have failed.

Any ideas would be appreciated.


RH

View 11 Replies View Related

How To Add Variable To Sql Command Text Field

Sep 7, 2007

In the data flow task I have multiple OLE DB source task to retreive data from a cube using MDX. Below is an example of the query from sql command text field: I have a few questions on how to modify this statment to make it more robust.

1) The where clause [Calendar].[Quarter 2 (2007)] should actually be some kind of variable that will change each time
the user runs this project. So I am wondering how to make this a variable and get it into this field.

2) I had thought that I could use the SSIS variable. I have created one, but I can not figure how to get it into the field that
contains the given select statment.

3) I believe that once I get this variable part to work then I want a way to have user set this data value. Either by selecting data from a table in database or through a user interface where user enters data. I did do some resarch on creating a user interface, but I did not understand what I had to do, so if any one knows where to find a tutorial on how to do this let me know, or what they believe the best/easiest way is to get data from user to fill this where clause.
select * from OPENROWSET('MSOLAP', 'DATASOURCE=local; Initial Catalog=Patient Demographics 2005;',
'with member [Measures].[TimeDisplayName] as [Calendar].CurrentMember.Name
SELECT NON EMPTY { [Measures].[TimeDisplayName],[Measures].[Adjusted Relative Weight],[Measures].[Adjusted Case Weight]} ON COLUMNS,
({[Facility].[REGION].[NATCODE], [Facility].[REGION].[REGCODE]} *
[RIC].[CMGGRPCD].ALLMEMBERS) ON ROWS FROM [ESR]
WHERE [Calendar].[Quarter 2 (2007)]
')

View 10 Replies View Related

Passing '% Variable %' To SqlDataSource Through E.Command.Parameters

Feb 23, 2008

 Hello all,I'm writing a site with one page that uses the session variable (User ID) to pick one user ID out of a comma separated list in the field Faculty. The default parameterized query designed in the SqlDataSource wizard only returns lines that contain an exact match:SELECT * FROM tStudents WHERE ([faculty] = @faculty) The query: SELECT * FROM tStudents WHERE ([faculty] LIKE '%userID%') works as I need when I hard code the query with a specific user ID into the SqlDataSource in the aspx page.  It will not work if I leave the @faculty parameter in it:SELECT * FROM tStudents WHERE ([faculty] LIKE '%@faculty%') e.Command.Parameters works to replace the @Faculty with a user ID, but again, adding the single quote and percentage sign either causes errors or returns no results.  I've tried several variations of:         string strEraiderID = "'%" + Session["eRaiderID"].ToString() + "%'";        e.Command.Parameters["@faculty"].Value = strEraiderID;no results are returned, not even the lines returned with the default select query.How do generate the equivalent of SELECT * FROM tStudents WHERE ([faculty] LIKE '%userID%') into the SqlDataSource? Thanks much! 

View 3 Replies View Related

Storing Variable In Command File From SQL Server

Sep 13, 2004

Hi,
i am using command files(.cmd) and osql utility to conneft to a database.
following is the requirement.
I have a query "SELECT top 1 au_fname FROM pubs..authors".

Can i store the value of au_fname in the command file?

Is there any work around for this.

Any help is appreciated.

Thanks.... :)

View 1 Replies View Related

Transact SQL :: Dynamically Creating And Running A Database Restore Command

Jun 17, 2015

If I run the following command in a Query window it works:

RESTORE DATABASE CIS_Source_Data_Test FROM DISK = 'y:CIS_Source_Data_backup_2015_06_17_085557_7782407.bak' WITH RECOVERY, REPLACE

If I dynamically put together the command and store it in variable @cmd and then execute it using
exec sp_executesql @cmd or exec (@cmd) it does not work. I get the following:

Msg 2745, Level 16, State 2, Procedure CIS_Source_Data_Refresh, Line 92
Process ID 62 has raised user error 50000, severity 20. SQL Server is terminating this process.
Msg 50000, Level 20, State 1, Procedure CIS_Source_Data_Refresh, Line 92
RESTORE DATABASE is terminating abnormally.
Msg 0, Level 20, State 0, Line 0

A severe error occurred on the current command.  The results, if any, should be discarded.

Why it won't work when I try to create and run it dynamically?

View 8 Replies View Related

SQL Select Command With Where Clause Variable Based On ASP.NET TextBox

Apr 19, 2007

All,
 
Please can someone advise how to use SQL select statement with where clasue which is based on a textBox.text value.
 ex. below example I set the textbox.text value to a C# variable called TextBoxValue1 but I receive error this is not a valid
This is all done in Page_Load event backend code.
string strCommandtext = "Select Type.TypeName, Type.TypeDesc FROM Type Where Type.TypeName = TextBoxValue1";

View 2 Replies View Related

Overriding A Datetime Pkg Variable From The Command Line - What's Been Fixed?

Apr 28, 2008

I'm looking at http://blogs.conchango.com/jamiethomson/archive/2007/03/13/SSIS_3A00_-Property-Paths-syntax.aspx and http://blogs.conchango.com/jamiethomson/archive/2005/10/11/SSIS_3A00_-How-to-pass-DateTime-parameters-to-a-package-via-dtexec.aspx trying to understand where we are at using datetime overrides in ssis from the command line. One of these articles suggests something has been fixed in ssis sp1, what part of this issue was fixed?

Can you override (from the command line) a user var in ssis whose ValueType property is datetime? Or do some of the older articles still apply, ie the user var must be string so that ssis wont create a separate user var with the same name etc etc? And if it is string, does some kind of conversion need to be done to compare it against datetime cols in t-sql inside the pkg?

I already know that the syntax is

dtexec /F Package.dtsx /SET Package.Variables[User::Var].Properties[Value];2007-01-02.

If this has been fixed, was it fixed in service pack 1, 2?

View 1 Replies View Related

To Access Global Variable Inside Oledb Command

Mar 13, 2008

Hi

How to use a global variable of a package inside oledb command

Scenario:
Glb_Rowcount Variable


I need to use this variable value inside oledb command.


P.S: No use of stored procedures and no script component

View 4 Replies View Related

SSIS OLE DB Source SQL Command From Variable DataType Problem

Sep 12, 2006

Hi,

I have to read data from an MDB file into SQL Server. Simple, but I need to place a literal into every row. In my OLE DB Source I would use:

SELECT '001' AS col1, foo, bar FROM Table1

But the problem is that I pass my value for col1 into the package from C# using Managed DTS.

So I set up my OLE DB Source using "SQL command from variable." And I have two variables one to receive the value of for col1 (col1) and one that is an evaluated expression that has a dynamic sql statement (dynSql).

col1 is a String as is dynSql. dynSql looks like this:

"SELECT " + @[col1] + " as col1, foo, bar FROM Table1"

Now as long as I set the value of col1 to '001' that should work. Right? Well not exactly. The destination for col1 in SQL Server is a char( 3 ). But the package makes the col1 column from the source a DT_WSTR. Which causes the famed: "Text was truncated or one or more characters had no match in the target code page.".

Is there a better solution?

Thanks. Kenneth.

View 1 Replies View Related

Creating Table By Variable Name

Feb 16, 2004

Hi
How can i Create a table with variable name
eg
suppose variable x=tab1
I want the table created to be tab1 if x=tab2 then table created shd be tab2.. so how can this be dynamically done(creation of tables as well as stored procs)

i tried : create table @x .. it doesnt seem to work
please help
regards

View 2 Replies View Related

How Do I Use A @variable To Hold On Value Return From An Exec ('string Command') Statement.

Sep 19, 2000

How do I use a @variable to hold on value return from an exec ('string command') statement.

Example for:

declare @OldID int
declare @cmd varchar(255)
declare @db varchar(25)
declare @OldOwner varchar(25)

set @db = 'DBNAME'
set @OldOwner = 'USERNAME'

select @cmd = 'select uid from ' + ltrim(rtrim(@db))'..sysusers where name = ' + "'" + ltrim(rtrim(@OldOwner)) + "'"
exec (@cmd)

How can I use @OldID to hold on UID return from this statement.

When I try use:
select @cmd = 'select @OldID = uid from ' + ltrim(rtrim(@db))'..sysusers where name = ' + "'" + ltrim(rtrim(@OldOwner)) + "'"
then I'm getting a error message: @OldID not declare.

Thanks.

View 2 Replies View Related

T-SQL (SS2K8) :: Use Variable In ALTER / CREATE PARTITION SCHEME Command For Filegroup?

May 9, 2010

Is it possible to use a variable to specify the filegroup in the ALTER/CREATE PARTITION SCHEME command?

I want the partition scheme to use the default filegroup for ALTER and CREATE PARTITION SCHEME. At the time the script is created, I don't know the default filegroup in the database.

My code:

declare @fileGroupName VARCHAR(50) = (select top 1 name from
sys.filegroups where is_default = 1)
ALTER PARTITION SCHEME MyScheme NEXT USED @fileGroupName

Is failing:

Incorrect syntax near '@fileGroupName'.

Q: Is it possible to use a variable for the filegroup in the ALTER/CREATE commands? Is so, what is the correct syntax?

Q: If using a variable is not possible, is there another way to specify the default filegroup?

View 2 Replies View Related

A Problem With Printing Very Long Strings Using The PRINT Command On A VARCHAR(MAX) Variable

Apr 2, 2008

Hi,

I'm seeing a problem with printing very long strings using the PRINT
command on a VARCHAR(MAX) variable. After a certain amount of
characters the string is truncated....it looks like the limit is at
around 8,000 characters.

Does anyone know of a solution or a workaround for this?

View 16 Replies View Related

Creating Views With Variable No. Of Input Tables

Aug 6, 2007

Hello,
I need to create a view which will include an additional table every week of our financial year.
Example: Every week we create a table called Sales_YYYY_WW where YYYY is the Year and WW is the Week, so after the first week we have the table Sales_2007_01 and after the second week we have Sales_2007_01 and Sales_2007_02 etc.. These tables are created evey week and don't exist at the start of the financial year.
I'd like to create a view at the beginning of each year to encompass each table evey week as it is created. If all of the tables were there at the start of the year it would be simple:
CREATE VIEW VIEW_SALES_2007
AS
SELECT * from Sales_2007_01
UNION ALL
SELECT * from Sales_2007_02
etc.
But I don't know how to achieve this when the tables don't already exist. I don't want to have to edit the view each week to add in the new table.

Can anyone out there help me?

View 3 Replies View Related







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