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.
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?
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
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.
---------------------------------------------------------------------- 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.
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
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?
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.
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
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
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
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.'
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 _____.
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?
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'
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?
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.
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
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.
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?
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.
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?
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.
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.
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.
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.
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.
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.
