T-SQL (SS2K8) :: Circular Variable Reference

Mar 6, 2014

I'm going around in circles trying to visualise what's happening in this code,

declare @ReturnValue int, @ProcessFlag int
exec @ReturnValue=spGetProcessFlag @ProcessFlag output
select @ReturnValue as ReturnValue, @ProcessFlag as ProcessFlag

I get the feeling that the code runs spGetProcessFlag, and assigns the value to either @ReturnValue or @ProcessFlag.

By the way - next task is to direct SSIS whether to fail package or continue running.

View 1 Replies


ADVERTISEMENT

Circular Reference To Update

Apr 25, 2008

I have a table like the following
Table=Items
Type / Name
3 / Active Item
3 / Active Item
4 / New Item
4 / New Item
5 / Old Item
5 / Old Item

How do I run a querry to update the Type Colum entrys from 5's to 3's? I have tried the following:
UPDATE Items SET Type = 3 WHERE Type = 5

The above querry passes SQL Querry analyzer, but the querry itself provides the following error: Unable to parse query text.
I think the error is due to the fact that I am using a circular reference to update the same Column that I am testing on.
MS-SQL 2005 SP-2

Any help would be FANTASTIC!

View 4 Replies View Related

DB Design Question. Circular Reference?

Jun 25, 2007

I have a hypothetical question. Suppose you have three database tables. One represents the moneyin a cash register. This money can be composed of Dollars or any amount of change. At any particular time you might have no dollars in the register but only change, or both or vice versa. To represent this I created three tables. One for Currency, Dollars and Change. My database isset up so that there is a one to many relationship between the table CashRegister and Dollars. And also a one to many relationship between CashRegister and change.
Is this alright? Or is this somehow a circular reference?
 
CashRegister-----------CurrencyID(PK)
Dollars --> this table represents dollar amounts-------DollarsId(PK)typeOfCurrnecyAmount
Change --> this table represents coins------ChangeIdAmounttypeOfCurrnecy
MoneyType --> table Indicates the type of currency, change or dollars---------typeOfCurrnecy(PK)

View 5 Replies View Related

Transact SQL :: Find Circular Reference In The Table

Jun 1, 2015

Create table #tblActvity
(
activityIDvarchar (50),
activityParentIDvarchar(50)
)
Insert into #tblActvity
SElect '1',Null

[Code] ...

--If I pass activityId 3 or 2 or 4 it should return 0 as none of the activity is circular but If I pass 5, 6 or 7 it should return 1 as they have circular reference....

I need a sql qry which will require to find a circular reference in it.....

As in above sample of data ,If I pass activityId 3 or 2 or 4 to qry it should return 0 as none of the activity is circular but If I pass 5, 6  or 7 it should return 1 as they have circular reference....

View 4 Replies View Related

T-SQL (SS2K8) :: Join 2 Reference Values In One Line

Jun 12, 2015

I have 2 tables ... order table (shipping city and destination city) and a referential city table:

1:New York
2:Chicago
3:Atlanta
etc...

In the order table the shipping address and destination address have the identity's a values, I want to write a join to show the names of the cities instead. I'm doing something like this, is there a way to do it in one select?

;with srcemkt as
(
select ID, OrderNumber, b.MarketCenterCity as 'SourceMKT',
from Orders as a
join MarketCity as b

[Code] .....

View 6 Replies View Related

T-SQL (SS2K8) :: Stored Procedure With Unavailable Server Reference

Sep 11, 2014

We have a stored procedure that makes a decision to pull records from one of two servers.

If one of these servers became unavailable but was no longer queried would the stored procedure still work? Doesn't SQL recompile stored procedures periodically?

Something like this:

If @ServerAIsDown=1
begin
select * from ServerB.dbo.MyTable
end
else
begin
select * from ServerA.dbo.MyTable
end

View 2 Replies View Related

T-SQL (SS2K8) :: Varbinary (max) Parameters In Nested Stored Procedures By Value Or Reference

Sep 15, 2014

Consider a situation where a stored procedure taking a varbinary(max) (BLOB) input parameter then calls a nested stored procedure and passes along that varbinary(max) as an input parameter to the nested stored procedure.

Is a copy of the BLOB provided to the nested stored procedure (passed by value) OR is the BLOB passed by reference.

My interest is in understanding the potential memory hit when handling large BLOBs in this environment.

For example, if the BLOB is 200MB, will SQL server need to allocate memory for a new copy each time it's passed to another stored procedure?

Looks like table type parameters are passed by reference, but I haven't been able to find any info on BLOBS in this context.

View 8 Replies View Related

How Do I Reference The Server Name In TSQL As A Variable

Dec 5, 1999

I have created a job which performs linked server inserts, the job is scheduled to run every 15min. The Job inserts to a local linked server, some data from one of our log tables. That linked server inturn gathers more data from other servers and exports that data to another application for further processing.

My problem is that I need to reference local machine (DNS Name) as a variable which can be passed along with the linked server insert. If I can get the Server name dynamically I can run this as a remote job on 2 or 20 or 200 servers and It will give the log data and the name of the source server.

QUESTION: How Do I reference the server name from within TSQL as a variable.

View 1 Replies View Related

How To Reference ADO Object Source Variable Within Script Component?

Dec 7, 2007



Hello,
I have a 'ForEach Loop Container' that does a select from a table and I use Variable Mappings to map each row set result to Package Variables in SSIS. This works fine. However, in the Data Flow, I have a Script component in which I need to access the values of those variables that have been set in the ForEach Loop Container. How do I do this?

Perhaps I'm on the wrong track but I guess I need to access the ADO Object Source variable set in the ForEach Loop Container? Through my own experimentation, I know that accessing the VariableDispenser collection only returns whatever default values that happen to be assigned to the variables in the SSIS GUI (not the values assigned during each iteration of the ForEach Loop).

Thanks,
Clive

View 16 Replies View Related

Circular Dependencies From Constraints

Jan 6, 2007

Hey!
 I am creating a kind of file browser for an application of mine. The principle is quite straight forward. It consists of folders and files. Each folder can contain other folders and files and so on. The twitch however is that i need a special root entity called site. The site is very much alike a folder but has some other properties. The site can contain folders and files.
To achieve this ive created te following tables (truncated for clearity):
####################            Sites          ##################### ID [Int]                   ## ...                         ####################
####################          Folders         ##################### ID [Int]                   ## SiteID [Int]             ## FolderID [Int]          ## ...                         ####################
####################          Files             ##################### ID [Int]                   ## SiteID [Int]             ## FolderID [Int]          ## ...                         ####################
 Both the folder table and the files table have a check constraint that ensures that either SiteID or FolderID is NULL. They WILL be part of EITHER a folder or a site. Not both!
Then i set up the foreign constraints as follows:Folders.FolderD -> Folders.IDFolders.SiteID -> Sites.IDFiles.FolderID -> Folders.IDFiles.SiteID -> Sites.ID
All constraints have cascade on delete and therefore the last of them cannot be created as it would be circular. (Wich it wont in this case, but theoreticaly its possible)
Iknow WHY this is rendering an error. But how can i work around it? Or would you suggest another design of the tables?

View 2 Replies View Related

Help - Tasks That Have Circular Dependencies

Sep 18, 2006

Hello,

I have a package, which calls a sub package to poulate a table depending on a flag in the database (using an ExecuteSQL task to return flagged table name).

The inner package populates some tables, and calculates what needs to be processed next. It sets the next flag.

However, I can't make this work in the control flow, as once the Execute package has completed, I need to start again from the top, as the flag will have changed to the next item.

I hope that I have explained this well enough.

I really need this to work, but SSIS will not let me create a circular dependency. Does anyone know a way around this, or can offer me an alternative solution??

I am getting desperate, so any suggestions will be welcome

Many thanks





View 4 Replies View Related

T-SQL (SS2K8) :: Declaring Value To A Variable

Oct 22, 2014

I have a job that runs in sql 2008r2 that declares a value to a variable. This variable is passed to a function. I need to include more values in and am unsure how to do this. @InCo could be 4,5,6,or 7.

See below.

Declare @ReportDateIN [datetime]=GETDATE(),
@InCo varchar(max) = 4,
@OutCo varchar(max) = 8,
@IncludeDetailIN [int]= 1,
@IncludeReleasedIN [int]= 1,
@IncludeHoldingIN [int]= 1

[Code] .....

View 2 Replies View Related

T-SQL (SS2K8) :: Set A Local Variable

Feb 24, 2015

declare @filename varchar(255),
@path varchar(255),
@sql varchar(8000),
@sql3 varchar(8000),

[Code].....

i need to set a Apostrophe in front of dir and at the end

View 4 Replies View Related

T-SQL (SS2K8) :: Trying To Pass A Value To A Variable

May 4, 2015

I am trying to do the following:

DECLARE @MinBoundary DateTime, @s nvarchar(max)
declare @DBName sysname, @TableName sysname
select @DBName = 'MyDB', @TableName = 'MyTable'
select @s = '
SELECT Convert(DateTime, MIN(PRV.value))

[Code] ...

It works if I print @s query and run it this way, so the query is correct):

DECLARE @MinBoundary DateTime
SELECT @MinBoundary = Convert(DateTime, MIN(PRV.value))
FROM sys.partition_functions AS PF
JOIN sys.partition_parameters AS PP
ON PF.function_id = PP.function_id

[Code] ....

View 6 Replies View Related

Circular Relationship-&&>can We Get Duplicate Records

Sep 20, 2006

Hi ,

I have two tables test1 and test2 .

Schema of table test1 is

A pk(primary key)

B

C fk (foreign key)

Schema of table test2 is

A

B pk

C fk

Table test1 and test2 have following relationships

1) test1.a pk -> test2.c fk

2) test2.b pk -> test1.c fk

Hence the two tables have circular relationship.

My question is when i query all rows of test1 based on a specific value of test1.a, can we get multiple rows in any case.

I am using following query to retrive the records:

select test1.* from A as A

inner join B on A.a=B.c

inner join A as AA on B.b= AA.c

where AA.a ='100'

Please let me know whether any duplicate row case is possible and if yes, please give any example.



Regards

View 1 Replies View Related

T-SQL (SS2K8) :: Difference In Variable Definition

Apr 23, 2014

Is there a difference in the following: DECLARE @T1 VARCHAR(50);

SET @T1 = (SELECT TOP(1) Col1 FROM MyTable); -- define variable first way
SELECT TOP(1) @t1 = Col1 FROM MyTable; --

define variable second wayI did a quick test and not finding a difference, however I was told that if nothing is returned by the query the result would be different and that the first way is better.

View 8 Replies View Related

T-SQL (SS2K8) :: Variable Declaration In A Loop

Jul 2, 2015

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] ....

View 4 Replies View Related

T-SQL (SS2K8) :: Cursor From Variable - Procedural Loop

May 8, 2014

I am using a cursor (i know - but this is actually something that is a procedural loop).

So effectively i have a table of names of stored procedures. I now have a store proc that loops around these procs and runs each one in order.

Now i am thinking i would like to be able to set the table it loops around in a variable at the start - is it possible to do this? So effectively use a tablename in a variable to use in the sql to define a cursor?

View 6 Replies View Related

T-SQL (SS2K8) :: Cursor - Declare Variable Error

Sep 9, 2014

The below cursor is giving an error

DECLARE @Table_Name NVARCHAR(MAX) ,
@Field_Name NVARCHAR(MAX) ,
@Document_Type NVARCHAR(MAX)

DECLARE @SOPCursor AS CURSOR;
SET
@SOPCursor = CURSOR FOR

[Code] ....

The @Table_Name variable is declared, If I replace the delete statement (DELETE FROM @Table_Name ) with (PRINT @table_name) it works and print the table names.

Why does the delete statement give an error ?

View 3 Replies View Related

T-SQL (SS2K8) :: Check If Variable Is Empty Or Null?

Sep 9, 2014

declare @user varchar(30) = ''
if(@user is not null or @user <> '')
BEGIN
print 'I am not empty'
END
ELSE
BEGIN
print 'I am empty'
ENd

The output should be 'i am empty' but when i execute this i am getting ' i am not empty'. even i did browse through but i don't find the difference in my logic.

View 9 Replies View Related

T-SQL (SS2K8) :: Check Variable Values Dynamically

Nov 9, 2014

I have created dynamic sql to declare variables based on columns from the table and i set values to those variable now here is the issue . i want to check the variable values how do i do that dynamically

drop table test
create table test
(
id varchar(10) not null,
col1 varchar(10) ,
col2 varchar(10)

[Code] .....

Now my next step is verify if the variable is blank or not how do i do that ?

How do i verify all of the columns one after the other .

I am after the statement like this dynamically

-- IF NOT (@col1 = '') THEN set @SQL = @SQL + '[col1] = ' + @col1 + ' '
--IF NOT (@col2 = '') THEN set @SQL = @SQL + '[col2] = ' + @col2 + ' '

I need to check if the columns are blank or not dynamically as i do not want to hard code the column names there.

View 4 Replies View Related

T-SQL (SS2K8) :: Declare Variable With Last Month Date?

Dec 16, 2014

i have a monthly production script that requires two forms of the prior month's date.'yyyymm' or '201411' and 'yyyy-mm-dd' or '2014-11-01'. The second date must have a day value of '1'. I have to use these date values in my script a half dozen times. Ive tried to declare two variables to accomodate but i can not get them correct.

I can execute the following:

SELECT LEFT(CONVERT(varchar, DATEADD(MONTH,-1,GetDate()),112),6)
---------------------------
201411

When I try this as a declared variable:

DECLARE @RPT_DTA VARCHAR
SET @RPT_DTA = CONVERT(varchar, DATEADD(MONTH,-1,GetDate()),112)
SELECT @RPT_DTA
--------------------------
2
DECLARE @RPT_DTB VARCHAR
SET @RPT_DTB = DATEPART(YEAR,DATEADD(MONTH,-1,GetDate())) + DATEPART(month,DATEADD(MONTH,-1,GetDate()))
SELECT @RPT_DTB
-----------------------------
*

View 3 Replies View Related

T-SQL (SS2K8) :: Calculation Of Total Discount With Variable

Feb 2, 2015

I need to calculate total discount on item in case when user has several discounts, and they each apply on discounted amount. I thought to have something like:

DECLARE @Disc float
SET @Disc = 0
SELECT @Disc = @Disc + (100 - @Disc) * Disc / 100
FROM UserDiscounts
WHERE UserID = 123

but, seems, it does not work.

Looking for single query without any loops?

View 4 Replies View Related

T-SQL (SS2K8) :: Passing Table Name And Date Value As Variable

Jun 10, 2015

I need building the dynamic sql . When I create below script and executed the procedure passing the table name and date values its giving me error saying "Incorrect syntax near '>'".

Create PROCEDURE sampleprocedure
@tablename AS VARCHAR(4000),
@date as date
AS
BEGIN
declare @table varchar(1000)

[Code] ....

View 9 Replies View Related

T-SQL (SS2K8) :: Get Output Of Procedure And Assign It To A Variable Used In WHERE Clause?

Mar 25, 2014

Get output of SQL Procedure and assign it to a variable used in WHERE Clause

Later I want to use this variable in my code in the WHERE Clause

Declare @ProjectNo nvarchar(10)

--Now I want to assign it to output of a storedprocedure which returns only 1 value and use it in the below SELECT query.

SELECT ID from TABLEA where Project in (@ProjectNo)

How to do it. How to assign @ProjectNo to output of storedProcedure called 'GetProjNumber'

View 1 Replies View Related

T-SQL (SS2K8) :: How To Select All Or Multiple Rows From Typed XML Variable

Apr 2, 2014

I am using xml schema that is like this:

<DetailRows>
<DetailRow>
<MonthNumber></MonthNumber>
<Amount></Amount>
</DetailRow>
</DetailRows>If my variable contains following xml document as un-typed xml

[Code] ....

However, if I use a typed xml variable that is based on above schema, I cannot use OPENXML. What is the correct way of achieving same result with a typed xml doc? I am using SS2K5.

View 1 Replies View Related

T-SQL (SS2K8) :: Inserting Multiple Records From A Single Variable?

Apr 24, 2014

I have two tables. Table 1 has column "job", table 2 has column "job" and column "item". In table table 2 there are multiple "items" for each "job"

I would like to insert all of the "items" into table 1, based on a join table1.job = table2.job

View 7 Replies View Related

T-SQL (SS2K8) :: Error Single Quote Stored In Variable

Sep 2, 2014

I'm trying to create a Character string so that I can execute dynamic SQL.

The date is going to change.

DECLARE @Select VARCHAR (50)
DECLARE @SQLQuery VARCHAR (500)
DECLARE @PreSelect CHAR (1)
DECLARE @CurrentDate Date
SET @SQLQuery = 'SELECT CAST(CAE_RDB_ENTRY_DATE as Date), *
FROM OPENQUERY(LS_RDB_DWH,'
SET @PreSelect = '''
SELECT @Preselect AS PreSelect

If I try this statement which what I really want. I would like to include the Quote with the Select.:

SET @Select = ''SELECT * FROM RDB_DWH_ASSOCIATE_ENTITY WHERE CAE_RDB_ENTRY_DATE >''

I get the following error:

Invalid object name 'RDB_DWH_ASSOCIATE_ENTITY'.

View 9 Replies View Related

T-SQL (SS2K8) :: Replacing String Values With Contents Of Variable

Dec 19, 2014

So I have the following column named String in a table:

<key>Children</key><integer>2</integer>

This of course can vary for the different records. What's the best way to replace the 2 with the contents of my variable with TSQL?

declare @children int
set @children = 4

I want to do something like this:

SELECT <key>Children</key><integer>@children</integer>

View 4 Replies View Related

T-SQL (SS2K8) :: Extract String - Variable Sizes With Breaks?

Jun 18, 2015

Row Value :

Fiscal Year: 2016(
)Budget Scenario: Main Budget (0+12)(
)Forecast Scenario: Jun (0+12) Forecast(
)Department: 400 - New York(
)YTD Period: Jun

and I need to extract 400 only... substring fails because the length of the Scenario, Forecast etc differs.

Should I just go for charindex on break() or is there a another way ?

View 3 Replies View Related

Cannot Write Logfile Error 1059 : Circular Service Dependency

Feb 14, 2007

I have a problem that looks like it has not been discussed before inthese groups.I have a simple SQLAgent job that runs sp_who (could be anything, butlet's just say sp_who for this example). I have set the jobstep towrite to an output file "T:out.txt". If the job is owned by anadmin, it runs fine and writes the output file. If it is owned by anon-admin user, it gets the following error msg:Warning: cannot write logfile t:out.txt. Error 1059 : Circularservice dependency was specified. The step failed.I know about setting up the SQLAgent CMDExec proxy account, and havedone that. In fact, both SQLAgent and the SQLAgent cmdexec proxy usethe same domain account, which is in the administrator group of thelocal server. So, I know that security is not the issue.When a simple job runs and writes to an output file, what service orservice group could it be trying to start or modify? I looked throughthe list of Services, and could not find any circular dependencies.Is there a utility to detect this? Why would running under onecontext (as an admin) be ok while the other context (non-admin on SQL,but using the same admin domain service account) fails?Thanks in advance for any info you might have.

View 3 Replies View Related

T-SQL (SS2K8) :: Store Result Of Stored Procedure Into XML / Nvarchar (max) Variable

Feb 16, 2012

I have a stored procedure that returns XML using FOR XML Explicit. I need to use the output of this procedure in another procedure, and modify the xml output before it is saved somewhere.

Say StoredProc1 is the one returning xml output and StoredProc2 needs to consume the output of StoredProc1

I declared a nvarchar(max) variable and trying to saved the result of StoredProc1

Declare @xml nvarchar(max)
EXEC @xml = StoredProc1 @Id

This doesn't work as expected as @xml doesn't get any value assigned or rather I would say

EXEC @xml = StoredProc1 @Id

outputs the entire xml whereas it should just save the xml in a variable.

View 7 Replies View Related

T-SQL (SS2K8) :: Embedding A Variable Into A Parameter When Calling A Stored Procedure

Apr 8, 2014

I want to add the result of a Select statement inside a parameter, not too sure how to pull this off, but here it is...

--Declare needed variables
Declare @NoOfApprovals Int

--Get count of approvals
Select @NoOfApprovals =
(
select NoOfApprovalsToClaim

[code]...

View 2 Replies View Related







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