sqlserver2000 date: studio

Monday, March 26, 2012

Incorrect syntax near 'REVERT'...message when sending test e-mail

TITLE: Microsoft SQL Server Management Studio
--
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
--
ADDITIONAL INFORMATION:
Incorrect syntax near 'REVERT'. You may need to set the compatibility level
of the current database to a higher value to enable this feature. See help
for the stored procedure sp_dbcmptlevel.
Incorrect syntax near 'REVERT'. (Microsoft SQL Server, Error: 325)
For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=325&LinkId=20476
--
BUTTONS:
OK
--Are you using a database in compatibility mode? Right-click on database,
properties, Options, compatability level drop-down at top of dialog.
"JimCinAlabama" <JimCinAlabama@.discussions.microsoft.com> wrote in message
news:60EF2EE1-2BE2-4D88-9B54-5B0BA3B58AE7@.microsoft.com...
> TITLE: Microsoft SQL Server Management Studio
> --
> An exception occurred while executing a Transact-SQL statement or batch.
> (Microsoft.SqlServer.ConnectionInfo)
> --
> ADDITIONAL INFORMATION:
> Incorrect syntax near 'REVERT'. You may need to set the compatibility
> level
> of the current database to a higher value to enable this feature. See help
> for the stored procedure sp_dbcmptlevel.
> Incorrect syntax near 'REVERT'. (Microsoft SQL Server, Error: 325)
> For help, click:
> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=325&LinkId=20476
> --
> BUTTONS:
> OK
> --
>|||What compatibility level is the database? I assume you have a @.query parm for the email sending, and
it seems that the database you access data from need to be in compat level 90.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"JimCinAlabama" <JimCinAlabama@.discussions.microsoft.com> wrote in message
news:60EF2EE1-2BE2-4D88-9B54-5B0BA3B58AE7@.microsoft.com...
> TITLE: Microsoft SQL Server Management Studio
> --
> An exception occurred while executing a Transact-SQL statement or batch.
> (Microsoft.SqlServer.ConnectionInfo)
> --
> ADDITIONAL INFORMATION:
> Incorrect syntax near 'REVERT'. You may need to set the compatibility level
> of the current database to a higher value to enable this feature. See help
> for the stored procedure sp_dbcmptlevel.
> Incorrect syntax near 'REVERT'. (Microsoft SQL Server, Error: 325)
> For help, click:
> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=325&LinkId=20476
> --
> BUTTONS:
> OK
> --
>|||Master is set to 9.0
Do I have to have all databases set to 9.0?
"Will Alber" wrote:
> Are you using a database in compatibility mode? Right-click on database,
> properties, Options, compatability level drop-down at top of dialog.
> "JimCinAlabama" <JimCinAlabama@.discussions.microsoft.com> wrote in message
> news:60EF2EE1-2BE2-4D88-9B54-5B0BA3B58AE7@.microsoft.com...
> > TITLE: Microsoft SQL Server Management Studio
> > --
> >
> > An exception occurred while executing a Transact-SQL statement or batch.
> > (Microsoft.SqlServer.ConnectionInfo)
> >
> > --
> > ADDITIONAL INFORMATION:
> >
> > Incorrect syntax near 'REVERT'. You may need to set the compatibility
> > level
> > of the current database to a higher value to enable this feature. See help
> > for the stored procedure sp_dbcmptlevel.
> > Incorrect syntax near 'REVERT'. (Microsoft SQL Server, Error: 325)
> >
> > For help, click:
> > http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=325&LinkId=20476
> >
> > --
> > BUTTONS:
> >
> > OK
> > --
> >
>
>|||You'll need the database that is issuing this error to be set to that
compatibility level - note however that this is not without it's (potential)
problems - some SQL Server 2000 features are broken in 2005 compat. level,
so tread carefully!
"JimCinAlabama" <JimCinAlabama@.discussions.microsoft.com> wrote in message
news:FFBDA9F3-BFCA-4DFA-B4F8-93D063C33919@.microsoft.com...
> Master is set to 9.0
> Do I have to have all databases set to 9.0?
> "Will Alber" wrote:
>> Are you using a database in compatibility mode? Right-click on database,
>> properties, Options, compatability level drop-down at top of dialog.
>> "JimCinAlabama" <JimCinAlabama@.discussions.microsoft.com> wrote in
>> message
>> news:60EF2EE1-2BE2-4D88-9B54-5B0BA3B58AE7@.microsoft.com...
>> > TITLE: Microsoft SQL Server Management Studio
>> > --
>> >
>> > An exception occurred while executing a Transact-SQL statement or
>> > batch.
>> > (Microsoft.SqlServer.ConnectionInfo)
>> >
>> > --
>> > ADDITIONAL INFORMATION:
>> >
>> > Incorrect syntax near 'REVERT'. You may need to set the compatibility
>> > level
>> > of the current database to a higher value to enable this feature. See
>> > help
>> > for the stored procedure sp_dbcmptlevel.
>> > Incorrect syntax near 'REVERT'. (Microsoft SQL Server, Error: 325)
>> >
>> > For help, click:
>> > http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=325&LinkId=20476
>> >
>> > --
>> > BUTTONS:
>> >
>> > OK
>> > --
>> >
>>|||I don't know what database it is. I'm simply trying to setup Database Mail
and test. Not really working with a particular database. I just assumed
that it was with the Master. I have about 6 databases on this server. -
Thanks.
"Will Alber" wrote:
> You'll need the database that is issuing this error to be set to that
> compatibility level - note however that this is not without it's (potential)
> problems - some SQL Server 2000 features are broken in 2005 compat. level,
> so tread carefully!
> "JimCinAlabama" <JimCinAlabama@.discussions.microsoft.com> wrote in message
> news:FFBDA9F3-BFCA-4DFA-B4F8-93D063C33919@.microsoft.com...
> > Master is set to 9.0
> > Do I have to have all databases set to 9.0?
> >
> > "Will Alber" wrote:
> >
> >> Are you using a database in compatibility mode? Right-click on database,
> >> properties, Options, compatability level drop-down at top of dialog.
> >>
> >> "JimCinAlabama" <JimCinAlabama@.discussions.microsoft.com> wrote in
> >> message
> >> news:60EF2EE1-2BE2-4D88-9B54-5B0BA3B58AE7@.microsoft.com...
> >> > TITLE: Microsoft SQL Server Management Studio
> >> > --
> >> >
> >> > An exception occurred while executing a Transact-SQL statement or
> >> > batch.
> >> > (Microsoft.SqlServer.ConnectionInfo)
> >> >
> >> > --
> >> > ADDITIONAL INFORMATION:
> >> >
> >> > Incorrect syntax near 'REVERT'. You may need to set the compatibility
> >> > level
> >> > of the current database to a higher value to enable this feature. See
> >> > help
> >> > for the stored procedure sp_dbcmptlevel.
> >> > Incorrect syntax near 'REVERT'. (Microsoft SQL Server, Error: 325)
> >> >
> >> > For help, click:
> >> > http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=325&LinkId=20476
> >> >
> >> > --
> >> > BUTTONS:
> >> >
> >> > OK
> >> > --
> >> >
> >>
> >>
> >>
>
>|||You might also try msdb, I believe this is where all the dbmail stuff lives.
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
"JimCinAlabama" <JimCinAlabama@.discussions.microsoft.com> wrote in message
news:9BC7D1DF-4D4B-4BD6-9256-E02E0E1C970D@.microsoft.com...
>I don't know what database it is. I'm simply trying to setup Database Mail
> and test. Not really working with a particular database. I just assumed
> that it was with the Master. I have about 6 databases on this server. -
> Thanks.|||... and just for the heck of it, while no-one is connected, make sure all is in 90 mode. If it
works, lower one at a time and see which one caused the problems.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OJYKqcCuHHA.4948@.TK2MSFTNGP06.phx.gbl...
> You might also try msdb, I believe this is where all the dbmail stuff lives.
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.sqlblog.com/
> http://www.aspfaq.com/5006
>
> "JimCinAlabama" <JimCinAlabama@.discussions.microsoft.com> wrote in message
> news:9BC7D1DF-4D4B-4BD6-9256-E02E0E1C970D@.microsoft.com...
>>I don't know what database it is. I'm simply trying to setup Database Mail
>> and test. Not really working with a particular database. I just assumed
>> that it was with the Master. I have about 6 databases on this server. -
>> Thanks.
>|||Changing msdb to 9.0 worked.
Thanks.
Problem resolved.
"Tibor Karaszi" wrote:
> ... and just for the heck of it, while no-one is connected, make sure all is in 90 mode. If it
> works, lower one at a time and see which one caused the problems.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:OJYKqcCuHHA.4948@.TK2MSFTNGP06.phx.gbl...
> > You might also try msdb, I believe this is where all the dbmail stuff lives.
> >
> > --
> > Aaron Bertrand
> > SQL Server MVP
> > http://www.sqlblog.com/
> > http://www.aspfaq.com/5006
> >
> >
> >
> > "JimCinAlabama" <JimCinAlabama@.discussions.microsoft.com> wrote in message
> > news:9BC7D1DF-4D4B-4BD6-9256-E02E0E1C970D@.microsoft.com...
> >>I don't know what database it is. I'm simply trying to setup Database Mail
> >> and test. Not really working with a particular database. I just assumed
> >> that it was with the Master. I have about 6 databases on this server. -
> >> Thanks.
> >
> >
>|||> Changing msdb to 9.0 worked.
> Thanks.
> Problem resolved.
Since I was able to reproduce this in Katmai I filed an issue on Connect:
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=284340
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006

Friday, March 23, 2012

Incorrect syntax near ')' SQL 2000

I keep receiving the error message Incorrect syntax near ')' whilst trying to save a stored procedure from within visual studio, I get the same error from within enterprise manager. The procedure is incomplete but valid, any idea appreciated. The procedure follows:

CREATE PROCEDURE dbo.ProcessComment
@.source AS VARCHAR(50) = NULL

AS
SET NOCOUNT ON

DECLARE @.sourceID AS INT
DECLARE @.counter AS INT
DECLARE @.Sources TABLE
(
sourceid AS INT,
lastconversation AS DATETIME
)

SET @.sourceID = -1
SET @.counter = -1

/*We need to see if this is a new or existing source*/
IF NOT @.source = NULL
BEGIN

INSERT @.Sources SELECT sourceID, lastconversation
FROM sources
WHERE name = @.source
ORDER BY lastconversation

--Do we have any matching sources or is this a new source?

--We need to pick the most likely source from the table
--The most likely source will be either one with an open conversation
--or the latest conversation or if we are lucky the only
--one in the result set

END

Thanks

Gav:

These two lines:

sourceid AS INT,
lastconversation AS DATETIME

need to be changed to:

sourceid AS INT,
lastconversation AS DATETIME

The compiler "thinks" that you are defining a computed column when you include the "AS" keyword.

Dave

|||Thanks Mugambo, that sorted me out :)

I wonder where I picked up that 'AS' syntax from in a table declaration, it just rolled off my fingertips onto the keyboard.

Thanks a lot.sql

Friday, March 9, 2012

Inconsistent Linked Server Query Results

Hello,

I have a linked server named 'Charlie_File' to an Excel Workbook that I set up in SQLServer 2005 Management Studio. The workbook is on my local C drive. Sometimes, I get the results back that I expect when I run the following query;

SELECT*FROMOPENQUERY(Charlie_file,'SELECT * FROM [Feb$]')

Sometimes, on subsequent runs of the above query, I get the following message;

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" for linked server "Charlie_file" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "Charlie_file".

There seems to be about a minute or so of a delay before the query will run correctly on subsequent attempts. Is there a connection issue here where a connection blocks subsequent attempts to select the data within a specific time span?

Thank you for your help!

cdun2

It turns out that the problem was with security settings on the linked server. I didn't have a mapping set up between the linked server and the SQL Server login for myself, so under 'For a login not defined in the list above, connections will;' I selected 'Be made using the login's current security context.'

cdun2

Sunday, February 19, 2012

In VS2005 where is Buisness Intelligence Project Option

Dear All,

I am using Visual studio .Net 2005. and i want to create report using sql server reporting services
for sql server 2000.
there is Buisness Intelligence Projects option in Visual studio .Net 2003 but this option
is not present in my vs2005 editor.
where is it exactaly resides or is there any way to do that.

Hi,

VS2005 does not have BI projects. These items are now part of SQL2005. You can install BI items from SQL2005 setup.

IN SQL Server Management Studio - Can connect to database sort of but Icon isn't green anymore

Preface: I'm a newbie at 2005 and sql server in general.

I can connect to a sql server (2005 express) but I can only make views and not run queries. When I open up a new query and create it with query designer and click add table I see no tables, views or anything in the box. When I create a new view in the database I do see tables and views though.

The little circle part of the icon on the server in the left hand pane is clear now when before it was green. I still can connect to the database ok but I can't use the query analyzer for queries anymore. I need to do that because I do have some slow running queries.

~Capt howdy

Select the database first (ie:AdventureWorks), 'new query' and then click design view.

|||I'm such a n00b with sql server it hurts sometimes. Thanks! I am used to access but it is definitely time to step up.

~Capt. Howdy

IN SQL Server Management Studio - Can connect to database sort of but Icon isn't green anym