date problem : dynamic SQL in an OLE DB Source component

Hello,

I have an OLEDB Source component with a Oracle OLEDB connection manager.
In my SQL statement I must do something like this ...

SELECT * FROM OracleTable
WHERE convert(datetime, OracleDate) = parameter

I've dynamicaly build the statement like this : http://blogs.conchango.com/jamiethomson/archive/2005/12/09/2480.aspx

Unfortunately this doesn't work because the variable is a datetime value and not a string.

Maybe another way is to store the date in a table in my SQL database and include it in my Oracle SQL statement ? This would mean that there are multiple connection managers being used in one SQL statement ... I have no idea if this would be possible ...

Any other options ?

Yes that would be possible but is a far far more complicated solution than it needs to be.

Keep persuing the expression solution. If the variable is a DateTime then you need to cast it as a string. Remember - the expression is populating a string property so it should return a string.

This is a string manipulation problem - pure and simple!

-Jamie

|||thanks, it works ... I now convert the date to integer format and then to string format so it can be used in the dynamic SQL query

Date Parameter in Reports Manager

I have one master report and 4 linked reports. I deployed a report and set defaults for BeginDate and EndDate as =Today(). I have 4 linked reports that I need to change the date parameters for each. For instance the first linked reports needs to be BeginDate =Today() and the EndDate as =Today()+30. So in my linked report, I tried to change the EndDate to =Today()+30 and an error, 'The value provided for the report parameter 'BeginDate' is not valid for its type.'

So I can't put =Today() or =Today()+30 in the parameters once I override the defaults. Can someone tell me what I'm missing here?

Thanks, Iris

Not sure if this will help but,

Try this:

=DateSerial(Year(Now), Month(Now), Day(Now)+30)

Instead of: Today()+30

|||I still got the same error. When I click 'override default' and put in the expression and click apply, I get this error.|||

When you say you have one master report and four linked reports. What does that mean?

The only way I can help you is to understand what your saying. Are you linking from within the master report by setting up four links using text boxes? Are you displaying data in a table and upon clicking on a field you are using the jump to report? What are your parameters initial settings for the StartDate and EndDate . ie(DateTime, String, etc...) I'm not sure just exactly what it is that your doing. If your attempting to pass a parameter value to a sub-report and change the default value, or setting up a subscription and over-riding the default value. Please explain further and I'll try to provide an answer.

|||

I've decided to use 'Available Values' in the Report Parameters area instead. Someone else tested and got the same error I did.

But basically, my idea came from an article located at www.databasejournal.com/features/mssql/article.php/3613751

Thanks,

Iris

Date Parameter in Reports Manager

I have one master report and 4 linked reports. I deployed a report and set defaults for BeginDate and EndDate as =Today(). I have 4 linked reports that I need to change the date parameters for each. For instance the first linked reports needs to be BeginDate =Today() and the EndDate as =Today()+30. So in my linked report, I tried to change the EndDate to =Today()+30 and an error, 'The value provided for the report parameter 'BeginDate' is not valid for its type.'

So I can't put =Today() or =Today()+30 in the parameters once I override the defaults. Can someone tell me what I'm missing here?

Thanks, Iris

Not sure if this will help but,

Try this:

=DateSerial(Year(Now), Month(Now), Day(Now)+30)

Instead of: Today()+30

|||I still got the same error. When I click 'override default' and put in the expression and click apply, I get this error.|||

When you say you have one master report and four linked reports. What does that mean?

The only way I can help you is to understand what your saying. Are you linking from within the master report by setting up four links using text boxes? Are you displaying data in a table and upon clicking on a field you are using the jump to report? What are your parameters initial settings for the StartDate and EndDate . ie(DateTime, String, etc...) I'm not sure just exactly what it is that your doing. If your attempting to pass a parameter value to a sub-report and change the default value, or setting up a subscription and over-riding the default value. Please explain further and I'll try to provide an answer.

|||

I've decided to use 'Available Values' in the Report Parameters area instead. Someone else tested and got the same error I did.

But basically, my idea came from an article located at www.databasejournal.com/features/mssql/article.php/3613751

Thanks,

Iris

date order of Tables Stored Procedures etc in Enterprise Manager

When I view the list of stored procedures, tables, etc. in Enterprise Manager
console, I cannot click the Create Date header and have the list sort in any
meaningful order. This only occurs with registered SQL servers that are
external to my LAN. SQL servers within the LAN work fine. Any ideas on what
might be causing this?
I stopped trying to figure this out long ago (though it is one of the peeves
I mention in http://www.aspfaq.com/2455).
Instead, why don't you create procedures like these, and run them in Query
Analyzer:
CREATE PROCEDURE dbo.ListTables
AS
BEGIN
SET NOCOUNT ON
SELECT o.Name, Owner = u.name, [Create Date] = o.crdate
FROM sysobjects o
INNER JOIN sysusers u
ON o.uid = u.uid
WHERE type = 'u'
ORDER BY o.crdate DESC
END
GO
CREATE PROCEDURE dbo.ListProcedures
AS
BEGIN
SET NOCOUNT ON
SELECT o.Name, Owner = u.name, [Create Date] = o.crdate
FROM sysobjects o
INNER JOIN sysusers u
ON o.uid = u.uid
WHERE type = 'p'
ORDER BY o.crdate DESC
END
GO
http://www.aspfaq.com/
(Reverse address to reply.)
"Bill" <Bill@.discussions.microsoft.com> wrote in message
news:ED0B754B-AF90-4BF9-8725-46ED1CAB3811@.microsoft.com...
> When I view the list of stored procedures, tables, etc. in Enterprise
Manager
> console, I cannot click the Create Date header and have the list sort in
any
> meaningful order. This only occurs with registered SQL servers that are
> external to my LAN. SQL servers within the LAN work fine. Any ideas on
what
> might be causing this?

date order of Tables Stored Procedures etc in Enterprise Manager

When I view the list of stored procedures, tables, etc. in Enterprise Manager
console, I cannot click the Create Date header and have the list sort in any
meaningful order. This only occurs with registered SQL servers that are
external to my LAN. SQL servers within the LAN work fine. Any ideas on what
might be causing this?I stopped trying to figure this out long ago (though it is one of the peeves
I mention in http://www.aspfaq.com/2455).
Instead, why don't you create procedures like these, and run them in Query
Analyzer:
CREATE PROCEDURE dbo.ListTables
AS
BEGIN
SET NOCOUNT ON
SELECT o.Name, Owner = u.name, [Create Date] = o.crdate
FROM sysobjects o
INNER JOIN sysusers u
ON o.uid = u.uid
WHERE type = 'u'
ORDER BY o.crdate DESC
END
GO
CREATE PROCEDURE dbo.ListProcedures
AS
BEGIN
SET NOCOUNT ON
SELECT o.Name, Owner = u.name, [Create Date] = o.crdate
FROM sysobjects o
INNER JOIN sysusers u
ON o.uid = u.uid
WHERE type = 'p'
ORDER BY o.crdate DESC
END
GO
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Bill" <Bill@.discussions.microsoft.com> wrote in message
news:ED0B754B-AF90-4BF9-8725-46ED1CAB3811@.microsoft.com...
> When I view the list of stored procedures, tables, etc. in Enterprise
Manager
> console, I cannot click the Create Date header and have the list sort in
any
> meaningful order. This only occurs with registered SQL servers that are
> external to my LAN. SQL servers within the LAN work fine. Any ideas on
what
> might be causing this?|||Thanks! A lot of good information on your hyperlink.

date order of Tables Stored Procedures etc in Enterprise Manager

When I view the list of stored procedures, tables, etc. in Enterprise Manage
r
console, I cannot click the Create Date header and have the list sort in any
meaningful order. This only occurs with registered SQL servers that are
external to my LAN. SQL servers within the LAN work fine. Any ideas on wha
t
might be causing this?I stopped trying to figure this out long ago (though it is one of the peeves
I mention in http://www.aspfaq.com/2455).
Instead, why don't you create procedures like these, and run them in Query
Analyzer:
CREATE PROCEDURE dbo.ListTables
AS
BEGIN
SET NOCOUNT ON
SELECT o.Name, Owner = u.name, [Create Date] = o.crdate
FROM sysobjects o
INNER JOIN sysusers u
ON o.uid = u.uid
WHERE type = 'u'
ORDER BY o.crdate DESC
END
GO
CREATE PROCEDURE dbo.ListProcedures
AS
BEGIN
SET NOCOUNT ON
SELECT o.Name, Owner = u.name, [Create Date] = o.crdate
FROM sysobjects o
INNER JOIN sysusers u
ON o.uid = u.uid
WHERE type = 'p'
ORDER BY o.crdate DESC
END
GO
http://www.aspfaq.com/
(Reverse address to reply.)
"Bill" <Bill@.discussions.microsoft.com> wrote in message
news:ED0B754B-AF90-4BF9-8725-46ED1CAB3811@.microsoft.com...
> When I view the list of stored procedures, tables, etc. in Enterprise
Manager
> console, I cannot click the Create Date header and have the list sort in
any
> meaningful order. This only occurs with registered SQL servers that are
> external to my LAN. SQL servers within the LAN work fine. Any ideas on
what
> might be causing this?

Date Created

Hi All,
I have recently moved a database from one server to another using the Copy
Database Wizard in Enterprise Manager, all went well, but the database that
I
copied from now has has a date created of the same time more or less by the
second as the new one that I put on the new Server, is this a common thing
when using the Wizard?
Thanks in advance!Yes, becuase it simply does a detach/copy/attach, so all of the meta data
should be exactly the same as well.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Phil" <Phil@.discussions.microsoft.com> wrote in message
news:41F7D7D1-20C0-4CF0-94AB-72CC34221D0D@.microsoft.com...
> Hi All,
> I have recently moved a database from one server to another using the Copy
> Database Wizard in Enterprise Manager, all went well, but the database
> that I
> copied from now has has a date created of the same time more or less by
> the
> second as the new one that I put on the new Server, is this a common thing
> when using the Wizard?
> Thanks in advance!|||Thanks for that Wayne!
"Wayne Snyder" wrote:

> Yes, becuase it simply does a detach/copy/attach, so all of the meta data
> should be exactly the same as well.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Phil" <Phil@.discussions.microsoft.com> wrote in message
> news:41F7D7D1-20C0-4CF0-94AB-72CC34221D0D@.microsoft.com...
>
>

Date Created

Hi All,
I have recently moved a database from one server to another using the Copy
Database Wizard in Enterprise Manager, all went well, but the database that I
copied from now has has a date created of the same time more or less by the
second as the new one that I put on the new Server, is this a common thing
when using the Wizard?
Thanks in advance!
Yes, becuase it simply does a detach/copy/attach, so all of the meta data
should be exactly the same as well.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Phil" <Phil@.discussions.microsoft.com> wrote in message
news:41F7D7D1-20C0-4CF0-94AB-72CC34221D0D@.microsoft.com...
> Hi All,
> I have recently moved a database from one server to another using the Copy
> Database Wizard in Enterprise Manager, all went well, but the database
> that I
> copied from now has has a date created of the same time more or less by
> the
> second as the new one that I put on the new Server, is this a common thing
> when using the Wizard?
> Thanks in advance!
|||Thanks for that Wayne!
"Wayne Snyder" wrote:

> Yes, becuase it simply does a detach/copy/attach, so all of the meta data
> should be exactly the same as well.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Phil" <Phil@.discussions.microsoft.com> wrote in message
> news:41F7D7D1-20C0-4CF0-94AB-72CC34221D0D@.microsoft.com...
>
>

Date Created

Hi All,
I have recently moved a database from one server to another using the Copy
Database Wizard in Enterprise Manager, all went well, but the database that I
copied from now has has a date created of the same time more or less by the
second as the new one that I put on the new Server, is this a common thing
when using the Wizard?
Thanks in advance!Yes, becuase it simply does a detach/copy/attach, so all of the meta data
should be exactly the same as well.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Phil" <Phil@.discussions.microsoft.com> wrote in message
news:41F7D7D1-20C0-4CF0-94AB-72CC34221D0D@.microsoft.com...
> Hi All,
> I have recently moved a database from one server to another using the Copy
> Database Wizard in Enterprise Manager, all went well, but the database
> that I
> copied from now has has a date created of the same time more or less by
> the
> second as the new one that I put on the new Server, is this a common thing
> when using the Wizard?
> Thanks in advance!|||Thanks for that Wayne!
"Wayne Snyder" wrote:
> Yes, becuase it simply does a detach/copy/attach, so all of the meta data
> should be exactly the same as well.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Phil" <Phil@.discussions.microsoft.com> wrote in message
> news:41F7D7D1-20C0-4CF0-94AB-72CC34221D0D@.microsoft.com...
> > Hi All,
> >
> > I have recently moved a database from one server to another using the Copy
> > Database Wizard in Enterprise Manager, all went well, but the database
> > that I
> > copied from now has has a date created of the same time more or less by
> > the
> > second as the new one that I put on the new Server, is this a common thing
> > when using the Wizard?
> >
> > Thanks in advance!
>
>

Date between 1/1/0001 to 31/12/9999 possible in Sql server

I installed SQL Server Express and SQL Server manager, then tried to start manager. SQL Service is running, but SQL Manager attempt to connect to SQL Server instance times-out even though I correctly identify instance as SQLEXPRESS? Everything seems to be normal - both SQL Server and Manager are on a single Windows XP home machine. Any suggestions. ThanksHi,
Try using .\SQLEXPRESS as the instance name...

cheers,
Paul June A. Domag|||Great - It Works! Thanks|||After reading this thread and another one I've tried entering the following in express manager and I still can't connect:

.\MSSQLSERVER
(local)\MSSQLSERVER

I am running SQL Server Express June CTP on a Win2K3 box. SQL Express Manager is also running on the same Win2K3 box.

I have opened all the network protocols for SQL Server Express, I've also enabled the SQL Server browser. There are no firewalls involved. This is probably the most insecure setup, yet I still cannot connect locally

Any ideas?

I followed all the instructions here:
http://download.microsoft.com/download/a/6/3/a63ec922-913e-4228-93e3-970abff3fd66/ReadmeSQLExpMgr.htm
and here
http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx

Nothing seems to work.
|||Hi,
Actually its .\SQLServer not .\MSSQLSERVER.
Have you tried it? got any versions installed before the June CTP?
cheers,
Paul June A. Domag|||Unfortunately that didn't work either.
All I have installed is June CTP on a fresh install of Win2K3 Standard.
In the SQL Server Configuration Manager under "name" it says: SQL Server (MSSQLSERVER).
This would indicate the name of the SQL server is "MSSQLSERVER" correct? I chose "default" instance during the install and that is the name it gave itself.
This is getting frustrating... I thought programming with MS stuff would be easy. I could have connected to MySQL a hundred different ways by now.
|||Just a two cents worth general comment - the MS beta's tend to have problems that the production versions don't - having used SQL Server from the time it was Sybase Server to date I can say it is great software that occassionally suffers from growing pains. PS|||Yeah, I don't expect Beta software to be 100% functional but I do expect the basic functionality to be working and straightforward. Connecting to the server shouldn't be this difficult. Especially if you consider the target market for this product.
|||Hi,
Oops, I guess I had a typo. Its .\SqlExpress, If you choose default instance...
cheers,
Paul June A. Domag|||After all that I accidently stumbled upon a solution.
The only thing that works for my installation is just plain ".\" (no quotes) and nothing else. Go figure. Hopefully this post saves someone else a lot of time.
|||Hi,
Weird... Could you report a bug in Product Feedback? So that Microsoft would be aware of this...

cheers,
Paul June A. Domag|||thanks a lot this helped!!!!!

Paul Domag wrote:

Hi,
Try using .\SQLEXPRESS as the instance name...

cheers,
Paul June A. Domag

|||Thanks!!!! I have been pulling my hair out all night trying to figure out why I couldn't connect. That did the trick. Why doesn't MS tell you about these important details.|||

I'm getting error like

SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.

I want to set it bet 1/1/0001 to 31/12/9999. how to do this?

So do u have any solution over it pls.

I think u have solution over it because I have seen same problem u had .

At last Happy New Year 2006 at this first meet ing with u

Balasaheb

|||Hi,

There are only two datatypes that could handle date and time values in sqlserver. These are DateTime and SmallDateTime. Both of these types cold not handle data between1/1/0001 to 31/12/9999. But you can store these dates as a varchar and just parse the dates to calculate them in your program...

cheers,

Paul June A. Domag