Too Many Loops For Query?
Aug 17, 2006
Hi, I have below psuedo code ... it will display correct result, however, it takes a bit longer time to display all results. I think it's because we have so many loops, and each record from the loop will do a query from database. I need some advice about how to speed up the process time. Thanks in advance.
...
Do While NOT RS.EOF
SQL_1 = "SELECT * FROM TB1" ;
Set RS2 = Conn.Execute(SQL_1);
Do WHILE NOT RS2.EOF
SQL_2 = "SELECT * FROM TB2 WHERE NAME=" + RS2.FIELDS("Name");
Set RS3 = Conn.Execute(SQL_2);
DO WHILE NOT RS3.EOF
SQL_3 = "SELECT * FROM TB3 WHERE AGE=" + RS3.FIELDS("Age");
Set RS4 = Conn.Execute(SQL_3);
DO WHILE NOT RS4.EOF
Response.Write RS4.FIELDS("VAL");
loop
loop
loop
loop
...
View 1 Replies
ADVERTISEMENT
Jan 24, 2002
Does anyone know if you can use do while or do until loops in stored procedures? If so what is the syntax?
thanks
Wheels
View 1 Replies
View Related
Oct 24, 2004
Hi all.
Im trying to create a Stored Procedure that inserts multiple rows. But I can't get it to work.
Here's how I would like it to work
for test in (select myid from tblPlayers)
begin
Insert into tblMatches VALUES(test.myid, 2)
end
but obviously it does not work. Any ideas?
View 1 Replies
View Related
May 6, 2008
Can i have equvivalent loop in SQL for the following C lannguage Loop.
I am not able to generate Loop of this kind in MSSQL Store procedures.
i=10;
Do { Statement 1 ;
Statement 2 ;
i-- ';
} while i=0 ;
View 2 Replies
View Related
Aug 23, 2007
hi,
I am trying to
1)get all the names of a table that match another table
2)if count=0, then I want to insert into another table 'group' after getting its key.
I am totally lost, can somebody point me in the right direction.
how do I get the individual name so that I can insert into the group table,
I tried 'select @name=name, that gave no results'.
Thanks
while (SELECT name FROM names WHERE name in (select name from Group) and status = 'M' )=0
begin
insert into group(group_id,name,action) values (@key, name, 'play' )
end
View 5 Replies
View Related
Aug 23, 2007
hi,I am trying to1)get all the names of a table that match another table2)while count=0, then I want to insert into another table 'group'after getting its key.I am totally lost, can somebody point me in the right direction.how do I get the individual name so that I can insert into the grouptable,I tried 'select @name=name, that gave no results'.Thankswhile (SELECT name FROM names WHERE name in (select name from Group)and status = 'M' )=0begininsert into CAll(group_id,name,action) values (@key, name, 'play' )endhow do i get the individual names to insert into another table ..
View 2 Replies
View Related
Jun 27, 2007
Hi. newbie here.
Also new to coding in SQL.
Struggling with how to implement the following psuedo-code in SQL server 2000.
** Can you use more than one CURSOR variable?
If yes, when use FETCH_STATUS is it for cur1 or cur2 ??
Sample data is at the bottom.
Thanks for ANY suggestions !!
** Assume TABLE 1 is sorted by Record_Type, Order_no, Order_line_no
************************************************** ***
dim @rectyp
dim @ord#
dim @lin#
Fetch (?) 1st record in TABLE1
While Still Records in TABLE1
Set sub_line# = 0
set @rectyp = Record_Type,
set @ord# = Order_no,
set @lin# = Order_line_no
while @rectyp = Record_Type and
@ord# = Order_no and
@lin# = Order_line_no
Set sub_line# = sub_line# + 1
update TABLE1 set line_ctr = sub_line#
get next record
end inner WHILE
end outer WHILE
************************************************** ****************************
Sample data :
Data as it currently exists:
Record_type ......Order No......Order line no ......Line Ctr
OP.....................458001................5.... ...............0
OP .....................458001..............5 .................. 0
OP..................... 458001..............5..................0
OP .....................458001..............5........ ..........0
OP.....................458191..............1 ..................0
OP.....................458191..............1 .................. 0
OP..................... 458308..............73..................0
OP .....................458308..............73....... ........... 0
OP.....................458308..............73..... .............0
OP.....................458308..............73..... .............0
Want data to look like this after executing code:
Record_type ......Order No......Order line no ......Line Ctr
OP.....................458001................5.... ...............1
OP .....................458001..............5 .................. 2
OP..................... 458001..............5..................3
OP .....................458001..............5........ ..........4
OP.....................458191..............1 ..................1
OP.....................458191..............1 .................. 2
OP..................... 458308..............73..................1
OP .....................458308..............73....... ........... 2
OP.....................458308..............73..... .............3
OP.....................458308..............73..... .............4
************************************************** *********************
View 2 Replies
View Related
Oct 26, 2007
I have a stored procedure that I am trying to write. I want it to Grab a list of ids from one table, then loop through that list and select a group of records from a second table based on the first list. The 2nd list can have 1 + records per item from the first list. With in that record, I need to check the values in 2 fields, if the values = something I update a third field and move on to the next. So I need to be able to loop thru the second set also. Currently I have a cursor with in a cursor, but was told that was slow and not a good idea, so I am trying to figure out what other options I have.
Thanks in Advance
Swoozie
View 9 Replies
View Related
Feb 21, 2008
Hello all.
I'm mostly a VB.Net developer. I've been working on a intranet app that allows poeple in our company to self regisiter for access to Tools/Systems.
The data stucture: 1 Request with many Users requesting access.. Also on the same request, many applications requested for those same people.
After the application routes some 'Manager Reviews' and Updated the tblRequest, approving the request, I combine the Information into a tblRegistrations.
So there is a Matrix created.
The Users requested are (User1, User2, UserX....)
The Apps requested are (App1, App2, Appx....)
I created a Stored Proc that reads the List of USers, and inserts a record into tblRegistered for each App that was requested.
User1, App1
User1, App2
User1, App3
User1, Appx
User2, App1
User2, App2
User2, App3
User2, AppX
UserX, AppX
...., ....
I user 2 cursors, Nested.
The outer Cursor is the List of Users, and the inner Cusros is the list apps.
I appreciate if somone can show me how to do this with some other looping structure, or if not, examine the Decalrative statement of the Cursor, and define the propteries to make it most efficient, ie Static Local Forward Only, ect.
As I said, I don't DB Developer Experience.
Below is the copy of the Working Stored Proc:
ALTER PROCEDURE [dbo].[sp_Insert_Registration]
-- Add the parameters for the stored procedure here
@Request_IDINT
,@SessionIDnvarchar(150)
AS
DECLARE @Request_user_FullName nvarchar(150)
DECLARE @Request_User_IonName nvarchar(150)
DECLARE @Request_App_ID INT
DECLARE @Request_App_Role nvarchar(50)
DECLARE @Reg_Date DateTime
BEGIN TRY
--Local Scalar Values
Select @Reg_Date=Request_Date From dbo.tblRequest Where Request_ID=@Request_ID
--First Cursor
DECLARE curRequest_Users CURSOR LOCAL STATIC FOR
SELECT Request_User_FullName, Request_User_IonName
From dbo.tblRequest_Users
Where Request_ID =@Request_ID AND request_User_Session_ID=@SessionID
Open curRequest_Users
--Second Cursor
DECLARE curRequest_Applications CURSOR LOCAL Static FOR
SELECT Request_App_ID, Request_App_Role
From dbo.tblRequest_Applications
Where Request_ID =@Request_ID
AND Request_Apps_SessionID=@SessionID
FETCH curRequest_Users INTO @Request_user_FullName, @Request_User_IonName
WHILE @@FETCH_STATUS = 0
BEGIN
--Insert the Row Into tblRegistrations
--Need to get the Application ID's the User(s) Has Been Approved For
Open curRequest_Applications
FETCH curRequest_Applications INTO @Request_App_ID, @Request_App_Role
WHILE @@FETCH_STATUS = 0
BEGIN
Insert Into dbo.tblRegistrations
(Request_ID
,FUllName
,IonName
,Application_ID
,Application_Role
,Reg_Date
,Approved
,Approval_Date
)
Values (
@Request_ID
,@Request_user_FullName
,@Request_User_IonName
,@Request_App_ID
,@Request_App_Role
,@Reg_Date
,'True'
,getdate()
)
FETCH curRequest_Applications INTO @Request_App_ID, @Request_App_Role
END
--Close the Inner Cursor
CLOSE curRequest_Applications
FETCH curRequest_Users INTO @Request_user_FullName, @Request_User_IonName
END
DEALLOCATE curRequest_Applications
CLOSE curRequest_Users
DEALLOCATE curRequest_Users
END TRY
BEGIN CATCH
-- Execute error retrieval routine.
EXECUTE usp_GetErrorInfo;
END CATCH;
View 2 Replies
View Related
Jan 29, 2007
Can somebody please tell me how can i write a tsql statement in sql server 2000.
Same time how can i get the last digit of a inteager variable through tsql .What i want is to write 'right(intVariable,4) which is in vb .I want that in sql server 2000
Thank you
View 8 Replies
View Related
Apr 2, 2008
I brought my server to it's knees by creating 499.9GB of transaction log on a 500GB drive. Oops.
The db recovery model is SIMPLE.
I want to loop through some code, and minimize the transaction log file size. My second query here has an explicit transaction. Assume this code will loop about....1/2 Billion times. Is one (or both) of these going to record ALL 500,000,000 update statements in the Transaction Log (remember SIMPLE recovery)? Will the second one of these record a single transaction 500,000,000 time but not overwhelm the Log file due to simple recovery?
Any thoughts anyone?
Code Snippet
declare @i bigint
select @i = min(ID) from <MyTable>
While @i is not null
begin
update <MyTable> set <SomeField> = <SomeValue> where ID = @i
select @i = min(id) from <MyTable> where ID > @i
end
Code Snippet
declare @i bigint
select @i = min(ID) from <MyTable>
While @i is not null
begin
BEGIN TRANACTION
update <MyTable> set <SomeField> = <SomeValue> where ID = @i
COMMIT
select @i = min(id) from <MyTable> where ID > @i
end
View 1 Replies
View Related
Jan 20, 2008
How can I create a query and loop through all its records in a stored procedure?
View 2 Replies
View Related
Jun 2, 2004
Hi, I'm trying to call a stored procedure in a for loop within an ASPX page. My code runs fine but it seems to skip over the function which uses the stored procedure. The procedure basically copies files from the the client to the server. I have the upload portion working which physically copies the files from the client to server but when it comes to copying the path into a field in a table it totally skips it.
Some one suggested that the for loop is operating too fast for the stored procedure to work.
Any suggestions
View 1 Replies
View Related
Mar 18, 2008
I am trying to figure out an efficient way of comparing two tables of identical structure and primary keys only I want to do a join where one of the tables reveals values for records which have been modified and/or updated.
To illustrate, I have two tables in the generic form:
id-dt-val
For which the 'val' in table 2 could be different from the 'val' in table 1 - for a given id-dt coupling that are identical in both tables.
Does anyone know of an efficient way I could return all id-dt couplings in table 2 which have values that are different from those with the same id-dt couplings in table 1?
NOTE: I am asking this because I am trying to avoid explicit comparisons between the 'val' columns. The tables I am working with in actuality have roughly 900 or so columns, so I don't want this kind of a monster query to do (otherwise, I would simply do something like where a.id = b.id and a.dt = b.dt and a.val <> b.val) - but this won't do in this case.
As a sample query, I have the following script below. When I attempt the where not exists, as you might expect, I only get the one record in which the id-dt coupling is different from those in table 1, but I'm not sure how to return the other records where the id-dt coupling is the same in table 1 but for where modified values exist:
create table #tab1
(
id varchar(3),
dt datetime,
val float
)
go
create table #tab2
(
id varchar(3),
dt datetime,
val float
)
go
insert into #tab1
values
('ABC','01/31/1990',5.436)
go
insert into #tab1
values
('DEF','01/31/1990',4.427)
go
insert into #tab1
values
('GHI','01/31/1990',7.724)
go
insert into #tab2
values
('XYZ','01/31/1990',3.333)
go
insert into #tab2
values
('DEF','01/31/1990',11.111)
go
insert into #tab2
values
('GHI','01/31/1990',12.112)
go
select a.* from #tab2 a --Trouble is, this only returns the XYZ record
where not exists
(select b.* from #tab1 b where a.id = b.id and a.dt = b.dt)
go
drop table #tab1
drop table #tab2
go
I really dont' want to have to code up a loop to do the value by value comparison for inequality, so if anyone knows of an efficient set-based way of doing this, I would really appreciate it.
Any advice appreciated!
-KS
View 7 Replies
View Related
Nov 16, 2007
I need help creating the following report.
I need to modify the folliwng report to produce a weekly version of it.
To get a weekly version, simply find the first day of the week for the @dtm1 value :
should be something like @firstofweek=dateadd(day,(datepart(dw,@dtm1)*-1)+1,@dtm1) to get the Sunday date
I think i will then then need to loop through from firstofweek to last of week (last of week will be the Saturday)
would suggest having a dayofweek column on your temporary table.
CREATE PROCEDURE rpt_siteMealListWeekly
@dtm1 AS DATETIME
,@cmb1 AS VARCHAR(100)
WITH ENCRYPTION
AS
--DECLARE @dtm1 AS DATETIME
DECLARE @siteid as integer
SELECT @siteid=siteid from site where sitename=@cmb1
--SELECT @dtm1='2007-09-13',@siteid=1
--select convert (char(11),@dtm1,113)
DECLARE @mybit AS INTEGER
SET @mybit=10
SELECT @mybit = CASE datepart(dw,@dtm1)
WHEN 1 THEN 1 -- 'Sunday'
WHEN 2 THEN 2 -- 'Monday'
WHEN 3 THEN 4 -- 'Tuesday'
WHEN 4 THEN 8 -- 'Wednesday'
WHEN 5 THEN 16 -- 'Thursday'
WHEN 6 THEN 32 -- 'Friday'
WHEN 7 THEN 64 -- 'Saturday'
END
CREATE TABLE #tmp_table(
childid INTEGER null
,br BIT null
,di BIT null
,te BIT null
,type integer default 0
)
INSERT #tmp_table
SELECT DISTINCT sa.childid
,CASE WHEN sha.br & @mybit>0 THEN 1 ELSE 0 END
,CASE WHEN sha.di & @mybit>0 THEN 1 ELSE 0 END
,CASE WHEN sha.te & @mybit>0 THEN 1 ELSE 0 END
,0
FROM
sessionAttendance sa
,simplehoursassignment sha
,session s
,child c
WHERE
sha.childid=sa.childid
AND
sha.siteid=sa.siteid
AND
s.siteid=sa.siteid
AND
c.siteid = sa.siteid
AND
c.childid = sa.childid
AND
sa.siteid=@siteid
AND
s.identityid=sa.identityid
AND
s.dayofweek=datepart(dw,@dtm1)
AND
s.siteid=sa.siteid
AND
@dtm1 between cast(floor(cast(sa.datefrom as float))as smalldatetime) and cast(floor(cast(sa.dateto as float))as smalldatetime)
AND
sa.userdefid=0
AND
(
--check not a company holiday
not exists
(select
1
from
companyholidays ch
where
siteID=@siteID
and
@dtm1 between cast(floor(cast(ch.datefrom as float))as smalldatetime)
and
cast(floor(cast(ch.dateto as float))as smalldatetime)
)
)
and
(
sa.childid in
View 3 Replies
View Related
Sep 26, 2006
hi,
My problem is basically i need to call a stored proc for each entry in a table, i.e, basically a for loop calling stored procs with parameter coming from the table. I know two ways of doing this .. using cursor and using while loop with temp table. I dont like both approaches. Is there any good practice for this situation..
declare mycur cursor fast_forward for select ID from sometable
open mycur
FETCH NEXT FROM mycur
INTO @AID
WHILE @@FETCH_STATUS = 0
begin
exec dbo.storedproc @AID
FETCH NEXT FROM mycur INTO @AID
end
CLOSE mycur
DEALLOCATE mycur
View 13 Replies
View Related
Jan 11, 2006
I have a problem when using nested loops in my Control Flow. The package contains an outer Foreach Loop using the Foreach File Enumerator which in my test case will loop over two files found in a directory. Inside this loop is another Foreach Loop using the Foreach Nodelist Enumerator. Before entering the inner loop a variable, xpath, is set to a value that depends on the current file, i e /file[name = '@CurrentFileName']/content. The Nodelist Enumerator is set to use this variable as its OuterXPATHString. Now, this is what happens:
First Iteration:
The first file is found and the value of xpath = /file[name = 'test1.txt']/content. When the inner loop is entered it iterates over the content elements under the file with name test1.txt as expected.
Second Iteration:
The second file is found and the value of xpath = /file[name = 'test2.txt']/content. When the inner loop is entered it unexpectedly still iterates over the content elements under the file with name test1.txt.
My question is: Should it not be possible to change the loop condition of an inner loop in an outer loop such that the next time it is entered it will be done based on the new condition? It seems that the xpath variable is read once, the first time, and never again. If that is the case, does anyone know of a workaround?
Regards,
Lars Rönnbäck
View 8 Replies
View Related
Jun 29, 2006
It would appear that if a Child package is called more than once from a Parent using the 'Execute Package' task, then after the first execute the Parent Package Variables are not applied to child package. I.E we build dimensions in a master database and these are then loaded to a number of topic specific datamarts. We simply pass Parent variables to the child that hold source & target connection strings, the first time the package is called the correct database is accessed, subsequent Executes ignore the variables and use the original values. Manipulating the (our) event queue to run the package once results in the correct behaviour
Are packages cached when they are called from a Parent? if so is there a flag that I have missed to force a reload each time a child is executed?
This has just become a big problen for us so any guidance would greatly appreciated.
Paul
View 6 Replies
View Related
Oct 9, 2006
The problem:
I have 2 tables, with a one to many relationship - lets say customers, and order items.
Each order record has a field that is meant to be a comma delimited list (they are reference numbers) that is driven by the quantity field. So, say in the order record, an item has a quantity of 3. The reference number will look like this:
1, 2, 3
And if the next order item for that customer has a quantity of 4, the reference number value is
4, 5, 6, 7
And the final item with quantity of 2:
8, 9
Reference numbers can either be auto assigned (and are in my web application) or manually set. If manually set they will NOT be numeric.
In my web application, it is possible for users to return to a customer's order and edit a line item. My problem is when users changes the quantity of an item, and I have to reset the reference numbers.
If the quantity of line item 2 changes from 4 to 3, I need to reset all the values for that, and any other, order item that comes after it:
4, 5, 6 (2nd)
7,8 (3rd with same quantity of 2).
I felt a cursor would be the best way to handle this. But I am having trouble re-assigning my variable to be the next number in the series when the cursor is running.
This is what I have so far. The print lines and hard coded values are for debugging purposes only.
DECLARE @NumberingType varchar(10)
DECLARE @TotalSum int
DECLARE @DoorLineItemID int
DECLARE @Quantity int
DECLARE @SeedInt int
SET @SeedInt = 1
SELECT @TotalSum = SUM(Quantity) FROM DoorLineItems WHERE UniversalOrderID = 12345
DECLARE UpdateRefCursor CURSOR FOR
SELECT DoorLineItemID, Quantity FROM DoorLineItems WHERE UniversalOrderID = 12345 AND NumberingType = 1
OPEN UpdateRefCursor
FETCH NEXT FROM UpdateRefCursor INTO @DoorLineItemID, @Quantity
DECLARE @RefNumberLine varchar(1024)
SET @RefNumberLine = ''
WHILE @@FETCH_STATUS = 0
BEGIN
WHILE @SeedInt <= @Quantity
BEGIN
SET @RefNumberLine = @RefNumberLine + CONVERT(varchar, @SeedInt, 101) + ', '
SET @SeedInt = @SeedInt + 1
END
PRINT @RefNumberLine
SET @SeedInt = @Quantity + @SeedInt
PRINT 'new seed: ' + CONVERT(varchar, @SeedInt, 101) + 'Quantity ' + CONVERT(varchar, @Quantity + @SeedInt, 101)
FETCH NEXT FROM UpdateRefCursor INTO @DoorLineItemID, @Quantity
END
CLOSE UpdateRefCursor
DEALLOCATE UpdateRefCursor
This returns the same delimited string for X number of items. So I'm getting this:
1,2,3
1,2,3
1,2,3
When I really want the results described above.
What am I doing wrong?
Thanks!
View 2 Replies
View Related
Mar 20, 2000
I need a stored proc to kill spids, but the following sproc loops infinitely with the same [correct] spid being printed out. What am I doing wrong?
The select statement, when I execute it via the query grid, returns the correct and finite number of spids.
Any help greatly appreciated.
Judith
CREATE PROCEDURE rasp_KillDBProcess
@dbname varchar(128)
AS
declare @KillSpid smallint
declare @SQL varchar(1000)
--
declare DBCursor cursor Forward_only for SELECT distinct l.spid
FROM master.dbo.syslocks l INNER JOIN
master.dbo.sysdatabases d ON l.dbid = d.dbid
WHERE (d.name = N'coj_pcisdata')
open DBCursor
--
Fetch next from DBCursor into @Killspid
--
While (@@Fetch_status <> -1)
Begin
If (@@Fetch_status <> -2)
begin
print 'spid = ' + cast(@killspid as varchar(12))
--exec ('kill ' + @killspid)
end
--
end
Fetch next from DBCursor into @Killspid
--
close dbcursor
deallocate dbcursor
print 'end'
return
View 1 Replies
View Related
Sep 28, 2005
The trick is to use a pivot tableCheck out the code herehttp://sqlservercode.blogspot.com/2...ops-in-sql.html
View 3 Replies
View Related
Apr 24, 2015
Does fetch status in nested loops conflict?I have a script that should output a cluster record line and for each cluster record it must create a line for each household in the cluster. All the data is pulled from one table, 'LFS_APRIL_2015.dbo.LISTING'.This is the script:
--VARIABLE DECLARATION
DECLARE @CLUSTER FLOAT, @HOUSEHOLD_NUMBER FLOAT, @FULL_ADDRESS CHAR(50), @HEAD_NAME CHAR(24)--CLUSTER LOOP
DECLARE CLUSTER_CURSOR CURSOR FOR
SELECT [LFS_APRIL_2015].[dbo].[LISTING].CLUSTER
from [LFS_APRIL_2015].[dbo].[LISTING] where CLUSTER IS NOT NULL and DISTRICT = 1
OPEN CLUSTER_CURSOR
[code]...
It appears however that the clusters are being repeated.
View 5 Replies
View Related
Dec 19, 2003
I'm running a query, actually its an insert that works when using the TSQL below.
However when I try to use the debugger to step through and using the exact same values as those below I get the following error:
[Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification
Its Killing me because everything else works, but this. Can somebody help.
DECLARE @NoteID INT,-- NULL OUTPUT,
@Note_Description NVARCHAR(3000),-- = NULL,
@Date DateTime,-- = NULL OUTPUT,
@ByWho NVARCHAR(30),-- = NULL,
@FK_Action_Performed NVARCHAR(40),-- = NULL,
@FK_UserID INT,-- = NULL,
@FK_JobID INT,-- = NULL,
@Job_Date DateTime,-- = NULL,
@Start DateTime,-- = NULL,
@Finish DateTime,-- = NULL,
@BeenRead NVARCHAR(10),-- = NULL
@FK_UserIDList NVARCHAR(4000)-- = NULL
--SET @NoteID = 409 --NULL OUTPUT,
SET @Note_Description = 'Tetsing'
--SET @Date DateTime = NULL OUTPUT,
SET @ByWho = 'GeorgeAgaian'
SET @FK_Action_Performed = 'Worked hard'
SET @FK_UserID = 5
SET @FK_JobID = 29
SET @Job_Date = 28/01/03
SET @Start = '1:00:20 PM'
SET @Finish = '1:00:20 PM'
SET @BeenRead = 'UnRead'
SET @FK_UserIDList = '1,2,3'
--AS
--SET NOCOUNT ON
SET NOCOUNT ON
SET XACT_ABORT ON
BEGIN TRANSACTION
SET @Date = GETDATE()
-- Insert Values into the customer table
INSERT Note (Note_Description,
Date,
ByWho,
FK_Action_Performed,
FK_UserID,
FK_JobID,
Job_Date,
Start,
Finish)
SELECT --@NoteID,
@Note_Description,
@Date,
@ByWho,
@FK_Action_Performed,
@FK_UserID,
@FK_JobID,
@Job_Date,
@Start,
@Finish
-- Get the new Customer Identifier, return as OUTPUT param
SELECT @NoteID = @@IDENTITY
-- Insert new notes for all the users that the note pertains to, in this case this will be by the assigned
-- users.
IF @FK_UserIDList IS NOT NULL
EXECUTE spInsertNotesByAssignedUsers @NoteID, @FK_UserIDList
-- Insert New Address record
-- Retrieve Address reference into @AddressId
-- EXEC spInsertForUserNote
-- @FK_UserID,
--@NoteID,
-- @BeenRead
-- @Fax,
-- @PKId,
-- @AddressId OUTPUT
COMMIT TRANSACTION
--------------------------------------------------
GO
View 1 Replies
View Related
May 28, 2008
ok can someone tell me why i get two different answers for the same query. (looking for last day of month for a given date)
SELECT DATEADD(ms, - 3, DATEADD(mm, DATEDIFF(m, 0, CAST('12/20/2006' AS datetime)) + 1, 0)) AS Expr1
FROM testsupplierSCNCR
I am getting the result of 01/01/2007
but in query analizer I get the result of
12/31/2006
Why the different dates
View 4 Replies
View Related
Jan 22, 2001
Hi,
I get this error dialog when I try to open all the rows of any table from Enterprise manager..
Any help would be really appreciated..
Thanks,
-Srini.
View 1 Replies
View Related
May 24, 2007
SQL Server 2005 9.0.3161 on Win 2k3 R2
I receive the following error:
"Error: 8624, Severity: 16, State: 1 Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services."
I have traced this to an insert statement that executes as part of a stored procedure.
INSERT INTO ledger (journal__id, account__id,account_recv_info__id,amount)
VALUES (@journal_id, @acct_id, @acct_recv_id, @amount)
There is also an auto-increment column called id. There are FK contraints on all of the columns ending in "__id". I have found that if I remove the contraint on account__id the procedure will execute without error. None of the other constraints seem to make a difference. Of course I don't want to remove this key because it is important to the database integrity and should not be causing problems, but apparently it confuses the optimizer.
Also, the strange thing is that I can get the procedure to execute without error when I run it directly through management studio, but I receive the error when executing from .NET code or anything using ODBC (Access).
View 5 Replies
View Related
Mar 28, 2007
Hey, i've written a query to search a database dependant on variables chosen by user etc etc. Opened up a new sqldatasource, entered the query shown below and went on to the test query page. Entered some test variables, everything works as it should do. Try to get it to show in a datagrid on a webpage - nothing. No data shows.
SELECT dbo.DERIVATIVES.DERIVATIVE_ID, count(*) AS Matches
FROM dbo.MAKES INNER JOIN
dbo.MODELS ON dbo.MAKES.MAKE_ID = dbo.MODELS.MAKE_ID INNER JOIN
dbo.DERIVATIVES ON dbo.MODELS.MODEL_ID = dbo.DERIVATIVES.MODEL_ID INNER JOIN
dbo.[VALUES] ON dbo.DERIVATIVES.DERIVATIVE_ID = dbo.[VALUES].DERIVATIVE_ID INNER JOIN
dbo.ATTRIBUTES ON dbo.[VALUES].ATTRIBUTE_ID = dbo.ATTRIBUTES.ATTRIBUTE_ID
WHERE ((ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID1 and (@VAL1 is null or VALUE = @VAL1)) or
(ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID2 and (@VAL2 is null or VALUE = @VAL2)) or
(ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID3 and (@VAL3 is null or VALUE = @VAL3)) or
(ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID4 and (@VAL4 is null or VALUE = @VAL4)) )
GROUP BY dbo.DERIVATIVES.DERIVATIVE_ID
HAVING count(*) >= CASE WHEN @VAL1 IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN @VAL2 IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN @VAL3 IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN @VAL4 IS NOT NULL THEN 1 ELSE 0 END -2
ORDER BY count(*) DESC
Here is the page source
<%@ Page Language="VB" MasterPageFile="~/MasterPage.master" Title="Untitled Page" %>
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:DevConnectionString1 %>"
SelectCommand="	SELECT dbo.DERIVATIVES.DERIVATIVE_ID, count(*) AS Matches 	FROM dbo.MAKES INNER JOIN 				 dbo.MODELS ON dbo.MAKES.MAKE_ID = dbo.MODELS.MAKE_ID INNER JOIN 				 dbo.DERIVATIVES ON dbo.MODELS.MODEL_ID = dbo.DERIVATIVES.MODEL_ID INNER JOIN 				 dbo.[VALUES] ON dbo.DERIVATIVES.DERIVATIVE_ID = dbo.[VALUES].DERIVATIVE_ID INNER JOIN 				 dbo.ATTRIBUTES ON dbo.[VALUES].ATTRIBUTE_ID = dbo.ATTRIBUTES.ATTRIBUTE_ID 	WHERE ((ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID1 and (@VAL1 is null or VALUE = @VAL1)) or 		 (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID2 and (@VAL2 is null or VALUE = @VAL2)) or 		 (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID3 and (@VAL3 is null or VALUE = @VAL3)) or 		 (ATTRIBUTES.ATTRIBUTE_ID = @ATT_ID4 and (@VAL4 is null or VALUE = @VAL4)) ) 	GROUP BY dbo.DERIVATIVES.DERIVATIVE_ID 	HAVING count(*) >= CASE WHEN @VAL1 IS NOT NULL THEN 1 ELSE 0 END + 									 CASE WHEN @VAL2 IS NOT NULL THEN 1 ELSE 0 END + 									 CASE WHEN @VAL3 IS NOT NULL THEN 1 ELSE 0 END + 									 CASE WHEN @VAL4 IS NOT NULL THEN 1 ELSE 0 END -2 	ORDER BY count(*) DESC ">
<SelectParameters>
<asp:ControlParameter ControlID="DropDownList1" Name="ATT_ID1" PropertyName="SelectedValue" />
<asp:ControlParameter ControlID="TextBox1" Name="VAL1" PropertyName="Text" />
<asp:Parameter Name="ATT_ID2" />
<asp:Parameter Name="VAL2" />
<asp:Parameter Name="ATT_ID3" />
<asp:Parameter Name="VAL3" />
<asp:Parameter Name="ATT_ID4" />
<asp:Parameter Name="VAL4" />
</SelectParameters>
</asp:SqlDataSource>
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:DevConnectionString1 %>"
SelectCommand="SELECT * FROM [ATTRIBUTES]"></asp:SqlDataSource>
<br />
<asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="SqlDataSource2"
DataTextField="ATTRIBUTE_NAME" DataValueField="ATTRIBUTE_ID">
</asp:DropDownList>
<asp:TextBox ID="TextBox1" runat="server" AutoPostBack="True"></asp:TextBox><br />
<br />
<br />
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="DERIVATIVE_ID"
DataSourceID="SqlDataSource1">
<Columns>
<asp:BoundField DataField="DERIVATIVE_ID" HeaderText="DERIVATIVE_ID" InsertVisible="False"
ReadOnly="True" SortExpression="DERIVATIVE_ID" />
<asp:BoundField DataField="Matches" HeaderText="Matches" ReadOnly="True" SortExpression="Matches" />
</Columns>
</asp:GridView>
</asp:Content>
AFAIK I have configured the source to pick up the dropdownlist value and the textbox value (the text box is autopostback).
Am i not submitting the data correctly? (It worked with a simple query...just not with this one). I have tried a stored procedure which works when testing just not when its live on a webpage.
Please help!
(Visual Web Devleoper 2005 Express and SQL Server Management Studio Express)
View 4 Replies
View Related
Aug 5, 2014
I have the following code.
SELECT _bvSerialMasterFull.SerialNumber, _bvSerialMasterFull.SNStockLink, _bvSerialMasterFull.SNDateLMove, _bvSerialMasterFull.CurrentLoc,
_bvSerialMasterFull.CurrentAccLink, _bvSerialMasterFull.StockCode, _bvSerialMasterFull.CurrentAccount, _bvSerialMasterFull.CurrentLocationDesc,
_bvSerialNumbersFull.SNTxDate, _bvSerialNumbersFull.SNTxReference, _bvSerialNumbersFull.SNTrCodeID, _bvSerialNumbersFull.SNTransType,
_bvSerialNumbersFull.SNWarehouseID, _bvSerialNumbersFull.TransAccount, _bvSerialNumbersFull.TransTypeDesc,
[code]...
However, as you can see, the original select query is run twice and joined together.What I was hoping for is this to be done in the original query without the need to duplicate the original query.
View 2 Replies
View Related
Jun 15, 2007
I'm trying to find the command to open up an odbc conection inside sql2005 express. I only have ues of an odbc connector, we're conection to remedy. We will eventually be using stored procedures to extract the data we need from remedy and doing additional data crunching. I'm a foxpro programmer so once I get the correct syntax for making the odbc connector I shold be ok. Also I need a really good advanced book on sql2005. The type of book that would have my odbc answer. I've spent all morning trying to find this information and was unable to.
Thanks in advance
Daniel Buchanan.
If this was the wrong forum to post this on, please move this question to the correct one. I need this answer soon.
View 1 Replies
View Related
Jul 19, 2015
We have a issue with a MDS server that have been over us for a couple of days, the original error msg from SQL Server Engine is the one "The query processor could not produce a query plan" but the ones we get on the Excel-Addin are "Sequece contains no elements" or "The value cannot be null" T
• Using Microsoft SQL Server 2012 (SP1) - 11.0.3393.0 (X64) for 6months on this server without issues
• Two weeks ago we started to have 2 errors: "Sequence Contains No Elements" | "The Value Cannot Be Null"
• We are using the last version of Excel Add-in
• We try to reinstall the MDS feature
• If I backup/restore MDS database to other server it works
• We updated to SQL 2012 SP2 + CU4 but the error persisted ...
Looking at the MDSTraceLog we are routed to the this msg
SQL Error Debug Info: Number: 8624, Message: Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services., Server: bbdvsql03inst01, Proc: udpMetadataEntityGetDetailsXML, Line: 28
At line 28 udpMetadataEntityGetDetailsXML is calling udfMetadataEntityGetDetailsXML … and here is where we stopped
** Error found when try to get data from a entity using Excel add-in **
===================================
Sequence contains no elements
------------------------------
Program Location:
at Microsoft.MasterDataServices.AsyncEssentials.AsyncResultBase.EndInvoke()
at Microsoft.MasterDataServices.ExcelAddInCore.AsyncProviderBase`1.EndOperation(IAsyncResult ar)
[code]....
View 3 Replies
View Related
Jun 26, 2015
how do I get the variables in the cursor, set statement, to NOT update the temp table with the value of the variable ? I want it to pull a date, not the column name stored in the variable...
create table #temptable (columname varchar(150), columnheader varchar(150), earliestdate varchar(120), mostrecentdate varchar(120))
insert into #temptable
SELECT ColumnName, headername, '', '' FROM eddsdbo.[ArtifactViewField] WHERE ItemListType = 'DateTime' AND ArtifactTypeID = 10
--column name
declare @cname varchar(30)
[code]...
View 4 Replies
View Related
Sep 22, 2015
-- The 3rd query uses an incorrect column name in a sub-query and succeeds but rows are incorrectly qualified. This is very DANGEROUS!!!
-- The issue exists is in 2008 R2, 2012 and 2014 and is "By Design"
set nocount on
go
if object_id('tempdb.dbo.#t1') IS NOT NULL drop table #t1
if object_id('tempdb.dbo
[code]....
This succeeds when the invalid column name is a valid column name in the outer query. So in this situation the sub-query would fail when run by itself but succeed with an incorrectly applied filter when run as a sub-query. The danger here is that if a SQL Server user runs DML in a production database with such a sub-query which then the results are likely not the expected results with potentially unintended actions applied against the data. how many SQL Server users have had incorrectly applied DML or incorrect query results and don't even know it....?
View 2 Replies
View Related
Apr 30, 2007
Hello everybody,
I'm developing a report using the following structure :
declare @sql as nvarchar(4000)
declare @where as nvarchar(2000)
set @sql = 'select ....'
If <conditional1>
begin
set @where = 'some where'
end
If <conditional2>
begin
set @where = 'some where'
end
set @sql = @sql + @where
exec(@sql)
I run it in query analyser and works fine, but when I try to run in Reporting Services, Visual studio stops responding and the cpu reaches 100 %.
I realize that when I cut off the if clauses, then it works at Reporting services.
Does anybody know what is happening?
Why the query works in query analyser and doesn't work in Reporting Service ?
Thanks,
Maurício
View 2 Replies
View Related
