I have a package that I migrated from DTS. There are several "execute SQL" tasks that take an Input parameter of "myVariable", which is a string defined at the package level and has a default value assigned to it.
However, the package is throwing this error:
Error 1 Validation error. Err LogComletion : Failed to lock variable "myVariable" for read access with error 0xC001F032 "The variable name is ambiguous because multiple variables with this name exist in different namespaces. Specify namespace-qualified name to prevent ambiguity.".
How come I cannot use this variable when defined at the package level? If I move the variable to the task level, it works fine, but I want to re-use the variable in several places in the package.
Dear experts, while i'm trying to insert data , i got error like Ambiguous column name...that is related to joins the query is INSERT INTO MATABLE044 SELECT newid(), ledger.COLUMN002, wh.COLUMN001, Loc.COLUMN005, ledger.COLUMN008, ledger.COLUMN009, ledger.COLUMN010, ledger.COLUMN011,0, 1-1-1900, ledger.COLUMN006, ledger.COLUMN007, ledger.COLUMN014, 'Y', ledger.column016,ledger.COLUMN020, ledger.COLUMN021, ledger.COLUMN022,ledger.COLUMN023, ledger.COLUMN024,ledger.COLUMN025,ledger.COLUMN026,ledger.COLUMN027,Loc.COLUMN005,ledger.COLUMN029,ledger.COLUMN030,ledger.COLUMN002EE, 'NULL','NULL','NULL','NULL','NULL','NULL','NULL','NULL','NULL','NULL','SNSP_BRANCH','SNSP_BRANCH','SNSP_USER',convert(varchar(19),getdate(),120),NULL,convert(varchar(19),getdate(),120),newid(),'DBMS_REPUTIL.GLOBAL_NAME','en'
FROM MATABLE043 ledger Inner JOIN MATABLE012 wh on ledger.column002 = wh.column002 Inner JOIN MATABLE024 Loc on wh.column001 = loc.column004 WHERE COLUMN002 ='c5a0bcc4-743a-4d9a-80a5-7c2ebbf6deec' and COLUMN006 = '3d42a36e-7df7-41cd-a6ee-189f4e3f2ad5' AND COLUMN009 NOT IN ( SELECT COLUMN006 FROM MATABLE044)
what is the meaning
thank you very much
Vinod Even you learn 1%, Learn it with 100% confidence.
t1.[Converted_Mortgage_Number], t1.[Converted_Mortgage_Advance_No], t1.[Original_Account_Number], CASE WHEN t3.description IS NOT NULL THEN t3.description ELSE t1.CACS_Location_Code END AS CACS_Location_Code, t1.[Original_Account_Number_1], t1.[Payment_Amount], t1.[Letter_ID], t1.[Batch_Activity_Date], t1.[Activity_Date], t1.[Activity_Time], CASE WHEN t6.description IS NOT NULL THEN t1.[Collection_Activity] + ' - '+ t6.description ELSE t1.[Collection_Activity] END AS [Collection_Activity], CASE WHEN t5.description IS NOT NULL THEN t1.[Party_Contact_Code] + ' - ' + t5.description ELSE t1.[Party_Contact_Code] END AS [Party_Contact_Code], CASE WHEN t7.description IS NOT NULL THEN t1.[Place_Called] + ' - ' + t7.description ELSE t1.[Place_Called] END AS [Place_Called], t1.[Promise_Amount_1], t1.[Promise_D_ate_1], t1.[Promise_Amount_2], t1.[Promise_D_ate_2], CASE WHEN t8.description IS NOT NULL THEN t1.[Non_Pay_Excuse_Code] + ' - ' + t8.description ELSE t1.[Non_Pay_Excuse_Code] END AS [Non_Pay_Excuse_Code], CASE WHEN t2.description IS NOT NULL THEN t1.CACS_State + ' - ' + t2.description ELSE t1.CACS_State END AS CACS_State, t1.[User_ID/Transaction_Type], t1.[Arrears_Amount], t4.[Sequence_Number], t4.[History_Text], ROW_NUMBER() OVER (ORDER BY [$criteria] $direction) AS RowNumber
FROM format_cacs_cch AS t1
LEFT OUTER JOIN format_cacs_history_1 AS t4 ON t1.Converted_Mortgage_Number = t4.Converted_Mortgage_Number AND t1.Activity_Date = t4.Activity_Date AND t1.Activity_Time = t4.Activity_Time
LEFT JOIN sd_cacs_states AS t2 ON t1.CACS_Location_Code = t2.location AND t1.CACS_State = t2.state
LEFT JOIN sd_cacs_Location_details AS t3 ON t1.CACS_Location_Code = t3.code
LEFT JOIN sd_party_contacted_code AS t5 ON t1.[Party_Contact_Code] = t5.code
LEFT JOIN sd_collection_activity_code AS t6 ON t1.[Collection_Activity] = t6.code
LEFT JOIN sd_place_called AS t7 ON t1.[Place_Called] = t7.code
LEFT JOIN sd_non_pay_excuse_code AS t8 ON t1.[Non_Pay_Excuse_Code] = t8.code
WHERE (t1.Converted_Mortgage_Number = '$mortgage' $additional_date_params)
I'm planning an upgrade to SQL Server 2012 and noticed the following.
We have a query that includes a duplicate column name in the SELECT statement. This query happens to work in a db we have that is set to SQL 2000 (80) compatibility level, but the same exact query fails when I set the db to SQL 2008 (100) compatibility level.
I'm brand new to this forum and I'm stuck. We've got a query which queries three tables: booking, catheter and patients. The following (dodgy) query has pulled off the data for years, without problems:
SELECT CARDIAC_CATHETER.CARDIAC_CATHETER_ID, CARDIAC_CATHETER.OPERATOR1, CARDIAC_CATHETER.TEST_DATE, BOOKING.STATUS, BOOKING.TEST_DATE AS BOOKING_TEST_DATE, BOOKING.PROCEDURE_DATE_TIME, CARDIAC_CATHETER.ITEM_NO, CARDIAC_CATHETER.BILLING_CATEGORY, PATIENTS.REFERRING_PHYSICIAN1, PATIENTS.HOSPITAL_UNIT_NUMBER FROM (CARDIAC_CATHETER INNER JOIN PATIENTS ON CARDIAC_CATHETER.PATIENT_ID = PATIENTS.PATIENT_ID) LEFT OUTER JOIN BOOKING ON PATIENTS.PATIENT_ID = BOOKING.PATIENT_ID AND 'CATHETER' = BOOKING.CLINIC AND BOOKING.STATUS <> 'CANCELLED' AND BOOKING.PROCEDURE_DATE_TIME >= (CARDIAC_CATHETER.TEST_DATE - 1) AND BOOKING.PROCEDURE_DATE_TIME < (CARDIAC_CATHETER.TEST_DATE + 1) WHERE CARDIAC_CATHETER.TEST_DATE > '2005-04-01' AND CARDIAC_CATHETER.BILLING_CATEGORY LIKE '3%'
About 2500 perfect rows and 12 rows which have duplicate catheter id values. There are six pairs of duplicated records. It's only the cardiac_catheter.catheter_id and cardiac_catheter.test_date which are duplicated - the first row shown below contains correct values, the second doens't. The other values returned, on both rows, are correct. Here is some sample data:
I'm running into problems with ambiguous column names. TransID also exists in tblWork. I tried adding SHPD + WORK, but it won't accept the SHPD on the UPDATE line. How do I code around this error? Thanks. UPDATE tblShipmentDet SHPD SET SHPD.Quantity = WORK.Quantity FROM tblWork WORK WHERE TransID = WORK.RecordID
I am getting an ambiguous column name error, but I am aliasing the column in question. The problem seems to be that I'm trying to use the same column in 2 inner joins:
select trackprice.id from trackprice tp inner join track tr on tp.trackid = tr.id inner join trackstorage ts on ts.trackid = tr.id inner join ... etc ....
.... gives me the error 'ambiguous column name trackid' - can this not be done with inner joins this way?
Can someone help with this syntax? I have a non-sensicle examplebelow, but it illustrates the problem if you copy/paste into QA.**********************************use pubsgoupdate authors set address = 'some address'from authors ainner join authors a2 on a.zip = a2.zip---------------------------------------------Server: Msg 8154, Level 16, State 1, Line 2The table 'authors' is ambiguous.**********************************
i have a problem in my Goldmine 6.7 corporate version when one of the user try to do some thing this error msg came
General SQL error. Native SQL error: 209 Context: SQLQuery: GoldMine: SELECT c1.Company,c1.Contact,c1.RecID,c1.owner,c1.status, c1.AccountNo, c2.AccountNo FROM dbo.CONTACT1 c1 (NOLOCK), dbo.CONTACT2 c2 (NOLOCK) WHERE ((c1.U_KEY4 = 'ANWAY' AND c2.UTAG = 'FOCUS') AND (c1.ACCOUNTNO = c2.ACCOUNTNO)) ORDER BY ACCOUNTNO 1: Server message: Ambiguous column name 'ACCOUNTNO'. BDE Error Cat:Code: [51:3] BDE: 500 [4/11/1999] GoldMine: 6.70.70226 User: ANWAY Window: Filters and Groups &Cancel Template: Details: NATIVEMSG: Ambiguous column name 'ACCOUNTNOneed ur help
Hi , I have Three Table 1- Student m <----- > m 2 - Exam 3- Registeration Std_ID E_ID Std_ID Std_FName E_Name E_ID Reg_Date ------------------------------------------------------------------------------------------------------------------------------------------ Ok... now ... I create StoredProcedure to Return values ( Std_FName , E_exam , Reg_Date )ALTER PROCEDURE dbo.StdExamInfo @StdNumber as int AS /* SET NOCOUNT ON */select Std_FName, Reg_Date, E_Name from Student inner join Registeration on Student.Std_ID = Registeration.Std_IDinner join Exam on Registeration.E_ID = Exam.E_IDwhere Std_ID = @StdNumber RETURN but it does't work I have this error message ( Ambiguous column name ) sorry for this long message
I'm a newb and this has been giving me a trouble for a while.
Code Block SELECT class.classcode, classname, instrFirst, instrLast, csDay, CONVERT(nvarchar(30), csStart, 8), CONVERT(nvarchar(30), csEnd, 8) FROM class, class_section, instructor WHERE class.classcode = class_section.classcode Group By className Order By classname, csNum;
Msg 8120, Level 16, State 1, Line 1
Column 'class.classCode' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
class and instructor and class_section are their own tables but classcode and instrID are foreign keys on the class_section table. If anyone can explain this as clearly as possible that would be great (the help examples are usually too complicated)
I have a query that is exhibiting a strange behaviour on different databases.
I've got something like this ...
select a.col, ... from tab1 as a inner join ... union select a.col, ... from tab1 as a inner join ... order by col
I know how to fix the problem, I make the last line read "order by a.col" ... that is understood.
However, on some databases I get an error and on others I don't. All databases are on the same server instance which is sql server 2005 developer (developer version isn't relevant as it happens on customer machines too).
I'm just wondering if anyone has any ideas why it is 'random'. It's as though the statement is being compiled differently on different databases ... what is influencing that ?
I'm fishing for a way to create a workaround, so if anyone has any ideas it might push me down the road of understanding why.
The real code is way too big to include here and it's not really relevant.
As a clue to what I'm looking for, if I copy the database to another database using SSIS, the resultant database doesn't cause the error despite being logically the same.
Presumably it is some physical or security difference that is causing the error to occur.
select DISTINCT destination_id, classified_job_id from classified_job_destinations inner join classified_jobs on classified_jobs.classified_jobs_id = classified_jobs.classified_job_id LEFT JOIN destinations_old ON destinations_old.destination_id = classified_job_destinations.destination_id WHERE classified_jobs.classified_jobs_id = '438'
but I am getting the error: #1052 - Column 'destination_id' in field list is ambiguous
Hi all,A (possibly dumb) question, but I've had no luck finding a definitiveanswer to it. Suppose I have two tables, Employees and Employers, whichboth have a column named "Id":Employees-Id-FirstName-LastName-SSNetc.Employers-Id-Name-Addressetc.and now I perform the following join:SELECT Employees.*, Employers.*FROM Employees LEFT JOIN Employers ON (Employees.Id=Employers.Id)The result-set will contain two "Id" columns, so SQL Server willdisambiguate them; one column will still be called "Id", while theother will be called "Id1." My question is, how are you supposed toknow which "Id" column belongs to which table? My intuition tells me,and limited testing seems to indicate, that it depends on the order inwhich the table names show up in the query, so that in the aboveexample, "Id" would refer to Employees.Id, while "Id1" would refer toEmployers.Id. Is this order guaranteed?Also, why does SQL Server use such a IMO brain-damaged technique tohandle column name conflicts? In MS Access, it's much morestraightforward; after executing the above query, you can use"Employees.Id" and "Employers.Id" (and more generally,"TableNameOrTableAlias.ColumnName") to refer to the specific "Id"column you want, instead of "Id" and "Id1" -- the"just-tack-on-a-number" strategy is slightly annoying when dealing withcomplex queries.--Mike S
This could well be down to my _limited_ knowledge of XSD.
I have a document and SXD supplied by 3rd party.
Both documents are valid, according to XMLSpy.
When I give the document and xsd to SSIS XML Source it complains about ambiguous complex types.
In the XML doc there is an element called Allowance that has child elements.
There is also a group which references many other elements including Allowance.
When I remove the group, SSIS stops complaining about allowance.
Would the problem stem from SSIS creating an output called Allowance ('cause of it's children), getting to the group and again, 'cause allowance has children, try create another output called Allowance.
Is my understanding of this correct? Is there a work around for a situation like this?
The only thing I can come up with is deleting the group....
Possible to alias an element? Could alias the Group > Allowance g_Allowance.
i'm getting following exception when i try to execute stored procedure.
{"Ambiguous column name 'MemberID'. Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1." }
I know why i'm getting "Ambiguous column name 'MemberID'" exception but i dont know why i'm getting "Transaction count after EXECUTE indicates that......"
In my stored proc i'm checking if error occured goto :Error_handler where i do ROLLBACK TRAN
/* Its failing at MemberID = tTempResult.MemberID bcoz of Ambiguous column name. I know i have to use RebateInstanceItem.MemberID=tTempResult.MemberID */
/* I have some logic here that will select rows into temporary table #TEMPRESULT */
UPDATE dbo.RebateInstanceItem SET ResubmitCreated = @dtmNewCreated FROM #TEMPRESULT tTempResult WHERE MemberID = tTempResult.MemberID AND RebateInstanceItem.IsResubmit = 'Y' AND (RebateInstanceItem.ResubmitCreated = @dtmLastCreated OR RebateInstanceItem.ResubmitCreated IS NULL)
IF @@ERROR<>0 GOTO ERR_HANDLER // when error it will goto error_handler that will rollback
DROP TABLE #TEMPRESULT IF @@ERROR<>0 GOTO ERR_HANDLER
i'm getting following exception when i try to execute stored procedure.{"Ambiguous column name 'MemberID'. Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1." }I know why i'm getting "Ambiguous column name 'MemberID'" exception but i dont know why i'm getting"Transaction count after EXECUTE indicates that......" In my stored proc i'm checking if error occured goto :Error_handler where i do ROLLBACK TRAN/* Its failing at MemberID = tTempResult.MemberID bcoz of Ambiguous column name. I know i have to use RebateInstanceItem.MemberID=tTempResult.MemberID */ here is my stored procCREATE PROCEDURE dbo.ExportFile @intMonth INT, @intYear INT, @dtFirstDayOfMonth DATETIMEAS BEGINBEGIN TRANSACTION /* I have some logic here that will select rows into temporary table #TEMPRESULT */ UPDATE dbo.RebateInstanceItem SET ResubmitCreated = @dtmNewCreated FROM #TEMPRESULT tTempResult WHERE MemberID = tTempResult.MemberID AND RebateInstanceItem.IsResubmit = 'Y' AND (RebateInstanceItem.ResubmitCreated = @dtmLastCreated OR RebateInstanceItem.ResubmitCreated IS NULL) IF @@ERROR<>0 GOTO ERR_HANDLER // when error it will goto error_handler that will rollback DROP TABLE #TEMPRESULT IF @@ERROR<>0 GOTO ERR_HANDLERCOMMIT TRANSACTIONRETURN 0ENDERR_HANDLER: ROLLBACK TRANSACTION RETURN 1GO
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?
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.
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.
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.
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.
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
insert into #t(branchnumber) values (005) insert into #t(branchnumber) values (090) insert into #t(branchnumber) values (115) insert into #t(branchnumber) values (210) insert into #t(branchnumber) values (216)
[code]....
I have a parameter which should take multiple values into it and pass that to the code that i use. For, this i created a parameter and temporarily for testing i am passing some values into it.Using a dynamic SQL i am converting multiple values into multiple records as rows into another variable (called @QUERY). My question is, how to insert the values from variable into a table (table variable or temp table or CTE).OR Is there any way to parse the multiple values into a table. like if we pass multiple values into a parameter. those should go into a table as rows.
Hello, I run the DTS to copy data from Progress to SQL Server. How can match/convert the date variables to check. The field p-date as format 'mm-dd-year' . It has the value of 10/09/2001. The field s-date as varchar format. It has the value 2001-10-09. How can use the where condition ( Select ...... WHERE p-date = s-date.)
Hello! I'm using SQL Server 2000. I have a variable which contains the name of another variable scoped in my stored procedure. I need to get the value of that other variable, namely:
CREATE table #myTable(value VARCHAR(20)) INSERT into #myTable values('@operation')
SELECT top 1 @parameterValue = value from #myTable -- Now @parameterValue is assigned the string '@operation' -- Here I need some way to retrieve the value of the @operation variable I declared before (in fact -- another process writes into the table I retrieved the value from), in this case 'DIS'
DROP TABLE #myTable
I've tried several ways, but didn't succeed yet! Please tell me there's a way to solve my problem!!!
I have an SSIS package that creates a csv file based on a execute sql task.
The sql is incredibly simple select singlecolumn from table.
In the properties of the execute sql task I specify the result set as "full result set" when I run it I get the error that: Error:
The type of the value being assigned to variable "User::CSVoutput" differs from the current variable type.
Variables may not change type during execution. Variable types are strict, except for variables of type Object.
If I change the resultset to single row then I only get the first row from the DB (same if I choose none), If I choose XML then I get the error that the result is not xml. What resultset type do I need to choose in order to get all the rows into the CSV? The variable I am populating is of type string and is User::CSVoutput
