Can Is Pass Tablename In SQL Stmt As Variable

Jul 1, 1999

I am trying to create a stored procedure for automating Bulk inserting into tables
for one database to another.
Is there any way i can pass the table name as variable to insert and select stmt


thanks in advance

View 1 Replies


ADVERTISEMENT

Pass &<tablename&> As Parametre In StoredProcedure

Feb 15, 2008

Hi Experts,I m using a stored procedure:set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgoALTER PROCEDURE [dbo].[SP_Table_SELECT](    @TableName VarChar)ASset nocount onset ansi_warnings offDECLARE @sql varchar(2000)SET @sql = 'SELECT* FROM (' +@TableName + ')'EXEC (@sql)when I run It it shows return value = o and Query Completed with ErrorsThere are data in My Table. Help Plzzzz!!!!!! 

View 7 Replies View Related

How Do I Pass A Parameter From A SSRS Report To The Sql Stmt In A SSIS Package

Aug 24, 2007

How do I pass a parameter from a SSRS report to the sql stmt in a SSIS package?
Mainly need to know the correct syntax of the connection string to use for the datasource in the SSRS report. Every time I add the /SET part of the string the connection breaks.
The connection string i've been using is:
/file "C:\PackageName.dtsx /Set Package.Variables[StartDate];"&Parameters!StartDate.Value

View 26 Replies View Related

Pass A Parameter From A SSRS Report To The Sql Stmt In A SSIS Package

Aug 24, 2007

How do I pass a parameter from a SSRS report to the sql stmt in a SSIS package?
Mainly need to know the correct syntax of the connection string to use for the datasource in the SSRS report. Every time I add the /SET part of the string the connection breaks.
The connection string i've been using is:
/file "C:\PackageName.dtsx /Set Package.Variables[StartDate];"&Parameters!StartDate.Value

View 3 Replies View Related

Using Cursor Variable As A Tablename

Oct 12, 2005

I am currently using a cursor to scroll through sysobjects to extract table names and then extracting relevant column names from syscolumns.

I then need to run the following script:

declare Detail_Cursor cursor for
select @colname, max(len(@colname)) from @table

The message I receive is "must declare variable @table".

Immediately prior to this script I printed out the result of @table which works fine so the variable obviously is declared and populated.

Can anyone let me know what I'm doing wrong or how I can get the same result.

Thanks

View 4 Replies View Related

Function With A Tablename As Variable

Feb 7, 2006

Dear all,

Can someone help me with the following function? I would like to use a table name as a variable.

Thanks in advance!

CREATE FUNCTION FAC_user.Overzicht_DTe (@tabel1 as nvarchar, @proces as nvarchar, @categorie as nvarchar)
RETURNS numeric AS
BEGIN
declare @aantal numeric

if @proces = 'Inhuizen'
begin
if @categorie = 'open_op_tijd'
begin
SET @aantal =(SELECT Count(@tabel1 + '.Contractnummer')
FROM @tabel1, Rapportageweek
WHERE@tabel1.Verwerkingsdatum is null
AND @tabel1.UiterlijkeVerwDatum >= Rapportageweek.Rapportagedatum
AND @tabel1.ItemType = 'ZVHG'
AND @tabel1.ItemType = 'ZVHN'
AND @tabel1.ItemType = 'ZVIG'
AND @tabel1.ItemType = 'ZVIN'
GROUP BY@tabel1.Maand, @tabel1.Jaar)
end

if @categorie = 'open_te_laat'
begin
SET @aantal =(SELECT Count(@tabel1 + '.Contractnummer')
FROM @tabel1, Rapportageweek
WHERE@tabel1.Verwerkingsdatum is null
AND @tabel1.UiterlijkeVerwDatum < Rapportageweek.Rapportagedatum
AND @tabel1.ItemType = 'ZVHG'
AND @tabel1.ItemType = 'ZVHN'
AND @tabel1.ItemType = 'ZVIG'
AND @tabel1.ItemType = 'ZVIN'
GROUP BY@tabel1.Maand, @tabel1.Jaar)
end

end

return @aantal

END

View 2 Replies View Related

Using A Variable For Tablename In Select Statement?

Sep 19, 2007

I have a stored procedure that accepts the table name as a parameter. Is there anyway I can use this variable in my select statement after the 'from' clause. ie "select count(*) from @Table_Name"?
When I try that is says "Must declare the table variable @Table_Name". Thanks!

View 1 Replies View Related

How Can You Use A Variable Tablename And Retrieve The Output From The Insert?

Aug 28, 2007

We are trying to create a unique key from a table with indentity set in the table. We will have a number of these tables. Therefore, we will be creating a stored procedure and passing the table as a parameter. In this example we are setting the table.

When we run the the script, the output clause from the insert should give us a unique number from the given table in the temporary table. This example stores the output in a temporary table @tTemp.

How can you use a variable table name and retrieve the output from the Insert?


declare @tTestTable varchar (20)

set @tTestTable = 'mis.test_sequence'


--DECLARE @tTestTable TABLE ( sqVal [int] IDENTITY(1,1) NOT NULL, add_date datetime)
declare @testsql varchar (4000), @testseq int

DECLARE @tTemp table (mainpk int)

set @testsql = 'DECLARE @tTemp table (mainpk int) INSERT ' + @tTestTable + ' OUTPUT INSERTED.sqVal into @tTemp VALUES (getdate() ) SELECT @testseq=mainpk FROM @tTemp'

select @testsql

EXECUTE sp_executesql @testsql, N'@testseq int output,@tTemp table (mainpk int),@tTemp table (mainpk int) ',@tTemp,@tTemp,@testseq output,@tTemp

SELECT * FROM @tTemp



Please help
Thanks Tim.

View 3 Replies View Related

Integration Services :: Passing Tablename In Query As Variable?

May 6, 2015

I am using a sql task to get all tablenames and then passing the output to another sql task inside a for each container.

So that the 2nd sql task will be executed for each table. My query looks like SELECT DISTINCT b.EmailAddress FROM  ? ......

Since I am passing the tablename as a variable (output from the 1st sql task), I get the following error:

[Task Execute SQL] Error:

Failed to execute the query 'SELECT DISTINCT b.EmailAddress FROM? AS a INNER... ':' Failed to extract

the result in a variable of type (DBTYPE_I4)'. Possible causes include the following: Problems with the query, not properly fixed ResultSet property, not properly set parameters or not properly established connection.

how to pass a tablename as a variable to a query?

View 5 Replies View Related

SSIS: Problem Mapping Global Variables To Stored Procedure. Can't Pass One Variable To Sp And Return Another Variable From Sp.

Feb 27, 2008

I'm new to SSIS, but have been programming in SQL and ASP.Net for several years. In Visual Studio 2005 Team Edition I've created an SSIS that imports data from a flat file into the database. The original process worked, but did not check the creation date of the import file. I've been asked to add logic that will check that date and verify that it's more recent than a value stored in the database before the import process executes.

Here are the task steps.


[Execute SQL Task] - Run a stored procedure that checks to see if the import is running. If so, stop execution. Otherwise, proceed to the next step.

[Execute SQL Task] - Log an entry to a table indicating that the import has started.

[Script Task] - Get the create date for the current flat file via the reference provided in the file connection manager. Assign that date to a global value (FileCreateDate) and pass it to the next step. This works.

[Execute SQL Task] - Compare this file date with the last file create date in the database. This is where the process breaks. This step depends on 2 variables defined at a global level. The first is FileCreateDate, which gets set in step 3. The second is a global variable named IsNewFile. That variable needs to be set in this step based on what the stored procedure this step calls finds out on the database. Precedence constraints direct behavior to the next proper node according to the TRUE/FALSE setting of IsNewFile.


If IsNewFile is FALSE, direct the process to a step that enters a log entry to a table and conclude execution of the SSIS.

If IsNewFile is TRUE, proceed with the import. There are 5 other subsequent steps that follow this decision, but since those work they are not relevant to this post.
Here is the stored procedure that Step 4 is calling. You can see that I experimented with using and not using the OUTPUT option. I really don't care if it returns the value as an OUTPUT or as a field in a recordset. All I care about is getting that value back from the stored procedure so this node in the decision tree can point the flow in the correct direction.


CREATE PROCEDURE [dbo].[p_CheckImportFileCreateDate]

/*

The SSIS package passes the FileCreateDate parameter to this procedure, which then compares that parameter with the date saved in tbl_ImportFileCreateDate.

If the date is newer (or if there is no date), it updates the field in that table and returns a TRUE IsNewFile bit value in a recordset.

Otherwise it returns a FALSE value in the IsNewFile column.

Example:

exec p_CheckImportFileCreateDate 'GL Account Import', '2/27/2008 9:24 AM', 0

*/

@ProcessName varchar(50)

, @FileCreateDate datetime

, @IsNewFile bit OUTPUT

AS

SET NOCOUNT ON

--DECLARE @IsNewFile bit

DECLARE @CreateDateInTable datetime

SELECT @CreateDateInTable = FileCreateDate FROM tbl_ImportFileCreateDate WHERE ProcessName = @ProcessName

IF EXISTS (SELECT ProcessName FROM tbl_ImportFileCreateDate WHERE ProcessName = @ProcessName)

BEGIN

-- The process exists in tbl_ImportFileCreateDate. Compare the create dates.

IF (@FileCreateDate > @CreateDateInTable)

BEGIN

-- This is a newer file date. Update the table and set @IsNewFile to TRUE.

UPDATE tbl_ImportFileCreateDate

SET FileCreateDate = @FileCreateDate

WHERE ProcessName = @ProcessName

SET @IsNewFile = 1

END

ELSE

BEGIN

-- The file date is the same or older.

SET @IsNewFile = 0

END

END

ELSE

BEGIN

-- This is a new process for tbl_ImportFileCreateDate. Add a record to that table and set @IsNewFile to TRUE.

INSERT INTO tbl_ImportFileCreateDate (ProcessName, FileCreateDate)

VALUES (@ProcessName, @FileCreateDate)

SET @IsNewFile = 1

END

SELECT @IsNewFile

The relevant Global Variables in the package are defined as follows:
Name : Scope : Date Type : Value
FileCreateDate : (Package Name) : DateType : 1/1/2000
IsNewFile : (Package Name) : Boolean : False

Setting the properties in the "Execute SQL Task Editor" has been the difficult part of this. Here are the settings.

General
Name = Compare Last File Create Date
Description = Compares the create date of the current file with a value in tbl_ImportFileCreateDate.
TimeOut = 0
CodePage = 1252
ResultSet = None
ConnectionType = OLE DB
Connection = MyServerDataBase
SQLSourceType = Direct input
IsQueryStoredProcedure = False
BypassPrepare = True

I tried several SQL statements, suspecting it's a syntax issue. All of these failed, but with different error messages. These are the 2 most recent attempts based on posts I was able to locate.
SQLStatement = exec ? = dbo.p_CheckImportFileCreateDate 'GL Account Import', ?, ? output
SQLStatement = exec p_CheckImportFileCreateDate 'GL Account Import', ?, ? output

Parameter Mapping
Variable Name = User::FileCreateDate, Direction = Input, DataType = DATE, Parameter Name = 0, Parameter Size = -1
Variable Name = User::IsNewFile, Direction = Output, DataType = BYTE, Parameter Name = 1, Parameter Size = -1

Result Set is empty.
Expressions is empty.

When I run this in debug mode with this SQL statement ...
exec ? = dbo.p_CheckImportFileCreateDate 'GL Account Import', ?, ? output
... the following error message appears.

SSIS package "MyPackage.dtsx" starting.
Information: 0x4004300A at Import data from flat file to tbl_GLImport, DTS.Pipeline: Validation phase is beginning.

Error: 0xC002F210 at Compare Last File Create Date, Execute SQL Task: Executing the query "exec ? = dbo.p_CheckImportFileCreateDate 'GL Account Import', ?, ? output" failed with the following error: "No value given for one or more required parameters.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Task failed: Compare Last File Create Date

Warning: 0x80019002 at GLImport: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

SSIS package "MyPackage.dtsx" finished: Failure.

When the above is run tbl_ImportFileCreateDate does not get updated, so it's failing at some point when calling the procedure.

When I run this in debug mode with this SQL statement ...
exec p_CheckImportFileCreateDate 'GL Account Import', ?, ? output
... the tbl_ImportFileCreateDate table gets updated. So I know that data piece is working, but then it fails with the following message.

SSIS package "MyPackage.dtsx" starting.
Information: 0x4004300A at Import data from flat file to tbl_GLImport, DTS.Pipeline: Validation phase is beginning.

Error: 0xC001F009 at GLImport: The type of the value being assigned to variable "User::IsNewFile" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.

Error: 0xC002F210 at Compare Last File Create Date, Execute SQL Task: Executing the query "exec p_CheckImportFileCreateDate 'GL Account Import', ?, ? output" failed with the following error: "The type of the value being assigned to variable "User::IsNewFile" differs from the current variable type. Variables may not change type during execution. Variable types are strict, except for variables of type Object.
". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Compare Last File Create Date

Warning: 0x80019002 at GLImport: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

SSIS package "MyPackage.dtsx" finished: Failure.

The IsNewFile global variable is scoped at the package level and has a Boolean data type, and the Output parameter in the stored procedure is defined as a Bit. So what gives?

The "Possible Failure Reasons" message is so generic that it's been useless to me. And I've been unable to find any examples online that explain how to do what I'm attempting. This would seem to be a very common task. My suspicion is that one or more of the settings in that Execute SQL Task node is bad. Or that there is some cryptic, undocumented reason that this is failing.

Thanks for your help.

View 5 Replies View Related

Pass Variable

Aug 8, 2007

Hello,
I placed a post regarding this issue previously but no success. So I thought I explain everything properly this time in a new post. Thanks

I have created a stored procedure which passes variables to the ssis package and then executes the package.
The two variables inside the ssis package are @FileName and @ConnectionPath
As you can see from the below stored procedure, xp_cmdshell is used to execute the package.
If only the first variable is used in the package and the @connectionPath variable is hardcoded inside the package then package runs fine.
Problem is in this particular call as you see below because @ConnectionPath is included.

The output of print is:

dtexec /f d:sysapplCEMSSISImportsTradesBaseProfiles2.dtsx /set Package.Variables[User::FileName].Properties[Value];"d:ApplDataCEMWorkingTempprofiles.csv"
/set Package.Variables[User::ConnectionPath].Properties[Value];"Data Source=servername1instancename1, 2025;Initial Catalog=CounterpartyExposure;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;"


Error is:

Error: 2007-08-08 08:46:01.29
Code: 0xC0202009
Source: BaseProfiles2 Connection manager "CounterpartyExposure"
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft OLE DB Provider for ODBC Drivers" Hresult: 0x80004005 Description: "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified".
End Error


if only the output is run in the query analyser then the error is:

The identifier that starts with 'Data Source=gblond088sjyMSQL_curves_DEV1, 2025;Initial Catalog=CounterpartyExposure;Provider=SQLNCLI.1;Integrated Security=SSPI' is too long. Maximum length is 128.



/*********************************************************************************

uspCEMTradeExecutePackage2 'd:sysapplCEMSSISImportsTradesStaticMappingOverride.dtsx',
'StaticMappingOverride.csv',
'Data Source=servername1instancename1, 2025;Initial Catalog=CounterpartyExposure;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;'
*********************************************************************************/

ALTER procedure [dbo].[uspCEMTradeExecutePackage2]

@FullPackagePath varchar(1000),
@FileName varchar(500),
@ConnectionPath varchar(1000)


as

declare @returncode int
declare @cmd varchar(1000)

declare @FilePath varchar(1000)
declare @FullFilePath varchar(1000)

set @FilePath = 'd:ApplDataCEMWorkingTemp'
set @FullFilePath = @FilePath + @FileName
print ' ----------- ' + @FileName

set @cmd = 'dtexec /f ' + @FullPackagePath + ' /set Package.Variables[User::FileName].Properties[Value];"' + @FullFilePath + '"'
set @cmd = 'dtexec /f ' + @FullPackagePath +
' /set Package.Variables[User::FileName].Properties[Value];"' + @FullFilePath + '"
/set Package.Variables[User::ConnectionPath].Properties[Value];"' + @ConnectionPath + '"'

print @cmd

set nocount on

begin try

exec @returncode = master..xp_cmdshell @cmd

end try

begin catch

exec @LastGoodVersionSP

DECLARE @msg nvarchar(200)
SET @msg = ('Error during execute package')

EXECUTE uspErrorReporter @msg
end catch

set nocount off

View 1 Replies View Related

How To Pass A Variable Like 'Santhosh'

Dec 10, 2007

Hi
I have a stored procedure which will do some thing like this
create proc xxx
(
@useralias varchar(32)
)
set @useralias = '''' +  @useralias + '''';
select * from users where useralias =  @useralias
EX: Select * from users where useralias = 'Santhosh'. But, when i give the query like this
EXec xxx santhosh
It gives the error message ...no column by name santhosh or Invalid column. So, i thought i will pass the same from the UI
EX:
string str = "'" +  santhos + "'".
But, the probem with this is, it is not giving any results.
So, how do i get around with this prob?
Thanks!
Santhosh

View 2 Replies View Related

How Do I Pass Variable Into DTS Package?

Nov 20, 2001

I have a SQL 7 package which uses a select statement. Within the the select statement is
a where clause. I would like to define a string variable for the object of the where clause. I would
like to have this string variable passed into the package just prior to execution of the package.

Anyone know how to do this?

Thanks in advance for the help!!!!!

Gary Andrews
andrews_gary_w@solarturbines.com

View 2 Replies View Related

T-SQL (SS2K8) :: Trying To Pass A Value To A Variable

May 4, 2015

I am trying to do the following:

DECLARE @MinBoundary DateTime, @s nvarchar(max)
declare @DBName sysname, @TableName sysname
select @DBName = 'MyDB', @TableName = 'MyTable'
select @s = '
SELECT Convert(DateTime, MIN(PRV.value))

[Code] ...

It works if I print @s query and run it this way, so the query is correct):

DECLARE @MinBoundary DateTime
SELECT @MinBoundary = Convert(DateTime, MIN(PRV.value))
FROM sys.partition_functions AS PF
JOIN sys.partition_parameters AS PP
ON PF.function_id = PP.function_id

[Code] ....

View 6 Replies View Related

How To Pass Value To Bind Variable

Jun 19, 2008

select field1 from table1 where field1 = @p_field1

Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@p_field1".

Hi, i'm getting this error, could anybody please explain, how the syntax should be.........thanks

View 8 Replies View Related

How To Pass A Variable Into In Operator

Jul 19, 2006

How can i execute this query thru Stored procedure ?

delete from ts_imported where ts_imported.c_change_symbol in ('A','B')

Symbols ('A','B') varies
How to pass this in paramter


I tried this

declare @Symbol varchar(20)
declare @apos char(1)
set @apos=''''

set @Symbol = @apos +'A' + @apos + ',' + @apos + 'B' + @apos
delete from ts_imported where c_change_symbol in (@Symbol)

Doesn't produce correct output!

View 7 Replies View Related

Pass Variable With A Space

Jul 31, 2007

Hi,
I am passing a filename to a command in sql.
The problem is that it gives an error if the @FullFilePath has a space in it.


set @cmd = 'dtexec /f ' + @FullPackagePath + ' /set Package.Variables[User::FileName].Properties[Value];' + @FullFilePath + '"'

print @cmd


Thanks

View 4 Replies View Related

How To Pass SourceConnectionFlatFile From A Variable?

Apr 20, 2007

Hello,

In my Data Flow Task, I have a Flat File source that I would like to use a variable. How do I accomplish that? I tried it in Advanced Editor & Connection Managers, but not sure where I could slide my variables for it to be recognized.



Any help is appreciated,

-Lawrence

View 7 Replies View Related

How To Pass A Variable To The Stored Procedure?

Feb 22, 2007

Hi,
i need to insert a record 1 or more times, depending of a variable in code-behind:dim amount as integeramount= value (e.g. 3)
My problem is: how to pass that variable to the stored procedure?I tried with this but nothing happens:
comd.Parameters.Add("@amount", SqlDbType.NVarChar, 10).Value = amount_of_details
Maybe is my stored procedure wrong?
Thanks
T.
Here is it:----------
ALTER PROCEDURE dbo.insert_table (@field1 nvarchar(10),...)ASDeclare @iLoopNumber intDeclare @amount intBEGIN TRAN
SET @iLoopNumber = 1
SET @amountr
While (@iLoopNumber <= @amount)
BEGIN
INSERT INTO table(field1,...)
VALUES (....))
 SET @iLoopNumber = @iLoopNumber +1
End
COMMIT TRAN
 

View 3 Replies View Related

Update SQL Variable Pass Problem

Sep 27, 2005

Sorry to bother you.
 
I am trying to create an Update Form. There is only one Field that can be edited on this form – agencydesc  (Agency Description).  I’m trying to perform the update to the Database the .Net way and I am definitely having some trouble. The form is loaded from the file AgencyEdit.aspx. An Image Button does a Server.Transfer to AgencyUPD.aspx where the update is completed. When AgencyEdit.aspx is loaded the Agency Description is pre-populated in TextBox1. When the value of TextBox1 is changed, the change does not show when the variable is passed to AgencyUPD.aspx, the value remains the initial populated value from the Database. Any help would greatly be appreciated.
 
Relevant CS Code from AgencyEdit.aspx
 
      public string myBase;
      public int eSernum;
      public string eAgency;
      public string eDesc;
      protected System.Web.UI.WebControls.ImageButton ImageButton3;
      protected System.Web.UI.WebControls.TextBox TextBox1;
       
      public string myTest
      {
            get {return this.TextBox1.Text;}
      }
       
      private void Page_Load(object sender, System.EventArgs e)
      {
      string myUrl = Application.Contents["Base"] + Common/Login.aspx";
      if (Session["isLogged"] == null) Response.Redirect(myUrl);
           
      myBase = Application.Contents["Base"].ToString();
      int mySernum = Int32.Parse(Request.QueryString["sernum"]);
           
SqlConnection con1 = new SqlConnection(Application.Contents["EwsConnectionString"].ToString());
      SqlDataReader drsLog1;
      SqlCommand cmd1 = new SqlCommand();
 
      con1.Open();
       
      cmd1.CommandText = "SELECT * FROM ewsagencies WHERE sernum = " + mySernum;
      cmd1.Connection = con1;
      drsLog1 = cmd1.ExecuteReader(CommandBehavior.CloseConnection);
       
      while (drsLog1.Read())
       {
       eSernum = drsLog1.GetInt32(0);
       eAgency = drsLog1.GetString(1);
       if (!drsLog1.IsDBNull(2)) eDesc = drsLog1.GetString(2);
       }
      con1.Close();
      this.TextBox1.Text = eDesc;  
      }
 
      private void ImageButton3_Click(object sender, System.Web.UI.ImageClickEventArgs e)
      {
       Server.Transfer("AgencyUPD.aspx");
      }
 
 
Relevant CS Code from AgencyUPD.aspx
 
 
namespace VWeb.vAdmin.Agencies
{
 public class AgencyUPD : System.Web.UI.Page
 {
   public AgencyEdit SourcePage;
  
private void Page_Load(object sender, System.EventArgs e)
      {
      AgencyEdit SourcePage = (AgencyEdit) Context.Handler;
      int mySernum = SourcePage.eSernum;
      string myDesc = SourcePage.myTest;
 
SqlConnection conUpdate = new SqlConnection(Application.Contents["EwsConnectionString"].ToString());
      SqlCommand cmd1 = new SqlCommand();
 
      conUpdate.Open();
       
      cmd1.CommandText = "UPDATE ewsagencies SET agencydesc = @desc WHERE sernum = " + mySernum;
      cmd1.Parameters.Add("@desc", SqlDbType.VarChar).Value = myDesc;
      cmd1.Connection = conUpdate;
 
      cmd1.ExecuteNonQuery();
 
      conUpdate.Close();
      string myUrl = Application.Contents["Base"] + "vAdmin/Agencies/AgencyList.aspx";
      Response.Redirect(myUrl);
}
 
            #region Web Form Designer generated code .. etc.
      }
}
 
 
 

View 1 Replies View Related

How Do You Pass Values From VB To A DTS Global Variable?

Jun 24, 2002

I am trying to pass a value from a VB Custom Task to
a DTS. The DTS doesn't get the value and I do not understand
why. Snipet follows:

oPKG.GlobalVariables.Item("gsAnyTypeData").Value = "Hello World"
oPKG.GlobalVariables.AddGlobalVariable ("gsAnyTypeData"), "Hello World"
oPKG.LoadFromSQLServer ".", , , 256, , , , DTSpackage_to_execute
oPKG.Execute

I've tried declaring the global variable in the called DTS and
I've tried without it. Neither contain the value when the DTS is
executed.

Thanks for your time and help,
Martin

View 3 Replies View Related

Pass Variable To Stored Procdure

Apr 4, 2007

I have the following stroed procedure and need someone to tell me what i am doing wrong. First off i am passing the value of sName from ASP to my sql stored procedure.

the following will work if i do (select distinct * )


Code:

SQL:
CREATE PROCEDURE Get_codes
@sName varchar(255)

AS

Select DISTINCT @sName
From CheckDetail
Where @sName is not Null and @sName <> ''
/*Order by @sName*/

GO

View 1 Replies View Related

SQL Server 2014 :: Not Able To Pass Value To A Variable

Aug 10, 2014

I've the below query where I am referencing an employee table that has the following structure and has three employee records

IDFIRST_NAMEEMAIL_ADDR
123DONALD DONALD@GMAIL.COM
345MAYANK MAYANKMIN@GMAIL.COM
657MAYANK_NEWMAYANKCGG@GMAIL.COM

I'm passing name of this table (dbO.TD_EmployeeProfile_FinalV2) to get the counts from function FN_COUNT_ROWS (count is 3 as you can see above) so this while loop should run 3 times for all three employees. I'm having issues with statement "SELECT @EMP_ID" and "SELECT @EMAIL"

DECLARE @email varchar(500)
,@intFlag INT
,@INTFLAGMAX int
,@TABLE_NAME VARCHAR(100)
,@EMP_ID INT

[Code] .....

---ERRORS i'm getting
Msg 102, Level 15, State 1, Line 23
Incorrect syntax near ')'.
Msg 102, Level 15, State 1, Line 24
Incorrect syntax near ')'

View 3 Replies View Related

Pass Sysdate Value To Global Variable

Aug 27, 2007



Hi,
How can we pass sysdate value as default value to a GlobalVariable to SSIS?

Regards,
Mohammad Sufian

View 1 Replies View Related

How Should I Pass Variable To Data Flow

Apr 8, 2008

Hi all,
By using for each loop container and script task, i am able to pick the file name from a specified folder to a user defined variable. Now i am trying to pass this variable to excel source (using data flow), but i am getting this error : -

===================================

Error at Data Flow Task [Excel Source [1]]: A destination table name has not been provided.

(Microsoft Visual Studio)

===================================

Exception from HRESULT: 0xC0202042 (Microsoft.SqlServer.DTSPipelineWrap)

------------------------------
Program Location:

at Microsoft.SqlServer.Dts.Pipeline.Wrapper.CManagedComponentWrapperClass.ReinitializeMetaData()
at Microsoft.DataTransformationServices.DataFlowUI.DataFlowComponentUI.ReinitializeMetadata()
at Microsoft.DataTransformationServices.DataFlowUI.DataFlowAdapterUI.connectionPage_SaveConnectionAttributes(Object sender, ConnectionAttributesEventArgs args)


Please can you suggest me how should i pass the vaiable to the data flow and how the Excel sheet will be selected there.
Hi all,
By using for each loop container and script task, i am able to pick the file name from a specified folder to a user defined variable. Now i am trying to pass this variable to excel source (using data flow), but i am getting this error : -

===================================

Error at Data Flow Task [Excel Source [1]]: A destination table name has not been provided.

(Microsoft Visual Studio)

===================================

Exception from HRESULT: 0xC0202042 (Microsoft.SqlServer.DTSPipelineWrap)

------------------------------
Program Location:

at Microsoft.SqlServer.Dts.Pipeline.Wrapper.CManagedComponentWrapperClass.ReinitializeMetaData()
at Microsoft.DataTransformationServices.DataFlowUI.DataFlowComponentUI.ReinitializeMetadata()
at Microsoft.DataTransformationServices.DataFlowUI.DataFlowAdapterUI.connectionPage_SaveConnectionAttributes(Object sender, ConnectionAttributesEventArgs args)


Please can you suggest me how should i pass the vaiable to the data flow and how the Excel sheet will be selected there.

View 4 Replies View Related

How To Pass Variable Values Between Tasks

Feb 16, 2007

Hi Everybody,

I have to generate a seq num and increment it by 1,i used execute sql task and wrote a proc using a variable(out) now this out variable has that value.Now i need to add a extra column to all the records coming in this current batch with the value in the variable.

I know that i can use derived column transformation ,but its not working,giving the following error.

the errors thrown are:

[OLE DB Destination [16]] Error: An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80004005 Description: "Unspecified error". An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80004005 Description: "ORA-01779: cannot modify a column which maps to a non key-preserved table ".
[OLE DB Destination [16]] Error: The "input "OLE DB Destination Input" (29)" failed because error code 0xC020907B occurred, and the error row disposition on "input "OLE DB Destination Input" (29)" specifies failure on error. An error occurred on the specified object of the specified component.
[DTS.Pipeline] Error: The ProcessInput method on component "OLE DB Destination" (16) failed with error code 0xC0209029. 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 0xC0209029.
[OLE DB Source [1]] Error: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.
[DTS.Pipeline] Error: The PrimeOutput method on component "OLE DB Source" (1) returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
[DTS.Pipeline] Error: Thread "SourceThread0" has exited with error code 0xC0047038.

i have created a variable with scope as package,used that in execute sql task.then using precedence constrains added a data flow task to it.

In the data flow task between the source and destination i placed a derived column transformation.

the execute sql task is running fine,dataflow task is failing.In dataflow task also source and destination is failing but derived column transformation is working.

Am i doing correct.Pls advice.



Regards

Swan



View 3 Replies View Related

Pass Variable Value To DataReader Source

Aug 17, 2007

Dear All,

I have created a DTS Package in Integration Services 2005.
Within the DTS Package declared a variable named xxx and passed a value 1234.

In the control flow i dropped a Data flow task and in the Property Expression Editor of DataFlow Task i defined
Property = [DataReader Source].[sqlCommand]
Expression = Variable name.

Now in the DataFlow Task Canvas dropped DataReaderSource.

How can i pass variable value to the SQLCommand ="Select * from table where name = Variable value.

regards
Sufian

View 4 Replies View Related

Pass Variable To Identity Function

Aug 11, 2006

is it possible to pass a variable to an identity funtion

example

declare @max_note int

select @max_note = max(key ) from notes

select m_key = identity( int, @max_note, 1),
name

into #prod_note

from prod_note

View 3 Replies View Related

Transact SQL :: Can Pass A Variable Value Out While Loop

Nov 3, 2015

-- Create an Employee table.

CREATE TABLE dbo.MyEmployees
(
EmployeeID smallint NOT NULL,
FirstName nvarchar(30)  NOT NULL,
LastName  nvarchar(40) NOT NULL,
Title nvarchar(50) NOT NULL,
DeptID smallint NOT NULL,
ManagerID int NULL,
 CONSTRAINT PK_EmployeeID PRIMARY KEY CLUSTERED (EmployeeID ASC) 
);

-- Populate the table with values.

INSERT INTO dbo.MyEmployees VALUES 
 (1, N'Ken', N'Sánchez', N'Chief Executive Officer',16,NULL)
,(273, N'Brian', N'Welcker', N'Vice President of Sales',3,1)
,(274, N'Stephen', N'Jiang', N'North American Sales Manager',3,273)
,(275, N'Michael', N'Blythe', N'Sales Representative',3,274)

[code]....

View 3 Replies View Related

Howto Pass Variable Into Bulk Insert In SP?

Aug 7, 2006

hi, good day,

if i would like to pass a parameter into store procedure for bulk insert command as below



BULK INSERT [mytable] FROM
@FILE_PATH
WITH (FIELDTERMINATOR = ' ', ROWTERMINATOR = '')


howto i put @FILEPATH in above query inside store procedure ?

thanks for guidance :)

View 2 Replies View Related

SQL 2012 :: SSIS - How To Pass A Value In Parameter To Variable

Sep 17, 2015

How do you pass a value in a parameter to a variable ?

View 2 Replies View Related

Pass A Variable To A DataReader In A DataFlow Task

Feb 13, 2007

How can I pass a variable to a DataReader in a DataFlow task?

My SqlCommand for the DataReader is:
SELECT CustName, CustCode FROM Customers WHERE CustCode = '?'

The DataFlow task is nested in a ForEach loop. I confirmed that the variable is changing with each loop by using a ScriptTaks and a message box. However, the DataReader SqlCommand does not seem to be updating.

View 4 Replies View Related

Pass A Variable To A Linked Server (FoxPro) Query

Nov 22, 2004

I'm having problem with an OpenQuery statement in stored procedure, which should be run on FoxPro linked server. I'm getting either an error messages or not the result I expected. I know that the problem is with WHERE part, without this part it works.


Here is the code:
-------------------------------------
DECLARE @LastDate datetime
SELECT @LastDate = MAX(DateChaged)
FROM tblPersonel
WHERE ACTIVE IN( 'A', 'T')

1. I tried:
SELECT * FROM OPENQUERY(FoxProLink, 'SELECT ACTIVE, EmpNumber FROM tblPersonel WHERE DateChanged >=''+@LastDate+''')

This line gives me an error msg:

Could not execute query against OLE DB provider 'MSDASQL'.
[OLE/DB provider returned message: [Microsoft][ODBC Visual FoxPro Driver]Operator/operand type mismatch.]


2. I tried to use CTOD() - FOXPRO function to convert character to date.

SELECT * FROM OPENQUERY(FoxProLink, 'SELECT ACTIVE, EmpNumber FROM tblPersonel WHERE DateChanged >=CTOD(''+@LastDate+'')')

-this doesn't give any error, but doesn't limit the result set as it should.

Thanks all.

View 2 Replies View Related







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