Cannot Set A Variable From A Select Statement That Contains A Variable??? Help Please
Oct 4, 2006
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 2 Replies
ADVERTISEMENT
Sep 6, 2006
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 2 Replies
View Related
Apr 23, 2007
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 3 Replies
View Related
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
Apr 16, 2008
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 1 Replies
View Related
Dec 7, 2005
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 1 Replies
View Related
Dec 28, 2004
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 7 Replies
View Related
Nov 10, 2006
How do I use a variable to specify the column name in a select statement?
declare @columnName <type>
set @columnName='ID'
select @columnName from Table1
View 8 Replies
View Related
Sep 23, 2006
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 8 Replies
View Related
Dec 19, 2003
Example:
Select icount + 1 as icount from table
or
Select counter() as icount from table
The above is wrong ...just a sample to show what I am trying to accomplish.
Is there a function in SQL statement? Thanks.
View 2 Replies
View Related
Mar 9, 2008
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 6 Replies
View Related
Nov 12, 2013
I am trying to figure out a way to retrieve a field value and assign it to a local variable with out destroying the whole structure of my T-SQL statement.
Here is the code:
DECLARE @AVERAGE_WHOLESALE_PRICE VARCHAR(20)
DECLARE @ORDERBY VARCHAR(20)
SELECT TOP 1 @AVERAGE_WHOLESALE_PRICE = P.NPT_PRICEX,
CASE NPT_TYPE
WHEN '07' THEN 1
WHEN '09' THEN 2
[Code] ....
The error message is
Msg 141, Level 15, State 1, Line 3
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.
How to modify this statement?
View 8 Replies
View Related
Feb 11, 2008
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 1 Replies
View Related
May 21, 2006
I have concatenated a long Select Statement and assigned it to a variable. (i.e.)
@a = Select * from foo ---Obviously mine is much more robust
How do I execute @a to actually Select * from foo?
View 3 Replies
View Related
Apr 21, 2015
Everything about this query works except I'm trying to capture the @companyid (which is a variable) into a column in my table via my select statement.
My error is Invalid column name 'A113', etc. However it is the A113 I'm trying to insert into the first column of the table SAP_GLsummary
-- retrieves a list of gl balances from all companies
truncate table sap_glsummary
declare @companyID char(6)
declare c_company cursor for
select INTERID from dbo.GP_Interid
open c_company fetch next from c_company into @companyID
[Code] .....
View 4 Replies
View Related
May 7, 2008
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 7 Replies
View Related
May 6, 2015
Is it possible to have an entire sql select statement as the input variable to a stored procedure? I want the stored procedure to execute the select statement.
ie.
exec sp_SomeFunc 'select * from table1 where id=1'
It may sound weird, but I have my reason for wanting to do it this way. Is this possible? if so, how do I implement this inside the stored procedure?
View 4 Replies
View Related
Apr 24, 2007
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 5 Replies
View Related
Nov 6, 2007
I'm sure this is a very simple piece of code, but I'm having trouble understanding how to do this.
First I have a database with three columns
ContactID
View 1 Replies
View Related
Mar 28, 2000
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 1 Replies
View Related
May 26, 2015
I tend to learn from example and am used to powershell. If for instance in powershell I wanted to get-something and store it in a variable I could, then use it again in the same code. In this example of a table order items where there are order_num, quantity and item_prices how could I declare ordertotal as a variable then instead of repeating it again at "having sum", instead use the variable in its place?
Any example of such a use of a variable that still lets me select the order_num, ordertotal and group them etc? I hope to simply replace in the "having section" the agg function with "ordertotal" which bombs out.
select order_num, sum(quantity*item_price) as ordertotal
from orderitems
group by order_num
having sum(quantity*item_price) >=50
order by ordertotal;
View 11 Replies
View Related
Nov 7, 2007
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 14 Replies
View Related
Oct 25, 2006
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 1 Replies
View Related
Mar 6, 2008
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 4 Replies
View Related
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
Oct 15, 2007
I am in the middle of taking course 2073B €“ Programming a Microsoft SQL Server 2000 Database. I noticed that in Module9: Implementing User-Defined Functions exercise 2, page 25; step 2 is not returning the correct answer.
Select employeeid,name,title,mgremployeeid from dbo.fn_findreports(2)
It returns manager id for both 2 and 5 and I think it should just return the results only for manager id 2. The query results for step 1 is correct but not for step 2.
Somewhere in the code I think it should compare the inemployeeid with the previous inemployeeid, and then add a counter. If the two inemployeeid are not the same then reset the counter. Then maybe add an if statement or a case statement. Can you help with the logic? Thanks!
Here is the code of the function in the book:
/*
** fn_FindReports.sql
**
** This multi-statement table-valued user-defined
** function takes an EmplyeeID number as its parameter
** and provides information about all employees who
** report to that person.
*/
USE ClassNorthwind
GO
/*
** As a multi-statement table-valued user-defined
** function it starts with the function name,
** input parameter definition and defines the output
** table.
*/
CREATE FUNCTION fn_FindReports (@InEmployeeID char(5))
RETURNS @reports TABLE
(EmployeeID char(5) PRIMARY KEY,
Name nvarchar(40) NOT NULL,
Title nvarchar(30),
MgrEmployeeID int,
processed tinyint default 0)
-- Returns a result set that lists all the employees who
-- report to a given employee directly or indirectly
AS
BEGIN
DECLARE @RowsAdded int
-- Initialize @reports with direct reports of the given employee
INSERT @reports
SELECT EmployeeID, Name = FirstName + ' ' + LastName, Title, ReportsTo, 0
FROM EMPLOYEES
WHERE ReportsTo = @InEmployeeID
SET @RowsAdded = @@rowcount
-- While new employees were added in the previous iteration
WHILE @RowsAdded > 0
BEGIN
-- Mark all employee records whose direct reports are going to be
-- found in this iteration
UPDATE @reports
SET processed = 1
WHERE processed = 0
-- Insert employees who report to employees marked 1
INSERT @reports
SELECT e.EmployeeID, Name = FirstName + ' ' + LastName , e.Title, e.ReportsTo, 0
FROM employees e, @reports r
WHERE e.ReportsTo = r.EmployeeID
AND r.processed = 1
SET @RowsAdded = @@rowcount
-- Mark all employee records whose direct reports have been
-- found in this iteration
UPDATE @reports
SET processed = 2
WHERE processed = 1
END
RETURN -- Provides the value of @reports as the result
END
GO
View 1 Replies
View Related
Nov 12, 2007
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 8 Replies
View Related
Dec 14, 2005
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 3 Replies
View Related
Dec 13, 2007
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 3 Replies
View Related
Jun 17, 2004
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 1 Replies
View Related
Aug 11, 2006
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 6 Replies
View Related
Jul 20, 2005
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 2 Replies
View Related
