Put Select Statement In SSIS Variable
Is it possible to add a variable in SSIS like
name of variable: myVar
Scope: Data Flow Task
Data Type: String
Value:SELECT hello FROM blah WHERE (azerty = @[User::pda]) AND (qwerty = @[User::phone])
@[User::pda] and @[User::phone] are also variables in SSIS just like the myVar I made
I know I'm doing something wrong with the data type because it's stores the whole select statement as a string
Help
Worf
View Complete Forum Thread with Replies
Related Forum Messages:
Cannot Set A Variable From A Select Statement That Contains A Variable??? Help Please
I am trying to set a vaiable from a select statement DECLARE @VALUE_KEEP NVARCHAR(120), @COLUMN_NAME NVARCHAR(120) SET @COLUMN_NAME = (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'CONTACTS' AND COLUMN_NAME = 'FIRSTNAME') SET @VALUE_KEEP = (SELECT @COLUMN_NAME FROM CONTACTS WHERE CONTACT_ID = 3) PRINT @VALUE_KEEP PRINT @COLUMN_NAME RESULTS ------------------------------------------------------------------------------------------- FirstName <-----------@VALUE_KEEP FirstName <-----------@COLUMN_NAME SELECT @COLUMN_NAME FROM CONTACTS returns: FirstName SELECT FirstName from Contacts returns: Brent How do I make this select statement work using the @COLUMN_NAME variable? Any help greatly appreciated!
View Replies !
Variable In A Select Statement
Is there anyway to use a variable to define a column in a select statement. I can put the variable in but I'm sure it will be read as a literal instead of the column. select @column_name from table
View Replies !
Variable Db Name In SP Select Statement
I need a general stored procedure so the database name will be an input parameter. I want to do a Select statemant such as: Select name from @DBName . . sysobjects where xtype - 'u' rather than a hard coded -- Select name from pubs..sysobjects where xtype = 'u' but I can't find the right way of using @DBName
View Replies !
Variable Db Name In SP Select Statement
I need a general stored procedure so the database name will be an input parameter, for example-- @DBName varchar(30) I want to do a Select statemant such as: Select name from @DBName . . sysobjects where xtype - 'u' rather than a hard coded -- Select name from pubs..sysobjects where xtype = 'u' but I can't find the right way of using @DBName Thanks, Judith
View Replies !
SQL Select Statement With A 'string' Variable?
I'm trying to add a 'change password' control to my site and seem to be having some issues. I have code that works if I statically define what user is displayed on the form, but I cant get it to detect the 'authenticated' user and show them the reset for for that ID.If I take the "+ myid" out of the select statement and just define the username statically the form works properly. Error:System.Data.SqlClient.SqlException: The column prefix 'System.Security.Principal' does not match with a table name or alias name used in the query. Here's a piece of the code that is supposed to detect the current logged in user. However, it gives the error. (some of the code may be redundant but its not causing issues that I can tell) public void InitPage() { IPrincipal p = HttpContext.Current.User; String myid = HttpContext.Current.User.ToString(); SqlServer sqlServer = new SqlServer(Util.SqlConnectionString()); DataTable dt; SqlConnection cnn = new SqlConnection(ConfigurationManager.ConnectionStrings["myconnection"].ConnectionString); SqlDataAdapter cmd1 = new SqlDataAdapter("select * from USER WHERE USER_NAME = "+ myid, cnn); DataTable UIDtable = new DataTable(); cmd1.Fill(UIDtable); User_Id.Value = UIDtable.Rows[0]["ID"].ToString(); dt = sqlServer.USER_SELECT(Util.SiteURL(Request.QueryString["Pg"].ToString()), User_Id.Value);
View Replies !
Using A Variable For Tablename In Select Statement?
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 Replies !
Variable For The Table Name In A SELECT Statement.
Hi,I'm trying to dynamically assign the table name for a SELECT statement but can't get it to work. Given below is my code: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE GetLastProjectNumber (@DeptCode varchar(20)) AS BEGIN TRANSACTION SET NOCOUNT ON DECLARE @ProjectNumber int SET @ProjectNumber = 'ProjectNumber' + REPLACE(CONVERT(char,@DeptCode),'.','') SELECT MAX(@ProjectNumber) FROM 'tbl_ProjectNumber' + REPLACE(CONVERT(char,@DeptCode),'.',''); END TRANSACTION Basically, I have a bunch of tables which were created dynamically using the code from this post and now I need to access the last row in a table that matches the supplied DeptCode. This is the error I get:Msg 102, Level 15, State 1, Procedure GetLastProjectNumber, Line 29Incorrect syntax near 'tbl_ProjectNumber'. Any help would be appreciated.Thanks.
View Replies !
Variable Not Holding Value For Select Statement
this querry below works perfect when i assign the us.UserID = 29 but i need to be able to use the @UsersMaxID variable..... when i debug all of my values are right where they need to be... even this on ((( @UsersMaxID ))) but for some reason it will not work with the next select statement... can someone make the pain go away and help me here..?? erik.. GOSET ANSI_NULLS ON GO ALTER PROCEDURE AA ASDECLARE @GenericColumn Varchar (200) DECLARE @GenericValue Varchar (200) SET @GenericColumn = 'FirstName'SET @GenericValue = 'Erik' DECLARE @SQL NVARCHAR(4000) DECLARE @UserID INT DECLARE @UsersMaxID INT DECLARE @MaxID INT declare @tempResult varchar (1000) -------------------------------------------Define the #Temporary Table----------------------------------------------CREATE TABLE #UsersTempTable ( ID int IDENTITY PRIMARY KEY, UserID [int], FirstName [varchar](30), LastName [varchar](30), CompanyName [varchar](200), Address1 [varchar](75), Address2 [varchar](75), City [varchar](75),ActiveInd [int], Zip [varchar](10), WkPhone [varchar](12),HmPhone [varchar](12), Fax [varchar](12), Email [varchar](200), Website [varchar](200), UserType [varchar](20),Title [varchar](100),Note [text], StateCD [char](2), CountryCD [char](2), CompanyPhoto [varchar](50), CompanyDescr [varchar](2000)) ---------------------------------------Fill the temp table with the Customers data-----------------------------------SET @SQL = 'INSERT INTO #UsersTempTable (UserID, FirstName, LastName, CompanyName, Address1, Address2, City, ActiveInd, Zip, WkPhone, HmPhone,Fax, Email, Website, UserType, Title, Note, StateCD, CountryCD, CompanyPhoto, CompanyDescr) Select Users.UserID, Users.FirstName,Users.LastName, Users.CompanyName, Users.Address1, Users.Address2, Users.City, Users.ActiveInd, Users.Zip, Users.WkPhone, Users.HmPhone,Users.Fax,Users.Email,Users.Website, Users.UserType,Users.Title, Users.Note,Users.StateCD, Users.CountryCD,Users.CompanyPhoto,Users.CompanyDescr FROM USERS WHERE ' + @GenericColumn +' = ''' + @GenericValue + '''' EXEC sp_executesql @SQL SET @MaxID = (SELECT MAX(ID) FROM #UsersTempTable)SET @UsersMaxID = (SELECT UserID From #UsersTempTable WHERE ID = @MaxID) SELECT SpecialtyName FROM Specialty s INNER JOIN UserSpecialty us ON s.SpecialtyCD = us.SpecialtyCD WHERE us.UserID = 29 SELECT * FROM #UsersTempTable ==========================================================================================SET @UsersMaxID = (SELECT UserID From #UsersTempTable WHERE ID = @MaxID) SELECT SpecialtyName FROM Specialty s INNER JOIN UserSpecialty us ON s.SpecialtyCD = us.SpecialtyCD WHERE us.UserID = 29 <<<<<<<<<<<<<<<<< i need @UserMaxID ........RIGHT HERE
View Replies !
Use A Variable Along With The FROM Clause In SELECT Statement
I have a table 'table_list' which contains two columns, table_name and a record_count. This table stores a list of tables and their corresponding record counts. What I am trying to do is, to be able to write a select statement, that can read each table name in the 'table_name' column, execute a select count(*) for the same, and update its record_count with the result of select count(*). This is the code in my procedure.. DECLARE @tab_list CURSOR set @tab_list = CURSOR FOR select * from table_list OPEN @tab_list DECLARE @tab_name varchar(256) DECLARE @rec_cnt int FETCH NEXT FROM @tab_list INTO @tab_name, @rec_cnt select count(*) from @tab_name This select is looping around along with FETCH till all the table names are exhausted and their counts are updated from the cursor back into the table. Problem is that, I am not able to use select count(*) from @tab_name, and its not accepting a variable there. Please help me to construct the select statement that is similiar to x=<table name> select * from x where x is a variable and the table name gets substituted. what is the syntax for it ?
View Replies !
Return Variable Name As Part Of Select Statement.
hey all, I have the following query: ALTER PROCEDURE [dbo].[sp_SelectMostRecentArticle] AS BEGIN DECLARE @article_id INT SELECT @article_id = ( SELECT TOP 1 article_id FROM article ORDER BY article_id DESC ) DECLARE @comment_count INT SELECT @comment_count = ( SELECT COUNT(comment_id) FROM comment JOIN article ON article_id = comment_article_id GROUP BY article_id HAVING article_id = @article_id ) SELECT TOP 1 article_id, article_author_id, article_title, article_body, article_post_date, article_edit_date, article_status, article_author_id article_author_ip, author_display_name, category_id, category_name--, comment_count AS @comment_count FROM article JOIN author ON author_id = article_author_id JOIN category ON category_id = article_category_id GROUP BY article_id, article_title, article_body, article_post_date, article_edit_date, article_status, article_author_ip,article_author_id, author_display_name, category_id, category_name HAVING article_id = @article_id END GO as you can see, im trying to return a comment_count value, but the only way I can do this is by defining the variable. I have had to do it this way, because I cannot say COUNT(comment.comment_id) AS comment_count or it returns an error that it cant reference the comment.comment_id. But when change it to FROM article, comment; I get errors about the article_author_id and article_comment_id. And i cant add a join, because it would return the amount of rows of the comment... unless someone could help with what i Just decribed (as i would prefer to do it this way), how would i return the variable value as part of the select statement? Cheers
View Replies !
Storing Results Of Select Statement In @variable
I'm new to sql stored procedures but I would like to store the results of an sql statement in a variable such as: SET @value = select max(price) from product but this does not work, can someone tell me how I would go about in storing the results in a variable. @value is declared as int Thanks in advance, Sharp_At_C
View Replies !
How To Assign The SELECT Statement Output To A Local Variable?
In my program i have function that will get one value from Database. Here i want to assign the output of the sql query to a local variable. Its like select emp_id into Num from emp where emp_roll=222; here NUM is local variable which was declared in my program. Is it correct.? can anyone please guide me..?
View Replies !
Unable To Create Variable Select Statement In For Each Loop
What I'm trying to do is this; I have a table with Year , Account and Amount as fields. I want to SELECT Year, Account, sum(Amount) AS Amt FROM GLTable WHERE Year <= varYear varYear being a variable which is each year from a query SELECT Distinct Year FROM GLTable My thought was that I would need to pass a variable into a select statement which then would be used as the source in my Data Flow Task. What I have done is to defined two variables as follows Name: varYear (this will hold the year) Scope: Package Data type: String Name:vSQL (This will hold a SQL statement using the varYear) Scope: Package Data type: String Value: "SELECT Year, Account, sum(Amount) AS Amount FROM GLTable WHERE Year <=" + @[User::varYear] I've created a SQL Task as follows Result set: Full Result Set Connection Type: OLE DB SQL Statement: SELECT DISTINCT Year FROM GLTable Result Name: 0 Variable Name: User::varYear Next I created a For Each Loop container with the following parameters Enumerator: Foreach ADO Enumerator ADO Object source Variable: User::varYear Enumeration Mode: Rows in First Table I then created a Data Flow Task in the Foreach Loop Container and as the source used OLE DB Source as follows Data Access Mode: SQL Command from Variable Variable Name: User::varYear However this returns a couple of errors "Statement(s) could not be prepared." and "Incorrect syntax near '='.". I'm not sure what is wrong or if this is the right way to accomplish what I am trying to do. I got this from another thread "Passing Variables" started 15 Nov 2005. Any help would be most appreciated. Regards, Bill
View Replies !
Combing In A Cursor, A Select Statement With The WHERE Clause Stored In A Variable
Hi I am ramesh here from go-events.com I am using sql mail to send out emails to my mailing list I have difficulty combining a select statement with a where clause stored in a variable inside a cursor The users select the mail content and frequency of delivery and i deliver the mail I use lots of queries and a stored procedure to retrieve thier preferences. In the end i use a cursor to send out mails to each of them. Because my query is dynamic, the where clause of my select statement is stored in a variable. I have the following code that does not work For example DECLARE overdue3 CURSOR LOCAL FORWARD_ONLY FOR SELECT DISTINCT Events.E_Name, Events.E_SDate, Events.E_City, Events.E_ID FROM Events, IndustryEvents + @sqlquery2 OPEN overdue3 I get an error message at the '+' sign which says, cannot use empty object or column names, use a single space if necessary How do I combine the select statement with the where clause? Help me...I need help urgently
View Replies !
Random Selection From Table Variable In Subquery As A Column In Select Statement
Consider the below code: I am trying to find a way so that my select statement (which will actually be used to insert records) can randomly place values in the Source and Type columns that it selects from a list which in this case is records in a table variable. I dont really want to perform the insert inside a loop since the production version will work with millions of records. Anyone have any suggestions of how to change the subqueries that constitute these columns so that they are randomized? SET NOCOUNT ON Declare @RandomRecordCount as int, @Counter as int Select @RandomRecordCount = 1000 Declare @Type table (Name nvarchar(200) NOT NULL) Declare @Source table (Name nvarchar(200) NOT NULL) Declare @Users table (Name nvarchar(200) NOT NULL) Declare @NumericBase table (Number int not null) Set @Counter = 0 while @Counter < @RandomRecordCount begin Insert into @NumericBase(Number)Values(@Counter) set @Counter = @Counter + 1 end Insert into @Type(Name) Select 'Type: Buick' UNION ALL Select 'Type: Cadillac' UNION ALL Select 'Type: Chevrolet' UNION ALL Select 'Type: GMC' Insert into @Source(Name) Select 'Source: Japan' UNION ALL Select 'Source: China' UNION ALL Select 'Source: Spain' UNION ALL Select 'Source: India' UNION ALL Select 'Source: USA' Insert into @Users(Name) Select 'keith' UNION ALL Select 'kevin' UNION ALL Select 'chris' UNION ALL Select 'chad' UNION ALL Select 'brian' select 1 ProviderId, -- static value '' Identifier, '' ClassificationCode, (select TOP 1 Name from @Source order by newid()) Source, (select TOP 1 Name from @Type order by newid()) Type from @NumericBase SET NOCOUNT OFF
View Replies !
Which SSIS Dataflow Transformations Will Accomplish This Select Statement?
I'm trying to find if there is a combination of dataflow transformations that will produce the following result SELECT period, project, task, employee = CASE when empid in (SELECT DISTINCT empid FROM EmpTable) then empid else 'Deleted Employee' end FROM ProjectTable I know I can create a dataflow task with this query as a data source and then send it to a destination, but I was wondering if that is the best way to do it or if there was a better way to do this using the data transformations available in SSIS. Any insight would be most appreciated. Regards, Bill Webster
View Replies !
Select Statement Within Select Statement Makes My Query Slow....
Hello... im having a problem with my query optimization.... I have a query that looks like this: SELECT * FROM table1 WHERE location_id IN (SELECT location_id from location_table WHERE account_id = 998) it produces my desired data but it takes 3 minutes to run the query... is there any way to make this faster?... thank you so much...
View Replies !
SSIS Script Task Alters Package Variable, But Variable Does Not Change.
I'm working on an SSIS package that uses a vb.net script to grab some XML from a webservice (I'd explain why I'm not using a web service task here, but I'd just get angry), and I wish to then assign the XML string to a package variable which then gets sent along to a DataFlow Task that contains an XML Source that points at said variable. when I copy the XML string into the variable value in the script, if do a quickwatch on the variable (as in Dts.Variable("MyXML").value) it looks as though the new value has been copied to the variable, but when I step out of that task and look at the package explorer the variable is its original value. I think the problem is that the dataflow XML source has a lock on the variable and so the script task isn't affecting it. Does anyone have any experience with this kind of problem, or know a workaround?
View Replies !
Passing A SSIS Global Variable To A Declared Variable In A Query In SQL Task
I have a SQL Task that updates running totals on a record inserted using a Data Flow Task. The package runs without error, but the actual row does not calculate the running totals. I suspect that the inserted record is not committed until the package completes and the SQL Task is seeing the previous record as the current. Here is the code in the SQL Task: DECLARE @DV INT; SET @DV = (SELECT MAX(DateValue) FROM tblTG); DECLARE @PV INT; SET @PV = @DV - 1; I've not been successful in passing a SSIS global variable to a declared parameter, but is it possible to do this: DECLARE @DV INT; SET @DV = ?; DECLARE @PV INT; SET @PV = @DV - 1; I have almost 50 references to these parameters in the query so a substitution would be helpful. Dan
View Replies !
SSIS Error Reading XML Loaded Into Variable With XML Source Using XML File From Variable.
Hi I am getting the following error when trying to extract data using XML source " Error: 0xC02090D0 at Data Flow Task - Load XML data to database, XML Source - Load XML data from variable [15893]: The component "XML Source - Load XML data from variable" (15893) was unable to read the XML data." What I have done is read XML data from file and stripped out DTD contents using XSLT transformation in an XML task. The XML file is loaded into a string variable called XMLProduct. Next I have a XML task to validate the variable contents against the XSD, which works. Then I am trying to load the data using XML Source within a Data Flow task, which is when the error occurs. If before I enter the Data Flow and use a script task to read the content of XMLProduct variable and save that to a file then point XML Source task to the file it works ok. I need to iterate through a whole bunch of XML files so reading it into a variable would be the preferred option if it worked of course. Any help would be hugely appreciated. Edit --- I have saved the output from the XSLT transformation XML Task to file and not a variable then read that in using the XML source task and it worked. It would be nice to get the variable method working though as it would save having to create another file. It is a work around at the moment though.
View Replies !
SSIS: Problem Mapping Global Variables To Stored Procedure. Can't Pass One Variable To Sp And Return Another Variable From Sp.
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 Replies !
Multiple Tables Used In Select Statement Makes My Update Statement Not Work?
I am currently having this problem with gridview and detailview. When I drag either onto the page and set my select statement to pick from one table and then update that data through the gridview (lets say), the update works perfectly. My problem is that the table I am pulling data from is mainly foreign keys. So in order to hide the number values of the foreign keys, I select the string value columns from the tables that contain the primary keys. I then use INNER JOIN in my SELECT so that I only get the data that pertains to the user I am looking to list and edit. I run the "test query" and everything I need shows up as I want it. I then go back to the gridview and change the fields which are foreign keys to templates. When I edit the templates I bind the field that contains the string value of the given foreign key to the template. This works great, because now the user will see string representation instead of the ID numbers that coinside with the string value. So I run my webpage and everything show up as I want it to, all the data is correct and I get no errors. I then click edit (as I have checked the "enable editing" box) and the gridview changes to edit mode. I make my changes and then select "update." When the page refreshes, and the gridview returns, the data is not updated and the original data is shown. I am sorry for so much typing, but I want to be as clear as possible with what I am doing. The only thing I can see being the issue is that when I setup my SELECT and FROM to contain fields from multiple tables, the UPDATE then does not work. When I remove all of my JOIN's and go back to foreign keys and one table the update works again. Below is what I have for my SQL statements:------------------------------------------------------------------------------------------------------------------------------------- SELECT:SELECT People.FirstName, People.LastName, People.FullName, People.PropertyID, People.InviteTypeID, People.RSVP, People.Wheelchair, Property.[House/Day Hab], InviteType.InviteTypeName FROM (InviteType INNER JOIN (Property INNER JOIN People ON Property.PropertyID = People.PropertyID) ON InviteType.InviteTypeID = People.InviteTypeID) WHERE (People.PersonID = ?)UPDATE:UPDATE [People] SET [FirstName] = ?, [LastName] = ?, [FullName] = ?, [PropertyID] = ?, [InviteTypeID] = ?, [RSVP] = ?, [Wheelchair] = ? WHERE [PersonID] = ? ---------------------------------------------------------------------------------------------------------------------------------------The only fields I want to update are in [People]. My WHERE is based on a control that I use to select a person from a drop down list. If I run the test query for the update while setting up my data source the query will update the record in the database. It is when I try to make the update from the gridview that the data is not changed. If anything is not clear please let me know and I will clarify as much as I can. This is my first project using ASP and working with databases so I am completely learning as I go. I took some database courses in college but I have never interacted with them with a web based front end. Any help will be greatly appreciated.Thank you in advance for any time, help, and/or advice you can give.Brian
View Replies !
Using Conditional Statement In Stored Prcodure To Build Select Statement
hiI need to write a stored procedure that takes input parameters,andaccording to these parameters the retrieved fields in a selectstatement are chosen.what i need to know is how to make the fields of the select statementconditional,taking in consideration that it is more than one fieldaddedfor exampleSQLStmt="select"if param1 thenSQLStmt=SQLStmt+ field1end ifif param2 thenSQLStmt=SQLStmt+ field2end if
View Replies !
TSQL - Use ORDER BY Statement Without Insertin The Field Name Into The SELECT Statement
Hi guys, I have the query below (running okay): Code Block SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02' FROM myTables WHERE Conditions are true ORDER BY Field01 The results are just as I need: Field01 Field02 ------------- ---------------------- 192473 8461760 192474 22810 Because other reasons. I need to modify that query to: Code Block SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02' INTO AuxiliaryTable FROM myTables WHERE Conditions are true ORDER BY Field01 SELECT DISTINCT [Field02] FROM AuxTable The the results are: Field02 ---------------------- 22810 8461760 And what I need is (without showing any other field): Field02 ---------------------- 8461760 22810 Is there any good suggestion? Thanks in advance for any help, Aldo.
View Replies !
How To Write Select Statement Inside CASE Statement ?
Hello friends, I want to use select statement in a CASE inside procedure. can I do it? of yes then how can i do it ? following part of the procedure clears my requirement. SELECT E.EmployeeID, CASE E.EmployeeType WHEN 1 THEN select * from Tbl1 WHEN 2 THEN select * from Tbl2 WHEN 3 THEN select * from Tbl3 END FROM EMPLOYEE E can any one help me in this? please give me a sample query. Thanks and Regards, Kiran Suthar
View Replies !
Help With Delete Statement/converting This Select Statement.
I have 3 tables, with this relation: tblChats.WebsiteID = tblWebsite.ID tblWebsite.AccountID = tblAccount.ID I need to delete rows within tblChats where tblChats.StartTime - GETDATE() < 180 and where they are apart of @AccountID. I have this select statement that works fine, but I am having trouble converting it to a delete statement: SELECT * FROM tblChats c LEFT JOIN tblWebsites sites ON sites.ID = c.WebsiteID LEFT JOIN tblAccounts accounts on accounts.ID = sites.AccountID WHERE accounts.ID = 16 AND GETDATE() - c.StartTime > 180
View Replies !
Select Statement Problem - Group By Maybe Nested Select?
Hey guys i have a stock table and a stock type table and what i would like to do is say for every different piece of stock find out how many are available The two tables are like thisstockIDconsumableIDstockAvailableconsumableIDconsumableName So i want to,Select every consumableName in my table and then group all the stock by the consumable ID with some form of total where stockavailable = 1I should then end up with a table like thisEpson T001 - Available 6Epson T002 - Available 0Epson T003 - Available 4If anyone can help me i would be very appreciative. If you want excact table names etc then i can put that here but for now i thought i would ask how you would do it and then give it a go myself.ThanksMatt
View Replies !
SQL Select Statement To Select The Last Ten Records Posted
SELECT Top 10 Name, Contact AS DCC, DateAdded AS DateTimeFROM NameTaORDER BY DateAdded DESC I'm trying to right a sql statement for a gridview, I want to see the last ten records added to the to the database. As you know each day someone could add one or two records, how can I write it show the last 10 records entered.
View Replies !
Using C# Variable With SQL Statement
Greetings everyone, I am trying to use a c# string with an SQL statement in a data adapter (.NET 03) The code works fine and I have a variable called : string test = ..... that takes the needed values. I just need to implement this string in the sql statement. I tried adding this to my query but I only got an empty row: WHERE (login = '" & test & "') WHERE (login = '" + test + "') any ideas? PS: If I change to something like WHERE (login = 'abcdef') I get a result meaning there's something wrong with the way I am putting the variable in the sql query. Again, I am not putting the string in a normal query in my .cs code. this is happening by right clicking the data adapter and configuring the sql statement in the designer window THANKS!
View Replies !
Variable In An Sql Statement
Hi, I've created an sql statement: select * from fin_installment where key_construction = (select ser_construction from fin_construction where key_contract = ' " & variable & " ') order by int_serial which is in an Dataset's TableAdapter. This variable receives its value during the form init and it is an integer. When I start the page the folowing error message is displayed: " An error has occurred during report processing. Exception has been thrown by the target of an invocation. Conversion failed when converting the varchar value ' " & azonosito & " ' to data type int. " So my question is that how can I use variables in sql statement in dataset?
View Replies !
USE Statement With Variable
Hi,I am doing a really simple test with SQL Server 7.0:Using the Query AnalyzerLogged as saLocated in master database#1 USE Test#2 EXEC('USE Test')#1 => the database context is switched to Test#2 => the database is NOT switched???
View Replies !
USE Statement With A Variable?
I'm having some trouble modifing a script to save me tons of work. The script if from Microsoft, and it is used as step 3 in a 6 step process to move MS Great Plains users from one server to another. Anyway, the script runs on only 1 company database at a time, and for most Great Plains environments there would only be 1 or 2 company DBs. But I am administering in an ASP environment and we have over 30 company DBs to move. So, I though I would adapt thier script to iterate over each company DB to do the work (rather than creating 30 separate scripts). So I wrapped their loop with my loop to do the iteration. The problem is that T-SQL will not let me use a variable in a USE statement. I've tried to remove the USE statements, but that added a lot of complexity in the internal loop. What is the best way to do this? Here is the modified code: /* ** Drop_Users_Company.sql ** ** This script will remove all users from the DYNGRP in the company database ** specified. It will then drop the DYNGRP and readd the DYNGRP to the company. ** It will then add all users back to the DYNGRP based on the SY60100 table. ** NOTE: You will need to replace %Companydb% with the company database ** name. */ /* Instead of replacing %Companydb% (in each USE statement) with the name of the single company database that this script is supposed to work on, I've added @cCompany to hold the company DB name through each iteration of the outside cursor/while loop. */ declare @cCompany sysname/* ADDED BY ME FOR THE OUTSIDE LOOP */ declare @cStatement varchar(255)/* Misc exec string */ declare @DynDB varchar(15)/* DB Name exec string */ declare @DYNGRPgid int/* Id of DYNGRP group */ /* ** Loop through all company databases, emptying the DYNGRP group. */ SET QUOTED_IDENTIFIER OFF use DYNAMICS /* Select all of the Great Plains database names from the DB_Upgrade table, where the DB names are conviently stored */ declare C_cursor CURSOR for select db_name from DYNAMICS..DB_Upgrade where db_name not in ('DYNAMICS') OPEN C_cursor FETCH NEXT FROM C_cursor INTO @cCompany WHILE (@@FETCH_STATUS <> -1) begin use @cCompany select @DYNGRPgid = (select gid from sysusers where name = 'DYNGRP') declare G_cursor CURSOR for select "sp_dropuser [" + name+"]" from sysusers where gid = @DYNGRPgid and name <> 'DYNGRP' set nocount on OPEN G_cursor FETCH NEXT FROM G_cursor INTO @cStatement WHILE (@@FETCH_STATUS <> -1) begin EXEC (@cStatement) FETCH NEXT FROM G_cursor INTO @cStatement end DEALLOCATE G_cursor /* ** Do not delete the group to attempt to preserve the permissions already ** granted to it. */ use @cCompany if exists (select gid from sysusers where name = 'DYNGRP') begin exec sp_dropgroup DYNGRP end /* ** Recreate the DYNGRP group in all company databases. */ use @cCompany if not exists (select name from sysusers where name = 'DYNGRP') begin exec ("sp_addgroup DYNGRP") end end DEALLOCATE C_cursor ______________________________________ Thanks for any help you have.
View Replies !
Using Variable In LIKE Statement
Hi, I am trying to use a variable inside a LIKE statement, but it is not working as expected. It will not give a error, but it shows no results while it does show results if I replace the variable with the normal string within the LIKE statement. Here is my code: Code: -- this example returns results SELECT whatever FROM mytable WHERE whatever LIKE 'blah%'; Code: -- this example returns no results DECLARE @test VARCHAR; SET @test='blah%'; SELECT whatever FROM mytable WHERE whatever LIKE @test; Any ideas why the version using the variable would not work? Patrick
View Replies !
SQL Statement With Variable.
Hi , I am testing a very simple query that use variable for sort direction and sort expression DECLARE @SortExp nvarchar(256), @SortDir nvarchar(10) Set @SortExp = 'curTime' Set @SortDir = 'DESC' Select * from table where recID < 20 order by @SortExp @SortDir and i got this error... The SELECT item identified by the ORDER BY number 1 contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name. Is there anyway to do this task. Thanks Ddee
View Replies !
SQL Statement In Variable
Hello Everyone, I wanted to pass a SQL statement thru a variable, and use that variable in my source component. SELECT CLINIC_SUK, CLINIC_CODE, CLINIC_DESC, CLINIC_ARABIC, Load_DT FROM DIM_CLINIC where load_dt > ? I had created a variable with my SQL statement and mapped that variable in my source component. Its giving me some error. Parameter Information cannot be derived from SQL statement. Set parameter information before preparing command. Please do inform me about the solution for having a parameter in my source SQL Statement.
View Replies !
My Variable In Sql Statement
Declare @MyCode nvarchar(20); Set @MyCode='ABC' set @int_rowcount=(SELECT count(hoten) FROM @MyCode) I run it but still errors ! How can i implement above statement ? Thank you very much !
View Replies !
Declaring A Table Variable Within A Select Table Joined To Other Select Tables In Query
Hello, I hope someone can answer this, I'm not even sure where to start looking for documentation on this. The SQL query I'm referencing is included at the bottom of this post. I have a query with 3 select statements joined together like tables. It works great, except for the fact that I need to declare a variable and make it a table within two of those 3. The example is below. You'll see that I have three select statements made into tables A, B, and C, and that table A has a variable @years, which is a table. This works when I just run table A by itself, but when I execute the entire query, I get an error about the "declare" keyword, and then some other errors near the word "as" and the ")" character. These are some of those errors that I find pretty meaningless that just mean I've really thrown something off. So, am I not allowed to declare a variable within these SELECT tables that I'm creating and joining? Thanks in advance, Andy Select * from ( declare @years table (years int); insert into @years select CASE WHEN month(getdate()) in (1) THEN year(getdate())-1 WHEN month(getdate()) in (2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12) THEN year(getdate()) END select u.fullname , sum(tx.Dm_Time) LastMonthBillhours , sum(tx.Dm_Time)/((select dm_billabledays from dm_billabledays where Dm_Month = Month(GetDate()))*8) lasmosbillingpercentage from Dm_TimeEntry tx join systemuserbase u on (tx.owninguser = u.systemuserid) where Month(tx.Dm_Date) = Month(getdate())-1 and year(dm_date) = (select years from @years) and tx.dm_billable = 1 group by u.fullname ) as A left outer join (select u.FullName , sum(tx.Dm_Time) Billhours , ((sum(tx.Dm_Time)) / ((day(getdate()) * ((5.0)/(7.0))) * 8)) perc from Dm_TimeEntry tx join systemuserbase u on (tx.owninguser = u.systemuserid) where tx.Dm_Billable = '1' and month(tx.Dm_Date) = month(GetDate()) and year(tx.Dm_Date) = year(GetDate()) group by u.fullname) as B on A.Fullname = B.Fullname Left Outer Join ( select u.fullname , sum(tx.Dm_Time) TwomosagoBillhours , sum(tx.Dm_Time)/((select dm_billabledays from dm_billabledays where Dm_Month = Month(GetDate()))*8) twomosagobillingpercentage from Dm_TimeEntry tx join systemuserbase u on (tx.owninguser = u.systemuserid) where Month(tx.Dm_Date) = Month(getdate())-2 group by u.fullname ) as C on A.Fullname = C.Fullname
View Replies !
Using Select Statement Result In If Statement Please Help
Hello How can i say this I would like my if statement to say: if what the client types in Form1.Cust is = to the Select Statement which should be running off form1.Cust then show the Cust otherwise INVALID CUSTOMER NUMBER .here is my if statement. <% If Request.Form("Form1.Cust") = Request.QueryString("RsCustNo") Then%> <%=Request.Params("Cust") %> <% Else %> <p>INVALID CUSTOMER NUMBER</p> <% End If%> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:RsCustNo %>" ProviderName="<%$ ConnectionStrings:RsCustNo.ProviderName %>" SelectCommand="SELECT [CU_CUST_NUM] FROM [CUSTOMER] WHERE ([CU_CUST_NUM] = ?)"> <SelectParameters> <asp:FormParameter FormField="Cust" Name="CU_CUST_NUM" Type="String" /> </SelectParameters> </asp:SqlDataSource>any help would be appreciated
View Replies !
If STATEMENT Within Select Statement Syntax
Hi, I am a newbie to this site and hope someone can help.... I have a select statement which I would like to create an extra column and put an if statement in it.... Current syntax is: if(TL_flag= '1', "yes") as [Trial Leave] it is coming up with an error.... I can use Select case but I should not need to as this should work? Any ideas?
View Replies !
Passing Variable To Sql Statement
could anyone please help me to resolve this issue? here's my sql query which retrieve last 3 month data t.execute(SELECT * tbl1 where nmonth >= datepart(mm,DATEADD(month, -3, getdate())) or nmonth <=datepart(mm,getdate()) and empno='"+emppip+"'") now instead of passing 3 in this query(datepart(mm,DATEADD(month, -3, getdate())) ) i need to pass a variable to retrieve data based on user requirements. i tried this way, dim mno as n mno=4 t.execute(SELECT * tbl1 where nmonth >= datepart(mm,DATEADD(month, -'"+mno+"', getdate())) or nmonth <=datepart(mm,getdate()) and empno='"+emppip+"'") its not working. can i achieve this using stored procedure? or can i directly pass a variable to sql synatax? thanks for any help
View Replies !
Can Variable Be Used In SQL UPDATE Statement In VB.NET
Hy, i have this problem in vb.net: I must use a variable in SQL UPDATE statement, after SET statement, and i'm getting error. This is that line of code: Dim variable_name As StringDim variable As Integer Dim sqlString As String = ("UPDATE table_name SET " variable_name " = " & variable & " WHERE UserID = '" & UserID & "'")Dim cmdSqlCommand As New SqlCommand(sqlString, conConnetion) cmdSqlCommand.ExecuteNonQuery() When I don't use a variable after SET statement, everything work fine. This code works fine: Dim variable As Integer Dim sqlString As String = ("UPDATE table_name SET column_name = " & variable & " WHERE UserID = '" & UserID & "'")Dim cmdSqlCommand As New SqlCommand(sqlString, conConnetion) cmdSqlCommand.ExecuteNonQuery() Please, if someone can help me in this...thanks..
View Replies !
Use A Table Name As A Variable In The From Statement
I'm curious if anyone knows the correct way to do this pseudo-statement correctly? I want to create a stored procedure in which I send it the table name of the table I want to query.declare @tableName varchar(500)set @tableName = 'PortfolioPreferenceOwnership' select * from @tableName
View Replies !
Want To Use A Variable In A 'use DB' Statement In SQL Script
Hi, I want to use a variable in a 'use' statement... but, I cannot figure out the syntax, nor do I know if it is possible... Here is an example SQL script: /*-----------------------------------------------------*/ DECLARE @DataBase varchar(60) --Declare cursor for all DBs except master, MSDB, Model, tempdb DECLARE curdb CURSOR for select name from master..sysdatabases where name not in ('master', 'MSDB', 'Model','tempdb')for read only --Open and perform initial fetch open curdb fetch curdb into @DataBase --While there are databases to process, process each DB While @@fetch_status = 0 PRINT @DataBase use + ' ' + @database --or, use @database fetch curdb into @DataBase end /*------------------------------*/ Thanks, Michael
View Replies !
USE @dbname (Use Statement With Variable)
Hi I need to run a stored procedure on each database in my SQL server. I want to have a loop to go through each db. Is there a way I can run 'Use @dbname', I tried Execute and sp_executesql but it didn't work. I want to execute the SP withing each db. Thanks
View Replies !
Variable In 'kill' Statement
I'm trying to kill all processes for a specific login name. My code is as follows: declare @spid int select @spid=(select spid from master.dbo.sysprocesses where loginame = 'name of user') kill @spid What I get is: Server: Msg 170, level 15, State 1, Line 3 Line 3: Incorrect syntax near '@spid'. Where have I gone wrong? Thanks.
View Replies !
|
TRACKER
