Distributed Query Connection Issues

I have the following situation: I have a database on ServerA and a database
on ServerB. My client app connects to ServerA and uses a stored procedure to
retrieve a dataset from both ServerA and ServerB.
While developing the app (using SQL 2K Dev on my workstation), I used a
linked server approach. I linked ServerB and refered to the database as
ServerB.DBName.dbo.TableName and it worked fine.
I'd like to use trusted connections and my users will have SQL server access
to both servers.
Here is the problem:
If I install the app on another workstation (i.e. not on the one running
ServerA), the I get:
Msg 18452
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
Using Query Analyser from the third workstation I can connect to both
serverA and ServerB using Windows Authentication, with both Named Pipes and
TCP/IP protocols.
What am I doing wrong?
Thank you very much for your help.
The issue you are hitting is sometimes called a double hop.
You are wanting to pass Windows credentials from one server
to another. The following KB article explains the issue and
still applies to SQL Server 2000 if you don't have kerberos
enabled and account delegation setup. The article specifies
SQL 7 as you can't get around this on SQL 7 and have to use
other methods to not hit issues with double hops.
PRB: Message 18456 from a Distributed Query
http://support.microsoft.com/?id=238477
Under SQL Server 2000, If you want to use Windows
authentication in this scenario, you need to use Active
Directory, enable kerberos and setup account delegation.
You can find more information in SQL Server books online
under the topic Security Account Delegation
-Sue
On Mon, 10 Jan 2005 10:55:01 -0800, "Konstantin"
<Konstantin@.discussions.microsoft.com> wrote:

>I have the following situation: I have a database on ServerA and a database
>on ServerB. My client app connects to ServerA and uses a stored procedure to
>retrieve a dataset from both ServerA and ServerB.
>While developing the app (using SQL 2K Dev on my workstation), I used a
>linked server approach. I linked ServerB and refered to the database as
>ServerB.DBName.dbo.TableName and it worked fine.
>I'd like to use trusted connections and my users will have SQL server access
>to both servers.
>Here is the problem:
>If I install the app on another workstation (i.e. not on the one running
>ServerA), the I get:
>Msg 18452
>Login failed for user '(null)'. Reason: Not associated with a trusted SQL
>Server connection.
>Using Query Analyser from the third workstation I can connect to both
>serverA and ServerB using Windows Authentication, with both Named Pipes and
>TCP/IP protocols.
>What am I doing wrong?
>Thank you very much for your help.

Distributed Query Connection Issues

I have the following situation: I have a database on ServerA and a database
on ServerB. My client app connects to ServerA and uses a stored procedure to
retrieve a dataset from both ServerA and ServerB.
While developing the app (using SQL 2K Dev on my workstation), I used a
linked server approach. I linked ServerB and refered to the database as
ServerB.DBName.dbo.TableName and it worked fine.
I'd like to use trusted connections and my users will have SQL server access
to both servers.
Here is the problem:
If I install the app on another workstation (i.e. not on the one running
ServerA), the I get:
Msg 18452
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection.
Using Query Analyser from the third workstation I can connect to both
serverA and ServerB using Windows Authentication, with both Named Pipes and
TCP/IP protocols.
What am I doing wrong?
Thank you very much for your help.The issue you are hitting is sometimes called a double hop.
You are wanting to pass Windows credentials from one server
to another. The following KB article explains the issue and
still applies to SQL Server 2000 if you don't have kerberos
enabled and account delegation setup. The article specifies
SQL 7 as you can't get around this on SQL 7 and have to use
other methods to not hit issues with double hops.
PRB: Message 18456 from a Distributed Query
http://support.microsoft.com/?id=238477
Under SQL Server 2000, If you want to use Windows
authentication in this scenario, you need to use Active
Directory, enable kerberos and setup account delegation.
You can find more information in SQL Server books online
under the topic Security Account Delegation
-Sue
On Mon, 10 Jan 2005 10:55:01 -0800, "Konstantin"
<Konstantin@.discussions.microsoft.com> wrote:

>I have the following situation: I have a database on ServerA and a database
>on ServerB. My client app connects to ServerA and uses a stored procedure t
o
>retrieve a dataset from both ServerA and ServerB.
>While developing the app (using SQL 2K Dev on my workstation), I used a
>linked server approach. I linked ServerB and refered to the database as
>ServerB.DBName.dbo.TableName and it worked fine.
>I'd like to use trusted connections and my users will have SQL server acces
s
>to both servers.
>Here is the problem:
>If I install the app on another workstation (i.e. not on the one running
>ServerA), the I get:
>Msg 18452
>Login failed for user '(null)'. Reason: Not associated with a trusted SQL
>Server connection.
>Using Query Analyser from the third workstation I can connect to both
>serverA and ServerB using Windows Authentication, with both Named Pipes and
>TCP/IP protocols.
>What am I doing wrong?
>Thank you very much for your help.

Distributed APP strategies-What to do where is killing me...

I just read this quote at DevX.Com
"It's generally better to use the stored procedure option to perform the
transaction against the database rather than initiate a transaction on a
Connection object. When possible, you should isolate transactions to the
data tier, because it's the DBMS that actually performs the transactions."
OK, that seems reasonbale to me. Also, it makes sense to me that Business
Rule edits should be performed in the Application tier.
Howver, what if the Business edit requires a DB lookup, should it be
performed in the database in a SP or in a Buiness Object on the app tier?
I expect the "correct" answer is in the App Tier? Hower, if the edit is
performed in the App tier and if it passes the edit, and a SP is called to
perform the update, it seems to me that unless we have placed the Business
Rule lookup AND the UPDATE in a TRANSACTION by intiatiating a Transaction on
the Connection object, that we cannot guarantee that at the time of the db
update, that the Business Rule edit check would still pass. Some other user
may have snuck in a performed an update in between the Business Rule check
on the application Tier and the actual update to the database!
I guess I need to understand more about LOCKING. If the records queried by
the Business Edit were still locked at the time of the update, we could
assume that the state of the database is still such that an update is still
permitted. I assumed that enclosing the Business edit and the update in a
transaction would ensure that.
Now my head starts hurting when I throw into the mix the fact that we are
using .NET Remoting (Well Known Single Call) STATELESS objects.
I understand that STATELESS remote objects allow for increase scalability
and load balancing, and so that is why this approach was probably taken.
Hwoever, since the objects ae stateless, each time we instantiate an object
to perform a database call, we are establishing a new connection. And this
presents the problem of how do you tie the Business Edit lookup and the Data
Update into a single transaction by initiating a Transaction on the
Connection object is a new Connection is established with each call to the
database!
Furthermore, if you have two business abojects, Account and User, and you
want to call the ACCOUNT.AddNewAccount method and the User.AddNewUser method
and have both operation performed in a transaction, how do you do this if a
each object is establishing its own connection!?
The answer, it seems to me, is that you can't, that you need to create a new
object to do both, but this throws away the ability to reuse existing
objects.
It seems that the more granular an Object is the more reusable it is, but by
using SingleCall Remoting we do not have the ability to tie multiple updates
into a transaction. Not to mention that the Account object need to perform
an BO edit before adding an Account (to make sure it doesnt already exist in
the db) and the User object needs to peform an edit before adding a user,
that this will require *TWO* round trips to the server, which hurts
performance. By writing a new object to do both at the same time, you
increase performance by making one trip by throw away reusability since most
of the time you will need to do one operation or the otehr, not both.
I could go on and on. I hope you were able to follw the jist it.
I wish you MVPs were sitting next to my cube.
Many thanks for all the help you've given me in just a short time..
Chad
"Chad" wrote:

> I just read this quote at DevX.Com
> "It's generally better to use the stored procedure option to perform the
> transaction against the database rather than initiate a transaction on a
> Connection object. When possible, you should isolate transactions to the
> data tier, because it's the DBMS that actually performs the transactions."
> OK, that seems reasonbale to me. Also, it makes sense to me that Business
> Rule edits should be performed in the Application tier.
> Howver, what if the Business edit requires a DB lookup, should it be
> performed in the database in a SP or in a Buiness Object on the app tier?
>
Thats where you need to the data to apply your rules, you're right..

> I expect the "correct" answer is in the App Tier? Hower, if the edit is
> performed in the App tier and if it passes the edit, and a SP is called to
> perform the update, it seems to me that unless we have placed the Business
> Rule lookup AND the UPDATE in a TRANSACTION by intiatiating a Transaction
on
> the Connection object, that we cannot guarantee that at the time of the db
> update, that the Business Rule edit check would still pass. Some other use
r
> may have snuck in a performed an update in between the Business Rule check
> on the application Tier and the actual update to the database!
This is a concurrency issue, I create a BINARY_CHECKSUM containing the rows
that will be updated when the data is loaded into the business object, it's
a
private readonly property, this gets passed back to the procedure that
updates if the checksum is different at the time of update then you know tha
t
the data has changed since the read, do whatever to handle it then.

> I guess I need to understand more about LOCKING. If the records queried by
> the Business Edit were still locked at the time of the update, we could
> assume that the state of the database is still such that an update is stil
l
> permitted. I assumed that enclosing the Business edit and the update in a
> transaction would ensure that.
The locking will only occur when you do the update.

> Now my head starts hurting when I throw into the mix the fact that we are
> using .NET Remoting (Well Known Single Call) STATELESS objects.
>
Don't let this confuse you these are totaly seperate techonologies, have you
looked at CSLA...? It's a very well thought out object oriented framework of
which there are many users...
http://groups.msn.com/CSLANET
http://www.apress.com/book/bookDisplay.html?bID=198
Also take a look at Ingo Rammers remoting book, very good stuff must have if
your doing .Net remoting.
http://www.apress.com/book/bookDisplay.html?bID=108

> I understand that STATELESS remote objects allow for increase scalability
> and load balancing, and so that is why this approach was probably taken.
> Hwoever, since the objects ae stateless, each time we instantiate an objec
t
> to perform a database call, we are establishing a new connection. And this
> presents the problem of how do you tie the Business Edit lookup and the Da
ta
> Update into a single transaction by initiating a Transaction on the
> Connection object is a new Connection is established with each call to the
> database!
> Furthermore, if you have two business abojects, Account and User, and you
> want to call the ACCOUNT.AddNewAccount method and the User.AddNewUser meth
od
> and have both operation performed in a transaction, how do you do this if
a
> each object is establishing its own connection!?
> The answer, it seems to me, is that you can't, that you need to create a n
ew
> object to do both, but this throws away the ability to reuse existing
> objects.
In these cases where you have more than one business object that you want to
enlist in the same transaction you might create a controller class that
orchestrates the transaction using the EnterpriseServices.ServicedComponent
functionality which is basically MTS for .Net. This is not an ADO.NET
transaction anymore, even though your still using an ADO transaction in your
business object and maybe even T-SQL transactions in your sp's. Nonetheless
they'll be controlled by the serviced component, keep in mind this creates a
LOT of overhead, don't do this unless you have to.

> It seems that the more granular an Object is the more reusable it is, but
by
> using SingleCall Remoting we do not have the ability to tie multiple updat
es
> into a transaction. Not to mention that the Account object need to perform
> an BO edit before adding an Account (to make sure it doesnt already exist
in
> the db) and the User object needs to peform an edit before adding a user,
> that this will require *TWO* round trips to the server, which hurts
> performance. By writing a new object to do both at the same time, you
> increase performance by making one trip by throw away reusability since mo
st
> of the time you will need to do one operation or the otehr, not both.
> I could go on and on. I hope you were able to follw the jist it.
> I wish you MVPs were sitting next to my cube.
> Many thanks for all the help you've given me in just a short time..
> Chad
>
>

Distinguish User Objects and System objects

How to differentiate between user objects(table,view...) and system objects. Where is this information stored in SQL Server 7.0?
Thanks in advance,
Arr S
How to differentiate where? Are you running a query? What is it? WHat
version of SQL Server are you running?
Typically you can add one of the following to limit the resultset to only
user-defined objects, depending on how you are querying for system objects:
WHERE OBJECTPROPERTY(id, 'isMSShipped') = 0
WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME), 'isMSShipped')=0
WHERE OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'isMSShipped')=0
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Arr S" <anonymous@.discussions.microsoft.com> wrote in message
news:8CB42760-C46E-438F-9DC9-785C79E1893F@.microsoft.com...
> How to differentiate between user objects(table,view...) and system
> objects. Where is this information stored in SQL Server 7.0?
> Thanks in advance,
> Arr S
|||You could try looking in the sysobjects table of the respective databases,
and use the Type column to distinguish between system and user created
objects. Look up the help file for details.
Regards
Ray Mond
"Arr S" <anonymous@.discussions.microsoft.com> wrote in message
news:8CB42760-C46E-438F-9DC9-785C79E1893F@.microsoft.com...
> How to differentiate between user objects(table,view...) and system
objects. Where is this information stored in SQL Server 7.0?
> Thanks in advance,
> Arr S
|||Hi,
The information about System and UserDefined Objects is stored in
sysobjects database... there are multiple ways for know userdefined
objects...
select * from dbo.sysobjects where OBJECTPROPERTY(id, N'IsUserTable')
= 1
--this statment gives all user defined tables.
select * from dbo.sysobjects where OBJECTPROPERTY(id, N'IsUserTable')
= 0
--this statment gives all system tables.
select * from dbo.sysobjects where OBJECTPROPERTY(id, N'IsForeignKey')
= 1
--this statment gives all forien key.
select * from dbo.sysobjects where OBJECTPROPERTY(id, N'IsPrimaryKey')
= 1
--this statment gives all primary key.
etc...
Regards-Hari Sharma
"Ray Mond" <yeohray@.hotmail.com> wrote in message news:<#Kbz7wSFEHA.3132@.TK2MSFTNGP12.phx.gbl>...
> You could try looking in the sysobjects table of the respective databases,
> and use the Type column to distinguish between system and user created
> objects. Look up the help file for details.
> --
> Regards
> Ray Mond
> "Arr S" <anonymous@.discussions.microsoft.com> wrote in message
> news:8CB42760-C46E-438F-9DC9-785C79E1893F@.microsoft.com...
> objects. Where is this information stored in SQL Server 7.0?

Distinguish User Objects and System objects

How to differentiate between user objects(table,view...) and system objects.
Where is this information stored in SQL Server 7.0?
Thanks in advance,
Arr SHow to differentiate where? Are you running a query? What is it? WHat
version of SQL Server are you running?
Typically you can add one of the following to limit the resultset to only
user-defined objects, depending on how you are querying for system objects:
WHERE OBJECTPROPERTY(id, 'isMSShipped') = 0
WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME), 'isMSShipped')=0
WHERE OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'isMSShipped')=0
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Arr S" <anonymous@.discussions.microsoft.com> wrote in message
news:8CB42760-C46E-438F-9DC9-785C79E1893F@.microsoft.com...
> How to differentiate between user objects(table,view...) and system
> objects. Where is this information stored in SQL Server 7.0?
> Thanks in advance,
> Arr S|||You could try looking in the sysobjects table of the respective databases,
and use the Type column to distinguish between system and user created
objects. Look up the help file for details.
Regards
Ray Mond
"Arr S" <anonymous@.discussions.microsoft.com> wrote in message
news:8CB42760-C46E-438F-9DC9-785C79E1893F@.microsoft.com...
> How to differentiate between user objects(table,view...) and system
objects. Where is this information stored in SQL Server 7.0?
> Thanks in advance,
> Arr S|||Hi,
The information about System and UserDefined Objects is stored in
sysobjects database... there are multiple ways for know userdefined
objects...
select * from dbo.sysobjects where OBJECTPROPERTY(id, N'IsUserTable')
= 1
--this statment gives all user defined tables.
select * from dbo.sysobjects where OBJECTPROPERTY(id, N'IsUserTable')
= 0
--this statment gives all system tables.
select * from dbo.sysobjects where OBJECTPROPERTY(id, N'IsForeignKey')
= 1
--this statment gives all forien key.
select * from dbo.sysobjects where OBJECTPROPERTY(id, N'IsPrimaryKey')
= 1
--this statment gives all primary key.
etc...
Regards-Hari Sharma
"Ray Mond" <yeohray@.hotmail.com> wrote in message news:<#Kbz7wSFEHA.3132@.TK2MSFTNGP12.phx.g
bl>...
> You could try looking in the sysobjects table of the respective databases,
> and use the Type column to distinguish between system and user created
> objects. Look up the help file for details.
> --
> Regards
> Ray Mond
> "Arr S" <anonymous@.discussions.microsoft.com> wrote in message
> news:8CB42760-C46E-438F-9DC9-785C79E1893F@.microsoft.com...
> objects. Where is this information stored in SQL Server 7.0?

Distincts databases on the same stored procedure

Hi!

I need to use two distincts databases on the same stored procedure. One, database1, is where I want to place the procedure, and where the table the procedure populates is (table1). The other, database2, hosts the table (table2) from where I select some data to put into table1 in database1.
Database1 name is fixed, while database2 name may change. So, I would like to pass database2 name as a parameter to the procedure. In this simple example, it works fine:

use database1
go
create procedure teste @.db_name varchar(10) as
exec ('select * from ' + @.db_name + '.dbo.table2')

But the problem is that in my procedure, database2 is used in a cursor, something like this:

create procedure myProcedure @.year int, @.pDatabase2 varchar(30) AS

declare ...

WHILE ...
BEGIN
...
declare cursor1 cursor for
select column1, column2 from @.pDatabase2.dbo.table2
where ...

open cursor1
fetch next from cursor1 into ...

close cursor1
deallocate cursor1

-- insert data into table
INSERT INTO table1
VALUES...

....

And I get an error if I try to use the exec! How can I do it? Can anyone help me please?

Thank you!

Try to rebuild your query; either refrain from using cursor, or from using variable for a database.

Also, you can EXEC('select column1, column2 from ' + @.pDatabase2 + '.dbo.table2 INTO ##TempTable'), then run a cursor over TempTable.

|||

You can execute the declare cursor dynamically if you use a global cursor like:

set @.dbname = quotename(@.pDatabase2)

exec ('declare cursor1 cursor global for select column1, column2 from ' + @.dbname + '.dbo.table2')

open cursor1

...

But it seems like what you are doing is unnecessary and complicated. You should instead do the following which is easier to manage.

1. Create a stored procedure in database2 that returns the expected results from table2 like:

create procedure gettable2

as

select col1, col2 from table2

2. And now do insert..exec from database1 like:

set @.sp = quotename(@.pDatabase2) + '.sys.sp_executesql'

insert into table1

exec @.sp 'gettable2'

Alternatively, you can just use approach in step #2 and issue the SELECT directly like:

set @.sp = quotename(@.pDatabase2) + '.sys.sp_executesql'

insert into table1

exec @.sp 'select col1, col2 from table2'

The SP is approach is slightly better from a security standpoint since you don't have to give SELECT permission on the table(s). With either approach, you will have to watch out for ownership chaining issues for example. See Books Online for more details on how ownership chaining works and db ownership chaining option.

|||Thank you a lot, it's much easier now!

DISTINCT MonthName for a lot of dates....

Hi all,
I have a table with several rows, each has a datetime field.
I want to query this table, ideally with my stored procedure and return just
a set of month names/numbers if possible, but I keep going around in circles
either getting ALL of my dates back with the names in a new column, or only
the month names, but order incorrectly...
table structure:
PregnancyLog
LogID int
LogDateTime datetime
sample data
LogID, LogDateTime
1,29/01/05
2,30/01/05
3,01/02/05
4,03/02/05
5,04/02/05
6,11/03/05
7,12/03/05
8,23/04/05
9,12/08/05
Expected results
MonthName, MonthNumber
January, 1
February, 2
March, 3
April, 4
August, 8
Any help would be appreciated - my only current resolution would be to
create a view of my data which gets me the month names, and then do a
distinct on that with the stored procedure, but I'd rather just do it once
in the stored procedure if possible.
Regards
Rob"Rob Meade" wrote ...

> Any help would be appreciated
I hate it when this happens...looks like I might have sussed it myself...
SELECT DATENAME(MONTH, LogDateTime) AS MonthName, MONTH(LogDateTime)
FROM PregnancyLog
GROUP BY DATENAME(MONTH, LogDateTime), MONTH(LogDateTime)
ORDER BY MONTH(LogDateTime)
Does that look acceptable to anyone? It gives me the results I wanted but I
just wanted to make sure..
Regards
Rob|||On Thu, 24 Nov 2005 23:20:43 GMT, Rob Meade wrote:

>"Rob Meade" wrote ...
>
>I hate it when this happens...looks like I might have sussed it myself...
>SELECT DATENAME(MONTH, LogDateTime) AS MonthName, MONTH(LogDateTime)
>FROM PregnancyLog
>GROUP BY DATENAME(MONTH, LogDateTime), MONTH(LogDateTime)
>ORDER BY MONTH(LogDateTime)
>Does that look acceptable to anyone? It gives me the results I wanted but
I
>just wanted to make sure..
>Regards
>Rob
>
Hi Rob,
Looks good.
Here's an (untested) alternative:
SELECT DISTINCT DATENAME(month, LogDateTime) AS MonthName,
MONTH(LogDateTime)
FROM PregnancyLog
ORDER BY MONTH(LogDateTime)
Maybe you can even remove the MONTH(LogDateTime) from the SELECT, but
I'm not sure of that.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||"Hugo Kornelis" wrote ...

> Looks good.
Thank you :o)

> Maybe you can even remove the MONTH(LogDateTime) from the SELECT, but
> I'm not sure of that.
Cheers for that Hugo, it worked a treat, I left the MONTH(LogDateTime) in,
and added an alias of MonthNumber as I use this in the application.
But its still less code than I had - many thanks :o)
Regards
Rob|||Hi Hugo,
Any ideas how I would add a "count" to the end of the result set of the
number of log items for each month returned by the existin query...
Ie...
MonthName MonthNumber Counter
January 1 2
February 2 6
March 3 15
Any help would be really appreciated, I've tried adding COUNT(LogID) to my
query, but then I get message telling me that things need adding to the
aggregate function or the group by clause, which I did try adding again but
then I have to lose the order by or else I get EVERY row
again...nightmare..
Any help appreciated.
Regards
Rob|||On Fri, 25 Nov 2005 23:17:45 GMT, Rob Meade wrote:

>Hi Hugo,
>Any ideas how I would add a "count" to the end of the result set of the
>number of log items for each month returned by the existin query...
>Ie...
>MonthName MonthNumber Counter
>January 1 2
>February 2 6
>March 3 15
>Any help would be really appreciated, I've tried adding COUNT(LogID) to my
>query, but then I get message telling me that things need adding to the
>aggregate function or the group by clause, which I did try adding again but
>then I have to lose the order by or else I get EVERY row
>again...nightmare..
>Any help appreciated.
>Regards
>Rob
>
Hi Rob,
If you need to add a count (or any other aggregate function), then you
can't use my shorter version; you'll have to return to your original
version with GROUP BY.
SELECT DATENAME(MONTH, LogDateTime) AS MonthName, MONTH(LogDateTime),
COUNT(LogID) AS Counter
FROM PregnancyLog
GROUP BY DATENAME(MONTH, LogDateTime), MONTH(LogDateTime)
ORDER BY MONTH(LogDateTime)
should work. If not, you'll need to provide more information, as
described in www.aspfaq.com/5006.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||"Hugo Kornelis" wrote ...

> SELECT DATENAME(MONTH, LogDateTime) AS MonthName, MONTH(LogDateTime),
> COUNT(LogID) AS Counter
> FROM PregnancyLog
> GROUP BY DATENAME(MONTH, LogDateTime), MONTH(LogDateTime)
> ORDER BY MONTH(LogDateTime)
> should work. If not, you'll need to provide more information, as
> described in www.aspfaq.com/5006.
Hi Hugo,
Worked a treat, many thanks - I thought I tried exactly that, but obviously
not, when I tried it, SQL moaned that I needed to add LogDateTime to the
GROUP BY...
Typical that I'd only just posted to see if I could get a few others to look
in this thread from yesterday as I wasn't sure if you'd return to this
message - and you've already solved it - lol - I'll get flamed now for
posting needlessly...hehe..sorry all :o)
Thanks muchly for the help - the website I'm creating is all about my new
born son, so its kinda important to me - thus appreciate the help even more
than usual :o)
Regards
Rob|||On Fri, 25 Nov 2005 23:31:09 GMT, Rob Meade wrote:
(snip)
> I'll get flamed now for
>posting needlessly...hehe..sorry all :o)
Hi Rob,
If you insist, I think I can arragne you being flamed. Do you want me to
call Celko over? ;->
Congratulations on your boy. Don't spend all your time building the
website - spend plenty time enjoying him. They grow up so fast.....
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Distinct Filter in SQL Reporting Services

Hello All,

I've got a stored proc that I can't change that creates a quite large
dataset and takes in 5 parameters. I need to be able to have each of
the parameters selectable from a drop down box so that as you go
through the list of parameters you restrict the results. I have it
able to load the parameters but I end up with this:

Location-
LONDON
LONDON
LONDON
NEW YORK
NEW YORK
NEW YORK
NEW YORK
TORONTO
TORONTO
...

Any idea how i can filter the list of locations by distinct? from
within SQL Reporting Services. I tried a temp tbl in query analyser
and that works but SQL reporting services doesn't like it.Nevermind, It seemed to work the second time i tried to run the query
in reporting services.

BEGIN

CREATE TABLE #temptbl2
(
EnddateVARCHAR(50),
ProjectNumber VARCHAR(50),
ProjectManager VARCHAR(50),
resourceManager VARCHAR(50),
CostCentreVARCHAR(50),
WorkInProgressFLOAT
)

INSERT INTO #temptbl2
EXECUTE jmpwipreportbasic '09/09/2009'

SELECT DISTINCT costcentre FROM #temptbl2

DROP TABLE #temptbl2

END

Maybe this will help someone else.

Distinct at two columns

I have this stored procedure:

ALTER PROCEDURE usp_My_Procedure
(
@.Country varchar(5)
)

AS
SELECT DISTINCT City, Short FROM Table1 WHERE Country = @.Country

RETURN

I want to select just one of each 'city' and 'short' in the database...But this is not working correct.....Whats wrong?

Lets say that I have a table that looks something like this

City Short

New York NY

Los Angeles LA

Lake Alice LA

Los Angeles LosAng

well ur code like this is anylized like give everything not repeated for these two columns,

try this

SELECT City, Short FROM Table1 WHERE Country = @.Country

and City in (SELECT DISTINCT City FROM Table1 WHERE Country = @.Country)

and Short in (SELECT DISTINCT short FROM Table1 WHERE Country = @.Country)

sql

Displaying XML Data

There is a requirement that I need to display the XML data stored in
database in one of the report. How can I display this data in Reporting
Services with the color schema similar to IE (elements and attributes in
different colors etc..)?
Thanks,
Live_Love_LaughThe displaying the XML data itself is not an issue. You can pass it to a
custom function (preferebaly located in an external assembly) and apply a XSL
transformation when the report is processed. What makes your task difficult
is the color-coding. Unfortunately, version 1.0 doesn't support HTML markers,
e.g. <b> for bold, <font> etc. To make the task even more difficult textboxes
are rendered as table cells and don't have ids which makes it difficult to
reference them by DHTML.
One thing you can try is exporting the report as XML and associating an XSL
stylesheet in the DataOutput properties which will render the report the way
you want it.
"Live_Love_Laugh" wrote:
> There is a requirement that I need to display the XML data stored in
> database in one of the report. How can I display this data in Reporting
> Services with the color schema similar to IE (elements and attributes in
> different colors etc..)?
> Thanks,
> Live_Love_Laugh
>
>|||I have the same requirement
Did either of you succeed?
If so can you tell me what you did or post an example?
Thanks in advance
"Teo Lachev" wrote:
> The displaying the XML data itself is not an issue. etc
> "Live_Love_Laugh" wrote:
> > There is a requirement that I need to display the XML data stored in
> > database in one of the report. How can I display this data in Reporting
> > Services with the color schema similar to IE (elements and attributes in
> > different colors etc..)?
> >
> > Thanks,
> > Live_Love_Laughsql

Displaying values from Informix Stored Procedure

I am not able to see the values returned from my stored procedure in the
report preview, but I am able to see them in the Data window. I made sure
that I went in and mapped the fields for each dataset that I am using in the
report, but this still does not work. Any ideas?
TIA,
JeremyAnyone?
"Chancetribe" wrote:
> I am not able to see the values returned from my stored procedure in the
> report preview, but I am able to see them in the Data window. I made sure
> that I went in and mapped the fields for each dataset that I am using in the
> report, but this still does not work. Any ideas?
> TIA,
> Jeremysql

Displaying print statements of Stored Procedures in SSIS Logs

Hi

I have few print statements in a stored procedure that gets called from the SSIS package. How do I make sure that these are captured in the SSIS log meaning how do I get them to be displayed in the Package Explorer window when running from the Business Intelligence Studio IDE.

Not possible. Print statements are diagnostic in nature and are not part of the returned data set, which is what gets exposed to SSIS.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1229612&SiteID=1

Displaying Print Debugging from Stored Procedure immediately

I am attempting to debug a very long stored procedure, for this I inserted many print statements at key points on the procedure, but I find that once I do an exec sp_name I only get the print statements show in the message tab only after the full execution or cancellation of the stored procedure.

Is there any way to force the message tab to display the messages mid execution?

One way is to use the raiserror command with a severity of 10 and a "with nowait" option. This will not cause an actual error to be raised, as you can see by running this code:

begin try

raiserror('Progress message', 10, 1) with nowait

end try

begin catch

print 'here'

end catch

Ron Rice

Displaying multiple records from one record

Hi there,

I've a complex stored procedure, which I'm trying to get a list of one or more users based the list of records that are being display. If I mentioned the overall store procedure here it'll turn you off, so I'll put the questions in bitesize chunks.

Example would be if Bob and Luke work for Acrm Corp and John for Maxwellarms Ltd, and there was an intimediate table which linked both. With my stored procedure I want to display both names in the same row as Acrm Corp. Is there a way to do this?

User table
Userid Username

1 Bob
2 Luke
3 John

Store procedure result
Companyname Username

Acrm Corp Null
Maxwellarm Ltd Null

Regards,

Since my last post, I found that the CMS is storing individual ids.

Still it would be nice to know how others are doing this.

An addition to my post above, I believe if I wanted more than one firstname and surname to appear in the columns, that I would have to create a different stored procedure retrieving the unique id from the first stored procedure.

Displaying messages to users

Hello,

I need some help coming up with a good concept for returning stored procedure messages to a user. Here is my setup:

Stored Proc:
@.ReturnCode INT - OUT
@.ReturnDetails VARCHAR(150) - OUT

If my stored procedure returns a value of less than 1 for the @.ReturnCode I want to display the error message in @.ReturnDetails, Now I have a DAL that returns back the @.ReturnCode to the codebehind ( via a function ) but how can i display the @.ReturnDetails to the user, I would like to use a javascript alert, I know how to display the message but my question is what is the best design for returning the message to the user?

I was thinking about passing in the caller when i instantiate my DAL, but I don't really like that idea, My other idea was have my function return a string like "-1|Invalid Password" and parsing it but don't like that either. Also is there a way to find out the page caller from my DAL? That would help a lot.

Your ideas are greatly appreciated.

Have you thought about having your DAL return a hashtable instead of just a return value? You could then do something like

string errorMessage;
if (int.Parse(myHashTable["ReturnValue"].ToString()) < 0)
// Return the error message to the users
errorMessage = myHashTable["ReturnMessage"].ToString();

Another alternative would be to pass a reference variable to your DAL, and populate this reference variable with the error message. Continue to use the integer return value, but have your DAL function populate the string with the return message to the user. You can then test the return value, and show the error if the return value is < 0. Try this:

string returnMessage ="";
if (MyDALFunction(ref returnMessage) < 0)
// Show your message
Response.Write(returnMessage);

Hope this helps...

displaying images horizontally - not vertically

I am trying to display image data stored in a database field using SRS 2005.
The image data is employee photos and I can get them to display in one column
straight down the report using a List control.
Is there a way to get them to display in 3 columns sorting across the page?
This would be like a High School yearbook layout...
Like this...
Pic 1 Pic 2 Pic 3
Pic 4 Pic 5 Pic 6
Instead of like this...
Pic 1
Pic 2
Pic 3
Pic 4Hi,
I'm not sure if this is done differently in SRS 2005 but in SRS 2000, you
can set the Columns property on the Report Page. Just click the page and
then in the Properties window, specify the number of columns needed (3 in
your case).
Hope that helps.
Assad
"jj#10" wrote:
> I am trying to display image data stored in a database field using SRS 2005.
> The image data is employee photos and I can get them to display in one column
> straight down the report using a List control.
> Is there a way to get them to display in 3 columns sorting across the page?
> This would be like a High School yearbook layout...
> Like this...
> Pic 1 Pic 2 Pic 3
> Pic 4 Pic 5 Pic 6
> Instead of like this...
> Pic 1
> Pic 2
> Pic 3
> Pic 4
>

Displaying image

I have an image (jpg/bmp) file on the hard drive. I want to be able to point
to the path from the report and display the image. The image stored on the
hard drive can change, hence I want the image displayed on the report also to
change accordingly. The stuff I am trying to do is about the company logo.
Currently I have my company's logo displayed, and when the report goes into
production I would like the customer's logo to be displayed on the report.
Any help is appreciated.Hi,
Yes it can be done. Select 'Web' from image properties and give the URL of
the image. But you need to have the same name for all the images. So if you
want to copy a different image change the name of the new image to the same
name what you had given in your URL.
Amarnath
"KMP" wrote:
> I have an image (jpg/bmp) file on the hard drive. I want to be able to point
> to the path from the report and display the image. The image stored on the
> hard drive can change, hence I want the image displayed on the report also to
> change accordingly. The stuff I am trying to do is about the company logo.
> Currently I have my company's logo displayed, and when the report goes into
> production I would like the customer's logo to be displayed on the report.
> Any help is appreciated.|||Sorry for the delay in replying. I don't understand what you are trying to
say - I want to use the file stored on the hard drive of the server, what is
the URL thing. Can you please explain more clearly?
Thanks.
"Amarnath" wrote:
> Hi,
> Yes it can be done. Select 'Web' from image properties and give the URL of
> the image. But you need to have the same name for all the images. So if you
> want to copy a different image change the name of the new image to the same
> name what you had given in your URL.
> Amarnath
> "KMP" wrote:
> > I have an image (jpg/bmp) file on the hard drive. I want to be able to point
> > to the path from the report and display the image. The image stored on the
> > hard drive can change, hence I want the image displayed on the report also to
> > change accordingly. The stuff I am trying to do is about the company logo.
> > Currently I have my company's logo displayed, and when the report goes into
> > production I would like the customer's logo to be displayed on the report.
> > Any help is appreciated.

Displaying HTML within an Access Report

Is this possible? I have a column which has data with HTML formatting.
Is it possible to remove the tags within a Stored Procedure so that it
displays nicely in an Access Report?
This is my SP:
ALTER PROCEDURE dbo.sp_Phat_Beats
AS SELECT fldcat, flddescript, fldtracklisting, fldprice, fldCategory
FROM dbo.tblProducts
WHERE (fldprice <> 0) AND (fldCategory = 17)
ORDER BY fldcat
fldtracklisting is the field that is HTML formatted. Any help would be
relly appreciated.
SteveHi
You could use replace multiple times to remove each tag, but this would
require embeded calls to the procedure one call per string you wish to
replace which could prove tedious. Check out replace in Books online
John
"Dooza" wrote:

> Is this possible? I have a column which has data with HTML formatting.
> Is it possible to remove the tags within a Stored Procedure so that it
> displays nicely in an Access Report?
> This is my SP:
> ALTER PROCEDURE dbo.sp_Phat_Beats
> AS SELECT fldcat, flddescript, fldtracklisting, fldprice, fldCategory
> FROM dbo.tblProducts
> WHERE (fldprice <> 0) AND (fldCategory = 17)
> ORDER BY fldcat
> fldtracklisting is the field that is HTML formatted. Any help would be
> relly appreciated.
> Steve
>

Displaying HTML in Report

Hi
I'm using a HTHL editor in a web site which enables users to format text in
HTML format which is then stored in the database e.g.
<span style="color: #000000">* Daily Email Checks <br/>* Tape Changes
<br/>* Documentation for Accessing OWA<br/>* Refamiliarisation of Site</span>
I'm sure I read somewhere that SQL Reporting Services 2008 would enable you
to display this in HTML format but I cant seem to get it to work in the
November 2008 CTP.
Am I correct with what I've think I read or is the functionality not
available in the November CTP?
Thanks
RKPCorrect, it is not available in the November CTP.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"RKP" <RKP@.discussions.microsoft.com> wrote in message
news:7D7FF1E7-9280-44C2-B8C2-9C03D3B496E4@.microsoft.com...
> Hi
> I'm using a HTHL editor in a web site which enables users to format text
> in
> HTML format which is then stored in the database e.g.
> <span style="color: #000000">* Daily Email Checks <br/>* Tape Changes
> <br/>* Documentation for Accessing OWA<br/>* Refamiliarisation of
> Site</span>
> I'm sure I read somewhere that SQL Reporting Services 2008 would enable
> you
> to display this in HTML format but I cant seem to get it to work in the
> November 2008 CTP.
> Am I correct with what I've think I read or is the functionality not
> available in the November CTP?
> Thanks
> RKP
>|||I presume we have a CTP later than November, does that mean this
feature is now available in the latest CTP or ithis feature is still
on-hold?
On Apr 10, 5:49=A0am, "Bruce L-C [MVP]" <bruce_lcNOS...@.hotmail.com>
wrote:
> Correct, it is not available in the November CTP.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "RKP" <R...@.discussions.microsoft.com> wrote in message
> news:7D7FF1E7-9280-44C2-B8C2-9C03D3B496E4@.microsoft.com...
>
> > Hi
> > I'm using a HTHL editor in a web site which enables users to format text=
> > in
> > HTML format which is then stored in the database e.g.
> > <span style=3D"color: #000000">* Daily Email Checks =A0 =A0<br/>* Tape C=hanges
> > <br/>* Documentation for Accessing OWA<br/>* Refamiliarisation of
> > Site</span>
> > I'm sure I read somewhere that SQL Reporting Services 2008 would enable
> > you
> > to display this in HTML format but I cant seem to get it to work in the
> > November 2008 CTP.
> > Am I correct with what I've think I read or is the functionality not
> > available in the November CTP?
> > Thanks
> > RKP- Hide quoted text -
> - Show quoted text -

Displaying HTML as text

I am trying to display a field in a report that sometimes contains html
stored as text. I want to be able to strip out the html tags so I can display
the text only in the report.To strip off the html I believe there is a framework function that you could
use. Set the value of the textbox to an expression like this:
= Code.StripHTML(Fields!Fieldname.value)
You would write the function StripHTML that would return the value with the
html stripped.
This link shows how to do this using regular expressions.
http://weblogs.asp.net/rosherove/archive/2003/05/13/6963.aspx
There is definitely a dotnet function that you could use too. Google on
stripping html and you should be able to find it.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Michael" <Michael@.discussions.microsoft.com> wrote in message
news:D5216804-6889-483A-BBB5-BC10BACEBB52@.microsoft.com...
>I am trying to display a field in a report that sometimes contains html
> stored as text. I want to be able to strip out the html tags so I can
> display
> the text only in the report.
>|||Thanks for the info Bruce. I am not a programmer so forgive for my questions,
how do you embed custom code into a sql report?
"Bruce L-C [MVP]" wrote:
> To strip off the html I believe there is a framework function that you could
> use. Set the value of the textbox to an expression like this:
> = Code.StripHTML(Fields!Fieldname.value)
> You would write the function StripHTML that would return the value with the
> html stripped.
> This link shows how to do this using regular expressions.
> http://weblogs.asp.net/rosherove/archive/2003/05/13/6963.aspx
> There is definitely a dotnet function that you could use too. Google on
> stripping html and you should be able to find it.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "Michael" <Michael@.discussions.microsoft.com> wrote in message
> news:D5216804-6889-483A-BBB5-BC10BACEBB52@.microsoft.com...
> >I am trying to display a field in a report that sometimes contains html
> > stored as text. I want to be able to strip out the html tags so I can
> > display
> > the text only in the report.
> >
>
>