failed to search using d.DateCreated >='05/07/2007' AND d.DateCreated<= '05/07/2007'

hi all programmers.
i want to find the record that created on only 05/07/2007
the datetime in my database is 5/7/2007 4:09:00 PM
i used this (d.DateCreated >='05/07/2007' AND d.DateCreated<= '05/07/2007' ) inside the where clause.
but it returns no result.
please help me on this issue. thanks for all helps.

Try this

d.DateCreated >='05/07/2007' AND d.DateCreated< '06/07/2007'

|||

Or, to avoid ambiguity caused by your connection's language settings, you might want to use the following date format:

yyyy-mm-dd hh:mi: ss.mmm

<ignore the space after mi: - excluding it from forum posts results in this: mi >

e.g.

d.DateCreated >= '2007-07-05 00:00:00.000' AND d.DateCreated < '2007-07-06 00:00:00.000'

or

d.DateCreated >= '2007-05-07 00:00:00.000' AND d.DateCreated < '2007-05-08 00:00:00.000'

(depending on the format of the date that you oiginally provided)

Chris

|||try this one

CONVERT(smalldatetime,CONVERT(varchar(10),d.DateCreated,101)) >=CONVERT(smalldatetime,'05/07/2007') AND
CONVERT(smalldatetime,CONVERT(varchar(10), d.DateCreated,101))<= CONVERT(smalldatetime,'05/07/2007')|||The where clause you specified is only looking for EXACTLY midnight, 5/07/2007 12:00am. If you want to search for anything on 5/7 use:

DateCreated >='5/7/2007' AND DateCreated < '5/8/2007'

Failed to load database information

I have Crystal Report 9 installed. I created rpt in VS2003 and set database as a dataset. Also, I developed a VS2003 dll to print report.

When I called the dll in VS2005, always got the error as the title: Failed to load database information. Even I made up datasource in dll, no luck. So there must be something wrong while calling 2003 dll from vs2005.

Any idea?

If I add CrystalDecisions.CrystalReport.Engine and CrystalDecisions.Shared in vs2005 project as reference, but the version is 9.2.3300.0. I add some codes to print report:

ReportDocument rpt = new ReportDocument();
rpt.Load(@."D:\VS 2005 Projects\CallPrintCrystalReport\CallPrintCrystalReport\CrystalReport1.rpt");
rpt.SetDataSource(CreateDS());
rpt.PrintToPrinter(1, false, 0, 0);
rpt.Close();

Same error!!! Does this mean CrystalReport 9.2.3300 can not be called by VS2005?Probably not, since CR of VS2005 is based-on version XI.|||Thank you. I just wanna make sure about it.sql

Failed to connect to sql server

Hi folks,

I′m brazilian and new in SQL Server.

Now i′m justing tryng to create a instance to use with WSUS.
I Already created but when i try to install the WSUS and its try to connect into my new instance, i received the message:
Failed to connect to sql server ... Class not registered.
Could you help me?

Regards,

Andre de CastroCan you copy-paste the complete text of the error message which you received? What is WSUS?

Thanks
Laurentiu

failed to connect to new instance in MSSQL2005

I tried to connect to a new created instance (specifying server\instance)in
MSSQL2005 server with mangement studio in another mssql2005 server. It show
an error
'An error has occurred while establishing a connection to the server. When
connecting to SQL Server 2005, this failure may be caused by the fact that
under the default settings SQL Server does not allow remote connections.
(provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instanc
e
Specified) (.Net SqlClient Data Provider)'
I have enabled the TCPIP in configuration manager and with allow remote
access enabled.
Any idea on it? Thanks in advance!Hello,
could be the following:
http://support.microsoft.com/defaul...kb;en-us;307197
regards,
Henk
"stephanie" <stephanie@.discussions.microsoft.com> wrote in message
news:2BD93101-CA8A-47A4-A766-DDC090C9AD5D@.microsoft.com...
>I tried to connect to a new created instance (specifying server\instance)in
> MSSQL2005 server with mangement studio in another mssql2005 server. It
> show
> an error
> 'An error has occurred while establishing a connection to the server.
> When
> connecting to SQL Server 2005, this failure may be caused by the fact that
> under the default settings SQL Server does not allow remote connections.
> (provider: SQL Network Interfaces, error: 26 - Error Locating
> Server/Instance
> Specified) (.Net SqlClient Data Provider)'
> I have enabled the TCPIP in configuration manager and with allow remote
> access enabled.
> Any idea on it? Thanks in advance!
>|||Could be blocked by the firewall, particularly if the SQL Server is running
on Windows XP Professional SP1.
Could be unable to get the port number of the named instance because the SQL
Server Browser Service (on the server computer) is not running, or is
blocked by the firewall.
http://msdn2.microsoft.com/en-us/library/ms345318(en-US,SQL.90).aspx has a
step by step review of some common connectivity issues.
--
Rick Byham
MCDBA, MCSE, MCSA
Documentation Manager,
Microsoft, SQL Server Books Online
This posting is provided "as is" with
no warranties, and confers no rights.
"Henk Steunenberg (Ms)" <stjesp@.hotmail.com> wrote in message
news:OFRCwrWLGHA.1180@.TK2MSFTNGP09.phx.gbl...
> Hello,
> could be the following:
> http://support.microsoft.com/defaul...kb;en-us;307197
> regards,
> Henk
> "stephanie" <stephanie@.discussions.microsoft.com> wrote in message
> news:2BD93101-CA8A-47A4-A766-DDC090C9AD5D@.microsoft.com...
>|||Thanks for all's reply.
I have studied through the suggestion material and configured in SQL2005 but
the same error is still occur.
While I telnet to servernname with named instance port #, it success.
Any other advise. Thanks in advance!
"Rick Byham [MS]" wrote:

> Could be blocked by the firewall, particularly if the SQL Server is runnin
g
> on Windows XP Professional SP1.
> Could be unable to get the port number of the named instance because the S
QL
> Server Browser Service (on the server computer) is not running, or is
> blocked by the firewall.
> http://msdn2.microsoft.com/en-us/library/ms345318(en-US,SQL.90).aspx has a
> step by step review of some common connectivity issues.
> --
> Rick Byham
> MCDBA, MCSE, MCSA
> Documentation Manager,
> Microsoft, SQL Server Books Online
> This posting is provided "as is" with
> no warranties, and confers no rights.
> "Henk Steunenberg (Ms)" <stjesp@.hotmail.com> wrote in message
> news:OFRCwrWLGHA.1180@.TK2MSFTNGP09.phx.gbl...
>
>|||I have enabled the service 'SQL Server Browers' and now it works
"stephanie" wrote:
[vbcol=seagreen]
> Thanks for all's reply.
> I have studied through the suggestion material and configured in SQL2005 b
ut
> the same error is still occur.
> While I telnet to servernname with named instance port #, it success.
> Any other advise. Thanks in advance!
> "Rick Byham [MS]" wrote:
>|||Right. The SQL Server Browser Service receives requests for named instances
and responds with the port number that the instance is listening on. Then
the SQL Server Native Client talks directly to the port.
--
Rick Byham
MCDBA, MCSE, MCSA
Documentation Manager,
Microsoft, SQL Server Books Online
This posting is provided "as is" with
no warranties, and confers no rights.
"stephanie" <stephanie@.discussions.microsoft.com> wrote in message
news:146E8DB0-DB1E-4C8B-8DF0-C30737B4692E@.microsoft.com...[vbcol=seagreen]
>I have enabled the service 'SQL Server Browers' and now it works
> "stephanie" wrote:
>|||I have exactly the same problem (see below)
I've enabled TCP/IP, enabled browser service, made sure there is no error in
binding to TCP/IP via the SQL logs. Still I keep getting the same error.
Any clue'
I have the following setup:
1. ASP.NET 2.0 web app hosted on a web server (inetpub directory
hosting physical files of the web site on a different drive name, E:
than the SQL Server Instance, which is on C
2. SQL Server on the same server, with security settings enabling
ASPNET group and groups containing users allowed to interact with the
website added to SQL Server Instance security settings.
3. Impersonation = true, i.e. ASPNET worker process will impersonate
the user who has been authenticated by IIS
I am continiously getting this error:
========================
An error has occurred while establishing a connection to the server.
When connecting to SQL Server 2005, this failure may be caused by the
fact that under the default settings SQL Server does not allow remote
connections. (provider: SQL Network Interfaces, error: 26 - Error
Locating Server/Instance Specified)
The stack trace and event log is at the end of this post.
I tried the following:
1. enabled TCPIP and Pipes for remote connection via the SQL Server
Configuration Manager
2. enabled remote and local connections via the SQL Surface area
Configuration (TCPIP and named pipes)
3. totally disabled the firewall (even if it is enabled, both the
sqlbrowser, and sqlserver are added as exceptions)
4. enabled the SQL Server Browser Service (to start automatically)
I've tried doing everything i came across on the net.
Nobody seems to have a solution but a lot of people have encountered
this problem.
If anybody out there does have a solution, PLEASE reply to this post.
Thanking you in advance
Part of the stack trace:
--
[SqlException (0x80131904): An error has occurred while establishing a
connection to the server. When connecting to SQL Server 2005, this
failure may be caused by the fact that under the default settings SQL
Server does not allow remote connections. (provider: SQL Network
Interfaces, error: 26 - Error Locating Server/Instance Specified)]
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
exception, Boolean breakConnection) +734995
System.Data.SqlClient.TdsParser. ThrowExceptionAndWarning(TdsParserStateO
bje
_ct
stateObj) +188
System.Data.SqlClient.TdsParser.Connect(Boolean& useFailoverPartner,
Boolean& failoverDemandDone, String host, String failoverPartner,
String protocol, SqlInternalConnectionTds connHandler, Int64
timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean
integratedSecurity, SqlConnection owningObject, Boolean aliasLookup)
+820
System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnectio
_n
owningObject, SqlConnectionString connectionOptions, String
newPassword, Boolean redirectedUserInstance) +628
System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdenti
_ty
identity, SqlConnectionString connectionOptions, Object providerInfo,
String newPassword, SqlConnection owningObject, Boolean
redirectedUserInstance) +170
System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOpt
_ions
options, Object poolGroupProviderInfo, DbConnectionPool pool,
DbConnection owningConnection) +130
System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConne
_ction
owningConnection, DbConnectionPool pool, DbConnectionOptions options)
+28
System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection
owningObject) +424
System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection
owningObject) +66
System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection
owningObject) +496
Part of the Event Log (Application)
---
Process information:
Process ID: 5600
Process name: w3wp.exe
Account name: EUSRCMW309\IWAM_EUSRCMW309
Exception information:
Exception type: SqlException
Exception message: An error has occurred while establishing a
connection to the server. When connecting to SQL Server 2005, this
failure may be caused by the fact that under the default settings SQL
Server does not allow remote connections. (provider: SQL Network
Interfaces, error: 26 - Error Locating Server/Instance Specified)
Request information:
Request URL: http://138.85.90.253/psdb/main.aspx
Request path: /psdb/main.aspx
User host address: 138.85.90.253
User: EAMCS\EUSJESadm
Is authenticated: True
Authentication Type: Negotiate
Thread account name: EUSRCMW309\IWAM_EUSRCMW309
"Rick Byham [MS]" wrote:

> Right. The SQL Server Browser Service receives requests for named instance
s
> and responds with the port number that the instance is listening on. Then
> the SQL Server Native Client talks directly to the port.
> --
> Rick Byham
> MCDBA, MCSE, MCSA
> Documentation Manager,
> Microsoft, SQL Server Books Online
> This posting is provided "as is" with
> no warranties, and confers no rights.
> "stephanie" <stephanie@.discussions.microsoft.com> wrote in message
> news:146E8DB0-DB1E-4C8B-8DF0-C30737B4692E@.microsoft.com...
>
>

failed to connect to new instance in MSSQL2005

I tried to connect to a new created instance (specifying server\instance)in
MSSQL2005 server with mangement studio in another mssql2005 server. It show
an error
'An error has occurred while establishing a connection to the server. When
connecting to SQL Server 2005, this failure may be caused by the fact that
under the default settings SQL Server does not allow remote connections.
(provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance
Specified) (.Net SqlClient Data Provider)'
I have enabled the TCPIP in configuration manager and with allow remote
access enabled.
Any idea on it? Thanks in advance!
Hello,
could be the following:
http://support.microsoft.com/default...b;en-us;307197
regards,
Henk
"stephanie" <stephanie@.discussions.microsoft.com> wrote in message
news:2BD93101-CA8A-47A4-A766-DDC090C9AD5D@.microsoft.com...
>I tried to connect to a new created instance (specifying server\instance)in
> MSSQL2005 server with mangement studio in another mssql2005 server. It
> show
> an error
> 'An error has occurred while establishing a connection to the server.
> When
> connecting to SQL Server 2005, this failure may be caused by the fact that
> under the default settings SQL Server does not allow remote connections.
> (provider: SQL Network Interfaces, error: 26 - Error Locating
> Server/Instance
> Specified) (.Net SqlClient Data Provider)'
> I have enabled the TCPIP in configuration manager and with allow remote
> access enabled.
> Any idea on it? Thanks in advance!
>
|||Could be blocked by the firewall, particularly if the SQL Server is running
on Windows XP Professional SP1.
Could be unable to get the port number of the named instance because the SQL
Server Browser Service (on the server computer) is not running, or is
blocked by the firewall.
http://msdn2.microsoft.com/en-us/library/ms345318(en-US,SQL.90).aspx has a
step by step review of some common connectivity issues.
Rick Byham
MCDBA, MCSE, MCSA
Documentation Manager,
Microsoft, SQL Server Books Online
This posting is provided "as is" with
no warranties, and confers no rights.
"Henk Steunenberg (Ms)" <stjesp@.hotmail.com> wrote in message
news:OFRCwrWLGHA.1180@.TK2MSFTNGP09.phx.gbl...
> Hello,
> could be the following:
> http://support.microsoft.com/default...b;en-us;307197
> regards,
> Henk
> "stephanie" <stephanie@.discussions.microsoft.com> wrote in message
> news:2BD93101-CA8A-47A4-A766-DDC090C9AD5D@.microsoft.com...
>
|||Thanks for all's reply.
I have studied through the suggestion material and configured in SQL2005 but
the same error is still occur.
While I telnet to servernname with named instance port #, it success.
Any other advise. Thanks in advance!
"Rick Byham [MS]" wrote:

> Could be blocked by the firewall, particularly if the SQL Server is running
> on Windows XP Professional SP1.
> Could be unable to get the port number of the named instance because the SQL
> Server Browser Service (on the server computer) is not running, or is
> blocked by the firewall.
> http://msdn2.microsoft.com/en-us/library/ms345318(en-US,SQL.90).aspx has a
> step by step review of some common connectivity issues.
> --
> Rick Byham
> MCDBA, MCSE, MCSA
> Documentation Manager,
> Microsoft, SQL Server Books Online
> This posting is provided "as is" with
> no warranties, and confers no rights.
> "Henk Steunenberg (Ms)" <stjesp@.hotmail.com> wrote in message
> news:OFRCwrWLGHA.1180@.TK2MSFTNGP09.phx.gbl...
>
>
|||I have enabled the service 'SQL Server Browers' and now it works
"stephanie" wrote:
[vbcol=seagreen]
> Thanks for all's reply.
> I have studied through the suggestion material and configured in SQL2005 but
> the same error is still occur.
> While I telnet to servernname with named instance port #, it success.
> Any other advise. Thanks in advance!
> "Rick Byham [MS]" wrote:
|||Right. The SQL Server Browser Service receives requests for named instances
and responds with the port number that the instance is listening on. Then
the SQL Server Native Client talks directly to the port.
Rick Byham
MCDBA, MCSE, MCSA
Documentation Manager,
Microsoft, SQL Server Books Online
This posting is provided "as is" with
no warranties, and confers no rights.
"stephanie" <stephanie@.discussions.microsoft.com> wrote in message
news:146E8DB0-DB1E-4C8B-8DF0-C30737B4692E@.microsoft.com...[vbcol=seagreen]
>I have enabled the service 'SQL Server Browers' and now it works
> "stephanie" wrote:

Failed Login SQL Express

Everything works fine with the website I've created except i get a Login failed message every now and then

Failed Login is: NT AUTHORITY\NETWORK SERVICE

Is this to do with the maximum number of connections to the database?

how can i stop this from happening?

AusClans:

Is this to do with the maximum number of connections to the database?

Maybe, but probably not. That would be a different error message. What is the full exception message you get?

Don

Failed Job Message

This is a post
I am in desparate need of some help with SQL Server 2000. I recently created several jobs to run at night to update several tables in my SQL Server. I then realized that I could schedule one job with various steps, so I tried to delete the original jobs
with the stored proc. but could not seem to get it to work. While examining the sysjob table in the msdn database I deleted one of the old jobs. This is where the problem started. I have since deleted the job steps out of the sysjobsteps and the sysjo
bschedule as well as systaskids for that same job. I am still getting the error notification each morning that the job failed Unable to retrieve steps for the certain job. Where is this notification coming from? Because there is no jobs or job steps in
any of the tables. Any help would greatly be appreciated.
There is a bit more that goes on in deleting a job than just
deleting rows from these tables. Jobs can be stored in the
job cache so there are also procedures that check the job
cache and update it if necessary - I think it's through
sp_agent_notify if I remember correctly. There could also be
some other references to the jobs in some of the other job
tables. That's why modifying these tables directly isn't
recommended. You could try restarting SQL Agent if you
didn't do so after modifying the tables but I'd guess you
could still have some other dangling references to the job
which may or may not affect things.
You may want to consider restoring your msdb database from
prior to your modifying the job tables and then delete the
jobs using sp_delete_job which is the supported method of
dropping a job. Using sp_delete_job will handle all the
details, update the job cache if necessary, etc.
-Sue
On Tue, 20 Apr 2004 10:51:04 -0700, "Cartman"
<anonymous@.discussions.microsoft.com> wrote:

>This is a post
>I am in desparate need of some help with SQL Server 2000. I recently created several jobs to run at night to update several tables in my SQL Server. I then realized that I could schedule one job with various steps, so I tried to delete the original job
s with the stored proc. but could not seem to get it to work. While examining the sysjob table in the msdn database I deleted one of the old jobs. This is where the problem started. I have since deleted the job steps out of the sysjobsteps and the sysj
obschedule as well as systaskids for that same job. I am still getting the error notification each morning that the job failed Unable to retrieve steps for the certain job. Where is this notification coming from? Because there is no jobs or job steps i
n any of the tables. Any help would greatly be appreciated.

Failed Job Message

This is a post
I am in desparate need of some help with SQL Server 2000. I recently created several jobs to run at night to update several tables in my SQL Server. I then realized that I could schedule one job with various steps, so I tried to delete the original jobs with the stored proc. but could not seem to get it to work. While examining the sysjob table in the msdn database I deleted one of the old jobs. This is where the problem started. I have since deleted the job steps out of the sysjobsteps and the sysjobschedule as well as systaskids for that same job. I am still getting the error notification each morning that the job failed Unable to retrieve steps for the certain job. Where is this notification coming from? Because there is no jobs or job steps in any of the tables. Any help would greatly be appreciatedThere is a bit more that goes on in deleting a job than just
deleting rows from these tables. Jobs can be stored in the
job cache so there are also procedures that check the job
cache and update it if necessary - I think it's through
sp_agent_notify if I remember correctly. There could also be
some other references to the jobs in some of the other job
tables. That's why modifying these tables directly isn't
recommended. You could try restarting SQL Agent if you
didn't do so after modifying the tables but I'd guess you
could still have some other dangling references to the job
which may or may not affect things.
You may want to consider restoring your msdb database from
prior to your modifying the job tables and then delete the
jobs using sp_delete_job which is the supported method of
dropping a job. Using sp_delete_job will handle all the
details, update the job cache if necessary, etc.
-Sue
On Tue, 20 Apr 2004 10:51:04 -0700, "Cartman"
<anonymous@.discussions.microsoft.com> wrote:
>This is a post
>I am in desparate need of some help with SQL Server 2000. I recently created several jobs to run at night to update several tables in my SQL Server. I then realized that I could schedule one job with various steps, so I tried to delete the original jobs with the stored proc. but could not seem to get it to work. While examining the sysjob table in the msdn database I deleted one of the old jobs. This is where the problem started. I have since deleted the job steps out of the sysjobsteps and the sysjobschedule as well as systaskids for that same job. I am still getting the error notification each morning that the job failed Unable to retrieve steps for the certain job. Where is this notification coming from? Because there is no jobs or job steps in any of the tables. Any help would greatly be appreciated.

Failed Job Message

This is a post
I am in desparate need of some help with SQL Server 2000. I recently create
d several jobs to run at night to update several tables in my SQL Server. I
then realized that I could schedule one job with various steps, so I tried
to delete the original jobs
with the stored proc. but could not seem to get it to work. While examining
the sysjob table in the msdn database I deleted one of the old jobs. This
is where the problem started. I have since deleted the job steps out of the
sysjobsteps and the sysjo
bschedule as well as systaskids for that same job. I am still getting the e
rror notification each morning that the job failed Unable to retrieve steps
for the certain job. Where is this notification coming from? Because there
is no jobs or job steps in
any of the tables. Any help would greatly be appreciated.There is a bit more that goes on in deleting a job than just
deleting rows from these tables. Jobs can be stored in the
job cache so there are also procedures that check the job
cache and update it if necessary - I think it's through
sp_agent_notify if I remember correctly. There could also be
some other references to the jobs in some of the other job
tables. That's why modifying these tables directly isn't
recommended. You could try restarting SQL Agent if you
didn't do so after modifying the tables but I'd guess you
could still have some other dangling references to the job
which may or may not affect things.
You may want to consider restoring your msdb database from
prior to your modifying the job tables and then delete the
jobs using sp_delete_job which is the supported method of
dropping a job. Using sp_delete_job will handle all the
details, update the job cache if necessary, etc.
-Sue
On Tue, 20 Apr 2004 10:51:04 -0700, "Cartman"
<anonymous@.discussions.microsoft.com> wrote:

>This is a post
>I am in desparate need of some help with SQL Server 2000. I recently created sever
al jobs to run at night to update several tables in my SQL Server. I then realized
that I could schedule one job with various steps, so I tried to delete the original
job
s with the stored proc. but could not seem to get it to work. While examini
ng the sysjob table in the msdn database I deleted one of the old jobs. Thi
s is where the problem started. I have since deleted the job steps out of t
he sysjobsteps and the sysj
obschedule as well as systaskids for that same job. I am still getting the
error notification each morning that the job failed Unable to retrieve steps
for the certain job. Where is this notification coming from? Because ther
e is no jobs or job steps i
n any of the tables. Any help would greatly be appreciated.

FAIL TO USE GETDATE() ON A FUNCTION

I have created my function call a getdate(). After running my function I get
the following error message:
INVALID USE OF GETDATE() IN A FUNCTION.
Is there any alternative to this as I need to know the current date in my
function?
Search the archives and you will find workarounds and the reason for this. One workaround is to
create a view in which you call getdate and use that view. But be aware that this makes getdate
non-atomic and non-deterministic.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Comfort P." <comfort.peter@.stcl.com> wrote in message news:u3hj6nu0EHA.3468@.TK2MSFTNGP14.phx.gbl...
> I have created my function call a getdate(). After running my function I get
> the following error message:
> INVALID USE OF GETDATE() IN A FUNCTION.
> Is there any alternative to this as I need to know the current date in my
> function?
>
|||Also see http://www.aspfaq.com/2439
http://www.aspfaq.com/
(Reverse address to reply.)
"Comfort P." <comfort.peter@.stcl.com> wrote in message
news:u3hj6nu0EHA.3468@.TK2MSFTNGP14.phx.gbl...
> I have created my function call a getdate(). After running my function I
get
> the following error message:
> INVALID USE OF GETDATE() IN A FUNCTION.
> Is there any alternative to this as I need to know the current date in my
> function?
>

FAIL TO USE GETDATE() ON A FUNCTION

I have created my function call a getdate(). After running my function I get
the following error message:
INVALID USE OF GETDATE() IN A FUNCTION.
Is there any alternative to this as I need to know the current date in my
function?Search the archives and you will find workarounds and the reason for this. One workaround is to
create a view in which you call getdate and use that view. But be aware that this makes getdate
non-atomic and non-deterministic.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Comfort P." <comfort.peter@.stcl.com> wrote in message news:u3hj6nu0EHA.3468@.TK2MSFTNGP14.phx.gbl...
> I have created my function call a getdate(). After running my function I get
> the following error message:
> INVALID USE OF GETDATE() IN A FUNCTION.
> Is there any alternative to this as I need to know the current date in my
> function?
>|||Also see http://www.aspfaq.com/2439
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Comfort P." <comfort.peter@.stcl.com> wrote in message
news:u3hj6nu0EHA.3468@.TK2MSFTNGP14.phx.gbl...
> I have created my function call a getdate(). After running my function I
get
> the following error message:
> INVALID USE OF GETDATE() IN A FUNCTION.
> Is there any alternative to this as I need to know the current date in my
> function?
>

FAIL TO USE GETDATE() ON A FUNCTION

I have created my function call a getdate(). After running my function I get
the following error message:
INVALID USE OF GETDATE() IN A FUNCTION.
Is there any alternative to this as I need to know the current date in my
function?Search the archives and you will find workarounds and the reason for this. O
ne workaround is to
create a view in which you call getdate and use that view. But be aware that
this makes getdate
non-atomic and non-deterministic.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Comfort P." <comfort.peter@.stcl.com> wrote in message news:u3hj6nu0EHA.3468@.TK2MSFTNGP14.ph
x.gbl...
> I have created my function call a getdate(). After running my function I g
et
> the following error message:
> INVALID USE OF GETDATE() IN A FUNCTION.
> Is there any alternative to this as I need to know the current date in my
> function?
>|||Also see http://www.aspfaq.com/2439
http://www.aspfaq.com/
(Reverse address to reply.)
"Comfort P." <comfort.peter@.stcl.com> wrote in message
news:u3hj6nu0EHA.3468@.TK2MSFTNGP14.phx.gbl...
> I have created my function call a getdate(). After running my function I
get
> the following error message:
> INVALID USE OF GETDATE() IN A FUNCTION.
> Is there any alternative to this as I need to know the current date in my
> function?
>

Fail to start SQL Server 2005 Analysis Services Service

Hi, all experts here,

How can we find the storage location of the first partition of a cube originally when the cube is created? I mean after the cube is created with the cube wizard, we can always add new partitions for a cube with the partition wizard which enables us to select the storage location of the partitions. But how about the first partition of a cube which is created automatically along with the creation of a cube? I need to locate the storage locations of all partitions of a cube to be able to put the OLAP database somewhere with enough space.

I am looking forward to hearing from you shortly and thanks a lot in advance for your kind advices and help.

With best regards,

Yours sincerely,

Open SSMS, connect to your instance, right-click it and select Properties. In the General tab, look for a property called DataDir. Your partitions will be created in a subfolder of that folder. If you open the value dialog on this property, you will be given instructions on how to make additional locations available.

Good luck,
Bryan

|||

You can also view and set this from within BIDS for any partion.

Open up the cube, click on the partitions tab, click on a partition and in the properties window (right click on the partition and select "Properties" if this window is not already visible, in my environment the properties window is always visible in the lower right hand corner). There is a property called StoreageLocation, if you click on the button for this property a dialog pops up that lets you see the default storage location and override it, to set it to a given folder.

You can also change this property at the Cube and MeasureGroup objects, but I think at this level the property only works as a default setting for any new partitions that are created.

|||

Why I am not able to start the Analysis Services Engine again as the error message from event viewer as:

Event Type: Error
Event Source: MSSQLServerOLAPService
Event Category: (289)
Event ID: 0
Date: 04/07/2007
Time: 10:45:48
User: N/A
Computer: computer_name
Description:
The service cannot be started: Message-handling subsystem: The message manager for the default locale cannot be found. The locale will be changed to US English. Message-handling subsystem: The message manager for the default locale cannot be found. The locale will be changed to US English. The following system error occurred: The filename, directory name, or volume label syntax is incorrect. . File system error: Error occurred during the creation of directory: '\\?\'.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

On SQL Server, when I start the Analysis services, the error message is : The SQL Server Analysis Services (MSSQLSERVER) service on local computer started and then stopped. Some services stop automatically if they have no work to do, for example, the performance logs and Alerts service.

Why is that? That is really frustrated and please any of you give me any advices or help on it. Thanks a lot in advance.

With best regards,

Yours sincerely,

|||

Hi, Bryan,

Thank you for your kind advices. But another very weird problem occured now. I am not even able to restart the SQL Server 2005 Analysis Services Service now.

Event Type: Error
Event Source: MSSQLServerOLAPService
Event Category: (289)
Event ID: 0
Date: 04/07/2007
Time: 10:45:48
User: N/A
Computer: computer_name
Description:
The service cannot be started: Message-handling subsystem: The message manager for the default locale cannot be found. The locale will be changed to US English. Message-handling subsystem: The message manager for the default locale cannot be found. The locale will be changed to US English. The following system error occurred: The filename, directory name, or volume label syntax is incorrect. . File system error: Error occurred during the creation of directory: '\\?\'.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

On SQL Server, when I start the Analysis services, the error message is : The SQL Server Analysis Services (MSSQLSERVER) service on local computer started and then stopped. Some services stop automatically if they have no work to do, for example, the performance logs and Alerts service.

I have no idea what is going on and that is pretty frustrated.

I am looking forward to hearing from any of you here for your kind advices and help.

Thanks in advance.

With best regards,

Yours sincerely,

|||

Did you change something?

The error message seems similar to the one you posted on June 13 in PostID 463688. Could this be the same issue?

B.

|||

Hi, Bryan,

Thanks for your reply.

I have no idea what did I change. The only thing I did change was DataDir setting of the server property which I changed to other storage location instead of where the Analysis Services Instance was originally installed.

Does that have something to do with this problem?

Thanks a lot and I am looking forward to hearing from you shortly.

With best regards,

Yours sincerely,

|||

Changing the default DataDir of a server that is in use is a bigger deal than either BOL or the user interface lets on.

Other people have posted about similar issues before: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=500300&SiteID=1

The above posting might give you some ideas as to how to rectify the issue, it might be that the SSAS service account does not have access to the new location.

An alternative approach would be to set the DataDir back to its default setting and to just move specific cubes/partitions to an alternate location.

|||These two threads have been merged as the same question was posted twice

Fail to run report made with report builder

Hi,

I am completely new to Report Builder. I created and deployed a report model and opened the Report Builder, and created a very simple report by dragging a table into the report. When running the report i get this error:

Keyword not supported: 'provider'.
-
Cannot create a connection to data source 'dataSource1'.
-
An error has occurred during report processing.
The data source name that I used in the report model was not called dataSource1.

Approximately the same error is displayed if I deploy (save) the report to the report server:

An error has occurred during report processing.
Cannot create a connection to data source 'dataSource1'.
Keyword not supported: 'provider'.
The report server itself seems to work fine, because I can deploy reports that are created the old way (ie from within BI Development Studio)

I am using the September CTP.

BR
KaDid you create model using Model Designer?

Fail to run as job but success execute in VS2005

hi all,

please help,

i using Execute DTS 2000 Package Task to create a package.dtsx, and execute successful in VS2005

but when i created it as a job and it fail to run.

the package.dtsx is calling a package2000.dts, which having 2 steps to run.

i execute the job by step, and the i found that the 1st job is running successful but the 2nd job was fail.

may i know where can i found the error log? or any where to debug it?

please help......

thanks 1st.....

Hi Khin,

* Note that configuring log for SSIS will not provide the log for Individual steps run in the Execute DTS 2000 Package task.

* Configure a Separate log for the DTS file enclosed by opening it in DTS designer, right click -> Properties ->Logging -> <Give a file Path to Store this log file >.

Thanks

Subhash Subramanyam

fail component error notification

I have created a package which forms data from flat file to sql database.I have a data flow where all my data is processed to destination.if the package fails by any chance i want an email notification with the cause of the error.how can i do this.please let me know..I'd start with Send Mail task and the OnError event handler for the package.|||Will i be able to send mail with the list of errors occured if the do the above|||Can you specify your goal in more detail? Do you want one email with all errors listed? Do you want all errors, or just the primary error that caused the package to fail?|||I want all the errors in one email.|||

This post might help:

http://agilebi.com/cs/blogs/jwelch/archive/2007/05/05/handling-multiple-errors-in-ssis.aspx

I created an example that uses a Script task in the OnError event to collect all the errors, and then uses a second Script task in the OnPostExecute event to process them. You could simply add a Send Mail task to the OnPostExecute to send the email.

fact table design question

I have created a factSales table with dimDate, dimCustomer, dimProduct, dimSalesPerson tables. The dimensions are all joined with surrogate integer identity PK fields which serve as the composite key in the fact table.

It is possible for the same customer to place multiple orders for the same product from the same sales person on the same date. When this happens it seems to me that only the last order will be stored in the fact table. I want to have a row for each order. How would one design a fact table to accomplish this. The only truly unique piece of data from the OLTP is the sales order number.

factSales:

PK DateKey, int

PK CustomerKey, int

PK ProductKey, int

PK SalesPersonKey, int

Amount

UnitCost

Weight

ShippingCost

dimDate

PK DateKey, int, identity

SalesDate

dimCustomer

PK CustomerKey, int, identity

CustomerName

Address

dimProduct

PK ProductKey, int, identity

Product#

ProductName

DimSalesPerson

PK SalesPersonKey, int, idnetity

SalesPersonName

Dept

StartDate

EndDate

Hi,

You'll need to stick another key on the fact to make each row unique. Even if it is something simple like a count

e.g.

FK DateKey, int

FK CustomerKey, int

FK ProductKey, int

FK SalesPersonKey, int

FK OrderNumberOfTheDay, int

measures ...

If possible a time stamp might be another way, I am assuming though that the different orders happen at different times of the day. But basically another key in your ETL process will fix that problem.

Hope that helps,

Matt

|||

Thanks Matt. I was going to just add the sales order # as it is unique but I didn't want to violate and conventions that might cause problems down the line.

Thanks again!

|||

Hi John

Matt is right in that you should simply bring through the Sales Order Number then your DSV design will take care of the aggregation

Alternatively, unless you need to analyse the data by the Sales Order Number, (which some might argue you should use your OLTP for) you could aggregate the facts in your fact table ETL load process. This can increase performance both in processing the cube and queries if you have a lot of data

If you want to use any drillthrough functionality then keep the Sales Order Number in.

HTH

Tim

|||

Thanks Tim.

I had also considered that possibility. My actual OLTP/OLAP is more complex than I showed (I tried to keep it simple for this example). I would have to join several dozen records in order to aggregate the data and I may have a sales order record which does not yet have a ship date which will be the key date slicer. I can load my fact table with a pointer to a 0 date key or just skip SO records without a ship date (my next task to figure out); it would be very complex to aggregate them. Plus I was already storing the SO# in my fact table on the likely probability the users will want to drill back to the original table.

Face problem into Report "ReportViewControl"

We have created report into Sql server 2005 and viewing this from
â'Reportviewcontrolâ' of Visual studio 2005. Now problem is some time report
first page displays only Report header and all data on Second page. Some time
its display report header and report details on same page.
Any one know how to solve this problem
Please help me out from this problem
I have made lots of R&D for it and finally come on conclusion that it is a
bug of control because of following reason
If report page has capacity of 25 records to display then first it tries to
display on single page. Now if we add one record then its display into
another page
In first case when record is too less then its display all record and report
header on same page
In second case if we have more record which can not display together with
Report header on same page then it will display report header only on first
page and report detail row on second page(as its try to fit all record on
single page)
In third case when we have more records which can not fit on single page
then it start to display record from first page (this time it is not possible
to it to fit all record on same page)Sounds alot like you've got the KeepTogether option checked?
Richard
"ecnindia@.newsgroups.nospam"
<ecnindianewsgroupsnospam@.discussions.microsoft.com> wrote in message
news:ED96230E-D761-41DD-9B46-C27C1C2BC883@.microsoft.com...
> We have created report into Sql server 2005 and viewing this from
> "Reportviewcontrol" of Visual studio 2005. Now problem is some time report
> first page displays only Report header and all data on Second page. Some
> time
> its display report header and report details on same page.
> Any one know how to solve this problem
> Please help me out from this problem
> I have made lots of R&D for it and finally come on conclusion that it is a
> bug of control because of following reason
> If report page has capacity of 25 records to display then first it tries
> to
> display on single page. Now if we add one record then its display into
> another page
> In first case when record is too less then its display all record and
> report
> header on same page
> In second case if we have more record which can not display together with
> Report header on same page then it will display report header only on
> first
> page and report detail row on second page(as its try to fit all record on
> single page)
> In third case when we have more records which can not fit on single page
> then it start to display record from first page (this time it is not
> possible
> to it to fit all record on same page)
>|||Thanks Richard
But i have not checked any option like "KeepTogether" or "fit in
one page".
"Richard Coltrane" wrote:
> Sounds alot like you've got the KeepTogether option checked?
> Richard
> "ecnindia@.newsgroups.nospam"
> <ecnindianewsgroupsnospam@.discussions.microsoft.com> wrote in message
> news:ED96230E-D761-41DD-9B46-C27C1C2BC883@.microsoft.com...
> > We have created report into Sql server 2005 and viewing this from
> > "Reportviewcontrol" of Visual studio 2005. Now problem is some time report
> > first page displays only Report header and all data on Second page. Some
> > time
> > its display report header and report details on same page.
> >
> > Any one know how to solve this problem
> > Please help me out from this problem
> >
> > I have made lots of R&D for it and finally come on conclusion that it is a
> > bug of control because of following reason
> >
> > If report page has capacity of 25 records to display then first it tries
> > to
> > display on single page. Now if we add one record then its display into
> > another page
> >
> > In first case when record is too less then its display all record and
> > report
> > header on same page
> >
> > In second case if we have more record which can not display together with
> > Report header on same page then it will display report header only on
> > first
> > page and report detail row on second page(as its try to fit all record on
> > single page)
> >
> > In third case when we have more records which can not fit on single page
> > then it start to display record from first page (this time it is not
> > possible
> > to it to fit all record on same page)
> >
>
>

Extremely slow median measures

Hi

I'm having a problem with extremely slow median measures.

I've created a named set of all record IDs and wrote the measure as Median( [All Records], [Measures].[Age] ). When I drop a dimension into one of the axes, it takes a very long time to calculate the median even at the top level of the hierarchy, and I suspect it's computing the median for all the members of that dimension, even before I've drilled down into them.

Anyone know a better method for this?

Your formula computes Median for all records always, regardless of the selection in Records dimension.|||

Sorry Mosha, I don't quite follow. I need this to be a generic measure that will return the median of any cell in the client browser. Since median cannot be preaggregated, I thought the only way to do this was to take the median of the set of all records in the current cell. The other option I had explored went something like this:

Median (

{ ( Axis(0)(0)( Axis(0)(0).Count - 1 ).Dimension.CurrentMember.All, [All Records].[ ID ].[ ID ] ) },

[Measures].[Age]

)

But this didn't work at all.

|||Sorry, this time I don't quite follow. What exactly do you mean by the following: "I need this to be a generic measure that will return the median of any cell in the client browser". Median of what ? Perha[s you could illustrate with couple of examples.|||

My mistake... Should have said median of a measure (e.g. age) within any cell in the spreadsheet. For example, if I have a 2 x 2 table with Male and Female as columns and marital status Single and Married as rows, I'd be showing the median age in each of the 4 cells. Other times, users would be interested in the median age for other combinations of factors, say Gender and Cancer, or Cancer and Socioeconomic Status, but they shouldn't have to select a different median measure for each combination. Sort of like a percentage/proportion against any dimension selected on the row/column axis, as discussed in the following post:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=720160&SiteID=1

|||

If you are guaranteed to always have two axes, then something like that may work

Median(CrossJoin(Axis(0), Axis(1)), Measures.[Age])

|||

Thanks Mosha, but no luck with that one either. There's probably no other way around this; that is, other than using the Fact Table primary key (Record ID) to select the set of all individual age values.

|||

Then I again don't understand your requirements :( Based on what you wrote before:

> For example, if I have a 2 x 2 table with Male and Female as columns and marital status Single and Married as rows, I'd be showing the median age in each of the 4 cells.

The formula that I wrote computes then median of these 4 cells and places it into each one of these 4 cells. I have verified it with AdventureWorks which has Gender and Marital Status attributes...

extracting results of XML stored proc to file

Hi all, I need a bit of guidance!
I have created a few queries using FOR XML AUTO, but am unsure how to extract them from the results into an XML file
out side of the sql server. is there a way I can make the stored procedure push the result to an xml file on my server? Or do
I have to use ado to extract the data from the server. I would rather push than pull!
thanks in advance!
Mike
There's no easy way to do it using T-SQL (I guess you could write an
extended Stored Proc or something, but it'd be a lot of effort.)
Your best bet is to install SQLXML 3.0
(http://www.microsoft.com/downloads/d...33a9-cf10-4e22
-8004-477098a407ac&displaylang=en) and use a script like the following
example to generate the file. You could then wrap this script up in a SQL
Agent job and execute it from within SQL Server.
Example script:
Const DBGUID_SQL = "{C8B522D7-5CF3-11CE-ADE5-00AA0044773D}"
Const adExecuteStream = 1024
Const adCmdStoredProc = 4
Dim conn
Set conn = CreateObject("ADODB.Connection")
conn.Provider = "SQLXMLOLEDB.3.0"
conn.ConnectionString = "DATA PROVIDER=SQLOLEDB;" & _
"SERVER=localhost;DATABASE=MyDB;INTEGRATED SECURITY=sspi;"
conn.Open
Dim cmd
Set cmd = CreateObject("ADODB.Command")
Set cmd.ActiveConnection = conn
'Set the dialect
cmd.Dialect = DBGUID_SQL
'Specify the root tag
cmd.Properties("xml root") = "QueryResults"
'Create DOMDocument object for results.
Dim xmlDoc
Set xmlDoc= CreateObject("MSXML2.DOMDocument")
'Assign the output stream.
cmd.Properties("Output Stream") = xmlDoc
'Specify the command
cmd.CommandText = "MyStoredProc"
cmd.CommandType = adCmdStoredProc
'Execute the command returning a stream
cmd.Execute, , adExecuteStream
' Save the file
xmlDoc.Save("C:\Results.xml")
MsgBox("File saved")
Hope that helps,
Graeme
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
www.microsoft.com/mspress/books/6137.asp
"Mike UK" <anonymous@.discussions.microsoft.com> wrote in message
news:6DA91318-4FF8-4DF7-B27B-0CDA3A876389@.microsoft.com...
> Hi all, I need a bit of guidance!
> I have created a few queries using FOR XML AUTO, but am unsure how to
extract them from the results into an XML file
> out side of the sql server. is there a way I can make the stored procedure
push the result to an xml file on my server? Or do
> I have to use ado to extract the data from the server. I would rather push
than pull!
> thanks in advance!
> Mike
|||Thanks Graeme, I will certainly give it a go! I must admit I m a little suprised we cant export from a stored proc,
especiall as we can get the results of a FOR XML query into the results pane.
Thanks again for the script!
Mike.
|||Hi Mike,
You can export from an SP! - We're doing it via BCP (bulk copy) and XML templates. It is a bit tedious to write but it has been working very well in our production environment for the past 3 months.
We have one stored proc that contains our FOR XML statement. For the purposes of discussion, I'll call it 'record_return' (in our case, we're using FOR XML EXPLICIT but it would not make a difference using AUTO) and another stored procedure 'launch_record
_return' that calls 'record_return'.
Within launch_record_return we build a dynamic SQL statement and execute the call to BCP. In our case, we are dynamically building the full path and file name for the exported XML.
Example (from within launch_record_return):
-- create full file path for our output XML document
SET @.xmlname = '\'+@.ord+'.xml' (in this case, @.ord is the name of the file which we're generating dynamically)
SET @.finalname = '\xx'+@.ord+'.xml'
SET @.fname=@.fullDirPath+@.xmlname
SET @.fullPathAndFinalName = @.fullDirPath+@.finalname
SET @.fname=@.fullDirPath+@.xmlname
SET @.bcpCommand = 'bcp "EXEC DB_NAME..record_return ' + "'" + @.ord + "'" + '"' +" queryout "
SET @.bcpCommand = @.bcpCommand + @.fname + ' -c -t -r'
SET @.appendCommand = 'copy/b c:\yourdirectory\header.txt+'+@.fname+'+c:\yourdire ctory\footer.txt '+ @.fullPathAndFinalName
-- execute commands
EXEC master..xp_cmdshell @.bcpCommand
EXEC master..xp_cmdshell @.appendCommand
-- delete temp case file
SET @.appendCommand = ' del ' + @.fname
EXEC master..xp_cmdshell @.appendCommand
The header.txt and footer.txt files contain the head of the document because you'll find that BCP does not wrap the XML in a root element.
This code would of course need to be revised to meet your specific requirements but should provide you with some insight on how to go about generating an actual XML file from an SP. We have also setup a DTS job that runs the 'launch' procedure because the
re is a bug (http://support.microsoft.com/default...;en-us;Q275583) that occurs when executing FOR XML statements via ODBC. We found that it was easy to get around this by creating a DTS job and connecting via OLE.
Hope this helps.
Have a great weekend.
Any questions, feel free to email me.
Dee
Denise E. White
IT Developer
Legal Marketing Services
Cheshire, England
|||Hi Denise,
I thank you for this great post. One thing I wanted to note - a way to get your XML Explicit sproc to have a root element without appending text files: start your sproc with a record that from your highest level table that you know occurs only once, per
haps a transaction identifier value, or perhaps a string value like your company name and wrap it as below
SELECT
1 AS Tag,
NULL AS Parent,
TR.TransSetCntrlNmbr AS [ROOT!1!Filler!Hide],
....
|||Thanks.This is a great post.can any one post the complete
code of the procedure?
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Thanks JT...I have been using this technique however, I still include a header and footer file beacause I cannot find a way using this method to have the root element contain a reference to my XSD schema.
Any thoughts?
Denise