GET VS. POST METHODS and Querystring lengths

We've run into a rather sizable problem here.

When passing querystring values using GET, to our SQL Server Reporting Services Reports, we've discovered that we've exceeded the maximum URL length,.

We are thinking that we need to change how we're passing this data to our reports.

Can we use POST and transfer the data throught hte HTTP header and bypass this limitation?

Thanks.

Doug

What in the world could you be passing as parameters? Is it that there are so many parameters or that the parameter values are long strings?|||yes u can use hidden variables , param tags or Session variable to overcome this problem|||

good question.

We have data issues on our back end...which translates to list box items with multiple sometimes up to 10 values per selection. With multiple selections in a wizard that has 5 steps, they add up...

yeah it's ugly...

|||Maybe you should create a record in a temp database with all the keys/values. Then pass in the key to that record to your report. A datasource goes out gets the crazy values.

Get the current year using SQL

Hi everyone,

Currenly, I'm getting the current year in my code, and passing this to my stored procedure.

I'm sure there's a way just to get the current year (ie '2004') just by using SQL.

I was just about to post this question here when I did a bit more digging around and found my solution:

DATEPART(yyyy, GETDATE())

This will get the year.

Hope this helps someone searching for the answer!Also, YEAR(GetDate()) will get you the year.|||> Also, YEAR(GetDate()) will get you the year.

That looks even better!!

Cheers

get records after executing a stored procedure

Hi All,

I have a Execute SQL Task I get some values from a table onto three variables. Next step in a DFT, I try to execute a stored proc by passing these variables as parameters.

EXEC [dbo].[ETLloadGROUPS]
@.countRun =?,
@.startTime =?,
@.endTime = ?

This is the syntax i use, in the parameters tab of the DFT I ensured that all the parameters are correctly mapped.

When I run the package, it executes successfully but no rows are fectched. I tried running the stored proc manually in the database, and it seems to work fine.

Am I missing something here ? Please Advice

Thanks in Advance

I am sure it is a type issue. SSIS has a VERY VERY irritating feature of not telling you it can't convert your var to the SQL type you set in the parameters section, it just ignores it and sets it to nothing.

Try setting your vars to "String" types and your parameters in the task to "VARCHAR". I bet it will work.

You might also try setting vars inside the SQL to the ?. I have had issues where it doesn't like ? in certain places.

DECLARE @.count INT, @.stime datetime, @.etime datetime
SET @.count = ?
SET @.stime = ?
SET @.etime = ?

EXEC [dbo].[ETLloadGROUPS]
@.countRun =@.count,
@.startTime =@.stime,
@.endTime = @.etime|||

Tom,

Thanks for the quick response. but guess am into a soup here... I have done the following in the parameters tab of the Execute SQL Task.

varName Direction Datatype ParaName

user::countRun Input varchar 0

user:endDate input varchar 1

user:runDate Input varchar 2

In the result set , I have done the following,

Result Name variable Name

0 user::countRun

1 user:endDate

2 user:runDate

al the three variables are of the datatype String.

when I execute the package its now failing with the error, the type of the value assigned to the variable differs from the current datatype. I guess the values from the table which are int and date are not accepted in this parameter mapping. How to handle this?

Thanks for the help so far

|||It sounds like you don't have dates in the strings. What are the values of the parameters you are passing.

Try this:

SET @.count = CAST(? AS INT)
SET @.stime = CAST(? AS DATETIME)
SET @.etime = CAST(? AS DATETIME)

You could run the SQL Profiler and capture exactly the command it is running.

|||

When you say "no rows are fetched" how are you determining this?

what are you doing with the results of the sqltask?

|||

Hi Jeff,

My whole idea is to query a table, get three values onto three variables, pass these values to a stored procedure and then get the entire set of records returned by the proc and then insert it to another OLE DB Destination.

|||

Tom Phillips wrote:

It sounds like you don't have dates in the strings. What are the values of the parameters you are passing.

Try this:

SET @.count = CAST(? AS INT)
SET @.stime = CAST(? AS DATETIME)
SET @.etime = CAST(? AS DATETIME)

You could run the SQL Profiler and capture exactly the command it is running.

I am selecting all the three values from a table and then passing them to a stored procedure.

|||

Hi All,

I have solved all the issues, now at the last summit though its giving me an error with the OLE DB Source component where am calling the stored proc. The erorr is, A rowset based on the SQL Command was not returned by the OLE DB Provider.

Any idea to resolve this ?

Thanks in advance.

|||adding "SET NOCOUNT ON" at the start of the stored proc resolved the issue. Thx for all the help :)