Referencing Result Set

Nov 20, 2007

Hi



This is probably a noob question. In reference to my previous post about OLE DB and variables, I need to copy the whole result set to a table. i tried


Code Block

exec('insert into '+@tableName+' ('+@ColumnNames+') '+@curTableResult)
where @tableName is the table name, @ColumnNames are the column names and @curTableResult is the Result set produced by the Execute SQL statement before. Obviously didnt work.

How do I copy the Result set to a table?

Thanks

CoyoteM

View 7 Replies


ADVERTISEMENT

Mind Boggling / How Can You Query Self Referencing Tables? (self Referencing Foreign Keys)

Jun 15, 2006

For example, the table below, has a foreign key (ManagerId) that points to EmployeeId (primary key) of the same table.
-------Employees table--------
EmployeeID  . . . . . . . .  .  .  int
Name  .  .  .  .  .  .  .  .  .  .  .  nvarchar(50)
ManagerID  . . . . . . . .  .  .  .  int
 
If someone gave you an ID of a manager, and asked you to get him all employee names who directly or indirectly report to this manager.
How can that be achieved?

View 6 Replies View Related

Set Variable Based On Result Of Procedure OR Update Columns Fromsproc Result

Jul 20, 2005

I need to send the result of a procedure to an update statement.Basically updating the column of one table with the result of aquery in a stored procedure. It only returns one value, if it didnt Icould see why it would not work, but it only returns a count.Lets say I have a sproc like so:create proc sp_countclients@datecreated datetimeasset nocount onselect count(clientid) as countfrom clientstablewhere datecreated > @datecreatedThen, I want to update another table with that value:Declare @dc datetimeset @dc = '2003-09-30'update anothertableset ClientCount = (exec sp_countclients @dc) -- this line errorswhere id_ = @@identityOR, I could try this, but still gives me error:declare @c intset @c = exec sp_countclients @dcWhat should I do?Thanks in advance!Greg

View 4 Replies View Related

Problem Assigning SQL Task Result To A Variable - Select Count(*) Result From Oracle Connection

Dec 26, 2007



I have an Execute SQL Task that executes "select count(*) as Row_Count from xyztable" from an Oracle Server. I'm trying to assign the result to a variable. However when I try to execute I get an error:
[Execute SQL Task] Error: An error occurred while assigning a value to variable "RowCount": "Unsupported data type on result set binding Row_Count.".

Which data type should I use for the variable, RowCount? I've tried Int16, Int32, Int64.

Thanks!

View 5 Replies View Related

Table-valued User-defined Function: Commands Completed Successfully, Where Is The Result? How Can I See Output Of The Result?

Dec 11, 2007

Hi all,

I copied the following code from Microsoft SQL Server 2005 Online (September 2007):
UDF_table.sql:

USE AdventureWorks;

GO

IF OBJECT_ID(N'dbo.ufnGetContactInformation', N'TF') IS NOT NULL

DROP FUNCTION dbo.ufnGetContactInformation;

GO

CREATE FUNCTION dbo.ufnGetContactInformation(@ContactID int)

RETURNS @retContactInformation TABLE

(

-- Columns returned by the function

ContactID int PRIMARY KEY NOT NULL,

FirstName nvarchar(50) NULL,

LastName nvarchar(50) NULL,

JobTitle nvarchar(50) NULL,

ContactType nvarchar(50) NULL

)

AS

-- Returns the first name, last name, job title, and contact type for the specified contact.

BEGIN

DECLARE

@FirstName nvarchar(50),

@LastName nvarchar(50),

@JobTitle nvarchar(50),

@ContactType nvarchar(50);

-- Get common contact information

SELECT

@ContactID = ContactID,

@FirstName = FirstName,

@LastName = LastName

FROM Person.Contact

WHERE ContactID = @ContactID;

SELECT @JobTitle =

CASE

-- Check for employee

WHEN EXISTS(SELECT * FROM HumanResources.Employee e

WHERE e.ContactID = @ContactID)

THEN (SELECT Title

FROM HumanResources.Employee

WHERE ContactID = @ContactID)

-- Check for vendor

WHEN EXISTS(SELECT * FROM Purchasing.VendorContact vc

INNER JOIN Person.ContactType ct

ON vc.ContactTypeID = ct.ContactTypeID

WHERE vc.ContactID = @ContactID)

THEN (SELECT ct.Name

FROM Purchasing.VendorContact vc

INNER JOIN Person.ContactType ct

ON vc.ContactTypeID = ct.ContactTypeID

WHERE vc.ContactID = @ContactID)

-- Check for store

WHEN EXISTS(SELECT * FROM Sales.StoreContact sc

INNER JOIN Person.ContactType ct

ON sc.ContactTypeID = ct.ContactTypeID

WHERE sc.ContactID = @ContactID)

THEN (SELECT ct.Name

FROM Sales.StoreContact sc

INNER JOIN Person.ContactType ct

ON sc.ContactTypeID = ct.ContactTypeID

WHERE ContactID = @ContactID)

ELSE NULL

END;

SET @ContactType =

CASE

-- Check for employee

WHEN EXISTS(SELECT * FROM HumanResources.Employee e

WHERE e.ContactID = @ContactID)

THEN 'Employee'

-- Check for vendor

WHEN EXISTS(SELECT * FROM Purchasing.VendorContact vc

INNER JOIN Person.ContactType ct

ON vc.ContactTypeID = ct.ContactTypeID

WHERE vc.ContactID = @ContactID)

THEN 'Vendor Contact'

-- Check for store

WHEN EXISTS(SELECT * FROM Sales.StoreContact sc

INNER JOIN Person.ContactType ct

ON sc.ContactTypeID = ct.ContactTypeID

WHERE sc.ContactID = @ContactID)

THEN 'Store Contact'

-- Check for individual consumer

WHEN EXISTS(SELECT * FROM Sales.Individual i

WHERE i.ContactID = @ContactID)

THEN 'Consumer'

END;

-- Return the information to the caller

IF @ContactID IS NOT NULL

BEGIN

INSERT @retContactInformation

SELECT @ContactID, @FirstName, @LastName, @JobTitle, @ContactType;

END;

RETURN;

END;

GO

----------------------------------------------------------------------
I executed it in my SQL Server Management Studio Express and I got: Commands completed successfully. I do not know where the result is and how to get the result viewed. Please help and advise.

Thanks in advance,
Scott Chang

View 1 Replies View Related

Saving Query Result To A File , When View Result Got TLV Error

Feb 13, 2001

HI,
I ran a select * from customers where state ='va', this is the result...

(29 row(s) affected)
The following file has been saved successfully:
C:outputcustomers.rpt 10826 bytes

I choose Query select to a file
then when I tried to open the customer.rpt from the c drive I got this error message. I am not sure why this happend
invalid TLV record

Thanks for your help

Ali

View 1 Replies View Related

End Result Is Main Query Results Ordered By Nested Result

May 1, 2008

As the topic suggests I need the end results to show a list of shows and their dates ordered by date DESC.
Tables I have are structured as follows:

SHOWS
showID
showTitle

SHOWACCESS
showID
remoteID

VIDEOS
videoDate
showID

SQL is as follows:

SELECT shows.showID AS showID, shows.showTitle AS showTitle,
(SELECT MAX(videos.videoFilmDate) AS vidDate FROM videos WHERE videos.showID = shows.showID)
FROM shows, showAccess
WHERE shows.showID = showAccess.showID
AND showAccess.remoteID=21
ORDER BY vidDate DESC;

I had it ordering by showTitle and it worked fine, but I need it to order by vidDate.
Can anyone shed some light on where I am going wrong?

thanks

View 3 Replies View Related

CASE Function Result With Result Expression Values (for IN Keyword)

Aug 2, 2007

I am trying to code a WHERE xxxx IN ('aaa','bbb','ccc') requirement but it the return values for the IN keyword changes according to another column, thus the need for a CASE function.

WHERE GROUP.GROUP_ID = 2 AND DEPT.DEPT_ID = 'D' AND WORK_TYPE_ID IN ( CASE DEPT_ID WHEN 'D' THEN 'A','B','C' <---- ERROR WHEN 'F' THEN 'C','D ELSE 'A','B','C','D' END )

I kept on getting errors, like

Msg 156, Level 15, State 1, Line 44Incorrect syntax near the keyword 'WHERE'.
which leads me to assume that the CASE ... WHEN ... THEN statement does not allow mutiple values for result expression. Is there a way to get the SQL above to work or code the same logic in a different manner in just one simple SQL, and not a procedure or T-SQL script.

View 3 Replies View Related

Referencing SQLDataSource In A Sub

Nov 16, 2006

I have a SqlDataSource  - The select statement
selects the DB row with an ID that equals a querystring value.... So I
know I am only selecting one row..Now I want to create a little
Sub that grabs any field(s) of my choice and then I can assign the
value of it to a textbox on my page or a variable if I need to... Any
idea how I actually reference the fields via the SQLDataSource in a
sub? I see you can reference parameters using..SqlDataSource1.SelectParameters()I was hoping I could use something similar to get the actual DB fields like the below...SqlDataSource1.Select("MyDataBaseField").Value = TextBox.TextCan anyone help please??Thanks

View 7 Replies View Related

Referencing Sql In IF THEN ELSE Statement

Mar 13, 2007

The Background:
From page 1 a search is created and this value is converted to a querystring and given the name "id=" and passed to page 2.  Page 2 then does a lookup in the sql 2000 db useing this querystring.
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ListingDBConnectionString %>"
SelectCommand="SELECT [ID], [CompName], [Package] FROM [CDetails] WHERE ([ID] = @ID)">
<SelectParameters>
<asp:QueryStringParameter Name="ID" QueryStringField="id" Type="Int32" />
</SelectParameters>
</asp:SqlDataSource>
 
The Problem:
What I then need to do is an IF THEN ELSE statement.
IF [Package in SQL1] = 4 Then
Package 4
IF [Package in SQL1] = 3 Then
Package 3
Only how do I reference the sql value in the if statement.
 I have tried
<%IF Eval("Package") = 4 Then %> and with Bind - No good

View 4 Replies View Related

Image Referencing

May 4, 2008

Hi all, How do I return pictures along with my search results?  For example, I have a shoe database and for the results I would like one column to hold a picture of the shoe (the other columns hold Brand, Name, Price, etc)  The way I am looking at implementing this is by referencing the image and retrieving it from the server.  I'm a bit lost on how to do this though.  Any help? Thanks in advance,Nick 

View 4 Replies View Related

Self-referencing Table

Sep 1, 2007

Hi,

I'm using MS SQL 2005 Express.

CREATE TABLE Folder (
iD int NOT NULL IDENTITY (1, 1) PRIMARY KEY,
folderName varchar(50) NOT NULL,
parentFolderID int NULL
FOREIGN KEY REFERENCES Folder (iD)
)
GO

if I add an ON DELETE CASCADE to the foreign key, then i get an error... which is annoying. If a folder is deleted, then all its sub-folders should also be automatically deleted.

The error is: 'Introducing FOREIGN KEY constraint 'FK__Folder__parentFo__7D78A4E7' on table 'Folder' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.'

Anyone got any advice?

View 1 Replies View Related

Referencing To A Function..

Jul 20, 2005

I was created a function named aa.It is possible to reference to it without using the dbo. prnounce ?eg: select aa(), not select dbo.aa()thanks,Viktor

View 1 Replies View Related

Referencing Subreport Value

Nov 16, 2007

Hello,

I need to sum up a sub-report value, but I don't know the syntax to reference the subreport.

How do I reference a subreports value to sum it up?

Thanks,

View 7 Replies View Related

Trigger Referencing

Nov 28, 2007



I'm setting up a trigger that will fire off an email explaining that this entry has been updated, however I need a reference to the updated row so I only send that bit of information off.

Heres my code thus far:


ALTER TRIGGER [dbo].[completeCreation2]

ON [Database_Test].[dbo].[Projects]

AFTER UPDATE

AS

-- SEND EMAIL

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'Administration',

@recipients = 'email@email.com',

@body = 'A new project has been created view details of the project below:',

@subject = 'Project Created',

@body_format = 'TEXT',

@importance = 'High',

@query = 'Select * FROM updated';

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

END


I thought the select From updated would work but it doesn't I need a reference for that line, select * FROM _____.

Thanks

View 3 Replies View Related

Referencing Issues

Apr 17, 2008



I am trying to run a mobile device with SQL's mobile 3.5.
When I run my application I am given an error that sqlceme35.dll plus several other references cannot be found

When I try referencing them It does not allow me too.
Is there any steps I should take to make referencing allowed?

View 1 Replies View Related

Referencing Variables By Name

Nov 8, 2007

Hi there everyone,

A quick question. How do I reference variables (system and user) in SSIS by name instead of by just using a "?". The reason why I'm asking is because I'm trying to log what happens in a package to SQL, and if I cannot reference a variable by name it tries to insert variable values into incorrect fields?

An example of the code I'm trying to use can be found below:



Code Block
UPDATE dbo.History_Control_Table
SET
End_Date = GetDate()
, Result = 'Success'
, Status = NULL
, No_Of_Records_Processed = ? --This is where I want to tell it which variable to use
, Package_Name = ?
, No_Of_Records_Rejected = ?

--etc etc etc

WHERE
Package_Name = ?
AND Task_Name = 'Deals_Fact_Stg'
AND Status = 'Running'




Thanks in advance.

Chow.

View 11 Replies View Related

Foreign Key NOT Referencing PK

Sep 18, 2006

This book 'MS SQL Server 2000 Bible' says the following:

'The foreign key can reference primary keys, unique constraints, or unique indexes of any table'

I've never heard of this before, I thought that the FK always established referential integrity by referencing the PK in the parent table. Does anyone have a further explanation of this?

thx,

Kat

View 5 Replies View Related

Referencing Textboxes

Feb 7, 2008

Is there a way to reference textboxes in an SSRS table like one would for cells in Excel? For example, something like =textbox1/textbox2? I am trying to replace a spreadsheet by turning it into an automated report and need to do some horizontal formulas and when I put in the grouped total, it always defaults to averaging percetages from the rows above, which isnt a true summary in my case.

View 11 Replies View Related

Select Self Referencing Table

May 25, 2005

I have a table that holds a ParentID and the RecordID.  There is a column called IsEnabled which is a bit field indicating if a folder can be displayed or not.  0 = NO, 1 = YESThe table is for a directory structure which is virtual and displays folders on a web page.Root----- 1---------- 1-1---------- 1- 2----------------- 1-2-1----------------- 1-2-2----------------------- 1-2-2-1----------------- 1-2-3----------------- 1-2-4---------- 1- 3---------- 1- 4I need a query that will not select any children that are under a Parent that is disabled. So if   '   1- 2   ' is disabled then:---------- 1- 2----------------- 1-2-1----------------- 1-2-2----------------------- 1-2-2-1----------------- 1-2-3----------------- 1-2-4SHOULD NOT SHOW.Can anyone give me a query that will overcome this problem i have.-J

View 4 Replies View Related

Referencing Certain Excel Fields

Jul 24, 2002

Anyone know a way to pull specific fields from an excel spreadsheet.
I'm using SQL7 DTS to pull data into a SQL table. I can pull the entire
sheet but I just want certain fields.

thanks, archie

View 2 Replies View Related

Referencing A Table By Variable

Jul 18, 2000

I would like a stored proc to be fed the table name as a paramater.
I tried below

create procedure testit as

set nocount on
declare @tblnm varchar(50)
select * from @tblnm

but it get Server: Msg 170, Level 15, State 1, Procedure testit, Line 5
Line 5: Incorrect syntax near '@tblnm'.

Any idea

View 1 Replies View Related

Referencing Data From A Query

Sep 30, 2006

I'm quite new to using t-sql, so hopefully the answer to this should be fairly simple... I have the following basic stored procedure:

Quote: CREATE PROCEDURE dbo.SP_Check_Login (
@arg_UserEmail VARCHAR(255),
@arg_UserPassword VARCHAR(255))
AS

BEGIN

SELECT a.userArchived,a.userPasswordDate
FROM app_users a
WHERE a.userEmail = @arg_UserEmail
AND a.userPassword = @arg_UserPassword

END;
GO

What I want to do is some conditional statements on the query results.

i.e:
IF (userArchived = 1)
RETURN "Archived"
ELSE IF (userPasswordDate < dateadd(month,-3,getdate())
RETURN "UpdatePassword"
ELSE
RETURN "OK"

So firstly how to I reference the data returned by the query, and secondly am I on the right track with the conditional code?

Thanks, Mike

View 2 Replies View Related

Referencing Other Databases In SQL Statements

Oct 17, 2005

Hello,

I have an access database and an SQL database and using data transformation services, i want to update the access database using the SQL data.

Can anyone tell me the syntax for referencing the access database?

Is it something like: [TABLENAME].dbo.FIELDNAME ?

Just to clarify, i have

Microsoft Access Database
Table 1 (UnitHistory)

SQL Database
Table 1 (UnitHistory)

How do i reference these seperately? I want to update the microsoft access database based on the SQL database data.

Eventually i'm trying to update an access database using the data held on my SQL server. Is DTS the best way for me to acomplish this or should i use another method?

Thanks guys

View 1 Replies View Related

Referencing DataSet From Other View

May 8, 2008

Hi there

Let say i have single dataset with different view (tables/list/matrix etc) with filter on it. Is it possible referencing from one view (table/list/matrix) to another? Basically how to access the other field for instance SUM from different view and used for the other view instead.

Thank you

View 1 Replies View Related

UniqueIdentifier Self Referencing Foreign Key

May 16, 2008

I have a UniqueIdentifier as a self referencing foreign key. The pk gets set by default using the newid(). I need the foreign key to default to that same value. how can i do that? @@identity doesnt work, $rowguid doesnt work, column name doesnt work. Any ideas?

View 9 Replies View Related

Referencing Aliased Columns

Jun 17, 2008

Hi there,

I'm trying to calculate a sort of rating:

select Player,
sum(case when Event = 'S' then 1.0 else 0.0 end) as Success,
sum(case when Event in ('S', 'F') then 1.0 else 0.0 end) as Attempts,
case when Attempts > 0 then Success / Attempts else NULL end as Rating
from EventList
group by Player

It says comlumn names are invalid. Any idea?

Thanks,
Bjoern


The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

View 4 Replies View Related

Referencing A Custom Column

Feb 27, 2008

Excuse me if my terminlogy is inaccurate, i'm a .NETer that's new to SQL.

I was wondering if it's possible to reference a column in the WHERE CLAUSE that has been customily defined in the SELECT statement

for example

select employeeID, case when JobCode = 'A' then 'Accountant'
case when JobCode = 'C' then 'Consultant'
case when JobCode = 'B' then 'Biller'
End as JobType
from Employee
where JobType is not null

This does not work, and saids JobType is an invalid column name. Basically, what I want is to display the jobtype of the employee but i also want to only display the employees that are Accountants, consultants and billers.

Is this possible and what would be the best way of doing this?

I do not seem to be about to reference JobType in the WHERE statement.

View 10 Replies View Related

Referencing Surface Objects As This Or Me

Oct 23, 2007

Is there a way to refer to "current report item" using something like "this" or "me"? For example: I find myself often cutting and copying code to change the format or color of text boxes based on their value thoughout a report, and it seems like it'd be more efficient to write the formatting code once and then call it from each text box.

View 1 Replies View Related

Referencing And Renaming Tables..

Mar 2, 2007

help how to reference n rename table..

is it possible by code?

View 7 Replies View Related

Referencing A Custom Column

Feb 27, 2008

Excuse me if my terminlogy is inaccurate, i'm a .NETer that's new to SQL.

I was wondering if it's possible to reference a column in the WHERE CLAUSE that has been customily defined in the SELECT statement

for example

select employeeID, case when JobCode = 'A' then 'Accountant'
case when JobCode = 'C' then 'Consultant'
case when JobCode = 'B' then 'Biller'
End as JobType
from Employee
where JobType is not null

This does not work, and saids JobType is an invalid column name. Basically, what I want is to display the jobtype of the employee but i also want to only display the employees that are Accountants, consultants and billers.

Is this possible and what would be the best way of doing this?

I do not seem to be about to reference JobType in the WHERE statement.

View 1 Replies View Related

Referencing Textbox Values

Feb 8, 2007

Is there a way to reference a value from a textbox in a matrix? In other words I want to pull the value in the textbox that is the column header into a cell in the matrix under certain conditions.

View 3 Replies View Related

Referencing An Alias In A Where Clause

Jun 1, 2007

I have the following data set - there is more to it than whats below, I just made it easier to read and highlight my problem!



SELECT LEFT(actv.ProjID, 4) AS proj, actv.Activity, actv.TotalExpensesLB, actv.PADM
FROM dbo.xtbl_MERActv actv
WHERE LEFT(actv.projID,4) = @project OR actv.PADM = @PADM



What I want to do is have the user enter a 4 digit number (@project) which will correspond to LEFT(actv.ProjID, 4). The way it is now, if the user enters a 4 digit number, no records are returned. If the user enters a 6 digit number ( the real length of the projID), then it runs correctly and I get the records I want.



I have tried to use the alias 'proj' in the where statement, but I get an error message that it is an invalid column name.



Where am I going wrong?



Thanks in advance!

View 6 Replies View Related







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