How to change the caption of parameter at run time

Hi,
I would like to change the caption of parameter based upon the other
parameter value in RS 2000 / 2005.
Thankstry using conditional logic
eg
=iif(Parameters!FieldName.Value = testvalue, value if true , value if
false)|||oops posted to wrong thread - sorry
andygadget wrote:
> try using conditional logic
> eg
> =iif(Parameters!FieldName.Value = testvalue, value if true , value if
> false)

How to change Crystal Reprots database at run time from ASP classic code

Dear All,

I'm new to Crystal Reprots and ASP, and need advises regarding to my problem below

I have two database in same SQL 2005 SERVER for TEST and LIVE Environtment, and I've created more than 100 reports with Crystal Reports 11 and call it from ASP classic page.

The problem is how can I change a database from TEST01 to LIVE01 at the run time from ASP code (including the sub reports) as I already using TEST01 database with ADO Connection on Crystal Reports and I do not want to set a new database location inside crystal for each reports

Thanks and wait for your reply soon.

Below is my code, which has no effect to crystal reports although I've change the database from TEST01 to LIVE01:

<%

Dim oADOConnection, oRptTable, oADORecordset, sql
Dim struser, strpwd, strdriver, dblocation, dbname, strConnect

struser = "sa"
strpwd = "sa"
strdriver = "{SQL SERVER}"
dblocation = "SQL200501"
dbname = "LIVE01" ' Changed from TEST01 to LIVE01

strConnect = "User Id=" & strUser & ";"
strConnect = strConnect & "PWD=" & strPwd & ";"
strConnect = strConnect & "DRIVER=" & StrDriver & ";"
strConnect = strConnect & "SERVER=" & DBLocation & ";"
strConnect = strConnect & "DATABASE=" & dbName

sql="Select * from Employee"

Set session("oApp") = Server.CreateObject("CrystalRuntime.Application.11")
Set session("oRpt") = session("oApp").OpenReport("C:\REPORTS\RPT01.RPT", 1) 'USING TEST01 DATABASE

session("oRpt").MorePrintEngineErrorMessages = False
session("oRpt").EnableParameterPrompting = False
session("oRpt").DiscardSavedData

Set oADOConnection = Server.CreateObject("ADODB.Connection")
oADOConnection.Open (strConnect)
Set oADORecordset = Server.CreateObject("ADODB.Recordset")

Set oRptTable = session("oRpt").Database.Tables.Item(1)
oRptTable.SetDataSource oADORecordset, 3

session("oRpt").SQLQueryString = CStr(sql)

session("oRpt").ReadRecords

%>See if you find answer here
http://support.businessobjects.com

How to change ConnectionString programmaticaly for report

Hi!
Is there any way to change the connecting string of a report data source
programmatically at run time. Actually my problem is that I have an ASP.NET
application and connection string is stored in Web.Config file. I have
multiple copies of databases hosted on different SQL Server and I update my
web.config connection string to switch between these databases. I just want
to use this connection string for my reports also. I am using SQL Server
authentication and user name and passwords are different for different
servers.
Please help me as this is becoming a show stopper for my application.
Regards,
NamwarOn Jun 8, 3:33 pm, "Namwar Rizvi" <nam...@.hotmail.com> wrote:
> Hi!
> Is there any way to change the connecting string of a report data source
> programmatically at run time. Actually my problem is that I have an ASP.NET
> application and connection string is stored in Web.Config file. I have
> multiple copies of databases hosted on different SQL Server and I update my
> web.config connection string to switch between these databases. I just want
> to use this connection string for my reports also. I am using SQL Server
> authentication and user name and passwords are different for different
> servers.
> Please help me as this is becoming a show stopper for my application.
> Regards,
> Namwar
This link should be helpful.
http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_thread/thread/53e96ed5cde45213/bbe61adc20aeeb87?lnk=st&q=dynamic+datasource+reporting+services&rnum=1#bbe61adc20aeeb87
Regards,
Enrique Martinez
Sr. Software Consultant|||On Jun 9, 3:50 pm, EMartinez <emartinez...@.gmail.com> wrote:
> On Jun 8, 3:33 pm, "Namwar Rizvi" <nam...@.hotmail.com> wrote:
> > Hi!
> > Is there any way tochangethe connecting string of areportdata source
> > programmatically at run time. Actually my problem is that I have an ASP.NET
> > application and connection string is stored in Web.Config file. I have
> > multiple copies of databases hosted on different SQL Server and I update my
> > web.config connection string to switch between these databases. I just want
> > to use this connection string for my reports also. I am using SQL Server
> > authentication and user name and passwords are different for different
> > servers.
> > Please help me as this is becoming a show stopper for my application.
> > Regards,
> > Namwar
> This link should be helpful.http://groups.google.com/group/microsoft.public.sqlserver.reportingsv...
> Regards,
> Enrique Martinez
> Sr. Software Consultant
I received your email. The only other thing I can think of is to
create the RDL file and/or the datasource file for the report
programmatically via a custom ASP.NET application.|||Have you tried using an expression as a datasource, as described here
http://msdn2.microsoft.com/en-us/library/ms156450.aspx
... look for the section on "dynamic datasources" or "expressions" or
something like that.
I do understand that you want to read your stuff out of the web config file.
But there are several ways you probably could handle this -- without
programmatically altering the RDL file -- assuming the basic idea of a
datasource based on an expression will work for you. To start with, how are
the reports actually invoked (in the asp.net application? or elsewhere?) and
what access does reporting code have to the web.config file and its
contents?
>L<
"Namwar Rizvi" <namwar@.hotmail.com> wrote in message
news:efkKU0hqHHA.1212@.TK2MSFTNGP05.phx.gbl...
> Hi!
> Is there any way to change the connecting string of a report data source
> programmatically at run time. Actually my problem is that I have an
> ASP.NET application and connection string is stored in Web.Config file. I
> have multiple copies of databases hosted on different SQL Server and I
> update my web.config connection string to switch between these databases.
> I just want to use this connection string for my reports also. I am using
> SQL Server authentication and user name and passwords are different for
> different servers.
> Please help me as this is becoming a show stopper for my application.
> Regards,
> Namwar
>

How to catch io's for tsql execution.

Hi gurus.
I want to catch execution statistics like 'reads', 'execution time' for some
reports.
I know I can run it with 'set statistics io on' but it does not effectively
servers the purpose since output can be truncated from sql agent history.
Or I could achiveve it by calling tsql from command line shell, catching
output and parsing it.
Or I can run profiler and catch statistics to table and than browse this
table.
Is there any more elegant and simple way doing it? I was thinking about
executing it and then being able to find execution statistics in system views.
Thank you for your input.
Gene.
You could set up a server-side trace to a file with a very tight filter of
just the few sprocs you wish to track. The file could be loaded into a
table for historical tracking and analysis.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Gene." <Gene@.discussions.microsoft.com> wrote in message
news:A7D159E0-B654-47F3-8CBE-EB374CA2ADDA@.microsoft.com...
> Hi gurus.
> I want to catch execution statistics like 'reads', 'execution time' for
> some
> reports.
> I know I can run it with 'set statistics io on' but it does not
> effectively
> servers the purpose since output can be truncated from sql agent history.
> Or I could achiveve it by calling tsql from command line shell, catching
> output and parsing it.
> Or I can run profiler and catch statistics to table and than browse this
> table.
> Is there any more elegant and simple way doing it? I was thinking about
> executing it and then being able to find execution statistics in system
> views.
> Thank you for your input.
> Gene.
>

How to catch io's for tsql execution.

Hi gurus.
I want to catch execution statistics like 'reads', 'execution time' for some
reports.
I know I can run it with 'set statistics io on' but it does not effectively
servers the purpose since output can be truncated from sql agent history.
Or I could achiveve it by calling tsql from command line shell, catching
output and parsing it.
Or I can run profiler and catch statistics to table and than browse this
table.
Is there any more elegant and simple way doing it? I was thinking about
executing it and then being able to find execution statistics in system views.
Thank you for your input.
Gene.You could set up a server-side trace to a file with a very tight filter of
just the few sprocs you wish to track. The file could be loaded into a
table for historical tracking and analysis.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Gene." <Gene@.discussions.microsoft.com> wrote in message
news:A7D159E0-B654-47F3-8CBE-EB374CA2ADDA@.microsoft.com...
> Hi gurus.
> I want to catch execution statistics like 'reads', 'execution time' for
> some
> reports.
> I know I can run it with 'set statistics io on' but it does not
> effectively
> servers the purpose since output can be truncated from sql agent history.
> Or I could achiveve it by calling tsql from command line shell, catching
> output and parsing it.
> Or I can run profiler and catch statistics to table and than browse this
> table.
> Is there any more elegant and simple way doing it? I was thinking about
> executing it and then being able to find execution statistics in system
> views.
> Thank you for your input.
> Gene.
>

How to catch io's for tsql execution.

Hi gurus.
I want to catch execution statistics like 'reads', 'execution time' for some
reports.
I know I can run it with 'set statistics io on' but it does not effectively
servers the purpose since output can be truncated from sql agent history.
Or I could achiveve it by calling tsql from command line shell, catching
output and parsing it.
Or I can run profiler and catch statistics to table and than browse this
table.
Is there any more elegant and simple way doing it? I was thinking about
executing it and then being able to find execution statistics in system view
s.
Thank you for your input.
Gene.You could set up a server-side trace to a file with a very tight filter of
just the few sprocs you wish to track. The file could be loaded into a
table for historical tracking and analysis.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"Gene." <Gene@.discussions.microsoft.com> wrote in message
news:A7D159E0-B654-47F3-8CBE-EB374CA2ADDA@.microsoft.com...
> Hi gurus.
> I want to catch execution statistics like 'reads', 'execution time' for
> some
> reports.
> I know I can run it with 'set statistics io on' but it does not
> effectively
> servers the purpose since output can be truncated from sql agent history.
> Or I could achiveve it by calling tsql from command line shell, catching
> output and parsing it.
> Or I can run profiler and catch statistics to table and than browse this
> table.
> Is there any more elegant and simple way doing it? I was thinking about
> executing it and then being able to find execution statistics in system
> views.
> Thank you for your input.
> Gene.
>

How to by pass this error message

Hi,

I am trying to import transfer data from one database(sqlserver) to another database(sqlserver)...

But when i run the stored procedure... it gives me the following error

Msg 2627, Level 14, State 1, Procedure usp_ImportFunds_Growthof10K, Line 36

Violation of PRIMARY KEY constraint 'PK_Growthof10K'. Cannot insert duplicate key in object 'Growthof10K'.

and this is my sproc

Code Snippet

USE [StageFiserv_Dev]

GO

/****** Object: StoredProcedure [dbo].[usp_ImportFunds_Growthof10K] Script Date: 08/10/2007 12:53:48 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER Procedure [dbo].[usp_ImportFunds_Growthof10K]

AS

BEGIN

DECLARE @.Count int

SET NOCOUNT ON;

UPDATE Fiserv_Dev..Growthof10K

SET

ChartHeader = g.ChartHeader,

Dates = Substring(g.Dates,1,9),

NAV = g.NAV,

LastChangeDate = GetDate()

FROM

Fiserv_Dev..Growthof10K gk

Join [Growth] g ON gk.Cusip = g.Cusip

Where

gk.ChartHeader <> g.ChartHeader

OR

gk.Dates <> Substring(g.Dates,1,9)

OR

gk.NAV <> g.NAV

SET @.Count = @.@.ROWCOUNT

IF @.Count > 0

RAISERROR('Updated %d records(s) in Growthof10K.', 0, 1, @.Count) WITH NOWAIT

ELSE

RAISERROR('No changes made Growthof10K.', 0, 1) WITH NOWAIT

RAISERROR('Adding records to Growthof10K.',0,1) With NOWAIT

INSERT INTO Fiserv_Dev..Growthof10K

(

Cusip,

ChartHeader,

Dates,

NAV

)

SELECT

g.Cusip,

g.ChartHeader,

Substring(g.Dates,1,9),

g.NAV

FROM

Growth g

Where

NOT Exists (

Select *

FROM

Fiserv_Dev..Growthof10K gk

Where

gk.Cusip = g.Cusip

)

SET @.Count = @.@.ROWCOUNT

IF @.Count > 0

RAISERROR('Added %d records(s) to Growthof10K table.', 0, 1, @.Count) WITH NOWAIT

ELSE

RAISERROR('No records added to Growthof10K table.', 0, 1) WITH NOWAIT

SET NOCOUNT OFF

END

There around 763 records in the growth table

Can some one please help me.

Regards,

Karen

Karen

You are inserting a duplicate value in a primary key. You can't do that. Delete the duplicate values for the primary key field or remove the primary key constraint.

|||

thanks...|||

Did this solve your problem? If so then please mark it as answer.

How to build a query into string variable and run it

Hi,

I need to to run an sql query that is stored in string variable, like this:

DECLARE @.wherestr nvarchar

DECLARE @.sqlstring nvarchar

SET @.wherestr = .... some more code to fill @.wherestr

SET @.sqlstring =

'SELECT @.tgid = tgid '+

' FROM target_groups '+

' WHERE '+@.wherestr

But when I call this command using sp_executesql:

EXEC sp_executesql @.SQLString

I recieve following error: "Only functions and extended stored procedures can be executed from within a function."

Is there any possibility to run the command that is stored in some string (nvarchar) variable inside the function?

Note: I'm using SQL Server 2005 Express, Management Studio

thanks for any help

Jiri Matejka

Jiri,

I don't see where you are trying to declare any function.

You can use

EXEC( @.sqlstring)

I am also not sure what you get when you declare something as "nvarchar". Perhaps just "nvarchar(1)". So maybe you want "nvarchar(8000)" or something like that. I have seen others use "nvarchar(max)", which I think is related to some "max" value used when setting up the SQL Server software.

Here is what MS documentation says about "nvarchar" without the size declaration:

http://msdn2.microsoft.com/en-us/library/ms186939.aspx

When n is not specified in a data definition or variable declaration statement, the default length is 1.

The MS terminology for this is DYNAMIC SQL. You can search this forum for it, as well as Microsoft documentation. There are some excellent internet articles with caveats concerning the use of DYNAMIC SQL. You will find links to these in various posts in this forum.

HTH.

Dan

|||

The function I wrote about is something like this

CREATE FUNCTION [dbo].[GetTargetGroup]
(
@.sex int, @.age int
)
RETURNS int
AS
BEGIN
DECLARE @.wherestr nvarchar(255)
SET @.wherestr=@.sexstr+' AND '+@.agecatstr

DECLARE @.sqlstring nvarchar(255)

some more code here ...

SET @.sqlstring = 'SELECT @.tgid = tgid '+
' FROM target_groups '+
' WHERE '+@.wherestr

EXEC sp_executesql @.SQLString

IF @.tgid IS NULL
SET @.tgid=-1

RETURN @.tgid
END

There is no problem with nvarchar, I use it like "nvarchar(255)", I've just shortened the code to be more readable.

Thanks for your help, but when I call EXEC @.SQLstring, that it causes error of "

Could not find stored procedure 'SELECT @.tgid = tgid FROM target_groups WHERE sex=2 AND agecat=32'". So it looks like if EXEC wants to run stored procedure, not given command. But your reference to "DYNAMIC SQL" is a good hint. I'll check it out.

Jiri Matejka

|||

You may not be able to use EXEC(@.SQLString) inside of a function. (But I seem to recall instances where I do just that.)

I think you also need some "N" in front of your strings you are placing in your NVARCHAR variables, as shown in http://msdn2.microsoft.com/en-us/library/ms188001.aspx .

I hope that will work for you. I don't see anything wrong with the SQL statement.

Dan

|||

You cant use dynamic SQL (sp_executesql or Exec ()) on function.

Change your logic to SP.

Possible Alternative,

Create Temp Table on calling proc

Insert data on Callable Proc on the Created Temp table

After the calling use the temp table on your query.

Create Temp table on calling proc

Insert the callable Proc output in Temp table

Use it on your rest of code.

|||i think its not possible cannot use sp_executesql inside a function

what you can do is try to convert your function into a procedure

Code Snippet

CREATE PROCEDURE [dbo].[GetTargetGroup]
(
@.sex int, @.age int
)
AS

DECLARE @.wherestr nvarchar(255)
SET @.wherestr=@.sexstr+' AND '+@.agecatstr

DECLARE @.sqlstring nvarchar(255)
some more code here ...

SET @.sqlstring = 'SELECT ISNULL(tgid,-1) AS tgid '+
' FROM target_groups '+
' WHERE '+@.wherestr
EXEC sp_executesql @.SQLString
GO

you can get your result by

Code Snippet

INSERT

INTO #TGIDResult

EXEC GetTargetGroup @.theSex, @.theAge

SELECT @.tgid = tgid

FROM #TGIDResult

DROP TABLE #TGIDResult

|||

The issue with this technique is that the @.SQLString executes in a separate scope. Here's how you can pass results from the @.SQLString back to your code:

DECLARE @.wherestr varchar(8000) --in SQL 2005, you may use varchar(max)

DECLARE @.sqlstring varchar(8000)

declare @.tgid bigint

--Create a temp table to hold results: in 2005, you can use @.Table rather than #Table if you prefer

select @.tgid as TG_ID into #TGID where 1 = 2

SET @.wherestr = .... some more code to fill @.wherestr

SET @.sqlstring =

'SELECT @.tgid = tgid '+

' FROM target_groups '+

' WHERE ' + @.wherestr

insert into #TGID

exec(@.SQLString)

select * from #TGID

drop table #TGID