Passing NULL-value into package variables

We have a package with a package variable.
This variable is of data-type 'DateTime'.
However, when i try to pass the value 'NULL' the package fails... i use the following statement with 'dtexec.exe'

/SET \Package.Variables[MyDate].Value;"NULL"

What's the correct syntax for passing null-values? But maybe (because i cannot find anything on this) i should ask if this is even possible...

I don't think you can pass nulls through the commandline dtexec or dtexecui simply because a DBNull is an object. One option you can pursue is to pass it using a console app written in VB or C#.

This link shows one of the ways of doing that.|||

Dennis_v_E wrote:

We have a package with a package variable.
This variable is of data-type 'DateTime'.
However, when i try to pass the value 'NULL' the package fails... i use the following statement with 'dtexec.exe'

/SET \Package.Variables[MyDate].Value;"NULL"

What's the correct syntax for passing null-values? But maybe (because i cannot find anything on this) i should ask if this is even possible...

Its not possible.

One way around it may be to have a boolean variable called IsDatetimeNull which you set to TRUE or FALSE from the command-line.

Then, you put an expression on your datetime variable which sets it to NULL(DT_DBTIMESTAMP) if IsDatetimeNull==TRUE.

Something like that anyway. You get the idea.

-Jamie

|||

Thanx,

To bad it cannot be done simple. But i get the idea.
Maybe i put a feature request in Connect.

Dennis

sql

Passing in Date Parameters

I have a stored procedure that accepts a date value.

Create Procedure spTest as
@.DateFrom DateTime,
@.DateTo DateTime
/* Procedure Logic Not Shown */
GO

I'm trying to pass in a date generated from Functions.

EXECUTE spTest Month(GetDate()) + '/1/2003', '2/2/2003'

When I run the Stored Procedure I get an "error near Month" but when I hard code in a date things work fine. i.e. '1/1/2003'. Any ideas?From the code taht you presented to us, I see two problems:

1)
Create Procedure spTest as
@.DateFrom DateTime,
@.DateTo DateTime
/* Procedure Logic Not Shown */
GO

should be like this:
Create Procedure spTest @.DateFrom DateTime,
@.DateTo DateTime as
/* Procedure Logic Not Shown */
GO

but this one I think it's just a type error when you posted your question, because you said that the sp worked when you passed the parameters hardcoded. The sp would never work (and never compile) in the form mentioned in your post

2)
Instead of:
EXECUTE spTest Month(GetDate()) + '/1/2003', '2/2/2003'
I would use:
EXECUTE spTest '' & Month(GetDate()) & '/1/2003', '2/2/2003'

Good luck!
ionut calin|||EXECUTE spTest '' & Month(GetDate()) & '/1/2003', '2/2/2003'

I paired up the quotes and the above line doesn't seem to work. Also tried:

EXECUTE spTest '' & Month(GetDate()) & '/1/2003''', '2/2/2003'
EXECUTE spTest Month(GetDate()) & '/1/2003', '2/2/2003'
EXECUTE spTest Month(GetDate()) + '/1/2003', '2/2/2003'

Just in case it was a data conversion issue I tried the following as well.

EXECUTE spTest CONVERT(VARCHAR(2), Month(GetDate)) + '/1/2003', '9/1/2003'

passing datetime variables into a bcp statement

Hi

I posted a question a while back about passing dates through a BCP SQL statement and received the answer that they should look as follows

declare @.sql as varchar(1000)

select @.sql = 'bcp "Exec CHC_Data_V2..TestSP ''05/01/07'', ''01/01/07''" queryout "c:\entitytext.txt" -SAJR\SQLEXPRESS -T -c -t'

exec master..xp_cmdshell @.sql

Now I need to do it differently and I have declared date variables and set the values and now i want to place the varaible names into the statement but i am receiving errors such as cannot convert character to datetime and once again i am looking for the correct way to type the bcp statement

I have the following example

Declare @.EndDate Datetime

Declare @.StartDate DateTime

Declare @.FilePath varchar (250)

Declare @.ServerName varchar (250)

Declare @.sql varchar(8000)

SET @.EndDate = '05/01/2007'

SET @.StartDate = '06/01/2007'

SET @.FilePath = 'C:\test.txt'

SET @.ServerName = 'SQLEXPRESSSERVERPATH'

select @.sql = 'bcp "Exec CHC_Data_V2..CHC_PRSACursor @.EndDate, @.StartDate " queryout "' + @.FilePath + '" -S' + @.ServerName + ' -T -c -t "|"'

exec master..xp_cmdshell @.sql

I have tried

select @.sql = 'bcp "Exec CHC_Data_V2..CHC_PRSACursor '' + @.EndDate+ '', '' + @.StartDate + ''" queryout "' + @.FilePath + '" -S' + @.ServerName + ' -T -c -t "|"'

And many many other variations but am mystified as to the correct format.

Can anyone help?

Syvers

Try:

select @.sql = 'bcp "Exec CHC_Data_V2..CHC_PRSACursor ' + @.EndDate + ', ' + @.StartDate + ' " queryout "' + @.FilePath + '" -S' + @.ServerName + ' -T -c -t "|"'

exec master..xp_cmdshell @.sql

|||

Code Snippet

select @.sql = 'bcp "Exec CHC_Data_V2..TestSP ''' + convert(varchar(10), @.EndDate, 101) + ''', '''+ convert(varchar(10), @.StartDate, 101) + '''" queryout "c:\entitytext.txt" -SAJR\SQLEXPRESS -T -c -t'

|||

Thanks Dale, my thinking was not on all cylinders this morning -had to rush out for a meeting.

|||

Team work!

|||Thank you for your help, works great now.

Passing datetime variable to stored proc as parameter

Hello,

I'm attempting to pass a datetime variable to a stored proc (called via sql task). The variables are set in a previous task where they act as OUTPUT paramters from a stored proc. The variables are set correctly after that task executes. The data type for those parameters is set to DBTIMESTAMP.

When I try to exectue a similar task passing those variables as parameters, I get an error:

Error: 0xC002F210 at ax_settle, Execute SQL Task: Executing the query "exec ? = dbo.ax_settle_2 ?, ?,?,3,1" failed with the following error: "Invalid character value for cast specification". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

If I replace the 2nd and 3rd parameters with quoted strings, it is successful:
exec ?= dbo.ax_settle ?, '3/29/06', '4/30/06',3,1

The stored proc is expecting datetime parameters.

Thanks for the help.

Mike

As a stopgap, I'm building the exec statement in an expression - converting the dates to single quoted strings.

I would still like to see the correct way to pass date parameters, so if anybody can help it would be much appreciated.

Thankee.

Mike

|||

Any chance your passing the value in as type "DBTimestamp"? Try changing the parameter to type DATE

This looks like a bug to me:

setup:

1. Extract date from query (SSIS will show type as DBTIMESTAMP..)

2. Pass that same date taken from SQL Server as a parameter of type DBTimestamp back into the same SQL Server (using same connection) and it will error with message like you have above.

I tried changing the stored procedure to take nvarchar(2000) so I could see what it was passing for the DBTimestamp parameter to SQL and it was passing '' (empty set.. not to be confused with NULL)

Passing datetime parameters to Reporting Services

Hi all...
How should I pass datetime parameters to a report in order for it to work?
This is the current scenario:
Dataset has, for example, this query:
SELECT *
FROM POLIZA
WHERE FECHA BETWEEN ? AND ?
where FECHA is of DATETIME type in an Oracle Database.
Both unnamed parameters are DateTime report parameters, which retrieve the
correct date values (using code embedded in the report)
When I run the report, I get the typical error message when date format is
incorrect:
"a non-numeric character was found where a numeric was expected"
It seems that report tries to pass the "-" of the date value to the unnamed
parameter.
Formerly I had this query that made the report to work:
SELECT *
FROM POLIZA
WHERE FECHA BETWEEN TO_DATE(?, 'DD-MM-YYYY') AND TO_DATE(?, 'DD-MM-YYYY')
In that case, parameters where of String type (forced to have 'dd/mm/yyyy'
format). But using this way I can have (and I already had) problems with
incompatibilities in Regional Settings where the Reporting Server is
installed. For example, using this latter way, I forced the date to have the
format dd/mm/yyyy (ex. 31/08/2005). In some server where Reporting Services
is installed, regional settings are different, so Report crashed because it
sent 08/31/2005 where database (in different server) expected 31/08/2005.
That's why I need to make it independent of regional settings of the target
server.
Any way to manage this?
Thanks a lot in advance
JaimeJaime
I believe this is an issue with the data being returned from the Query.
Not the rendering of the report.
If you run the report in the data tab and scroll to the bottom do you
get the same error?
You might want to take a look at the function Isdate(). Be advised this
will return valid dates so it will filter out any bad dates.
Sounds like someone put a character string in a date field. Does your
database allow this? Like an ASAP or somthing?
Leo
Jaime Stuardo wrote:
> Hi all...
> How should I pass datetime parameters to a report in order for it to work?
> This is the current scenario:
> Dataset has, for example, this query:
> SELECT *
> FROM POLIZA
> WHERE FECHA BETWEEN ? AND ?
> where FECHA is of DATETIME type in an Oracle Database.
> Both unnamed parameters are DateTime report parameters, which retrieve the
> correct date values (using code embedded in the report)
> When I run the report, I get the typical error message when date format is
> incorrect:
> "a non-numeric character was found where a numeric was expected"
> It seems that report tries to pass the "-" of the date value to the unnamed
> parameter.
> Formerly I had this query that made the report to work:
> SELECT *
> FROM POLIZA
> WHERE FECHA BETWEEN TO_DATE(?, 'DD-MM-YYYY') AND TO_DATE(?, 'DD-MM-YYYY')
> In that case, parameters where of String type (forced to have 'dd/mm/yyyy'
> format). But using this way I can have (and I already had) problems with
> incompatibilities in Regional Settings where the Reporting Server is
> installed. For example, using this latter way, I forced the date to have the
> format dd/mm/yyyy (ex. 31/08/2005). In some server where Reporting Services
> is installed, regional settings are different, so Report crashed because it
> sent 08/31/2005 where database (in different server) expected 31/08/2005.
> That's why I need to make it independent of regional settings of the target
> server.
> Any way to manage this?
> Thanks a lot in advance
> Jaime
>|||Thanks Leo for answering...
When using the Data tab and run the query, I have to enter the date this
way:
30-apr-05 and query returns results.
The date field in database is correct, since as I told, when I use
TO_DATE(xxxx, 'DD-MM-YYYY') report works, defining parameters as Strings,
not DateTime as it should be. I cannot use the parameter as String as I
explained in my first post. In conclusion, the problem is only related to
passing parameters between Reporting Services and Oracle database.
Those DateTime parameters are calculated, for example, using this custom code:
Function LastDay(ByVal iYear As Integer, ByVal iQuarter As Integer) As
DateTime
Return DateSerial(iYear, 4 * iQuarter + 5, 1).AddDays(-1)
End Function
Finally, I'm going to tell you that I could display parameters just before
rendering the report (I'm using API to do it) and these are :
CUATRIMESTRE=1
AÃ?O=2005
NUMERO_POLIZA=5506666
FECHA_INICIO=5/1/2005 12:00:00 AM
FECHA_TERMINO=8/31/2005 12:00:00 AM
CUA_MESES=May - August
CUATRIMESTRE, AÃ?O and NUMERO_POLIZA are parameters the user enters.
FECHA_INICIO and FECHA_TERMINO are calculated parameters depending on
CUATRIMESTRE parameter, and CUA_MESES is a calculated parameter depending on
FECHA_INICIO and FECHA_TERMINO.
It's clear that Reporting Service is passing the datetime parameter formated
in a manner that isn't accepted by Oracle. I thought date time would be
passed as is, as a DATE datatype.
Any further help would be greately appreciated,
Thanks
Jaime
"cte25117@.centurytel.net" wrote:
> Jaime
> I believe this is an issue with the data being returned from the Query.
> Not the rendering of the report.
> If you run the report in the data tab and scroll to the bottom do you
> get the same error?
> You might want to take a look at the function Isdate(). Be advised this
> will return valid dates so it will filter out any bad dates.
> Sounds like someone put a character string in a date field. Does your
> database allow this? Like an ASAP or somthing?
> Leo
>
>
> Jaime Stuardo wrote:
> > Hi all...
> >
> > How should I pass datetime parameters to a report in order for it to work?
> > This is the current scenario:
> >
> > Dataset has, for example, this query:
> > SELECT *
> > FROM POLIZA
> > WHERE FECHA BETWEEN ? AND ?
> >
> > where FECHA is of DATETIME type in an Oracle Database.
> >
> > Both unnamed parameters are DateTime report parameters, which retrieve the
> > correct date values (using code embedded in the report)
> >
> > When I run the report, I get the typical error message when date format is
> > incorrect:
> > "a non-numeric character was found where a numeric was expected"
> >
> > It seems that report tries to pass the "-" of the date value to the unnamed
> > parameter.
> >
> > Formerly I had this query that made the report to work:
> > SELECT *
> > FROM POLIZA
> > WHERE FECHA BETWEEN TO_DATE(?, 'DD-MM-YYYY') AND TO_DATE(?, 'DD-MM-YYYY')
> >
> > In that case, parameters where of String type (forced to have 'dd/mm/yyyy'
> > format). But using this way I can have (and I already had) problems with
> > incompatibilities in Regional Settings where the Reporting Server is
> > installed. For example, using this latter way, I forced the date to have the
> > format dd/mm/yyyy (ex. 31/08/2005). In some server where Reporting Services
> > is installed, regional settings are different, so Report crashed because it
> > sent 08/31/2005 where database (in different server) expected 31/08/2005.
> >
> > That's why I need to make it independent of regional settings of the target
> > server.
> >
> > Any way to manage this?
> > Thanks a lot in advance
> >
> > Jaime
> >
> >
>|||Have you tried CASTing the dates as Integers in the Oracel SQL query?
"Jaime Stuardo" <JaimeStuardo@.discussions.microsoft.com> wrote in message
news:B960FCF6-7317-4197-8B76-55F79733BC71@.microsoft.com...
> Thanks Leo for answering...
> When using the Data tab and run the query, I have to enter the date this
> way:
> 30-apr-05 and query returns results.
> The date field in database is correct, since as I told, when I use
> TO_DATE(xxxx, 'DD-MM-YYYY') report works, defining parameters as Strings,
> not DateTime as it should be. I cannot use the parameter as String as I
> explained in my first post. In conclusion, the problem is only related to
> passing parameters between Reporting Services and Oracle database.
> Those DateTime parameters are calculated, for example, using this custom code:
> Function LastDay(ByVal iYear As Integer, ByVal iQuarter As Integer) As
> DateTime
> Return DateSerial(iYear, 4 * iQuarter + 5, 1).AddDays(-1)
> End Function
> Finally, I'm going to tell you that I could display parameters just before
> rendering the report (I'm using API to do it) and these are :
> CUATRIMESTRE=1
> AÑO=2005
> NUMERO_POLIZA=5506666
> FECHA_INICIO=5/1/2005 12:00:00 AM
> FECHA_TERMINO=8/31/2005 12:00:00 AM
> CUA_MESES=May - August
> CUATRIMESTRE, AÑO and NUMERO_POLIZA are parameters the user enters.
> FECHA_INICIO and FECHA_TERMINO are calculated parameters depending on
> CUATRIMESTRE parameter, and CUA_MESES is a calculated parameter depending on
> FECHA_INICIO and FECHA_TERMINO.
> It's clear that Reporting Service is passing the datetime parameter formated
> in a manner that isn't accepted by Oracle. I thought date time would be
> passed as is, as a DATE datatype.
> Any further help would be greately appreciated,
> Thanks
> Jaime
> "cte25117@.centurytel.net" wrote:
>> Jaime
>> I believe this is an issue with the data being returned from the Query.
>> Not the rendering of the report.
>> If you run the report in the data tab and scroll to the bottom do you
>> get the same error?
>> You might want to take a look at the function Isdate(). Be advised this
>> will return valid dates so it will filter out any bad dates.
>> Sounds like someone put a character string in a date field. Does your
>> database allow this? Like an ASAP or somthing?
>> Leo
>>
>>
>> Jaime Stuardo wrote:
>> > Hi all...
>> >
>> > How should I pass datetime parameters to a report in order for it to work?
>> > This is the current scenario:
>> >
>> > Dataset has, for example, this query:
>> > SELECT *
>> > FROM POLIZA
>> > WHERE FECHA BETWEEN ? AND ?
>> >
>> > where FECHA is of DATETIME type in an Oracle Database.
>> >
>> > Both unnamed parameters are DateTime report parameters, which retrieve the
>> > correct date values (using code embedded in the report)
>> >
>> > When I run the report, I get the typical error message when date format is
>> > incorrect:
>> > "a non-numeric character was found where a numeric was expected"
>> >
>> > It seems that report tries to pass the "-" of the date value to the unnamed
>> > parameter.
>> >
>> > Formerly I had this query that made the report to work:
>> > SELECT *
>> > FROM POLIZA
>> > WHERE FECHA BETWEEN TO_DATE(?, 'DD-MM-YYYY') AND TO_DATE(?, 'DD-MM-YYYY')
>> >
>> > In that case, parameters where of String type (forced to have 'dd/mm/yyyy'
>> > format). But using this way I can have (and I already had) problems with
>> > incompatibilities in Regional Settings where the Reporting Server is
>> > installed. For example, using this latter way, I forced the date to have
>> > the
>> > format dd/mm/yyyy (ex. 31/08/2005). In some server where Reporting Services
>> > is installed, regional settings are different, so Report crashed because it
>> > sent 08/31/2005 where database (in different server) expected 31/08/2005.
>> >
>> > That's why I need to make it independent of regional settings of the target
>> > server.
>> >
>> > Any way to manage this?
>> > Thanks a lot in advance
>> >
>> > Jaime
>> >
>> >

Passing DateTime Parameter to Subreport (Error)

Hi guys,

I have managed to pass a string parameter form the main report to the subreport with no problems, but when i try and use the same method to pass a datetime type, i am issued with the following error messase:

"[rsErrorExecutingSubreport] An error occurred while executing the subreport ‘SubReportName’: The value provided for the report parameter 'MasterDate' is not valid for its type."

I cant see why it work for strings, but not for datetime. Any suggestions will be very much appreciated

Just to clarify, i am using the same dateTime type to define both main and sub report parameters|||Does anybody know why i am getting this error. Your help will be very much appreciated.|||Does anybody know why i am getting this error?

Passing datetime parameter to stored procedure

I want to pass a date parameter to stored procedure which is expecting
smalldatetime. I need to subtract a number of days from the current time and
pass it to the stored procedure. I tried DateAdd("d", -1, Now()). I can
display the the result in a text field on the report but when passing it to
the stored procedure to retrieve data I get and error that it is in bad
format.
Thanks,
AntoninOn May 22, 11:03 pm, "Antonin" <Antonin.Koude...@.fmc.sa.gov.au> wrote:
> I want to pass a date parameter to stored procedure which is expecting
> smalldatetime. I need to subtract a number of days from the current time and
> pass it to the stored procedure. I tried DateAdd("d", -1, Now()). I can
> display the the result in a text field on the report but when passing it to
> the stored procedure to retrieve data I get and error that it is in bad
> format.
> Thanks,
> Antonin
You could do one of a few different things:
- Use the CDate() function to pass it to a stored procedure.
- Redefine the smalldatetime input parameter in the stored procedure
as datetime and manipulate it to be a smalldatetime somewhere else in
the stored procedure
- Try to use Datepart to split the report field and then concatenate
it with '/' and then convert it to a datetime (the stored procedure
might implicitly accept it as a smalldatetime, though I don't recall).
- A combination of the above.
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Thanks Enrique. Would you know the solution for my other problem?
When I try to deploy a report I get this error:
The underlying connection was closed: Could not establish trust
relationship for the SSL/TLS secure channel.
The same error I get when I try to open
http://localhost/Reports/Pages/Folder.aspx
Antonin
"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:1179923004.037985.311190@.q69g2000hsb.googlegroups.com...
> On May 22, 11:03 pm, "Antonin" <Antonin.Koude...@.fmc.sa.gov.au> wrote:
>> I want to pass a date parameter to stored procedure which is expecting
>> smalldatetime. I need to subtract a number of days from the current time
>> and
>> pass it to the stored procedure. I tried DateAdd("d", -1, Now()). I can
>> display the the result in a text field on the report but when passing it
>> to
>> the stored procedure to retrieve data I get and error that it is in bad
>> format.
>> Thanks,
>> Antonin
> You could do one of a few different things:
> - Use the CDate() function to pass it to a stored procedure.
> - Redefine the smalldatetime input parameter in the stored procedure
> as datetime and manipulate it to be a smalldatetime somewhere else in
> the stored procedure
> - Try to use Datepart to split the report field and then concatenate
> it with '/' and then convert it to a datetime (the stored procedure
> might implicitly accept it as a smalldatetime, though I don't recall).
> - A combination of the above.
> Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>sql

Passing DateTime Parameter to MDX Query

HI All,
Looking for a little help with this issue: I currently have an MDX
query which is accepting 2 parameters for a date range. The query is
as follows:
SELECT NON EMPTY { [Measures].[Line Item Sales Amount] } ON COLUMNS
FROM ( SELECT ( STRTOMEMBER(@.FromDateDateForReporting, CONSTRAINED) :
STRTOMEMBER(@.ToDateDateForReporting, CONSTRAINED) ) ON COLUMNS FROM
[KIT Retail Sales]) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR,
FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
The parameters pull their respective values from a dataset populated
with this query:
WITH MEMBER [Measures].[ParameterCaption] AS '[Date].[Date For
Reporting].CURRENTMEMBER.MEMBER_CAPTION' MEMBER
[Measures].[ParameterValue] AS '[Date].[Date For
Reporting].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel]
AS '[Date].[Date For Reporting].CURRENTMEMBER.LEVEL.ORDINAL' SELECT
{[Measures].[ParameterCaption], [Measures].[ParameterValue],
[Measures].[ParameterLevel]} ON COLUMNS , [Date].[Date For
Reporting].ALLMEMBERS ON ROWS FROM [KIT Retail Sales]
In the Report Parameters window, I would like these parameters to be
DateTime datatypes so my users would get the handy Calendar control.
The only way I can get them to work now is by setting them to string
datatype. This works, but the dropdown list is populated with
thousands of date strings (in the form 01/01/2005, 01/02/2005, etc...)
which makes it difficult for users.
Whenver I attempt to make them DateTime datatypes, I get the error:
"The Property 'ValidValues' of the report parameter
'FromDateDateForReporting' doesn't have the expected type" when I run
the report.
My question is: How can I do the conversion to get the datetime back
to a string to use in the first MDX query above?Quick followup: In the report parameters window, if I choose to make
the available values "Non-queried", I can see the calendar control on
the preview page. However when choosing dates for the range, and then
clicking "view report", I get the following error:
The restrictions imposed by the CONSTRAINED flag in the STRTOMEMBER
function have been violated.|||Guys-
This appears to work fine. However, now go add a column to your main query
(the one with the parameter) and now your report parameter is trashed because
SSRS decided it needed to refresh it. Anybody know of a way to tell it to
leave that parameter alone?
"Bret Updegraff" wrote:
> This is the method from my class file. It is in C# so if you are using
> VB.net or if you are putting this in the Code tab under Report-->
> Properties you will need to convert to VB.NET
> public string ParseDateToMDX( DateTime sDate )
> {
> string year = sDate.Year.ToString();
> string month = sDate.Month.ToString();
> string day = sDate.Day.ToString();
> StringBuilder MDXValue = new StringBuilder();
> if (month.Length == 1)
> {
> month = "0" + month; //pad month
> }
> if (day.Length == 1)
> {
> day = "0" + day; //pad day
> }
> MDXValue.Append("[PostingDate].[Date].&[").Append(year.ToString()).Append("-");
> MDXValue.Append(month.ToString()).Append("-");
> MDXValue.Append(day.ToString()).Append("T00:00:00]");
> return MDXValue.ToString();
> }
> Hope this helps
> "dvdastor@.yahoo.com" <dvdastor@.yahoo.com> wrote in message
> news:1137680384.850501.248380@.g49g2000cwa.googlegroups.com:
> > Quick followup: In the report parameters window, if I choose to make
> > the available values "Non-queried", I can see the calendar control on
> > the preview page. However when choosing dates for the range, and then
> > clicking "view report", I get the following error:
> > The restrictions imposed by the CONSTRAINED flag in the STRTOMEMBER
> > function have been violated.
>
> --
> Bret Updegraff, MCAD,MCSD,MCDBA
> Microsoft MVP - SQL Server
> Crowe Chizek and Company LLC
> President - Indianapolis Professional Association for SQL Server
> Join our SQL Server Community http;//www.IndyPASS.org
> 317.208.2538 - FAX (317.706.2660) -BUpdegraff@.CroweChizek.com
>|||I hear this may be fixed in SP1:
http://prologika.com/CS/forums/thread/1069.aspx
"FurmanGG" wrote:
> Guys-
> This appears to work fine. However, now go add a column to your main query
> (the one with the parameter) and now your report parameter is trashed because
> SSRS decided it needed to refresh it. Anybody know of a way to tell it to
> leave that parameter alone?
> "Bret Updegraff" wrote:
> > This is the method from my class file. It is in C# so if you are using
> > VB.net or if you are putting this in the Code tab under Report-->
> > Properties you will need to convert to VB.NET
> >
> > public string ParseDateToMDX( DateTime sDate )
> > {
> > string year = sDate.Year.ToString();
> > string month = sDate.Month.ToString();
> > string day = sDate.Day.ToString();
> > StringBuilder MDXValue = new StringBuilder();
> >
> > if (month.Length == 1)
> > {
> > month = "0" + month; //pad month
> > }
> > if (day.Length == 1)
> > {
> > day = "0" + day; //pad day
> > }
> >
> > MDXValue.Append("[PostingDate].[Date].&[").Append(year.ToString()).Append("-");
> > MDXValue.Append(month.ToString()).Append("-");
> > MDXValue.Append(day.ToString()).Append("T00:00:00]");
> >
> > return MDXValue.ToString();
> > }
> >
> > Hope this helps
> >
> > "dvdastor@.yahoo.com" <dvdastor@.yahoo.com> wrote in message
> > news:1137680384.850501.248380@.g49g2000cwa.googlegroups.com:
> >
> > > Quick followup: In the report parameters window, if I choose to make
> > > the available values "Non-queried", I can see the calendar control on
> > > the preview page. However when choosing dates for the range, and then
> > > clicking "view report", I get the following error:
> > > The restrictions imposed by the CONSTRAINED flag in the STRTOMEMBER
> > > function have been violated.
> >
> >
> > --
> > Bret Updegraff, MCAD,MCSD,MCDBA
> > Microsoft MVP - SQL Server
> > Crowe Chizek and Company LLC
> > President - Indianapolis Professional Association for SQL Server
> > Join our SQL Server Community http;//www.IndyPASS.org
> > 317.208.2538 - FAX (317.706.2660) -BUpdegraff@.CroweChizek.com
> >
> >

Pass datetime as parameter to SP

my SP like this
CREATE PROCEDURE dbo.sel_apinv_nonsettle
@.cocode varchar(10),
@.billcode varchar(10),
@.issuedate datetime,
@.branchid varchar(10),
@.accttype varchar(10)
IN SQL Analyzer,
exec sel_apinv_nonsettle 'CNC','CNC',getdate,'HLSHK','APINV'
and i got this error "Server: Msg 8114, Level 16, State 4, Procedure
sel_apinv_nonsettle, Line 0
Error converting data type nvarchar to datetime."
What's wrong 'Hi
Probably you need to put it this way
IN SQL Analyzer,
exec sel_apinv_nonsettle 'CNC','CNC',getdate(),'HLSHK','APINV'
getdate() is a function
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.examnotes.net/gurus/default.asp?p=4223
---
"Agnes" wrote:

> my SP like this
> CREATE PROCEDURE dbo.sel_apinv_nonsettle
> @.cocode varchar(10),
> @.billcode varchar(10),
> @.issuedate datetime,
> @.branchid varchar(10),
> @.accttype varchar(10)
> IN SQL Analyzer,
> exec sel_apinv_nonsettle 'CNC','CNC',getdate,'HLSHK','APINV'
> and i got this error "Server: Msg 8114, Level 16, State 4, Procedure
> sel_apinv_nonsettle, Line 0
> Error converting data type nvarchar to datetime."
> What's wrong '
>
>|||Stored Proc Parameters can only be constants, or T-SQL Variables... You cnno
t
directly pass an expression, or a function, to a stored proc... And even if
you could, the function getdate would require the open closed parenethses at
the end -- getdate()...
What you need to do is create a T-SQL Variable, set it to be equal to
getdate(), and then pass that variable.
Declare @.GDT DateTime
Set @.GDT = getdate() -- ("Current_TimeStamp" is ANSI-SQL Standard)
exec sel_apinv_nonsettle 'CNC','CNC',@.GDT,'HLSHK','APINV'
exec sel_apinv_nonsettle 'CNC','CNC',getdate,'HLSHK','APINV'
"Agnes" wrote:

> my SP like this
> CREATE PROCEDURE dbo.sel_apinv_nonsettle
> @.cocode varchar(10),
> @.billcode varchar(10),
> @.issuedate datetime,
> @.branchid varchar(10),
> @.accttype varchar(10)
> IN SQL Analyzer,
> exec sel_apinv_nonsettle 'CNC','CNC',getdate,'HLSHK','APINV'
> and i got this error "Server: Msg 8114, Level 16, State 4, Procedure
> sel_apinv_nonsettle, Line 0
> Error converting data type nvarchar to datetime."
> What's wrong '
>
>