How to remote processing reports?

Hi everyone!

I have my reports published in a report server. What do I need to do so that the user can processing reports from a windows application?

There are many ways to do that. Within VIsual Studio 2005 there is a reportviewer control which you can use within your application to contact and display report. If you only want to get a report from the Server and present the user a byte stream to save to a file (something like a button which says Save the report to PDF...) THen you can just use the Report Server Services interface. WHat do you actually want to do ? (Meanwhile you can take a look on: http://www.gotreportviewer.com/)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

sql

How to read third word from a string?

Hallow, everyone:
I want to read third word from a string. For example, the string is,
ABCDE fghijk 031 LPN OPQ
I need 031. How to read it?
Any help will be appreciated.
Thanks
ZYTOk, do I win a prize for finding the ugliest, most cumbersome way to do this? :)

declare @.strtest as varchar(100)
set @.strtest='ABCDE fghijk 031 LPN OPQ'
select substring(@.strtest,charindex(' ', @.strtest, charindex(' ',@.strTest,1)+1), charindex(' ', @.strtest,charindex(' ', @.strtest, charindex(' ',@.strTest,1)+1)+1)-charindex(' ', @.strtest, charindex(' ',@.strTest,1)+1))

Of course, it crashes if there are only three words to begin with.|||I'm sure there is an easier way..but here's my offering

DECLARE @.x varchar(8000)

SET @.x = 'ABCDE fghijk 031 LPN OPQ'

SELECT SUBSTRING(@.x
, CHARINDEX(' ',@.x,CHARINDEX(' ',@.x)+1)+1
, (CHARINDEX(' ',@.x,CHARINDEX(' ',@.x,CHARINDEX(' ',@.x)+1)+1)-1)
- (CHARINDEX(' ',@.x,CHARINDEX(' ',@.x)+1)+1)+1)|||I think you would have to add a space to the end of the string, just to make sure it always works, even if there are only 3 words.|||parsing strings is so much easier in compiled code. forgive me for saying this but sql sucks at it. what sql is good at is set based operations.

you should do this on the client if you can:

string[] s = mystring.Split(' ');
string third = s.Length >= 3 ? s[2] : null;|||that is what is listed in Inside T-SQL Programming as one of the justifications for implementing CLR and I looked over the performance metrics and played with it myself. but I am still of the mind that if it requires C# code do it in the application. we are prepping for the whole 2K5 thing, and one the things that scares me is the potential abuse that CLR leaves open. in sql 2k, i had to worry about bad sql which I could fix. Now I might have to worry about bad C# in my sql server process and it has been too long since I really programmed app code.|||that is what is listed in Inside T-SQL Programming as one of the justifications for implementing CLR and I looked over the performance metrics and played with it myself. but I am still of the mind that if it requires C# code do it in the application. we are prepping for the whole 2K5 thing, and one the things that scares me is the potential abuse that CLR leaves open. in sql 2k, i had to worry about bad sql which I could fix. Now I might have to worry about bad C# in my sql server process and it has been too long since I really programmed app code.

any technology can be abused. that's not necessarily a reason not to use it though. it can be a very powerful thing for stuff that sql is not designed for, such as string parsing or complicated math.

if you are not comfortable with C# or some other .net language though, I wouldn't recommend using CLR in sql server. why not learn it though? it's always good to grow. :)|||Here's so more abuse

CREATE FUNCTION udf_GetWord (
@.str nvarchar(4000)
, @.Word int
, @.Delim char(1)
)
RETURNS nvarchar(4000)
AS
BEGIN
DECLARE @.LastPosition int, @.DelimFound int, @.Start int, @.End int, @.WordFound nvarchar(4000)

SELECT @.LastPosition = 0, @.DelimFound = 0
WHILE (@.DelimFound < @.Word-1)
BEGIN
IF (CHARINDEX(@.Delim, @.str, @.LastPosition + 1) = 0)
BREAK

ELSE
BEGIN
SET @.LastPosition = CHARINDEX(@.Delim, @.str, @.LastPosition + 1)
SET @.DelimFound = @.DelimFound + 1
END

END

SET @.Start = @.LastPosition + 1
SET @.End = CHARINDEX(@.Delim, @.str, @.LastPosition + 1) - @.Start
IF @.End < 0 SET @.End = LEN(@.str)-@.start+1
SELECT @.WordFound = SUBSTRING(@.str,@.start,@.end)
RETURN @.WordFound
END|||any technology can be abused. that's not necessarily a reason not to use it though. it can be a very powerful thing for stuff that sql is not designed for, such as string parsing or complicated math.

if you are not comfortable with C# or some other .net language though, I wouldn't recommend using CLR in sql server. why not learn it though? it's always good to grow. :)

the average dba makes more than the average developer and no one is going to pay me to code C# right now. I actually spent the first few years of my programming career as a vb \ java \ classic asp developer but it has been all dba for a while now. lets see, should I take the entry level c# developer job with a paycut or should I take the good paying mid-level\senior dba job? hmmmmmm. I am only into my 2nd sql 2k5 book, so I am far enough behind in what I am decent at.|||I wasn't suggesting that you quit and start fresh as a dime-a-dozen programmer. just that you could learn C# if you wanted to. sounds like you don't want to though.

in that case stick with parsing strings in sql. it works, it's just not the most efficient way. ;)|||It's a front end issue...let the minions handle it|||I wasn't suggesting that you quit and start fresh as a dime-a-dozen programmer. just that you could learn C# if you wanted to. sounds like you don't want to though.

in that case stick with parsing strings in sql. it works, it's just not the most efficient way. ;)

I hear you but the only way to get good is to do it everyday and I just do not have that opp. I actually picked up Inside C# and got myself a compiler last summer and frankly I got bored with it.|||fair enough.

about getting bored though, i think that's your own fault (or maybe the books fault - I hardly ever read tech books), not the fault of the language. there are lots of interesting and fun things you can do in C/C++/C#.

Here's just one tiny example: implement an RDBMS. your beloved SQL Server is implemented in those languages. ;)

How to read data from remote server inside a transaction?

Hello, everyone:

I have a local transaction,

BEGIN TRAN
INSERT Z_Test SELECT STATE_CODE FROM View_STATE_CODE
COMMIT

View_STATE_CODE points to remote SQL server named PROD. There is error when I run this query:

Server: Msg 8501, Level 16, State 1, Line 12
MSDTC on server 'PROD' is unavailable.
Server: Msg 7391, Level 16, State 1, Line 12
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d01c].

It looks like remote server is not available inside the local transaction. How to handle that?

Thanks

ZYT1) make sure MSDTC is running, u can see that from SQl Service Manager.
2) add this code before 'begin tran' , SET XACT_ABORT ON
3) instead of 'begin tran' begin distributed tran'
4) And SET XACT_ABORT OFF, AFTER COMMIT TRAN
Eg:

SET XACT_ABORT ON
begin distributed tran

insert into sometable select * from remoteserver.database.dbo..foo

commit tran
SET XACT_ABORT OFF


or check this site http://support.microsoft.com/default.aspx?scid=kb;en-us;839279 if ur SQL Server 2000 server installed on Windows Server 2003 or Windows XP Service Pack 2|||Hello, Mailler:

Thanks. MSDTC is running. The query you posted doesn't work.

ZYT|||what error u getting now.Is SQL Server 2000 server installed on Windows Server 2003 or Windows XP Service ?|||Hi, Mailler:

Thanks. The error is still "MSDTC on server 'PROD' is unavailable.
". The SQL Server 2000 is running on Windows XP SP1. Is it the reason?

ZYT|||check the link i given in first reply

How to query by grouping one column

Hello, everyone:
I have a table like:
Col1 Col2
1 A
2 B
1 D
1 P
2 F
2 W
How to query this table to return by Col1 like
Col1 Col2
1 A,D,P
2 B,F,W
Thanks a lot
ZYTUSE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(Col1 int, Col2 char(1))
GO

INSERT INTO myTable99(Col1, Col2)
SELECT 1, 'A' UNION ALL
SELECT 2, 'B' UNION ALL
SELECT 1, 'D' UNION ALL
SELECT 1, 'P' UNION ALL
SELECT 2, 'F' UNION ALL
SELECT 2, 'W' UNION ALL
SELECT 3, 'X' UNION ALL
SELECT 3, 'Y' UNION ALL
SELECT 4, 'Z' UNION ALL
SELECT 5, 'O'
GO

DECLARE @.MAX_Col1 int, @.Col1 int, @.strCol2 varchar(8000)
DECLARE @.rs table(Col1 int, strCol2 varchar(8000))

SELECT @.MAX_Col1 = MAX(Col1), @.Col1 = MIN(Col1) FROM myTable99

WHILE @.MAX_Col1 > = @.Col1
BEGIN
SELECT @.strCol2 = COALESCE(@.strCol2 + ', ','') + Col2 FROM myTable99 WHERE Col1 = @.Col1
INSERT INTO @.rs(Col1, strCol2) SELECT @.Col1, @.strCol2
SELECT @.Col1 = MIN(Col1), @.strCol2 = null FROM myTable99 WHERE Col1 > @.Col1
END

SELECT * FROM @.rs
GO

SET NOCOUNT OFF
DROP TABLE myTable99
GO|||Your code is always so thorough. I bet your desk is spotless. ;)|||Naw, not spotless, nut definetly not a pig sty

I need to stay organized though...I hate to have to remember things...

Except where the tequila is...|||When you forget that, you probably shouldn't be having any more anyway.|||See!

No Need to remember|||To Brett Kaiser
Why so difficult?
You can create function:
CREATE FUNCTION GetAllOnLine(@.id int)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @.Result VARCHAR(8000)
SET @.Result = ''

SELECT @.Result = @.Result + col2 + ' ,'
FROM YourTable
WHERE col1=@.id

RETURN @.Result
END

And use it:

SELECT Col1, GetAllOnLine(Col1)
FROM YourTable|||OK...needs a little cleaning up...

CREATE FUNCTION GetAllOnLine(@.id int)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @.Result VARCHAR(8000)

SELECT @.Result = COALESCE(@.Result + ', ','') + col2
FROM myTable99
WHERE col1=@.id

RETURN @.Result
END
GO

SELECT Col1, dbo.GetAllOnLine(Col1) FROM MyTable99

But don't you double the access paths to the table? One for the UDF and another for the SELECT? If it's a lot of rows I think mine would be more effecient...(though I still would like to know why anyone would like to do this)

Also, I don't usually like to make my UDF's some very specific, I prefer them to be as generic as possible. You could only use this for one very specific case.

Do you mind if I blog your solution as another example?|||This is execution plan of my result query:
|--Compute Scalar(DEFINE:([Expr1002]=[dbo].[MyGetAllOnLine2]([Mail].[CC])))
|--Hash Match(Aggregate, HASH:([Mail].[CC]), RESIDUAL:([Mail].[CC]=[Mail].[CC]))
|--Clustered Index Scan(OBJECT:([MWolf_Mail].[dbo].[Mail].[PK_Mail]))
It is better plan that I saw. Unfortunately, specific UDF is pay for good performance.|||Oh. Forum engine replace my plan on smile!!!
:-()|||With code tags perhaps...very nice plan..do you mind if I blog this with the appropriate credit?

This is execution plan of my result query:
|--Compute Scalar(DEFINE:([Expr1002]=[dbo].[MyGetAllOnLine2]([Mail].[CC])))
|--Hash Match(Aggregate, HASH:([Mail].[CC]), RESIDUAL:([Mail].[CC]=[Mail].[CC]))
|--Clustered Index Scan(OBJECT:([MWolf_Mail].[dbo].[Mail].[PK_Mail]))
It is better plan that I saw. Unfortunately, specific UDF is pay for good performance.|||If i understood you correctly, you want to post my example somewhere for others to see it and learn (please apologize my bad English)
If i'm right i have no objections else please correct me