Invoking or sending SQL queries using dOS

Hello Folks,
I want to create a simple batch DOS script to query mi SQL Server 2000 database. How I could do this?
I just want to run a select for a table but I dont want people to interct with sql query analyzer or enterprise manager to avoid any issues.
Regards,Look up OSQL or ISQL in Books Online.

Basically:
OSQL -S <name of your server/instance> -U <UID> -P <PWD> -d <Database to use> -Q"<your select statment>" -n -b

I do this all the time so post back if you have more questions.|||Originally posted by Paul Young
Look up OSQL or ISQL in Books Online.

Basically:
OSQL -S <name of your server/instance> -U <UID> -P <PWD> -d <Database to use> -Q"<your select statment>" -n -b

I do this all the time so post back if you have more questions.

UID stands for? PWD I assume is the pasword, isnt.

How I can redirect the results to a txt file,

OSQL -S OSQL -S <name of your server/instance> -U <UID> -P <PWD> -d <Database to use> -Q"<your select statment>" -n -b >> query.txt

Am I right?

Thanks for your prompt reply and help|||UID = User ID

You could pipe the output to a text file but the -o parm would be more usefull.|||How I can get the instance name. I have tested in my environment and I got the next error:

[DBNETLIB] Sql Server does not exist or access denied
[DBNETLIB] ConnectionOpen <Connect(())

As per this result I checked the server name I used typing osql -L. I got the names and used them but with the same result.

When I open the Sql query analyzer I connect using windows authentication option to connect to my databases.

Please suggest..|||For Windows Security I think you need to use -E option instead of -U and -P options.

Tim Ssql

Inventory update problem

I am trying to update a master inventory table from an order details table, the query below works fine except when the order details table contains the same code number multiple times. When this occurs the update only updates for the first instance of the code number.

How do I make the update work for all the records not just the unique records?

UPDATE Inventory.Inventory
SET Qty = Inventory.Inventory.Qty - Retail.OrderDetails.Qty FROM Inventory.Inventory INNER JOIN
Retail.OrderDetails ON Inventory.Inventory.Code = Retail.OrderDetails.Code
WHERE (Retail.OrderDetails.Invoice = 207070202)

Thanks

Quote:

Originally Posted by Kliot

I am trying to update a master inventory table from an order details table, the query below works fine except when the order details table contains the same code number multiple times. When this occurs the update only updates for the first instance of the code number.

How do I make the update work for all the records not just the unique records?

UPDATE Inventory.Inventory
SET Qty = Inventory.Inventory.Qty - Retail.OrderDetails.Qty FROM Inventory.Inventory INNER JOIN
Retail.OrderDetails ON Inventory.Inventory.Code = Retail.OrderDetails.Code
WHERE (Retail.OrderDetails.Invoice = 207070202)

Thanks

I think it is not possible in SQL Server. But it will work in MS Access.

You have to fetch record and then update it|||hi

i have gone through ur query, but if possible just send me 1 or two records of each table and tell me exactily what u want|||Here is an example,

Invoice table

Code|||Here is an example,

Invoice table

Code Quantity
DM01 2
LG02 2
DM01 3
QP76 1

The update query will update the Inventory table quantity for DM01 by 2 not 5, the second DM01 is not updated

I can get around this by doing a sum query inside the select but it's not ideal.

UPDATE Inventory.Inventory
set RQty = Inventory.Inventory.RQty - od.Quantity
FROM (SELECT Code, SUM(Quantity) AS Quantity FROM Retail.OrderDetails WHERE Invoice = 207022101
GROUP BY Code) as od WHERE(Inventory.inventory.code = od.code)

Invalid operator for data type.

Kudos to y'all experts out there. I kinda needed your help. I'm trying to run a query...

SELECT a.AUF_POS AS Pos, c.ZL_STR AS Panel, a.POS_TEXT AS Description, a.BREITE AS W1, a.HOEHE

AS H1, a.BREITE2 AS W2, a.HOEHE2 AS H2, SUM(b.ANZ) AS Qty, SUM(b.LIEFER_ANZ) AS Dlvd,

SUM(b.RG_ANZ) AS Inv, (a.BREITE*a.HOEHE/CAST(1000000 AS NUMERIC)) AS UnitSQM,

(a.BREITE*a.HOEHE*SUM(b.ANZ)/CAST(1000000 AS NUMERIC)) as TotPosSQM

FROM liorder..LIORDER.AUF_POS a INNER JOIN liorder..LIORDER.AUF_STAT b ON a.AUF_NR = b.AUF_NR

AND a.AUF_POS = b.AUF_POS INNER JOIN liorder..LIORDER.AUF_TEXT c ON a.AUF_NR = c.AUF_NR AND

b.AUF_POS = c.AUF_POS

WHERE (c.ZL_MOD = 0) AND (b.AUF_NR = '86260')

GROUP BY a.AUF_POS, a.POS_TEXT, a.BREITE, a.BREITE2, a.HOEHE, a.HOEHE2, a.SFORM_NR, c.ZL_STR

...and I keep getting this error: Invalid operator for data type. Operator equals multiply, type equals nvarchar. I've tried every possible CAST and CONVERT but I just can't make it work. I'm pretty sure that the data types for the columns I mentioned on the mathematical equation are all numeric. Please help...Its going to be difficult for us to help without the DDL. Your query does alot of aggregrations (i.e. sum/avg/etc), thus you'd want to ensure that the column datatype is numeric.

Invalid object name in subquery

Hi
I have this problem.
In Query Analyzer when I write
dbo.spParseArray '12-13-14','-'
everytihng goes OK
but when i write
SELECT * FROM tblOrder WHERE ID_ORDER IN (dbo.spParseArray ('12-13-14','-'))
it returns me an error
Server: message 208, level 16, state 1, row 1
Invalid object name 'dbo.spParseArray'
What's the matter?Hi
Stored Procedures cannot be used as in line functions.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"xxx" wrote:

> Hi
> I have this problem.
> In Query Analyzer when I write
> dbo.spParseArray '12-13-14','-'
> everytihng goes OK
> but when i write
> SELECT * FROM tblOrder WHERE ID_ORDER IN (dbo.spParseArray ('12-13-14','-'
))
> it returns me an error
> Server: message 208, level 16, state 1, row 1
> Invalid object name 'dbo.spParseArray'
> What's the matter?
>
>|||Is dbo.spParseArray a Stored Proc or a UDF? If it's a Stored Proc, you canno
t
use the result set of a Stored Proc in a Select that way, you have to
"insert" the result set into a Table of some kind, (Real tbale, Temp Table,
Table Variable) and then use that table object in your Select.
IOr, rewrite the Stored Proc as a Table-Valued User Defined Function, then
you can write your select as either:
SELECT * FROM tblOrder
WHERE ID_ORDER IN
(Select OrderID
From dbo.spParseArray ('12-13-14','-'))
Or:
SELECT * FROM tblOrder O
Join dbo.spParseArray ('12-13-14','-') A
On A.OrderID = O.ID_Order
"xxx" wrote:

> Hi
> I have this problem.
> In Query Analyzer when I write
> dbo.spParseArray '12-13-14','-'
> everytihng goes OK
> but when i write
> SELECT * FROM tblOrder WHERE ID_ORDER IN (dbo.spParseArray ('12-13-14','-'
))
> it returns me an error
> Server: message 208, level 16, state 1, row 1
> Invalid object name 'dbo.spParseArray'
> What's the matter?
>
>

Invalid object name in stored procedure

-- In SQL Server 2000
--When run from Query Analyzer I correctly get identification of line
numbers having duplicate values of SKU_NameUsedBySCS:
-- LineNumber1 LineNumber2
-- 2 5
-- but when I try to create a stored procedure having this same code I get:
-- Invalid object name '#x'.
IF not EXISTS (SELECT name FROM sysobjects WHERE name = 'PermTable' )
begin
create table PermTable (scs_id int, sku_nameusedbyscs nvarchar(40))
insert into PermTable VALUES (11, 'name23')
insert into PermTable VALUES (11, 'name81')
insert into PermTable VALUES (11, 'name27')
insert into PermTable VALUES (11, 'name88')
insert into PermTable VALUES (11, 'name81')
end
declare @.SCS_ID int
set @.SCS_ID =11
set nocount on
select identity(int,1,1) as Sequence, scs_id,sku_nameusedbyscs into #x from
PermTable WHERE SCS_ID =@.SCS_ID
go
select top 40 min(Sequence) as LineNumber1, max(Sequence) as LineNumber2
from #x GROUP BY SKU_NameUsedBySCS having count(*) > 1
drop table #x
gohello steve, did you check previous error messages? is you database Case
Sensitive? If it is, then the select into statement must have the names of
your fields in lower case.
hope this helps.
"SteveInSC" wrote:

> -- In SQL Server 2000
> --When run from Query Analyzer I correctly get identification of line
> numbers having duplicate values of SKU_NameUsedBySCS:
> -- LineNumber1 LineNumber2
> -- 2 5
> -- but when I try to create a stored procedure having this same code I get
:
> -- Invalid object name '#x'.
>
> IF not EXISTS (SELECT name FROM sysobjects WHERE name = 'PermTable' )
> begin
> create table PermTable (scs_id int, sku_nameusedbyscs nvarchar(40))
> insert into PermTable VALUES (11, 'name23')
> insert into PermTable VALUES (11, 'name81')
> insert into PermTable VALUES (11, 'name27')
> insert into PermTable VALUES (11, 'name88')
> insert into PermTable VALUES (11, 'name81')
> end
> declare @.SCS_ID int
> set @.SCS_ID =11
> set nocount on
> select identity(int,1,1) as Sequence, scs_id,sku_nameusedbyscs into #x fro
m
> PermTable WHERE SCS_ID =@.SCS_ID
> go
> select top 40 min(Sequence) as LineNumber1, max(Sequence) as LineNumber2
> from #x GROUP BY SKU_NameUsedBySCS having count(*) > 1
> drop table #x
> go
>
>|||Hi
And where do you create the temporary table #x?
John
"SteveInSC" wrote:

> -- In SQL Server 2000
> --When run from Query Analyzer I correctly get identification of line
> numbers having duplicate values of SKU_NameUsedBySCS:
> -- LineNumber1 LineNumber2
> -- 2 5
> -- but when I try to create a stored procedure having this same code I get
:
> -- Invalid object name '#x'.
>
> IF not EXISTS (SELECT name FROM sysobjects WHERE name = 'PermTable' )
> begin
> create table PermTable (scs_id int, sku_nameusedbyscs nvarchar(40))
> insert into PermTable VALUES (11, 'name23')
> insert into PermTable VALUES (11, 'name81')
> insert into PermTable VALUES (11, 'name27')
> insert into PermTable VALUES (11, 'name88')
> insert into PermTable VALUES (11, 'name81')
> end
> declare @.SCS_ID int
> set @.SCS_ID =11
> set nocount on
> select identity(int,1,1) as Sequence, scs_id,sku_nameusedbyscs into #x fro
m
> PermTable WHERE SCS_ID =@.SCS_ID
> go
> select top 40 min(Sequence) as LineNumber1, max(Sequence) as LineNumber2
> from #x GROUP BY SKU_NameUsedBySCS having count(*) > 1
> drop table #x
> go
>
>|||At the time the proc is compiled the #x table does not exist as it's
created at runtime. So when you try to compile the code to get an
execution plan the query optimiser cannot create a plan involving #x
because it doesn't yet exist.
Try creating the temp table explicitly in the proc and then inserting
into it with a normal INSERT statement (rather than SELECT ... INTO).
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
SteveInSC wrote:

>-- In SQL Server 2000
>--When run from Query Analyzer I correctly get identification of line
>numbers having duplicate values of SKU_NameUsedBySCS:
>-- LineNumber1 LineNumber2
>-- 2 5
>-- but when I try to create a stored procedure having this same code I get:
>-- Invalid object name '#x'.
>
>IF not EXISTS (SELECT name FROM sysobjects WHERE name = 'PermTable' )
> begin
> create table PermTable (scs_id int, sku_nameusedbyscs nvarchar(40))
> insert into PermTable VALUES (11, 'name23')
> insert into PermTable VALUES (11, 'name81')
> insert into PermTable VALUES (11, 'name27')
> insert into PermTable VALUES (11, 'name88')
> insert into PermTable VALUES (11, 'name81')
> end
>declare @.SCS_ID int
>set @.SCS_ID =11
>set nocount on
>select identity(int,1,1) as Sequence, scs_id,sku_nameusedbyscs into #x from
>PermTable WHERE SCS_ID =@.SCS_ID
>go
>select top 40 min(Sequence) as LineNumber1, max(Sequence) as LineNumber2
>from #x GROUP BY SKU_NameUsedBySCS having count(*) > 1
>drop table #x
>go
>
>
>|||Hi Steve
If you have simply created the stored procedure by wrapping the SQL below in
a create procedure call then you have a GO in the middle of the declaration.
This will terminate the declaration.
Query Analyser will then try to run the later commands as immediate
commands. As the table is created by the SELECT ... INTO inside the
definition it will not find it for the later SELECT statement.
Try removing the GO statement in the middle of the declaration if there is
one.
As a separate point I would recommend using a Table variable (you know the
structure you want) as the scope is much better defined.
I hope this helps
Alasdair Russell
"SteveInSC" wrote:

> -- In SQL Server 2000
> --When run from Query Analyzer I correctly get identification of line
> numbers having duplicate values of SKU_NameUsedBySCS:
> -- LineNumber1 LineNumber2
> -- 2 5
> -- but when I try to create a stored procedure having this same code I get
:
> -- Invalid object name '#x'.
>
> IF not EXISTS (SELECT name FROM sysobjects WHERE name = 'PermTable' )
> begin
> create table PermTable (scs_id int, sku_nameusedbyscs nvarchar(40))
> insert into PermTable VALUES (11, 'name23')
> insert into PermTable VALUES (11, 'name81')
> insert into PermTable VALUES (11, 'name27')
> insert into PermTable VALUES (11, 'name88')
> insert into PermTable VALUES (11, 'name81')
> end
> declare @.SCS_ID int
> set @.SCS_ID =11
> set nocount on
> select identity(int,1,1) as Sequence, scs_id,sku_nameusedbyscs into #x fro
m
> PermTable WHERE SCS_ID =@.SCS_ID
> go
> select top 40 min(Sequence) as LineNumber1, max(Sequence) as LineNumber2
> from #x GROUP BY SKU_NameUsedBySCS having count(*) > 1
> drop table #x
> go
>
>|||Comment out the first "GO" and you will be all set.
Try this:
create procedure sp_abc as
IF not EXISTS (SELECT name FROM sysobjects WHERE name = 'PermTable' )
begin
create table PermTable (scs_id int, sku_nameusedbyscs nvarchar(40))
insert into PermTable VALUES (11, 'name23')
insert into PermTable VALUES (11, 'name81')
insert into PermTable VALUES (11, 'name27')
insert into PermTable VALUES (11, 'name88')
insert into PermTable VALUES (11, 'name81')
end
declare @.SCS_ID int
set @.SCS_ID =11
set nocount on
select identity(int,1,1) as Sequence, scs_id,sku_nameusedbyscs into #x
from
PermTable WHERE SCS_ID =@.SCS_ID
-- ############## MySQLServer ############ --go
select top 40 min(Sequence) as LineNumber1, max(Sequence) as
LineNumber2
from #x GROUP BY SKU_NameUsedBySCS having count(*) > 1
drop table #x
go
SteveInSC wrote:
> -- In SQL Server 2000
> --When run from Query Analyzer I correctly get identification of line
> numbers having duplicate values of SKU_NameUsedBySCS:
> -- LineNumber1 LineNumber2
> -- 2 5
> -- but when I try to create a stored procedure having this same code I get
:
> -- Invalid object name '#x'.
>
> IF not EXISTS (SELECT name FROM sysobjects WHERE name = 'PermTable' )
> begin
> create table PermTable (scs_id int, sku_nameusedbyscs nvarchar(40))
> insert into PermTable VALUES (11, 'name23')
> insert into PermTable VALUES (11, 'name81')
> insert into PermTable VALUES (11, 'name27')
> insert into PermTable VALUES (11, 'name88')
> insert into PermTable VALUES (11, 'name81')
> end
> declare @.SCS_ID int
> set @.SCS_ID =11
> set nocount on
> select identity(int,1,1) as Sequence, scs_id,sku_nameusedbyscs into #x fro
m
> PermTable WHERE SCS_ID =@.SCS_ID
> go
> select top 40 min(Sequence) as LineNumber1, max(Sequence) as LineNumber2
> from #x GROUP BY SKU_NameUsedBySCS having count(*) > 1
> drop table #x
> go|||You forgot to remove the "GO" before the "SELECT TOP 40".
Razvan

'Invalid Object name'

Hello

Once i have created a new query and it all works fine I save the query and then I run into trouble. When I come to re use the query at a later date it dosen't work and brings up the following error.

Msg 208, Level 16, State 1, Line 15

Invalid object name 'kup_regions'.

which equates to this line -

if (select sitetype from kup_regions where region_code = @.Location) = 10

I am using 'sa' as my username and sql server management studio

Cheers for any help

hi,

SimonJohns wrote:

Hello

Once i have created a new query and it all works fine I save the query and then I run into trouble. When I come to re use the query at a later date it dosen't work and brings up the following error.

Msg 208, Level 16, State 1, Line 15

Invalid object name 'kup_regions'.

which equates to this line -

if (select sitetype from kup_regions where region_code = @.Location) = 10

I am using 'sa' as my username and sql server management studio

Cheers for any help

assuming the kup_regions table/view exists at successive call in the current database, I'd just try with the complete object's name, that's the schemaName.objectName, in order to avoid eventual different default schema problems among different database users.. so it all becomes

IF (SELECT sitetype FROM dbo.kup_regions WHERE region_code = @.Location ) = 10 BEGIN

-- whatever

END;

but I see a side effect if region_code is not a "unique" value in the underlying table... if this is the case, your code will actually fail with
"Msg 512, Level 16, State 1, Line 10
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."
exception...

regards

Invalid Object Name

I restored all Databases in other server.

First I restored the master database and after the others databases. But when I connect by Query Analyser with a user that is a DBO and I execute a select the system return : "Invalid object name 'XXXX'"

My MS-SQL is the version 7.0.What happens when you run this query on that database:

select uid, name
from sysobjects
where name = 'XXXX'

where 'XXXX' is the name of the table you are after.

Invalid Object Name

I am getting an error 'Invalid Object Name' when I try and insert a row yet I can query the same table and get results??
Please Help!Your login probably does not have permission to access the table, either because it has not been set specifically, or the table is not owned by either dbo or the login you are using.

If these are not the problems, then post your code so someone online can review it.|||I am logged in as SA, so I assumed I have permission. But I have also tried running the scripts below when logged in as 'sysdba' user.

Here is a line of code that gives the Invalid Object Name:-

insert into sysdba.hot_list ('HOT_LISTID', 'Jan' , 'type_of_activity') values ('TEMPHOT00JAN' ,'0' ,'Meeting')

This line executes without error:-

truncate table sysdba.hot_list

I must be missing something really obvious!

Thanks
Graham|||I'd suggest:insert into sysdba.hot_list ([HOTLISTID], [Jan] , [type_of_activity])
values ('TEMPHOT00JAN' ,'0' ,'Meeting')...or do without column name quoting at all.

-PatP|||trying...

insert into sysdba.hot_list ([HOTLISTID], [Jan] , [type_of_activity])
values ('TEMPHOT00JAN' ,'0' ,'Meeting')

...didn't work.

I need the column headers as there are more fields in the table than I am inserting with this script.

Thanks
Graham|||Please cut and paste the error message and post it for us.

-PatP|||Here it is:-

Server: Msg 208, Level 16, State 3, Line 1
Invalid object name 'sysdba.TEMP_HOT_LIST'.|||Does the TEMP_HOT_LIST table exist, or just the HOT_LIST table? Could the "problem child" be addressed in a trigger perhaps?

-PatP

Invalid object name

I have a stored procedure that creates several temporary tables. When I call
this procedure from the Query Analyzer, it works just fine.
When I call the stored procedure through the DTS or from a query from the
reporting services, I get the error: Invalid object name '#NSLP'
#NSLP is the first temporary table. Any suggestions will be highly
appreciated.
Code for the stored procedure follows
CREATE PROCEDURE [dbo].[procGetSponsorApprovals]
@.iMonth AS integer
AS
SET NOCOUNT ON
CREATE TABLE dbo.#NSLP ( Sponsor varchar(10),
EligBkfstSevere Integer,
EligBkfst Integer,
EligLunch Integer,
EligSnack Integer
)
INSERT INTO #NSLP
SELECT
B.Sponsor,
Sum(CASE WHEN B.EligBkfstSeverePct > 40 THEN 1 ELSE 0 END) AS
EligBkfstSevere,
Sum(CASE WHEN EligBkfst = 'Regular' or EligBkfst = 'Prov1' or
EligBkfst = 'Prov2' or EligBkfst = 'Prov3' THEN 1 ELSE 0 END) AS EligBkfst,
Sum(CASE WHEN EligLunch = 'Regular' or EligLunch = 'Prov1' or
EligLunch = 'Prov2' or EligLunch = 'Prov3' THEN 1 ELSE 0 END) AS EligLunch,
Sum(CASE WHEN EligSnack = 'Regular' or EligSnack = 'Prov1' or
EligSnack = 'Prov2' or EligSnack = 'Prov3' THEN 1 ELSE 0 END) AS EligSnack
FROM tblSLPAppCenter B,
(SELECT MAX(K.EnteredDate) AS EnteredDate ,
K.AgreementNo AS AgreementNo
FROM tblSLPAppCenter K
WHERE (K.StartMonth <= @.iMonth) AND (K.EndMonth >= @.iMonth) AND
(Status IN ('Approved','Suspended'))
GROUP BY K.AgreementNo) U
WHERE B.EnteredDate = U.EnteredDate AND B.AgreementNo = U.AgreementNo
GROUP BY Sponsor
ORDER BY sponsor
CREATE TABLE #DCCenter (Sponsor varchar(10), DCCenters integer)
INSERT INTO #DCCenter
SELECT
B.Sponsor,
Count(*) AS DCCenters
FROM tblDCAppCenter B,
(SELECT MAX(K.EnteredDate) AS EnteredDate ,
K.AgreementNo AS AgreementNo
FROM tblDCAppCenter K
WHERE (K.StartMonth <= @.iMonth) AND (K.EndMonth >= @.iMonth) AND
(Status IN ('Approved','Suspended'))
GROUP BY K.AgreementNo) U
WHERE B.EnteredDate = U.EnteredDate AND B.AgreementNo = U.AgreementNo
GROUP BY Sponsor
ORDER BY sponsor
CREATE TABLE #ACCenter (Sponsor varchar(10), ACCenters integer)
INSERT INTO #ACCenter
SELECT
B.Sponsor,
Count(*) AS ACCenters
FROM tblACAppCenter B,
(SELECT MAX(K.EnteredDate) AS EnteredDate ,
K.AgreementNo AS AgreementNo
FROM tblACAppCenter K
WHERE (K.StartMonth <= @.iMonth) AND (K.EndMonth >= @.iMonth) AND
(Status IN ('Approved','Suspended'))
GROUP BY K.AgreementNo) U
WHERE B.EnteredDate = U.EnteredDate AND B.AgreementNo = U.AgreementNo
GROUP BY Sponsor
ORDER BY sponsor
CREATE TABLE #SMCenter (Sponsor varchar(10), SMCenters integer)
INSERT INTO #SMCenter
SELECT
B.Sponsor,
Count(*) AS SMCenters
FROM tblSMAppCenter B,
(SELECT MAX(K.EnteredDate) AS EnteredDate ,
K.AgreementNo AS AgreementNo
FROM tblSMAppCenter K
WHERE (K.StartMonth <= @.iMonth) AND (K.EndMonth >= @.iMonth) AND
(Status IN ('Approved','Suspended'))
GROUP BY K.AgreementNo) U
WHERE B.EnteredDate = U.EnteredDate AND B.AgreementNo = U.AgreementNo
GROUP BY Sponsor
ORDER BY sponsor
CREATE TABLE #SFCenter (Sponsor varchar(10), SFCenters integer)
INSERT INTO #SFCenter
SELECT
B.Sponsor,
Count(*) AS SFCenters
FROM tblSFAppCenter B,
(SELECT MAX(K.EnteredDate) AS EnteredDate ,
K.AgreementNo AS AgreementNo
FROM tblSFAppCenter K
WHERE (K.StartMonth <= @.iMonth) AND (K.EndMonth >= @.iMonth) AND
(Status IN ('Approved','Suspended'))
GROUP BY K.AgreementNo) U
WHERE B.EnteredDate = U.EnteredDate AND B.AgreementNo = U.AgreementNo
GROUP BY Sponsor
ORDER BY sponsor
CREATE TABLE #SSFCenter (Sponsor varchar(10), SSFCenters integer)
INSERT INTO #SSFCenter
SELECT
B.Sponsor,
Count(*) AS SSFCenters
FROM tblSSFAppCenter B,
(SELECT MAX(K.EnteredDate) AS EnteredDate ,
K.AgreementNo AS AgreementNo
FROM tblSSFAppCenter K
WHERE (K.StartMonth <= @.iMonth) AND (K.EndMonth >= @.iMonth) AND
(Status IN ('Approved','Suspended'))
GROUP BY K.AgreementNo) U
WHERE B.EnteredDate = U.EnteredDate AND B.AgreementNo = U.AgreementNo
GROUP BY Sponsor
ORDER BY sponsor
SELECT
A.AgreementNo,
A.SponsorName,
CASE WHEN isnull(A.SLP,' ') = 'Y' THEN 'Y' ELSE '_' END AS [Ever SLP],
CASE WHEN isnull(A.DC,' ') = 'Y' THEN 'Y' ELSE '_' END AS [Ever DC],
CASE WHEN isnull(A.AC,' ') = 'Y' THEN 'Y' ELSE '_' END AS [Ever AC],
CASE WHEN isnull(A.SM,' ') = 'Y' THEN 'Y' ELSE '_' END AS [Ever SH],
CASE WHEN isnull(A.FH,' ') = 'Y' THEN 'Y' ELSE '_' END AS [Ever FH],
CASE WHEN isnull(A.SF,' ') = 'Y' THEN 'Y' ELSE '_' END AS [Ever SF],
isnull((SELECT 'Y'
FROM dbo.fnAdminSLP('All', @.iMonth)
WHERE AgreementNo = A.AgreementNo AND
(Status = 'Approved' OR Status = 'Suspended')),'_') AS [SLP Sponsor],
isnull((SELECT EligLunch
FROM #NSLP
WHERE Sponsor = A.AgreementNo AND
EligLunch > 0 ),0) AS NSLP,
isnull((SELECT EligBkfst
FROM #NSLP
WHERE Sponsor = A.AgreementNo AND
EligBkfst > 0 ),0) AS Brk,
isnull((SELECT EligBkfstSevere
FROM #NSLP
WHERE Sponsor = A.AgreementNo AND
EligBkfstSevere > 0 ),0) AS SevereBrk,
isnull((SELECT EligSnack
FROM #NSLP
WHERE Sponsor = A.AgreementNo AND
EligSnack > 0 ),0) AS ASSnk,
isnull((SELECT 'Y'
FROM dbo.fnAdminDC('All', @.iMonth)
WHERE AgreementNo = A.AgreementNo AND
(Status = 'Approved' OR Status = 'Suspended')),'_')
AS [DC Sponsor],
isnull((SELECT DCCenters
FROM #DCCenter
WHERE Sponsor = A.AgreementNo AND
DCCenters > 0 ),0) AS [DC Centers],
isnull((SELECT 'Y'
FROM dbo.fnAdminAC('All', @.iMonth)
WHERE AgreementNo = A.AgreementNo),'_') AS [AC Sponsor],
isnull((SELECT ACCenters
FROM #ACCenter
WHERE Sponsor = A.AgreementNo AND
ACCenters > 0 ),0) AS [AC Centers],
isnull((SELECT 'Y'
FROM dbo.fnAdminFH('All', @.iMonth)
WHERE AgreementNo = A.AgreementNo),'_') AS [FH Sponsor],
isnull((SELECT 'Y'
FROM dbo.fnAdminSM('All', @.iMonth)
WHERE AgreementNo = A.AgreementNo),'_') AS [SM Sponsor],
isnull((SELECT SMCenters
FROM #SMCenter
WHERE Sponsor = A.AgreementNo AND
SMCenters > 0 ),0) AS [SM Centers],
isnull((SELECT 'Y'
FROM dbo.fnAdminSF('All', @.iMonth)
WHERE AgreementNo = A.AgreementNo),'_') AS [SF Sponsor],
isnull((SELECT SFCenters
FROM #SFCenter
WHERE Sponsor = A.AgreementNo AND
SFCenters > 0 ),0) AS [SF Centers],
isnull((SELECT 'Y'
FROM dbo.fnAdminSSF('All', @.iMonth)
WHERE AgreementNo = A.AgreementNo),'_') AS [SSF Sponsor],
isnull((SELECT SSFCenters
FROM #SSFCenter
WHERE Sponsor = A.AgreementNo AND
SSFCenters > 0 ),0) AS [SSF Centers],
isnull(Type, '') as Type
FROM tblAgreeData A
WHERE Sponsor <> 0 and AgreementNo NOT like 'OO%'
ORDER BY AgreementNo
GOTry to press the refresh button next to the data source. This will populate
all the fields for you. There are also some other replies to this question
just search for Invalid object in the newsgroup and you should see other
people making suggestions to this. Hope this helps. Let me know if this is
what you are looking for or if you have a different question.
Brendon Schwartz
http://spaces.msn.com/members/brendon
"Ron Sellers" wrote:
> I have a stored procedure that creates several temporary tables. When I call
> this procedure from the Query Analyzer, it works just fine.
> When I call the stored procedure through the DTS or from a query from the
> reporting services, I get the error: Invalid object name '#NSLP'
> #NSLP is the first temporary table. Any suggestions will be highly
> appreciated.
>
> Code for the stored procedure follows
> CREATE PROCEDURE [dbo].[procGetSponsorApprovals]
> @.iMonth AS integer
> AS
> SET NOCOUNT ON
> CREATE TABLE dbo.#NSLP ( Sponsor varchar(10),
> EligBkfstSevere Integer,
> EligBkfst Integer,
> EligLunch Integer,
> EligSnack Integer
> )
> INSERT INTO #NSLP
> SELECT
> B.Sponsor,
> Sum(CASE WHEN B.EligBkfstSeverePct > 40 THEN 1 ELSE 0 END) AS
> EligBkfstSevere,
> Sum(CASE WHEN EligBkfst = 'Regular' or EligBkfst = 'Prov1' or
> EligBkfst = 'Prov2' or EligBkfst = 'Prov3' THEN 1 ELSE 0 END) AS EligBkfst,
> Sum(CASE WHEN EligLunch = 'Regular' or EligLunch = 'Prov1' or
> EligLunch = 'Prov2' or EligLunch = 'Prov3' THEN 1 ELSE 0 END) AS EligLunch,
> Sum(CASE WHEN EligSnack = 'Regular' or EligSnack = 'Prov1' or
> EligSnack = 'Prov2' or EligSnack = 'Prov3' THEN 1 ELSE 0 END) AS EligSnack
> FROM tblSLPAppCenter B,
> (SELECT MAX(K.EnteredDate) AS EnteredDate ,
> K.AgreementNo AS AgreementNo
> FROM tblSLPAppCenter K
> WHERE (K.StartMonth <= @.iMonth) AND (K.EndMonth >=> @.iMonth) AND
> (Status IN ('Approved','Suspended'))
> GROUP BY K.AgreementNo) U
> WHERE B.EnteredDate = U.EnteredDate AND B.AgreementNo = U.AgreementNo
> GROUP BY Sponsor
> ORDER BY sponsor
> CREATE TABLE #DCCenter (Sponsor varchar(10), DCCenters integer)
> INSERT INTO #DCCenter
> SELECT
> B.Sponsor,
> Count(*) AS DCCenters
> FROM tblDCAppCenter B,
> (SELECT MAX(K.EnteredDate) AS EnteredDate ,
> K.AgreementNo AS AgreementNo
> FROM tblDCAppCenter K
> WHERE (K.StartMonth <= @.iMonth) AND (K.EndMonth >=> @.iMonth) AND
> (Status IN ('Approved','Suspended'))
> GROUP BY K.AgreementNo) U
> WHERE B.EnteredDate = U.EnteredDate AND B.AgreementNo = U.AgreementNo
> GROUP BY Sponsor
> ORDER BY sponsor
>
> CREATE TABLE #ACCenter (Sponsor varchar(10), ACCenters integer)
> INSERT INTO #ACCenter
> SELECT
> B.Sponsor,
> Count(*) AS ACCenters
> FROM tblACAppCenter B,
> (SELECT MAX(K.EnteredDate) AS EnteredDate ,
> K.AgreementNo AS AgreementNo
> FROM tblACAppCenter K
> WHERE (K.StartMonth <= @.iMonth) AND (K.EndMonth >=> @.iMonth) AND
> (Status IN ('Approved','Suspended'))
> GROUP BY K.AgreementNo) U
> WHERE B.EnteredDate = U.EnteredDate AND B.AgreementNo = U.AgreementNo
> GROUP BY Sponsor
> ORDER BY sponsor
> CREATE TABLE #SMCenter (Sponsor varchar(10), SMCenters integer)
> INSERT INTO #SMCenter
> SELECT
> B.Sponsor,
> Count(*) AS SMCenters
> FROM tblSMAppCenter B,
> (SELECT MAX(K.EnteredDate) AS EnteredDate ,
> K.AgreementNo AS AgreementNo
> FROM tblSMAppCenter K
> WHERE (K.StartMonth <= @.iMonth) AND (K.EndMonth >=> @.iMonth) AND
> (Status IN ('Approved','Suspended'))
> GROUP BY K.AgreementNo) U
> WHERE B.EnteredDate = U.EnteredDate AND B.AgreementNo = U.AgreementNo
> GROUP BY Sponsor
> ORDER BY sponsor
> CREATE TABLE #SFCenter (Sponsor varchar(10), SFCenters integer)
> INSERT INTO #SFCenter
> SELECT
> B.Sponsor,
> Count(*) AS SFCenters
> FROM tblSFAppCenter B,
> (SELECT MAX(K.EnteredDate) AS EnteredDate ,
> K.AgreementNo AS AgreementNo
> FROM tblSFAppCenter K
> WHERE (K.StartMonth <= @.iMonth) AND (K.EndMonth >=> @.iMonth) AND
> (Status IN ('Approved','Suspended'))
> GROUP BY K.AgreementNo) U
> WHERE B.EnteredDate = U.EnteredDate AND B.AgreementNo = U.AgreementNo
> GROUP BY Sponsor
> ORDER BY sponsor
>
> CREATE TABLE #SSFCenter (Sponsor varchar(10), SSFCenters integer)
> INSERT INTO #SSFCenter
> SELECT
> B.Sponsor,
> Count(*) AS SSFCenters
> FROM tblSSFAppCenter B,
> (SELECT MAX(K.EnteredDate) AS EnteredDate ,
> K.AgreementNo AS AgreementNo
> FROM tblSSFAppCenter K
> WHERE (K.StartMonth <= @.iMonth) AND (K.EndMonth >=> @.iMonth) AND
> (Status IN ('Approved','Suspended'))
> GROUP BY K.AgreementNo) U
> WHERE B.EnteredDate = U.EnteredDate AND B.AgreementNo = U.AgreementNo
> GROUP BY Sponsor
> ORDER BY sponsor
> SELECT
> A.AgreementNo,
> A.SponsorName,
> CASE WHEN isnull(A.SLP,' ') = 'Y' THEN 'Y' ELSE '_' END AS [Ever SLP],
> CASE WHEN isnull(A.DC,' ') = 'Y' THEN 'Y' ELSE '_' END AS [Ever DC],
> CASE WHEN isnull(A.AC,' ') = 'Y' THEN 'Y' ELSE '_' END AS [Ever AC],
> CASE WHEN isnull(A.SM,' ') = 'Y' THEN 'Y' ELSE '_' END AS [Ever SH],
> CASE WHEN isnull(A.FH,' ') = 'Y' THEN 'Y' ELSE '_' END AS [Ever FH],
> CASE WHEN isnull(A.SF,' ') = 'Y' THEN 'Y' ELSE '_' END AS [Ever SF],
> isnull((SELECT 'Y'
> FROM dbo.fnAdminSLP('All', @.iMonth)
> WHERE AgreementNo = A.AgreementNo AND
> (Status = 'Approved' OR Status => 'Suspended')),'_') AS [SLP Sponsor],
> isnull((SELECT EligLunch
> FROM #NSLP
> WHERE Sponsor = A.AgreementNo AND
> EligLunch > 0 ),0) AS NSLP,
> isnull((SELECT EligBkfst
> FROM #NSLP
> WHERE Sponsor = A.AgreementNo AND
> EligBkfst > 0 ),0) AS Brk,
> isnull((SELECT EligBkfstSevere
> FROM #NSLP
> WHERE Sponsor = A.AgreementNo AND
> EligBkfstSevere > 0 ),0) AS SevereBrk,
> isnull((SELECT EligSnack
> FROM #NSLP
> WHERE Sponsor = A.AgreementNo AND
> EligSnack > 0 ),0) AS ASSnk,
> isnull((SELECT 'Y'
> FROM dbo.fnAdminDC('All', @.iMonth)
> WHERE AgreementNo = A.AgreementNo AND
> (Status = 'Approved' OR Status = 'Suspended')),'_')
> AS [DC Sponsor],
> isnull((SELECT DCCenters
> FROM #DCCenter
> WHERE Sponsor = A.AgreementNo AND
> DCCenters > 0 ),0) AS [DC Centers],
> isnull((SELECT 'Y'
> FROM dbo.fnAdminAC('All', @.iMonth)
> WHERE AgreementNo = A.AgreementNo),'_') AS [AC Sponsor],
> isnull((SELECT ACCenters
> FROM #ACCenter
> WHERE Sponsor = A.AgreementNo AND
> ACCenters > 0 ),0) AS [AC Centers],
> isnull((SELECT 'Y'
> FROM dbo.fnAdminFH('All', @.iMonth)
> WHERE AgreementNo = A.AgreementNo),'_') AS [FH Sponsor],
> isnull((SELECT 'Y'
> FROM dbo.fnAdminSM('All', @.iMonth)
> WHERE AgreementNo = A.AgreementNo),'_') AS [SM Sponsor],
> isnull((SELECT SMCenters
> FROM #SMCenter
> WHERE Sponsor = A.AgreementNo AND
> SMCenters > 0 ),0) AS [SM Centers],
> isnull((SELECT 'Y'
> FROM dbo.fnAdminSF('All', @.iMonth)
> WHERE AgreementNo = A.AgreementNo),'_') AS [SF Sponsor],
> isnull((SELECT SFCenters
> FROM #SFCenter
> WHERE Sponsor = A.AgreementNo AND
> SFCenters > 0 ),0) AS [SF Centers],
> isnull((SELECT 'Y'
> FROM dbo.fnAdminSSF('All', @.iMonth)
> WHERE AgreementNo = A.AgreementNo),'_') AS [SSF Sponsor],
> isnull((SELECT SSFCenters
> FROM #SSFCenter
> WHERE Sponsor = A.AgreementNo AND
> SSFCenters > 0 ),0) AS [SSF Centers],
> isnull(Type, '') as Type
> FROM tblAgreeData A
> WHERE Sponsor <> 0 and AgreementNo NOT like 'OO%'
> ORDER BY AgreementNo
> GO
>|||Have you tried using table variables instead of temp tables? On the surface,
they provide the same functionality, but may be treated differently by RS.
"Ron Sellers" <RonSellers@.discussions.microsoft.com> wrote in message
news:73374365-09D9-41B8-8B35-FD7E7B382264@.microsoft.com...
>I have a stored procedure that creates several temporary tables. When I
>call
> this procedure from the Query Analyzer, it works just fine.
> When I call the stored procedure through the DTS or from a query from the
> reporting services, I get the error: Invalid object name '#NSLP'
> #NSLP is the first temporary table. Any suggestions will be highly
> appreciated.
>
> Code for the stored procedure follows
> CREATE PROCEDURE [dbo].[procGetSponsorApprovals]
> @.iMonth AS integer
> AS
> SET NOCOUNT ON
> CREATE TABLE dbo.#NSLP ( Sponsor varchar(10),
> EligBkfstSevere Integer,
> EligBkfst Integer,
> EligLunch Integer,
> EligSnack Integer
> )
> INSERT INTO #NSLP
> SELECT
> B.Sponsor,
> Sum(CASE WHEN B.EligBkfstSeverePct > 40 THEN 1 ELSE 0 END) AS
> EligBkfstSevere,
> Sum(CASE WHEN EligBkfst = 'Regular' or EligBkfst = 'Prov1' or
> EligBkfst = 'Prov2' or EligBkfst = 'Prov3' THEN 1 ELSE 0 END) AS
> EligBkfst,
> Sum(CASE WHEN EligLunch = 'Regular' or EligLunch = 'Prov1' or
> EligLunch = 'Prov2' or EligLunch = 'Prov3' THEN 1 ELSE 0 END) AS
> EligLunch,
> Sum(CASE WHEN EligSnack = 'Regular' or EligSnack = 'Prov1' or
> EligSnack = 'Prov2' or EligSnack = 'Prov3' THEN 1 ELSE 0 END) AS EligSnack
> FROM tblSLPAppCenter B,
> (SELECT MAX(K.EnteredDate) AS EnteredDate ,
> K.AgreementNo AS AgreementNo
> FROM tblSLPAppCenter K
> WHERE (K.StartMonth <= @.iMonth) AND (K.EndMonth
> >=> @.iMonth) AND
> (Status IN
> ('Approved','Suspended'))
> GROUP BY K.AgreementNo) U
> WHERE B.EnteredDate = U.EnteredDate AND B.AgreementNo = U.AgreementNo
> GROUP BY Sponsor
> ORDER BY sponsor
> CREATE TABLE #DCCenter (Sponsor varchar(10), DCCenters integer)
> INSERT INTO #DCCenter
> SELECT
> B.Sponsor,
> Count(*) AS DCCenters
> FROM tblDCAppCenter B,
> (SELECT MAX(K.EnteredDate) AS EnteredDate ,
> K.AgreementNo AS AgreementNo
> FROM tblDCAppCenter K
> WHERE (K.StartMonth <= @.iMonth) AND (K.EndMonth
> >=> @.iMonth) AND
> (Status IN
> ('Approved','Suspended'))
> GROUP BY K.AgreementNo) U
> WHERE B.EnteredDate = U.EnteredDate AND B.AgreementNo = U.AgreementNo
> GROUP BY Sponsor
> ORDER BY sponsor
>
> CREATE TABLE #ACCenter (Sponsor varchar(10), ACCenters integer)
> INSERT INTO #ACCenter
> SELECT
> B.Sponsor,
> Count(*) AS ACCenters
> FROM tblACAppCenter B,
> (SELECT MAX(K.EnteredDate) AS EnteredDate ,
> K.AgreementNo AS AgreementNo
> FROM tblACAppCenter K
> WHERE (K.StartMonth <= @.iMonth) AND (K.EndMonth
> >=> @.iMonth) AND
> (Status IN
> ('Approved','Suspended'))
> GROUP BY K.AgreementNo) U
> WHERE B.EnteredDate = U.EnteredDate AND B.AgreementNo = U.AgreementNo
> GROUP BY Sponsor
> ORDER BY sponsor
> CREATE TABLE #SMCenter (Sponsor varchar(10), SMCenters integer)
> INSERT INTO #SMCenter
> SELECT
> B.Sponsor,
> Count(*) AS SMCenters
> FROM tblSMAppCenter B,
> (SELECT MAX(K.EnteredDate) AS EnteredDate ,
> K.AgreementNo AS AgreementNo
> FROM tblSMAppCenter K
> WHERE (K.StartMonth <= @.iMonth) AND (K.EndMonth
> >=> @.iMonth) AND
> (Status IN
> ('Approved','Suspended'))
> GROUP BY K.AgreementNo) U
> WHERE B.EnteredDate = U.EnteredDate AND B.AgreementNo = U.AgreementNo
> GROUP BY Sponsor
> ORDER BY sponsor
> CREATE TABLE #SFCenter (Sponsor varchar(10), SFCenters integer)
> INSERT INTO #SFCenter
> SELECT
> B.Sponsor,
> Count(*) AS SFCenters
> FROM tblSFAppCenter B,
> (SELECT MAX(K.EnteredDate) AS EnteredDate ,
> K.AgreementNo AS AgreementNo
> FROM tblSFAppCenter K
> WHERE (K.StartMonth <= @.iMonth) AND (K.EndMonth
> >=> @.iMonth) AND
> (Status IN
> ('Approved','Suspended'))
> GROUP BY K.AgreementNo) U
> WHERE B.EnteredDate = U.EnteredDate AND B.AgreementNo = U.AgreementNo
> GROUP BY Sponsor
> ORDER BY sponsor
>
> CREATE TABLE #SSFCenter (Sponsor varchar(10), SSFCenters integer)
> INSERT INTO #SSFCenter
> SELECT
> B.Sponsor,
> Count(*) AS SSFCenters
> FROM tblSSFAppCenter B,
> (SELECT MAX(K.EnteredDate) AS EnteredDate ,
> K.AgreementNo AS AgreementNo
> FROM tblSSFAppCenter K
> WHERE (K.StartMonth <= @.iMonth) AND (K.EndMonth
> >=> @.iMonth) AND
> (Status IN
> ('Approved','Suspended'))
> GROUP BY K.AgreementNo) U
> WHERE B.EnteredDate = U.EnteredDate AND B.AgreementNo = U.AgreementNo
> GROUP BY Sponsor
> ORDER BY sponsor
> SELECT
> A.AgreementNo,
> A.SponsorName,
> CASE WHEN isnull(A.SLP,' ') = 'Y' THEN 'Y' ELSE '_' END AS [Ever SLP],
> CASE WHEN isnull(A.DC,' ') = 'Y' THEN 'Y' ELSE '_' END AS [Ever DC],
> CASE WHEN isnull(A.AC,' ') = 'Y' THEN 'Y' ELSE '_' END AS [Ever AC],
> CASE WHEN isnull(A.SM,' ') = 'Y' THEN 'Y' ELSE '_' END AS [Ever SH],
> CASE WHEN isnull(A.FH,' ') = 'Y' THEN 'Y' ELSE '_' END AS [Ever FH],
> CASE WHEN isnull(A.SF,' ') = 'Y' THEN 'Y' ELSE '_' END AS [Ever SF],
> isnull((SELECT 'Y'
> FROM dbo.fnAdminSLP('All', @.iMonth)
> WHERE AgreementNo = A.AgreementNo AND
> (Status = 'Approved' OR Status => 'Suspended')),'_') AS [SLP Sponsor],
> isnull((SELECT EligLunch
> FROM #NSLP
> WHERE Sponsor = A.AgreementNo AND
> EligLunch > 0 ),0) AS NSLP,
> isnull((SELECT EligBkfst
> FROM #NSLP
> WHERE Sponsor = A.AgreementNo AND
> EligBkfst > 0 ),0) AS Brk,
> isnull((SELECT EligBkfstSevere
> FROM #NSLP
> WHERE Sponsor = A.AgreementNo AND
> EligBkfstSevere > 0 ),0) AS SevereBrk,
> isnull((SELECT EligSnack
> FROM #NSLP
> WHERE Sponsor = A.AgreementNo AND
> EligSnack > 0 ),0) AS ASSnk,
> isnull((SELECT 'Y'
> FROM dbo.fnAdminDC('All', @.iMonth)
> WHERE AgreementNo = A.AgreementNo AND
> (Status = 'Approved' OR Status = 'Suspended')),'_')
> AS [DC Sponsor],
> isnull((SELECT DCCenters
> FROM #DCCenter
> WHERE Sponsor = A.AgreementNo AND
> DCCenters > 0 ),0) AS [DC Centers],
> isnull((SELECT 'Y'
> FROM dbo.fnAdminAC('All', @.iMonth)
> WHERE AgreementNo = A.AgreementNo),'_') AS [AC Sponsor],
> isnull((SELECT ACCenters
> FROM #ACCenter
> WHERE Sponsor = A.AgreementNo AND
> ACCenters > 0 ),0) AS [AC Centers],
> isnull((SELECT 'Y'
> FROM dbo.fnAdminFH('All', @.iMonth)
> WHERE AgreementNo = A.AgreementNo),'_') AS [FH Sponsor],
> isnull((SELECT 'Y'
> FROM dbo.fnAdminSM('All', @.iMonth)
> WHERE AgreementNo = A.AgreementNo),'_') AS [SM Sponsor],
> isnull((SELECT SMCenters
> FROM #SMCenter
> WHERE Sponsor = A.AgreementNo AND
> SMCenters > 0 ),0) AS [SM Centers],
> isnull((SELECT 'Y'
> FROM dbo.fnAdminSF('All', @.iMonth)
> WHERE AgreementNo = A.AgreementNo),'_') AS [SF Sponsor],
> isnull((SELECT SFCenters
> FROM #SFCenter
> WHERE Sponsor = A.AgreementNo AND
> SFCenters > 0 ),0) AS [SF Centers],
> isnull((SELECT 'Y'
> FROM dbo.fnAdminSSF('All', @.iMonth)
> WHERE AgreementNo = A.AgreementNo),'_') AS [SSF Sponsor],
> isnull((SELECT SSFCenters
> FROM #SSFCenter
> WHERE Sponsor = A.AgreementNo AND
> SSFCenters > 0 ),0) AS [SSF Centers],
> isnull(Type, '') as Type
> FROM tblAgreeData A
> WHERE Sponsor <> 0 and AgreementNo NOT like 'OO%'
> ORDER BY AgreementNo
> GO
>sql

Invalid Data for Numeric when EXEC returns empty row

Hi, whenever the underlying query being called by EXEC in the following
has an empty result set I get the following error -- Invalid Data for
'Numeric' when EXEC returns empty row. However if I call the query
without using REPLACE (which I'm forced to do, because openquery does
not allow variables), I get just an empty result set. Whenever the
underlying query returns a non-empty result set, the code works without
error (regardless of wether there are nulls in the numeric column).

set @.switch ='5707550'
set @.start_date = '01-JAN-2006'
set @.end_date = '27-JAN-2006'
set @.month = 1
set @.year = 2006

set @.sql_str='
SELECT * FROM
(select MSC_KEY,
to_char(trunc(TSTAMP), ''yyyy-Mon-dd'') as "Timestamp",
ROUND( NVL(SUM(SUNRGMMSCBHCP1.XASUTIL),0) / DECODE (
NVL(SUM(SUNRGMMSCBHCP1.XASNXFR),0),0,NULL,NVL(SUM( SUNRGMMSCBHCP1.XASNXFR),0)
), 5)
as "PER_CPU_UTIL"
FROM NOR_GSM_COMPOSITE_MSC1_BHCPP SUNRGMMSCBHCP1,mscs_view v
WHERE SUNRGMMSCBHCP1.gsm_msc_key = v.msc_key and v.MSC_KEY in (' +
@.switch + ')
and SUNRGMMSCBHCP1.TSTAMP between to_date(''' + @.start_date + '
00:00:00'', ''DD-MON-YYYY HH24:MI:SS'') and
to_date(''' + @.end_date + ' 23:59:00'', ''DD-MON-YYYY
HH24:MI:SS'')

group by MSC_KEY, trunc(tstamp)
)
WHERE rownum < 10000'

SET @.sql_str = N'select * from OPENQUERY(VISION, ''' +
REPLACE(@.sql_str, '''', ''') + ''')'

EXEC (@.sql_str);

Is there anyway to prevent this error?

Thanks,

CrazyCrazy Cat wrote:
> Hi, whenever the underlying query being called by EXEC in the following
> has an empty result set I get the following error -- Invalid Data for
> 'Numeric' when EXEC returns empty row. However if I call the query
> without using REPLACE (which I'm forced to do, because openquery does
> not allow variables), I get just an empty result set. Whenever the
> underlying query returns a non-empty result set, the code works without
> error (regardless of wether there are nulls in the numeric column).

code deleted to save space ...

> Is there anyway to prevent this error?
> Thanks,
> Crazy

Found the problem -- apparently one of the keys was of type numeric and
I wasn't converting it to varchar before selecting it -- funny it
worked when the result set was non-empty.

Thanks,

Crazy

Invalid column name from c# sql query

Hi

I have the following problem. I am trying to get some data from a database which matches the name in a session from a previous page:

e.g.

SqlCommand menubar = new SqlCommand("Select pernme from Person where pernme = " + (string)Session["tbname"], sqlConn);
SqlDataAdapter dataAdapter5 = new SqlDataAdapter();
dataAdapter5.SelectCommand = menubar;
DataSet dataSet5 = new DataSet();
dataAdapter5.Fill(dataSet5);
DataTable selcartest4 = dataSet5.Tables["table"];
if (selcartest4.Rows.Count != 0)

The session is called tbname and in that session is a users name

however insetad of doing the nornal thing and retrieving the data in the sql database table matching that name it comes up with the following error message:

System.Data.SqlClient.SqlException: Invalid column name 'jamie'

this is weird as the 'jamie' is the name in the session from the previous page and in fact not a column name at all the column name is pernme

I am totally stuck any help would eb great thanks

J

Change

SqlCommand menubar = new SqlCommand("Select pernme from Person where pernme = " + (string)Session["tbname"], sqlConn);

To

SqlCommand menubar = new SqlCommand("Select pernme from Person where pernme = '" + (string)Session["tbname"] + "'", sqlConn);

You need to put single quote around string.

|||

Thankyou that work

appreciated!

cheers

Invalid Column Name error,

Hi,

I'm having trouble with the following query.

select convert(datetime, convert(int, audit_timestamp - 0.5)) as auditdate, database_name, sum(FileSize) as FileSize, sum(fileUsed) as FileUsed, sum(FileFree) as FileFree
from tbl_dbSize
where auditdate > getDate() -7
and lower(server_name) = 'xxx'
group by auditdate, database_name

Basically what I am trying to do is convert my records in the select statement (as I don't want to update the actual data) which were recorded on the same day (however with different times, i.e. 27/12/2003 00:01:03 , 27/12/2003 00:01:03) to be the same (i.e. 27/12/2003 00:00:00).

I keep getting the error,

Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'auditdate'.

Any help appreciated.First, use this to truncate your datetime values:

cast(Convert(varchar(10), audit_timestamp, 120) as datetime) as auditdate

Second, you can't reference AuditDate by name; you have to reference it by the formula:

where cast(Convert(varchar(10), audit_timestamp, 120) as datetime) > getDate() -7
.
.
.
group by cast(Convert(varchar(10), audit_timestamp, 120) as datetime), database_name

It would be nice if TSQL allowed you to define the formula once and then refer to it by name, but the name isn't assigned until the query is completed and so is not available to the parser. (The exception is if you query is a subquery of another query, but that is another discussion...).

blindman|||reference to a column alias is allowed only in order by clause|||Thanks for the help.

It's working now!

invalid column exception

Hi,
I have the following query

"SELECT Test_Question.Question_ID, Test_Question.Grade_Number as GNum, Test_Question.Question_Number as QNum, Question.Question_Text as QText , Answer.Answer_Number as AnsNum, Answer.Answer_Text as AnsTxt, Answer.ID AS Ans_ID FROM Test_Question, Question, Answer WHERE Test_Question.Active=1 AND Test_Question.Question_ID = Question.ID AND Test_Question.Deleted=0 and Test_Question.Test_Detail_ID ="+ currPTestId +" AND Question.ID = Answer.Question_ID GROUP BY Test_Question.Question_ID ORDER BY Test_Question.Question_Number, Test_Question.Question_ID, Answer.Answer_Number";

But I get an exception column Test_Question.Grade_Number is invalid in the select list because it is not contained in either an aggregate function or the group by clause.

Could some one point out what is the problem in the above query.

thanks in advance
shailinclude that column too in the groupby list.

hth

Invalid column error

Hello all,
Does anyone see anything wrong with the sql query below

DECLARE @.BUILDINGLIST nvarchar(100)
SET @.BUILDINGLIST = 'ALABAMA'
DECLARE @.SQL nvarchar(1024)
SET @.SQL = 'SELECT id, CLOSED, building AS BUILDING FROM
 requests WHERE building = (' + @.BUILDINGLIST + ')'
EXEC sp_executesql @.SQL

I keep on getting the following error:
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'ALABAMA'.

Thanks in advance.
Richard M.you need an xtra set of quotations.

SET @.SQL = 'SELECT [id], CLOSED, building AS BUILDING FROM
 requests WHERE building = ('' + @.BUILDINGLIST + '')'

hth|||actually... one more than that.

(''' + @.BuildingList + ''')'

or SET @.BUILDINGLIST = '' + @.BUILDINGLIST + ''

Then run the original way.

'' would cancel into one '.

Invalid character in XML

I'm using XML EXPLICIT to query some data which may contain some invalid XML
characters. While I'm reading in the data I get an error. Other than
removing the characters before the data is inserted into the database, is
there a way to handle (or omit) reading the invalid characters?
Thanks.Steve,
Have you thought about escaping the invalid characters? You can escape using
either &<decimal>; or &x<hexadecimal>;
Thanks,
Amol
"SteveISOA" wrote:

> I'm using XML EXPLICIT to query some data which may contain some invalid X
ML
> characters. While I'm reading in the data I get an error. Other than
> removing the characters before the data is inserted into the database, is
> there a way to handle (or omit) reading the invalid characters?
> Thanks.|||Amol,
At what point can you escape the invalid characters? I do not want to
modify the existing data in the database, and I'm using a very simple proces
s
of reading and writing the data. It looks something like this:
...
SqlCommand mCommand = new SqlCommand(...); //sp with XML EXPLICIT
...
XmlTextWriter txtWriter = new XmlTextWriter(...);
XmlReader xmlReader = mCommand.ExecuteXmlReader();
while(xmlReader.ReadState != System.Xml.ReadState.EndOfFile)
{
txtWriter.WriteNode(xmlReader,false);
}
...
Thanks again.
Steve
"Amol Kher" wrote:
> Steve,
> Have you thought about escaping the invalid characters? You can escape usi
ng
> either &<decimal>; or &x<hexadecimal>;
> Thanks,
> Amol
> "SteveISOA" wrote:
>|||Steve,
The escaping should happen before the reader is created. But looks like you
dont have control over the reader creation. Once the reader is created, it
will work off the stream and if you can somehow intercept this stream then
you can replace it there.
Unfortunately invalid characters in XML is not allowed by the XML Spec so
the best solution is if you can fix it when the data gets in and not when yo
u
pull it out. Even if you find a solution to work around this issue,
potentially this is a compatibility issue with other compliant parsers.
Thanks,
Amol
"SteveISOA" wrote:
> Amol,
> At what point can you escape the invalid characters? I do not want to
> modify the existing data in the database, and I'm using a very simple proc
ess
> of reading and writing the data. It looks something like this:
> ...
> SqlCommand mCommand = new SqlCommand(...); //sp with XML EXPLICIT
> ...
> XmlTextWriter txtWriter = new XmlTextWriter(...);
> XmlReader xmlReader = mCommand.ExecuteXmlReader();
> while(xmlReader.ReadState != System.Xml.ReadState.EndOfFile)
> {
> txtWriter.WriteNode(xmlReader,false);
> }
> ...
> Thanks again.
> Steve
> "Amol Kher" wrote:
>|||You need to ensure that all binary columns or char columns which has invalid
char values like 0xa, 0xb are binary encoded with encodings like binbase64.
--
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"SteveISOA" <SteveISOA@.discussions.microsoft.com> wrote in message
news:385305A5-2C52-4843-A93B-36D209ED13DD@.microsoft.com...
> Amol,
> At what point can you escape the invalid characters? I do not want to
> modify the existing data in the database, and I'm using a very simple
> process
> of reading and writing the data. It looks something like this:
> ...
> SqlCommand mCommand = new SqlCommand(...); //sp with XML EXPLICIT
> ...
> XmlTextWriter txtWriter = new XmlTextWriter(...);
> XmlReader xmlReader = mCommand.ExecuteXmlReader();
> while(xmlReader.ReadState != System.Xml.ReadState.EndOfFile)
> {
> txtWriter.WriteNode(xmlReader,false);
> }
> ...
> Thanks again.
> Steve
> "Amol Kher" wrote:
>|||Assuming that the characters are invalid not because of the wrong encoding
(FOR XML results are UTF-16 encoded which means that you need to set it
accordingly on the client side), you have to filter the invalid characters
out in your TSQL code. There may be some non-standard option on the XML
parser that allows you to parse the invalid characters in System.XML, but I
am not sure about that.
Best regards
Michael
"SteveISOA" <SteveISOA@.discussions.microsoft.com> wrote in message
news:385305A5-2C52-4843-A93B-36D209ED13DD@.microsoft.com...
> Amol,
> At what point can you escape the invalid characters? I do not want to
> modify the existing data in the database, and I'm using a very simple
> process
> of reading and writing the data. It looks something like this:
> ...
> SqlCommand mCommand = new SqlCommand(...); //sp with XML EXPLICIT
> ...
> XmlTextWriter txtWriter = new XmlTextWriter(...);
> XmlReader xmlReader = mCommand.ExecuteXmlReader();
> while(xmlReader.ReadState != System.Xml.ReadState.EndOfFile)
> {
> txtWriter.WriteNode(xmlReader,false);
> }
> ...
> Thanks again.
> Steve
> "Amol Kher" wrote:
>

Invalid character in XML

I'm using XML EXPLICIT to query some data which may contain some invalid XML
characters. While I'm reading in the data I get an error. Other than
removing the characters before the data is inserted into the database, is
there a way to handle (or omit) reading the invalid characters?
Thanks.
Steve,
Have you thought about escaping the invalid characters? You can escape using
either &<decimal>; or &x<hexadecimal>;
Thanks,
Amol
"SteveISOA" wrote:

> I'm using XML EXPLICIT to query some data which may contain some invalid XML
> characters. While I'm reading in the data I get an error. Other than
> removing the characters before the data is inserted into the database, is
> there a way to handle (or omit) reading the invalid characters?
> Thanks.
|||Amol,
At what point can you escape the invalid characters? I do not want to
modify the existing data in the database, and I'm using a very simple process
of reading and writing the data. It looks something like this:
...
SqlCommand mCommand = new SqlCommand(...); //sp with XML EXPLICIT
...
XmlTextWriter txtWriter = new XmlTextWriter(...);
XmlReader xmlReader = mCommand.ExecuteXmlReader();
while(xmlReader.ReadState != System.Xml.ReadState.EndOfFile)
{
txtWriter.WriteNode(xmlReader,false);
}
...
Thanks again.
Steve
"Amol Kher" wrote:
[vbcol=seagreen]
> Steve,
> Have you thought about escaping the invalid characters? You can escape using
> either &<decimal>; or &x<hexadecimal>;
> Thanks,
> Amol
> "SteveISOA" wrote:
|||Steve,
The escaping should happen before the reader is created. But looks like you
dont have control over the reader creation. Once the reader is created, it
will work off the stream and if you can somehow intercept this stream then
you can replace it there.
Unfortunately invalid characters in XML is not allowed by the XML Spec so
the best solution is if you can fix it when the data gets in and not when you
pull it out. Even if you find a solution to work around this issue,
potentially this is a compatibility issue with other compliant parsers.
Thanks,
Amol
"SteveISOA" wrote:
[vbcol=seagreen]
> Amol,
> At what point can you escape the invalid characters? I do not want to
> modify the existing data in the database, and I'm using a very simple process
> of reading and writing the data. It looks something like this:
> ...
> SqlCommand mCommand = new SqlCommand(...); //sp with XML EXPLICIT
> ...
> XmlTextWriter txtWriter = new XmlTextWriter(...);
> XmlReader xmlReader = mCommand.ExecuteXmlReader();
> while(xmlReader.ReadState != System.Xml.ReadState.EndOfFile)
> {
> txtWriter.WriteNode(xmlReader,false);
> }
> ...
> Thanks again.
> Steve
> "Amol Kher" wrote:
|||You need to ensure that all binary columns or char columns which has invalid
char values like 0xa, 0xb are binary encoded with encodings like binbase64.
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"SteveISOA" <SteveISOA@.discussions.microsoft.com> wrote in message
news:385305A5-2C52-4843-A93B-36D209ED13DD@.microsoft.com...[vbcol=seagreen]
> Amol,
> At what point can you escape the invalid characters? I do not want to
> modify the existing data in the database, and I'm using a very simple
> process
> of reading and writing the data. It looks something like this:
> ...
> SqlCommand mCommand = new SqlCommand(...); //sp with XML EXPLICIT
> ...
> XmlTextWriter txtWriter = new XmlTextWriter(...);
> XmlReader xmlReader = mCommand.ExecuteXmlReader();
> while(xmlReader.ReadState != System.Xml.ReadState.EndOfFile)
> {
> txtWriter.WriteNode(xmlReader,false);
> }
> ...
> Thanks again.
> Steve
> "Amol Kher" wrote:
|||Assuming that the characters are invalid not because of the wrong encoding
(FOR XML results are UTF-16 encoded which means that you need to set it
accordingly on the client side), you have to filter the invalid characters
out in your TSQL code. There may be some non-standard option on the XML
parser that allows you to parse the invalid characters in System.XML, but I
am not sure about that.
Best regards
Michael
"SteveISOA" <SteveISOA@.discussions.microsoft.com> wrote in message
news:385305A5-2C52-4843-A93B-36D209ED13DD@.microsoft.com...[vbcol=seagreen]
> Amol,
> At what point can you escape the invalid characters? I do not want to
> modify the existing data in the database, and I'm using a very simple
> process
> of reading and writing the data. It looks something like this:
> ...
> SqlCommand mCommand = new SqlCommand(...); //sp with XML EXPLICIT
> ...
> XmlTextWriter txtWriter = new XmlTextWriter(...);
> XmlReader xmlReader = mCommand.ExecuteXmlReader();
> while(xmlReader.ReadState != System.Xml.ReadState.EndOfFile)
> {
> txtWriter.WriteNode(xmlReader,false);
> }
> ...
> Thanks again.
> Steve
> "Amol Kher" wrote:

Invalid authorization specification

Hi,
I am trying to run some queries across the servers.
I have admin rights on both the boxes, I have linked the server.
But whenever i run a query on the remote server, I get the following error
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' reported an error.
[OLE/DB provider returned message: Invalid authorization specification]
The query is a basic one like
SELECT distinct [name] FROM remoteserver.sms_rdm.dbo.v_r_system.
Any suggestions pls ?
Hi Arun,
Possible reasons...
* Check whether you have added your login id in the remote sql server. If
you haven't added the login id, use sp_addremotelogin system stored procedure
to add your local login in the remote server and give appropriate access
rights.
"Arun" wrote:

> Hi,
> I am trying to run some queries across the servers.
> I have admin rights on both the boxes, I have linked the server.
> But whenever i run a query on the remote server, I get the following error
> --
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'SQLOLEDB' reported an error.
> [OLE/DB provider returned message: Invalid authorization specification]
> --
> The query is a basic one like
> SELECT distinct [name] FROM remoteserver.sms_rdm.dbo.v_r_system.
> Any suggestions pls ?
|||Shri,
Thanks for the reply. But i can run the query successfully when i dirctly
connect to remoteserver via q/a. and have full rights on the remoteserver.
Arun
"Shri.DBA" wrote:
[vbcol=seagreen]
> Hi Arun,
> Possible reasons...
> * Check whether you have added your login id in the remote sql server. If
> you haven't added the login id, use sp_addremotelogin system stored procedure
> to add your local login in the remote server and give appropriate access
> rights.
> "Arun" wrote:
|||The issue was simple ( now that i figured it out ;))
I was getting the error below because the security tab of the linked server
was set to 'connection to be made without security context' once it was
changed to 'use current context' it worked fine
Thanks for the reply
Arun
"Arun" wrote:
[vbcol=seagreen]
> Shri,
> Thanks for the reply. But i can run the query successfully when i dirctly
> connect to remoteserver via q/a. and have full rights on the remoteserver.
> Arun
> "Shri.DBA" wrote:

Invalid authorization specification

Hi,
I am trying to run some queries across the servers.
I have admin rights on both the boxes, I have linked the server.
But whenever i run a query on the remote server, I get the following error
--
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' reported an error.
[OLE/DB provider returned message: Invalid authorization specification]
--
The query is a basic one like
SELECT distinct [name] FROM remoteserver.sms_rdm.dbo.v_r_system.
Any suggestions pls ?Hi Arun,
Possible reasons...
* Check whether you have added your login id in the remote sql server. If
you haven't added the login id, use sp_addremotelogin system stored procedure
to add your local login in the remote server and give appropriate access
rights.
"Arun" wrote:
> Hi,
> I am trying to run some queries across the servers.
> I have admin rights on both the boxes, I have linked the server.
> But whenever i run a query on the remote server, I get the following error
> --
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'SQLOLEDB' reported an error.
> [OLE/DB provider returned message: Invalid authorization specification]
> --
> The query is a basic one like
> SELECT distinct [name] FROM remoteserver.sms_rdm.dbo.v_r_system.
> Any suggestions pls ?|||Shri,
Thanks for the reply. But i can run the query successfully when i dirctly
connect to remoteserver via q/a. and have full rights on the remoteserver.
Arun
"Shri.DBA" wrote:
> Hi Arun,
> Possible reasons...
> * Check whether you have added your login id in the remote sql server. If
> you haven't added the login id, use sp_addremotelogin system stored procedure
> to add your local login in the remote server and give appropriate access
> rights.
> "Arun" wrote:
> > Hi,
> > I am trying to run some queries across the servers.
> > I have admin rights on both the boxes, I have linked the server.
> > But whenever i run a query on the remote server, I get the following error
> > --
> > Server: Msg 7399, Level 16, State 1, Line 1
> > OLE DB provider 'SQLOLEDB' reported an error.
> > [OLE/DB provider returned message: Invalid authorization specification]
> > --
> > The query is a basic one like
> > SELECT distinct [name] FROM remoteserver.sms_xxx.dbo.v_r_system.
> >
> > Any suggestions pls ?|||The issue was simple ( now that i figured it out ;))
I was getting the error below because the security tab of the linked server
was set to 'connection to be made without security context' once it was
changed to 'use current context' it worked fine
Thanks for the reply
Arun
"Arun" wrote:
> Shri,
> Thanks for the reply. But i can run the query successfully when i dirctly
> connect to remoteserver via q/a. and have full rights on the remoteserver.
> Arun
> "Shri.DBA" wrote:
> > Hi Arun,
> >
> > Possible reasons...
> >
> > * Check whether you have added your login id in the remote sql server. If
> > you haven't added the login id, use sp_addremotelogin system stored procedure
> > to add your local login in the remote server and give appropriate access
> > rights.
> >
> > "Arun" wrote:
> >
> > > Hi,
> > > I am trying to run some queries across the servers.
> > > I have admin rights on both the boxes, I have linked the server.
> > > But whenever i run a query on the remote server, I get the following error
> > > --
> > > Server: Msg 7399, Level 16, State 1, Line 1
> > > OLE DB provider 'SQLOLEDB' reported an error.
> > > [OLE/DB provider returned message: Invalid authorization specification]
> > > --
> > > The query is a basic one like
> > > SELECT distinct [name] FROM remoteserver.sms_xxx.dbo.v_r_system.
> > >
> > > Any suggestions pls ?

Invalid authorization specification

Hi,
I am trying to run some queries across the servers.
I have admin rights on both the boxes, I have linked the server.
But whenever i run a query on the remote server, I get the following error
--
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' reported an error.
[OLE/DB provider returned message: Invalid authorization specification]
--
The query is a basic one like
SELECT distinct [name] FROM remoteserver.sms_rdm.dbo.v_r_system.
Any suggestions pls ?Hi Arun,
Possible reasons...
* Check whether you have added your login id in the remote sql server. If
you haven't added the login id, use sp_addremotelogin system stored procedur
e
to add your local login in the remote server and give appropriate access
rights.
"Arun" wrote:

> Hi,
> I am trying to run some queries across the servers.
> I have admin rights on both the boxes, I have linked the server.
> But whenever i run a query on the remote server, I get the following error
> --
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'SQLOLEDB' reported an error.
> [OLE/DB provider returned message: Invalid authorization specification
]
> --
> The query is a basic one like
> SELECT distinct [name] FROM remoteserver.sms_rdm.dbo.v_r_system.
> Any suggestions pls ?|||Shri,
Thanks for the reply. But i can run the query successfully when i dirctly
connect to remoteserver via q/a. and have full rights on the remoteserver.
Arun
"Shri.DBA" wrote:
[vbcol=seagreen]
> Hi Arun,
> Possible reasons...
> * Check whether you have added your login id in the remote sql server. If
> you haven't added the login id, use sp_addremotelogin system stored proced
ure
> to add your local login in the remote server and give appropriate access
> rights.
> "Arun" wrote:
>|||The issue was simple ( now that i figured it out ;))
I was getting the error below because the security tab of the linked server
was set to 'connection to be made without security context' once it was
changed to 'use current context' it worked fine
Thanks for the reply
Arun
"Arun" wrote:
[vbcol=seagreen]
> Shri,
> Thanks for the reply. But i can run the query successfully when i dirctly
> connect to remoteserver via q/a. and have full rights on the remoteserve
r.
> Arun
> "Shri.DBA" wrote:
>

Invalid attempt to read when no data is present

I am using a standard dbreader type of loop in a query to retrieve data. I am running over what should be end of record set, every time.

I have altered my read procedures to use while dbreader.read() and if dbreader.read(), to attempt to avoid getting the error. Neither is stopping it.

While debugging it, as I get to the last item and actually get the error, if I check the dbreader status, it still indicates that it has rows.

Anyone have any ideas on how to get around this?

TIA, Tom

Can you post your code?
|||

Sure can. The below posted is the whole thing. The sqlcommand, connection, etc. should be irrelavant.

' get the data from the database and pass it back.

Function getTheData(ByVal cmd As String) As DataTable

' note that the passed cmd is the below sqlString

Dim sqlString As String = "SELECT CONVERT(char(20), Date, 107) AS Date, City, State, Company, Position, JobNumber, Row_Number() Over (ORDER BY JobNumber DESC) as Item FROM JobsDB "

' define the new datatable to hold our results

dt = New DataTable("Jobs")

' define the columns we will be saving

Dim dcIt As New DataColumn("Item", GetType(String))

Dim dcDt As New DataColumn("Date", GetType(String))

Dim dcCt As New DataColumn("City", GetType(String))

Dim dcSt As New DataColumn("State", GetType(String))

Dim dcJN As New DataColumn("JobNumber", GetType(Integer))

Dim dcKW As New DataColumn("KeyWords", GetType(String))

Dim dcPos As New DataColumn("Position", GetType(String))

Dim dcCo As New DataColumn("Company", GetType(String))

' add columns

dt.Columns.Add(dcIt)

dt.Columns.Add(dcDt)

dt.Columns.Add(dcCt)

dt.Columns.Add(dcSt)

dt.Columns.Add(dcJN)

dt.Columns.Add(dcKW)

dt.Columns.Add(dcPos)

dt.Columns.Add(dcCo)

' define datarow

Dim dr As DataRow

' build sql command info

sqlCmd = New SqlCommand

sqlCmd.Connection = sqlConn

sqlCmd.CommandType = CommandType.Text

sqlCmd.CommandText = sqlString

Try

sqlConn.Open()

dbReader = sqlCmd.ExecuteReader()

If dbReader.HasRows Then

While dbReader.Read() 'this can be changed to if dbreader.read(), no diff

' build the data table item from the database

dr = dt.NewRow()

dr("Item") = dbReader.Item("Item").ToString()

dr("Date") = dbReader.Item("Date").ToString()

dr("City") = dbReader.Item("City").ToString()

dr("State") = dbReader.Item("State").ToString()

dr("JobNumber") = dbReader.Item("JobNumber").ToString()

dr("Position") = dbReader.Item("Position").ToString()

dr("Company") = dbReader.Item("Company").ToString()

dr("KeyWords") = keywords

dt.Rows.Add(dr)

End While

Else

lblNoData.Visible = True

End If

Catch ex As Exception

Dim exMsg As String = Request.ServerVariables("Script_Name") + ", getTheData(cmd=" & sqlString & "): msg=" + ex.Message.ToString()

utils.writeApplicationLog(exMsg, System.Configuration.ConfigurationManager.AppSettings("UtilityDbName"))

Response.Redirect(ConfigurationManager.AppSettings("errorPage") & ConfigurationManager.AppSettings("errCatchAll") & "&return=" & Request.ServerVariables("Script_Host"))

End Try

Return dt.Copy

End Function

Thanks, Tom

|||The code worked for me using sample data.

Some thoughts: Make the declarations of sqlConn, sqlCmd, and dbReader local to the function instead of global. Add a Finally section that contains dbReader.Close, sqlConn.Close, and sqlConn.Dispose.

If that doesn't change anything, is it possible that there's something in your data that is causing the problem?