i get an error when i use COUNT(DISTINCT colum_name)

hello,

i have a working stored procedure

SELECT CommentID, UserName, PictureID, DateCommented, COUNT(CommentID) OVER (PARTITION BY PictureID) AS'NrOfComments'FROM Comments WHERE PictureID = @.PictureID

witch returns among others the number of comments for a picture

i need to select the number of distinct users who commented that user too, so i added this at SELECT statement

, COUNT(DISTINCT UserName) AS'Expr1'

i get that error:

"Colum 'Comments.CommentID' is invalig in the select list beacuse it is not contained in either an aggregate function or the GROUP BY clause."

what should i do, to select the number of distinct users who commented on a specific picture?

here are the table rows, if you need that

CommentID -- UserName -- PictureID -- DateCommented

please help me, thank you

You cannot get both the distinct users and the total number of comments for a picture in the same query. To use the COUNT function, you need to use GROUP BY.

For instance, to get the total number of comments for a given PictureID you would do:

SELECT PictureID,COUNT(*)FROM CommentsWHERE PictureID = @.PictureIDGROUP BY PictureID

If you would like to get the number of comments each user has made for a given PictureID you would do:

SELECT PictureID, UserName,COUNT(*)FROM CommentsWHERE PictureID = @.PictureIDGROUP BY PictureID, UserName

You will have to do two queries to get the data you want - one for the summary, and one for the actual rows.|||

ok, i understand...

i have one last question

Is possible to have two SELECT statements in the same stored procedure?

thank you for replays

|||

Basically, you can only return the result from one SELECT statement but it is possible to build this query as a UNION or JOIN with values that you need. However, this can be quite complex, and I would recommend you to split it into two procedures in this case.

|||

yes, two select statements are not possbile, but select in select works very good for what i was needed

SELECT CommentID, UserName, PictureID,(SelectCOUNT(CommentID)FROM UserPictures)AS 'Comments'
FROM UserPictures

thanks for replyes, the post is solved

|||

Ouch. I don't mean to scare you, but this will in fact count the total number of comments for every row in your table. Doing the same calculation over and over again, it will give you the same result for every row. Is this really what you want? It is ok as long as you are aware that there may be performance issues with this. It might be an easy solution, (and working) if you dont have zillions of rows in your table ;-)

I don't want user dynamic sql

Dear all,
I have a stored procedure which might do INSERT, UPDATE over a specified
table.
Issue is that, at the outset, that table is totally unknown:
' into aux_PLAZO_PRIMER_IMPAGO ' +
' from ' + @.TABLA_COB
EXEC
..
' end DISC043 ' +
' into aux_PLAZO_PRIMER_IMPAGO ' +
' from ' + @.TABLA_COB
EXEC...
BLA,BLA,
I'm looking for a best version of that, dangerous dynamic sql is not well
welcomed here so that...
Declaring a variable as table also to solve the problem.
Thanks in advance for any input,
--
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''s hard to provide information
without seeing the code. location: Alicante (ES)Enric
The best solution is know a table name that you operate on.
http://www.sommarskog.se/dynamic_sql.html
"Enric" <vtam13@.terra.es.(donotspam)> wrote in message
news:72F7C1B3-7C25-414A-8957-D9B0472476D6@.microsoft.com...
> Dear all,
> I have a stored procedure which might do INSERT, UPDATE over a specified
> table.
> Issue is that, at the outset, that table is totally unknown:
> ' into aux_PLAZO_PRIMER_IMPAGO ' +
> ' from ' + @.TABLA_COB
> EXEC
> ..
> ' end DISC043 ' +
> ' into aux_PLAZO_PRIMER_IMPAGO ' +
> ' from ' + @.TABLA_COB
> EXEC...
> BLA,BLA,
> I'm looking for a best version of that, dangerous dynamic sql is not well
> welcomed here so that...
> Declaring a variable as table also to solve the problem.
> Thanks in advance for any input,
> --
> Please post DDL, DCL and DML statements as well as any error message in
> order to understand better your request. It''s hard to provide information
> without seeing the code. location: Alicante (ES)|||not sure if this is what you're looking for, but sp_executesql is the
first step to prevent SQL injection attacks. after that you're next
best step would be to validate the table against the sysobjects table
to ensure that it is a real table.|||I'm not sure I agree with what he says on that link. IMO he uses exec
way too much. His reasons for not using sp_executesql do not make
sense, and while he does suggest using quotename(), stored procedures
are a better way to ensure that your parameters are unable to be
anything other than the datatype suggested.|||Create as many INSERT and UPDATE procedures as there are tables, that's the
best advice any one can (and should) give you.
If you don't care about data, however, you could just as well store it all
in a single table. (NOT RECOMMENDED!)
I see no reason for dynamic SQL in such elementary processes as inserting,
updating or deleteing a row in a table - building the query string might eve
n
take longer than the execution (i.e. if you decide to do it right - clean up
the parameters to prevent SQL injection, check whether objects exist,
validating the parameters that contain actual values, etc.).
ML
http://milambda.blogspot.com/|||I don't know if this is the situation, but creating a generic insert /
update procedure for tables that store different types of objects is a bad
idea. Even if it's not the case now, as time goes on, there will be a need
to implment additional table specific parameters, data validation
programming, etc. and the procedure will become a big pile of unmanageable
spaghetti. Business rules for things like data validation and referential
integrity are best embedded at the table level in the form of constraints
and triggers.
Generic data access programming is best implmented on the application side
in the form of a data access class.
Designing Data Tier Components and Passing Data Through Tiers:
http://msdn.microsoft.com/library/d...Gag
.asp
On the other hand, if these tables store the same object (vertical
paritioning), then you can implement a partitioned view and perform inserts
/ updates into that:
Modifying Data in Partitioned Views:
http://msdn2.microsoft.com/en-us/library/ms187067.aspx
Strategies for Partitioning Relational Data Warehouses in Microsoft SQL
Server:
http://www.microsoft.com/technet/pr.../2005/spdw.mspx
That said; if dynamic SQL can't be avoided, here my links on the topic of
SQL injection:
http://www.sqlservercentral.com/col...qlinjection.asp
http://www.sqlservercentral.com/col...ectionpart1.asp
http://www.nextgenss.com/papers/adv...l_injection.pdf
"Enric" <vtam13@.terra.es.(donotspam)> wrote in message
news:72F7C1B3-7C25-414A-8957-D9B0472476D6@.microsoft.com...
> Dear all,
> I have a stored procedure which might do INSERT, UPDATE over a specified
> table.
> Issue is that, at the outset, that table is totally unknown:
> ' into aux_PLAZO_PRIMER_IMPAGO ' +
> ' from ' + @.TABLA_COB
> EXEC
> ..
> ' end DISC043 ' +
> ' into aux_PLAZO_PRIMER_IMPAGO ' +
> ' from ' + @.TABLA_COB
> EXEC...
> BLA,BLA,
> I'm looking for a best version of that, dangerous dynamic sql is not well
> welcomed here so that...
> Declaring a variable as table also to solve the problem.
> Thanks in advance for any input,
> --
> Please post DDL, DCL and DML statements as well as any error message in
> order to understand better your request. It''s hard to provide information
> without seeing the code. location: Alicante (ES)|||Will (william_pegg@.yahoo.co.uk) writes:
> I'm not sure I agree with what he says on that link. IMO he uses exec
> way too much. His reasons for not using sp_executesql do not make
> sense, and while he does suggest using quotename(), stored procedures
> are a better way to ensure that your parameters are unable to be
> anything other than the datatype suggested.
What I try to say about this particular case, is that that you should
not do this at all. That is, you should have one procedure per table.
I can't recall that I suggest that EXEC() should be used over sp_executesql,
but you are right that the text could be stronger on using sp_executesql,
and most of all using parameters. In fact, I'm already working with
reworking the article with a lot more emphasis on sp_executesql.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

I don't understand it

Dear all,
I'm executing a DTS which own a sql server task that launch a stored
procedure.
This stored procedure run a WHILE sentence and then launch a bcp command
dinamically.
Well, everything works debugging that stored procedure or simply launch it
throught Query Analyzer with its parameters.
Problem come up when I launch the DTS. That sql server task works but only
launch one time the BCP command.
I know that is very difficult reach to see the exit but...
There goes an excerpt:
set @.cont = 1
Set @.iCont = (select count(*) from tblDownloadKF_Bridge_Info)
WHILE @.cont < @.iCont + 1
begin
set @.table = (select name from tblDownloadKF_Bridge_Info where ID = @.cont)
SET @.bcpCommand= 'bcp ' + @.db + '' + @.table + ' out ' + @.strPath +
Ltrim(RTrim(@.table)) + '.dat -c -S ' + @.host + ' -U ' + @.strUser +' -P '
+
@.strPassword + ''
EXEC @.result = master.dbo.xp_cmdshell @.bcpCommand
if (@.result = 0)
set @.status = 'done'
else
set @.status = 'error'
update tblDownloadKF_Bridge_Info
set Remarks = Ltrim(Rtrim(@.status)),
dateoperation = getdate()
where ID = @.cont
set @.table = ''
set @.cont = @.cont + 1
end
GO
Is there any restriction on that command via DTS'
Thanks so much,Hi Enric,
Your problem is not the BCP command but xp_cmdshell stored procedure.
"INF: How to Run a DTS Package as a Scheduled Job"
http://support.microsoft.com/defaul...kb;en-us;269074
Cristian Lefter, SQL Server MVP
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:0C0CB603-97B8-40B0-9255-D6E3E3F245B3@.microsoft.com...
> Dear all,
> I'm executing a DTS which own a sql server task that launch a stored
> procedure.
> This stored procedure run a WHILE sentence and then launch a bcp command
> dinamically.
> Well, everything works debugging that stored procedure or simply launch it
> throught Query Analyzer with its parameters.
> Problem come up when I launch the DTS. That sql server task works but only
> launch one time the BCP command.
> I know that is very difficult reach to see the exit but...
> There goes an excerpt:
> set @.cont = 1
> Set @.iCont = (select count(*) from tblDownloadKF_Bridge_Info)
> WHILE @.cont < @.iCont + 1
> begin
> set @.table = (select name from tblDownloadKF_Bridge_Info where ID = @.cont)
>
> SET @.bcpCommand= 'bcp ' + @.db + '' + @.table + ' out ' + @.strPath +
> Ltrim(RTrim(@.table)) + '.dat -c -S ' + @.host + ' -U ' + @.strUser +' -P
> ' +
> @.strPassword + ''
> EXEC @.result = master.dbo.xp_cmdshell @.bcpCommand
> if (@.result = 0)
> set @.status = 'done'
> else
> set @.status = 'error'
> update tblDownloadKF_Bridge_Info
> set Remarks = Ltrim(Rtrim(@.status)),
> dateoperation = getdate()
> where ID = @.cont
> set @.table = ''
> set @.cont = @.cont + 1
> end
> GO
>
> Is there any restriction on that command via DTS'
> Thanks so much,

I don't remember...

Dear folks,
Could you please so kind to tell me where the fixed type data stored are? I
mean, in what system table are available these data?
"bigint, binary, float, decimal", and so on
Thanks in advance and regards,
Enricsystypes
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:0FEAE3E6-C6D8-400E-B210-F999DEBE6D17@.microsoft.com...
> Dear folks,
> Could you please so kind to tell me where the fixed type data stored are?
I
> mean, in what system table are available these data?
> "bigint, binary, float, decimal", and so on
> Thanks in advance and regards,
> Enric|||thanx
"Tibor Karaszi" wrote:

> systypes
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Enric" <Enric@.discussions.microsoft.com> wrote in message
> news:0FEAE3E6-C6D8-400E-B210-F999DEBE6D17@.microsoft.com...
>
>

I dont know how to use storedProcedure that returns one value as a result to a VB calling

My stored procedure works perfectly when I run Query Analyser, but when I run my VB program I get the eror: I get the message : An SqlParameter with ParameterName'@.total' is not contained by this SqlParameterCollection.

Here is my stored Proc and my VB program is right below

I- Stored Proc:

CREATE PROCEDURE dbo.totalsub
@.account bigint,
@.total bigint output
AS
select total=sum(SubPhnNmbr) from tblsub whereSubAccNmbr=@.account
return
GO

II- And my pogram in VB is:

Dim totsub As Int64
Dim cm As New SqlCommand
Dim cn As New MyConnection
cn.open
'my connection is defined by me don't worry about it
cm.CommandType = CommandType.StoredProcedure
cm.CommandText = "totalsub"
cm.Connection = cn
Dim pm As SqlParameter
pm = cm.Parameters.Add(New System.Data.SqlClient.SqlParameter("@.Account", System.Data.SqlDbType.BigInt))
pm.Value = 100000165
pm = cm.Parameters.Add(New System.Data.SqlClient.SqlParameter("total", System.Data.SqlDbType.BigInt, 4))
pm.Direction = ParameterDirection.Output
totsub = cm.Parameters("total").Value
cm.ExecuteScalar()
totsub = cm.Parameters("total").Value

I also tried using @.total instead of total and I tried ParameterDirection.ReturmValue instead of ParameterDirection.Output

No Luck, someone pls can help

check the second part ofthis article.|||Thanks.

I can't see my Procedure. Why? This is my first Stored Procedure.

Hello,
I created the "MyDb" database using Microsoft SQL 2005 Server Management
Studio and I added the table "dbo.Surveys".
Then I right clicked on Store Procedures and created a new procedure.
When I close it I am asked to save it. I save it and gave the file a
name.
However my stored procedure doesn't show in the Stored Procedures list.
I can only access it by loading the file (File > Open > File). Why?
When I open the File the Connect Window shows again.
I believe the procedure is created because I used CREATE.
When I execute it twice I got the message that there is already another
procedure with that name so I change CREATE to ALTER.
Could you explain to me how to make my procedure visible in:
Databases/MyDb/Programmability/StoredProcedores
How to run it with dbo.Surveys table and see all the records that
returns?
Thank You,
Miguel
My Stored Procedure code is:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE dbo.Surveys_GetSurveyBySurveyName
-- Procedure Parameters
@.SurveyName nvarchar
AS
BEGIN
-- Prevent extra result sets from interfering with SELECT statements
SET NOCOUNT ON;
-- Procedure statements
SELECT m.SurveyName
FROM dbo.application_Surveys
WHERE @.SurveyName = u.SurveyName
IF ( @.@.ROWCOUNT = 0 ) -- Survey Name not found
RETURN -1
RETURN 0
END
GO> When I close it I am asked to save it. I save it and gave the file a name.
That is a file.

> However my stored procedure doesn't show in the Stored Procedures list.
> I can only access it by loading the file (File > Open > File). Why?
You need to *RUN* the CREATE PROCEDURE script to apply the procedure to the
database. This has nothing to do with a file you save on your hard drive.

> I believe the procedure is created because I used CREATE.
> When I execute it twice I got the message that there is already another
> procedure with that name so I change CREATE to ALTER.
Then maybe you created it in the wrong database? Just because you created a
database does not necessarily mean that your current query window is in that
database's context.

> Could you explain to me how to make my procedure visible in:
> Databases/MyDb/Programmability/StoredProcedores
Did you right-click and hit refresh? Did you try EXEC
dbo.Surveys_GetSurveyBySurveyName?

> My Stored Procedure code is:
What happens when you insert this here:

> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
USE MyDB
GO

> ALTER PROCEDURE dbo.Surveys_GetSurveyBySurveyName
...|||Well, have you hit F5 after creation the SP? Have you refresh on stored
procedure--system stored procedure folder?
"Miguel Dias Moura" <md*REMOVE*moura@.gmail*NOSPAM*.com> wrote in message
news:u$nJopbTGHA.5108@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I created the "MyDb" database using Microsoft SQL 2005 Server Management
> Studio and I added the table "dbo.Surveys".
> Then I right clicked on Store Procedures and created a new procedure.
> When I close it I am asked to save it. I save it and gave the file a name.
> However my stored procedure doesn't show in the Stored Procedures list.
> I can only access it by loading the file (File > Open > File). Why?
> When I open the File the Connect Window shows again.
> I believe the procedure is created because I used CREATE.
> When I execute it twice I got the message that there is already another
> procedure with that name so I change CREATE to ALTER.
> Could you explain to me how to make my procedure visible in:
> Databases/MyDb/Programmability/StoredProcedores
> How to run it with dbo.Surveys table and see all the records that returns?
> Thank You,
> Miguel
> My Stored Procedure code is:
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> ALTER PROCEDURE dbo.Surveys_GetSurveyBySurveyName
> -- Procedure Parameters
> @.SurveyName nvarchar
> AS
> BEGIN
> -- Prevent extra result sets from interfering with SELECT statements
> SET NOCOUNT ON;
> -- Procedure statements
> SELECT m.SurveyName
> FROM dbo.application_Surveys
> WHERE @.SurveyName = u.SurveyName
> IF ( @.@.ROWCOUNT = 0 ) -- Survey Name not found
> RETURN -1
> RETURN 0
> END
> GO
>

I cant see my Procedure. Why? This is my first Stored Procedure.

Hello,

I created the "MyDb" database using Microsoft SQL 2005 Server Management Studio and I added the table "dbo.Surveys".

Then I right clicked on Store Procedures and created a new procedure.
When I close it I am asked to save it. I save it and gave the file a name.
However my stored procedure doesn't show in the Stored Procedures list.
I can only access it by loading the file (File > Open > File). Why?

When I open the File the Connect Window shows again.

I believe the procedure is created because I used CREATE.
When I execute it twice I got the message that there is already another procedure with that name so I change CREATE to ALTER.

Could you explain to me how to make my procedure visible in:
Databases/MyDb/Programmability/StoredProcedores

How to run it with dbo.Surveys table and see all the records that returns?

Thank You,
Miguel

My Stored Procedure code is:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE dbo.Surveys_GetSurveyBySurveyName
-- Procedure Parameters
@.SurveyName nvarchar
AS
BEGIN
-- Prevent extra result sets from interfering with SELECT statements
SET NOCOUNT ON;

-- Procedure statements
SELECT m.SurveyName
FROM dbo.application_Surveys
WHERE @.SurveyName = u.SurveyName

IF ( @.@.ROWCOUNT = 0 ) -- Survey Name not found
RETURN -1
RETURN 0

END
GO

USE MyDb

GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE dbo.Surveys_GetSurveyBySurveyName
-- Procedure Parameters
@.SurveyName nvarchar
AS
BEGIN
-- Prevent extra result sets from interfering with SELECT statements
SET NOCOUNT ON;

-- Procedure statements
SELECT m.SurveyName
FROM dbo.application_Surveys
WHERE @.SurveyName = u.SurveyName

IF ( @.@.ROWCOUNT = 0 ) -- Survey Name not found
RETURN -1
RETURN 0

END
GO

this one can create on sp on my machine.

Limno

|||

You must have created the stored proc in some other database.

Here's a script from SQLServerCentral.com that you can use to find any object: Compile the script in master database and exec it with the stored proc name.

/*
Purpose- Search object across database server.
Author-Vidyadhar P.
Email-vidya_pande@.yahoo.com
Date-6th-Feb-2006
Location- Pune,India
Input Parameters
@.object_name= Name of object to be searched part of object nameto be searched
@.ExactORLikeSearch= If no parameters are passed Sp will search for exact object names.
if 'L' is passes as parameter it will to like seach.
*/

create procedure dbo.sp_find_object -- 'product_master','L'
@.object_name varchar(100),
@.ExactORLikeSearch char(1)=E --E/L
as
begin
set nocount on
declare @.databases table (colid int identity ,dbname varchar(50))
create table ##object_db (dbName varchar(100), objectName varchar(100),objectType varchar(100))
insert into @.databases (dbname) select name from sysdatabases where dbid>4 and name not in ('pubs','northwind', 'holding_tank')
declare @.max_dbs int
select @.max_dbs=max(colid) from @.databases
declare @.current_db varchar(100)

declare @.Qstr nvarchar(2000)
declare @.Qstr1 nvarchar(2000)
set @.Qstr='insert into ##object_db select '
set @.Qstr1=''

declare @.i int
set @.i=1

while @.i<=@.max_dbs
begin
select @.current_db=dbname from @.databases wherecolid=@.i
--------------------------
if @.ExactORLikeSearch='E'

set @.Qstr1=@.Qstr+''''+@.current_db+''''+',name, case xtype when '+''''+'U'+'''' +' then ' +''''+'table'+''''+' when ' +''''+'P'+''''+' then '+''''+'procedure'+''''+' when '+''''+'F'+''''+' then '+''''+'function'+''''+' when '+''''+'V'+''''+' then '+''''+'view'+''''+ ' end as ObjectType
from '+@.current_db+'.dbo.sysobjects wherename='+''''+@.object_name+''''
--------------------------
if @.ExactORLikeSearch='L'

set @.Qstr1=@.Qstr+''''+@.current_db+''''+',name, case xtype when '+''''+'U'+'''' +' then ' +''''+'table'+''''+' when ' +''''+'P'+''''+' then '+''''+'procedure'+''''+' when '+''''+'F'+''''+' then '+''''+'function'+''''+' when '+''''+'V'+''''+' then '+''''+'view'+''''+ ' end as ObjectType
from '+@.current_db+'.dbo.sysobjects where namelike'+''''+'%'+@.object_name+'%'+'''' +'and xtype in ('+''''+'U'+''''+','+''''+'P'+''''+','+''''+'F'+','+''''+''''+'V'+''''+')'
--------------------------

exec sp_executesql @.Qstr1
set @.i=@.i+1
end

select * from ##object_db
drop table ##object_db

end

sql

I can't get ANY query with Report Builder to work!

I don't want to use a stored procedure, or do I have to?
I can't get any of my querying (whether from a dropdown or not) to do
anything other than display entire table...I'm creating report from
report wizard.
Help IS appreciated.
TrintTrint,
Each report has one, or more, datasets assigned to it.
Each dataset is independant of each other (except foe the datasources of
course), and each table on any report can be assigned to one of those data
sets.
I would finish with the wizard (I NEVER use those wizards) open the report
in design view, look at the dataset associated with the report, and alter it
accordingly. The dataset GUI in VS2003 is exactly the same as Enterprise
Manager for SQL.
Any further problems, just ask,
Hope this assists,
Tony
"trint" wrote:
> I don't want to use a stored procedure, or do I have to?
> I can't get any of my querying (whether from a dropdown or not) to do
> anything other than display entire table...I'm creating report from
> report wizard.
> Help IS appreciated.
> Trint
>

I cant figure out why this update isnt working

i dont know if it is just because im tired or what. im trying to do a update one this table here is the stored procedure im using

ALTER PROCEDURE Snake.UpdateSPlits @.name nvarchar(50),@.splitnvarchar(50)ASUpdate accountsSet split_id = @.splitWhere name = @.name RETURN

Here is what im using to call it

 
Protected Sub GridView1_RowUpdating(ByVal senderAs Object,ByVal eAs System.Web.UI.WebControls.GridViewUpdateEventArgs)Handles GridView1.RowUpdatingMe.SqlDataSource1.UpdateParameters.Clear()Dim nameAs String =Me.GridView1.SelectedRow.Cells(0).TextDim SplitAs String =Me.GridView1.SelectedRow.Cells(1).TextDim pnameAs New Parameter("name", TypeCode.String, name)Me.SqlDataSource1.UpdateParameters.Add(pname)Dim psplitAs New Parameter("split", TypeCode.String, Split)Me.SqlDataSource1.UpdateParameters.Add(psplit)Me.SqlDataSource1.Update()End Sub

I keep getting one of 2 errors they are

Object reference not set to an instance of an object. or

one that says i had to many aurgements

any idea what im doing wrong?

Change these lines

Dim nameAs String =Me.GridView1.SelectedRow.Cells(0).TextDim SplitAs String =Me.GridView1.SelectedRow.Cells(1).Text

to

Dim nameAs String = e.NewValues["name"].ToString();Dim SplitAs String = e.NewValues["split"].ToString();

I guess above changes can solve your proble.

|||

In addition topooya.m's answer, try putting a break point and debug through your code.

|||

I tried that and getting this

Compiler Error Message:BC30203: Identifier expected.

Line 154:Me.SqlDataSource1.UpdateParameters.Clear()Line 155:Line 156:Dim nameAs String = e.NewValues["name"].ToString();Line 157:Dim SplitAs String = e.NewValues["split"].ToString();Line 158:

|||

Looks like u need to @.Name and @.split as the name of the parameters .. cause that whats ur sproc is expecting instead of Name and spilt

|||

YahoosSnake:

Line 156: Dim nameAs String = e.NewValues["name"].ToString();
Line 157: Dim SplitAs String = e.NewValues["split"].ToString();

You're getting the compiler error because the above code is not valid code. You are combining VB and C# style syntax. Use

Dim name As String = e.NewValues("name").ToString()
Dim Split As String = e.NewValues("split").ToString()

|||

DisturbedBuddha is right,

I used C# syntax to access NewValues. asDisturbedBuddha said you have to change NewValues["..."] to NewValues("...")

|||

How do i set up the web.confgire to run the debuger thorw vb.net 2005 i put this in the file already

<compilation debug="true" />

but i get this error

Unable to start debugging on the web server. Logon Fauilure, unknow username or bad password.

Where do i setup the username and password for my sever on the web.config file?

sql

I cannot explain the difference in execution plans.

I have a very strange problem with my SQL Server database that I cannot
explain. Suppose I have the following stored procedure:
CREATE PROCEDURE sp_TMS_PRJ_Test
@.TestplanVersionId uniqueidentifier = NULL
AS
SELECT @.TestplanVersionId = 'E340196B-D701-4814-8E75-04ABFD2777A5'
SELECT [TMS_PRJ_TestplanTestcases].[Id],[TMS_PRJ_TestResults].[Id]
FROM [TMS_PRJ_TestplanTestcases]
INNER JOIN [TMS_TCH_SpecificationTestcases] ON
[TMS_PRJ_TestplanTestcases].[SpecificationTestcase_Ref] =
[TMS_TCH_SpecificationTestcases].[Id]
INNER JOIN [TMS_TCH_SpecificationConditionSets] ON
[TMS_PRJ_TestplanTestcases].[ConditionSet_Ref] =
[TMS_TCH_SpecificationConditionSets].[Id]
INNER JOIN [TMS_PRJ_TestplanVersions] ON
[TMS_PRJ_TestplanTestcases].[TestplanVersion_Ref] =
[TMS_PRJ_TestplanVersions].[Id]
INNER JOIN [TMS_PRJ_Testplans] ON
[TMS_PRJ_TestplanVersions].[Testplan_Ref] = [TMS_PRJ_Testplans].[Id]
INNER JOIN [TMS_PRJ_TestResults] ON [TMS_PRJ_TestResults].[Project_Ref]
= [TMS_PRJ_Testplans].[Project_Ref]
INNER JOIN [TMS_SWP_TestcaseParameters] ON
[TMS_PRJ_TestResults].[Parameter_Ref] = [TMS_SWP_TestcaseParameters].[Id]
INNER JOIN [TMS_SWP_SoftwarePackageTestcases] ON
[TMS_SWP_TestcaseParameters].[SoftwarePackageTestcase_Ref] =
[TMS_SWP_SoftwarePackageTestcases].[Id]
INNER JOIN [TMS_SWP_UniformTestcases] ON
[TMS_SWP_UniformTestcases].[SoftwarePackageTestcase_Ref] =
[TMS_SWP_SoftwarePackageTestcases].[Id]
WHERE [TMS_PRJ_TestplanTestcases].[TestplanVersion_Ref] =
@.TestplanVersionId
AND [TMS_SWP_UniformTestcases].[UniformTestcase_Ref] =
[TMS_TCH_SpecificationTestcases].[UniformTestcase_Ref]
AND [TMS_PRJ_Testplans].[Active] <> 0
AND [TMS_PRJ_TestResults].[ConditionSet_Ref] =
[TMS_TCH_SpecificationConditionSets].[ConditionSet_Ref]
When I run this stored procedure then it takes over 4 minutes to complete
and takes almost 62 million read
operations to return 1295 rows. Suppose I rewrite this procedure like this:
CREATE PROCEDURE sp_TMS_PRJ_Test
AS
DECLARE @.TestplanVersionId AS uniqueidentifier
SELECT @.TestplanVersionId = 'E340196B-D701-4814-8E75-04ABFD2777A5'
SELECT [TMS_PRJ_TestplanTestcases].[Id],[TMS_PRJ_TestResults].[Id]
FROM [TMS_PRJ_TestplanTestcases]
INNER JOIN [TMS_TCH_SpecificationTestcases] ON
[TMS_PRJ_TestplanTestcases].[SpecificationTestcase_Ref] =
[TMS_TCH_SpecificationTestcases].[Id]
INNER JOIN [TMS_TCH_SpecificationConditionSets] ON
[TMS_PRJ_TestplanTestcases].[ConditionSet_Ref] =
[TMS_TCH_SpecificationConditionSets].[Id]
INNER JOIN [TMS_PRJ_TestplanVersions] ON
[TMS_PRJ_TestplanTestcases].[TestplanVersion_Ref] =
[TMS_PRJ_TestplanVersions].[Id]
INNER JOIN [TMS_PRJ_Testplans] ON
[TMS_PRJ_TestplanVersions].[Testplan_Ref] = [TMS_PRJ_Testplans].[Id]
INNER JOIN [TMS_PRJ_TestResults] ON [TMS_PRJ_TestResults].[Project_Ref]
= [TMS_PRJ_Testplans].[Project_Ref]
INNER JOIN [TMS_SWP_TestcaseParameters] ON
[TMS_PRJ_TestResults].[Parameter_Ref] = [TMS_SWP_TestcaseParameters].[Id]
INNER JOIN [TMS_SWP_SoftwarePackageTestcases] ON
[TMS_SWP_TestcaseParameters].[SoftwarePackageTestcase_Ref] =
[TMS_SWP_SoftwarePackageTestcases].[Id]
INNER JOIN [TMS_SWP_UniformTestcases] ON
[TMS_SWP_UniformTestcases].[SoftwarePackageTestcase_Ref] =
[TMS_SWP_SoftwarePackageTestcases].[Id]
WHERE [TMS_PRJ_TestplanTestcases].[TestplanVersion_Ref] =
@.TestplanVersionId
AND [TMS_SWP_UniformTestcases].[UniformTestcase_Ref] =
[TMS_TCH_SpecificationTestcases].[UniformTestcase_Ref]
AND [TMS_PRJ_Testplans].[Active] <> 0
AND [TMS_PRJ_TestResults].[ConditionSet_Ref] =
[TMS_TCH_SpecificationConditionSets].[ConditionSet_Ref]
In the second example the entire operation is finished in 1.3 seconds, so
this is much faster and takes only
5500 reads (and of course also returns 1295 rows). The execution plans
differ for both queries. As you can
see from the execution trees below, the fast version uses hash matches and
the slow version uses nested
loops. Can anyone explain the difference between the two queries?
PS: If I remove the '[TMS_PRJ_Testplans].[Active] <> 0' clause from the slow
query then it is fast again.
This is weird, because it only needs to check one record in this table.
Execution Tree (slow)
--
Nested Loops(Inner Join, OUTER
REFERENCES:([TMS_PRJ_TestplanTestcases].[SpecificationTestcase_Ref],
[TMS_SWP_UniformTestcases].[UniformTestcase_Ref]) WITH PREFETCH)
|--Nested Loops(Inner Join, OUTER
REFERENCES:([TMS_SWP_TestcaseParameters]
.[SoftwarePackageTestcase_Ref]))
| |--Nested Loops(Inner Join, OUTER
REFERENCES:([TMS_PRJ_TestResults].[Parameter_Ref]))
| | |--Nested Loops(Inner Join,
WHERE:([TMS_PRJ_Testplans].[Id]=[TMS_PRJ_TestplanVersions].[Testplan_Ref]))
| | | |--Clustered Index
S(OBJECT:([SiemensTMS].[dbo].[TMS_PRJ_TestplanVersions].[PK_TMS_PRJ_TestplanVersions]),
SEEK:([TMS_PRJ_TestplanVersions].[Id]=[@.TestplanVersionId]) ORDERED FORWARD)
| | | |-- Filter(WHERE:(Convert([TMS_PRJ_Testplans
].[Active])<>0))
| | | |--Bookmark Lookup(BOOKMARK:([Bmk1008]),
OBJECT:([SiemensTMS].[dbo].[TMS_PRJ_Testplans]))
| | | |--Nested Loops(Inner Join, OUTER
REFERENCES:([TMS_PRJ_TestResults].[Project_Ref]))
| | | |--Hash Match(Inner Join,
HASH:([TMS_TCH_SpecificationConditionSet
s].[ConditionSet_Ref])=([TMS_PRJ_Tes
tResults].[ConditionSet_Ref]),
RESIDUAL:([TMS_PRJ_TestResults]. [ConditionSet_Ref]=[TMS_TCH_Specificatio
nCon
ditionSets].[ConditionSet_Ref]))
| | | | |--Nested Loops(Inner Join, OUTER
REFERENCES:([TMS_PRJ_TestplanTestcases].[ConditionSet_Ref]))
| | | | | |--Clustered Index
S(OBJECT:([SiemensTMS].[dbo].[TMS_PRJ_TestplanTestcases].[IX_TMS_PRJ_TestplanTestcases_Tes
tplanVersionSpecificationTestcaseConditi
onSet]),
SEEK:([TMS_PRJ_TestplanTestcases]. [TestplanVersion_Ref]=[@.TestplanVersionI
d]
)
ORDERED FORWARD)
| | | | | |--Index
S(OBJECT:([SiemensTMS].[dbo].[TMS_TCH_SpecificationConditionSets].[IX_TMS_TCH_Specificatio
nConditionSets_ID]),
SEEK:([TMS_TCH_SpecificationConditionSet
s].[Id]=[TMS_PRJ_TestplanTestcases].[Con
ditionSet_Ref]) ORDERED FORWARD)
| | | | |--Clustered Index
Scan(OBJECT:([SiemensTMS].[dbo].[TMS_PRJ_TestResults].[PK_TMS_PRJ_TestResults]))
| | | |--Index
S(OBJECT:([SiemensTMS].[dbo].[TMS_PRJ_Testplans]. [IX_TMS_PRJ_Testplans_MilestoneType_Name
]
),
SEEK:([TMS_PRJ_Testplans].[Project_Ref]=[TMS_PRJ_TestResults].[Project_Ref])
ORDERED FORWARD)
| | |--Clustered Index
S(OBJECT:([SiemensTMS].[dbo].[TMS_SWP_TestcaseParameters].[PK_TMS_SWP_TestcaseParameters])
,
SEEK:([TMS_SWP_TestcaseParameters].[Id]=[TMS_PRJ_TestResults].[Parameter_Ref]) O
RDERED FORWARD)
| |--Index
S(OBJECT:([SiemensTMS].[dbo].[TMS_SWP_UniformTestcases].[IX_TMS_SWP_UniformTestcases_Softw
arePackageTestcaseUniformTestcase]),
SEEK:([TMS_SWP_UniformTestcases]. [SoftwarePackageTestcase_Ref]=[TMS_SWP_T
est
caseParameters].[SoftwarePackageTestcase_Ref]) ORDERED FORWARD)
|--Clustered Index
S(OBJECT:([SiemensTMS].[dbo].[TMS_TCH_SpecificationTestcases].[PK_TMS_TCH_SpecificationTes
tcases]),
SEEK:([TMS_TCH_SpecificationTestcases].[Id]=[TMS_PRJ_TestplanTestcases].[Specifi
cationTestcase_Ref]),
WHERE:([TMS_SWP_UniformTestcases]. [UniformTestcase_Ref]=[TMS_TCH_Specifica
ti
onTestcases].[UniformTestcase_Ref]) ORDERED FORWARD)
Execution Tree (fast)
--
Hash Match(Inner Join, HASH:([TMS_TCH_SpecificationConditionSet
s].[Id],
[TMS_TCH_SpecificationTestcases].[Id])=([TMS_PRJ_TestplanTestcases].[ConditionSe
t_Ref],
[TMS_PRJ_TestplanTestcases].[SpecificationTestcase_Ref]),
RESIDUAL:([TMS_TCH_SpecificationConditio
nSets].[Id]=[TMS_PRJ_TestplanTestcases].
[ConditionSet_Ref]
AND
[TMS_TCH_SpecificationTestcases].[Id]=[TMS_PRJ_TestplanTestcases].[Specification
Testcase_Ref]))
|--Hash Match(Inner Join,
HASH:([TMS_SWP_UniformTestcases]. [UniformTestcase_Ref])=([TMS_TCH_Specifi
cat
ionTestcases].[UniformTestcase_Ref]),
RESIDUAL:([TMS_SWP_UniformTestcases].[UniformTestcase_Ref]=[TMS_TCH_Specific
ationTestcases].[UniformTestcase_Ref]))
| |--Nested Loops(Inner Join, OUTER
REFERENCES:([TMS_SWP_TestcaseParameters]
.[SoftwarePackageTestcase_Ref]))
| | |--Nested Loops(Inner Join, OUTER
REFERENCES:([TMS_PRJ_TestResults].[Parameter_Ref]))
| | | |--Hash Match(Inner Join,
HASH:([TMS_TCH_SpecificationConditionSet
s].[ConditionSet_Ref])=([TMS_PRJ_Tes
tResults].[ConditionSet_Ref]),
RESIDUAL:([TMS_PRJ_TestResults]. [ConditionSet_Ref]=[TMS_TCH_Specificatio
nCon
ditionSets].[ConditionSet_Ref]))
| | | | |--Index
Scan(OBJECT:([SiemensTMS].[dbo].[TMS_TCH_SpecificationConditionSets].[IX_TMS_TCH_Specificatio
nConditionSets_ID]))
| | | | |--Nested Loops(Inner Join,
WHERE:([TMS_PRJ_TestResults].[Project_Ref]=[TMS_PRJ_Testplans].[Project_Ref]
))
| | | | |--Nested Loops(Inner Join, OUTER
REFERENCES:([TMS_PRJ_TestplanVersions].[Testplan_Ref]))
| | | | | |--Clustered Index
S(OBJECT:([SiemensTMS].[dbo].[TMS_PRJ_TestplanVersions].[PK_TMS_PRJ_TestplanVersions]),
SEEK:([TMS_PRJ_TestplanVersions].[Id]=[@.TestplanVersionId]) ORDERED FORWARD)
| | | | | |--Clustered Index
S(OBJECT:([SiemensTMS].[dbo].[TMS_PRJ_Testplans].[PK_TMS_PRJ_Testplans]),
SEEK:([TMS_PRJ_Testplans].[Id]=[TMS_PRJ_TestplanVersions].[Testplan_Ref]),
WHERE:(Convert([TMS_PRJ_Testplans].[Active])<>0) ORDERED FORWARD)
| | | | |--Clustered Index
Scan(OBJECT:([SiemensTMS].[dbo].[TMS_PRJ_TestResults].[PK_TMS_PRJ_TestResults]))
| | | |--Clustered Index
S(OBJECT:([SiemensTMS].[dbo].[TMS_SWP_TestcaseParameters].[PK_TMS_SWP_TestcaseParameters])
,
SEEK:([TMS_SWP_TestcaseParameters].[Id]=[TMS_PRJ_TestResults].[Parameter_Ref]) O
RDERED FORWARD)
| | |--Index
S(OBJECT:([SiemensTMS].[dbo].[TMS_SWP_UniformTestcases].[IX_TMS_SWP_UniformTestcases_Softw
arePackageTestcaseUniformTestcase]),
SEEK:([TMS_SWP_UniformTestcases]. [SoftwarePackageTestcase_Ref]=[TMS_SWP_T
est
caseParameters].[SoftwarePackageTestcase_Ref]) ORDERED FORWARD)
| |--Index
Scan(OBJECT:([SiemensTMS].[dbo].[TMS_TCH_SpecificationTestcases].[IX_TMS_TCH_SpecificationTes
tcases_SpecificationVersionUniformTestca
se]))
|--Clustered Index
S(OBJECT:([SiemensTMS].[dbo].[TMS_PRJ_TestplanTestcases].[IX_TMS_PRJ_TestplanTestcases_Tes
tplanVersionSpecificationTestcaseConditi
onSet]),
SEEK:([TMS_PRJ_TestplanTestcases]. [TestplanVersion_Ref]=[@.TestplanVersionI
d]
)
ORDERED FORWARD)Google for "parameter sniffing".
Also consider using indexed views when low-selectivity status columns exist
in the tables. If [TMS_PRJ_Testplans].[Active] is such a column, then rather
than indexing the column, consider using an indexed view for each status
value.
Although in your case a simple "[TMS_PRJ_Testplans].[Active] = 1" (if value
1 is appropriate) might help.
ML
http://milambda.blogspot.com/|||Ramon de Klein (RamondeKlein@.discussions.microsoft.com) writes:
> I have a very strange problem with my SQL Server database that I cannot
> explain. Suppose I have the following stored procedure:
> CREATE PROCEDURE sp_TMS_PRJ_Test
> @.TestplanVersionId uniqueidentifier = NULL
> AS
> SELECT @.TestplanVersionId = 'E340196B-D701-4814-8E75-04ABFD2777A5'
>...
> CREATE PROCEDURE sp_TMS_PRJ_Test
> AS
> DECLARE @.TestplanVersionId AS uniqueidentifier
> SELECT @.TestplanVersionId = 'E340196B-D701-4814-8E75-04ABFD2777A5'
>...
> In the second example the entire operation is finished in 1.3 seconds, so
> this is much faster and takes only
> 5500 reads (and of course also returns 1295 rows). The execution plans
> differ for both queries. As you can
> see from the execution trees below, the fast version uses hash matches and
> the slow version uses nested
> loops. Can anyone explain the difference between the two queries?
This is something that baffles about anyone at some point in his SQL
Server career, but once you know how SQL Server builds query plans, it is
less mysterious.
Say that would hard-code the GUID into the query. In this case, SQL
Server knows exactly what you are after, and can look at the statistics
to be able to make an estimate of how many rows it will hit.
On the other hand, when you use a variable, SQL Server does not know
which value the variable will have at run-time, so it makes a standard
assumption.
For procedure parameters there is yet a choice. When you call the
procedure the first time, SQL Server uses the actual parameter value
and builds the plan according to that. Tbis means that if the first
call is for an atypical value, or, as in this, you assign a default
value, you're taking SQL Server to the cleaners.
For procedures where you need to have a parameter with a NULL default
value which in reality means something else, for instance "today" for
a date parameter, you should always copy the parameter into a local
variable, so that the optimizer does not act on incorrect information.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||When I rewrite the stored procedure, so it doesn't the parameter at all then
it is still slow. In this case I hardcoded the GUID
'E340196B-D701-4814-8E75-04ABFD2777A5' into the query, but it is still slow.
When I use an intermediate variable to push the GUID into the SELECT query
then it is fast again, so this query is fast (1 second)
SELECT @.Parameter = 'E340196B-D701-4814-8E75-04ABFD2777A5'
SELECT X,Y
FROM ...
INNER JOIN ...
WHERE Clause = @.Parameter
And this one is slow (over 4 minutes):
SELECT X,Y
FROM ...
INNER JOIN ...
WHERE Clause = 'E340196B-D701-4814-8E75-04ABFD2777A5
It doesn't have anything to do with stored procedures, because when I run it
manually in SQL Query Analyser then the results are the same. I think I have
hit a bug in SQL Server 2000.
--
Greetings,
Ramon de Klein|||Ramon de Klein (RamondeKlein@.discussions.microsoft.com) writes:
> When I rewrite the stored procedure, so it doesn't the parameter at all
> then it is still slow. In this case I hardcoded the GUID
> 'E340196B-D701-4814-8E75-04ABFD2777A5' into the query, but it is still
> slow. When I use an intermediate variable to push the GUID into the
> SELECT query then it is fast again, so this query is fast (1 second)
> SELECT @.Parameter = 'E340196B-D701-4814-8E75-04ABFD2777A5'
> SELECT X,Y
> FROM ...
> INNER JOIN ...
> WHERE Clause = @.Parameter
> And this one is slow (over 4 minutes):
> SELECT X,Y
> FROM ...
> INNER JOIN ...
> WHERE Clause = 'E340196B-D701-4814-8E75-04ABFD2777A5
> It doesn't have anything to do with stored procedures, because when I
> run it manually in SQL Query Analyser then the results are the same. I
> think I have hit a bug in SQL Server 2000.
Maybe. Or maybe not.
The likely problem here is that the statistics indicate that the
query will hit more rows for this GUID that it actually does. First
try UPDATE STATISTICS WITH FULLSCAN for this table. If you still get
the same result, post the output from DBCC SHOW_STATISTICS for this
index.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

I cannot debug inside VS 2005 Developer Edition

Two questions...

1. I saw that you must have the Professional Edition and above to debug stored procedures. Is this available in the Developer Edition.

2. If #1 is okay, why do i not have the menu item, "Step Into Procedure" or whatever it's called?

Thank you,

Brian

What i installed was SQL Server 2005 Developer Edition, it came with a MSDN Professional Subscription. So i have the Professional Edition but i do not have the "Step Into Procedure" menu item when right-clicking a stored procedure within the Server Explorer? Maybe noteworthy here is that i did not install any of the languages, does that make a difference?

When i view debug options for Just-In-Time, i see a warning stating, "Another debugger has registered itself as the Just-In-Time debugger. To repair, enable Just-In-Time debugging or run Visual Studio repair." Could this be the reason for not having the ability to debug stored procedures?

Thanks,

Brian

|||

That means a problem with the current that doesn't have the Administrator privileges, check that out.

http://msdn2.microsoft.com/en-us/library/5hs4b7a6(VS.80).aspx fyi.

i am using a stored procedure for paging....

However it is not saving in visual srudio 2005. it is saying 'ambiguous column name ID' does anyone know why?

CREATE PROCEDUREPagedResults_New

(

@.startRowIndexint,

@.maximumRowsint

)

AS

--Create a table variable

DECLARE@.TempItemsTABLE

(

IDint IDENTITY,

ShortListIdint

)

-- Insert the rows from tblItems into the temp. table

INSERT INTO@.TempItems (ShortListId)

SELECTId

FROMShortlist S

WHEREPublish ='True'order by date DESC

-- Now, return the set of paged records

SELECTS.*

FROM@.TempItems t

INNER JOINShortList SON

t.ShortListId = S.Id

WHEREIDBETWEEN@.startRowIndexAND(@.startRowIndex + @.maximumRows) - 1

Change:

WHEREIDBETWEEN@.startRowIndexAND(@.startRowIndex + @.maximumRows) - 1

To:

WHERET.IDBETWEEN@.startRowIndexAND(@.startRowIndex + @.maximumRows) - 1

|||

Thanks. do you know how i would implement paging with this?

|||

Thanks. do you know how i would implement paging with this?

It should be very easy to impliment this sp to your application using visual studio. For example ,you can use sqlcommand. Your stored procedure takes two parameters @.startRowIndex and@.maximumRows . First build a sqlcommand and set commond type to stored procedure and command text to your procedure name. Add two parameters(corresponding to @.startRowIndex and@.maximumRows) to your sqlcommand.parameters and call ExecuteReader(). This way you will be able to get the table returned by your stored procedure.

I suggest you reading this:http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.aspx

Hope my suggestion helps

I am Having an Odd Problem with Exporting to Excel.

The background:
Before I get into my error let me give you some background in what I am
required to do. All the text field parameters must be stored in the
database. What I mean by this is every text box's value, background
colour, font(weight, size and etc), TextAlign and TextDecoration
information is stored in the database. This includes the fields for
headers and footers. To get these parameters to work properly in
headers and footers I have set up report parameters that get the data
from the database and then feed it to the text fields inside the header
or footer.
The problem:
I am currently getting the following error while trying to export to
Excel:
--
Microsoft Internet Explorer
--
Internet Explorer cannot download Format=EXCEL from SERVERNAME.
Internet Explorer was not able to open this Internet site. The
requested site is either unavailable or cannot be found. Please try
again later.
--
OK
--
After some investigation I discovered that the appearance property
"TextAlign" for all footers is causing this error. So, if I put in
"Left", "Right" or "Center" text directly into the
"TextAlign" field for all the footer text boxes the report exports
into Excel properly. But if I use the property from the database it
will not load into Excel. I've checked to make sure the database
field is giving a valid value. In fact, I've tried all 3 values and
had no luck. I've also tried other TextAlign parameters from
different text fields on the report and received the same error as
stated above.
What I find odd about this is the fact it works fine for all the other
properties but TextAlign. Could this be an obscure bug?
Another thing to note is in Excel I can see the header all the time;
but, I can only see the footer when I am in print preview. Is there a
way to have the footer set up just like the header; so, it is just on
bottom of the page instead of being a "true" footer. It wouldn't
be a pretty way to solve my issue but I think it would at least allow
my clients to use Excel export for now.Oh I am using SQL Server 2000 Reporing Services with SP2.|||Hello!
I have encountered the same problem with export to Excel ("Internet Explorer
cannot download Format=EXCEL from SERVERNAME."). In my case the solution was
simple. I've had just to make the name of the report shorter. You can simply
upload your rdl under another (shorter) name to report manager and it will
possibly start working.
"Jeremy Balliston" wrote:
> Oh I am using SQL Server 2000 Reporing Services with SP2.
>|||Any ideas?

I am getting an error in creating a simple store procedure!

Hi ,
Here is my stored proce and I am not sure why its complaining about
the below error
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[USP_InsertUserInfo]
@.aspnet_userid uniqueidentifier(50)
,@.DealershipID int
,@.LastName varchar(50)
,@.FirstName varchar(50)
,@.AddressLine1 varchar(50)
,@.AddressLine2 varchar(50)
,@.City varchar(50)
,@.State varchar(50)
,@.Zip varchar(50)
,@.Country varchar(50)
,@.HomePhoneNumber varchar(50)
,@.MobileNumber varchar(50)
,@.OfficePhoneNumbre varchar(50)
,@.OfficePhoneExtension varchar(5)
,@.EmailAddress varchar(50)
,@.PreferredCommunication varchar(50)
,@.InsertedBy uniqueidentifier
,@.InsertedDate datetime
,@.ModifiedBy uniqueidentifier
,@.ModifiedDate datetime
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO [GroundingDemo].[dbo].[Users]
(aspnet_userid
,DealershipID
,LastName
,FirstName
,AddressLine1
,AddressLine2
,City
,State
,Zip
,Country
,HomePhoneNumber
,MobileNumber
,OfficePhoneNumbre
,OfficePhoneExtension
,EmailAddress
,PreferredCommunication
,InsertedBy
,InsertedDate
,ModifiedBy
,ModifiedDate)
VALUES
(aspnet_userid
,DealershipID
,LastName
,FirstName
,AddressLine1
,AddressLine2
,City
,State
,Zip
,Country
,HomePhoneNumber
,MobileNumber
,OfficePhoneNumbre
,OfficePhoneExtension
,EmailAddress
,PreferredCommunication
,InsertedBy
,InsertedDate
,ModifiedBy
,ModifiedDate)
END
GO
*************************************her
e is the error
***********************************
Msg 128, Level 15, State 1, Procedure USP_InsertUserInfo, Line 63
The name "aspnet_userid" is not permitted in this context. Valid
expressions are constants, constant expressions, and (in some contexts)
variables. Column names are not permitted.
****************************************
***********************
if I take off the aspnet_userid column from the stored proce and also
the table and re execute it then complains about DealerShipID with the
same kind of error.
Am I missing some thing here?
Thanks
-Lyou are missing a comma after @.aspnet_userid uniqueidentifier(50)
and also
VALUES
(aspnet_userid
,DealershipID
has to be
VALUES
(@.aspnet_userid, --comma here also
,@.DealershipID etc etc etc
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Hi,
You are using the procedure's parameters in the values list, but forgot to
prefix them with the "@.". Like this:
VALUES
(@.aspnet_userid
,@.DealershipID
etc.
hth,
Dean
"Learner" <pradev@.gmail.com> wrote in message
news:1145648788.592029.198590@.g10g2000cwb.googlegroups.com...
> Hi ,
> Here is my stored proce and I am not sure why its complaining about
> the below error
>
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> CREATE PROCEDURE [dbo].[USP_InsertUserInfo]
> @.aspnet_userid uniqueidentifier(50)
> ,@.DealershipID int
> ,@.LastName varchar(50)
> ,@.FirstName varchar(50)
> ,@.AddressLine1 varchar(50)
> ,@.AddressLine2 varchar(50)
> ,@.City varchar(50)
> ,@.State varchar(50)
> ,@.Zip varchar(50)
> ,@.Country varchar(50)
> ,@.HomePhoneNumber varchar(50)
> ,@.MobileNumber varchar(50)
> ,@.OfficePhoneNumbre varchar(50)
> ,@.OfficePhoneExtension varchar(5)
> ,@.EmailAddress varchar(50)
> ,@.PreferredCommunication varchar(50)
> ,@.InsertedBy uniqueidentifier
> ,@.InsertedDate datetime
> ,@.ModifiedBy uniqueidentifier
> ,@.ModifiedDate datetime
> AS
> BEGIN
> SET NOCOUNT ON;
> INSERT INTO [GroundingDemo].[dbo].[Users]
> (aspnet_userid
> ,DealershipID
> ,LastName
> ,FirstName
> ,AddressLine1
> ,AddressLine2
> ,City
> ,State
> ,Zip
> ,Country
> ,HomePhoneNumber
> ,MobileNumber
> ,OfficePhoneNumbre
> ,OfficePhoneExtension
> ,EmailAddress
> ,PreferredCommunication
> ,InsertedBy
> ,InsertedDate
> ,ModifiedBy
> ,ModifiedDate)
> VALUES
> (aspnet_userid
> ,DealershipID
> ,LastName
> ,FirstName
> ,AddressLine1
> ,AddressLine2
> ,City
> ,State
> ,Zip
> ,Country
> ,HomePhoneNumber
> ,MobileNumber
> ,OfficePhoneNumbre
> ,OfficePhoneExtension
> ,EmailAddress
> ,PreferredCommunication
> ,InsertedBy
> ,InsertedDate
> ,ModifiedBy
> ,ModifiedDate)
> END
> GO
> *************************************her
e is the error
> ***********************************
> Msg 128, Level 15, State 1, Procedure USP_InsertUserInfo, Line 63
> The name "aspnet_userid" is not permitted in this context. Valid
> expressions are constants, constant expressions, and (in some contexts)
> variables. Column names are not permitted.
> ****************************************
***********************
> if I take off the aspnet_userid column from the stored proce and also
> the table and re execute it then complains about DealerShipID with the
> same kind of error.
>
> Am I missing some thing here?
> Thanks
> -L
>|||"SQL" <denis.gobo@.gmail.com> wrote in message
news:1145649123.181217.315180@.u72g2000cwu.googlegroups.com...
> you are missing a comma after @.aspnet_userid uniqueidentifier(50)
Actually, he's not :) The comma *is* there, but not where one would normally
expect it to be.
Dean

> and also
> VALUES
> (aspnet_userid
> ,DealershipID
> has to be
> VALUES
> (@.aspnet_userid, --comma here also
> ,@.DealershipID etc etc etc
>
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>|||I am not sure if I am understanding it right... I have another stored
proc that works and it doesn't have commas after it . I mean I am
trying to do the same thing with my new one ...
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[SP_InsertNewVehicleFeed]
@.VIN char(17),
@.AccountNum varchar(14),
@.CustomerName varchar(50),
@.CoByerName varchar(50),
@.Owner varchar(15),
@.SubOwner varchar(15),
@.IssuingFSO varchar(10),
@.OwningFSO varchar(10),
@.DealerShipID int,
@.ReturnDate smallDateTime,
@.Odometer varchar(6),
@.Year int,
@.MakeID int,
@.ModelID int,
@.SubModelID int
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO VehicleInformation
(VIN,
AccountNum,
CustomerName,
CoBuyerName,
Owner,
SubOwner,
IssuingFSO,
OwningFSO,
DealerShipID,
ReturnDate,
Odometer,
Year,
MakeID,
ModelID,
SubModelID)
VALUES
(@.VIN,
@.AccountNum,
@.CustomerName,
@.CoByerName,
@.Owner,
@.SubOwner,
@.IssuingFSO,
@.OwningFSO,
@.DealerShipID,
@.ReturnDate,
@.Odometer,
@.Year,
@.MakeID,
@.ModelID,
@.SubModelID)
END
IF (@.@.ERROR > 0)
Begin
Select 'Failure'
End
Else
Begin
Select 'Success'
End|||He Dean,
yes that was silly :) jezz I was so stupid abou it..
THANK YOU
-L|||yes thats right. I just put infront of the each parameter not after :)
I don't know when I started it but just doing it.

Hypothetically speaking...

Let's pretend I'm running a big fat batch in a giant stored procedure that's
chewing on about half a million rows, and meanwhile, some clown in MIS
decides it would be a neat idea to reboot the big momma SQL Server in the
middle of the work day.
What might happen? Think the MSSQLServer service completes the batch before
stopping, or roles it back so I get to run it all over again? [grumble]
Peace & happy computing,
Mike Labosh, MCSD
"Escriba coda ergo sum." -- vbSenseiIf you have your batch inside a transaction you are correct. If not the
only thing that will roll back will be any statement that you are in the
middle of.
Then, get the person who rebooted the machine and take him on a little
ride...
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:OjEu88IYFHA.2128@.TK2MSFTNGP15.phx.gbl...
> Let's pretend I'm running a big fat batch in a giant stored procedure
> that's chewing on about half a million rows, and meanwhile, some clown in
> MIS decides it would be a neat idea to reboot the big momma SQL Server in
> the middle of the work day.
> What might happen? Think the MSSQLServer service completes the batch
> before stopping, or roles it back so I get to run it all over again?
> [grumble]
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "Escriba coda ergo sum." -- vbSensei
>|||What a hypothetical headache :( If the batch is a single transaction, it
will all roll back. If it's a bunch of individual transactions, only the
uncommitted transactions will be rolled back - basically the last one it was
executing when your buddy pulled the plug.
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:OjEu88IYFHA.2128@.TK2MSFTNGP15.phx.gbl...
> Let's pretend I'm running a big fat batch in a giant stored procedure
> that's chewing on about half a million rows, and meanwhile, some clown in
> MIS decides it would be a neat idea to reboot the big momma SQL Server in
> the middle of the work day.
> What might happen? Think the MSSQLServer service completes the batch
> before stopping, or roles it back so I get to run it all over again?
> [grumble]
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "Escriba coda ergo sum." -- vbSensei
>|||> What a hypothetical headache :( If the batch is a single transaction, it
> will all roll back. If it's a bunch of individual transactions, only the
> uncommitted transactions will be rolled back - basically the last one it
> was executing when your buddy pulled the plug.
They also recently got a new SQL Server so that we now finally have a
production server and a development server.
The original one is now the dev server. But now that they've moved the
databases over to the new server, they (now that it's back up) deleted the
huge importing database that my group uses off of the dev server, and now
nobody can get in because that database is set as the "default one" on all
our logins. And the two guys that know the sa password are out of the
office. I wanna rip somebody's "hypothetical" eyes out.
So now I can't even get in and look at the hypothetical mess that got made
by my long list of little transactions.
"MIS" must mean "Masters In Silliness". I swear, working here is like
living inside a Salvador Dali painting.
Peace & happy computing,
Mike Labosh, MCSD
"Escriba coda ergo sum." -- vbSensei|||Mike,
Do you have access to another db in the server?, if so, then use osql
utility, connect to the db you have access to and execute sp_defaultdb to
change your default db to one that you have access to.
Example:
c:\> osql.exe -S my_server -d db_i_have_access_to -E -Q "exec sp_defaultdb
'my_login', 'new_default_db'"
AMB
"Mike Labosh" wrote:

> They also recently got a new SQL Server so that we now finally have a
> production server and a development server.
> The original one is now the dev server. But now that they've moved the
> databases over to the new server, they (now that it's back up) deleted the
> huge importing database that my group uses off of the dev server, and now
> nobody can get in because that database is set as the "default one" on all
> our logins. And the two guys that know the sa password are out of the
> office. I wanna rip somebody's "hypothetical" eyes out.
> So now I can't even get in and look at the hypothetical mess that got made
> by my long list of little transactions.
> "MIS" must mean "Masters In Silliness". I swear, working here is like
> living inside a Salvador Dali painting.
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "Escriba coda ergo sum." -- vbSensei
>
>|||To change the default database for a login, when that database was
dropped and when "sa"-s are out of the office, use ISQL to connect to
SQL Server, as suggested by the following KB articles:
http://support.microsoft.com/defaul...b;en-us;Q196076
http://support.microsoft.com/defaul...b;en-us;Q307864
Razvan|||> Do you have access to another db in the server?, if so, then use osql
> utility, connect to the db you have access to and execute sp_defaultdb to
> change your default db to one that you have access to.
OH WOW! I would *never* have thought of that
--
Peace & happy computing,
Mike Labosh, MCSD
"Escriba coda ergo sum." -- vbSensei|||How transactions are rolled back depends on the recovery model setting on
the database. Also, did he stop the SQL Server service, click Windows
re-start, or punch the power button?
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:OjEu88IYFHA.2128@.TK2MSFTNGP15.phx.gbl...
> Let's pretend I'm running a big fat batch in a giant stored procedure
that's
> chewing on about half a million rows, and meanwhile, some clown in MIS
> decides it would be a neat idea to reboot the big momma SQL Server in the
> middle of the work day.
> What might happen? Think the MSSQLServer service completes the batch
before
> stopping, or roles it back so I get to run it all over again? [grumble]
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "Escriba coda ergo sum." -- vbSensei
>|||> How transactions are rolled back depends on the recovery model setting on
> the database.
No they arent. The recovery model determines how long committed transactions
are stored in the log, not uncommitted ones. And once they are committed
they are "more or less" useless to us (except to apply to a backup or poke
around in with a log explorer.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"JT" <someone@.microsoft.com> wrote in message
news:OOBRwgKYFHA.3584@.TK2MSFTNGP12.phx.gbl...
> How transactions are rolled back depends on the recovery model setting on
> the database. Also, did he stop the SQL Server service, click Windows
> re-start, or punch the power button?
> "Mike Labosh" <mlabosh@.hotmail.com> wrote in message
> news:OjEu88IYFHA.2128@.TK2MSFTNGP15.phx.gbl...
> that's
> before
>|||Thank you all.
The "suit" that has the sa password has fixed our logins, app-dev has
continued (amidst a rather surreal delay) and I have finished mopping up the
mess that my batch became.
In the morning I will soothe the users with a bunch of "It's ok, we just had
a glitch" foolishness.
I cannot believe I even work here. Perhaps I should change my sig to "Life
is like a Salvador Dali painting".
Peace & happy computing,
Mike Labosh, MCSD
"(bb)|(^b){2}" -- William Shakespeare

Hyperlink to a new window

I have two hyperlink-related questions:
1. How can I generate a URL from within a query or stored procedure such that it appears as a hyperlink within the report? On my first attempt, the text of the link shows but it does not appear as a "live" link within the generated report.
2. Once I've achieved #1, how can I get that link appear in a new browser window rather than the one in which the report is currently displayed?
Thanks in advance for the advice.#1:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RShowto/htm/hrs_designer_v1_6mwb.asp
#2: Take a look at _LinkTarget device info setting:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/rsp_prog_soapapi_dev_3i49.asp
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"virtualfergy" <virtualfergy@.discussions.microsoft.com> wrote in message
news:11FD9027-8D50-4DD6-83F9-D9091D3F099A@.microsoft.com...
> I have two hyperlink-related questions:
> 1. How can I generate a URL from within a query or stored procedure such
that it appears as a hyperlink within the report? On my first attempt, the
text of the link shows but it does not appear as a "live" link within the
generated report.
> 2. Once I've achieved #1, how can I get that link appear in a new browser
window rather than the one in which the report is currently displayed?
> Thanks in advance for the advice.
>|||Your links proved unhelpful for the following reasons:
#1 - This link talks about how to add a hyperlink to a report manually. I want to return a URL from a query in a field and have the link appear and be active without having to manually set it up.
#2 - This link indicates how to use the LinkTarget parameter when executing a report to set the device to which the links in the report are directed. But what I'm attempting to do is to have certain links in a report shown in a new window. These links are not drillthrough reports, they're just links to other related subsystems (in this case a problem tracking system).
Example:
If my report gets information from the following query...
select url from urlsource
and urlsource contains
* http://www.site1.com/
* http://www.site2.com/
How could I get the site1 url to show in the same window as the report and site2 to popup a new window? And if the text of these URLs are returned as fields, can I make them active links automatically or do I have to set an action on the field?
"Ravi Mumulla (Microsoft)" wrote:
> #1:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RShowto/htm/hrs_designer_v1_6mwb.asp
> #2: Take a look at _LinkTarget device info setting:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/rsp_prog_soapapi_dev_3i49.asp
> --
> Ravi Mumulla (Microsoft)
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "virtualfergy" <virtualfergy@.discussions.microsoft.com> wrote in message
> news:11FD9027-8D50-4DD6-83F9-D9091D3F099A@.microsoft.com...
> > I have two hyperlink-related questions:
> >
> > 1. How can I generate a URL from within a query or stored procedure such
> that it appears as a hyperlink within the report? On my first attempt, the
> text of the link shows but it does not appear as a "live" link within the
> generated report.
> >
> > 2. Once I've achieved #1, how can I get that link appear in a new browser
> window rather than the one in which the report is currently displayed?
> >
> > Thanks in advance for the advice.
> >
>
>|||On the question of treating your field data as a URL, there is the URLEncode command.
=Web.HttpUtility.UrlEncode(Fields!Serial.Value)
(http://www.microsoft.com/sql/community/newsgroups/dgbrowser/en-us/default.mspx?query=URLEncode&dg=microsoft.public.sqlserver.reportingsvcs&cat=&lang=en&cr=US&pt=&catlist=6C839803-6334-48D8-A2C3-72A1BEF0053D&dglist=&ptlist=&exp=
)
There is another thread that can answer some potential issues you may have with implementing this.
(http://www.microsoft.com/sql/community/newsgroups/dgbrowser/en-us/default.mspx?query=URLEncode&dg=microsoft.public.sqlserver.reportingsvcs&cat=&lang=en&cr=US&pt=&catlist=6C839803-6334-48D8-A2C3-72A1BEF0053D&dglist=&ptlist=&exp=)
On your second question, about being able to open a link in a new window...if you are using Report Manager for your distribution platform, then there is NOT a current way to do so. Even if you are using the HTML Viewer and sending in rc: comands, it is an all or nothing deal - either all your links open in a new window or none of them do. I have sugested a few features that I hope MS will implement into one of their future SPs, but until that time, your options are few.
"virtualfergy" wrote:
> Your links proved unhelpful for the following reasons:
> #1 - This link talks about how to add a hyperlink to a report manually. I want to return a URL from a query in a field and have the link appear and be active without having to manually set it up.
> #2 - This link indicates how to use the LinkTarget parameter when executing a report to set the device to which the links in the report are directed. But what I'm attempting to do is to have certain links in a report shown in a new window. These links are not drillthrough reports, they're just links to other related subsystems (in this case a problem tracking system).
> Example:
> If my report gets information from the following query...
> select url from urlsource
> and urlsource contains
> * http://www.site1.com/
> * http://www.site2.com/
> How could I get the site1 url to show in the same window as the report and site2 to popup a new window? And if the text of these URLs are returned as fields, can I make them active links automatically or do I have to set an action on the field?
> "Ravi Mumulla (Microsoft)" wrote:
> > #1:
> > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RShowto/htm/hrs_designer_v1_6mwb.asp
> > #2: Take a look at _LinkTarget device info setting:
> > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/rsp_prog_soapapi_dev_3i49.asp
> >
> > --
> > Ravi Mumulla (Microsoft)
> > SQL Server Reporting Services
> >
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> > "virtualfergy" <virtualfergy@.discussions.microsoft.com> wrote in message
> > news:11FD9027-8D50-4DD6-83F9-D9091D3F099A@.microsoft.com...
> > > I have two hyperlink-related questions:
> > >
> > > 1. How can I generate a URL from within a query or stored procedure such
> > that it appears as a hyperlink within the report? On my first attempt, the
> > text of the link shows but it does not appear as a "live" link within the
> > generated report.
> > >
> > > 2. Once I've achieved #1, how can I get that link appear in a new browser
> > window rather than the one in which the report is currently displayed?
> > >
> > > Thanks in advance for the advice.
> > >
> >
> >
> >|||Look at my post from 8/26:
I think I have a solution. If you want a particular link to be opened in a
new window, enter this into the "Jump to URL" field:
javascript:if(window.open(yourPage.aspx','RsWindow','width=400,height=500,location=0,menubar=0,status=0,toolbar=0,scrollbars=1',true)){}
"Rob 'Spike' Stevens" wrote:
> On the question of treating your field data as a URL, there is the URLEncode command.
> =Web.HttpUtility.UrlEncode(Fields!Serial.Value)
> (http://www.microsoft.com/sql/community/newsgroups/dgbrowser/en-us/default.mspx?query=URLEncode&dg=microsoft.public.sqlserver.reportingsvcs&cat=&lang=en&cr=US&pt=&catlist=6C839803-6334-48D8-A2C3-72A1BEF0053D&dglist=&ptlist=&exp=
> )
> There is another thread that can answer some potential issues you may have with implementing this.
> (http://www.microsoft.com/sql/community/newsgroups/dgbrowser/en-us/default.mspx?query=URLEncode&dg=microsoft.public.sqlserver.reportingsvcs&cat=&lang=en&cr=US&pt=&catlist=6C839803-6334-48D8-A2C3-72A1BEF0053D&dglist=&ptlist=&exp=)
> On your second question, about being able to open a link in a new window...if you are using Report Manager for your distribution platform, then there is NOT a current way to do so. Even if you are using the HTML Viewer and sending in rc: comands, it is an all or nothing deal - either all your links open in a new window or none of them do. I have sugested a few features that I hope MS will implement into one of their future SPs, but until that time, your options are few.
> "virtualfergy" wrote:
> > Your links proved unhelpful for the following reasons:
> >
> > #1 - This link talks about how to add a hyperlink to a report manually. I want to return a URL from a query in a field and have the link appear and be active without having to manually set it up.
> >
> > #2 - This link indicates how to use the LinkTarget parameter when executing a report to set the device to which the links in the report are directed. But what I'm attempting to do is to have certain links in a report shown in a new window. These links are not drillthrough reports, they're just links to other related subsystems (in this case a problem tracking system).
> >
> > Example:
> >
> > If my report gets information from the following query...
> >
> > select url from urlsource
> >
> > and urlsource contains
> >
> > * http://www.site1.com/
> > * http://www.site2.com/
> >
> > How could I get the site1 url to show in the same window as the report and site2 to popup a new window? And if the text of these URLs are returned as fields, can I make them active links automatically or do I have to set an action on the field?
> >
> > "Ravi Mumulla (Microsoft)" wrote:
> >
> > > #1:
> > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RShowto/htm/hrs_designer_v1_6mwb.asp
> > > #2: Take a look at _LinkTarget device info setting:
> > > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/rsp_prog_soapapi_dev_3i49.asp
> > >
> > > --
> > > Ravi Mumulla (Microsoft)
> > > SQL Server Reporting Services
> > >
> > > This posting is provided "AS IS" with no warranties, and confers no rights.
> > > "virtualfergy" <virtualfergy@.discussions.microsoft.com> wrote in message
> > > news:11FD9027-8D50-4DD6-83F9-D9091D3F099A@.microsoft.com...
> > > > I have two hyperlink-related questions:
> > > >
> > > > 1. How can I generate a URL from within a query or stored procedure such
> > > that it appears as a hyperlink within the report? On my first attempt, the
> > > text of the link shows but it does not appear as a "live" link within the
> > > generated report.
> > > >
> > > > 2. Once I've achieved #1, how can I get that link appear in a new browser
> > > window rather than the one in which the report is currently displayed?
> > > >
> > > > Thanks in advance for the advice.
> > > >
> > >
> > >
> > >