Datatype Question

I've got numbers stored in my table as varchar which i need to cast to a
datatype so that i can perform an arithmetic operation on.
34.2348905444367
45.08070345435
34.6546456354354353
43.6540697929
the problem is, I need them to be cast so that i can perform calculations on
them. I need the specific value back, and not something that is rounded up
or down.
If I use float I get extra numbers appearing at the end, (ie. there is a
rounding issue)
If I try to cast as decimal(12, 16)
The scale must be less than or equal to the precision.
How can i simply get the true value of the record ?
Many thanks.decimal(28, 16)
28 = total length
16 = positions to the right of the decimal point.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Douglas Adams" wrote:

> I've got numbers stored in my table as varchar which i need to cast to a
> datatype so that i can perform an arithmetic operation on.
> 34.2348905444367
> 45.08070345435
> 34.6546456354354353
> 43.6540697929
> the problem is, I need them to be cast so that i can perform calculations
on
> them. I need the specific value back, and not something that is rounded u
p
> or down.
> If I use float I get extra numbers appearing at the end, (ie. there is a
> rounding issue)
>
> If I try to cast as decimal(12, 16)
> The scale must be less than or equal to the precision.
>
> How can i simply get the true value of the record ?
> Many thanks.
>
>|||The following works for me. But don't confuse how the number is
*displayed* with the way it is stored
CREATE TABLE T1 (x VARCHAR(20) NOT NULL PRIMARY KEY)
INSERT INTO T1 (x)
SELECT 34.2348905444367 UNION ALL
SELECT 45.08070345435 UNION ALL
SELECT 34.6546456354354353 UNION ALL
SELECT 43.6540697929 ;
SELECT CAST(x AS DECIMAL(20,16)) FROM T1
Result:
34.2348905444367000
34.6546456354354353
43.6540697929000000
45.0807034543500000
(4 row(s) affected)
David Portas
SQL Server MVP
--|||Thanks guys
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1121852998.202064.66570@.g43g2000cwa.googlegroups.com...
> The following works for me. But don't confuse how the number is
> *displayed* with the way it is stored
> CREATE TABLE T1 (x VARCHAR(20) NOT NULL PRIMARY KEY)
> INSERT INTO T1 (x)
> SELECT 34.2348905444367 UNION ALL
> SELECT 45.08070345435 UNION ALL
> SELECT 34.6546456354354353 UNION ALL
> SELECT 43.6540697929 ;
> SELECT CAST(x AS DECIMAL(20,16)) FROM T1
> Result:
> --
> 34.2348905444367000
> 34.6546456354354353
> 43.6540697929000000
> 45.0807034543500000
> (4 row(s) affected)
> --
> David Portas
> SQL Server MVP
> --
>

DataType Problem

Hi,

I want to pass data to the stored procedure by vb code.One of the input parameters of the SP has Text Data Type.whats the equivalent of this datatype in vb?

Thanks in Advance.

You can use byte array (byte[]) for BLOB data type (text/ntext/image) in SQL Server:)

Datatype mismatch in export

I am exporting my sql table to the access database. My text fileds in SQL are stored as varchar(50) in SQL server 2005. but when I export the table to Access, These fileds get converted to 'memo' fields. I tried converting these memo fields in access to text field but Access does do that, throws an error message saying not enough disk space.

When I try to convert these 'varchar' fields in sql to text fileds, sql throws a 'time out ' message nad does not convert it to text.

What do I do?

Thanks

As per the guidelines of Microsoft, the equivalent of 'VARCHAR' in Sql server is 'TEXT' in MS-Access. How are you exporting Sql server data to Access? Are you using SSIS? On a side note, equivalent of 'MEMO' is 'TEXT' in Sql Server. I wouldn't recommend you to change the data type in Sql Server to change to 'TEXT'.

Text data type in Sql Server is used to store large texts that go beyond 8KB in size. Usually, sql server keeps a 16 byte pointer to the text data in the page. It can contain upto 2 GB size data. In Sql Server 2005, usage of Text data type is not recommended and varchar(max) is recommended.

http://articles.techrepublic.com.com/5100-6345-5033381.html
https://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part2/c0561.mspx?mfr=true

Datatype Conversion during insert

I am performing an insert inside a stored procedure. In the values list , I am doing some data converision. I am getting an compile error like:

Server: Msg 170, Level 15, State 1, Procedure premiumstage_to_fact, Line 303
Line 303: Incorrect syntax near '='.
The code is:

INSERT INTO table-name( c1,c2,c3,c4)

VALUES
(@.v1,
@.v2,
@.variable = CASE WHEN ISDATE([@.variable]) <> 1
THEN 'NULL'
END
END AS @.variable,
@.v3)

Where am I going wrong? Where do I do the conversion? The comma after END AS @.variable, Is that syntax right?

Please advise.

ThanksUse SELECT instead of VALUES.

INSERT INTO table-name( c1,c2,c3,c4)

SELECT @.v1,
@.v2,
@.variable = CASE WHEN ISDATE([@.variable]) <> 1
THEN 'NULL'
END
END AS @.variable,
@.v3|||snail, i don't think your select will work. your syntax attempts to perform a variable assignment which is not allowed in this context. just remove "@.variable =" from your select. i would also remove quotes from THEN 'NULL' because i think the true null is intended.|||Originally posted by ms_sql_dba
snail, i don't think your select will work. your syntax attempts to perform a variable assignment which is not allowed in this context. just remove "@.variable =" from your select. i would also remove quotes from THEN 'NULL' because i think the true null is intended.

to ms_sql_dba:

You are right - it works for 2000. I am not sure about 7. May somebody test it and reply.

create table test13(id int,code varchar(10))
go
insert test13 values(1,case when 1=1 then 1 else 0 end)
insert test13 values(1,'4'+'5')

DataTable and stored Procedure insertion

hi,

How can I insert a DataTable as a whole into the Database (into an existing table) using a stored procedure?

can I just send the DataTable as a parameter to the procedure?

how can I do it?

thanx

Hello,

You need to iterate through the Rows (DataRows) of that DataTable and to insert them one by one to the database.

HTH
Regads

DataSource and more than one recordset

I've a report that use a stored procedure as datasource.
This s.p. return 2 recordset. How to access the second recordset in
reporting services? (like Recordset.NextRecordset in VB)
--
OSVALDO COLITTIRS does not support more than one recordset being returned.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Osvaldo Colitti" <OsvaldoColitti@.discussions.microsoft.com> wrote in
message news:14D1D808-F9CE-40ED-8493-CD8B77055E69@.microsoft.com...
> I've a report that use a stored procedure as datasource.
> This s.p. return 2 recordset. How to access the second recordset in
> reporting services? (like Recordset.NextRecordset in VB)
> --
> OSVALDO COLITTI
>|||Bruce is correct in that the native Data Processing Extensions do not
support stored procedures that return more than one recordset - what happens
is that the first recordset is all that your Reporting Services DataSet will
see.
However, you can certainly create a custom Data Processing Extension, and
could then gain access to the other recordsets returned by a stored
procedure in a report. Its not optimal and the best way forward is usually
to re-design the report logic if this is at all possible. Typically what you
need to do in your Data Processing Extension is archive off the recordsets
into a tables that you can then pull them down from seperately for other
Report DataSets in the Report.
Peter Blackburn
Windows Server Systems - SQL Server MVP
Hitchhiker's Guide to SQL Server Reporting Services
http://www.sqlreportingservices.net
"Osvaldo Colitti" <OsvaldoColitti@.discussions.microsoft.com> wrote in
message news:14D1D808-F9CE-40ED-8493-CD8B77055E69@.microsoft.com...
> I've a report that use a stored procedure as datasource.
> This s.p. return 2 recordset. How to access the second recordset in
> reporting services? (like Recordset.NextRecordset in VB)
> --
> OSVALDO COLITTI
>

Datasource = SQL 2000 sp2 (Cross-Database-Ownership Chaining?)

We have a report in RS 2005, data source uses Windows Integrated Security,
Initial Catalog is the database where the stored procedure is. The stored
procedure selects data from a different database on the server. This server
is running SQL Server 200 sp2 (so pre-Cross-Database-Ownership Chaining
option). The users can execute the stored procedure fine so it does not seem
to be a permissions issue to the data. The Report runs fine FROM the Report
Server. But, if I try to run the report from a different machine, I get:
An error has occurred during report processing. (rsProcessingAborted)
Cannot create a connection to data source 'DataSourceNameIsHere'.
(rsErrorOpeningConnection)
For more information about this error navigate to the report server on the
local server machine, or enable remote errors
The report also runs fine if we don't use Windows Integrated Security. The
report runs fine if the datasource (both databases) are on SQL2000 sp3. Is
there anything that we can do to get this working on SQL2000 sp2? I'm not
sure that all systems on this server are supported on sp3 (3a or 4). It hosts
several databases from purchased products.I found this in the Reporting Services Log:
ERROR: Throwing
Microsoft.ReportingServices.ReportProcessing.ReportProcessingException:
Cannot create a connection to data source 'DataSourceNameIsHere'., ;
Info:
Microsoft.ReportingServices.ReportProcessing.ReportProcessingException:
Cannot create a connection to data source 'DataSourceNameIsHere'. -->
System.Data.SqlClient.SqlException: Login failed for user 'NT
AUTHORITY\ANONYMOUS LOGON'.
Michelle
"Michelle" wrote:
> We have a report in RS 2005, data source uses Windows Integrated Security,
> Initial Catalog is the database where the stored procedure is. The stored
> procedure selects data from a different database on the server. This server
> is running SQL Server 200 sp2 (so pre-Cross-Database-Ownership Chaining
> option). The users can execute the stored procedure fine so it does not seem
> to be a permissions issue to the data. The Report runs fine FROM the Report
> Server. But, if I try to run the report from a different machine, I get:
> An error has occurred during report processing. (rsProcessingAborted)
> Cannot create a connection to data source 'DataSourceNameIsHere'.
> (rsErrorOpeningConnection)
> For more information about this error navigate to the report server on the
> local server machine, or enable remote errors
> The report also runs fine if we don't use Windows Integrated Security. The
> report runs fine if the datasource (both databases) are on SQL2000 sp3. Is
> there anything that we can do to get this working on SQL2000 sp2? I'm not
> sure that all systems on this server are supported on sp3 (3a or 4). It hosts
> several databases from purchased products.sql

Datasource = SQL 2000 sp2 (Cross-Database-Ownership Chaining?)

We have a report in RS 2005, data source uses Windows Integrated

Security, Initial Catalog is the database where the stored procedure

is. The stored procedure selects data from a different database on the

server. This server is running SQL Server 200 sp2 (so

pre-Cross-Database-Ownership Chaining option). The users can execute

the stored procedure fine so it does not seem to be a permissions issue

to the data. The Report runs fine FROM the Report Server. But, if I try

to run the report from a different machine, I get:

An error has occurred during report processing. (rsProcessingAborted)

Cannot create a connection to data source 'DataSourceNameIsHere'. (rsErrorOpeningConnection)

For more information about this error navigate to the report server on the local server machine, or enable remote errors

The report also runs fine if we don't use Windows Integrated Security.

The report runs fine if the datasource (both databases) are on SQL2000

sp3. Is there anything that we can do to get this working on SQL2000

sp2? I'm not sure that all systems on this server are supported on sp3

(3a or 4). It hosts several databases from purchased products.I found this in the Reporting Services Log:

ERROR: Throwing

Microsoft.ReportingServices.ReportProcessing.ReportProcessingException:

Cannot create a connection to data source 'DataSourceNameIsHere'., ;

Info: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot create a connection to data source 'DataSourceNameIsHere'. > System.Data.SqlClient.SqlException: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

Michelle

Datasource = SQL 2000 sp2 (Cross-Database-Ownership Chaining?)

We have a report in RS 2005, data source uses Windows Integrated

Security, Initial Catalog is the database where the stored procedure

is. The stored procedure selects data from a different database on the

server. This server is running SQL Server 200 sp2 (so

pre-Cross-Database-Ownership Chaining option). The users can execute

the stored procedure fine so it does not seem to be a permissions issue

to the data. The Report runs fine FROM the Report Server. But, if I try

to run the report from a different machine, I get:

An error has occurred during report processing. (rsProcessingAborted)

Cannot create a connection to data source 'DataSourceNameIsHere'. (rsErrorOpeningConnection)

For more information about this error navigate to the report server on the local server machine, or enable remote errors

The report also runs fine if we don't use Windows Integrated Security.

The report runs fine if the datasource (both databases) are on SQL2000

sp3. Is there anything that we can do to get this working on SQL2000

sp2? I'm not sure that all systems on this server are supported on sp3

(3a or 4). It hosts several databases from purchased products.I found this in the Reporting Services Log:

ERROR: Throwing

Microsoft.ReportingServices.ReportProcessing.ReportProcessingException:

Cannot create a connection to data source 'DataSourceNameIsHere'., ;

Info: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Cannot create a connection to data source 'DataSourceNameIsHere'. > System.Data.SqlClient.SqlException: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

Michelle

Datasets that rely on parameters

Hi,
I'm trying to create a report under the following conditions:
1. I have a central database that has stored procedures that require @.db as
the parameter so you can specify where the data is coming from.
2. The report I have only wants data from a particular record, so I created
a second dataset to use in a dropdown list.
3.That second dataset uses a stored proc that requires a parameters @.id and
@.db
The problem is that the columns returned from the stored proc has the @.id
are not recognized when I try to specify them as the "value field" and the
"label field" in the Report Parameters Dialog box.
I suspect this is because it needs to know what the @.db is.
1. How do I get the second stored proc to be recognized by the Report
Parameters Dialog box?
2. How do I pass the @.db to the second stored proc without user intervention?
thanks,
-TrishIf I understand you correctly, what you are trying to do is called cascading
parameters. Search books on line for that and see if it answers you
question.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Trishmi" <Trishmi@.discussions.microsoft.com> wrote in message
news:FCF8157D-2E15-4FF7-B70D-DFB3955C9FE8@.microsoft.com...
> Hi,
> I'm trying to create a report under the following conditions:
> 1. I have a central database that has stored procedures that require @.db
> as
> the parameter so you can specify where the data is coming from.
> 2. The report I have only wants data from a particular record, so I
> created
> a second dataset to use in a dropdown list.
> 3.That second dataset uses a stored proc that requires a parameters @.id
> and
> @.db
> The problem is that the columns returned from the stored proc has the @.id
> are not recognized when I try to specify them as the "value field" and the
> "label field" in the Report Parameters Dialog box.
> I suspect this is because it needs to know what the @.db is.
> 1. How do I get the second stored proc to be recognized by the Report
> Parameters Dialog box?
> 2. How do I pass the @.db to the second stored proc without user
> intervention?
> thanks,
> -Trish

Datasets

Problem: I need to add filters on a Dataset. The current dataset is based on
a stored procedure that I would prefere NOT to touch.
Question: Is it possible to build a new dataset based on the first dataset?
(This would inable me to filter on the data output). Or are there other
suggestions for a solution to this problem.
Thanks.
Regards
JonasAs I am going on holidays can you please respond to
terry.bilsborough@.Alcan.com.
Thanks.
Regards
Jonas Larsen
Alcan Engineering
"Jonas Larsen" <Jonas.Larsen@.Alcan.com> wrote in message
news:u8Xvim6YEHA.3664@.TK2MSFTNGP12.phx.gbl...
> Problem: I need to add filters on a Dataset. The current dataset is based
on
> a stored procedure that I would prefere NOT to touch.
> Question: Is it possible to build a new dataset based on the first
dataset?
> (This would inable me to filter on the data output). Or are there other
> suggestions for a solution to this problem.
> Thanks.
> Regards
> Jonas
>|||Yes it is possible to add a filter to a data set. The data set filter
functionality is located on the dataset dialog : Filter tab. Additionally
all data regions (lists, tables, matrix, and chart) support this
functionality.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jonas Larsen" <Jonas.Larsen@.Alcan.com> wrote in message
news:%23VWlAv%23YEHA.384@.TK2MSFTNGP10.phx.gbl...
> As I am going on holidays can you please respond to
> terry.bilsborough@.Alcan.com.
> Thanks.
> Regards
> Jonas Larsen
> Alcan Engineering
> "Jonas Larsen" <Jonas.Larsen@.Alcan.com> wrote in message
> news:u8Xvim6YEHA.3664@.TK2MSFTNGP12.phx.gbl...
> > Problem: I need to add filters on a Dataset. The current dataset is
based
> on
> > a stored procedure that I would prefere NOT to touch.
> >
> > Question: Is it possible to build a new dataset based on the first
> dataset?
> > (This would inable me to filter on the data output). Or are there other
> > suggestions for a solution to this problem.
> >
> > Thanks.
> >
> > Regards
> > Jonas
> >
> >
>sql

Dataset′s

Im trying to make a dataset from a stored procedure. My problem is that RS wont use all of the rows returned.

My SP returns 9 rows. Some of them are dublicates and some ar on of a kind. And it seeems that RS just displays one of each kind. No mater how many there are i the resultset.

Ive read that RS cant handle more than one resultset, but i think that the result returned is just one resultset.

If a run the SP in the Data tab it works just fine, it displays all rows. But when im using it in previewmode its not.

The SP is built around a cursor. It makes a temporary table with info. Then just JOINs it with a real table. Would not that create a SINGLE resultset witch RS should read?

Thanks in advanceIf it is working in preview, you are doing something in the layout of your report. Maybe you are grouping by some repeating value which will cause for the number of rows to be reduced. Try to start with a blank slate and drop the fields in the detail row of a table.|||

Thanks, it workt. I started of with a blank report. You where right, it was somehing in the layout section that flawed.

DataSet/DataReader Performance

Hi,

I've run some tests on fetching data from a SQL Server 2005 database using a DataSet and a DataReader.

In my database, I'm calling a stored procedure that fetches one record from a table. The Sql Server Profiler says the call takes between 20 - 50 ms (within SqlServer). The C# application and Sql Server are on the same machine.

DataSet - performance: average 575ms (for steps 3 & 4) :

1.) Create Connection

2.) Create Command

3.) Create Adapter

4.) Fill DataSet using Adapter

DataReader - performance: average 200ms (for steps 3 & 4):

1.) Create Connection

2.) Create Command

3.) Execute Reader

4.) Read with Reader

Does this performance jibe with what others have seen?

Can the performance be improved either on the DataSet or the DataReader?

Any feelings on how performance will be affected with larger record sets?

This is by design. A datareader is a readonly forward cursor which is optimized for reading data. It should be prefered whenever reading data from a datasource.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Hi Jens,

Thanks for the reply. Are there variants that can be set to opimize the read performance of the datareader (meaning to decrease the 200ms time to respond)?

Thanks,

Andy

|||

Hi,

the only thing that would be really affecting the performance would be to decrease the retrieved data with choosing some parameters / filters in the condition.

HTH, Jens Suessmeyer-

http://www.sqlserver2005.de

DataSet works... DataTable doesnt... (ODBC)

I have a longstanding problem where Stored Procedures or complex T-SQL called from VB.NET will not populate a DataTable object, but will work fine with a DataSet. For example:

'oConn is defined elsewhere...
Dim sErr as String = ""
Dim dt As New DataTable
If Not oConn Is Nothing Then
Try
Dim sSQL as String = "select 1"
Dim oCommand As New OdbcDataAdapter(sSQL, oConn)
oCommand.Fill(dt)
Catch ex As Exception
sErr = "Database Error: " & ex.Message
Finally
sqlCloseConnection(oConn)
End Try
End If

this works fine and my dt DataTable object gets one row. However using this as the SQL:

Dim sSQL as String = "declare @.foo table(mycol integer);insert @.foo select 1;select mycol from @.foo;"

does not work. It executes with no errors, but the DataTable has no rows. Finally, if I replace the DataTable with:

Dim ds as DataSet

I can then get the data in ds.Tables(0) no problem.

So, if the results of the sql are a single result table being put at index 0 of a DataSet, why are they not being put in a single DataTable?

When a sql is a simple select statement it always works directly to a DataTable. Only when it's a SP or sql with some logic does it require the DataSet approach. This is a reporting utility so I need to standardize the code though the sql will be dynamic.

Any ideas?

Hello my friend,

I tried your code in my application and it worked for me, but I am using the SqlDataAdapter. Why are you using Odbc? Try using the Connection, Command, DataAdapter, etc classes from the System.Data.SqlClient namespace and I think it will work.

Kind regards

Scotty

Dataset with multiple tables is not getting populated

Hi,

I have a stored proc which returns multiple result sets. These results sets I am capturing using a strongly typed dataset which in turn I am using to display in the code. My dataset will have 5 tables. However when I run the code only 3 tables get populated and the remaining 2 gets no data.

I have seen the problem earlier and could not resolved it. Please let me know if any one can help.

Thanks in advance

Rohit

From your description, i think it really has no records.

because you can really see five tables without any error.

it means the SP does populate five tables and pass them to Dataset.

|||

Hi

Thanks for your reply.

There is data in the database and when I run the sproc directly through the execute command it returns me the results that I want i.e. all the 5 tables with the rows. However when I use the data adapter method using the fill dataset it just populates fhe first 3 tables and the next 2 are blank. Between the tables in the strongly typed dataset I have no relations set either.

Thanks in advance.

|||Try to useSqlDataAdapter to fill a DataSet, instead of using strongly typed dataset.sql

dataset using stored proc with multi select params

I have a stored proc that I'm using to run a report. It works fine, but currently I'm using a parameter that is a single selection from a dropdown. I'd like to use multi select, but have not been able to get it to work.

In the data tab I'm currently using "text" for command type and :

Code Snippet

declare @.sql nvarchar(2000)

set @.sql = '
EXEC [Monitor] '' + @.p_OfferStatus + '''

exec sp_executesql @.sql, N'@.p_OfferStatus VARCHAR(100)', @.p_OfferStatus = @.p_OfferStatus

when I run this in the data tab, it works fine, returning data, but when I try to preview it it tells me there are syntax errors. Anyone know the correct way to use multi selects with stored procs?

That depends on your code within the proc. You will normally have to split the values up to a table variable to make them searchable as they will be passed as 01,2,3,4. COuld you send your code over ?

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||Hey, thanks for the reply. I can send the code, but you probobly dont need it, I'm just using
WHERE field IN(@.parameter)
|||

Code Snippet

SET @.v_sql = '
SELECT
a.clientname AS Client,
s.Suptype_ID,
s.[name] AS [The Name],
convert(int, GETDATE() - COALESCE(s.datelastprocessed, s.datecreated)) AS [Days Overdue],
CASE
WHEN ol. success_flag = 0 THEN ''FAILED''
WHEN ol. success_flag = 1 THEN ''SUCCESS''
ELSE ''''
END AS Processed,
s.datelastprocessed AS [Date Processed],
o.offerstatus_id AS [Offer Status],
CASE
WHEN s.advertiserlink LIKE ''%suptype_id=%'' THEN 2
WHEN ods.suptype_id IS NULL THEN 0
ELSE 1
END AS Auto
--ft.[File Name],
FROM #temp_suppressiontype s
LEFT OUTER JOIN #temp_1 ods ON ods.suptype_id = s.suptype_id
LEFT OUTER JOIN #temp_2 ol ON ol.suptype_id = s.suptype_id
LEFT OUTER JOIN #temp_3 p ON p.subtype_id = s.suptype_id
LEFT OUTER JOIN #temp_4 a ON a.clientkey_id = p.outsideadvertiser_clientkey_id
LEFT OUTER JOIN #temp_5 o ON o.suptype_id = s.suptype_id
LEFT OUTER JOIN #temp_6 os ON os.offerstatus_id = o.offerstatus_id
WHERE 1 = 1 ' +
CASE WHEN @.p_DaysOverdue IS NULL THEN
''
ELSE '
AND CONVERT(INT, GETDATE() - COALESCE(s.datelastprocessed, s.datecreated)) >= ' + CONVERT(VARCHAR, @.p_DaysOverdue)
END +
CASE WHEN @.p_OfferStatus IS NULL THEN
''
ELSE '
AND o.offerstatus_id IN (' + CONVERT(VARCHAR, @.p_OfferStatus) + ')'
END +
CASE WHEN @.p_Auto IS NULL THEN
''
ELSE '
AND CASE
WHEN s.advertiserlink LIKE ''%suptype_id=%'' THEN 2
WHEN ods.suptype_id IS NULL THEN 0
ELSE 1
END IN (' + CONVERT(VARCHAR, @.p_Auto) + ')'
END + '
ORDER BY 8 DESC, 1, 3 ASC'
PRINT @.v_sql
EXEC sp_executesql @.v_sql

|||

You cannot do this in your procedure, the parameters will be passed as a delimited string, this works if you are using directly a query in Reporting Service, but won′t work using the sttored procedure interface. See my Split function which you can use inside your SQL Server to chop the values and join them to the original query.

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

Jens K. Suessmeyer

http://www.sqlserver2005.de

Dataset to XML

Hi
I have an SQL Server stored procedure wich returns ceratin data and I have
it in a Dataset. I am supposed to change that data into an specific standard
XML format and write it to file.
I am using Visual Studio 2005 wich is completely new to me.
Could you help with some hints about how can I do that.
I have been looking a lot about this from the Net but every thing I have
found is very simple: GetXml() But that is not enough for me. I need to
change that XML so that I get it in the previously defined standard format
Thanks for any help
GabrielHello Gabriel,
Likely the fastest way to deal with this would be the write an XSL/T that
transforms the dataset XML to the desired format. Otherwise, if you have
SQL Server 2005 and are allowed to replace the stored procedure, you could
write a FOR XML PATH query and probably get pretty close to what you're look
ing
for straight out of SQL Server.
Thanks,
Kent Tegels
http://staff.develop.com/ktegels/|||Thanks a lot Kent.
I am completely new in this field and I have to study a lot. Your message
give me good hints to to start with.
greetings from Finland
Gabriel
"Kent Tegels" wrote:

> Hello Gabriel,
> Likely the fastest way to deal with this would be the write an XSL/T that
> transforms the dataset XML to the desired format. Otherwise, if you have
> SQL Server 2005 and are allowed to replace the stored procedure, you could
> write a FOR XML PATH query and probably get pretty close to what you're lo
oking
> for straight out of SQL Server.
> Thanks,
> Kent Tegels
> http://staff.develop.com/ktegels/
>
>sql

Dataset to XML

Hi
I have an SQL Server stored procedure wich returns ceratin data and I have
it in a Dataset. I am supposed to change that data into an specific standard
XML format and write it to file.
I am using Visual Studio 2005 wich is completely new to me.
Could you help with some hints about how can I do that.
I have been looking a lot about this from the Net but every thing I have
found is very simple: GetXml() But that is not enough for me. I need to
change that XML so that I get it in the previously defined standard format
Thanks for any help
Gabriel
Hello Gabriel,
Likely the fastest way to deal with this would be the write an XSL/T that
transforms the dataset XML to the desired format. Otherwise, if you have
SQL Server 2005 and are allowed to replace the stored procedure, you could
write a FOR XML PATH query and probably get pretty close to what you're looking
for straight out of SQL Server.
Thanks,
Kent Tegels
http://staff.develop.com/ktegels/
|||Thanks a lot Kent.
I am completely new in this field and I have to study a lot. Your message
give me good hints to to start with.
greetings from Finland
Gabriel
"Kent Tegels" wrote:

> Hello Gabriel,
> Likely the fastest way to deal with this would be the write an XSL/T that
> transforms the dataset XML to the desired format. Otherwise, if you have
> SQL Server 2005 and are allowed to replace the stored procedure, you could
> write a FOR XML PATH query and probably get pretty close to what you're looking
> for straight out of SQL Server.
> Thanks,
> Kent Tegels
> http://staff.develop.com/ktegels/
>
>

dataset stored procedure problem

I am trying to make a dataset to use with a report. I need to get the data out of a stored procedure. I am using a temporaty table in the stored procedure, and the dataset doesnt recognize any of the colums that are output.It doesnt matter what you use in your stored proc as long as your stored proc returns values when executed. post some code and we might be able to help.

Dataset Refresh...

Hi Folks,
I have a dataset in Reporting Services which points to an stored procedure.
This SP returns all data. Then the data is filtered in the dataset based on
the report parameters is choosen.
1. Will the dataset be refreshed every time i hit the View Report button or
will it get refreshed only the first time the view report is choosen and
would have the data cached till the session ends.
2. Is it better to do the filtering of data in the SP by passing parameters
from the report or is it better to use filters on a dataset.
Thank you
RamdasThe dataset gets refreshed each time you click on View Report with one
important note. In development the designer caches the data and only
refreshes if the parameter changes. If deployed to the server it refreshes
every time.
You are much better off to filter with the query or the SP. I pretty much
never use report filters. The very best thing to do for performance is have
the database return only those records you want.
In RS 2005 all rendering operations are done in RAM. If you have a large
resultset you will be using up a lot of your server's resources.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Ram" <Ram@.discussions.microsoft.com> wrote in message
news:FF7426E4-2FC0-4069-8282-D819CE00F6AF@.microsoft.com...
> Hi Folks,
> I have a dataset in Reporting Services which points to an stored
> procedure.
> This SP returns all data. Then the data is filtered in the dataset based
> on
> the report parameters is choosen.
> 1. Will the dataset be refreshed every time i hit the View Report button
> or
> will it get refreshed only the first time the view report is choosen and
> would have the data cached till the session ends.
> 2. Is it better to do the filtering of data in the SP by passing
> parameters
> from the report or is it better to use filters on a dataset.
> Thank you
> Ramdas
>|||Hi Bruce,
Thank you for the insight.
One more question, is it possible to refresh the report without clicking the
View Report button.
Thank you
"Bruce L-C [MVP]" wrote:
> The dataset gets refreshed each time you click on View Report with one
> important note. In development the designer caches the data and only
> refreshes if the parameter changes. If deployed to the server it refreshes
> every time.
> You are much better off to filter with the query or the SP. I pretty much
> never use report filters. The very best thing to do for performance is have
> the database return only those records you want.
> In RS 2005 all rendering operations are done in RAM. If you have a large
> resultset you will be using up a lot of your server's resources.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Ram" <Ram@.discussions.microsoft.com> wrote in message
> news:FF7426E4-2FC0-4069-8282-D819CE00F6AF@.microsoft.com...
> > Hi Folks,
> > I have a dataset in Reporting Services which points to an stored
> > procedure.
> > This SP returns all data. Then the data is filtered in the dataset based
> > on
> > the report parameters is choosen.
> >
> > 1. Will the dataset be refreshed every time i hit the View Report button
> > or
> > will it get refreshed only the first time the view report is choosen and
> > would have the data cached till the session ends.
> >
> > 2. Is it better to do the filtering of data in the SP by passing
> > parameters
> > from the report or is it better to use filters on a dataset.
> >
> > Thank you
> >
> > Ramdas
> >
>
>|||If you have a report that you want to refresh automatically then you can set
that in the dataset tab. Click on the ...
For instance, I have a report that refreshes every 5 minutes automatically.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Ram" <Ram@.discussions.microsoft.com> wrote in message
news:D205F7FF-A85D-4ACD-95EF-D68050FA3EFE@.microsoft.com...
> Hi Bruce,
> Thank you for the insight.
> One more question, is it possible to refresh the report without clicking
> the
> View Report button.
> Thank you
> "Bruce L-C [MVP]" wrote:
>> The dataset gets refreshed each time you click on View Report with one
>> important note. In development the designer caches the data and only
>> refreshes if the parameter changes. If deployed to the server it
>> refreshes
>> every time.
>> You are much better off to filter with the query or the SP. I pretty much
>> never use report filters. The very best thing to do for performance is
>> have
>> the database return only those records you want.
>> In RS 2005 all rendering operations are done in RAM. If you have a large
>> resultset you will be using up a lot of your server's resources.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Ram" <Ram@.discussions.microsoft.com> wrote in message
>> news:FF7426E4-2FC0-4069-8282-D819CE00F6AF@.microsoft.com...
>> > Hi Folks,
>> > I have a dataset in Reporting Services which points to an stored
>> > procedure.
>> > This SP returns all data. Then the data is filtered in the dataset
>> > based
>> > on
>> > the report parameters is choosen.
>> >
>> > 1. Will the dataset be refreshed every time i hit the View Report
>> > button
>> > or
>> > will it get refreshed only the first time the view report is choosen
>> > and
>> > would have the data cached till the session ends.
>> >
>> > 2. Is it better to do the filtering of data in the SP by passing
>> > parameters
>> > from the report or is it better to use filters on a dataset.
>> >
>> > Thank you
>> >
>> > Ramdas
>> >
>>