hi,
i am new to sql server...
i have used @ symbol for declaring local variables in stored procedure....
The symbol @@ is there....where to use and what is the purpose of using that symbol...
could anyone tell...
thanks
Can someone say how to declare a record like variable in MSSQL-2000 Like:
mr_rec record of variables a int, b char(20), c datetime? I could not find any examples on BOL. I want to use this in a stored procedure create script.
Thanks, Vinnie
I'm wondering if there's a way to pass a variable to assigning a decimal datatype;
declare @intPrecision int
set @intPrecision = 3
declare @decVariable decimal(38, @intPrecision)
I've basically been given the task by my mentor to create a script to round a decimal to a given number of decimal places.
ie; 1234.56789; 2 dp => 1234.57 and not 1234.57000
Any advice would be great.
I am reviewing some code we have inherited (riddled with multiple nested cursors) and in the process of re-writing some of the code. I came across this and it has me puzzled.
As I understand it, if you declare a variable and then try to re-declare a variable of the same name an error is generated. If I do this inside a While loop this does not seem to be the case. What ever is assigned is kept and just added to (in the case of a table variable)
I understand things are in scope for the batch currently running but I would expect an error to return (example 1 and 2)
--Table var declaration in loop
SET NOCOUNT ON
DECLARE @looper INT = 0
WHILE @looper <= 10
BEGIN
DECLARE @ATable TABLE ( somenumber INT )
[Code] ....
Hi,
here is the code segment below;
...
DECLARE find_dates CURSOR FOR
SELECT @SQL = 'select DISTINC(Dates) from ['+@name+'].dbo.['+@t_name+'] order by [Dates] ASC'
EXEC (@SQL)
but it gives error, variable assignment is not allowed in a cursor declaration. I need to use dynamic SQL , the only way to access all the dbs and their tables inside. Please help.
thanks
I have the following lines embedded in a stored procedure
---
----
----
select @querystr = "select @lkinpos = pos_lkin_pos from "+@database+" where pos_clnt_id = "+str(@clntid)+" and pos_isin ="+"'"+ @isinno+"'"
print @querystr
exec(@querystr)
---
---
---
When i execute the above stored procedure, it returns an error as follows:
Server: Msg 137, Level 15, State 1, Line 1
Must declare the variable '@lkinpos'.
When i see the print @querystr statement, it returns the query str as follows:
select @lkinpos = pos_lkin_pos from nsdldpm..pos_mstr where pos_clnt_id = 10000045 and pos_isin ='ine227a01011'
This when executed independently, gives me the required output.
Can anybody solve this for me?
Thanks in advance
Is there a way to write such a query where we can declare the variable dynamically ? Currently I am using the query as shown below :
declare @pYear_Internal as NVarchar(100)
set @pYear_Internal = [D FISCALPERIOD].[FP CODE].[FP CODE]
WITH
MEMBER MEASURES.[REVENUE] AS [Measures].[TOTAL REVENUE]
SET LAST5YEARS AS STRTOMEMBER(@pYear_Internal).Lag(4) : STRTOMEMBER(@pYear_Internal)
[code]....
While executing the above query, getting the error - Query (1, 9) Parser: The syntax for '@pYear_Internal' is incorrect. Â It looks like it doesn't recognize DECLARE keyword as it does with SQL queries. Â I just want a query that runs directly against the server.Â
I've been curious why some variables don't need to be declared with a value type in a stored procedure. Please see the example below in the bolded area. The stored procedure works fine - nothing is wrong with it, but I just wanted an explanation on why and when is a value type not needed for a variable.
CREATE PROCEDURE [DBO].[EmailRpt]
(
@TagDest char(2) = NULL,
@DateWanted smalldatetime = NULL,
@CustName varchar(64) = NULL,
@AcctID AcctId = NULL,
@ContactPhn phone = NULL,
@CustAddr address = NULL,
@City city,
@CrossSt varchar(50) ,
@Access varchar(50) = NULL,
@Activity tinyint,
)
AS
DECLARE @String as varchar(2000),
@Header as varchar(200),
@MailBox as varchar(50),
@ActivityName as Name,
@Return as int,
@UserName as Name
Hello All,I am trying to use a variable(@varStr ) in a cursor declaration. But I am unable to use it. something like:declare @intID as intset @intID = 1DECLARE curDetailRecords CURSOR FOR (select fnameFrom Customers where id = @intID)Can we not use a variable in a cursor declaration.?ThanksImran
View 1 Replies View RelatedHi!
While working for a client on a SQL Server 6.5 SP5a, I got the following error when running the code below in first SQL Enterprise Manager 6.5 and then SQL Query Analyzer 7.0:
IF @Departures = 1
DECLARE TableCursor CURSOR
FOR SELECT AcType,
BackPax = CASE BackPax
WHEN NULL THEN 0 ELSE BackPax END,
BestPax = CASE BestPax
WHEN NULL THEN 0 ELSE BestPax END,
DepTime,
FlightNumber,
ArrStn
FROM #TimeCall
ORDER BY DepTime, FlightNumber
ELSE
DECLARE TableCursor CURSOR
FOR SELECT AcType,
BackPax = CASE BackPax WHEN NULL THEN 0 ELSE BackPax END,
BestPax = CASE BestPax WHEN NULL THEN 0 ELSE BestPax END,
ArrTime,
FlightNumber,
DepStn
FROM #TimeCall
ORDER BY ArrTime, FlightNumber
And the error I get when the query is run for the first time after switching tool:
Server: Msg 202, Level 11, State 2, Procedure CreateFile, Line 178
Internal error -- Unable to open table at query execution time.
Server: Msg 202, Level 11, State 1, Procedure CreateFile, Line 188
Internal error -- Unable to open table at query execution time.
If I run the query again in one of the tools, it works. It also works if I use WITH RECOMPILE in the stored proc header.
If I use the code below, it also works, and without RECOMPILE:
DECLARE @SqlStr varchar( 255 )
IF @Departures = 1
SELECT @SqlStr = 'DECLARE TableCursor CURSOR ' +
'FOR SELECT AcType, ' +
'BackPax = CASE BackPax WHEN NULL THEN 0 ELSE BackPax END, ' +
'BestPax = CASE BestPax WHEN NULL THEN 0 ELSE BestPax END, ' +
'DepTime, FlightNumber, ArrStn ' +
'FROM #TimeCall ORDER BY DepTime, FlightNumber'
ELSE
SELECT @SqlStr = 'DECLARE TableCursor CURSOR ' +
'FOR SELECT AcType, ' +
'BackPax = CASE BackPax WHEN NULL THEN 0 ELSE BackPax END, ' +
'BestPax = CASE BestPax WHEN NULL THEN 0 ELSE BestPax END, ' +
'ArrTime, FlightNumber, DepStn ' +
'FROM #TimeCall ORDER BY ArrTime, FlightNumber'
EXECUTE( @SqlStr )
Trying to get around the problem with the following code did not do any good:
DECLARE TableCursor CURSOR FOR
SELECT AcType,
BackPax = CASE BackPax WHEN NULL THEN 0 ELSE BackPax END,
BestPax = CASE BestPax WHEN NULL THEN 0 ELSE BestPax END,
CurTime = CASE @Departures
WHEN 1 THEN DepTime
ELSE ArrTime END,
FlightNumber,
OtherStation = CASE @Departures
WHEN 1 THEN ArrStn
ELSE DepStn END
FROM #TimeCall
ORDER BY CurTime, FlightNumber
Server: Msg 202, Level 11, State 2, Procedure CreateFile, Line 176
Internal error -- Unable to open table at query execution time.
Anyone have some good ideas on why this happens?
Brgds
Jonas Hilmersson
Hi,
I am trying to convert an active x script in a script task. Below is a snippet of code. The underlined AsOfDate has a blue squiggly line under it and if I hover over it, it says "Declaration Expected."
Public Class ScriptMain
Dim AsOfDate As String
AsOfDate = Dts.Variables("MyDate").Value
...
Can someone please tell me what I'm missing? I thought maybe I'm missing an import statement, but I have:
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
I have used similar syntax in script components and it works fine.
Thanks
Hello everybody!I have a small table "ABC" like this:id_position | value---------------------------1 | 112 | 223 | 33I try to use a dynamic cursor as below.When the statement "order by id_position" in declare part of the cursor_abcis omitted - cursor work as it should.But when the statement "order by id_position" is used, cursor behave asstatic one.What's the matter, does anybody know?Code:declare @id_position as int, @value as intDECLARE cursor_abc CURSORFORselect id_position, value from abcorder by id_positionset nocount onopen cursor_abcFETCH NEXT FROM cursor_abcINTO @id_position, @valueWHILE @@FETCH_STATUS = 0BEGINprint @id_positionprint @valueprint '----------------------------'update abc set value=666 --next reading should give value=666FETCH NEXT FROM cursor_abcINTO @id_position, @valueENDCLOSE cursor_abcDEALLOCATE cursor_abcGORegardsLucas
View 2 Replies View RelatedI have some stored procedure and there is a cursor inside it. I added some new columns to the table and those columns I included in the cursor declaration and fetch statement. In the cursor declaration I forgot to add comma (,) in between the new columns. So SQL Server it considers as a alias name for that column so syntactically it is correct. But logically in the cursor declaration having less number of columns than the columns in the fetch statement. So it should throw an error. But the procedure is getting compiled without raising any error. But if I execute the procedure that time it is throwing the error.
For example, below I have given the sample procedure. In this procedure, in the cursor declaration I removed the comma (,) between DOB and DOJ. If I compile this procedure it is getting compiled. But when execute that time only it is throwing the error. So I am interested in if any option is available to know the error in the compilation time itself.
ALTER PROCEDURE Test
AS
BEGIN
BEGIN TRY
DECLARE @empId INT,
@fname VARCHAR(50),
@dob DATE,
@doj DATE
[code]....
Hi All,
I'm using some xslt documents to transform the xml output of my Reports
but have come across two curiosities where the xslt filter seems to
behave unusually.
Firstly, I need the final saved file to have an xml declaration, which
I believe it should do by default. Even if I put
omit-xml-declaration="no" in the xsl:output tag I don't get an xml
declaration. At present we have a custom job that writes these
declarations back into the xml after SRS has saved it.
Secondly and more importantly, I need to have some of my output tags
wrapped in CDATA sections. I've tried using the cdata-section-elements
attribute, again with no luck.
my XSLT looks something like this (simplified for space)
<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="xml" encoding="utf-8" media-type="text/xml" omit-xml-declaration="no" cdata-section-elements="description"/>
<xsl:template match="/">
<xsl:for-each select="Report/table1/Detail_Collection/Detail">
<item>
<description>
<xsl:value-of select="@Description"/>
</description>
</item>
</xsl:for-each>
</xsl:template>
</xsl:stylesheet>
The output is something like:
<item>
<description>My first description text...</description>
</item>
<item>
<description>My seconddescription text...</description>
</item>
What I want is:
<?xml version="1.0" encoding="utf-8"?>
<item>
<description><![CDATA[My first description text...]]></description>
</item>
<item>
<description><![CDATA[My secondfirst description text...]]></description>
</item>
All help gratefully appreciated.
Thanks - Andrew.
I think I may have figured out the solution to my recent conundrum with SQL Server 2000 that had me stressed and depressed over the last couple of days. In a nutshell, after a HotFix was installed on a SQL2K database server I have space on, I was unable to perform INSERT or UPDATE queries on database table of type TEXT, when trying to either create or modify records with more than 4,000 characters of data. While it was frustrating as heck, it seemed too rigid to be random, so I did some snooping.
The HotFix was intended to solve a known problem of not being able to run UPDATEs against TEXT fields, but in so doing, caused another headache entirely: http://support.microsoft.com/?kbid=839523
Apparently this is a semi-known problem, in that a certain HotFix forces SQL Server 2000 to be a lot more stringent in requiring explicit declaration of data types and data lengths for parameters in stored procedure. In my client code, I was initially using the overloaded constructor of the SqlParameter object that took as arguments only the parameter name and a value, without specifying a value from the SQLDBType enumeration or length of the parameter (which in my case, needs to be TEXT and 16 (or 2147483647), respectively):
System.Data.SqlDataAdapter.InsertCommand.Parameters.Add(new SqlParameter("@parameterName",parameterValue);
It appears that after the HotFix is installed, if the client doesn’t syntactically set the type and length of data for a parameter, SQL Server and/or .NET will default to a type of NVARCHAR, which has the 4,000-character limit. This all makes sense. I’m going to now need to modify the code to straight out declare what’s going in the SPROC:
System.Data.SqlDataAdapter.InsertCommand.Parameters.Add(new SqlParameter("@parameterName",SqlDbType.Text);
System.Data.SqlDataAdapter.InsertCommand.Parameters["@parameterName"].Value = parameterValue;
It’s a minor change, and it sucks that I have to make it after the code had worked flawlessly over several thousands executions over several months, but c’est la vie! Better thay than have to rebuild my DB from scratch or switch to a new server. Changing the client code evidently is the only known fix at this time: http://support.microsoft.com/default.aspx?scid=kb%3ben-us%3b827366
is there any way or a tool to identify if in procedure the Parameter length was declarated less than table Column length ..
I have a table
CREATE TABLE TEST001 (KeyName Varchar(100) ) a procedure
CREATE PROCEDURE SpFindNames ( @KeyName VARCHAR(40) )
AS
BEGIN
SELECT KeyName FROM TEST001
WHERE KeyName = @KeyName
END
KeyName = @KeyName
Here table Column with 100 char length "KeyName" was compared with SP parameter "@KeyName" with length 40 char ..
IS there any way to find out all such usage on the ALL Procedures in the Database ?
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.
Dan
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.
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
Hi
View 7 Replies View RelatedI keep getting this debug error, see my code below, I have gone thru it time and time agian and do not see where the problem is. I have checked and have no NULL values that I'm trying to write back.
~~~~~~~~~~~
Error:
System.NullReferenceException was unhandled by user code Message="Object variable or With block variable not set." Source="Microsoft.VisualBasic"
~~~~~~~~~~~~
My Code
Dim DBConn As SqlConnection
Dim DBAdd As New SqlCommand
Dim strConnect As String = ConfigurationManager.ConnectionStrings("ProtoCostConnectionString").ConnectionString
DBConn = New SqlConnection(strConnect)
DBAdd.CommandText = "INSERT INTO D12_MIS (" _
& "CSJ, EST_DATE, RECORD_LOCK_FLAG, EST_CREATE_BY_NAME, EST_REVIEW_BY_NAME, m2_1, m2_2_date, m2_3_date, m2_4_date, m2_5, m3_1a, m3_1b, m3_2a, m3_2b, m3_3a, m3_3b" _
& ") values (" _
& "'" & Replace(vbCSJ.Text, "'", "''") _
& "', " _
& "'" & Replace(tmp1Date, "'", "''") _
& "', " _
& "'" & Replace(tmpRecordLock, "'", "''") _
& "', " _
& "'" & Replace(CheckedCreator, "'", "''") _
& "', " _
& "'" & Replace(CheckedReviewer, "'", "''") _
& "', " _
& "'" & Replace(vb2_1, "'", "''") _
& "', " _
& "'" & Replace(tmp2Date, "'", "''") _
& "', " _
& "'" & Replace(tmp3Date, "'", "''") _
& "', " _
& "'" & Replace(tmp4Date, "'", "''") _
& "', " _
& "'" & Replace(vb2_5, "'", "''") _
& "', " _
& "'" & Replace(vb3_1a, "'", "''") _
& "', " _
& "'" & Replace(vb3_1b, "'", "''") _
& "', " _
& "'" & Replace(vb3_2a, "'", "''") _
& "', " _
& "'" & Replace(vb3_2b, "'", "''") _
& "', " _
& "'" & Replace(vb3_3a, "'", "''") _
& "', " _
& "'" & Replace(vb3_3b, "'", "''") _
& "')"
DBAdd.Connection = DBConn
DBAdd.Connection.Open()
DBAdd.ExecuteNonQuery()
DBAdd.Connection.Close()
CREATE TABLE #T(branchnumber VARCHAR(4000))
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.)
Thanks
When trying to upsize an access database to sql server using the upsize wizard, I get the following error:
"Object variable or With block variable not set."
Any assistance is greatly appreciated.
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:
DECLARE @operation VARCHAR(3)
DECLARE @parameterValue VARCHAR(50)
SELECT @operation='DIS'
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!!!
Thank you very much in advance!
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
While I was processing the cubes, error "Object Variable Or With Block Variable Not Set" prompt out,
what does it mean ?
Please help !!!
Is there any way to use a 2 dimensional array of strings as the Variable Enumerator for the "Foreach From Variable Enumerator". I am trying to copy a collection of files from folder A to folder B. In a script, I would populate, let us say, an array of (2,10), for 10 files, with one column representing the source file and other column representing the target column task. Then I would like to set this string array variable as the "Variable Enumerator" for the "Foreach From Variable Enumerator" and use file system tasks in the foreach loop to perform the tasks. The problem is that the "Foreach From Variable Enumerator" does not let me choose an index, but passes only one index 0, so, I will only be able to pass just one column. How do I let the foreach enumerator let me choose an index. The other foreach enumerators, foreach item and ADO give me the option to select index. I would like the same functionality in the foreach variable.
Note: I cannot use the "For Each File" enumerator, since the files are to be selected by a script only.
Thanks for the help.
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!
I've got two Sql Tasks on my dtsx. The first one loads a value into "Proyecto" user variable and the second one executes a variable named "SegundoProceso" which contains from the beginning:
"select Fecha from LogsCargaExcel where Proyecto = " + @[User::Proyecto] +""
As SqlSourceType propety I have "Variable" and inside ResultSet or Parameter Mapping nodes there is nothing.
[Execute SQL Task] Error: Executing the query ""select Fecha from LogsCargaExcel where Proyecto = " + @[User::Proyecto] +""" failed with the following error: "Cannot use empty object or column names. Use a single space if necessary.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Where am I wrong?
TIA
Hi,
I have an Execute SQL Task (OLE DB Connnection Manager) with a SQL script in it. In this script I use several SQL variables (@my_variable). I would like to assign an IS variable ([User::My_Variable]) to one of my SQL variables on this script. Example:
DECLARE @my_variable int
, <several_others>
SET @my_variable = ?
<do_some_stuff>
Of course, I also set up the parameter mapping.
However, it seems this is not possible. Assigning a variable using a ? only seems to work in simple T-SQL statements.
I have several reasons for wanting to do this:
- the script uses several variables, several times. Not all SQL variables are assigned via IS variables.
- For reading and mainenance purposes, I prefer to pass the variable only once. Otherwise every time the script changes u need to keep track of all questionmarks and their order.
- Passing the variable once also makes it easier to design the script outside IS using Management Studio.
- This script only does preparation for the actual ETL, so I prefer to keep it in one task instead of taking it apart to several consecutive Execute SQL Tasks.
- I prefer to use the OLE DB connection manager because it's a de facto standard here.
Could anyone help me out with the following questions:
- Is the above possible?
- If so, how?
- If not, why not?
- If not, what would be the best way around this problem?
Thanx in advance,
Pipo
How can I inside a DFT set a System variable, for example "TaskName" to an own created User Variable?
The reason is that I need to use this variable later in the Control Flow.
Regards
Riccardo