Transact SQL Probklems With Variable Scope

Jul 20, 2005

I have 24 tables named tblData1 ... tblData24 and I have a scheduled
job that runs successfully to delete all data older than 31 days.

My problem is that I need to keep at least one record in each table
for the aggregate function max() to work in one of my application's
functions, as if there are no records the result is null.
Although I have figured out a workaround in the function using max() I
would like to know how to change my script.

Functionally I would like to get the max() value of the ID column
(autoincrementing) and then add to the where "And ID <> @maxID".
I have tried a few options and come unstuck with scope of variables,
and tried to use a temp table to store the max values for the 24
tables and got no where. Can anyone help ?


Working script without the @maxID bit:-


DECLARE @days VARCHAR(12)
DECLARE @intData int
DECLARE @SQL1 VARCHAR(2000)
set @Days = 31
set @intData = 1



While @intData<=24
Begin


SET @SQL1 = 'DELETE FROM [DB1_SQL].[dbo].[tblData'+
rtrim(CONVERT(char(2), @intData)) + '] Where
datediff(Day,Datim,getdate())> '+ @days

EXEC(@SQL1)

/*print @SQL1*/

set @intData= @intData + 1

End

go

View 2 Replies


ADVERTISEMENT

Variable Scope And Connection Manger Scope

Mar 13, 2008



I have taken three dtsx files and re written them into one each in its own container. I use the XML Task task alot which the File connection is set by a variable and the variable value is evaluated by expression (the expression makes up the path/filename from other variable values). All the variables that make up the connection are at the container scope. The package will not run now because it is saying that the source (created by variables) for the file connection do not exist.

It seems the answer is that file connections exist at the package level therefore the variable has to be at the package level. This seems to be alot of variables i now have to move to package level to generate the XML source connection. Which in essence makes it confusing as to which variables operate in which container.

My question is can we easily move variable scope (Not ideal as we have alot of variables at package level) Or Can we do the same for connection managers as we do for variables and have them only used in a scope? (this will be ideal as some connections only need to be at a container scope)

View 1 Replies View Related

Scope Of Variable

Dec 4, 2007

Hi,
Is there any way to change the scope of a user defined variable?

View 3 Replies View Related

Scope Of Variable

Dec 4, 2007

Hi,
how to change the a scope of a user defined variable?

View 2 Replies View Related

Variable Scope

Sep 7, 2006

Hello again,

Variable scope of package variables should be in a dropdown. I want to copy (20+) variables from one sequence container to another. Do I have to retype all the names, types and initial values because I made the mistake not to place them one level higher?

Greets,
Tom

View 2 Replies View Related

Scope Of Global Variable

Aug 22, 2006

Hi!

I want to know the scope of a Global Variable in case of multi users.

Means i have declared a global variable in a function. And a new value is assigned to this global variable into this function, each time it is called.

So if, 3 users call this function at same time, then will the get different gloabl variables or same?

Regards,
Shabber.

View 11 Replies View Related

Variable Scope Problem

Mar 1, 2007

Greetings SSIS friends!

Consider the following scenario :

Source table : Result (contains 100 rows with primary key Reuslt_ID)

Destination Table : stage_RESULT (same structure as source table)



Source table gets regular inserts with new result_ids. I want my package to pick up new result_ids only, i.e. (Result_ids > maximum(result_id) in stage_RESULT.

My package is designed to do the following :

1) Retrieve current maximum result_id into @max_result_id from RESULT_STAGE

2) Retrieve rows from Result where result_id > @max_result_id



sounds simple, BUT.. it's not working.... my source table resides in a SQL Server 6.5 database so I am having to use a datareader source adapter to pull the data.

The first time I run the package (when my stage_RESULT) is empty, the package pulls all 100 rows from the source to the destination table, but the second time I run it, it still retrieves all 100 rows again even though the value of the variable is greater than all result_ids in the source.



What am I doing wrong?

I have a variable defined at the package level. I use a SQL Query Task to assign a value to this variable (this bit works just fine). I then use this variable in my data flow task in order to retrieve data using the My expression used for my datareader source adapter is :

"select * from result where result_id > " + (dt_str, 10, 1252) @[max_result_id]



This is confusing the hell out of me.

View 3 Replies View Related

Can I Create The Variable With Scope --Project

Mar 19, 2008

can i create the variable with scope --Project

View 5 Replies View Related

Can I Create The Variable With Scope --Project

Mar 19, 2008



Hi All,

Could u plz tell me how to create a variable with scope project but not package(normally)

View 4 Replies View Related

What Is The Scope Of A Table Variable Defined In A Dataset?

Dec 14, 2007

if a dataset doesnt use a stored proc to define a table variable, what is the scope of that table variable? Does the name need to be unique from such variables defined by other datasets?

View 4 Replies View Related

Changing Variable Scope In Package Templates?

Jun 15, 2006

Is there any way to change variable scope while using package templates?

I have created a package template that has several variables, a "typical" control flow and data flow. My goal was to try and use this as a starting point to create other packages within the same project and edit as required in the new package. I couldn't find any way (yet) to change scope of variables...these still show as belonging to the scope of package used to create the template.

Appreciate any help...thanks.

View 4 Replies View Related

Transact SQL :: Temp Table Scope On Error

Oct 12, 2015

In my sp I'm have an Insert statement. In the event that after the insert @@rowcount = 0 I'm throwing an error with RAISEERROR.

This is fine, however, in my CATCH block I'm referencing a local temp table but getting an error that it no longer exists.

My question is, does throwing an error drop any #temp tables? I thought they were in scope for the session?

View 4 Replies View Related

Transact SQL :: Table Lock Time Scope

May 8, 2015

I have a query which is part of bigger transaction:

;WITH CTE(
SELECT id, q,
totalQ=SUM(q) OVER (ORDER BY id ROWS UNBOUNDED PRECEDING)
FROM dbo.myTable WITH (UPDLOCK, ROWLOCK)
), CTE1 AS(
SELECT id, q_take=CASE WHEN c.Total<@qRequired THEN c.q ELSE
@qRequired-c.Total+c.q END FROM CTE
WHERE (CASE WHEN c.Total<@qRequired THEN c.q ELSE
@qRequired-c.Total+c.q END)>0
)
UPDATE t set q-=c1.q_take FROM dbo.myTable t INNER JOIN CTE1 c1 ON t.id=c1.id

Because CTE query is executed separately of update query I must put UPDLOCK hint, otherwise myTable could be changed while my query executes by some other user.(I have snapshot row committed transaction).

The problem is that my hint will hold lock on rows of myTable until the end of outer transaction(the bigger one).

But I need lock only for the time of this small query execution. What would be the best way to achieve that?

View 8 Replies View Related

Reporting Services :: Writing SSRS Expression Using Scope Or Variable?

May 21, 2015

I have created a heat map and it is working pretty well. The only issue I am having is that the expression for the fill is using "DataSet1"

=Code.GetHeatmapColor(sum(Fields!AnnualPremium.Value),Min(Fields!AnnualPremium.Value,"DataSet1",Recursive),

Max(Fields!AnnualPremium.Value,"DataSet1",Recursive))

This is making the heatmap look at the whole dataset instead of just what I am grouping by. Within the Dataset there are Regions and Credit Unions. Since the Dataset is looking at an entire region, the heatmap is coloring based on all data for the region. I need to heatmap to color based on the Credit Unions in that region. The Credit Unions are a group. I need the group to be the value it is referencing in the heat map and not "DataSet1". I have been told to use scope or a variable but cannot get it to work correctly.

View 3 Replies View Related

Passing A Variable Value (Package Scope) To A DTS Package Embedded Into Execute DTS 2000 Task

Jul 19, 2007

Hi friends,

I have a for each loop that populates from a set of flat files into a Sql Server table, I run the Flat file Import via a dts package embedded into Execute DTS 2000 Task. I want to pass the Sourcefile Name that is fetched by the For Each Loop to assign it Global Variable in DTS. how this can be made ?



Thanks

Subhash Subramanyam

View 4 Replies View Related

Transact SQL :: Insert Values From Variable Into Table Variable

Nov 4, 2015

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.

View 6 Replies View Related

How To Use Transact SQL Variable In A SQL In Statement

Mar 2, 2007

Hi all,

I have been struggling with the below transact sql user defined function. I want to use a transact sql variable in an "in" statement. I don't get any results and I am not sure if I am receiving an error or not.

Code:


DECLARE @myval varchar(50),@username varchar(50)
DECLARE @rolelist varchar(2000)
SET @rolelist = ''
SET @myval = 'user a,user b'
select @myval = ''''+ replace(@myval,',',''',''') + ''''
print @myval

DECLARE User_Cursor CURSOR FOR
select distinct eusername
from euser
where eusername in (@myval)


OPEN User_Cursor

FETCH NEXT FROM User_Cursor INTO @username

SET @myval = @username
SET @rolelist = @username
WHILE @@FETCH_STATUS = 0
BEGIN
SET @rolelist =+ @rolelist + ',' + @username


FETCH NEXT FROM User_Cursor INTO @username
END
CLOSE User_Cursor
DEALLOCATE User_Cursor
print @myval
print 'rolelist' + @rolelist
GO



I am at a loss any suggestions would be greatly appreciated.

View 4 Replies View Related

Transact SQL - Local Variable

Jun 28, 2004

I execute the following in my Query Analyzer:

Declare @Test varchar(8000)

Set @Test='SELECT VIOXX_LastName + '' + VIOXX_FirstName + '' + CONVERT(varchar(50), VIOXX_Number) AS PlaintiffsName, VIOXX_Number
FROM tblPlaintiff WHERE VIOXX_Number NOT IN(SELECT VIOXX_Number FROM tblCase_Plaintiff) OR
VIOXX_Number IN (SELECT tblCase_Plaintiff.VIOXX_Number FROM tblCase INNER JOIN tblCase_Plaintiff ON tblCase.Case_Number = tblCase_Plaintiff.Case_Number
WHERE (tblCase.Status = ''InActive'')) ORDER BY VIOXX_Number, VIOXX_LastName'
Select @Test

and get the following result:
SELECT VIOXX_LastName + ' + VIOXX_FirstName + ' + CONVERT(varchar(50), VIOXX_Number) AS PlaintiffsName, VIOXX_Number
FROM tblPlaintiff WHERE VIOXX_Number NOT IN(SELECT VIOXX_Number FROM tblCase_Plaintiff) OR
VIOXX_Number IN (SELECT

the latter part of my original text is not stored in the variable. Is there some limitation on the number of characters for a local variable in transact sql?

Any ideas? Thanks in advance.

View 2 Replies View Related

Transact SQL :: Save Value Of PREPARE Into A Variable

Jul 16, 2015

I have below code:

WHILE i < total_rows DO
SET @param_employee_number = (SELECT employee_number FROM earned_leaves LIMIT i,1);

PREPARE query_statement FROM 'CALL sp_populate_leave_summary(?)';
EXECUTE query_statement USING @param_employee_number;

-- UPDATE earned_leaves SET earned_leave = returned_by_EXECUTE
SET i = i + 1;
END WHILE;

I want to save the value returned by the EXECUTE into a variable in order to use it in the next UPDATE statement.

View 4 Replies View Related

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

Nov 3, 2015

-- Create an Employee table.

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

-- Populate the table with values.

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

[code]....

View 3 Replies View Related

Transact SQL :: How To Check For Existence Of Environment Variable

Oct 8, 2015

I'm trying to figure out the best way to write a script to deploy environment variables to different servers. To create the variable I'm currently using  catalog. create_environment_variable. I want to wrap that in an if not exist statement.I had thought about just blowing away all the variables and recreating them but I thought that wouldn't go over well in prod. I wasn't sure if by deleting the variable, references to the variable would be lost.

View 3 Replies View Related

Transact SQL :: How To Select Result From OPENQUERY Into A Variable

Sep 23, 2015

I have the following SQL. I need the results to be in the @NSTATUS variable. How do I do this?

DECLARE    @HISTORY_ID        INT,
        @NSTATUS    VARCHAR(20),
        @IMPORT_DATE    DATETIME,
        @TSQL            VARCHAR(8000);
SET        @HISTORY_ID = 350721;
   SET @TSQL = 'SELECT DM_IMPORT_STATUS FROM OPENQUERY(NGDEV2_LINK2, ''SELECT DM_IMPORT_STATUS from NEXTGEN.PARTY_HISTORY WHERE PARTY_HISTORY_ID = ''''' + CAST(@HISTORY_ID as nvarchar(30)) + ''''''')'
   SELECT @TSQL, @HISTORY_ID;
   EXEC (@TSQL) ;

View 7 Replies View Related

Transact SQL :: Casting A Variable To Nested Table?

Sep 24, 2015

Is there any way to convert a bind variable, which is a list of integer or string values, to nested table in MS SQL Server. I am looking for something like

CAST in Oracle, which converts a varray type column into a nested table. Do we have something like this in SQL Server.

in Oracle:

SELECT CAST(s.addresses AS address_book_t)
FROM states s
WHERE s.state_id = 111;

I have read about Table valued Parameter, but I am looking for another solution. 

View 4 Replies View Related

Transact SQL :: Determining Procedure Variable Type?

May 12, 2015

I am building as Search page whereby a user passes in a variable and depending on the variable type, different results will come back ...if the search criteria is '123 somewhere' it would be a string and we would search the address field.  If the search criteria is '123' i want to search the address field as well as the id field.that being said, in TSQL is there a way to determine if the variable coming in is a string or an int?

View 3 Replies View Related

Transact SQL :: Passing Variable In Select Statement

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

Transact SQL :: 2012 - Declare Variable With Multiple Values

Sep 28, 2015

In a t-sql 2012, I want to declare variables with multiple values and I am having problems since sql server thinks I am working with numbers when I am really working with character and bit values. Here is a copy of the sql that I am trying to use:

DECLARE @Account  varchar(100)
DECLARE @Processed bit
set @Account = '58100,98326,09897'
set @Processed ='0,1'

Thus would you show me what I can do so that the sql server knows that I want the values in the set states above to be varchar or character value for @Account and bit value for @Processed?

View 7 Replies View Related

Transact SQL :: Profile Variable To Return From Queue Table?

Jun 18, 2015

Trying to create a report... Report should show * documents on hold then depending on the "on-hold type" look in the corresponding table and SELECT a few fields. Here is what I have. Where do I SET the @profile variable to return the profile from my queue table?

DECLARe
@profilevarchar(256)
SELECT
q.[profile],q.on_hold,q.on_hold_message,q.dbc_state 
FROM
QueueASq

[code]...

View 5 Replies View Related

Transact SQL :: Using A Variable Containing Comma Separated Values In IN Clause?

Jun 12, 2015

I am try to use a variable containing comma separated values in the IN clause.

View 10 Replies View Related

Transact SQL :: Declarations Do Not Have DECLARE Keyword Prefacing Variable Identifier

Jul 20, 2015

So I was looking at the code for the proc created by Ola's script and noticed a number of variable declarations happening early on (like at line 10) but these variable declarations do not have the DECLARE keyword prefacing the variable identifier.

Then, a couple lines lower, a new batch of variables are declared, but this time with the DECLARE keyword. I was under the impression that you always needed the DECLARE. Is it a scope thing? Like related to the fact the first set of variables are before the stored proc's begin statement?

USE [master]
GO
/****** Object: StoredProcedure [dbo].[DatabaseBackup] Script Date: 7/20/2015 2:23:36 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[DatabaseBackup]

[Code] ....

View 3 Replies View Related

Transact SQL :: How To Turn Select Aggregate Function Statement Into A Variable

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

Transact SQL :: Use Print Function To Output Numeric Variable With Fixed Amount Of Leading Zeroes

Apr 23, 2015

I need to create an output from a T-SQL query that picks a numeric variable and uses the print function to output with leading zeroes if it is less than three characters long when converted to string.  For example if the variable is 12 the output should be 012 and if the variable is 3 the output should be 003.

Presently the syntax I am using is PRINT STR(@CLUSTER,3) .  But if @CLUSTER which is numeric is less than three characters I get spaces in front.

View 4 Replies View Related

Looking For A Way To Refer To A Package Variable Within Any Transact-SQL Code Included In Execute SQL Or Execute T-SQL Task

Apr 19, 2007

I'm looking for a way to refer to a package variable within any
Transact-SQL code included in either an Execute SQL or Execute T-SQL
task. If this can be done, I need to know the technique to use -
whether it's something similar to a parameter placeholder question
mark or something else.


FYI - I've been able to successfully execute Transact-SQL statements
within the Execute SQL task, so I don't think the Execute T-SQL task
is even necessary for this purpose.

View 5 Replies View Related

Scope Identity Again...

Mar 6, 2007

I have been using tis page as a reference http://forums.asp.net/thread/1511323.aspxbut i cant seem to get this to work. The above page suggests using Dim newId As Object = e.Command.Parameters("@RETURN_VALUE").Value to get the value but when i do that i get an error that Command is not a member of system.web.ui.webcontrols.formViewInsertedEventArgs Can anyone help?ThanksMatt 

View 1 Replies View Related







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