(long) querry doesn't fit in "query-string" window

hi,

i have worked three days on a query to display all my results in a beautiful report. The query is fine because when i execute it in Query Analyzer i have all results i want to see in my statistics-table in my report...

One thing: it's contains about 100 unioned statements, which results in a super-long query. Performance is OK because it are all 100 very easy statements that are union-ed together.

But, when I copy-paste it in my query-string window/textbox of the report designer, I see that there's a maximum on that textbox lenght, which results in the fact that my long query suddenly stops.

Any solutions?
put it in a stored procedure |||yes of course, but this was not asked by the client.

i tried already to edit the rdl file directly. (i paste my whole query in the xml structure), but then when i ask a report preview via BIDS, the BIDS crashes !!!!!!!!!!!!!!!!

i have no other choice then do the workaround via the stored procedure,
but it is really a bug in microsoft i say, isn't it?

|||

You have another alternative...

Create your query string in embedded code, like this:

Function GetSQL
DIM x AS New System.Text.StringBuilder()
x.Append("SELECT ") ' etc...
RETURN x.ToString()
End Function

... Now your query string looks like this

= Code.GetSQL()

HTH, and yes it works <g>, and when you're trying to create a dynamic query out of a lot of parameters it's a heck of a lot more maintainable/legible, too,

>L<

'(-)' in list of index columns which I get after sp_helpindexes

Hi,
Does anybody know what '(-)' means in the list of index
columns when I execute sp_helpindexes for the table.
For example:
exec sp_helpindexes <table name> returns:
column1(-),column2,column3.
I saw this several times, and it gets disapeared when I
rebuild index.
Thanks,
OJ
Descending.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"OJ" <anonymous@.discussions.microsoft.com> wrote in message
news:866d01c4d0d3$b19ed5b0$a601280a@.phx.gbl...
> Hi,
> Does anybody know what '(-)' means in the list of index
> columns when I execute sp_helpindexes for the table.
> For example:
> exec sp_helpindexes <table name> returns:
> column1(-),column2,column3.
> I saw this several times, and it gets disapeared when I
> rebuild index.
> Thanks,
> OJ
|||Hi OJ
It means the index was build with the index keys sorted in descending order.
If you rebuild your indexes, and don't explicitly state you want to build
them in descending order, they will be built in ascending order and the (-)
will go away.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"OJ" <anonymous@.discussions.microsoft.com> wrote in message
news:866d01c4d0d3$b19ed5b0$a601280a@.phx.gbl...
> Hi,
> Does anybody know what '(-)' means in the list of index
> columns when I execute sp_helpindexes for the table.
> For example:
> exec sp_helpindexes <table name> returns:
> column1(-),column2,column3.
> I saw this several times, and it gets disapeared when I
> rebuild index.
> Thanks,
> OJ

'(-)' in list of index columns which I get after sp_helpindexes

Hi,
Does anybody know what '(-)' means in the list of index
columns when I execute sp_helpindexes for the table.
For example:
exec sp_helpindexes <table name> returns:
column1(-),column2,column3.
I saw this several times, and it gets disapeared when I
rebuild index.
Thanks,
OJDescending.
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"OJ" <anonymous@.discussions.microsoft.com> wrote in message
news:866d01c4d0d3$b19ed5b0$a601280a@.phx.gbl...
> Hi,
> Does anybody know what '(-)' means in the list of index
> columns when I execute sp_helpindexes for the table.
> For example:
> exec sp_helpindexes <table name> returns:
> column1(-),column2,column3.
> I saw this several times, and it gets disapeared when I
> rebuild index.
> Thanks,
> OJ|||Hi OJ
It means the index was build with the index keys sorted in descending order.
If you rebuild your indexes, and don't explicitly state you want to build
them in descending order, they will be built in ascending order and the (-)
will go away.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"OJ" <anonymous@.discussions.microsoft.com> wrote in message
news:866d01c4d0d3$b19ed5b0$a601280a@.phx.gbl...
> Hi,
> Does anybody know what '(-)' means in the list of index
> columns when I execute sp_helpindexes for the table.
> For example:
> exec sp_helpindexes <table name> returns:
> column1(-),column2,column3.
> I saw this several times, and it gets disapeared when I
> rebuild index.
> Thanks,
> OJ

'(-)' in list of index columns which I get after sp_helpindexes

Hi,
Does anybody know what '(-)' means in the list of index
columns when I execute sp_helpindexes for the table.
For example:
exec sp_helpindexes <table name> returns:
column1(-),column2,column3.
I saw this several times, and it gets disapeared when I
rebuild index.
Thanks,
OJDescending.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"OJ" <anonymous@.discussions.microsoft.com> wrote in message
news:866d01c4d0d3$b19ed5b0$a601280a@.phx.gbl...
> Hi,
> Does anybody know what '(-)' means in the list of index
> columns when I execute sp_helpindexes for the table.
> For example:
> exec sp_helpindexes <table name> returns:
> column1(-),column2,column3.
> I saw this several times, and it gets disapeared when I
> rebuild index.
> Thanks,
> OJ|||Hi OJ
It means the index was build with the index keys sorted in descending order.
If you rebuild your indexes, and don't explicitly state you want to build
them in descending order, they will be built in ascending order and the (-)
will go away.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"OJ" <anonymous@.discussions.microsoft.com> wrote in message
news:866d01c4d0d3$b19ed5b0$a601280a@.phx.gbl...
> Hi,
> Does anybody know what '(-)' means in the list of index
> columns when I execute sp_helpindexes for the table.
> For example:
> exec sp_helpindexes <table name> returns:
> column1(-),column2,column3.
> I saw this several times, and it gets disapeared when I
> rebuild index.
> Thanks,
> OJ

"String or binary data would be truncated" and field specifications

Hi all,

i have "String or binary data would be truncated" error when i try to execute an insert statment.

can i find witch field is affected by this error? (for return it to the user)

thank's all

If possible it would be far better to truncate the string to the maximum allowable length within the client application (and warn the user if necessary) before passing it to SQL Server for insertion into the database.

For debug purposes you could run SQL Profiler to witness the values of the parameters being passed into the stored procedure then work through the SQL code and locate where the error is being caused.

Chris

|||

this is not possible because client and db musn't be linked (db can be modified). i don't know futur size of these fields.

it's a feature for my users, indicating whitch field is too long

|||

As far as I am aware, there's no way to determine which column's length has been exceeded.

You should add code into your stored procedure to check the lengths of variables before inserting their values into your tables, raising an error if necessary - see the example below.

Again I stress that it would be better to modify the client application's code to either warn the user or to limit the number of characters they can enter into a field.

Chris

Code Snippet

--This batch will fail with the SQL Server error message

DECLARE @.MyTable TABLE (MyID INT IDENTITY(1, 1), MyValue VARCHAR(10))

DECLARE @.MyParameter VARCHAR(100)

--Create a string of 52 chars in length

SET @.MyParameter = REPLICATE('Z', 52)

INSERT INTO @.MyTable(MyValue)

VALUES (@.MyParameter)

GO

--This batch will fail with a custom error message

DECLARE @.MyTable TABLE (MyID INT IDENTITY(1, 1), MyValue VARCHAR(10))

DECLARE @.MyParameter VARCHAR(100)

--Create a string of 52 chars in length

SET @.MyParameter = REPLICATE('Z', 52)

IF LEN(@.MyParameter) > 10

BEGIN

RAISERROR('You attempted to insert too many characters into MyTable.MyValue.', 16, 1)

RETURN

END

ELSE

BEGIN

INSERT INTO @.MyTable(MyValue)

VALUES (@.MyParameter)

END

GO

"Row yielded no match during lookup" while there is no row going through the Lookup

Hi all,

I don't understand what's happening here.

I have a Conditional Split with 3 outputs. On the first output I have a lookup, when I execute the package I have 56 rows going through the Conditional Split, all rows are then going to the 2nd and 3rd output but the lookup on the first output generates an error "Row yielded no match during lookup".

I don't understand why the lookup is generating an error while there is no row going through it.

Any idea ?

Sbastien.

As an image worth more than a thousand words... http://www.mediamax.com/sebnunes/Links/98B4724720

|||

I have no idea why is that happening; but have you try to configure errors in the lookup task to redirect them? just to see if there is something going through it....just a thought

Rafael Salas

|||Yes I did, there is no row redirected in the Error output either :-\|||

Am I the only to have this behaviour ? I'm stuck here, how a transformation could raise an error while there is no row going through it ? It's nonsense isn't it ?

Any idea how to workaround this ?

|||I've found the problem, it's just the Designer which was messed up. I have 2 different lookup transformation with the same query and the designer was highlighting (in red in Debug Mode) one of the Lookup transformation as the one causing the error but in fact the error was happening in the other Lookup transformation.

"Row yielded no match during lookup" while there is no row going through the Looku

Hi all,

I don't understand what's happening here.

I have a Conditional Split with 3 outputs. On the first output I have a lookup, when I execute the package I have 56 rows going through the Conditional Split, all rows are then going to the 2nd and 3rd output but the lookup on the first output generates an error "Row yielded no match during lookup".

I don't understand why the lookup is generating an error while there is no row going through it.

Any idea ?

Sbastien.

As an image worth more than a thousand words... http://www.mediamax.com/sebnunes/Links/98B4724720

|||

I have no idea why is that happening; but have you try to configure errors in the lookup task to redirect them? just to see if there is something going through it....just a thought

Rafael Salas

|||Yes I did, there is no row redirected in the Error output either :-\|||

Am I the only to have this behaviour ? I'm stuck here, how a transformation could raise an error while there is no row going through it ? It's nonsense isn't it ?

Any idea how to workaround this ?

|||I've found the problem, it's just the Designer which was messed up. I have 2 different lookup transformation with the same query and the designer was highlighting (in red in Debug Mode) one of the Lookup transformation as the one causing the error but in fact the error was happening in the other Lookup transformation.

"Package Failed Validation" error

I'm trying to have a parent package derive the filename for the appropriate child package to execute. The parent package logic manipulates a Filename variable that is then used in the connection string of the Execute Package task: "c:\\Packages\\" + @.[User::Filename]
The error I'm getting is Error 0xC0012050 while loading package file "c:\Packages\test.dtsx". Package failed validation from the ParentPackage task. The package cannot run.
How can I resolve this security/authentication problem?
Why I'm trying to do this? I'm importing dozens of different flat files. Some of the files share a generic import package, but many use their own custom import packages. I've not found a reasonable way to do this kind of branch from within the Control Flow of the parent package. I'm eager for suggestions though!

Thanks

TGrant wrote:

I'm trying to have a parent package derive the filename for the appropriate child package to execute. The parent package logic manipulates a Filename variable that is then used in the connection string of the Execute Package task: "c:\\Packages\\" + @.[User::Filename]
The error I'm getting is Error 0xC0012050 while loading package file "c:\Packages\test.dtsx". Package failed validation from the ParentPackage task. The package cannot run.
How can I resolve this security/authentication problem?
Why I'm trying to do this? I'm importing dozens of different flat files. Some of the files share a generic import package, but many use their own custom import packages. I've not found a reasonable way to do this kind of branch from within the Control Flow of the parent package. I'm eager for suggestions though!

Thanks

Hi,
You can place expressions onto the precedence constraints in your control-flow. This allows for conditional execution of tasks which sounds as though its what you're looking for.

You may want to explore the Foreach loop container which enables you to carry out the same procedure on a collection of files one at a time. (Here's a demo of how to do this: http://blogs.conchango.com/jamiethomson/archive/2005/05/30/1489.aspx) You can place your conditional logic (as described above) within the Foreach loop container.

Also, if you want to process multiple files which have the same format in exactly the same way, consider using a multiflatfile connection manager!

Hope this helps.|||Is the package available at the time of validation? It may be that the error is telling you that it cannot load the child package. That will fail validation. There are a few things you can do if this is the case, but the easiest is to set "DelayValidation" equal to true on the ExecutePackage task.
There should be a valid package there when validating though.

"OnGroupChange" event?

I find that I regularly need to evaluate all the rows in "data groups" within the pipeline and execute script based transform logic for each grouping (aggregate transform does not provide script access to the data).

I do this in script by sorting the pipelined data on some key columns to define the groupings, caching the key values as the rows pass through the ProcessInputRow procedure, and running logic that compares the current row's key values as each row passes through the transform with the previous row's key values. When they differ I know I have a new group (special casing for the very first row in the pipleine), so I perform my transforms with cached data from the previous group .

This works but I am thinking this must be a common "pattern". Also, I have experience with a competing ETL tool which has "built in" support for script processing of user defined data groupings. That product provides an "OnChange" event, where the user defines the key in the GUI (can be composite), and the event provides a container to hold script (with access to the row data values) to run when the event "fires"(any values change in the key vs. the previous). Of course the data stream again has to be sorted for this to work.

Anyone else regularly have a need to do this type of processing? If so perhaps it is a good candidate for a custom component? Or a suggestion to Microsoft to enhance the aggregate component for the next SSIS version to provide a script "hook" in the aggregate transform? Anyone else regularly have this type of processing requirement?

Ken

Yeah, this would be a useful feature, but it looks like someone will have to write a custom component!

"Illegal characters in path" while getting varaible values from stored proc

hi!

I am getting some junk characters while executing sql task(which is a stored proceedure) when i execute the same sql environment it is fine.

User::ArchiveDir {? )ArchiveVoucherLog_6-26-2006

What is problem in here?

Any help

Thanks,

Jasmine

} String

was an error message generated? if so, please post it.

"&" in an URL

Hi,

I have the following problem.
I want to execute this url :

http://SERVER/reportserver?/testCUBE/CUBE02&rs:Command=Render&Budgethouder=[Budgethouder].[Naam Budgethouder].%26[1]

"Budgethouder" is my parameter and "[Budgethouder].[Naam Budgethouder].&[1]" is the value

If I enter this url in IE then it executes perfectly, but when I open it with my winform (C# with URI) in a webbrowser it won't work. The URI seems to convert the '%26' to '&', but the ampersand shouldn't have its function, it must be 'seen' as a ordinary character. Can somebody help me with this?
I don't know if this works, but you could try:
%2526 this is a double url-encoded &

|||It won't work, I think he doesn't understand %25. an other idea?

'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral,

I have a report that uses some embedded custom code. The embedded custom code is a function that execute some sql query on a sql server database.Everything works fine in Visual studio. The report gets deployed on the server successfully, however when running the report from report manager i get the following error message :

The Hidden expression for the table ‘table1’ contains an error: Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.

Here is the code :

************************************************************************

Public function get_field() as string
Dim myConnection As System.Data.SqlClient.SqlConnection
Dim myCommand As System.Data.SqlClient.SqlCommand
Dim data_reader As System.Data.SqlClient.SqlDataReader
Dim field(100) as string
Dim i as integer
Dim j as integer
Dim sql_field as string
Dim nbr_field as integer
Dim rtn_string as string

i = 0
sql_field ="Select field from mytable"
myConnection = New System.Data.SqlClient.SqlConnection("Datasource=xxx.xxx.xxx.xx\mydatabase;Initial Catalog=mydatabase;User Id=user1;Password=password1;")
myConnection.Open()
myCommand = New System.Data.SqlClient.SqlCommand(sql_field, myConnection)
data_reader = myCommand.ExecuteReader()
While data_reader.Read()
if data_reader.HasRows then
field(i)= data_reader(0).ToString()
end if
nbr_field = nbr_field + 1
i= i+1
End While
data_reader.Close()
myConnection.Close()

for j = 0 to nbr_field -1
rtn_string = rtn_string + field(j) + ","
Next j

rtn_string = left(rtn_string,rtn_string.length-1)
return rtn_string
'return sql_cmd
'return yes_no
'return lkupfield
end function

******************************************************************

Why do i get the error message ?, is this related to Code Access Security issues with .net framework. if yes

how do i set the Security so the report server or report manager allows embedded custom code to be executed. Any advice ?

Chi

To set code access security for your custom code, see the following article: http://msdn2.microsoft.com/en-us/library/aa237680(SQL.80).aspx

-Albert

|||

Thanks Albert, i have one more question, do i have to grant permission even if the code that i use is embedded in the report .I mean i did not create a custom DLL. In other words do i have to grant permission to the code even if it's embedded in the report, but in my code i reference System.Data.SqlClient.

Public function get_field() as string
Dim myConnection As System.Data.SqlClient.SqlConnection
Dim myCommand As System.Data.SqlClient.SqlCommand
Dim data_reader As System.Data.SqlClient.SqlDataReader
Dim field(100) as string
Dim i as integer
Dim j as integer
Dim sql_field as string
Dim nbr_field as integer
Dim rtn_string as string

i = 0
sql_field ="Select field from mytable"
myConnection = New System.Data.SqlClient.SqlConnection("Datasource=xxx.xxx.xxx.xx\mydatabase;Initial Catalog=mydatabase;User Id=user1;Password=password1;")
myConnection.Open()
myCommand = New System.Data.SqlClient.SqlCommand(sql_field, myConnection)
data_reader = myCommand.ExecuteReader()
While data_reader.Read()
if data_reader.HasRows then
field(i)= data_reader(0).ToString()
end if
nbr_field = nbr_field + 1
i= i+1
End While
data_reader.Close()
myConnection.Close()

for j = 0 to nbr_field -1
rtn_string = rtn_string + field(j) + ","
Next j

rtn_string = left(rtn_string,rtn_string.length-1)
return rtn_string
'return sql_cmd
'return yes_no
'return lkupfield
end function

chi

|||

Sorry, I didn't read your post carefully enough. Yes, you need to set code access security for the custom code in your report. The instructions are in the following article: http://msdn2.microsoft.com/en-us/library/aa237693(SQL.80).aspx. You need to modify the permissions for the "Report_Expressions_Default_Permissions" code group in the policy file.

-Albert

#Re: manipulate field value from select statement

Hi all,

any assistance will be much appreciated on this one .... a bit clueless at the mo!

I've been trying to execute the code below in which part of my select statement is a calculated value i.e. Right([ED],2) & "/" & SUBSTRING([ED],5,2) & "/" & Left([ED],4) AS ENDDATE

code:

SELECT vw_contract_dates.[ContractNo], vw_contract_dates.[Title], vw_contract_dates.[CC], vw_contract_dates.[Sponsor],
Right([SD],2) & "/" & SUBSTRING([SD],5,2) & "/" & Left([SD],4) AS STARTDATE,
Right([ED],2) & "/" & SUBSTRING([ED],5,2) & "/" & Left([ED],4) AS ENDDATE, vw_contract_dates.[CEILING], [CEILING]-[SPEND] AS Remain,
vw_contract_spend.[SPEND], CASE WHEN [CEILING]-[SPEND]<0 THEN 1 ELSE [SPEND]/[CEILING] END AS [% Spend],
DATEDIFF(DAY,GETDATE(), ENDDATE) AS [Days Remain]
FROM vw_contract_spend INNER JOIN vw_contract_dates ON vw_contract_spend.[CONTRACTCODE] = vw_contract_dates.[ContractNo]

however this error message keeps coming up at runtime:

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'ENDDATE'.

My guess is it's happening when I try to get the date difference (DATEDIFF)....

help!!

Try this..

SELECT vw_contract_dates.[ContractNo], vw_contract_dates.[Title], vw_contract_dates.[CC], vw_contract_dates.[Sponsor],
Right([SD],2) + '/' + SUBSTRING([SD],5,2) + '/' + Left([SD],4) AS STARTDATE,
Right([ED],2) + '/' + SUBSTRING([ED],5,2) + '/' + Left([ED],4) AS ENDDATE,
vw_contract_dates.[CEILING], [CEILING]-[SPEND] AS Remain,
vw_contract_spend.[SPEND], CASE WHEN [CEILING]-[SPEND]<0 THEN 1 ELSE [SPEND]/[CEILING] END AS [% Spend],
DATEDIFF(DAY,GETDATE(), ENDDATE) AS [Days Remain]
FROM vw_contract_spend INNER JOIN vw_contract_dates ON vw_contract_spend.[CONTRACTCODE] = vw_contract_dates.[ContractNo]

|||

Sh... should have seen that one.

Cheers mate .. however I'm still having an error from that code:

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'vw_contract_spend'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'vw_contract_dates'.

Is there some sort of restriction on selecting from a view in sql server?

|||

Bolugbe wrote:

My guess is it's happening when I try to get the date difference (DATEDIFF)....

You're right, the problem is in the DATEDIFF statement.
You cannot use just assigned aliases in calculations, so you should either copy/paste the formula for getting ENDDATE into DATEDIFF function or use nested select statements|||

Try this one..

SELECT vw_contract_dates.[ContractNo], vw_contract_dates.[Title], vw_contract_dates.[CC], vw_contract_dates.[Sponsor],
Right([SD],2) + '/' + SUBSTRING([SD],5,2) + '/' + Left([SD],4) AS STARTDATE,
Right([ED],2) + '/' + SUBSTRING([ED],5,2) + '/' + Left([ED],4) AS ENDDATE,
vw_contract_dates.[CEILING], [CEILING]-[SPEND] AS Remain,
vw_contract_spend.[SPEND], CASE WHEN [CEILING]-[SPEND]<0 THEN 1 ELSE [SPEND]/[CEILING] END AS [% Spend],
DATEDIFF(DAY,GETDATE(), Convert(datetime,Right([ED],2) + '/' + SUBSTRING([ED],5,2) + '/' + Left([ED],4))) AS [Days Remain]
FROM vw_contract_spend INNER JOIN vw_contract_dates ON vw_contract_spend.[CONTRACTCODE] = vw_contract_dates.[ContractNo]