[SQLSTATE 42000] (Error 8525)

Product : SQL2000
Question : [SQLSTATE 42000] (Error 8525)
When run the Management \ Jobs , I get the Error Message [SQLSTATE 42000] (Error 8525)
Please help me to explain the Error Message , thanks a lot
is it talking to SQL 7 Server via linked server as per below by any chance ?
http://support.microsoft.com/default...b;en-us;834849
Andy.
"Pon" <Pon@.discussions.microsoft.com> wrote in message
news:EC00A4A2-5FE7-42E3-B12A-16167980CF65@.microsoft.com...
> Product : SQL2000
> Question : [SQLSTATE 42000] (Error 8525)
> When run the Management \ Jobs , I get the Error Message [SQLSTATE 42000]
(Error 8525)
> Please help me to explain the Error Message , thanks a lot

[F4] properties dialog does not follow convention of defaultvalue <> bold, changedvalue=bo

Just thought I'd point it out as something that needs fixing. This is inconsistent with every other MS product using the VS.net IDE framework.

I'd post it on Connect but then I'd wait 2 months to get "won't fix - this does not 'fit' with the current Katmai schedule" i.e. if I'm lucky (based on the new improved 3yr delivery of sql) it might get delivered in 2011. Wow.

Imagine if Xbox or PS3 designers told their customers, "sorry we know that up/down/left/right are in fact right/left/down/up, as you may expect on a game controller, but if you wait 3 years we might fix it"

I'm tired of your attitude regarding Connect. That is the means we have in place right now. Submit the Connect bug and move on. At least then it is flagged.|||
Adolf,

I would suggest to open a a report in connect and to place the link in this thread; that way others can add their vote if they want. I agree on something, requests at connect sometimes do not get the attention we would want, but you always have the opportunity of replying back.|||

Rafael Salas wrote:

Adolf,

I would suggest to open a a report in connect and to place the link in this thread; that way others can add their vote if they want. I agree on something, requests at connect sometimes do not get the attention we would want, but you always have the opportunity of replying back.

And at least there is an official record of the bug. That is the most important part.

@working_directory

We are trying to create generic scripts to enable/configure replication at
various customer sites that purchase our product. As I have no way of
knowing which drive they will use for data and log file storage I would like
to set the @.working_directory parameter for sp_adddistpublisher to null.
Will this cause the default UNC (\\<servername>\<drive letter>$\Program
Files\Microsoft SQL Server\MSSQL\ReplData) to be used?
Thanks,
Mark
Hi Mark, the SQL2005 version of sp_adddistpublisher will provide the
behavior that you want but unfortunately the change has not been (and will
unlikey be) backported to any versions of SQL2000. In SQL2005, we use the
following xp_instance_regread call to find out the root data folder path of
SQL Server so you may be able to do something similar:
EXECUTE @.retcode = master.dbo.xp_instance_regread
'HKEY_LOCAL_MACHINE',
'SOFTWARE\Microsoft\MSSQLServer\Setup',
'SQLDataRoot',
@.param = @.working_directory OUTPUT,
@.no_output = 'no_output'
Hope that helps.
-Raymond
"mrprice" <mrprice@.discussions.microsoft.com> wrote in message
news:22060AF0-414E-4BA0-8C98-FA643FCFB83C@.microsoft.com...
> We are trying to create generic scripts to enable/configure replication at
> various customer sites that purchase our product. As I have no way of
> knowing which drive they will use for data and log file storage I would
> like
> to set the @.working_directory parameter for sp_adddistpublisher to null.
> Will this cause the default UNC (\\<servername>\<drive letter>$\Program
> Files\Microsoft SQL Server\MSSQL\ReplData) to be used?
> Thanks,
> Mark
>
|||Raymond,
As it needs to be a UNC, I'm doing this? Look reasonable?
DECLARE @.retcode INT
DECLARE @.pubworkingdir SYSNAME
EXECUTE @.retcode = master.dbo.xp_instance_regread
'HKEY_LOCAL_MACHINE',
'SOFTWARE\Microsoft\MSSQLServer\Replication',
'WorkingDirectory',
@.pubworkingdir OUTPUT,
'no_output'
SET @.pubworkingdir = '\\' + @.@.SERVERNAME + '\' + REPLACE(@.pubworkingdir,
':', '$')
Thanks,
Mark
|||You may want to use serverproperty('MachineName') instead of @.@.servername to
get the "real" server name. Other than that, your code snippet looks fine to
me (you should, of course, test it extensively in your environment...)
"mrprice" <mrprice@.discussions.microsoft.com> wrote in message
news:060F1CA5-EDCE-4EC8-824A-CD5F12F05D21@.microsoft.com...
> Raymond,
> As it needs to be a UNC, I'm doing this? Look reasonable?
> DECLARE @.retcode INT
> DECLARE @.pubworkingdir SYSNAME
> EXECUTE @.retcode = master.dbo.xp_instance_regread
> 'HKEY_LOCAL_MACHINE',
> 'SOFTWARE\Microsoft\MSSQLServer\Replication',
> 'WorkingDirectory',
> @.pubworkingdir OUTPUT,
> 'no_output'
> SET @.pubworkingdir = '\\' + @.@.SERVERNAME + '\' + REPLACE(@.pubworkingdir,
> ':', '$')
> Thanks,
> Mark

@local_var really cannot hold table name ?

Hi,
I have tables named like ag97a027, ag98a027, ... where 97 and 98 is
from year 1997, 1998 and a027 is just specification of product type. I
need carry out repeated operataions over these tables. To generate
their names is easy, for example

use [P5-01]
declare @.Yint int, @.Y char(2), @.tabName char(8)
set @.Yint = 1997
while @.Yint < 2002
begin
set @.tabName = 'ag' + substring(convert(char(4),@.Yint),3,2)+'a027'
print @.tabName
set @.Yint = @.Yint + 1
/*
select *
from @.tabName
*/
end

prints properly

ag97a027
ag98a027
ag99a027
ag00a027
ag01a027

but when I uncomment the select statement, MS-SQL server responds:
Server: Msg 137, Level 15, State 2, Line 11
Must declare the variable '@.tabName'.

How to use @.local_variable for browsing through tables in above
described way?

thanks
MartinOn 23 May 2004 23:10:43 -0700, Martin Mrazek wrote:

>Hi,
>I have tables named like ag97a027, ag98a027, ... where 97 and 98 is
>from year 1997, 1998 and a027 is just specification of product type. I
>need carry out repeated operataions over these tables. To generate
>their names is easy, for example
>use [P5-01]
>declare @.Yint int, @.Y char(2), @.tabName char(8)
>set @.Yint = 1997
>while @.Yint < 2002
>begin
> set @.tabName = 'ag' + substring(convert(char(4),@.Yint),3,2)+'a027'
> print @.tabName
> set @.Yint = @.Yint + 1
>/*
> select *
> from @.tabName
>*/
>end
>prints properly
>ag97a027
>ag98a027
>ag99a027
>ag00a027
>ag01a027
>but when I uncomment the select statement, MS-SQL server responds:
>Server: Msg 137, Level 15, State 2, Line 11
>Must declare the variable '@.tabName'.
>How to use @.local_variable for browsing through tables in above
>described way?
>thanks
>Martin

Hi Martin,

The error message is caused because SQL Server accepts either a literal
table name or a table variable in the from clause. The @. denotes the next
thing as a variable, so SQL Server will try to find a table variable
called @.tabName, which it can't find.

The best answer to your question is: change your table design. Generally
speaking, 99 out of 100 cases where each year's data is stored in a
seperate table are better off storing everything in one table (adding a
"year" column if there isn't already a "date" column that can serve this
purpose), or using a two table design (one "current" table and one
"history" table), again adding a "year" column if needed.

If you feel you must do it this way, google this newsgroup for "dynamic
SQL". That's the kludge that will do what you're trying to achieve.

(I assume that the above SQL is used only to illustrate your problem and
that you're not actually using "select *" in production code or printing
all data in all tables - who would ever want to read that much output??).

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||mrazek@.compik.fd.cvut.cz (Martin Mrazek) wrote in message news:<dcd39e84.0405232210.5a75aeb6@.posting.google.com>...
> Hi,
> I have tables named like ag97a027, ag98a027, ... where 97 and 98 is
> from year 1997, 1998 and a027 is just specification of product type. I
> need carry out repeated operataions over these tables. To generate
> their names is easy, for example
> use [P5-01]
> declare @.Yint int, @.Y char(2), @.tabName char(8)
> set @.Yint = 1997
> while @.Yint < 2002
> begin
> set @.tabName = 'ag' + substring(convert(char(4),@.Yint),3,2)+'a027'
> print @.tabName
> set @.Yint = @.Yint + 1
> /*
> select *
> from @.tabName
> */
> end
> prints properly
> ag97a027
> ag98a027
> ag99a027
> ag00a027
> ag01a027
> but when I uncomment the select statement, MS-SQL server responds:
> Server: Msg 137, Level 15, State 2, Line 11
> Must declare the variable '@.tabName'.
> How to use @.local_variable for browsing through tables in above
> described way?
> thanks
> Martin

You can use dynamic SQL for this (see below), but read this first to
understand why you probably shouldn't:

http://www.sommarskog.se/dynamic_sql.html#Sales_yymm

Simon

declare @.tblname sysname
set @.tblname = 'MyTable'
exec('select * from ' + @.tblname)|||Martin Mrazek (mrazek@.compik.fd.cvut.cz) writes:
> How to use @.local_variable for browsing through tables in above
> described way?

The answer is you don't. Well, you can if you go over hops of dynamic
SQL, but you should really only have one table, with year and product
types as keys in your one single table.

If you have some application which really requires all these tables,
then set up a view over all the tables, with year and product type
added:

CREATE VIEW all_my_tables (year, product_type, col1, col2, col3, ...) AS
SELECT '1998', 'a027', col1, col2, col3,
FROM ag98a027
UNION ALL
SELECT '1999', 'a027', col1, col2, col3,
FROM ag99a027
UNION ALL
...

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

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