CommandText Size Limit?

Hello, some of our users have some "canned" reports they want to run without the hassle of searching for members throughout a myriad of hierarchies in our OLAP cubes. Tools like ProClarity and Novaview are great for analysis, but when needing long lists of unrelated low lying members of a dimension, they're quite cumbersome.

We decided to use Reporting Services to save the reports' static layouts and pass the MDX strings programatically (SQL 2000/Analysis Services/ .NET Framework 1.1/Development Env 2003). The problem is, many of the named sets the users can build dynamically into the MDX to bounce of the cubes goes well past 65k, and our web services app keeps choking on this. Is there any way to bump this limit up?

Thanks in advance for any help!

Anyone? Sorry to push this up, but I was hoping someone had some insight. Thanks!

Commandtext problem

I am trying to get the return value from the select statement and store it into a variable
does anyone know whats wrong with my code?

Private Function check_login(ByVal login As String) As String

dim dbCon As SqlConnection = New SqlConnection
Dim dbCmd As SqlCommand = New SqlCommand

Dim returnuser As Integer

dbCon.ConnectionString = _
"Data Source=localhost;" + _
"Initial Catalog=registeruser;" + _
"User ID=int422;" + _
"Password=int422"

dbCon.Open()

dbCmd.Connection = dbCon
dbCmd.CommandText = "SELECT count(login_id) from users where login_id=@.login"
dbCmd.CommandType = CommandType.Text

returnuser = dbCmd.ExecuteScalar

Return returnuser

dbCon.Close()

SELECT count(login_id) from users where login_id=@.login

I don't see where you are setting the value for the @.login parameter. Also the dbCon.Close() should be immediately after the ExecuteScalar() call and, in particular, before the return statement.

CommandText on Report Server

When a report runs is it possible to retrieve the command text and modify it in order to append some additional "where" conditions, before the sql is processed to return data to the report?

You can write code like this

declare sSQL varchar(500)

declare sWhere varchar(200)

set sSQL = "Select * from mytable"

if lenght(@.Parameter1) > 0

begin

sWhere = " Where mycolume = '" + @.Parameter2 + "'"

end

sSQL = sSQL + sWhere

EXEC (sSQL)

|||

Thanks for the reply, but I need to be clearer with the question that I am asking.

I have written code to construct the piece of SQL that I need to append - it retrieves conditions from the database which define some additional filtering for security.

What I need to know is if it is possible at runtime to trap the SQL statement stored in the <CommandText> element of the RDL for the report that is running - append my additional segment of SQL just before it gets processed. Bear in mind that a Report designed may have several SQL statements from individual parts of the overall report - a data set tp provide values for parameters, a dataset for each section on a report, chart and matrix for example.

Should I be looking at doing it via a Data Processing extension, therefore trapping any report that that is run via SSRS?

CommandText issue - SQL Reporting Services

I'm having a ton of trouble with a dataset. It builds at design time,
but fails at runtime, saying:

--------
Processing Errors
--------
An error has occurred during report processing.
Cannot set the command text for data set 'ds_Legal_Entity'.
Error during processing of the CommandText expression of dataset
'ds_Legal_Entity'.
--------
OK
--------

Below is the CommandText for ds_Legal_Entity that gives me the error:

="SELECT DISTINCT dbo.t_d_legal_entity.legal_entity_desc FROM
dbo.t_d_legal_entity INNER JOIN dbo.t_f_month_summary ON
dbo.t_d_legal_entity.legal_entity_key =
dbo.t_f_month_summary.legal_entity_key WHERE
(dbo.t_f_month_summary.acctg_mth_key = " &
Parameters!acctg_mth_key.Value & ")" &
IIF(Parameters!BusUnitKey.Value = 0,""," AND
(dbo.t_f_month_summary.bus_unit_key = " & Parameters!BusUnitKey.Value &
")") & " ORDER BY dbo.t_d_business_unit.legal_entity_desc"

If I delete everything after " & Parameters!acctg_mth_key.Value & ")",
I won't get the error, so I assume that's where the problem lies. I
just need another pair of eyes to see it.

Thanks!
Mike(Michael.EJ.Reynolds@.gmail.com) writes:
> Below is the CommandText for ds_Legal_Entity that gives me the error:
>="SELECT DISTINCT dbo.t_d_legal_entity.legal_entity_desc FROM
> dbo.t_d_legal_entity INNER JOIN dbo.t_f_month_summary ON
> dbo.t_d_legal_entity.legal_entity_key =
> dbo.t_f_month_summary.legal_entity_key WHERE
> (dbo.t_f_month_summary.acctg_mth_key = " &
> Parameters!acctg_mth_key.Value & ")" &
> IIF(Parameters!BusUnitKey.Value = 0,""," AND
> (dbo.t_f_month_summary.bus_unit_key = " & Parameters!BusUnitKey.Value &
> ")") & " ORDER BY dbo.t_d_business_unit.legal_entity_desc"
>
> If I delete everything after " & Parameters!acctg_mth_key.Value & ")",
> I won't get the error, so I assume that's where the problem lies. I
> just need another pair of eyes to see it.

The best way to sort this out, is to grab the generated SQL and then
copy and paste into Query Analyzer to run it there for hopefully a
better error message.

The one error I can see is that the ORDER BY clause includes a column
which is not in the SELECT list, which is required when you have DISTINCT.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

CommandText

Dim MiSQL As String = "INSERT INTO tabla1(ID,Proveedor,Tipo) VALUES (@.IDCentro,@.Proveedor,@.Tipo)"
............
cm.Parameters.Add(New SqlParameter("@.IDCentro", SqlDbType.Int, 4)).Value = 15
cm.Parameters.Add(New SqlParameter("@.Proveedor", SqlDbType.NVarChar, 50)).Value = "IBM"
cm.Parameters.Add(New SqlParameter("@.Tipo", SqlDbType.TinyInt, 1)).Value = 35

Hi friens, its possible to get the string with the vaules of parameters changed?, i mean get this string in code:

INSERT INTO tabla1(ID,Proveedor,Tipo) VALUES (15,IBM,35);

I tried with CommandText but in this string are the variables and not the values...thx a lot.

No.

You can get something similiar if you you SQL Profiler, but it'll look something like:

sp_ExecuteSQL "INSERT INTO tabla1(ID,Proveedor,Tipo) VALUES (@.IDCentro,@.Proveedor,@.Tipo)",@.IDCentruo=N'15',@.Proveedor=N'IBM',@.Tipo=N'35'