isql query output issue: stored

Showing posts with label stored. Show all posts

Friday, March 30, 2012

izzit got for loop in transact-sql stored procedure

Dear all,
i was confuse that izzit there are sql server was support for loop
statement,
i got one section of inner query in my asp program, and i would like
to change in to stored procedure,i using ms sql server as my database.
the following is the asp code with the inner sql
For i = 1 To 10
If not fixQuote(Request.Form("txtDescription" & i)) = "" Then
strSQL = "INSERT INTO ack_item
(Ack_id,Part_no,Serial_no,Description,Qt
y,Itm) VALUES ("& strAckID
&",'"& fixQuote(Request.Form("txtPartNo" & i)) &"',"
strSQL = strSQL & "'"& fixQuote(Request.Form("txtSerialNo" & i))
&"','"& fixQuote(Request.Form("txtDescription" & i)) &"',"&
CheckComma(Request.Form("txtQty" & i)) &","
strSQL = strSQL & "'"& fixQuote(Request.Form("txtItm" & i)) &"')"
call SetConnection(strSQL,2)
End If
Next
could i change whole code into the sql server (store procedure)?Hi
Yep , there is a WHILE loop in the SQL Server
DECLARE @.i INT
SET @.i=1
WHILE @.i<=10
BEGIN
--Do something here
SET @.i=@.i+1
END
Can you elaborate a little bit what you are doing so we can suggest a
solution without using a loop?
<yokesanhoo@.gmail.com> wrote in message
news:1140499360.016330.300370@.g44g2000cwa.googlegroups.com...
> Dear all,
> i was confuse that izzit there are sql server was support for loop
> statement,
> i got one section of inner query in my asp program, and i would like
> to change in to stored procedure,i using ms sql server as my database.
> the following is the asp code with the inner sql
> For i = 1 To 10
> If not fixQuote(Request.Form("txtDescription" & i)) = "" Then
> strSQL = "INSERT INTO ack_item
> (Ack_id,Part_no,Serial_no,Description,Qt
y,Itm) VALUES ("& strAckID
> &",'"& fixQuote(Request.Form("txtPartNo" & i)) &"',"
> strSQL = strSQL & "'"& fixQuote(Request.Form("txtSerialNo" & i))
> &"','"& fixQuote(Request.Form("txtDescription" & i)) &"',"&
> CheckComma(Request.Form("txtQty" & i)) &","
> strSQL = strSQL & "'"& fixQuote(Request.Form("txtItm" & i)) &"')"
> call SetConnection(strSQL,2)
> End If
> Next
> could i change whole code into the sql server (store procedure)?
>

Wednesday, March 28, 2012

Its to slow when I handle XML field in SQL 2005!

It's to slow when I handle XML field in SQL 2005!

I find that it's slow when I handle XML field (maybe only stored 200kb date) in SQL 2005! Especially when I insert a sub tree to XML field !
I have indexed the XML field.

Is it possible that the XML field is not ripe in SQL 2005 database?

Can you quantify "slow"?|||I made a online bookmark program with ASP.Net 2.0+ SQL 2005, I stored bookmarks in a XML Field, I find it's very slow when I insert, edit or delete bookmark, especially import bookmark to a folder selected.you can try it athttp://www.hellocw.com/onlinebookmark/Default.aspx|||

I'm not sure what you mean by very slow... I just tried your site, clicking on the recently added pages was instaneous... The page came up quickly, and I even created an account, added a page to a couple folders (root, sports), and I couldn't see it taking any time at all.

So..What is slow?

|||Hi,i too need the functionality of importing and exporting bookmarks to my website will u plshelp me in this i mean will u pls give me some code sample.

Iteration within Stored Procedure

A Stored Procedure inserts a record in a base table. I want to add n records (n=1 to 5) into a related table. Another SP (tdAuthorityInsert) is already set up to insert one record into the related table.

So, two questions
1. What is the form of an iterative loop in a Stored Procedure?
2. How do you call a Stored Procedure from inside another?

Thanks, td

1. while

2. just use exec your_stored_procecure_name

|||

Thanks KH. I'm afraid, though, I am very new & your help is a bit too cryptic for me. Sorry but I require a bit more.

In the interim I had figured out the EXEC (I presume this is an abbreviation of EXECUTE) but I have trouble with the parameters. The SP goes something like this:

...AS
@.RequestID BigInt
.....
EXEC InsertReviewer @.RequestID,@.RequestID,@.RequestDate,GETDATE()

The problem is that the SP doing the calling has a parameter of @.Request. So does the called SP. I want to pass the ID through with other parameters.

The WHILE, is it used with a BEGIN/END eg
WHILE @.NumReviewers<5
BEGIN

END

Thanks very much for your patience

|||Is this what you trying to do ?
declare @.NumReviewers int
-- Initialize the @.NumReviewers
select @.NumReviewers = 1
WHILE @.NumReviewers <= 5
BEGIN
-- execute your SP and pass in the @.NumReviewers
exec your_sp_name @.NumReviewers
-- increment the @.NumReviewers
select @.NumReviewers = @.NumReviewers + 1
END

Iteration in a stored procedure

I have a storde procedure which compares one column of a table against each
of the other columns in the same table. If it finds a 'one' in each column,
it counts the number of 'paired data' . As there can be up to 60 columns and
60 rows in any table , can anyone suggest a shorter , more efficient way of
writing the following code? Any assistanc ewould be appreciated. Thank you.
ALTER PROCEDURE GetXXXXXXAll
/*
(
@.IdNumber = 1
)
*/
AS
Begin
/* SET NOCOUNT ON */
SELECT COUNT ([1]) As Expr1,
COUNT([1] + [2]) AS Expr2, COUNT([1] + [3]) AS Expr3, COUNT([1]
+ [4]) AS Expr4, COUNT([1] + [5]) AS Expr5,
COUNT([1] + [6]) AS Expr6,COUNT([1] + [7]) AS Expr7, COUNT([1] +
[8]) AS Expr8, COUNT([1] + [9]) AS Expr9,
COUNT([1] + [10]) AS Expr10, COUNT([1] + [11]) AS Expr11,
COUNT([1] + [12]) AS Expr12,COUNT([1] + [13]) AS Expr13,
COUNT([1] + [14]) AS Expr14, COUNT([1] + [15]) AS Expr15,
COUNT([1] + [16]) AS Expr16,COUNT([1] + [17]) AS Expr17,
COUNT([1] + [18]) AS Expr18, COUNT([1] + [19]) AS Expr19,
COUNT([1] + [20]) AS Expr20,COUNT([1] + [21]) AS Expr21,
COUNT([1] + [22]) AS Expr22, COUNT([1] + [23]) AS Expr23,
COUNT([1] + [24]) AS Expr24,COUNT([1] + [25]) AS Expr25,
COUNT([1] + [26]) AS Expr26, COUNT([1] + [27]) AS Expr27,
COUNT([1] + [28]) AS Expr28,COUNT([1] + [29]) AS Expr29,
COUNT([1] + [30]) AS Expr30,
COUNT ([2]) As Expr31,
COUNT([2] + [3]) AS Expr32, COUNT([2] + [4]) AS Expr33,
COUNT([2] + [5]) AS Expr34,
COUNT([2] + [6]) AS Expr35,COUNT([2] + [7]) AS Expr36, COUNT([2]
+ [8]) AS Expr37, COUNT([2] + [9]) AS Expr38,
COUNT([2] + [10]) AS Expr39, COUNT([2] + [11]) AS Expr40,
COUNT([2] + [12]) AS Expr41,COUNT([2] + [13]) AS Expr42,
COUNT([2] + [14]) AS Expr43, COUNT([2] + [15]) AS Expr44,
COUNT([2] + [16]) AS Expr45,COUNT([2] + [17]) AS Expr46,
COUNT([2] + [18]) AS Expr47, COUNT([2] + [19]) AS Expr48,
COUNT([2] + [20]) AS Expr49,COUNT([2] + [21]) AS Expr50,
COUNT([2] + [22]) AS Expr51, COUNT([2] + [23]) AS Expr52,
COUNT([2] + [24]) AS Expr53,COUNT([2] + [25]) AS Expr54,
COUNT([2] + [26]) AS Expr55, COUNT([2] + [27]) AS Expr56,
COUNT([2] + [28]) AS Expr57,COUNT([2] + [29]) AS Expr58,
COUNT([2] + [30]) AS Expr59,
COUNT ([3]) As Expr60,
COUNT([3] + [4]) AS Expr61, COUNT([3] + [5]) AS Expr62,
COUNT([3] + [6]) AS Expr63,COUNT([3] + [7]) AS Expr64, COUNT([3]
+ [8]) AS Expr65, COUNT([3] + [9]) AS Expr66,
COUNT([3] + [10]) AS Expr67, COUNT([3] + [11]) AS Expr68,
COUNT([3] + [12]) AS Expr69,COUNT([3] + [13]) AS Expr70,
COUNT([3] + [14]) AS Expr71, COUNT([3] + [15]) AS Expr72,
COUNT([3] + [16]) AS Expr73,COUNT([3] + [17]) AS Expr74,
COUNT([3] + [18]) AS Expr75, COUNT([3] + [19]) AS Expr76,
COUNT([3] + [20]) AS Expr77,COUNT([3] + [21]) AS Expr78,
COUNT([3] + [22]) AS Expr79, COUNT([3] + [23]) AS Expr80,
COUNT([3] + [24]) AS Expr81,COUNT([3] + [25]) AS Expr82,
COUNT([3] + [26]) AS Expr83, COUNT([3] + [27]) AS Expr84,
COUNT([3] + [28]) AS Expr85,COUNT([3] + [29]) AS Expr86,
COUNT([3] + [30]) AS Expr87,
COUNT ([4]) As Expr88,
COUNT([4] + [5]) AS Expr89,
COUNT([4] + [6]) AS Expr90,COUNT([4] + [7]) AS Expr91, COUNT([4]
+ [8]) AS Expr92, COUNT([4] + [9]) AS Expr93,
COUNT([4] + [10]) AS Expr94, COUNT([4] + [11]) AS Expr95,
COUNT([4] + [12]) AS Expr96,COUNT([4] + [13]) AS Expr97,
COUNT([4] + [14]) AS Expr98, COUNT([4] + [15]) AS Expr99,
COUNT([4] + [16]) AS Expr100,COUNT([4] + [17]) AS Expr101,
COUNT([4] + [18]) AS Expr102, COUNT([4] + [19]) AS Expr103,
COUNT([4] + [20]) AS Expr104,COUNT([4] + [21]) AS Expr105,
COUNT([4] + [22]) AS Expr106, COUNT([4] + [23]) AS Expr107,
COUNT([4] + [24]) AS Expr108,COUNT([4] + [25]) AS Expr109,
COUNT([4] + [26]) AS Expr110, COUNT([4] + [27]) AS Expr111,
COUNT([4] + [28]) AS Expr112,COUNT([4] + [29]) AS Expr113,
COUNT([4] + [30]) AS Expr114,
COUNT ([5]) As Expr115,
COUNT([5] + [6]) AS Expr116,COUNT([5] + [7]) AS Expr117,
COUNT([5] + [8]) AS Expr118, COUNT([5] + [9]) AS Expr119,
COUNT([5] + [10]) AS Expr120, COUNT([5] + [11]) AS Expr121,
COUNT([5] + [12]) AS Expr122,COUNT([5] + [13]) AS Expr123,
COUNT([5] + [14]) AS Expr124, COUNT([5] + [15]) AS Expr125,
COUNT([5] + [16]) AS Expr126,COUNT([5] + [17]) AS Expr127,
COUNT([5] + [18]) AS Expr128, COUNT([5] + [19]) AS Expr129,
COUNT([5] + [20]) AS Expr130,COUNT([5] + [21]) AS Expr131,
COUNT([5] + [22]) AS Expr132, COUNT([5] + [23]) AS Expr133,
COUNT([5] + [24]) AS Expr134,COUNT([5] + [25]) AS Expr135,
COUNT([5] + [26]) AS Expr136, COUNT([5] + [27]) AS Expr137,
COUNT([5] + [28]) AS Expr138,COUNT([5] + [29]) AS Expr139,
COUNT([5] + [30]) AS Expr140
.
.
. etc
.
.
COUNT([60] + [60]) AS Expr1859
FROM XXXXXXFrequencyTable
WHERE ([1] = 1) OR
([1] = 1) AND ([2] = 1) OR
([1] = 1) AND ([3] = 1) OR
([1] = 1) AND ([4] = 1) OR
([1] = 1) AND ([5] = 1) OR
([1] = 1) AND ([6] = 1) OR
([1] = 1) AND ([7] = 1) OR
([1] = 1) AND ([8] = 1) OR
([1] = 1) AND ([9] = 1) OR
([1] = 1) AND ([10] = 1) OR
([1] = 1) AND ([11] = 1) OR
([1] = 1) AND ([12] = 1) OR
([1] = 1) AND ([13] = 1) OR
([1] = 1) AND ([14] = 1) OR
([1] = 1) AND ([15] = 1) OR
([1] = 1) AND ([16] = 1) OR
([1] = 1) AND ([17] = 1) OR
([1] = 1) AND ([18] = 1) OR
([1] = 1) AND ([19] = 1) OR
([1] = 1) AND ([20] = 1) OR
([1] = 1) AND ([21] = 1) OR
([1] = 1) AND ([22] = 1) OR
([1] = 1) AND ([23] = 1) OR
([1] = 1) AND ([24] = 1) OR
([1] = 1) AND ([25] = 1) OR
([1] = 1) AND ([26] = 1) OR
([1] = 1) AND ([27] = 1) OR
([1] = 1) AND ([28] = 1) OR
([1] = 1) AND ([29] = 1) OR
([1] = 1) AND ([30] = 1) OR
([2] = 1) OR
([2] = 1) AND ([3] = 1) OR
([2] = 1) AND ([4] = 1) OR
([2] = 1) AND ([5] = 1) OR
([2] = 1) AND ([6] = 1) OR
([2] = 1) AND ([7] = 1) OR
([2] = 1) AND ([8] = 1) OR
([2] = 1) AND ([9] = 1) OR
([2] = 1) AND ([10] = 1) OR
([2] = 1) AND ([11] = 1) OR
([2] = 1) AND ([12] = 1) OR
([2] = 1) AND ([13] = 1) OR
([2] = 1) AND ([14] = 1) OR
([2] = 1) AND ([15] = 1) OR
([2] = 1) AND ([16] = 1) OR
([2] = 1) AND ([17] = 1) OR
([2] = 1) AND ([18] = 1) OR
([2] = 1) AND ([19] = 1) OR
([2] = 1) AND ([20] = 1) OR
([2] = 1) AND ([21] = 1) OR
([2] = 1) AND ([22] = 1) OR
([2] = 1) AND ([23] = 1) OR
([2] = 1) AND ([24] = 1) OR
([2] = 1) AND ([25] = 1) OR
([2] = 1) AND ([26] = 1) OR
([2] = 1) AND ([27] = 1) OR
([2] = 1) AND ([28] = 1) OR
([2] = 1) AND ([29] = 1) OR
([2] = 1) AND ([30] = 1) OR
([3] = 1) OR
([3] = 1) AND ([4] = 1) OR
([3] = 1) AND ([5] = 1) OR
([3] = 1) AND ([6] = 1) OR
([3] = 1) AND ([7] = 1) OR
([3] = 1) AND ([8] = 1) OR
([3] = 1) AND ([9] = 1) OR
([3] = 1) AND ([10] = 1) OR
([3] = 1) AND ([11] = 1) OR
([3] = 1) AND ([12] = 1) OR
([3] = 1) AND ([13] = 1) OR
([3] = 1) AND ([14] = 1) OR
([3] = 1) AND ([15] = 1) OR
([3] = 1) AND ([16] = 1) OR
([3] = 1) AND ([17] = 1) OR
([3] = 1) AND ([18] = 1) OR
([3] = 1) AND ([19] = 1) OR
([3] = 1) AND ([20] = 1) OR
([3] = 1) AND ([21] = 1) OR
([3] = 1) AND ([22] = 1) OR
([3] = 1) AND ([23] = 1) OR
([3] = 1) AND ([24] = 1) OR
([3] = 1) AND ([25] = 1) OR
([3] = 1) AND ([26] = 1) OR
([3] = 1) AND ([27] = 1) OR
([3] = 1) AND ([28] = 1) OR
([3] = 1) AND ([29] = 1) OR
([3] = 1) AND ([30] = 1) OR
([4] = 1) OR
([4] = 1) AND ([5] = 1) OR
([4] = 1) AND ([6] = 1) OR
([4] = 1) AND ([7] = 1) OR
([4] = 1) AND ([8] = 1) OR
([4] = 1) AND ([9] = 1) OR
([4] = 1) AND ([10] = 1) OR
([4] = 1) AND ([11] = 1) OR
([4] = 1) AND ([12] = 1) OR
([4] = 1) AND ([13] = 1) OR
([4] = 1) AND ([14] = 1) OR
([4] = 1) AND ([15] = 1) OR
([4] = 1) AND ([16] = 1) OR
([4] = 1) AND ([17] = 1) OR
([4] = 1) AND ([18] = 1) OR
([4] = 1) AND ([19] = 1) OR
([4] = 1) AND ([20] = 1) OR
([4] = 1) AND ([21] = 1) OR
([4] = 1) AND ([22] = 1) OR
([4] = 1) AND ([23] = 1) OR
([4] = 1) AND ([24] = 1) OR
([4] = 1) AND ([25] = 1) OR
([4] = 1) AND ([26] = 1) OR
([4] = 1) AND ([27] = 1) OR
([4] = 1) AND ([28] = 1) OR
([4] = 1) AND ([29] = 1) OR
([4] = 1) AND ([30] = 1) OR
([5] = 1) OR
([5] = 1) AND ([6] = 1) OR
([5] = 1) AND ([7] = 1) OR
([5] = 1) AND ([8] = 1) OR
([5] = 1) AND ([9] = 1) OR
([5] = 1) AND ([10] = 1) OR
([5] = 1) AND ([11] = 1) OR
([5] = 1) AND ([12] = 1) OR
([5] = 1) AND ([13] = 1) OR
([5] = 1) AND ([14] = 1) OR
([5] = 1) AND ([15] = 1) OR
([5] = 1) AND ([16] = 1) OR
([5] = 1) AND ([17] = 1) OR
([5] = 1) AND ([18] = 1) OR
([5] = 1) AND ([19] = 1) OR
([5] = 1) AND ([20] = 1) OR
([5] = 1) AND ([21] = 1) OR
([5] = 1) AND ([22] = 1) OR
([5] = 1) AND ([23] = 1) OR
([5] = 1) AND ([24] = 1) OR
([5] = 1) AND ([25] = 1) OR
([5] = 1) AND ([26] = 1) OR
([5] = 1) AND ([27] = 1) OR
([5] = 1) AND ([28] = 1) OR
([5] = 1) AND ([29] = 1) OR
([5] = 1) AND ([30] = 1)
.
.
. etc
.
([60] = 1) AND ([60] = 1)
ENDonecorp wrote:
> I have a storde procedure which compares one column of a table against eac
h
> of the other columns in the same table. If it finds a 'one' in each column
,
> it counts the number of 'paired data' . As there can be up to 60 columns a
nd
> 60 rows in any table , can anyone suggest a shorter , more efficient way o
f
> writing the following code? Any assistanc ewould be appreciated. Thank you
.
>
Holy moley!!! What in the world is this for? If the values of these
fields are truly limited to 1 or 0, you could probably do something like
SUM([1] * [2]) AS Expr2 instead of COUNT([1] + [2]) AS Expr2, and
eliminate the WHERE clause entirely. If [1] = 1 and [2] = 0, the SUM is
going to increase by zero (1 * 0), essentially the same effect you're
getting by counting WHERE ([1] = 1 AND [2] = 1).
Does that make sense?|||Hi There,
I hope this help
Select 1 R ,1 val1,1 val2,1 val3 , 1 val4 into dummy
insert into dummy Select 2 R ,1 val1,1 val2,1 val3 , 1 val4
insert into dummy Select 3 R ,1 val1,1 val2,1 val3 , 4 val4
Select * from dummy
Select *,
(
case when val1=1 then 1 else 0 end +
case when val2=1 then 1 else 0 end +
case when val3=1 then 1 else 0 end +
case when val4=1 then 1 else 0 end
)/2 As Pairs
From dummy
With Warm regards
Jatinder Singh
http://jatindersingh.blogspot.com
Tracy McKibben wrote:
> onecorp wrote:
> Holy moley!!! What in the world is this for? If the values of these
> fields are truly limited to 1 or 0, you could probably do something like
> SUM([1] * [2]) AS Expr2 instead of COUNT([1] + [2]) AS Expr2, and
> eliminate the WHERE clause entirely. If [1] = 1 and [2] = 0, the SUM is
> going to increase by zero (1 * 0), essentially the same effect you're
> getting by counting WHERE ([1] = 1 AND [2] = 1).
> Does that make sense?|||Hi,
Thanks for your post!
From your description, I understand that:
A table of your database includes 60 columns and 60 rows.
You wanted to get a 60*60 matrix, in which any item ([x,y]) value
represents the count when column x equals column y and both of their values
are 1.
If I have misunderstood, please feel free to let me know.
If the column type is bit, Tracy's idea is wonderful. I also recommend you
use SUM expression to retrieve the count value.
Otherwise, in this case, the where clause can be written as "WHERE
[1]+[2]+[3]+...+[60]>0".
However if the column type is not bit and the value may be out of 0 and 1,
the where clause should be separated.
I recommend you write a function which can count by accepting the two
parameters of each coordinates.
And then you can get all items count by this way:
SELECT proc_selcount(1,1) as [1_1],proc_selcount(1,2) as
[1_2],proc_selcount(1,3) as [1_3],...,proc_selcount(60,60) as [60_60] into
MATRIX;
SELECT * FROM MATRIX;
If you have any other concerns, please feel free to let me know. It's my
pleasure to be of assistance.
+++++++++++++++++++++++++++
Charles Wang
Microsoft Online Partner Support
+++++++++++++++++++++++++++
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a w

to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/te...erview/40010469
Others:
https://partner.microsoft.com/US/te...upportoverview/
If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/defaul...rnational.aspx.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi,
Can you give the table definition and the insert script for the data? I
have got the result you had mailed.
Moreover, is it always 60 columns or will it change?
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||Hi,
Since I didn't get the data, I created with a temp table which has 6
columns.
It can be extended to 60 columns or how many ever you want, just add those
columns in the pivot and unpivot expression.
Let me know if this was what you expected.
-- Insert script
Select 1 [1],0 [2],1 [3] , 1 [4] , 0 [5], 1 [6] into #temp
insert into #temp Select 1 ,1 ,0 , 1,0,0
insert into #temp Select 1 ,1 ,1 , 1 ,1,1
insert into #temp Select 1 ,0 ,0 , 1,1,0
insert into #temp Select 1 ,1 ,1 , 0,0,1
insert into #temp Select 0 ,1 ,1 , 0,1,0
--Query
with cte as(
select row_id, loc, val from
(select row_number() over (order by [1]) as row_id, * from #temp) as a
unpivot (val for loc in ([1],[2],[3],[4],[5],[6])) as U
), cte1 as(
select a.loc as loc,b.loc as loc2,sum(a.val*b.val) as cnt from cte a, cte b
where a.row_id = b.row_id
and a.loc <= b.loc
group by a.loc,b.loc
)
select * from cte1
pivot ( sum(cnt) for loc2 in ([1],[2],[3],[4],[5],[6])) as P
Hope this helps.
-Omni|||Thank you to all for their assistance.
To Omnibuzz
....my apologies for the lack of a reply, but it was probably dut to time
differences etc.
I originally derived the table by using in-line SQL. I was then advised that
that was not a good idea, and hence came up with the stored procedure (one
example of my attempts being the one I posted ).
Regarding the table definition:
At the first block of code referring to NUmber [1] I used create table.
At each block thereafter, I used 'insert into table' thereby building up
each row.
To display the results, I used a gridview control to obtain the data from
the tbale created in the stored procedure and a 'sort' to get the rows in th
e
right order.
In your reply you wrote:
-- Insert script
Select 1 [1],0 [2],1 [3] , 1 [4] , 0 [5], 1 [6] into #temp
insert into #temp Select 1 ,1 ,0 , 1,0,0
insert into #temp Select 1 ,1 ,1 , 1 ,1,1
insert into #temp Select 1 ,0 ,0 , 1,1,0
insert into #temp Select 1 ,1 ,1 , 0,0,1
insert into #temp Select 0 ,1 ,1 , 0,1,0
Why in the select stmt is it 1 [1],0 [2],1 [3] , 1 [4] , 0 [5], 1 [6] and
not
1 [1],1 [2],1 [3] , 1 [4] , 1 [5], 1 [6] ?
What do the 'ones' and 'zeroes' mean in this exp ? insert into #temp Select
1 ,1 ,0 , 1,0,0
Dear Mr WANG,
Can you please tell me what proc_selcount means?
Thank you
"onecorp" wrote:

> I have a stored procedure which compares one column of a table against eac
h
> of the other columns in the same table. If it finds a 'one' in each column
,
> it counts the number of 'paired data' . As there can be up to 60 columns a
nd
> 60 rows in any table , can anyone suggest a shorter , more efficient way o
f
> writing the following code? Any assistance would be appreciated. Thank you
.
> ALTER PROCEDURE GetXXXXXXAll
> /*
> (
> @.IdNumber = 1
> )
> */
> AS
> Begin
> /* SET NOCOUNT ON */
> SELECT COUNT ([1]) As Expr1,
> COUNT([1] + [2]) AS Expr2, COUNT([1] + [3]) AS Expr3, COUNT([1
]
> + [4]) AS Expr4, COUNT([1] + [5]) AS Expr5,
> COUNT([1] + [6]) AS Expr6,COUNT([1] + [7]) AS Expr7, COUNT([1]
+
> [8]) AS Expr8, COUNT([1] + [9]) AS Expr9,
> COUNT([1] + [10]) AS Expr10, COUNT([1] + [11]) AS Expr11,
> COUNT([1] + [12]) AS Expr12,COUNT([1] + [13]) AS Expr13,
> COUNT([1] + [14]) AS Expr14, COUNT([1] + [15]) AS Expr15,
> COUNT([1] + [16]) AS Expr16,COUNT([1] + [17]) AS Expr17,
> COUNT([1] + [18]) AS Expr18, COUNT([1] + [19]) AS Expr19,
> COUNT([1] + [20]) AS Expr20,COUNT([1] + [21]) AS Expr21,
> COUNT([1] + [22]) AS Expr22, COUNT([1] + [23]) AS Expr23,
> COUNT([1] + [24]) AS Expr24,COUNT([1] + [25]) AS Expr25,
> COUNT([1] + [26]) AS Expr26, COUNT([1] + [27]) AS Expr27,
> COUNT([1] + [28]) AS Expr28,COUNT([1] + [29]) AS Expr29,
> COUNT([1] + [30]) AS Expr30,
> COUNT ([2]) As Expr31,
> COUNT([2] + [3]) AS Expr32, COUNT([2] + [4]) AS Expr33,
> COUNT([2] + [5]) AS Expr34,
> COUNT([2] + [6]) AS Expr35,COUNT([2] + [7]) AS Expr36, COUNT([
2]
> + [8]) AS Expr37, COUNT([2] + [9]) AS Expr38,
> COUNT([2] + [10]) AS Expr39, COUNT([2] + [11]) AS Expr40,
> COUNT([2] + [12]) AS Expr41,COUNT([2] + [13]) AS Expr42,
> COUNT([2] + [14]) AS Expr43, COUNT([2] + [15]) AS Expr44,
> COUNT([2] + [16]) AS Expr45,COUNT([2] + [17]) AS Expr46,
> COUNT([2] + [18]) AS Expr47, COUNT([2] + [19]) AS Expr48,
> COUNT([2] + [20]) AS Expr49,COUNT([2] + [21]) AS Expr50,
> COUNT([2] + [22]) AS Expr51, COUNT([2] + [23]) AS Expr52,
> COUNT([2] + [24]) AS Expr53,COUNT([2] + [25]) AS Expr54,
> COUNT([2] + [26]) AS Expr55, COUNT([2] + [27]) AS Expr56,
> COUNT([2] + [28]) AS Expr57,COUNT([2] + [29]) AS Expr58,
> COUNT([2] + [30]) AS Expr59,
> COUNT ([3]) As Expr60,
> COUNT([3] + [4]) AS Expr61, COUNT([3] + [5]) AS Expr62,
> COUNT([3] + [6]) AS Expr63,COUNT([3] + [7]) AS Expr64, COUNT([
3]
> + [8]) AS Expr65, COUNT([3] + [9]) AS Expr66,
> COUNT([3] + [10]) AS Expr67, COUNT([3] + [11]) AS Expr68,
> COUNT([3] + [12]) AS Expr69,COUNT([3] + [13]) AS Expr70,
> COUNT([3] + [14]) AS Expr71, COUNT([3] + [15]) AS Expr72,
> COUNT([3] + [16]) AS Expr73,COUNT([3] + [17]) AS Expr74,
> COUNT([3] + [18]) AS Expr75, COUNT([3] + [19]) AS Expr76,
> COUNT([3] + [20]) AS Expr77,COUNT([3] + [21]) AS Expr78,
> COUNT([3] + [22]) AS Expr79, COUNT([3] + [23]) AS Expr80,
> COUNT([3] + [24]) AS Expr81,COUNT([3] + [25]) AS Expr82,
> COUNT([3] + [26]) AS Expr83, COUNT([3] + [27]) AS Expr84,
> COUNT([3] + [28]) AS Expr85,COUNT([3] + [29]) AS Expr86,
> COUNT([3] + [30]) AS Expr87,
> COUNT ([4]) As Expr88,
> COUNT([4] + [5]) AS Expr89,
> COUNT([4] + [6]) AS Expr90,COUNT([4] + [7]) AS Expr91, COUNT([
4]
> + [8]) AS Expr92, COUNT([4] + [9]) AS Expr93,
> COUNT([4] + [10]) AS Expr94, COUNT([4] + [11]) AS Expr95,
> COUNT([4] + [12]) AS Expr96,COUNT([4] + [13]) AS Expr97,
> COUNT([4] + [14]) AS Expr98, COUNT([4] + [15]) AS Expr99,
> COUNT([4] + [16]) AS Expr100,COUNT([4] + [17]) AS Expr101,
> COUNT([4] + [18]) AS Expr102, COUNT([4] + [19]) AS Expr103,
> COUNT([4] + [20]) AS Expr104,COUNT([4] + [21]) AS Expr105,
> COUNT([4] + [22]) AS Expr106, COUNT([4] + [23]) AS Expr107,
> COUNT([4] + [24]) AS Expr108,COUNT([4] + [25]) AS Expr109,
> COUNT([4] + [26]) AS Expr110, COUNT([4] + [27]) AS Expr111,
> COUNT([4] + [28]) AS Expr112,COUNT([4] + [29]) AS Expr113,
> COUNT([4] + [30]) AS Expr114,
> COUNT ([5]) As Expr115,
> COUNT([5] + [6]) AS Expr116,COUNT([5] + [7]) AS Expr117,
> COUNT([5] + [8]) AS Expr118, COUNT([5] + [9]) AS Expr119,
> COUNT([5] + [10]) AS Expr120, COUNT([5] + [11]) AS Expr121,
> COUNT([5] + [12]) AS Expr122,COUNT([5] + [13]) AS Expr123,
> COUNT([5] + [14]) AS Expr124, COUNT([5] + [15]) AS Expr125,
> COUNT([5] + [16]) AS Expr126,COUNT([5] + [17]) AS Expr127,
> COUNT([5] + [18]) AS Expr128, COUNT([5] + [19]) AS Expr129,
> COUNT([5] + [20]) AS Expr130,COUNT([5] + [21]) AS Expr131,
> COUNT([5] + [22]) AS Expr132, COUNT([5] + [23]) AS Expr133,
> COUNT([5] + [24]) AS Expr134,COUNT([5] + [25]) AS Expr135,
> COUNT([5] + [26]) AS Expr136, COUNT([5] + [27]) AS Expr137,
> COUNT([5] + [28]) AS Expr138,COUNT([5] + [29]) AS Expr139,
> COUNT([5] + [30]) AS Expr140
> .
> .
> . etc
> .
> .
> COUNT([60] + [60]) AS Expr1859
>
> FROM XXXXXXFrequencyTable
> WHERE ([1] = 1) OR
> ([1] = 1) AND ([2] = 1) OR
> ([1] = 1) AND ([3] = 1) OR
> ([1] = 1) AND ([4] = 1) OR
> ([1] = 1) AND ([5] = 1) OR
> ([1] = 1) AND ([6] = 1) OR
> ([1] = 1) AND ([7] = 1) OR
> ([1] = 1) AND ([8] = 1) OR
> ([1] = 1) AND ([9] = 1) OR
> ([1] = 1) AND ([10] = 1) OR
> ([1] = 1) AND ([11] = 1) OR
> ([1] = 1) AND ([12] = 1) OR
> ([1] = 1) AND ([13] = 1) OR
> ([1] = 1) AND ([14] = 1) OR
> ([1] = 1) AND ([15] = 1) OR
> ([1] = 1) AND ([16] = 1) OR
> ([1] = 1) AND ([17] = 1) OR
> ([1] = 1) AND ([18] = 1) OR
> ([1] = 1) AND ([19] = 1) OR
> ([1] = 1) AND ([20] = 1) OR
> ([1] = 1) AND ([21] = 1) OR
> ([1] = 1) AND ([22] = 1) OR
> ([1] = 1) AND ([23] = 1) OR
> ([1] = 1) AND ([24] = 1) OR
> ([1] = 1) AND ([25] = 1) OR
> ([1] = 1) AND ([26] = 1) OR
> ([1] = 1) AND ([27] = 1) OR
> ([1] = 1) AND ([28] = 1) OR
> ([1] = 1) AND ([29] = 1) OR
> ([1] = 1) AND ([30] = 1) OR
> ([2] = 1) OR
> ([2] = 1) AND ([3] = 1) OR
> ([2] = 1) AND ([4] = 1) OR
> ([2] = 1) AND ([5] = 1) OR
> ([2] = 1) AND ([6] = 1) OR
> ([2] = 1) AND ([7] = 1) OR
> ([2] = 1) AND ([8] = 1) OR
> ([2] = 1) AND ([9] = 1) OR
> ([2] = 1) AND ([10] = 1) OR
> ([2] = 1) AND ([11] = 1) OR
> ([2] = 1) AND ([12] = 1) OR
> ([2] = 1) AND ([13] = 1) OR
> ([2] = 1) AND ([14] = 1) OR
> ([2] = 1) AND ([15] = 1) OR
> ([2] = 1) AND ([16] = 1) OR
> ([2] = 1) AND ([17] = 1) OR
> ([2] = 1) AND ([18] = 1) OR
> ([2] = 1) AND ([19] = 1) OR
> ([2] = 1) AND ([20] = 1) OR
> ([2] = 1) AND ([21] = 1) OR
> ([2] = 1) AND ([22] = 1) OR
> ([2] = 1) AND ([23] = 1) OR
> ([2] = 1) AND ([24] = 1) OR
> ([2] = 1) AND ([25] = 1) OR
> ([2] = 1) AND ([26] = 1) OR
> ([2] = 1) AND ([27] = 1) OR
> ([2] = 1) AND ([28] = 1) OR
> ([2] = 1) AND ([29] = 1) OR
> ([2] = 1) AND ([30] = 1) OR
> ([3] = 1) OR
> ([3] = 1) AND ([4] = 1) OR
> ([3] = 1) AND ([5] = 1) OR
> ([3] = 1) AND ([6] = 1) OR
> ([3] = 1) AND ([7] = 1) OR
> ([3] = 1) AND ([8] = 1) OR
> ([3] = 1) AND ([9] = 1) OR
> ([3] = 1) AND ([10] = 1) OR
> ([3] = 1) AND ([11] = 1) OR
> ([3] = 1) AND ([12] = 1) OR
> ([3] = 1) AND ([13] = 1) OR
> ([3] = 1) AND ([14] = 1) OR
> ([3] = 1) AND ([15] = 1) OR
> ([3] = 1) AND ([16] = 1) OR
> ([3] = 1) AND ([17] = 1) OR
> ([3] = 1) AND ([18] = 1) OR
> ([3] = 1) AND ([19] = 1) OR
> ([3] = 1) AND ([20] = 1) OR
> ([3] = 1) AND ([21] = 1) OR
> ([3] = 1) AND ([22] = 1) OR
> ([3] = 1) AND ([23] = 1) OR
> ([3] = 1) AND ([24] = 1) OR
> ([3] = 1) AND ([25] = 1) OR
> ([3] = 1) AND ([26] = 1) OR
> ([3] = 1) AND ([27] = 1) OR
> ([3] = 1) AND ([28] = 1) OR
> ([3] = 1) AND ([29] = 1) OR
> ([3] = 1) AND ([30] = 1) OR
> ([4] = 1) OR
> ([4] = 1) AND ([5] = 1) OR
> ([4] = 1) AND ([6] = 1) OR
> ([4] = 1) AND ([7] = 1) OR
> ([4] = 1) AND ([8] = 1) OR
> ([4] = 1) AND ([9] = 1) OR
> ([4] = 1) AND ([10] = 1) OR
> ([4] = 1) AND ([11] = 1) OR
> ([4] = 1) AND ([12] = 1) OR
> ([4] = 1) AND ([13] = 1) OR
> ([4] = 1) AND ([14] = 1) OR
> ([4] = 1) AND ([15] = 1) OR
> ([4] = 1) AND ([16] = 1) OR
> ([4] = 1) AND ([17] = 1) OR
> ([4] = 1) AND ([18] = 1) OR
> ([4] = 1) AND ([19] = 1) OR
> ([4] = 1) AND ([20] = 1) OR
> ([4] = 1) AND ([21] = 1) OR
> ([4] = 1) AND ([22] = 1) OR
> ([4] = 1) AND ([23] = 1) OR
> ([4] = 1) AND ([24] = 1) OR
> ([4] = 1) AND ([25] = 1) OR
> ([4] = 1) AND ([26] = 1) OR
> ([4] = 1) AND ([27] = 1) OR
> ([4] = 1) AND ([28] = 1) OR
> ([4] = 1) AND ([29] = 1) OR
> ([4] = 1) AND ([30] = 1) OR
> ([5] = 1) OR
> ([5] = 1) AND ([6] = 1) OR
> ([5] = 1) AND ([7] = 1) OR
> ([5] = 1) AND ([8] = 1) OR
> ([5] = 1) AND ([9] = 1) OR
> ([5] = 1) AND ([10] = 1) OR
> ([5] = 1) AND ([11] = 1) OR
> ([5] = 1) AND ([12] = 1) OR
> ([5] = 1) AND ([13] = 1) OR
> ([5] = 1) AND ([14] = 1) OR
> ([5] = 1) AND ([15] = 1) OR
> ([5] = 1) AND ([16] = 1) OR
> ([5] = 1) AND ([17] = 1) OR
> ([5] = 1) AND ([18] = 1) OR
> ([5] = 1) AND ([19] = 1) OR
> ([5] = 1) AND ([20] = 1) OR
> ([5] = 1) AND ([21] = 1) OR
> ([5] = 1) AND ([22] = 1) OR
> ([5] = 1) AND ([23] = 1) OR
> ([5] = 1) AND ([24] = 1) OR
> ([5] = 1) AND ([25] = 1) OR
> ([5] = 1) AND ([26] = 1) OR
> ([5] = 1) AND ([27] = 1) OR
> ([5] = 1) AND ([28] = 1) OR
> ([5] = 1) AND ([29] = 1) OR
> ([5] = 1) AND ([30] = 1)
> .
> .
> . etc
> .
> ([60] = 1) AND ([60] = 1)
> END|||The way I understood...
The values in the columns 1 through 6 can have 0s or 1s.
And you want to find the count of all possible pair of columns where both
have the value 1. Its just a sample data. You can have it as all 1s or all 0
s.
Did you run the code and check?
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||Hi,
Thanks for your response.
The proc_selcount is a custom function that can count any one or two
columns equaling 1.
However this name is not canonical, you can specify the name according to
your standard.
+++++++++++++++++++++++++++
Charles Wang
Microsoft Online Partner Support
+++++++++++++++++++++++++++
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

Iterating through tables

I want to create a stored procedure that will list all the tables for a
given database. I can get the databases using the sp_database function, but
the sp_tables requires that I be in database to use it. I want to create a
stored procedure that will take in the database name as a variable and
return a list of the tables for that database. I need to do this so as new
databases and tables are added and removed my program will have the up to
date data for the database. thanks.
JohnDECLARE @.dbname NVARCHAR(128), @.sql NVARCHAR(4000)
SET @.dbname = 'pubs'
SET @.sql =
'SELECT [table_name]
FROM '+QUOTENAME(@.dbname)+'.[information_schema].[tables]'
EXEC (@.sql)
David Portas
SQL Server MVP
--|||>> take in the database name as a variable and return a list of the tables f
or that database. <<
Have you considered that this is a METADATA problem and should be done
with sp_ routines and never in your own stored procedure? This will
lead to kludgfes with dynamic SQL, etc.
Gee, do you do that a lot? Do your users get to create tables, columns
or entire databases on the fly? Your whole approach sounds screwed up.
Would you like to expalin what you are doing so you can get a few
thousand dollars of freee consulting or do you just want a stinking
dirty little kludge?
No, your data dictionary should contain this kinfd of information, not
the programs.

Iterating through a recordset

I am a newly indoctrinated SQL Server 2005 user. So, forgive me if this
question is amatuer.
I am trying to create a stored proc that will read in a list of values
from one table (Possibly using a cte?) then iterate through each record
running another sp to populate a temp table and return all matching
records. The logic would be like the following:
///////
create procedure spSample1
@.column2 int
As
create table #tmptbl
(tmptblcol1 int not null)
with samplecte (var1 int)
As
(select column1 from table where column2 = @.column2)
begin
while not end-of-file
set localvar1 = current value of samplecte var1
insert into #tmptbl exec spSample2 localvar1
end
////////
This is a very rough outline. Hopefully it helps with clarification of
what I am trying to acheive. Any help would be greatly appreciated.
Thank you in advance!
rbrryankbrown@.gmail.com wrote:
> I am a newly indoctrinated SQL Server 2005 user. So, forgive me if this
> question is amatuer.
> I am trying to create a stored proc that will read in a list of values
> from one table (Possibly using a cte?) then iterate through each record
> running another sp to populate a temp table and return all matching
> records. The logic would be like the following:
> ///////
> create procedure spSample1
> @.column2 int
> As
> create table #tmptbl
> (tmptblcol1 int not null)
> with samplecte (var1 int)
> As
> (select column1 from table where column2 = @.column2)
> begin
> while not end-of-file
> set localvar1 = current value of samplecte var1
> insert into #tmptbl exec spSample2 localvar1
> end
> ////////
> This is a very rough outline. Hopefully it helps with clarification of
> what I am trying to acheive. Any help would be greatly appreciated.
> Thank you in advance!
> rbr
>
Something like the code below will do what you need, but I'd encourage
you to think "outside the box" and come up with a way to do this without
a temp table. Temp tables are usually unnecessary results of trying to
do "procedural" processes in a set-based environment like SQL.
DECLARE @.LoopVar
SELECT @.LoopVar = 0
SELECT TOP 1 @.LoopVar = <someincrementalID>
FROM table
WHERE <someincrementalID> > @.LoopVar
WHILE @.@.ROWCOUNT > 0
BEGIN
EXEC storedproc @.LoopVar
SELECT TOP 1 @.LoopVar = <someincrementalID>
FROM table
WHERE <someincrementalID> > @.LoopVar
END|||>> I am trying to create a stored proc that will read in a list of values from one tab
le (possibly using a CTE?) then iterate through each record [sic] running another sp t
o populate a temp table and return all matching records [sic]. The logic would be
l
ike the following: <<
You are still thinking of a file system - a magnetic tape file
system, to be more precise, doing a merge from a scratch tape.
Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files; there is no sequential access or
ordering in an RDBMS, so "first", "next" and "last" are totally
meaningless.
SQL is a declarative set-oriented language. We tend to write one
statement to do a job; if I want to insert data into a table, I write
something like:
INSERT INTO Foobar (a, b, c, ..)
SELECT x, y, z, ..
FROM Floob, Snarf, ..
WHERE ..;
One statement, no loops, no if-then control flow. You tell SQL
**what** you want, not **how** to do it. You want to write code as if
you were still in a 3GL procedural language. And I'll bet that your
schema is full of redundancies, too.
Nope, it was too vague to be usable for any concrete suggestions. But
it did demonstrate that you missed the very foundations of RDBMS. Get
a few books (insert shameless plug for my stuff here), clear out
everything you already know and start over. "To drink new tea, you
must first empty the old tea from your cup" - Zen proverb.|||Trust the CELKO grasshopper... He is a curmudgeon and will not hesitate to
tell you what he thinks about a subject but search on his previous posts and
you will learn. Hang around here and lurk. Search is your friend... Good
luck.
"ryankbrown@.gmail.com" wrote:

> I am a newly indoctrinated SQL Server 2005 user. So, forgive me if this
> question is amatuer.
> I am trying to create a stored proc that will read in a list of values
> from one table (Possibly using a cte?) then iterate through each record
> running another sp to populate a temp table and return all matching
> records. The logic would be like the following:
> ///////
> create procedure spSample1
> @.column2 int
> As
> create table #tmptbl
> (tmptblcol1 int not null)
> with samplecte (var1 int)
> As
> (select column1 from table where column2 = @.column2)
> begin
> while not end-of-file
> set localvar1 = current value of samplecte var1
> insert into #tmptbl exec spSample2 localvar1
> end
> ////////
> This is a very rough outline. Hopefully it helps with clarification of
> what I am trying to acheive. Any help would be greatly appreciated.
> Thank you in advance!
> rbr
>|||I trust that you are all correct. As I stated, I am new to this from a
much more procedural background. As most of us do when faced with a new
problem we fall back on what we know best.
I already wrote a simple sp that does the job just fine. I was asked by
a superior to find a way to do it reusing another pre-existing sp to
acheive the same result. This is what spawned my question.
Thanks for imparting your wisdom upon me!
rbr
StvJston wrote:
> Trust the CELKO grasshopper... He is a curmudgeon and will not hesitate to
> tell you what he thinks about a subject but search on his previous posts a
nd
> you will learn. Hang around here and lurk. Search is your friend... Goo
d
> luck.
> "ryankbrown@.gmail.com" wrote:
>|||Oh, and by the way...
All is fine with my db. No redundancies here. The only complaint i've
heard from my programmers is that it may be over-normalized.
But thanks for the unproductive insult anyway.
rbr
rbr wrote:
> I trust that you are all correct. As I stated, I am new to this from a
> much more procedural background. As most of us do when faced with a new
> problem we fall back on what we know best.
> I already wrote a simple sp that does the job just fine. I was asked by
> a superior to find a way to do it reusing another pre-existing sp to
> acheive the same result. This is what spawned my question.
> Thanks for imparting your wisdom upon me!
> rbr
> StvJston wrote:

Monday, March 26, 2012

Iterate result set inside stored procedure

Hello,

I have a situation that I query a table and return multiple rows (email addresses). I want to iterate through the rows and concatenate all email addresses into one string (will be passing this to another stored procedure to send mail).

How can I process result rows inside a stored procedure?

This is what I have so far:

CREATE PROCEDURE [dbo].[lm_emailComment_OnInsert]
@.serviceDetailIDint,
@.commentvarchar(500),
@.commentDateDateTime,
@.commentAuthorvarchar(100)
AS
BEGIN
DECLARE @.serviceIDint
DECLARE @.p_recipientsvarchar(8000)
DECLARE @.p_messagevarchar(8000)
DECLARE @.p_subjectvarchar(100)/* Grab the Service_id from underlying Service_Detail_id*/
SELECT @.serviceID = Service_idFROM lm_Service_DetailWHERE Service_Detail_id = @.serviceDetailID
/* Get email addresses of Service Responsible Parties */SELECT DISTINCT dbo.lm_Responsible_Party.Email
FROM dbo.lm_Service_DetailINNERJOIN dbo.lm_Service_Filing_TypeON dbo.lm_Service_Detail.Service_id = dbo.lm_Service_Filing_Type.Service_idINNERJOIN dbo.lm_Responsible_Party_Filing_TypeON
 dbo.lm_Service_Filing_Type.Filing_Type_id = dbo.lm_Responsible_Party_Filing_Type.Filing_Type_idINNERJOIN dbo.lm_Responsible_PartyON dbo.lm_Responsible_Party_Filing_Type.Party_id = dbo.lm_Responsible_Party.Party_id
WHERE (dbo.lm_Service_Detail.Service_Detail_id = @.serviceDetailID)
/* Build message */SET @.p_subject = "KLM - Service ID: " +CAST(@.serviceIDAS varchar(4))
SET @.p_recipients = ""/*need string of addresses*/SET @.p_message = @.p_message + "Service Detail ID: " +CAST(@.serviceDetailIDAS varchar(4)) +char(13)
SET @.p_message = @.p_message + "Comment Date: " +CAST(@.commentDateAs varchar(25)) +char(13)
SET @.p_message = @.p_message + "Comment Author: " + @.commentAuthor +char(13)
SET @.p_message = @.p_message + "Comment: " + @.comment +char(13)
PRINT "subject: " + @.p_subject +char(13)
PRINT "recip: " + @.p_recipients +char(13)
PRINT "msg: " + @.p_message +char(13)
/*Send the email*/Execute master..xp_sendmail @.recipients = @.p_recipients, @.message = @.p_message, @.subject = @.p_subject
ENDGO

Hi,
You can declare a variable and append the results onto it for each row with:

DECLARE @.emails varchar(1000)

SELECT @.emails = isnull( @.emails,'' ) + ', ' + dbo.lm_Responsible_Party.Email
FROM xxxxxx

This gives you the email addresses in comma delimted form:
"me@.me.com, you@.you.com, www.this.com"

The only caveat is that I don't believe you can use the DISTINCT with this. So you would need to amend your SELECT so that it does not use this.
You could do this with:

SELECT @.emails = isnull(@.emails,'') + ', ' + Email
FROM
( SELECT DISTINCT dbo.lm_Responsible_Party.Email AS Email
FROM dbo.lm_Service_DetailINNERJOIN
xxxx
) subquery

|||

Awesome, thanks! I got the concatenation working; and you were right about the DISTINCT command. So, when I tried the way you suggested (putting DISTINCT in select below), I kept getting errors. It didn't like the statement. So, any other ideas on how to retrieve only unique values?

Also, I get a leading comma - how do I avoid that on the first entry?

Ex: "recip: , xxx@.xxx.com, yyy@.yyy.com"

|||

I got it! Needed to assign a derived table.

Still have the comma issue, though.

/* Get email addresses of Service Responsible Parties */
SELECT @.emails =isnull(@.emails,'') +', ' + tmpEmail
FROM (
SELECT DISTINCT dbo.lm_Responsible_Party.EmailAS tmpEmail
FROM dbo.lm_Service_DetailINNERJOIN
 dbo.lm_Service_Filing_TypeON dbo.lm_Service_Detail.Service_id = dbo.lm_Service_Filing_Type.Service_idINNERJOIN
 dbo.lm_Responsible_Party_Filing_TypeON
 dbo.lm_Service_Filing_Type.Filing_Type_id = dbo.lm_Responsible_Party_Filing_Type.Filing_Type_idINNERJOIN
 dbo.lm_Responsible_PartyON dbo.lm_Responsible_Party_Filing_Type.Party_id = dbo.lm_Responsible_Party.Party_id
WHERE (dbo.lm_Service_Detail.Service_Detail_id = @.serviceDetailID))AS derivedtbl_1

|||

The leading comma is probably because you may have assigned a value to the variable first?
like:
DECLARE @.emails varchar(1000)
SET @.emails=''

The SQL I gave you accounts for it being null the first time round and puts an empty string instead:
DECLARE @.emails varchar(1000)
SELECT @.emails = isnull( @.emails,'' ) + ', ' + Email

if you can't get rid of it, then use the SUBSTRING function to get rid of the first character.

As for error on the DISTINCT i suggested, it should work, I wrote some sample stuff at my end.
I'll try my explanation in full.. try copying this: (note that you have to name the subquery and you have to alias the Email)

DECLARE @.emails varchar(1000)
SELECT @.emails = isnull( @.emails,'' ) + ', ' + PartyEmail
FROM
(SELECT DISTINCT dbo.lm_Responsible_Party.Email AS PartyEmail

FROM dbo.lm_Service_DetailINNERJOIN
 dbo.lm_Service_Filing_TypeON dbo.lm_Service_Detail.Service_id = dbo.lm_Service_Filing_Type.Service_idINNERJOIN
 dbo.lm_Responsible_Party_Filing_TypeON
 dbo.lm_Service_Filing_Type.Filing_Type_id = dbo.lm_Responsible_Party_Filing_Type.Filing_Type_idINNERJOIN
 dbo.lm_Responsible_PartyON dbo.lm_Responsible_Party_Filing_Type.Party_id = dbo.lm_Responsible_Party.Party_id
WHERE (dbo.lm_Service_Detail.Service_Detail_id = @.serviceDetailID)
 ) subquery

|||

Just seen your post after I did one!
Excellent news that you got it working.

The comma again is coming from the fact that you have probably assigned "recip:" before you do the select.
Add it on afterwards with

SET @.emails = 'recip: ' + @.emails

|||

Instead of

SELECT @.email=ISNULL(@.email,'') + ',' + {your field}

use

SELECT @.email=CASE WHEN @.email IS NULL THEN '' ELSE @.email+',' END + {your field}

|||

I figured it out, the comma had to be inside the ISNULL command (otherwise it was displaying it regardless of the value of @.emails).

Like this: SELECT @.emails = ISNULL(@.emails + ', ' ,'') + tmpEmail

And, yeah - you were right about the distinct. I didn't realize "subquery" was part of the actual query. I thought you were just referencing the remaining queries.

Either way, thanks a lot for the help - it's good to go now!

items in list A that dont appear in list B (was "Simple Query...I think")

Ok, I want to write a stored procedure / query that says the following:
Code:
If any of the items in list 'A' also appear in list 'B' --return false
If none of the items in list 'A' appear in list 'B' --return true

In pseudo-SQL, I want to write a clause like this

Code:

IF
(SELECT values FROM tableA) IN(SELECT values FROM tableB)
Return False
ELSE
Return True

Unfortunately, it seems I can't do that unless my subquery before the 'IN' statement returns only one value. Needless to say, it returns a number of values.

I may have to achieve this with some kind of logical loop but I don't know how to do that.

Can anyone help?OK so it wasnt' so simple...at least MY solution isn't:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER PROC sp_get_trolley_inconsistencies
@.grower CHAR(2),
@.load_id INT
AS

DECLARE @.ID int,
@.TrolleyList varchar(300),
@.Date DATETIME
--Get the relevant date from the DB Table
SELECT @.Date = (SELECT CONVERT(DATETIME, rl_eta, 102) FROM requi_load WHERE rl_id = @.load_id)

SET @.TrolleyList = ''
--Get the list of values into a comma delimited string
DECLARE crs_Trolleys CURSOR
FOR SELECT DISTINCT ldl_trolley
FROM load_detail_lines
WHERE ldl_requi_load_id = @.load_id

OPEN crs_Trolleys
FETCH NEXT FROM crs_Trolleys INTO @.ID

WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT @.TrolleyList = @.TrolleyList+CAST(@.ID AS varchar(5))+ ', '
FETCH NEXT FROM crs_Trolleys INTO @.ID
END

SET @.TrolleyList = SUBSTRING(@.TrolleyList,1,DATALENGTH(@.TrolleyList)-2)

CLOSE crs_Trolleys
DEALLOCATE crs_Trolleys

--Parse the string and run the 'IN' statement on each of the Parsed Values

DECLARE @.parsingList VARCHAR(300)
DECLARE @.find_comma INT
DECLARE @.trolleytocheck VARCHAR(4)

SELECT @.parsingList = @.TrolleyList

WHILE @.parsingList IS NOT NULL

BEGIN
SELECT @.find_comma = PATINDEX('%,%',@.parsingList)
IF @.find_comma <> 0
BEGIN
SELECT @.trolleytocheck = SUBSTRING(@.parsingList,1,(@.find_comma-1))
SELECT @.parsingList = LTRIM(SUBSTRING(@.parsingList,(@.find_comma+1),300))
PRINT @.trolleytocheck
IF @.trolleytocheck IN(SELECT distinct ldl_trolley
FROM load_detail_lines, requi_load
WHERE ldl_requi_load_id = rl_id
AND ldl_requi_load_id NOT LIKE @.load_id
AND rl_status = 1
AND DAY(rl_eta) = DAY(@.Date)
AND MONTH(rl_eta) = MONTH(@.Date)
AND YEAR(rl_eta) = YEAR(@.Date))
BEGIN
RAISERROR 50001 'Error! You screwed up, trolley '
END
CONTINUE
END
ELSE
BEGIN
--this block finds the last value in the trolley list
SELECT @.trolleytocheck = @.parsingList
SELECT @.parsingList = null
PRINT @.trolleytocheck
BREAK
END
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

If anyone can suggest anything more elegant...please let me know.|||Why not join the tables and count the rows that match?|||Doh!

*Commits ritual suicide*

Friday, March 23, 2012

It doesnt execute my assemblies.

Hello I just did 2 stored procedures, they dont have errors and they deployed succesfully but after I tried to exec them it doesnt happen anything.
I waited for 10 minutes and nothing

using System;

using System.Data;

using System.Data.Sql;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

public partial class StoredProcedures

{

[Microsoft.SqlServer.Server.SqlProcedure]

public static void getSalesOrdersHeaders()

{

// Put your code here

SqlPipe sp = SqlContext.Pipe;

using (SqlConnection conn = new SqlConnection(@."Data Source=ESTACION15\SQL2005; User Id=pruebas; Password=pruebas;database=AdventureWorks;"))

{

conn.Open();

SqlCommand cmd = new SqlCommand();

cmd.CommandType = CommandType.Text;

cmd.Connection = conn;

cmd.CommandText = "select * from sales.SalesOrderHeader";

SqlDataReader rdr = cmd.ExecuteReader();

sp.Send(rdr);

}

};

I tried to run it from visual studio .net and I get A TimeOut error

Error 1 Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. Pubs|||

How did you run it from Visual Studio?
From server explorer, test scripts or database project script

Thanks,
-Vineet.

|||Hello. It suddently began working after restarting my machine. Strange behavior.|||same here. It works after restarting my machine. Is this really normal? Any hints on this one?|||dunno if this is relevant or what you wrote was intended, but by creating the connection like you do you're creating a new connection from the connection your sproc's already using.

SqlConnection conn = new SqlConnection("context connection = true");

will get a reference to the existing one.
more info @. http://codebetter.com/blogs/sahil.malik/archive/2005/07/26/129824.aspx

It doesnt execute my assemblies.

Hello I just did 2 stored procedures, they dont have errors and they deployed succesfully but after I tried to exec them it doesnt happen anything.
I waited for 10 minutes and nothing

using System;

using System.Data;

using System.Data.Sql;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

public partial class StoredProcedures

{

[Microsoft.SqlServer.Server.SqlProcedure]

public static void getSalesOrdersHeaders()

{

// Put your code here

SqlPipe sp = SqlContext.Pipe;

using (SqlConnection conn = new SqlConnection(@."Data Source=ESTACION15\SQL2005; User Id=pruebas; Password=pruebas;database=AdventureWorks;"))

{

conn.Open();

SqlCommand cmd = new SqlCommand();

cmd.CommandType = CommandType.Text;

cmd.Connection = conn;

cmd.CommandText = "select * from sales.SalesOrderHeader";

SqlDataReader rdr = cmd.ExecuteReader();

sp.Send(rdr);

}

};

I tried to run it from visual studio .net and I get A TimeOut error

Error 1 Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. Pubs|||

How did you run it from Visual Studio?
From server explorer, test scripts or database project script

Thanks,
-Vineet.

SqlConnection conn = new SqlConnection("context connection = true");

will get a reference to the existing one.
more info @. http://codebetter.com/blogs/sahil.malik/archive/2005/07/26/129824.aspx

Wednesday, March 21, 2012

Issues with temp tables in stored procedures using OLEDB connectio

I am having an issue when converting some of my reports to SQL reporting
services 2005. All of my reports use an OLEDB connection that point to a UDL
file so that we can easily change connection strings as needed without having
to touch the report server. They all use stored procedures to obtain the
data. The issues I am running into is with stored procedures that use temp
tables. Anything that is done in a stored procedure after the temp table is
created is not shown in the report designer on the data tab within visual
studio 2005. Iâ've read a few posts about this problem but it seems like no
one at Microsoft has been able to reproduce it. I have been able to
reproduce it with a simple example using the NorthWind database. I will
include the stored procedure and the rdl file below. I set the report to
prompt for credentials instead of using the UDL file but the same problem
occurs. It will occur with Windows authentication as well. If I change the
connection type to â'Microsoft SQL Serverâ' then it will work correctly the
problem is that I will not be able to use UDL or DSN files. My method worked
just fine with RS 2000. What has changed with RS 2005 to cause this issue?
Any ideas on how I might get this to work?
Thanks,
-Nathan
SP Script-
use northwind
set ANSI_NULLS ON
set QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[Pr_TestingRSProblem]
AS
Select FirstName
into #EmployeeTemp
FROM Employees
--*NOTE It doesnâ't matter what you do here it will not be returned in the
Report Designer Data tab.
SELECT FirstName
FROM #EmployeeTemp
--Select 'Test'
RDL File-
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition"
xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="DataSource1">
<ConnectionProperties>
<Prompt>Specify a user name and password for data source
DataSource1</Prompt>
<ConnectString>Provider=SQLOLEDB.1;Data Source=saintdevsql1;Initial
Catalog=Northwind</ConnectString>
<DataProvider>OLEDB</DataProvider>
</ConnectionProperties>
<rd:DataSourceID>d66c0234-6fba-48b8-8c6a-6e6e72f006ca</rd:DataSourceID>
</DataSource>
</DataSources>
<BottomMargin>1in</BottomMargin>
<RightMargin>1in</RightMargin>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>8.5in</InteractiveWidth>
<rd:SnapToGrid>true</rd:SnapToGrid>
<Body>
<Height>0.75in</Height>
</Body>
<rd:ReportID>0eee2348-f25d-4594-b4f1-3bcec4cd58b1</rd:ReportID>
<LeftMargin>1in</LeftMargin>
<DataSets>
<DataSet Name="Northwind">
<Query>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
<CommandType>StoredProcedure</CommandType>
<CommandText>Pr_TestingRSProblem</CommandText>
<DataSourceName>DataSource1</DataSourceName>
</Query>
</DataSet>
</DataSets>
<Width>6.5in</Width>
<InteractiveHeight>11in</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>1in</TopMargin>
</Report>I meant to mention that I tried the sp with and without explicitly dropping
the table at the end. Neither way worked.
"Nathan" wrote:
> I am having an issue when converting some of my reports to SQL reporting
> services 2005. All of my reports use an OLEDB connection that point to a UDL
> file so that we can easily change connection strings as needed without having
> to touch the report server. They all use stored procedures to obtain the
> data. The issues I am running into is with stored procedures that use temp
> tables. Anything that is done in a stored procedure after the temp table is
> created is not shown in the report designer on the data tab within visual
> studio 2005. Iâ've read a few posts about this problem but it seems like no
> one at Microsoft has been able to reproduce it. I have been able to
> reproduce it with a simple example using the NorthWind database. I will
> include the stored procedure and the rdl file below. I set the report to
> prompt for credentials instead of using the UDL file but the same problem
> occurs. It will occur with Windows authentication as well. If I change the
> connection type to â'Microsoft SQL Serverâ' then it will work correctly the
> problem is that I will not be able to use UDL or DSN files. My method worked
> just fine with RS 2000. What has changed with RS 2005 to cause this issue?
> Any ideas on how I might get this to work?
> Thanks,
> -Nathan
> SP Script-
> use northwind
> set ANSI_NULLS ON
> set QUOTED_IDENTIFIER OFF
> GO
> CREATE PROCEDURE [dbo].[Pr_TestingRSProblem]
> AS
> Select FirstName
> into #EmployeeTemp
> FROM Employees
> --*NOTE It doesnâ't matter what you do here it will not be returned in the
> Report Designer Data tab.
> SELECT FirstName
> FROM #EmployeeTemp
> --Select 'Test'
>
> RDL File-
> <?xml version="1.0" encoding="utf-8"?>
> <Report
> xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition"
> xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
> <DataSources>
> <DataSource Name="DataSource1">
> <ConnectionProperties>
> <Prompt>Specify a user name and password for data source
> DataSource1</Prompt>
> <ConnectString>Provider=SQLOLEDB.1;Data Source=saintdevsql1;Initial
> Catalog=Northwind</ConnectString>
> <DataProvider>OLEDB</DataProvider>
> </ConnectionProperties>
> <rd:DataSourceID>d66c0234-6fba-48b8-8c6a-6e6e72f006ca</rd:DataSourceID>
> </DataSource>
> </DataSources>
> <BottomMargin>1in</BottomMargin>
> <RightMargin>1in</RightMargin>
> <rd:DrawGrid>true</rd:DrawGrid>
> <InteractiveWidth>8.5in</InteractiveWidth>
> <rd:SnapToGrid>true</rd:SnapToGrid>
> <Body>
> <Height>0.75in</Height>
> </Body>
> <rd:ReportID>0eee2348-f25d-4594-b4f1-3bcec4cd58b1</rd:ReportID>
> <LeftMargin>1in</LeftMargin>
> <DataSets>
> <DataSet Name="Northwind">
> <Query>
> <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
> <CommandType>StoredProcedure</CommandType>
> <CommandText>Pr_TestingRSProblem</CommandText>
> <DataSourceName>DataSource1</DataSourceName>
> </Query>
> </DataSet>
> </DataSets>
> <Width>6.5in</Width>
> <InteractiveHeight>11in</InteractiveHeight>
> <Language>en-US</Language>
> <TopMargin>1in</TopMargin>
> </Report>
>|||First, do not explicitly drop the table.
Second, exactly what is happening? No data? No field list? Any error?
I just had a case where I would get an error about a temp table (I use temp
tables all the time, why this particular SP had a problem I don't know).
What worked for me was to refresh the fields (button is to the right of the
...). I then had a field list. After that I could execute the query in the
data tab and I got data back.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Nathan" <Nathan@.discussions.microsoft.com> wrote in message
news:07802D42-8A7D-4A56-B06D-8ED97EFB75F3@.microsoft.com...
> I meant to mention that I tried the sp with and without explicitly
> dropping
> the table at the end. Neither way worked.
> "Nathan" wrote:
>> I am having an issue when converting some of my reports to SQL reporting
>> services 2005. All of my reports use an OLEDB connection that point to a
>> UDL
>> file so that we can easily change connection strings as needed without
>> having
>> to touch the report server. They all use stored procedures to obtain the
>> data. The issues I am running into is with stored procedures that use
>> temp
>> tables. Anything that is done in a stored procedure after the temp table
>> is
>> created is not shown in the report designer on the data tab within visual
>> studio 2005. I've read a few posts about this problem but it seems like
>> no
>> one at Microsoft has been able to reproduce it. I have been able to
>> reproduce it with a simple example using the NorthWind database. I will
>> include the stored procedure and the rdl file below. I set the report to
>> prompt for credentials instead of using the UDL file but the same problem
>> occurs. It will occur with Windows authentication as well. If I change
>> the
>> connection type to "Microsoft SQL Server" then it will work correctly the
>> problem is that I will not be able to use UDL or DSN files. My method
>> worked
>> just fine with RS 2000. What has changed with RS 2005 to cause this
>> issue?
>> Any ideas on how I might get this to work?
>> Thanks,
>> -Nathan
>> SP Script-
>> use northwind
>> set ANSI_NULLS ON
>> set QUOTED_IDENTIFIER OFF
>> GO
>> CREATE PROCEDURE [dbo].[Pr_TestingRSProblem]
>> AS
>> Select FirstName
>> into #EmployeeTemp
>> FROM Employees
>> --*NOTE It doesn't matter what you do here it will not be returned in the
>> Report Designer Data tab.
>> SELECT FirstName
>> FROM #EmployeeTemp
>> --Select 'Test'
>>
>> RDL File-
>> <?xml version="1.0" encoding="utf-8"?>
>> <Report
>> xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition"
>> xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
>> <DataSources>
>> <DataSource Name="DataSource1">
>> <ConnectionProperties>
>> <Prompt>Specify a user name and password for data source
>> DataSource1</Prompt>
>> <ConnectString>Provider=SQLOLEDB.1;Data
>> Source=saintdevsql1;Initial
>> Catalog=Northwind</ConnectString>
>> <DataProvider>OLEDB</DataProvider>
>> </ConnectionProperties>
>> <rd:DataSourceID>d66c0234-6fba-48b8-8c6a-6e6e72f006ca</rd:DataSourceID>
>> </DataSource>
>> </DataSources>
>> <BottomMargin>1in</BottomMargin>
>> <RightMargin>1in</RightMargin>
>> <rd:DrawGrid>true</rd:DrawGrid>
>> <InteractiveWidth>8.5in</InteractiveWidth>
>> <rd:SnapToGrid>true</rd:SnapToGrid>
>> <Body>
>> <Height>0.75in</Height>
>> </Body>
>> <rd:ReportID>0eee2348-f25d-4594-b4f1-3bcec4cd58b1</rd:ReportID>
>> <LeftMargin>1in</LeftMargin>
>> <DataSets>
>> <DataSet Name="Northwind">
>> <Query>
>> <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
>> <CommandType>StoredProcedure</CommandType>
>> <CommandText>Pr_TestingRSProblem</CommandText>
>> <DataSourceName>DataSource1</DataSourceName>
>> </Query>
>> </DataSet>
>> </DataSets>
>> <Width>6.5in</Width>
>> <InteractiveHeight>11in</InteractiveHeight>
>> <Language>en-US</Language>
>> <TopMargin>1in</TopMargin>
>> </Report>|||Bruce,
The simple answer is there is no data, no field list, and no error. I
should have mentioned that I have tried refreshing the field list several
times and no fields are returned. I've tried this on two separate machines.
The field list is empty and when I try to run the sp within the data tab it
returns no results. Were you not able to reproduce the problem with an OLEDB
connection type? What I found strange was that anything in the sp before the
temp table declaration worked fine. It's as if the meta data describing the
table structure isn't returned. Any ideas? Let me know if I can provide you
with any more information. Thanks for the quick reply.
-Nathan
"Bruce L-C [MVP]" wrote:
> First, do not explicitly drop the table.
> Second, exactly what is happening? No data? No field list? Any error?
> I just had a case where I would get an error about a temp table (I use temp
> tables all the time, why this particular SP had a problem I don't know).
> What worked for me was to refresh the fields (button is to the right of the
> ...). I then had a field list. After that I could execute the query in the
> data tab and I got data back.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Nathan" <Nathan@.discussions.microsoft.com> wrote in message
> news:07802D42-8A7D-4A56-B06D-8ED97EFB75F3@.microsoft.com...
> >
> > I meant to mention that I tried the sp with and without explicitly
> > dropping
> > the table at the end. Neither way worked.
> >
> > "Nathan" wrote:
> >
> >> I am having an issue when converting some of my reports to SQL reporting
> >> services 2005. All of my reports use an OLEDB connection that point to a
> >> UDL
> >> file so that we can easily change connection strings as needed without
> >> having
> >> to touch the report server. They all use stored procedures to obtain the
> >> data. The issues I am running into is with stored procedures that use
> >> temp
> >> tables. Anything that is done in a stored procedure after the temp table
> >> is
> >> created is not shown in the report designer on the data tab within visual
> >> studio 2005. I've read a few posts about this problem but it seems like
> >> no
> >> one at Microsoft has been able to reproduce it. I have been able to
> >> reproduce it with a simple example using the NorthWind database. I will
> >> include the stored procedure and the rdl file below. I set the report to
> >> prompt for credentials instead of using the UDL file but the same problem
> >> occurs. It will occur with Windows authentication as well. If I change
> >> the
> >> connection type to "Microsoft SQL Server" then it will work correctly the
> >> problem is that I will not be able to use UDL or DSN files. My method
> >> worked
> >> just fine with RS 2000. What has changed with RS 2005 to cause this
> >> issue?
> >> Any ideas on how I might get this to work?
> >>
> >> Thanks,
> >> -Nathan
> >>
> >> SP Script-
> >>
> >> use northwind
> >>
> >> set ANSI_NULLS ON
> >> set QUOTED_IDENTIFIER OFF
> >> GO
> >>
> >> CREATE PROCEDURE [dbo].[Pr_TestingRSProblem]
> >> AS
> >>
> >> Select FirstName
> >> into #EmployeeTemp
> >> FROM Employees
> >> --*NOTE It doesn't matter what you do here it will not be returned in the
> >> Report Designer Data tab.
> >> SELECT FirstName
> >> FROM #EmployeeTemp
> >> --Select 'Test'
> >>
> >>
> >> RDL File-
> >>
> >> <?xml version="1.0" encoding="utf-8"?>
> >> <Report
> >> xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition"
> >> xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
> >> <DataSources>
> >> <DataSource Name="DataSource1">
> >> <ConnectionProperties>
> >> <Prompt>Specify a user name and password for data source
> >> DataSource1</Prompt>
> >> <ConnectString>Provider=SQLOLEDB.1;Data
> >> Source=saintdevsql1;Initial
> >> Catalog=Northwind</ConnectString>
> >> <DataProvider>OLEDB</DataProvider>
> >> </ConnectionProperties>
> >>
> >> <rd:DataSourceID>d66c0234-6fba-48b8-8c6a-6e6e72f006ca</rd:DataSourceID>
> >> </DataSource>
> >> </DataSources>
> >> <BottomMargin>1in</BottomMargin>
> >> <RightMargin>1in</RightMargin>
> >> <rd:DrawGrid>true</rd:DrawGrid>
> >> <InteractiveWidth>8.5in</InteractiveWidth>
> >> <rd:SnapToGrid>true</rd:SnapToGrid>
> >> <Body>
> >> <Height>0.75in</Height>
> >> </Body>
> >> <rd:ReportID>0eee2348-f25d-4594-b4f1-3bcec4cd58b1</rd:ReportID>
> >> <LeftMargin>1in</LeftMargin>
> >> <DataSets>
> >> <DataSet Name="Northwind">
> >> <Query>
> >> <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
> >> <CommandType>StoredProcedure</CommandType>
> >> <CommandText>Pr_TestingRSProblem</CommandText>
> >> <DataSourceName>DataSource1</DataSourceName>
> >> </Query>
> >> </DataSet>
> >> </DataSets>
> >> <Width>6.5in</Width>
> >> <InteractiveHeight>11in</InteractiveHeight>
> >> <Language>en-US</Language>
> >> <TopMargin>1in</TopMargin>
> >> </Report>
> >>
>
>|||I don't have northwind, I have AdventureWorks. Here is my stored procedure:
create PROCEDURE [dbo].[Pr_TestingRSProblem]
AS
Select FirstName
into #TEMP
FROM person.contact
SELECT distinct FirstName
FROM #TEMP order by firstname
return
I did not use the wizard. I added a report. I went to the data tab. Added a
new dataset. My shared data source credentials were windows authentication.
When creating the dataset I changed the data type to stored procedure and
put in the name of the stored procedure as the query string (do not put in
exec, just put in the name of the procedure). I get an error and no field
list. I click on refresh the field list I get a field list and now I can
execute the query and get data back in the data tab.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Nathan" <Nathan@.discussions.microsoft.com> wrote in message
news:07802D42-8A7D-4A56-B06D-8ED97EFB75F3@.microsoft.com...
> I meant to mention that I tried the sp with and without explicitly
> dropping
> the table at the end. Neither way worked.
> "Nathan" wrote:
>> I am having an issue when converting some of my reports to SQL reporting
>> services 2005. All of my reports use an OLEDB connection that point to a
>> UDL
>> file so that we can easily change connection strings as needed without
>> having
>> to touch the report server. They all use stored procedures to obtain the
>> data. The issues I am running into is with stored procedures that use
>> temp
>> tables. Anything that is done in a stored procedure after the temp table
>> is
>> created is not shown in the report designer on the data tab within visual
>> studio 2005. I've read a few posts about this problem but it seems like
>> no
>> one at Microsoft has been able to reproduce it. I have been able to
>> reproduce it with a simple example using the NorthWind database. I will
>> include the stored procedure and the rdl file below. I set the report to
>> prompt for credentials instead of using the UDL file but the same problem
>> occurs. It will occur with Windows authentication as well. If I change
>> the
>> connection type to "Microsoft SQL Server" then it will work correctly the
>> problem is that I will not be able to use UDL or DSN files. My method
>> worked
>> just fine with RS 2000. What has changed with RS 2005 to cause this
>> issue?
>> Any ideas on how I might get this to work?
>> Thanks,
>> -Nathan
>> SP Script-
>> use northwind
>> set ANSI_NULLS ON
>> set QUOTED_IDENTIFIER OFF
>> GO
>> CREATE PROCEDURE [dbo].[Pr_TestingRSProblem]
>> AS
>> Select FirstName
>> into #EmployeeTemp
>> FROM Employees
>> --*NOTE It doesn't matter what you do here it will not be returned in the
>> Report Designer Data tab.
>> SELECT FirstName
>> FROM #EmployeeTemp
>> --Select 'Test'
>>
>> RDL File-
>> <?xml version="1.0" encoding="utf-8"?>
>> <Report
>> xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition"
>> xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
>> <DataSources>
>> <DataSource Name="DataSource1">
>> <ConnectionProperties>
>> <Prompt>Specify a user name and password for data source
>> DataSource1</Prompt>
>> <ConnectString>Provider=SQLOLEDB.1;Data
>> Source=saintdevsql1;Initial
>> Catalog=Northwind</ConnectString>
>> <DataProvider>OLEDB</DataProvider>
>> </ConnectionProperties>
>> <rd:DataSourceID>d66c0234-6fba-48b8-8c6a-6e6e72f006ca</rd:DataSourceID>
>> </DataSource>
>> </DataSources>
>> <BottomMargin>1in</BottomMargin>
>> <RightMargin>1in</RightMargin>
>> <rd:DrawGrid>true</rd:DrawGrid>
>> <InteractiveWidth>8.5in</InteractiveWidth>
>> <rd:SnapToGrid>true</rd:SnapToGrid>
>> <Body>
>> <Height>0.75in</Height>
>> </Body>
>> <rd:ReportID>0eee2348-f25d-4594-b4f1-3bcec4cd58b1</rd:ReportID>
>> <LeftMargin>1in</LeftMargin>
>> <DataSets>
>> <DataSet Name="Northwind">
>> <Query>
>> <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
>> <CommandType>StoredProcedure</CommandType>
>> <CommandText>Pr_TestingRSProblem</CommandText>
>> <DataSourceName>DataSource1</DataSourceName>
>> </Query>
>> </DataSet>
>> </DataSets>
>> <Width>6.5in</Width>
>> <InteractiveHeight>11in</InteractiveHeight>
>> <Language>en-US</Language>
>> <TopMargin>1in</TopMargin>
>> </Report>|||Whoops, didn't use OLEDB. Yep, I can duplicate. My only suggestion is to not
use OLEDB. I tried ODBC (which is what I use with Sybase) but also had a
proble with that. Against SQL Server I don't use OLEDB or ODBC, just ODBC
against Sybase (for which I don't have this problem). Weird. Sorry, other
than duplicating the problem there isn't much more I can do to help.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Nathan" <Nathan@.discussions.microsoft.com> wrote in message
news:CCF681B4-BB0F-4027-8672-7CDFFE14ECE1@.microsoft.com...
> Bruce,
> The simple answer is there is no data, no field list, and no error. I
> should have mentioned that I have tried refreshing the field list several
> times and no fields are returned. I've tried this on two separate
> machines.
> The field list is empty and when I try to run the sp within the data tab
> it
> returns no results. Were you not able to reproduce the problem with an
> OLEDB
> connection type? What I found strange was that anything in the sp before
> the
> temp table declaration worked fine. It's as if the meta data describing
> the
> table structure isn't returned. Any ideas? Let me know if I can provide
> you
> with any more information. Thanks for the quick reply.
> -Nathan
>
> "Bruce L-C [MVP]" wrote:
>> First, do not explicitly drop the table.
>> Second, exactly what is happening? No data? No field list? Any error?
>> I just had a case where I would get an error about a temp table (I use
>> temp
>> tables all the time, why this particular SP had a problem I don't know).
>> What worked for me was to refresh the fields (button is to the right of
>> the
>> ...). I then had a field list. After that I could execute the query in
>> the
>> data tab and I got data back.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Nathan" <Nathan@.discussions.microsoft.com> wrote in message
>> news:07802D42-8A7D-4A56-B06D-8ED97EFB75F3@.microsoft.com...
>> >
>> > I meant to mention that I tried the sp with and without explicitly
>> > dropping
>> > the table at the end. Neither way worked.
>> >
>> > "Nathan" wrote:
>> >
>> >> I am having an issue when converting some of my reports to SQL
>> >> reporting
>> >> services 2005. All of my reports use an OLEDB connection that point
>> >> to a
>> >> UDL
>> >> file so that we can easily change connection strings as needed without
>> >> having
>> >> to touch the report server. They all use stored procedures to obtain
>> >> the
>> >> data. The issues I am running into is with stored procedures that use
>> >> temp
>> >> tables. Anything that is done in a stored procedure after the temp
>> >> table
>> >> is
>> >> created is not shown in the report designer on the data tab within
>> >> visual
>> >> studio 2005. I've read a few posts about this problem but it seems
>> >> like
>> >> no
>> >> one at Microsoft has been able to reproduce it. I have been able to
>> >> reproduce it with a simple example using the NorthWind database. I
>> >> will
>> >> include the stored procedure and the rdl file below. I set the report
>> >> to
>> >> prompt for credentials instead of using the UDL file but the same
>> >> problem
>> >> occurs. It will occur with Windows authentication as well. If I
>> >> change
>> >> the
>> >> connection type to "Microsoft SQL Server" then it will work correctly
>> >> the
>> >> problem is that I will not be able to use UDL or DSN files. My method
>> >> worked
>> >> just fine with RS 2000. What has changed with RS 2005 to cause this
>> >> issue?
>> >> Any ideas on how I might get this to work?
>> >>
>> >> Thanks,
>> >> -Nathan
>> >>
>> >> SP Script-
>> >>
>> >> use northwind
>> >>
>> >> set ANSI_NULLS ON
>> >> set QUOTED_IDENTIFIER OFF
>> >> GO
>> >>
>> >> CREATE PROCEDURE [dbo].[Pr_TestingRSProblem]
>> >> AS
>> >>
>> >> Select FirstName
>> >> into #EmployeeTemp
>> >> FROM Employees
>> >> --*NOTE It doesn't matter what you do here it will not be returned in
>> >> the
>> >> Report Designer Data tab.
>> >> SELECT FirstName
>> >> FROM #EmployeeTemp
>> >> --Select 'Test'
>> >>
>> >>
>> >> RDL File-
>> >>
>> >> <?xml version="1.0" encoding="utf-8"?>
>> >> <Report
>> >> xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition"
>> >> xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
>> >> <DataSources>
>> >> <DataSource Name="DataSource1">
>> >> <ConnectionProperties>
>> >> <Prompt>Specify a user name and password for data source
>> >> DataSource1</Prompt>
>> >> <ConnectString>Provider=SQLOLEDB.1;Data
>> >> Source=saintdevsql1;Initial
>> >> Catalog=Northwind</ConnectString>
>> >> <DataProvider>OLEDB</DataProvider>
>> >> </ConnectionProperties>
>> >>
>> >> <rd:DataSourceID>d66c0234-6fba-48b8-8c6a-6e6e72f006ca</rd:DataSourceID>
>> >> </DataSource>
>> >> </DataSources>
>> >> <BottomMargin>1in</BottomMargin>
>> >> <RightMargin>1in</RightMargin>
>> >> <rd:DrawGrid>true</rd:DrawGrid>
>> >> <InteractiveWidth>8.5in</InteractiveWidth>
>> >> <rd:SnapToGrid>true</rd:SnapToGrid>
>> >> <Body>
>> >> <Height>0.75in</Height>
>> >> </Body>
>> >> <rd:ReportID>0eee2348-f25d-4594-b4f1-3bcec4cd58b1</rd:ReportID>
>> >> <LeftMargin>1in</LeftMargin>
>> >> <DataSets>
>> >> <DataSet Name="Northwind">
>> >> <Query>
>> >> <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
>> >> <CommandType>StoredProcedure</CommandType>
>> >> <CommandText>Pr_TestingRSProblem</CommandText>
>> >> <DataSourceName>DataSource1</DataSourceName>
>> >> </Query>
>> >> </DataSet>
>> >> </DataSets>
>> >> <Width>6.5in</Width>
>> >> <InteractiveHeight>11in</InteractiveHeight>
>> >> <Language>en-US</Language>
>> >> <TopMargin>1in</TopMargin>
>> >> </Report>
>> >>
>>|||I tried the example you have given with the adventure works database. I am
able to reproduce what you have with the connection type of â'Microsoft SQL
Serverâ'. However, if you select â'OLEDBâ' you will encounter the problem I am
having. I need to use this because of my requirements of using a dsn or udl
file.
Thanks again,
-Nathan
"Bruce L-C [MVP]" wrote:
> I don't have northwind, I have AdventureWorks. Here is my stored procedure:
> create PROCEDURE [dbo].[Pr_TestingRSProblem]
> AS
> Select FirstName
> into #TEMP
> FROM person.contact
> SELECT distinct FirstName
> FROM #TEMP order by firstname
> return
> I did not use the wizard. I added a report. I went to the data tab. Added a
> new dataset. My shared data source credentials were windows authentication.
> When creating the dataset I changed the data type to stored procedure and
> put in the name of the stored procedure as the query string (do not put in
> exec, just put in the name of the procedure). I get an error and no field
> list. I click on refresh the field list I get a field list and now I can
> execute the query and get data back in the data tab.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Nathan" <Nathan@.discussions.microsoft.com> wrote in message
> news:07802D42-8A7D-4A56-B06D-8ED97EFB75F3@.microsoft.com...
> >
> > I meant to mention that I tried the sp with and without explicitly
> > dropping
> > the table at the end. Neither way worked.
> >
> > "Nathan" wrote:
> >
> >> I am having an issue when converting some of my reports to SQL reporting
> >> services 2005. All of my reports use an OLEDB connection that point to a
> >> UDL
> >> file so that we can easily change connection strings as needed without
> >> having
> >> to touch the report server. They all use stored procedures to obtain the
> >> data. The issues I am running into is with stored procedures that use
> >> temp
> >> tables. Anything that is done in a stored procedure after the temp table
> >> is
> >> created is not shown in the report designer on the data tab within visual
> >> studio 2005. I've read a few posts about this problem but it seems like
> >> no
> >> one at Microsoft has been able to reproduce it. I have been able to
> >> reproduce it with a simple example using the NorthWind database. I will
> >> include the stored procedure and the rdl file below. I set the report to
> >> prompt for credentials instead of using the UDL file but the same problem
> >> occurs. It will occur with Windows authentication as well. If I change
> >> the
> >> connection type to "Microsoft SQL Server" then it will work correctly the
> >> problem is that I will not be able to use UDL or DSN files. My method
> >> worked
> >> just fine with RS 2000. What has changed with RS 2005 to cause this
> >> issue?
> >> Any ideas on how I might get this to work?
> >>
> >> Thanks,
> >> -Nathan
> >>
> >> SP Script-
> >>
> >> use northwind
> >>
> >> set ANSI_NULLS ON
> >> set QUOTED_IDENTIFIER OFF
> >> GO
> >>
> >> CREATE PROCEDURE [dbo].[Pr_TestingRSProblem]
> >> AS
> >>
> >> Select FirstName
> >> into #EmployeeTemp
> >> FROM Employees
> >> --*NOTE It doesn't matter what you do here it will not be returned in the
> >> Report Designer Data tab.
> >> SELECT FirstName
> >> FROM #EmployeeTemp
> >> --Select 'Test'
> >>
> >>
> >> RDL File-
> >>
> >> <?xml version="1.0" encoding="utf-8"?>
> >> <Report
> >> xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition"
> >> xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
> >> <DataSources>
> >> <DataSource Name="DataSource1">
> >> <ConnectionProperties>
> >> <Prompt>Specify a user name and password for data source
> >> DataSource1</Prompt>
> >> <ConnectString>Provider=SQLOLEDB.1;Data
> >> Source=saintdevsql1;Initial
> >> Catalog=Northwind</ConnectString>
> >> <DataProvider>OLEDB</DataProvider>
> >> </ConnectionProperties>
> >>
> >> <rd:DataSourceID>d66c0234-6fba-48b8-8c6a-6e6e72f006ca</rd:DataSourceID>
> >> </DataSource>
> >> </DataSources>
> >> <BottomMargin>1in</BottomMargin>
> >> <RightMargin>1in</RightMargin>
> >> <rd:DrawGrid>true</rd:DrawGrid>
> >> <InteractiveWidth>8.5in</InteractiveWidth>
> >> <rd:SnapToGrid>true</rd:SnapToGrid>
> >> <Body>
> >> <Height>0.75in</Height>
> >> </Body>
> >> <rd:ReportID>0eee2348-f25d-4594-b4f1-3bcec4cd58b1</rd:ReportID>
> >> <LeftMargin>1in</LeftMargin>
> >> <DataSets>
> >> <DataSet Name="Northwind">
> >> <Query>
> >> <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
> >> <CommandType>StoredProcedure</CommandType>
> >> <CommandText>Pr_TestingRSProblem</CommandText>
> >> <DataSourceName>DataSource1</DataSourceName>
> >> </Query>
> >> </DataSet>
> >> </DataSets>
> >> <Width>6.5in</Width>
> >> <InteractiveHeight>11in</InteractiveHeight>
> >> <Language>en-US</Language>
> >> <TopMargin>1in</TopMargin>
> >> </Report>
> >>
>
>|||I must not have refreshed before sending my last reply. Looks like you did
try the OLEDB. Well, thanks for trying. At least I know I'm not going crazy
and it is reproducible and may actually be an issue with reporting services.
The funny thing is that if you try making the same report in the previous
version of RS within VS2003 it works fine. That is with it connecting to the
same DB server (SQL 2005). This seems like an unintended â'featureâ' to meâ?¦
Anyone else out there have any ideas?
Thanks,
-Nathan
"Nathan" wrote:
> I tried the example you have given with the adventure works database. I am
> able to reproduce what you have with the connection type of â'Microsoft SQL
> Serverâ'. However, if you select â'OLEDBâ' you will encounter the problem I am
> having. I need to use this because of my requirements of using a dsn or udl
> file.
> Thanks again,
> -Nathan
>
> "Bruce L-C [MVP]" wrote:
> > I don't have northwind, I have AdventureWorks. Here is my stored procedure:
> > create PROCEDURE [dbo].[Pr_TestingRSProblem]
> >
> > AS
> >
> > Select FirstName
> >
> > into #TEMP
> >
> > FROM person.contact
> >
> > SELECT distinct FirstName
> >
> > FROM #TEMP order by firstname
> >
> > return
> >
> > I did not use the wizard. I added a report. I went to the data tab. Added a
> > new dataset. My shared data source credentials were windows authentication.
> > When creating the dataset I changed the data type to stored procedure and
> > put in the name of the stored procedure as the query string (do not put in
> > exec, just put in the name of the procedure). I get an error and no field
> > list. I click on refresh the field list I get a field list and now I can
> > execute the query and get data back in the data tab.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> >
> > "Nathan" <Nathan@.discussions.microsoft.com> wrote in message
> > news:07802D42-8A7D-4A56-B06D-8ED97EFB75F3@.microsoft.com...
> > >
> > > I meant to mention that I tried the sp with and without explicitly
> > > dropping
> > > the table at the end. Neither way worked.
> > >
> > > "Nathan" wrote:
> > >
> > >> I am having an issue when converting some of my reports to SQL reporting
> > >> services 2005. All of my reports use an OLEDB connection that point to a
> > >> UDL
> > >> file so that we can easily change connection strings as needed without
> > >> having
> > >> to touch the report server. They all use stored procedures to obtain the
> > >> data. The issues I am running into is with stored procedures that use
> > >> temp
> > >> tables. Anything that is done in a stored procedure after the temp table
> > >> is
> > >> created is not shown in the report designer on the data tab within visual
> > >> studio 2005. I've read a few posts about this problem but it seems like
> > >> no
> > >> one at Microsoft has been able to reproduce it. I have been able to
> > >> reproduce it with a simple example using the NorthWind database. I will
> > >> include the stored procedure and the rdl file below. I set the report to
> > >> prompt for credentials instead of using the UDL file but the same problem
> > >> occurs. It will occur with Windows authentication as well. If I change
> > >> the
> > >> connection type to "Microsoft SQL Server" then it will work correctly the
> > >> problem is that I will not be able to use UDL or DSN files. My method
> > >> worked
> > >> just fine with RS 2000. What has changed with RS 2005 to cause this
> > >> issue?
> > >> Any ideas on how I might get this to work?
> > >>
> > >> Thanks,
> > >> -Nathan
> > >>
> > >> SP Script-
> > >>
> > >> use northwind
> > >>
> > >> set ANSI_NULLS ON
> > >> set QUOTED_IDENTIFIER OFF
> > >> GO
> > >>
> > >> CREATE PROCEDURE [dbo].[Pr_TestingRSProblem]
> > >> AS
> > >>
> > >> Select FirstName
> > >> into #EmployeeTemp
> > >> FROM Employees
> > >> --*NOTE It doesn't matter what you do here it will not be returned in the
> > >> Report Designer Data tab.
> > >> SELECT FirstName
> > >> FROM #EmployeeTemp
> > >> --Select 'Test'
> > >>
> > >>
> > >> RDL File-
> > >>
> > >> <?xml version="1.0" encoding="utf-8"?>
> > >> <Report
> > >> xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition"
> > >> xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
> > >> <DataSources>
> > >> <DataSource Name="DataSource1">
> > >> <ConnectionProperties>
> > >> <Prompt>Specify a user name and password for data source
> > >> DataSource1</Prompt>
> > >> <ConnectString>Provider=SQLOLEDB.1;Data
> > >> Source=saintdevsql1;Initial
> > >> Catalog=Northwind</ConnectString>
> > >> <DataProvider>OLEDB</DataProvider>
> > >> </ConnectionProperties>
> > >>
> > >> <rd:DataSourceID>d66c0234-6fba-48b8-8c6a-6e6e72f006ca</rd:DataSourceID>
> > >> </DataSource>
> > >> </DataSources>
> > >> <BottomMargin>1in</BottomMargin>
> > >> <RightMargin>1in</RightMargin>
> > >> <rd:DrawGrid>true</rd:DrawGrid>
> > >> <InteractiveWidth>8.5in</InteractiveWidth>
> > >> <rd:SnapToGrid>true</rd:SnapToGrid>
> > >> <Body>
> > >> <Height>0.75in</Height>
> > >> </Body>
> > >> <rd:ReportID>0eee2348-f25d-4594-b4f1-3bcec4cd58b1</rd:ReportID>
> > >> <LeftMargin>1in</LeftMargin>
> > >> <DataSets>
> > >> <DataSet Name="Northwind">
> > >> <Query>
> > >> <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
> > >> <CommandType>StoredProcedure</CommandType>
> > >> <CommandText>Pr_TestingRSProblem</CommandText>
> > >> <DataSourceName>DataSource1</DataSourceName>
> > >> </Query>
> > >> </DataSet>
> > >> </DataSets>
> > >> <Width>6.5in</Width>
> > >> <InteractiveHeight>11in</InteractiveHeight>
> > >> <Language>en-US</Language>
> > >> <TopMargin>1in</TopMargin>
> > >> </Report>
> > >>
> >
> >
> >|||Iâ'm thinking of starting a support ticket with Microsoft about this issue.
Before I do does anyone know of a reason why the behavior of the OLEDB
connection would change between rs2000 and rs2005? Does anyone know of any
hot fixes or SPs that might already fix this issue? Iâ'm at a loss at what to
do at this point. This is kind of a show stopper for us with moving to
rs2005.
Thanks,
-Nathan
"Nathan" wrote:
> I must not have refreshed before sending my last reply. Looks like you did
> try the OLEDB. Well, thanks for trying. At least I know I'm not going crazy
> and it is reproducible and may actually be an issue with reporting services.
> The funny thing is that if you try making the same report in the previous
> version of RS within VS2003 it works fine. That is with it connecting to the
> same DB server (SQL 2005). This seems like an unintended â'featureâ' to meâ?¦
> Anyone else out there have any ideas?
> Thanks,
> -Nathan
>
> "Nathan" wrote:
> > I tried the example you have given with the adventure works database. I am
> > able to reproduce what you have with the connection type of â'Microsoft SQL
> > Serverâ'. However, if you select â'OLEDBâ' you will encounter the problem I am
> > having. I need to use this because of my requirements of using a dsn or udl
> > file.
> >
> > Thanks again,
> > -Nathan
> >
> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > I don't have northwind, I have AdventureWorks. Here is my stored procedure:
> > > create PROCEDURE [dbo].[Pr_TestingRSProblem]
> > >
> > > AS
> > >
> > > Select FirstName
> > >
> > > into #TEMP
> > >
> > > FROM person.contact
> > >
> > > SELECT distinct FirstName
> > >
> > > FROM #TEMP order by firstname
> > >
> > > return
> > >
> > > I did not use the wizard. I added a report. I went to the data tab. Added a
> > > new dataset. My shared data source credentials were windows authentication.
> > > When creating the dataset I changed the data type to stored procedure and
> > > put in the name of the stored procedure as the query string (do not put in
> > > exec, just put in the name of the procedure). I get an error and no field
> > > list. I click on refresh the field list I get a field list and now I can
> > > execute the query and get data back in the data tab.
> > >
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > >
> > > "Nathan" <Nathan@.discussions.microsoft.com> wrote in message
> > > news:07802D42-8A7D-4A56-B06D-8ED97EFB75F3@.microsoft.com...
> > > >
> > > > I meant to mention that I tried the sp with and without explicitly
> > > > dropping
> > > > the table at the end. Neither way worked.
> > > >
> > > > "Nathan" wrote:
> > > >
> > > >> I am having an issue when converting some of my reports to SQL reporting
> > > >> services 2005. All of my reports use an OLEDB connection that point to a
> > > >> UDL
> > > >> file so that we can easily change connection strings as needed without
> > > >> having
> > > >> to touch the report server. They all use stored procedures to obtain the
> > > >> data. The issues I am running into is with stored procedures that use
> > > >> temp
> > > >> tables. Anything that is done in a stored procedure after the temp table
> > > >> is
> > > >> created is not shown in the report designer on the data tab within visual
> > > >> studio 2005. I've read a few posts about this problem but it seems like
> > > >> no
> > > >> one at Microsoft has been able to reproduce it. I have been able to
> > > >> reproduce it with a simple example using the NorthWind database. I will
> > > >> include the stored procedure and the rdl file below. I set the report to
> > > >> prompt for credentials instead of using the UDL file but the same problem
> > > >> occurs. It will occur with Windows authentication as well. If I change
> > > >> the
> > > >> connection type to "Microsoft SQL Server" then it will work correctly the
> > > >> problem is that I will not be able to use UDL or DSN files. My method
> > > >> worked
> > > >> just fine with RS 2000. What has changed with RS 2005 to cause this
> > > >> issue?
> > > >> Any ideas on how I might get this to work?
> > > >>
> > > >> Thanks,
> > > >> -Nathan
> > > >>
> > > >> SP Script-
> > > >>
> > > >> use northwind
> > > >>
> > > >> set ANSI_NULLS ON
> > > >> set QUOTED_IDENTIFIER OFF
> > > >> GO
> > > >>
> > > >> CREATE PROCEDURE [dbo].[Pr_TestingRSProblem]
> > > >> AS
> > > >>
> > > >> Select FirstName
> > > >> into #EmployeeTemp
> > > >> FROM Employees
> > > >> --*NOTE It doesn't matter what you do here it will not be returned in the
> > > >> Report Designer Data tab.
> > > >> SELECT FirstName
> > > >> FROM #EmployeeTemp
> > > >> --Select 'Test'
> > > >>
> > > >>
> > > >> RDL File-
> > > >>
> > > >> <?xml version="1.0" encoding="utf-8"?>
> > > >> <Report
> > > >> xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition"
> > > >> xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
> > > >> <DataSources>
> > > >> <DataSource Name="DataSource1">
> > > >> <ConnectionProperties>
> > > >> <Prompt>Specify a user name and password for data source
> > > >> DataSource1</Prompt>
> > > >> <ConnectString>Provider=SQLOLEDB.1;Data
> > > >> Source=saintdevsql1;Initial
> > > >> Catalog=Northwind</ConnectString>
> > > >> <DataProvider>OLEDB</DataProvider>
> > > >> </ConnectionProperties>
> > > >>
> > > >> <rd:DataSourceID>d66c0234-6fba-48b8-8c6a-6e6e72f006ca</rd:DataSourceID>
> > > >> </DataSource>
> > > >> </DataSources>
> > > >> <BottomMargin>1in</BottomMargin>
> > > >> <RightMargin>1in</RightMargin>
> > > >> <rd:DrawGrid>true</rd:DrawGrid>
> > > >> <InteractiveWidth>8.5in</InteractiveWidth>
> > > >> <rd:SnapToGrid>true</rd:SnapToGrid>
> > > >> <Body>
> > > >> <Height>0.75in</Height>
> > > >> </Body>
> > > >> <rd:ReportID>0eee2348-f25d-4594-b4f1-3bcec4cd58b1</rd:ReportID>
> > > >> <LeftMargin>1in</LeftMargin>
> > > >> <DataSets>
> > > >> <DataSet Name="Northwind">
> > > >> <Query>
> > > >> <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
> > > >> <CommandType>StoredProcedure</CommandType>
> > > >> <CommandText>Pr_TestingRSProblem</CommandText>
> > > >> <DataSourceName>DataSource1</DataSourceName>
> > > >> </Query>
> > > >> </DataSet>
> > > >> </DataSets>
> > > >> <Width>6.5in</Width>
> > > >> <InteractiveHeight>11in</InteractiveHeight>
> > > >> <Language>en-US</Language>
> > > >> <TopMargin>1in</TopMargin>
> > > >> </Report>
> > > >>
> > >
> > >
> > >|||This is the first I have heard of it on the newsgroups. I am not aware of
any hot fixes (SP1 makes no difference).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Nathan" <Nathan@.discussions.microsoft.com> wrote in message
news:18D44662-1B40-41D9-B4A0-049F8A893B76@.microsoft.com...
> I'm thinking of starting a support ticket with Microsoft about this issue.
> Before I do does anyone know of a reason why the behavior of the OLEDB
> connection would change between rs2000 and rs2005? Does anyone know of
> any
> hot fixes or SPs that might already fix this issue? I'm at a loss at what
> to
> do at this point. This is kind of a show stopper for us with moving to
> rs2005.
> Thanks,
> -Nathan
>
> "Nathan" wrote:
>> I must not have refreshed before sending my last reply. Looks like you
>> did
>> try the OLEDB. Well, thanks for trying. At least I know I'm not going
>> crazy
>> and it is reproducible and may actually be an issue with reporting
>> services.
>> The funny thing is that if you try making the same report in the previous
>> version of RS within VS2003 it works fine. That is with it connecting to
>> the
>> same DB server (SQL 2005). This seems like an unintended "feature" to
>> me.
>> Anyone else out there have any ideas?
>> Thanks,
>> -Nathan
>>
>> "Nathan" wrote:
>> > I tried the example you have given with the adventure works database.
>> > I am
>> > able to reproduce what you have with the connection type of "Microsoft
>> > SQL
>> > Server". However, if you select "OLEDB" you will encounter the problem
>> > I am
>> > having. I need to use this because of my requirements of using a dsn
>> > or udl
>> > file.
>> >
>> > Thanks again,
>> > -Nathan
>> >
>> >
>> > "Bruce L-C [MVP]" wrote:
>> >
>> > > I don't have northwind, I have AdventureWorks. Here is my stored
>> > > procedure:
>> > > create PROCEDURE [dbo].[Pr_TestingRSProblem]
>> > >
>> > > AS
>> > >
>> > > Select FirstName
>> > >
>> > > into #TEMP
>> > >
>> > > FROM person.contact
>> > >
>> > > SELECT distinct FirstName
>> > >
>> > > FROM #TEMP order by firstname
>> > >
>> > > return
>> > >
>> > > I did not use the wizard. I added a report. I went to the data tab.
>> > > Added a
>> > > new dataset. My shared data source credentials were windows
>> > > authentication.
>> > > When creating the dataset I changed the data type to stored procedure
>> > > and
>> > > put in the name of the stored procedure as the query string (do not
>> > > put in
>> > > exec, just put in the name of the procedure). I get an error and no
>> > > field
>> > > list. I click on refresh the field list I get a field list and now I
>> > > can
>> > > execute the query and get data back in the data tab.
>> > >
>> > >
>> > > --
>> > > Bruce Loehle-Conger
>> > > MVP SQL Server Reporting Services
>> > >
>> > >
>> > > "Nathan" <Nathan@.discussions.microsoft.com> wrote in message
>> > > news:07802D42-8A7D-4A56-B06D-8ED97EFB75F3@.microsoft.com...
>> > > >
>> > > > I meant to mention that I tried the sp with and without explicitly
>> > > > dropping
>> > > > the table at the end. Neither way worked.
>> > > >
>> > > > "Nathan" wrote:
>> > > >
>> > > >> I am having an issue when converting some of my reports to SQL
>> > > >> reporting
>> > > >> services 2005. All of my reports use an OLEDB connection that
>> > > >> point to a
>> > > >> UDL
>> > > >> file so that we can easily change connection strings as needed
>> > > >> without
>> > > >> having
>> > > >> to touch the report server. They all use stored procedures to
>> > > >> obtain the
>> > > >> data. The issues I am running into is with stored procedures that
>> > > >> use
>> > > >> temp
>> > > >> tables. Anything that is done in a stored procedure after the
>> > > >> temp table
>> > > >> is
>> > > >> created is not shown in the report designer on the data tab within
>> > > >> visual
>> > > >> studio 2005. I've read a few posts about this problem but it
>> > > >> seems like
>> > > >> no
>> > > >> one at Microsoft has been able to reproduce it. I have been able
>> > > >> to
>> > > >> reproduce it with a simple example using the NorthWind database.
>> > > >> I will
>> > > >> include the stored procedure and the rdl file below. I set the
>> > > >> report to
>> > > >> prompt for credentials instead of using the UDL file but the same
>> > > >> problem
>> > > >> occurs. It will occur with Windows authentication as well. If I
>> > > >> change
>> > > >> the
>> > > >> connection type to "Microsoft SQL Server" then it will work
>> > > >> correctly the
>> > > >> problem is that I will not be able to use UDL or DSN files. My
>> > > >> method
>> > > >> worked
>> > > >> just fine with RS 2000. What has changed with RS 2005 to cause
>> > > >> this
>> > > >> issue?
>> > > >> Any ideas on how I might get this to work?
>> > > >>
>> > > >> Thanks,
>> > > >> -Nathan
>> > > >>
>> > > >> SP Script-
>> > > >>
>> > > >> use northwind
>> > > >>
>> > > >> set ANSI_NULLS ON
>> > > >> set QUOTED_IDENTIFIER OFF
>> > > >> GO
>> > > >>
>> > > >> CREATE PROCEDURE [dbo].[Pr_TestingRSProblem]
>> > > >> AS
>> > > >>
>> > > >> Select FirstName
>> > > >> into #EmployeeTemp
>> > > >> FROM Employees
>> > > >> --*NOTE It doesn't matter what you do here it will not be returned
>> > > >> in the
>> > > >> Report Designer Data tab.
>> > > >> SELECT FirstName
>> > > >> FROM #EmployeeTemp
>> > > >> --Select 'Test'
>> > > >>
>> > > >>
>> > > >> RDL File-
>> > > >>
>> > > >> <?xml version="1.0" encoding="utf-8"?>
>> > > >> <Report
>> > > >> xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition"
>> > > >> xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
>> > > >> <DataSources>
>> > > >> <DataSource Name="DataSource1">
>> > > >> <ConnectionProperties>
>> > > >> <Prompt>Specify a user name and password for data source
>> > > >> DataSource1</Prompt>
>> > > >> <ConnectString>Provider=SQLOLEDB.1;Data
>> > > >> Source=saintdevsql1;Initial
>> > > >> Catalog=Northwind</ConnectString>
>> > > >> <DataProvider>OLEDB</DataProvider>
>> > > >> </ConnectionProperties>
>> > > >>
>> > > >> <rd:DataSourceID>d66c0234-6fba-48b8-8c6a-6e6e72f006ca</rd:DataSourceID>
>> > > >> </DataSource>
>> > > >> </DataSources>
>> > > >> <BottomMargin>1in</BottomMargin>
>> > > >> <RightMargin>1in</RightMargin>
>> > > >> <rd:DrawGrid>true</rd:DrawGrid>
>> > > >> <InteractiveWidth>8.5in</InteractiveWidth>
>> > > >> <rd:SnapToGrid>true</rd:SnapToGrid>
>> > > >> <Body>
>> > > >> <Height>0.75in</Height>
>> > > >> </Body>
>> > > >> <rd:ReportID>0eee2348-f25d-4594-b4f1-3bcec4cd58b1</rd:ReportID>
>> > > >> <LeftMargin>1in</LeftMargin>
>> > > >> <DataSets>
>> > > >> <DataSet Name="Northwind">
>> > > >> <Query>
>> > > >> <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
>> > > >> <CommandType>StoredProcedure</CommandType>
>> > > >> <CommandText>Pr_TestingRSProblem</CommandText>
>> > > >> <DataSourceName>DataSource1</DataSourceName>
>> > > >> </Query>
>> > > >> </DataSet>
>> > > >> </DataSets>
>> > > >> <Width>6.5in</Width>
>> > > >> <InteractiveHeight>11in</InteractiveHeight>
>> > > >> <Language>en-US</Language>
>> > > >> <TopMargin>1in</TopMargin>
>> > > >> </Report>
>> > > >>
>> > >
>> > >
>> > >

Friday, March 30, 2012

izzit got for loop in transact-sql stored procedure

Wednesday, March 28, 2012

Its to slow when I handle XML field in SQL 2005!

Iteration within Stored Procedure

Iteration in a stored procedure

Iterating through tables

Iterating through a recordset

Monday, March 26, 2012

Iterate result set inside stored procedure

items in list A that dont appear in list B (was "Simple Query...I think")

Friday, March 23, 2012

It doesnt execute my assemblies.

It doesnt execute my assemblies.

Wednesday, March 21, 2012

Issues with temp tables in stored procedures using OLEDB connectio

isql query output issue

Blog Archive

About Me