<Newbie> Problem with Server-Name in Connect to Remote Server

Dear group,

kindly please answer me my newbie question :-)

I want to connect to a remote SQL-Server using sp_addlinkedservers and
"openquery". I tried and tried and I couldn't get it to work. Then i
found out, that it works with a different server which doesn't have
the '-' character within its servername.

So

sp_addlinkedserver 'ULI-SERVER'
select * from openquery(uli-server, 'select * from
northwind.dbo.sometable')

yields a syntax error ("Incorrect syntax near '-'")

sp_addlinkedserver 'someotherserver'
select * from openquery(someotherserver, 'select * from
northwind.dbo.sometable')

is working

Am I doing something wrong ? Is there a workaround ? Do I have to
rename servers which would be tedious for us.

Thanks for any hints

Greetings from Vienna

UliUli (uli2003wien@.lycos.at) writes:
> kindly please answer me my newbie question :-)
> I want to connect to a remote SQL-Server using sp_addlinkedservers and
> "openquery". I tried and tried and I couldn't get it to work. Then i
> found out, that it works with a different server which doesn't have
> the '-' character within its servername.
> So
> sp_addlinkedserver 'ULI-SERVER'
> select * from openquery(uli-server, 'select * from
> northwind.dbo.sometable')
> yields a syntax error ("Incorrect syntax near '-'")
> sp_addlinkedserver 'someotherserver'
> select * from openquery(someotherserver, 'select * from
> northwind.dbo.sometable')
> is working
>
> Am I doing something wrong ? Is there a workaround ? Do I have to
> rename servers which would be tedious for us.

- is not a legal character in identifiers. Whence the syntax error.

Fortunately, the rememdy is simple:

select * from openquery([uli-server],
'select * from northwind.dbo.sometable')

Whenever you have a name in SQL Server which does not adhere to the
grammar for identifiers, but the name in brackets. (Or in double quotes,
if you want stick to ANSI standards.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

'sys.sp_dbcmptlevel' can only be executed at the ad hoc level

hi

i want to change the Compatibility level of the database(currently in use),autocommit is ON

as

EXEC sp_dbcmptlevel DM, 90 (OK in SQL-server 2005, no error)

Now when execute this command in my application(build using Centura) throws error as

Microsoft SQL Server:15432[Microsoft][ODBC SQL Server Driver][SQL Server]Stored procedure 'sys.sp_dbcmptlevel' can only be executed at the ad hoc level.

I check the docs, what i find this Error comes only if u try to execute this in other procedure or uncommited Transation.

there is no such thing i already checked that then

What this error mean?

any hint?

Gurpreet S. Gill

I don't know what Centura is or how it works. You are most probably using some construct that results in the use of sp_executesql to run the statement from the client like:

exec sp_executesql N'EXEC sp_dbcmptlevel DM, 90';

This will happen in ADO.NET for example if you use a parameterized statement. You need to use direct execution mode of operation. So check your documentation on how to run SQL statements in direct execution mode. You can also trace the calls from your application to see the command it is sending and that should give you a clue.

|||

I don't think that's it, Umachandar. I think it's just what he said - he wrote a proc to exec sp_dbcmptlevel and it throws the error if it's inside another proc. Run this and you'll see what I mean:

CREATE PROC dbo.testing (@.DbName SYSNAME) AS

EXEC sys.sp_dbcmptlevel @.dbname=@.DbName, @.new_cmptlevel=90

GO

EXEC testing Adventureworks

Msg 15432, Level 16, State 1, Procedure sp_dbcmptlevel, Line 28

Stored procedure 'sys.sp_dbcmptlevel' can only be executed at the ad hoc level.

If you try to execute it using EXEC() or sp_executesql, it still throws the same error:

CREATE PROC dbo.testing (@.DbName SYSNAME) AS

DECLARE @.DynamicSql NVARCHAR(255)

SET @.DynamicSql = 'EXEC sys.sp_dbcmptlevel @.dbname=' + @.DbName + ', @.new_cmptlevel=90'

EXEC (@.DynamicSql)

GO

EXEC testing Adventureworks

|||

The problem is this part of code of sp_dbcmptlevel:

if (@.@.nestlevel > 1)

begin

raiserror(15432,-1,-1,'sys.sp_dbcmptlevel')

return (1)

end

And BOL says: "

When @.@.NESTLEVEL is executed within a Transact-SQL string, the value returned is 1 + the current nesting level. When @.@.NESTLEVEL is executed dynamically by using sp_executesql the value returned is 2 + the current nesting level.

"

How it is possible to bypass this check? Why does Microsoft this check?