Select For Cursor Returns 0 Same Select Highlighted Returns 2,000+
Jul 23, 2005
Grrr!
I'm trying to run a script:
print 'Declaring cursor'
declare cInv cursor forward_only static for
select
distinct
inv.company,
inv.contact,
inv.address1,
inv.city,
inv.state,
inv.postalcode,
inv.cmcompanyid
from
dedupe.dbo.ln_invoice as inv
left join
dedupe.dbo.customerid as cid
on
dbo.fnCleanString(inv.company) = cid.searchco
where
((inv.customerid is null
and cid.searchco is null)
and (inv.date >= '01/01/2003' or (inv.date < '01/01/2003' and
inv.outstanding > 0.01))
and not inv.company is null)
print 'Cursor declared'
declare
@contact varchar(75),
@company varchar(50),
@address1 varchar(75),
@city varchar(30),
@state varchar(20),
@zip varchar(10),
@cmcompanyid varchar(32),
@iCount int,
@FetchString varchar(512)
open cInv
print 'cursor opened'
fetch cInv into
@company,@contact,@address1,@city,@state,@zip,@cmc ompanyid
print 'Cursor fetched @@Cursor_rows = ' + cast(@@cursor_rows as
varchar(5))
All the prints are there to help me figure out what's going on!
When I get to the Print 'Cursor fetched @@cursor_rows....
the value is 0 and the script skips down to the close and deallocate.
BUT, if I just highlight the Select...When section, I get over 2,000
rows. What am I missing?
Thanks.
View 6 Replies
ADVERTISEMENT
Dec 7, 2006
HiI have a query that is performing very strangely.I f I put a top statement in it returns rows,soSelect top 10 * from .......returns 10 rowsbut without it then no data is returnedSelect * from ..........returns 0 rows.
View 1 Replies
View Related
Jul 20, 2005
Goodmorning,Could I have a SELECT statement that normally returns two rows,but that instead returns one row appending to the first row the secondone of the result ?For exampleQuery: "SELECT username from tab1 where year in (2001,2002)"Result:1° - "'John'"2° - "'Adam'"Instead I need:Result:"'John','Adam'"?I have Win2000 Pro , SqlServer2000.Thank You--Posted via Mailgate.ORG Server - http://www.Mailgate.ORG
View 3 Replies
View Related
Jan 21, 2004
hi,
i have the following query to sum the total due balance for a customer:
select sum(outstanding)from out where customer = 'myvariable' the problem is when the customer has no outstanding it returns NULL is there a way to return 0 when there are no outstanding?
thanks
View 7 Replies
View Related
Aug 15, 2003
Hi,
I have DB monitoring jobs which use Sysperfinfo to monitor some of the counters. On One SQL 2K Server since few days the select on sysperfinfo returns 0.
Do you think I need to start any service from the OS side to enable this?
Your input is highly apppreciated.
Thanks,:confused:
View 2 Replies
View Related
Jan 30, 2014
I'm trying to understand why I can enter a query such as:
select 5,"random"
from customers;
and get two columns with 5 and "random" in every respective column field.Why don't I receive a syntax error ?
View 4 Replies
View Related
Jun 6, 2006
I am using the following conditional select statement but it returns no results.
Declare @DepartmentName as varchar
Set @DepartmentName = null
Declare @status as bigint
Set @status = 4
IF (@DepartmentName = null) BEGIN
SELECT CallNumber AS [Call Number], Problem, Solution, Note
FROM AdminView
WHERE (Status = @status)
ORDER BY CallLoggedDT
END
ELSE IF (@DepartmentName <> null) Begin
SELECT CallNumber AS [Call Number], Problem, Solution, Note
FROM dbo.AdminView
WHERE (Status = @status) AND
(DepartmentName = @DepartmentName)
ORDER BY CallLoggedDT
end
when i run the 2nd half by itself it tells me to declare @status but not @departmentname. whats going on???
Chris Morton
View 3 Replies
View Related
Apr 24, 2008
I have a Select Distinct myfield that returns multiple rows with same value for myfield when it should only one. Why is this happening?
View 4 Replies
View Related
Sep 12, 2006
Hi,
The test.sql scripts I write to test CLR stored procedures run successfully, but when I want to display the resulting data in the database with a simple "SELECT * from Employee"
I get the result as:
Name Address
---- -------
No rows affected.
(1 row(s) returned)
But not the actual row is displayed whereas I would expect to see something like:
Name Address
---- -------
John Doe
No rows affected.
(1 row(s) returned)
I have another database project where doing the same thing displays the row information but there doesn't seem to be a lot different between the two.
Why no results in first case?
Thanks,
Bahadir
View 1 Replies
View Related
Feb 22, 2006
If I run this statement in Query Analyzer, it properly returns 1for my testing table. But if I put the statement into a storedprocedure, the stored procedure returns NULL. What am I doingwrong? I suspect it may be related to how I defined the parametersfor the stored procedure. Perhaps my definition of TableName andColumnName don't match what COLUMNPROPERTY and OBJECT_ID expect toreceive, but I don't know where to look for the function declarationsfor those. Any pointers would be appreciated.Select statement:SELECT COLUMNPROPERTY(OBJECT_ID('Table1'), 'TestID', 'IsIdentity') ASIsIdentityTable definition:CREATE TABLE [dbo].[Table1] ([TestID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,[Description] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL) ON [PRIMARY]Stored Procedure definition:CREATE PROCEDURE spTest(@TableName varchar,@ColumnName varchar)AS SELECT COLUMNPROPERTY(OBJECT_ID(@TableName), @ColumnName,'IsIdentity') AS IsIdentity
View 2 Replies
View Related
Jul 20, 2005
Does anyone know a select statement that would return the column namesand keys and indexes of a table?Thanks,TGru*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 3 Replies
View Related
Feb 21, 2008
I have quite a few tables which allow NULL values. I have to struggle a lot with DBnull Exceptions :|example: col1,col2,... are all columns of type Integer and possibly NULL. var query = from person in table select new { person.col1, test = (int?) person.col2, person.col3, person.col4, ...}; As soon as my result encounters a DBNull value.. the whole query fails. This is really bad.How would I return the valid values.. and set the keys where there is no value to a null type? (e.g. int -> 0)I tried using "(int?)" but I'm not *really* sure what it does :-) Anyway.. it has no effect :-)
View 1 Replies
View Related
May 18, 2006
Hello:
I need assistance writing a SELECT statement. I need data from a table that matches one (or more) of multiple criteria, and I need to know which of those criteria it matched. For instance, looking at the Orders table in the Northwind database, I might want all the rows with an OrderDate after Jan 1, 1997 and all the rows with a ShippedDate after June 1, 1997. Depending on which of those criteria the row matches, it should include a field stating whether it is in the result set because of its OrderDate, or its ShippedDate. One way of doing this that I've already tried is:
SELECT 'OrderDate' AS [ChosenReason], Orders.*FROM OrdersWHERE OrderDate > '1-1-1997'UNIONSELECT 'ShippedDate' AS [ChosenReason], Orders.*FROM OrdersWHERE ShippedDate > '6-1-1997'
In my application, scanning a table with thousands of records for five sets of criteria takes a few seconds to run, which is not acceptable to my boss. Is there a better way of doing this than with the UNION operator?
Thank you
View 2 Replies
View Related
Mar 4, 2005
OK heres the situation, I have a Categories table and a Products table, each Category can have one or many Products, but a product can only belong to one Category hence one-to-many relationship.
Now I want to do a SELECT query that outputs all of the Categories onto an ASP page, but also displays how many Products are in each category eg.
CatID | Name | Description | No. Products
0001 | Cars | Blah blah blah | 5
etc etc
At the moment I'm doing nesting in my application logic so that for each category that is displayed, another query is run that returns the number of products for that particular category. It works ok!
However, is there a way to write a SQL Statement that returns all the Categories AND number products from just the one SELECT statement, rather than with the method I'm using outlined above? The reason I'm asking is that I want to be able to order by the number of products for each category and my method doesn't allow me to do this.
Many thanks!
View 3 Replies
View Related
Aug 9, 2014
I don't understand using a dynamic cursor.
Summary
* The fetch next statement returns multiple rows when using a dynamic cursor on the sys.dm_db_partition_stats.
* As far as I know a fetch-next-statement always returns a single row?
* Using a static cursor works as aspected.
* Works on production OLTP as well as on a local SQL server instance.
Now the Skript to reproduce the whole thing.
create database objects
-- create the partition function
create partition function fnTestPartition01( smallint )
as range right for values ( 1, 2, 3, 4, 5, 6, 7, 8 , 9, 10 ) ;
[Code]....
Why does the fetch statement return more than 1 row? It returns the whole result of the select-statement. When using a STATIC cursors instead I get the first row of the cursor as I would expect. Selecting a "normal" user table using a dynamic cursor I get the first row only, again as expected.
View 8 Replies
View Related
Jul 20, 2005
Sorry for asking stupid questions...I can't remember which settings in MS SQL define the behaviour of this comparison:select * from table where 'Anna ' = 'Anna'to be TRUE. Both strings are different because the first contains trailing blanks.How to change it to return FALSE what is my expected value?
View 2 Replies
View Related
May 11, 2015
java code to retrieve the data returned by SQL server stored procedure which is of CURSOR VARYING OUTPUT type and display the details on console.
View 3 Replies
View Related
Jul 23, 2005
buddies,situation: a processing must take place on every row of a table, andoutput results to another table, that can't be done via an insertinto..select query (let's assume that it's not possible for now).There're 2 solutions I have in mind:1) open a cursor and cycle through each row (The table can have up to1M rows)2) create a clustered index (i.e on an identity column) then have aloop like:declare @i int, @rows int,@col1 varchar(20), @col2 varchar(20),... @coln varchar(20),@outval1 varchar(20),... -- output valuesselect @i=1, @rows = max(xid) from tblname -- xid is clustered indexedwhile (@i<=@rows)beginselect @col1 = col1, @col2 = col2,...@coln = colnfrom tblnamewhere xid = i-- do the processing on the variables-- then insert results to another tableset @i = @i+1endI'd like to know your ideas of which one would be more efficient. Anyother solutions are much appreciatedthanks,Tamy
View 4 Replies
View Related
May 8, 2002
In my previous post I asked how to do the bottom question. I got a response to use a cursor, now I made an attempt to use a cursor but I still get the same response. Any help will be greatly appreciated.
--CURRENT OUTPUT--
empID Rank Skills
------- ---- --------------------------------------------------
2924 1 Create Documents
2924 2 Mail Merge
2924 3 Create Header and footer
2924 3 Create Spreadsheet
2924 3 Joining Tables in a Query
--DESIRED OUTPUT--
empID Rank Skills
------ ---- ------------
2924 1 Create Documents
2924 2 Mail Merge
2924 3 Create Header and footer, Joining Tables in a Query, Create Spreadsheet
--Here is the cursor script.--
Declare @skills varchar(255),@skills2 varchar(255),@message varchar(255),@empID varchar(255), @Rank varchar(255)
DECLARE emp_skills CURSOR For
select C.empID, B.Rank,B.Text1 as Skills from tbl_survey_rank B , tbl_survey_valueID C
where PATINDEX ( '%'+ltrim(rtrim(B.valueID))+'%', C.text1) >0 and C.empID = '2924'and
(B.Rank ='1'or B.Rank ='2' or B.Rank ='3') or PATINDEX ( '%'+ltrim(rtrim(B.valueID))+'%', C.text1) >0 and
B.Rank ='3'and C.empID = '2924' or PATINDEX ( '%'+ltrim(rtrim(B.valueID))+'%', C.text1) >0 and
B.Rank ='3'and C.empID = '2924' or PATINDEX ( '%'+ltrim(rtrim(B.valueID))+'%', C.text1) >0 and
B.Rank ='3'and C.empID = '2924' or PATINDEX ( '%'+ltrim(rtrim(B.valueID))+'%', C.text1) >0 and
B.Rank ='3'and C.empID = '2924'
DECLARE emp_skills2 CURSOR For
select B.Text1 as Skills from tbl_survey_rank B , tbl_survey_valueID C
where PATINDEX ( '%'+ltrim(rtrim(B.valueID))+'%', C.text1) >0 and C.empID = '2924'and
(B.Rank ='1'or B.Rank ='2' or B.Rank ='3') or PATINDEX ( '%'+ltrim(rtrim(B.valueID))+'%', C.text1) >0 and
B.Rank ='3'and C.empID = '2924' or PATINDEX ( '%'+ltrim(rtrim(B.valueID))+'%', C.text1) >0 and
B.Rank ='3'and C.empID = '2924' or PATINDEX ( '%'+ltrim(rtrim(B.valueID))+'%', C.text1) >0 and
B.Rank ='3'and C.empID = '2924' or PATINDEX ( '%'+ltrim(rtrim(B.valueID))+'%', C.text1) >0 and
B.Rank ='3'and C.empID = '2924'
OPEN emp_skills
OPEN emp_skills2
FETCH NEXT FROM emp_skills into @empID, @Rank, @skills
FETCH NEXT FROM emp_skills2 into @skills2
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @message = @skills2
FETCH NEXT FROM emp_skills2 into @skills2
Print @empID + ' '+ @Rank + ' ' + @message
FETCH NEXT FROM emp_skills into @empID, @Rank, @skills
End
CLOSE emp_skills
DEALLOCATE emp_skills
CLOSE emp_skills2
DEALLOCATE emp_skills2
--Previous Post--
Another question for all you SQL experts, I have a lot of them. I am trying to select from a table wher some conditions need to be met based on an employee ID. What I am doing is when the rank is a 1,2, or 3 I pick up the text description of that rank. Can I make it so that I get the ID only once and all the text descriptions are on the same line. Here is the sql script along with my current output and my desired output.
--SQL SCRIPT__
select C.empID, B.Rank,B.Text1 as Skills from tbl_survey_rank B , tbl_survey_valueID C
where PATINDEX ( '%'+ltrim(rtrim(B.valueID))+'%', C.text1) >0 and C.empID = '2924'and
(B.Rank ='1'or B.Rank ='2' or B.Rank ='3')
--CURRENT OUTPUT--
empID Rank Skills
------- ---- --------------------------------------------------
2924 1 Create Documents
2924 2 Mail Merge
2924 3 Create Header and footer
2924 3 Create Spreadsheet
2924 3 Joining Tables in a Query
--DESIRED OUTPUT--
empID Rank Skills
------ ---- ------------
2924 1 Create Documents
2924 2 Mail Merge
2924 3 Create Header and footer, Joining Tables in a Query, Create Spreadsheet
View 2 Replies
View Related
Mar 26, 2000
Hi...
I have a stored procedure that rertrieves data from an sql database
and sends out a mail to each receipient who meets the criteria
I am using SQL mail.
I dynamically generate the where clause for my sql query based on criteria taken
from other stored procedures and store it in a varchar variable
called @sqlquery
When i have the following code to run my cursor
DECLARE overdue3 CURSOR
LOCAL FORWARD_ONLY
FOR SELECT DISTINCT Events.E_Name, Events.E_SDate, Events.E_City,
Events.E_ID FROM Events, IndustryEvents + @sqlquery2
OPEN overdue3
I get an error message at the '+' sign
which says, cannot use empty object or column names, use a single
space if necessary.
What should i do. i have tested the variable @sqlquery and it is
definately not blank. There is no bracket error or anything.
Please help!!!
Thanks much indeed
Ramesh
View 1 Replies
View Related
May 20, 2003
Hi All, Can anyone please help?
TableA has data as below:
ssn sex dob rel_code
111111111 m 19500403 m
111111111 f 19570908 w
111111111 f 19770804 d
111111111 f 19801203 d
111111111 f 19869712 d
111111111 m 19870907 s
111111111 m 19901211 s
I have to convert the rel_code into a specific manner so the data will look as below in TableB:
ssn sex dob rel_code
111111111 m 19500403 01
111111111 f 19570908 02
111111111 f 19770804 20
111111111 f 19801203 21 111111111 f 19869712 22
111111111 m 19870907 30
111111111 m 19901211 31
Member's rel_code = 01
spouse's rel_code = 02
daughter's rel_code starts from 20 with the oldest and increments by 1.
Son's rel_code starts from 30 and increments by 1 from oldest to the youngest.
I know You can write a Sp with cursor and do this, but I would like to know if you can accomplish the same thing by a select or case or something else instead of a cursor.
Thanks in advance.
Jannat.
View 6 Replies
View Related
May 14, 2008
Hi all
I am trying to do dynamic Select for Cursor. The dynamic would be like this:
IF CONDITION1 IS TRUE:
SELECT CustomerTenderID, CustomerSiteID, ContractPeriod, SupplierID
FROM dbo.tnd_TenderSiteRateConsumptionView
WHERE CustomerTenderID = @CustomerTenderID
IF CONDITION2 IS TRUE:
SELECT CustomerTenderID, CustomerSiteID, ContractPeriod, SupplierID
FROM dbo.tnd_TenderSiteRateConsumptionView
WHERE CustomerTenderID = @CustomerTenderID AND
CustomerSiteID = @CustomerSiteID
etc etc
Here's the cursor
DECLARE RateList CURSOR FOR
SELECT CustomerTenderID, CustomerSiteID, ContractPeriod, SupplierID
FROM dbo.tnd_TenderSiteRateConsumptionView
WHERE (BASED ON CONDITION)
ORDER BY CustomerTenderID,
CustomerSiteID,
SupplierID,
ContractPeriod
OPEN RateList
FETCH NEXT FROM RateList
INTO@CustomerTenderID, @ReturnedCustomerSiteID, @ReturnedContractPeriod, @ReturnedSupplierID
WHILE @@FETCH_STATUS = 0
BEGIN
SET @rowNum = @rowNum + 1
-- DO SOME FUNKY STUFF
FETCH NEXT
FROM RateList
INTO@CustomerTenderID, @ReturnedCustomerSiteID, @ReturnedContractPeriod, @ReturnedSupplierID
View 4 Replies
View Related
Oct 11, 2006
I need to dynamically construct the field order of a cursor based on fixed labels from another table, but when I put that resulting query I receive the error:
Server: Msg 16924, Level 16, State 1, Line 78
Cursorfetch: The number of variables declared in the INTO list must match that of selected columns.
I have 6 fields defined in the cursor select, and 6 parameters in the fetch. The results of running the @sql portion returns valid data. Should this be possible to define a parameter containing the select clause of the cursor?
select colnum, coldesc, colname into #ae_defs from ae_adefs
select @Sql = (select colname from #ae_defs where coldesc = 'PATIENT NAME') +
', ' +
(select colname from #ae_defs where coldesc = 'PATIENT NUMBER') +
', ' +
(select colname from #ae_defs where coldesc = 'ACCOUNT NUMBER') +
', ' +
(select colname from #ae_defs where coldesc = 'VISIT DATE') +
', ' +
(select colname from #ae_defs where coldesc = 'VISIT TYPE') +
', DocID from ae_dtl1'
DECLARE myCursor CURSOR FOR
Select @SQL
OPEN myCursor
print @@Cursor_rows
FETCH NEXT FROM myCursor into @var1, @var2, @var3, @var4, @var5, @DocID
View 2 Replies
View Related
Oct 23, 2006
I have problems to place my variable into the select statement.
DECLARE @DB_NAME varchar(64)
DECLARE MR_ReqPro_DB_cursor CURSOR FOR
select name from dbo.sysdatabases where name like '%MR_req%'
OPEN MR_ReqPro_DB_cursor
FETCH NEXT FROM MR_ReqPro_DB_cursor
INTO @DB_NAME
WHILE @@FETCH_STATUS = 0
BEGIN
print @DB_NAME; --works fine
Select NAME, FILEDIRECTORY FROM @DB_NAME.MR_ReqPro.RQDOCUMENTS WHERE (FILEDIRECTORY LIKE '%\%');
FETCH NEXT FROM MR_ReqPro_DB_cursor INTO @DB_NAME
END
CLOSE MR_ReqPro_DB_cursor
DEALLOCATE MR_ReqPro_DB_cursor
GO
How could i use a variable like @DB_Name in my select ?
View 1 Replies
View Related
Dec 16, 2004
CREATE PROCEDURE UpdateAccount
(
@AccountID int,
@EmailAddress varchar(255),
@Password varchar(16)
)
AS
DECLARE @actualPassword varchar(16)
SELECT @actualPassword = Password
FROM Account WHERE AccountID = @AccountID
If Exists( SELECT EmailAddress FROM Account
WHERE EmailAddress = @EmailAddress )
RETURN -1
ELSE IF @Password <> @actualPassword
RETURN - 2
ELSE
UPDATE Account
SET
EmailAddress = @EmailAddress,
Password = @Password
WHERE AccountID = @AccountID
GO
View 2 Replies
View Related
Oct 13, 2003
I'm trying to build a select statement for a CURSOR where part of the SQL statement is built using a variable.
The following fails to parse:
Declare Cursor1 Cursor
For
'select table_name from ' + @database + '.Information_Schema.Tables Where Table_Type = ''Base Table'' order by Table_Name'
Open cursor1
That doesn't work, I've also tried using an Execute() statement, no luck there either. Any ideas or suggestions are greatly appreciated.
View 7 Replies
View Related
Jun 7, 2004
I can't seem to get a cursor to work when I'm passing in a variable for a column name of the select statement. For example:
declare @col varchar(50)
set @col = 'Temperature'
declare notifycurs cursor scroll for
select @col from Table
Obviously this won't work correctly (since the result will simply be 'Temperature' instead of the actual float value for temperature). I tried to use quotes for the entire statement with an EXEC
(ie. exec('select '+@col+' from Table' )
but that gave me an error.
Is there a way to pass in a variable for a column name for a curor select statement????
View 7 Replies
View Related
Nov 9, 2007
Hi everyone,
I have been trying to perform the following task:
Using the sys.databases & sys.sysindexes views to display all the columns with a clustered index for all tables and all databases in a given server. So the end result will have 3 columns:
Database name
Table name
Column name from that table with a clustered index
I have already created the following script which displays all the databases for a given server:
declare @DBname nvarchar(128)
declare testCursorForDB cursor
for
select name from sys.databases with (nolock)
where name not in ('master','tempdb','model','msdb')
order by name
open testCursorForDB
fetch next from testCursorForDB
into @DBname
while @@fetch_status = 0
begin
print @DBname
fetch next from testCursorForDB
into @DBname
end
close testCursorForDB
deallocate testCursorForDB
I also have created the following query which will display all the table and column names which have a clustered index for a given database:
select object_name(i.id) as TableName,
i.name as IndexName
from sys.sysindexes as i with (nolock)
where i.indid = '1'
However, what I need help/advice on is how do I combine these two together into one working script (either using nested cursors or a better way). In other words, how can I specify which database to use (ie. using the "use database_name") so that my query above will be applied to each database found within the cursor.
Any help is greatly appreciated
Thanks!
View 7 Replies
View Related
Aug 11, 2006
Whats wrong with the following code,its compiling but returning only first memberid. i want all memberids to be returned
DECLARE @memberList VARCHAR(4000),@hDoc INT
SET @memberList = '<MemberList><MemberID>7136</MemberID><MemberID>7137</MemberID><MemberID>7138</MemberID></MemberList>'exec sp_xml_preparedocument @hDoc OUTPUT, @memberListSELECT MemberID FROM OPENXML (@hdoc, 'MemberList', 2) WITH (MemberID BIGINT)
EXEC sp_xml_removedocument @hDoc
View 1 Replies
View Related
Feb 26, 2007
@Names is a query string passed in, I need to count the number of records as a result of the below query/
Dim test As String
Dim sqlConnection3 As New SqlConnection("data Source=EQ-520-WEBSQLEXPRESS;Initial Catalog=CRDB.MDF;Integrated Security=True")
Dim cmd As New SqlCommand
Dim returnValue As Object
cmd.CommandText = "SELECT COUNT(ReqID) AS Expr1, LineManager FROM TblReqMain GROUP BY LineManager HAVING (LineManager = @Names)"
cmd.CommandType = Data.CommandType.Text
cmd.Connection = sqlConnection3
cmd.Parameters.Add("@Names", Data.SqlDbType.NVarChar)
sqlConnection3.Open()
cmd.Parameters("@Names").Value = test
If test = "" Then
Response.Write("An error occured")
Exit Sub
Else
returnValue = cmd.ExecuteScalar()
sqlConnection3.Close()
Label6.Text = "Number " & returnValue
View 1 Replies
View Related
May 4, 2008
I have a stored procedure with an output bit variable. When I set this variable to 0, I get an output of 0. When I set this variable to 1, I get an output of -1. What is going on?
View 9 Replies
View Related
Oct 15, 2004
The following query returns 0 when executing in Query Analyzer:SELECT isnull(Count(*),0) as total FROM SplitDetail WHERE SiteCode = 14 AND ProjectID = 4367Yet ExecuteScalar() in vb.net return a -1.
Any ideas on what I might be doing wrong... ?
View 1 Replies
View Related
Dec 3, 2001
I have an sp (attached below) that returns records as desired when run in the query analyzer, but when run from within my adp I get only a "the sp ran successfully but did not return any records" message box. I would like to use it as a record source for a report, and when I try to do that I get a message box saying that the "Provider command for child rowset does not produce a rowset".
To repeat, the sp runs fine in QA, just not in access.
Any thoughts, anybody? Many thanks. David
Alter Procedure spDailyMicroLabel
@Date Datetime = null
AS
IF @Date IS NULL
BEGIN
SET @Date=getdate()
SELECT FERMID, fldfermtank AS "Tank", CONVERT(varchar(12), fldfilltime, 101) AS "Filled",
fldfermprod AS "Brand", CONVERT(varchar(15),fldfilltime, 108) AS "FillTime", CONVERT(varchar(12), @Date, 101) as "ReportDate",
fldfermidold AS "FermenterCode",
CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) AS "FillDay",
"Sample Test" = CASE
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int)=1
THEN 'HLP/UBA'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 3 THEN 'XTF'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 4 THEN 'HLP, TF'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 6 THEN 'TA'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 7 THEN 'HLP/UBA, BU/Color'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 10 THEN 'HLP'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 13 THEN 'HLP/UBA'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 16 THEN 'HLP'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 19 THEN 'HLP/UBA'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 90 THEN 'HLP/UBA'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 91 THEN 'HLP/UBA'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 92 THEN 'HLP/UBA'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 93 THEN 'HLP/UBA'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 94 THEN 'HLP/UBA'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 95 THEN 'HLP/UBA'
WHEN CAST(CAST(CONVERT(char(8),GETDATE(),112) AS datetime) - flddtferfil AS int) = 96 THEN 'HLP/UBA'
ELSE 'No Test Micro'
END
INTO #tblTempA
FROM vwUnfilteredFermenters
SELECT #tblTempA.FermenterCode AS "Fermenter ID", #tblTempA.Filled AS "Fill Date", #tblTempA.FillDay,
tblMicroTestDays.Test, tblMicroTestDays.HLPResults AS "HLP Results", tblMicroTestDays.UBAResults AS "UBA Results"
FROM tblMicroTestDays RIGHT JOIN #tblTempA ON tblMicroTestDays.TestDay = #tblTempA.FillDay
END
View 1 Replies
View Related