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


ADVERTISEMENT

Command Text Was Not Set For The Command Object Error

Sep 19, 2006

Hi. I am writing a program in C# to migrate data from a Foxpro database to an SQL Server 2005 Express database. The package is being created programmatically. I am creating a separate data flow for each Foxpro table. It seems to be doing it ok but I am getting the following error message at the package validation stage:

Description: An OLE DB Error has occured. 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".

.........

Description: "component "OLE DB Destination" (22)" failed validation and returned validation status "VS_ISBROKEN".

This is the first time I am writing such code and I there must be something I am not doing correct but can't seem to figure it out. Any help will be highly appreciated. My code is as below:

private bool BuildPackage()

{




// Create the package object

oPackage = new Package();

// Create connections for the Foxpro and SQL Server data

Connections oPkgConns = oPackage.Connections;

// Foxpro Connection

ConnectionManager oFoxConn = oPkgConns.Add("OLEDB");

oFoxConn.ConnectionString = sSourceConnString; // Created elsewhere

oFoxConn.Name = "SourceConnectionOLEDB";

oFoxConn.Description = "OLEDB Connection For Foxpro Database";

// SQL Server Connection

ConnectionManager oSQLConn = oPkgConns.Add("OLEDB");

oSQLConn.ConnectionString = sTargetConnString; // Created elsewhere

oSQLConn.Name = "DestinationConnectionOLEDB";

oSQLConn.Description = "OLEDB Connection For SQL Server Database";

// Add Prepare SQL Task

Executable exSQLTask = oPackage.Executables.Add("STOCK:SQLTask");

TaskHost thSQLTask = exSQLTask as TaskHost;

thSQLTask.Properties["Connection"].SetValue(thSQLTask, "oSQLConn");

thSQLTask.Properties["DelayValidation"].SetValue(thSQLTask, true);

thSQLTask.Properties["ResultSetType"].SetValue(thSQLTask, ResultSetType.ResultSetType_None);

thSQLTask.Properties["SqlStatementSource"].SetValue(thSQLTask, @"C:LPFMigrateLPF_Script.sql");

thSQLTask.Properties["SqlStatementSourceType"].SetValue(thSQLTask, SqlStatementSourceType.FileConnection);

thSQLTask.FailPackageOnFailure = true;



// Add Data Flow Tasks. Create a separate task for each table.

// Get a list of tables from the source folder

arFiles = Directory.GetFileSystemEntries(sLPFDataFolder, "*.DBF");

for (iCount = 0; iCount <= arFiles.GetUpperBound(0); iCount++)

{


// Get the name of the file from the array

sDataFile = Path.GetFileName(arFiles[iCount].ToString());

sDataFile = sDataFile.Substring(0, sDataFile.Length - 4);

oDataFlow = ((TaskHost)oPackage.Executables.Add("DTS.Pipeline.1")).InnerObject as MainPipe;

oDataFlow.AutoGenerateIDForNewObjects = true;



// Create the source component

IDTSComponentMetaData90 oSource = oDataFlow.ComponentMetaDataCollection.New();

oSource.Name = (sDataFile + "Src");

oSource.ComponentClassID = "DTSAdapter.OLEDBSource.1";

// Get the design time instance of the component and initialize the component

CManagedComponentWrapper srcDesignTime = oSource.Instantiate();

srcDesignTime.ProvideComponentProperties();

// Add the connection manager

if (oSource.RuntimeConnectionCollection.Count > 0)

{


oSource.RuntimeConnectionCollection[0].ConnectionManagerID = oFoxConn.ID;

oSource.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(oFoxConn);

}

// Set Custom Properties

srcDesignTime.SetComponentProperty("AccessMode", 0);

srcDesignTime.SetComponentProperty("AlwaysUseDefaultCodePage", true);

srcDesignTime.SetComponentProperty("OpenRowset", sDataFile);

// Re-initialize metadata

srcDesignTime.AcquireConnections(null);

srcDesignTime.ReinitializeMetaData();

srcDesignTime.ReleaseConnections();

// Create Destination component

IDTSComponentMetaData90 oDestination = oDataFlow.ComponentMetaDataCollection.New();

oDestination.Name = (sDataFile + "Dest");

oDestination.ComponentClassID = "DTSAdapter.OLEDBDestination.1";

// Get the design time instance of the component and initialize the component

CManagedComponentWrapper destDesignTime = oDestination.Instantiate();

destDesignTime.ProvideComponentProperties();

// Add the connection manager

if (oDestination.RuntimeConnectionCollection.Count > 0)

{


oDestination.RuntimeConnectionCollection[0].ConnectionManagerID = oSQLConn.ID;

oDestination.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(oSQLConn);

}

// Set custom properties

destDesignTime.SetComponentProperty("AccessMode", 2);

destDesignTime.SetComponentProperty("AlwaysUseDefaultCodePage", false);

destDesignTime.SetComponentProperty("OpenRowset", "[dbo].[" + sDataFile + "]");



// Create the path to link the source and destination components of the dataflow

IDTSPath90 dfPath = oDataFlow.PathCollection.New();

dfPath.AttachPathAndPropagateNotifications(oSource.OutputCollection[0], oDestination.InputCollection[0]);

// Iterate through the inputs of the component.

foreach (IDTSInput90 input in oDestination.InputCollection)

{


// Get the virtual input column collection

IDTSVirtualInput90 vInput = input.GetVirtualInput();

// Iterate through the column collection

foreach (IDTSVirtualInputColumn90 vColumn in vInput.VirtualInputColumnCollection)

{


// Call the SetUsageType method of the design time instance of the component.

destDesignTime.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READWRITE);

}

//Map external metadata to the inputcolumn

foreach (IDTSInputColumn90 inputColumn in input.InputColumnCollection)

{


IDTSExternalMetadataColumn90 externalColumn = input.ExternalMetadataColumnCollection.New();

externalColumn.Name = inputColumn.Name;

externalColumn.Precision = inputColumn.Precision;

externalColumn.Length = inputColumn.Length;

externalColumn.DataType = inputColumn.DataType;

externalColumn.Scale = inputColumn.Scale;

// Map the external column to the input column.

inputColumn.ExternalMetadataColumnID = externalColumn.ID;

}

}

}

// Add precedence constraints to the package executables

PrecedenceConstraint pcTasks = oPackage.PrecedenceConstraints.Add((Executable)thSQLTask, oPackage.Executables[0]);

pcTasks.Value = DTSExecResult.Success;

for (iCount = 1; iCount <= (oPackage.Executables.Count - 1); iCount++)

{


pcTasks = oPackage.PrecedenceConstraints.Add(oPackage.Executables[iCount - 1], oPackage.Executables[iCount]);

pcTasks.Value = DTSExecResult.Success;

}

// Validate the package

DTSExecResult eResult = oPackage.Validate(oPkgConns, null, null, null);

// Check if the package was successfully executed

if (eResult.Equals(DTSExecResult.Canceled) || eResult.Equals(DTSExecResult.Failure))

{


string sErrorMessage = "";

foreach (DtsError pkgError in oPackage.Errors)

{


sErrorMessage = sErrorMessage + "Description: " + pkgError.Description + "";

sErrorMessage = sErrorMessage + "HelpContext: " + pkgError.HelpContext + "";

sErrorMessage = sErrorMessage + "HelpFile: " + pkgError.HelpFile + "";

sErrorMessage = sErrorMessage + "IDOfInterfaceWithError: " + pkgError.IDOfInterfaceWithError + "";

sErrorMessage = sErrorMessage + "Source: " + pkgError.Source + "";

sErrorMessage = sErrorMessage + "Subcomponent: " + pkgError.SubComponent + "";

sErrorMessage = sErrorMessage + "Timestamp: " + pkgError.TimeStamp + "";

sErrorMessage = sErrorMessage + "ErrorCode: " + pkgError.ErrorCode;

}

MessageBox.Show("The DTS package was not built successfully because of the following error(s):" + sErrorMessage, "Package Builder", MessageBoxButtons.OK, MessageBoxIcon.Information);

return false;

}

// return a successful result

return true;
}

View 2 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

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

SSIS OLE DB Command Error

Sep 21, 2006

I have written a stored procedure that accepts 33 parameters. I am trying to execute the stored procedure from a SSIS OLE DB Command task. The code I am putting into the "SqlCommand" property is "Exec dbo.CO_PROD_UPDATE ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?

View 6 Replies View Related

OLE DB Command Error SSIS

Jun 15, 2006

Hi,

when we are using OLEDB Command we are getting the following error?

when we use sql server as the source its working fine .but its throwing error when we try to connect the orcale database .
Error at Data FLOW TASk[OLE DB Command[3863]]: An OLE DB error has occured :0x80040E51.
An OLE DB record is available. Source : "Microsoft OLE DB Provider for Oracle" Hresult: 0x80040E51
Description: "Provider cannot derive parameter information and SetParameterInfo has not been called.".
ERrror at Data Flow Task [OLE DB Command [3863]]: Unable to retrieve destination column description from the Parameter of the SQL Command
Thanks & Regards
Jegan.T





View 4 Replies View Related

SSIS OLE DB Command Fatal Error

Mar 24, 2008

I have a pretty simple senario.

In a data flow task, I have a OLE DB Source, which is a simple select statement. That is then connected to a OLE DB Command object, which executes a stored procedure on a different server, with the columns of the source as parameters to the stored procedure. I then have that connected to another OLE DB Command object, that will update the row in the source, marking it processed if there wasn't an error (no errors raised) in the stored procedure.

This seems to work just fine. Rows that had errors will error out, but I have the max allowed errors set very high to prevent the task from stopping. The problem that I have noticed is that if the very last row of the original source has an error, the component will fail with a fatal error and not go back and mark the rows that did succed as processed.

The fatal error looks like this:
Error: 0xC0047022 at OrderItems, DTS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Insert OrderItem" (97) failed with error code 0xC0202009. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.

At this point I really believe this is a bug in SSIS, because if the last row succeeds, reguardless of how many rows failed in the middle, the last step runs, and updates the source as processed for the rows that did not fail. But if the last row fails, it doesn't execute the next step.

Has anyone experienced this before. I really hope there is a minor configuration change that can be made so I do not have to redesign the package. My only alternative at this point is to redesign the stored procedure to not raise an error, and return any errors in an output parameter, and then use a conditional split to determine what rows to go back and update at the source, and then rather than relying on the package output to see the errors, I'll have to write the errors to a table somewhere.

- Eric

View 1 Replies View Related

Sp_executesql Error From SSIS OLE DB Command

Jul 20, 2006

Hi


I've got an SSIS package, I'm in a Data Flow step that has a OLE DB Command Data Flow Transformation.


The SQLCommand in the OLD DB Command is:
______________________
UPDATE Employment_Episode_Dim
SET Commence_Serv_Date = ?
WHERE AGS_Number = ?
______________________


The package will run through successfully, but I don't see any updates
in Employment_Episode_Dim. I ran a trace, and this is a sample of the
SQL that is being executed:


______________________
exec sp_executesql N'UPDATE EMPLOYMENT_EPISODE_DIM
SET COMMENCE_SERV_DATE = @P1
WHERE (AGS_NUMBER = @P2)',N'@P1 datetime,@P2 int',''2005-01-27
00:00:00:000'',78577229
______________________


If I take that SQL and execute it in a query window, it fails due to
two single quotation marks placed around the date parameter being used
as @P1.


Why does SQL/SSIS put two singles around the date? It's outside of the
string to be executed, so it doesn't seem to need to have the double.


Can anyone please help?


Thanks
Earth

View 2 Replies View Related

SSIS OLE DB Command Task Error

May 25, 2007

hi,



I am having trouble executing a DB2 stored procedure using OLE DB command task.



The stored procedure takes 2 paramters.



1. filename 2. logfilename



filename - servernamefoldername extfilename

logfilename - servernamefoldernamelogfilename

Both are user defined variables with data type string. i created 2 dervied columns FILE and LOG to pass these values.



FILE takes the filename value



LOG takes the logfilename value



When I execute it gives me the following errors.



[OLE DB Command 1 [77]] Error: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "IBM OLE DB Provider for DB2" Hresult: 0x80004005 Description: "[DB2/NT] SQL0811N The result of a scalar fullselect, SELECT INTO statement, or VALUES INTO statement is more than one row. SQLSTATE=21000 ".



[DTS.Pipeline] Error: The ProcessInput method on component "OLE DB Command 1" (77) failed with error code 0xC0202009. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.



[DTS.Pipeline] Error: Thread "WorkThread0" has exited with error code 0xC0202009.



Can anyone please help me resolve this issue.



I can execute the SP using execute sql task but the problem is I need to pass the rejected rows to a reject table for logging purposes. thts why am tryin the OLE DB command.



Thank You

View 7 Replies View Related

Saving Passwrod In SSIS | Error When Using Command Line Dtexec.exe

Sep 13, 2007

Hi All,

In one of my SSIS package (which connect to DB2) i save the DB2 username and password in SSIS packge itself by using option "EncryptAllWithPassword".
When I try to run the packge using dtexecui.exe it works perfectly fine. It prompts me for package password and then run fine.

Now I am trying to run it using command line dtxece.exe and i get fooolwoing error. Please suggest and help!!!
DTExec.exe /FILE C:pkgDB2Load.dtsx /CONFIGFILE C:ConfigpkgConfigMaster.dtsConfig /Decrypt mypassword /CHECKPOINTING OFF /REPORTING EWCDI

Error Message:
----------------------
Description: An OLE DB error has occurred. Error code: 0x80040E21.
An OLE DB record is available. Source: "IBM OLE DB Provider for DB2 Servers" Hresult: 0x80040E21 Description: " SQL30082N Attempt to establish connection fa
iled with security reason "17" ("UNSUPPORTED FUNCTION"). SQLSTATE=08001
"



Thanks
Sid

View 1 Replies View Related

SSIS Package That Runs Fine Through Command Prompt, XML Error When Run Through SQL Server Agent Job CmdExec

Aug 17, 2006

I have an SSIS package that runs fine through command pormpt although when I try to run it from a SQL Servr Agent Job CmdExec step it bombs out.  Please help this has me stumped...the SSIS package uses an XML connection string so certain key settings such as connection strings and email info can be changed easily.  Currently this is all on the same machine.  I have not moved it beyond where I am developing.

 

On the command line I am using the following command...

dtexec /F "S:connectionscontacts.dtsConfig" /DE "password"

 

Below is the output log...

 

Microsoft (R) SQL Server Execute Package Utility
Version 9.00.1399.06 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.

Started:  6:59:40 PM
Progress: 2006-08-16 18:59:41.29
   Source: Data Flow Task
   Validating: 0% complete
End Progress
Progress: 2006-08-16 18:59:41.29
   Source: Data Flow Task
   Validating: 33% complete
End Progress
Progress: 2006-08-16 18:59:41.71
   Source: Data Flow Task
   Validating: 66% complete
End Progress
Progress: 2006-08-16 18:59:41.73
   Source: Data Flow Task
   Validating: 100% complete
End Progress
Progress: 2006-08-16 18:59:41.77
   Source: Data Flow Task
   Validating: 0% complete
End Progress
Progress: 2006-08-16 18:59:41.77
   Source: Data Flow Task
   Validating: 33% complete
End Progress
Progress: 2006-08-16 18:59:41.77
   Source: Data Flow Task
   Validating: 66% complete
End Progress
Progress: 2006-08-16 18:59:41.77
   Source: Data Flow Task
   Validating: 100% complete
End Progress
Progress: 2006-08-16 18:59:41.79
   Source: Data Flow Task
   Prepare for Execute: 0% complete
End Progress
Progress: 2006-08-16 18:59:41.79
   Source: Data Flow Task
   Prepare for Execute: 33% complete
End Progress
Progress: 2006-08-16 18:59:41.79
   Source: Data Flow Task
   Prepare for Execute: 66% complete
End Progress
Progress: 2006-08-16 18:59:41.79
   Source: Data Flow Task
   Prepare for Execute: 100% complete
End Progress
Progress: 2006-08-16 18:59:41.81
   Source: Data Flow Task
   Pre-Execute: 0% complete
End Progress
Progress: 2006-08-16 18:59:41.84
   Source: Data Flow Task
   Pre-Execute: 33% complete
End Progress
Progress: 2006-08-16 18:59:41.90
   Source: Data Flow Task
   Pre-Execute: 66% complete
End Progress
Progress: 2006-08-16 18:59:41.90
   Source: Data Flow Task
   Pre-Execute: 100% complete
End Progress
Progress: 2006-08-16 18:59:41.92
   Source: Data Flow Task
   Post Execute: 0% complete
End Progress
Progress: 2006-08-16 18:59:41.92
   Source: Data Flow Task
   Post Execute: 33% complete
End Progress
Progress: 2006-08-16 18:59:41.92
   Source: Data Flow Task
   Post Execute: 66% complete
End Progress
Progress: 2006-08-16 18:59:41.92
   Source: Data Flow Task
   Post Execute: 100% complete
End Progress
Progress: 2006-08-16 18:59:41.92
   Source: Data Flow Task
   Cleanup: 0% complete
End Progress
Progress: 2006-08-16 18:59:41.93
   Source: Data Flow Task
   Cleanup: 33% complete
End Progress
Progress: 2006-08-16 18:59:41.93
   Source: Data Flow Task
   Cleanup: 66% complete
End Progress
Progress: 2006-08-16 18:59:41.93
   Source: Data Flow Task
   Cleanup: 100% complete
End Progress
Progress: 2006-08-16 18:59:41.95
   Source: Send Mail Task
   The SendMail task is initiated.: 0% complete
End Progress
Progress: 2006-08-16 18:59:42.09
   Source: Send Mail Task
   The SendMail task is completed.: 100% complete
End Progress
DTExec: The package execution returned DTSER_SUCCESS (0).
Started:  6:59:40 PM
Finished: 6:59:42 PM
Elapsed:  1.984 seconds

 

 

When I try to use the same command within SQL Server Agent Job using a CmdExec step I get the following error...

 

Description: The package is attempting to configure from the XML file "S:connectionscontacts.dtsConfig".  End Info  Warning: 2006-08-16 18:40:03.15     Code: 0x80012012     Source: contactsPackage Description: The configuration file name "S:connectionscontacts.dtsConfig" is not valid. Check the configuration file name.  End Warning  Warning: 2006-08-16 18:40:03.15     Code: 0x80012059     Source: contactsPackage Description: Failed to load at least one of the configuration entries for the package. Check configurations entries and previous warnings to see descriptions of which configuration failed.  End Warning  Info: 2006-08-16 18:40:03.20  ...  Process Exit Code 1.  The step failed.

 

Thanks in advance for any help!!!

View 2 Replies View Related

Can I Pass A SqlParameterCollection Object Into A SQL Command

Dec 6, 2004

I currently have a connection class that handles all of my database connectivity. What I am trying to do is build a SqlParameterCollection object on one page, then pass that object to my connection class. Is it possible to do this? I am not getting any compilation errors, but I can not get the parameters to be recognized. Here is what I am trying to do:

Page 1:

string sql = null;
conn.strConn = connectionstring;

sql = "sqlstring";

SqlParameterCollection newcollect = null;
newcollect.Add("@Switch",1);

conn.OpenReader(sql, newcollect);
while (conn.DR.Read())
{
read data onto page here...
}
conn.CloseReader();

Page 2 (connection class) :

public void OpenReader(string sql, SqlParameterCollection collect)
{
Conn = new SqlConnection(strConn);
Conn.Open();
Command = new SqlCommand(sql,Conn);

Command.Parameters.Add(collect); <------This is the root of my question
Command.CommandTimeout = 300;
// executes sql and fills data reader with data
DR = Command.ExecuteReader();
}

Can you do this??? And if so, can anyone tell me why the statement will not return any data? The procedure works perfectly, and will work if I use the standard way of passing the parameters to the command statement.

View 4 Replies View Related

Removing SQL Parameters From Command Object After Use.

Sep 26, 2006

Hi all,

is it good practice to remove SQL Parameters from the Command Object's parameters collection after the CommandObject has executed its SQL containing the SQL Parameters?

The reason I ask is because I have encountered some issues (errors) stating that the SQL Parameter cant be used because it is still contained in another Parameter collection - ( I created the Parameters, and then use these same parameter (through looping) to execute new commands).

Thanks

View 3 Replies View Related

Defining Command,commandtype And Connectionstring For SELECT Command Is Not Similar To INSERT And UPDATE

Feb 23, 2007

i am using visual web developer 2005 and SQL 2005 with VB as the code behindi am using INSERT command like this        Dim test As New SqlDataSource()        test.ConnectionString = ConfigurationManager.ConnectionStrings("DatabaseConnectionString1").ToString()        test.InsertCommandType = SqlDataSourceCommandType.Text        test.InsertCommand = "INSERT INTO try (roll,name, age, email) VALUES (@roll,@name, @age, @email) "                  test.InsertParameters.Add("roll", TextBox1.Text)        test.InsertParameters.Add("name", TextBox2.Text)        test.InsertParameters.Add("age", TextBox3.Text)        test.InsertParameters.Add("email", TextBox4.Text)        test.Insert() i am using UPDATE command like this        Dim test As New SqlDataSource()        test.ConnectionString = ConfigurationManager.ConnectionStrings("DatabaseConnectionString").ToString()        test.UpdateCommandType = SqlDataSourceCommandType.Text        test.UpdateCommand = "UPDATE try SET name = '" + myname + "' , age = '" + myage + "' , email = '" + myemail + "' WHERE roll                                                         123 "        test.Update()but i have to use the SELECT command like this which is completely different from INSERT and  UPDATE commands   Dim tblData As New Data.DataTable()         Dim conn As New Data.SqlClient.SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|Database.mdf;Integrated                                                                                Security=True;User Instance=True")   Dim Command As New Data.SqlClient.SqlCommand("SELECT * FROM try WHERE age = '100' ", conn)   Dim da As New Data.SqlClient.SqlDataAdapter(Command)   da.Fill(tblData)   conn.Close()                   TextBox4.Text = tblData.Rows(1).Item("name").ToString()        TextBox5.Text = tblData.Rows(1).Item("age").ToString()        TextBox6.Text = tblData.Rows(1).Item("email").ToString()       for INSERT and UPDATE commands defining the command,commandtype and connectionstring is samebut for the SELECT command it is completely different. why ?can i define the command,commandtype and connectionstring for SELECT command similar to INSERT and UPDATE ?if its possible how to do ?please help me

View 2 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 cant 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

Do Somebody Know How Long (in Chars) Script(command) Can Be Solved By SQL Command?

Aug 30, 2004

Do somebody know how long (in chars) script(command) can be solved by SQL Command?
Thanks

View 1 Replies View Related

What Command Is Used To Get Back The Privileges Offered By The GRANT Command?

Mar 10, 2007

reply.

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

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 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

SQL Tools :: Can Make SSMS Parse Command Resolve Object Names?

Jul 29, 2010

When I run the parse command in SSMS, it merely does a syntax check.  When I run through the export data wizard by right clicking on a table, it allows a query as the data source.  When I click on the parse button in the window that accepts that query, it resolves object names and notifies me of invalid ones.  I'd really like the same thing to happen when I parse in SSMS...

View 5 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

SQL Command Text

Jun 22, 2007

I have a select statement as follows:




Code Snippetselect * from employees where empname in ('name1', 'name2')



im trying to implement this with parameters in an OLE DB Source using SQL Command Text...im having trouble implementing the multiple parameters. Any suggestions on how to do this? Im trying to assign the components of the 'in' field in variables, also, im not gonna know before hand how many 'in' parameters im gonna have.







View 11 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







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