Not a valid identifier

Hi All,
I am trying to write a query and send the same output thru
xp_sendmail but i get "Server: Msg 203, Level 16, State 2,
Line 15" not a valid identifier.
The query is attached below:-
declare @.VADATABASE varchar(30)
declare @.DBQ varchar(255)
declare @.DT varchar(10)
declare @.code varchar(1)
select @.DT=convert(varchar(10),last_full_dump_dtm,103)
from backup_sched where mon_bkup_cde="S"
set @.VADATABASE='mydb'
select @.VADATABASE
set @.code='S'
select @.DT=convert(varchar(10),last_full_dump_dtm,103)
from backup_sched where mon_bkup_cde=@.code and
db_nme=@.VADATABASE
select @.Dt
set @.DBQ= 'select DBNAME=db_nme,"Dump Status"= case convert
(varchar(10),@.DT,103) when
convert(varchar(10),getdate(),103)
then "Successful Dump" else "Dump Failed" End,
"Dump Date"=last_full_dump_dtm from
backup_sched where Mon_bkup_cde=@.Code --and
db_nme=@.VADATABASE '
exec @.DBQ
You need brackets when you execute a string:
EXEC('@.sql')
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Mohamadi" <anonymous@.discussions.microsoft.com> wrote in message
news:789601c43136$59353230$a401280a@.phx.gbl...
> Hi All,
> I am trying to write a query and send the same output thru
> xp_sendmail but i get "Server: Msg 203, Level 16, State 2,
> Line 15" not a valid identifier.
> The query is attached below:-
> declare @.VADATABASE varchar(30)
> declare @.DBQ varchar(255)
> declare @.DT varchar(10)
> declare @.code varchar(1)
> select @.DT=convert(varchar(10),last_full_dump_dtm,103)
> from backup_sched where mon_bkup_cde="S"
> set @.VADATABASE='mydb'
> select @.VADATABASE
> set @.code='S'
> select @.DT=convert(varchar(10),last_full_dump_dtm,103)
> from backup_sched where mon_bkup_cde=@.code and
> db_nme=@.VADATABASE
> select @.Dt
> set @.DBQ= 'select DBNAME=db_nme,"Dump Status"= case convert
> (varchar(10),@.DT,103) when
> convert(varchar(10),getdate(),103)
> then "Successful Dump" else "Dump Failed" End,
> "Dump Date"=last_full_dump_dtm from
> backup_sched where Mon_bkup_cde=@.Code --and
> db_nme=@.VADATABASE '
> exec @.DBQ
>

Not a valid identifier

Hi All,
I am trying to write a query and send the same output thru
xp_sendmail but i get "Server: Msg 203, Level 16, State 2,
Line 15" not a valid identifier.
The query is attached below:-
declare @.VADATABASE varchar(30)
declare @.DBQ varchar(255)
declare @.DT varchar(10)
declare @.code varchar(1)
select @.DT=convert(varchar(10),last_full_dump_dtm,103)
from backup_sched where mon_bkup_cde="S"
set @.VADATABASE='mydb'
select @.VADATABASE
set @.code='S'
select @.DT=convert(varchar(10),last_full_dump_dtm,103)
from backup_sched where mon_bkup_cde=@.code and
db_nme=@.VADATABASE
select @.Dt
set @.DBQ= 'select DBNAME=db_nme,"Dump Status"= case convert
(varchar(10),@.DT,103) when
convert(varchar(10),getdate(),103)
then "Successful Dump" else "Dump Failed" End,
"Dump Date"=last_full_dump_dtm from
backup_sched where Mon_bkup_cde=@.Code --and
db_nme=@.VADATABASE '
exec @.DBQYou need brackets when you execute a string:
EXEC('@.sql')
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Mohamadi" <anonymous@.discussions.microsoft.com> wrote in message
news:789601c43136$59353230$a401280a@.phx.gbl...
> Hi All,
> I am trying to write a query and send the same output thru
> xp_sendmail but i get "Server: Msg 203, Level 16, State 2,
> Line 15" not a valid identifier.
> The query is attached below:-
> declare @.VADATABASE varchar(30)
> declare @.DBQ varchar(255)
> declare @.DT varchar(10)
> declare @.code varchar(1)
> select @.DT=convert(varchar(10),last_full_dump_dtm,103)
> from backup_sched where mon_bkup_cde="S"
> set @.VADATABASE='mydb'
> select @.VADATABASE
> set @.code='S'
> select @.DT=convert(varchar(10),last_full_dump_dtm,103)
> from backup_sched where mon_bkup_cde=@.code and
> db_nme=@.VADATABASE
> select @.Dt
> set @.DBQ= 'select DBNAME=db_nme,"Dump Status"= case convert
> (varchar(10),@.DT,103) when
> convert(varchar(10),getdate(),103)
> then "Successful Dump" else "Dump Failed" End,
> "Dump Date"=last_full_dump_dtm from
> backup_sched where Mon_bkup_cde=@.Code --and
> db_nme=@.VADATABASE '
> exec @.DBQ
>sql

Not a valid identifier

Hi All,
I am trying to write a query and send the same output thru
xp_sendmail but i get "Server: Msg 203, Level 16, State 2,
Line 15" not a valid identifier.
The query is attached below:-
declare @.VADATABASE varchar(30)
declare @.DBQ varchar(255)
declare @.DT varchar(10)
declare @.code varchar(1)
select @.DT=convert(varchar(10),last_full_dump_d
tm,103)
from backup_sched where mon_bkup_cde="S"
set @.VADATABASE='mydb'
select @.VADATABASE
set @.code='S'
select @.DT=convert(varchar(10),last_full_dump_d
tm,103)
from backup_sched where mon_bkup_cde=@.code and
db_nme=@.VADATABASE
select @.Dt
set @.DBQ= 'select DBNAME=db_nme,"Dump Status"= case convert
(varchar(10),@.DT,103) when
convert(varchar(10),getdate(),103)
then "Successful Dump" else "Dump Failed" End,
"Dump Date"=last_full_dump_dtm from
backup_sched where Mon_bkup_cde=@.Code --and
db_nme=@.VADATABASE '
exec @.DBQYou need brackets when you execute a string:
EXEC('@.sql')
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Mohamadi" <anonymous@.discussions.microsoft.com> wrote in message
news:789601c43136$59353230$a401280a@.phx.gbl...
> Hi All,
> I am trying to write a query and send the same output thru
> xp_sendmail but i get "Server: Msg 203, Level 16, State 2,
> Line 15" not a valid identifier.
> The query is attached below:-
> declare @.VADATABASE varchar(30)
> declare @.DBQ varchar(255)
> declare @.DT varchar(10)
> declare @.code varchar(1)
> select @.DT=convert(varchar(10),last_full_dump_d
tm,103)
> from backup_sched where mon_bkup_cde="S"
> set @.VADATABASE='mydb'
> select @.VADATABASE
> set @.code='S'
> select @.DT=convert(varchar(10),last_full_dump_d
tm,103)
> from backup_sched where mon_bkup_cde=@.code and
> db_nme=@.VADATABASE
> select @.Dt
> set @.DBQ= 'select DBNAME=db_nme,"Dump Status"= case convert
> (varchar(10),@.DT,103) when
> convert(varchar(10),getdate(),103)
> then "Successful Dump" else "Dump Failed" End,
> "Dump Date"=last_full_dump_dtm from
> backup_sched where Mon_bkup_cde=@.Code --and
> db_nme=@.VADATABASE '
> exec @.DBQ
>

Normalization Question Regarding Column Combinations

We need to store land title information about properties in various
Australian states, but each state maintains it's own land title
registry and use different columns (well actually different
combinations of the same columns). For example:

Victoria store:

TorrensUnit
TorrensVolume
TorrensFolio

Queensland store:

TorrensCounty
TorrensLot
TorrensPlan
TorrensParish
TorrensUnit
TorrensVolume
TorrensTitleRef

There are 11 different columns and they are used in 8 different
combinations depending on the state.

Since we need to store information about land in different states I see
two possible solutions:

1. A sparse table containing the 11 columns with a CHECK constraint to
enforce the valid combinations.

2. A table for each state containing only the columns relevant to the
state with a foreign key relationship to the table containing the
common columns.

I'm not sure if the data type and length is consistent between states
yet (waiting to find this out) but assuming that it is which of these
approaches is going to be the most rigorous? I'm leaning towards (2)
but I don't like the feel of a table per state.>From a design standpoint I try to stay away from your first choice if
possible, seems like it'd be difficult to maintain. I like your second
point, but if there is a common set of data that is shared, then
"normalize" that information.

CREATE TABLE Store(
StoreID int IDENTITY(1,1) NOT NULL,
TorrensUnit varchar(50) NOT NULL,
TorrensVolume varchar(50) NOT NULL
)

CREATE TABLE StoreVictoria(
StoreVictoriaID int IDENTITY(1,1) NOT NULL,
StoreID INT NOT NULL, -- FK
--Specific columns
)

CREATE TABLE StoreQueensLand
(
StoreQueensLandID int IDENTITY(1,1) NOT NULL,
StoreID INT NOT NULL, -- FK
TorrensParish varchar(50),
TorrensTitleRef varchar(50)
--Specific columns
)|||On one hand, property does not move from state to state so one table
per state would work and make sense.

But do you ever view the set of all parcels of land in the country as
your unit of work? I woudl go with one table per state and VIEW that
has the global summary information.|||90% of the time we will be dealing with individual properties, there
will be time where we need to show a list of properties that are in
different states so a VIEW would make sense here. Thanks Celko, when's
your SQL Coding Standards book coming out? Looking forward to it.|||>> Thanks Celko, when's your SQL Coding Standards book coming out? Looking forward to it. <<

It has been out for a several weeks now.

http://www.amazon.com/exec/obidos/t...=glance&s=bookssql

noob syntax question

What am I doing wrong?:
DELETE FROM tblmainacs2 WHERE tblmainacs2.IDnum = Meter.id

I get this message:
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Meter.id" could not be bound.

Is Meter a table? If so you do contain an explicit reference to it in the DELETE statement in the FROM clause or part of subquery for example. You can write it like below assuming you want to perform a join:

delete from tblmainacs2

where IDnum in (select id from Meter)

|||Thank you. That query did exaclty what I wanted. Could someone show me some different queries that do exactly the same thing, just for learning purposes. Thanks in advancesql

Non-US address

Someone told me that some non-US address do not have State/Province, and
some might not have Postal Code. Is this true?J wrote:
> Someone told me that some non-US address do not have State/Province, and
> some might not have Postal Code. Is this true?

Yes. And some might have things that aren't in a US address. For example:

3-1, Kudan-minami 1-chome
Chiyoda-ku,
Tokyo, Japan 102-8660

Chiyoda-ku has no US equivalent and not no equivalent for state.
Tokyo is in Kanto but you won't find that in the normal address
listing.
--
Daniel A. Morgan
http://www.psoug.org
damorgan@.x.washington.edu
(replace x with u to respond)|||Check out

http://www.upu.int/post_code/en/pos...countries.shtml|||J (jungnaja@.hotmail.com) writes:
> Someone told me that some non-US address do not have State/Province, and
> some might not have Postal Code. Is this true?

Yes, here in the outside world things are rough. You know, for our tiny
little country it would be preposterous to have a thing like states. Many
US states have a bigger population than we have. Besides, by tradition
this is a centralised country where the national government strongly
regulates what counties and municiplaties should do. In theory they are
free to set their on tax level, but the government meddles there as well.
On top of that, county and municiplatity borders changes sometimes, so
it would not be good for addresses.

We do have postal codes. And to make you feel a little more like home,
they are even five digit ones. However, there are really rough places
like the UK where the postal codes looks entirely different.

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

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

Non-SA can't view Activity Monitor with VIEW SERVER STATE perm.

I have a developer who has dbo privileges in master and msdb, and also has
VIEW SERVER STATE and VIEW ANY DATABASE. According to SQL Server 2005
documentation she should be able to view the Activity Monitor, but instead
gets the following error:
TITLE: Microsoft SQL Server Management Studio
--
Cannot show requested dialog.
ADDITIONAL INFORMATION:
Unable to execute requested command.
Unable to launch Activity Monitor. You may not have sufficient permissions.
(ActivityMonitor)
SELECT permission denied on object 'tables', database 'mssqlsystemresource',
schema 'sys'.
SELECT permission denied on object 'partitions', database
'mssqlsystemresource', schema 'sys'.
Changed database context to '<db1>'.
Changed database context to '<db2>'.
Changed database context to '<db3>'.
Changed database context to '<db4>'.
Changed database context to '<db5>'.
Changed database context to '<db6>'.
Changed database context to 'tempdb'. (Microsoft SQL Server, Error: 229)
It popped up last week, and I read that Reporting Services running may have
an effect on it, so I stopped the service and she was able to get in with no
problem. No changes have been made to the server since I stopped the
service, but this error just started again today. No updates or patches hav
e
been installed on the server recently.
I have tried granting permission to 'tables' and 'partition' for master and
mssqlsystemresource db's, and to individual system tables but I run into
errors every time. I'm pretty new to SQL Server 2005 so please explain
everything in very simple terms!
THANKS!
Details: OS is Windows Server 2003 (clustered) and I have SQL Server 2005 SP
1 installed.Augspies (Augspies@.discussions.microsoft.com) writes:
> I have a developer who has dbo privileges in master and msdb, and also has
> VIEW SERVER STATE and VIEW ANY DATABASE. According to SQL Server 2005
> documentation she should be able to view the Activity Monitor, but instead
> gets the following error:
>...
> SELECT permission denied on object 'tables', database
> 'mssqlsystemresource',
> schema 'sys'.
> SELECT permission denied on object 'partitions', database
> 'mssqlsystemresource', schema 'sys'.
That's a tricky one. It would indicate that there are locks on tables
in msqlsystemresource. And mssqlsystemresource is unavailable to
everyone, including sa, but if you're in the sysadmin role, the
check may be bypassed so you don't see the error.
I don't really know what to suggest, but you could try my aba_lockinfo
which gives about the same information as the Actvity Monitor, but
without any GUI. You can get it from
http://www.sommarskog.se/sqlutil/aba_lockinfo.html.
Maybe I would be more worried over the locks in mssqlsystemresource.
That does not really sound normal to me...
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Non Usesfull error Message

ERROR MSG:

Msg 515, Level 16, State 2, Server SQL\CIS, Procedure ssp_gas_CustomerCancellation_A, Line 161

Cannot insert the value NULL into column '', table ''; column does not allow nulls. INSERT fails.

The statement has been terminated.

Why didn't it actually tell me the Column and table name? i've never seen this before?

Anyone can help

Thanks

Marios

Oh I know this isn't the right forum but i couldn't find the right one to post in.

How is the procedure executed?

Not sure about why the error message is like that. Are there many fields in the table that do not allow nulls, I'd start checking those.

|||

It is called from another Stored Procedure the Stored Procedure that it rasises the error in is about 600lines of code and there are serval places where it Inserts int Tables and Temp Tables. I've looked at the data passed and which fields they are passed in to but i don't see where this error happends. It has only happend in our Production Database i can not so far reproduce it in our development environment. I don't understand why it doesn't show me the column and table

Thanks

Marios

|||

Do you explicity create your temporary tables or create/populate them through select into statments? Maybe that is why it doesn't show the table/column name (the fact that that it is a temporary table).

Nevermind, just tested that and it doesn't seem to be the case.

|||

no i explicitly defne the tables for temp tables before using them and but they are declare

CREATE #TableName

(

)

|||The only other thing I can think of, and I realize it may be impractical due to the large amount of code within the stored procedure is to use try...catch statements.|||i can't because it's a SQL 2000 server

Non Clustered Index

Hi ,
When I creare a non clustered index , I get error as show below ,
Server: Msg 1904, Level 16, State 1, Line 1
Cannot specify more than 16 column names for statistics or index key list.
21 specified.
SQL Server only support up to 16 key values ?
Travis Tan
First off, this question should be posted in
Microsoft.public.sqlserver.programming. Clustering is a technology which
allows individual computers to share the same data and back each other up to
prevent system failures. Clients connect to a virtual server, whose
resources float between nodes which form the cluster.
Clustered indexes are when the data is clustered or grouped in a predefined
format or order for rapid retrieval of ranges of data.
16 columns makes your index very large and I suspect inefficient. You may be
able to use an indexed view to group your data in different orders for your
particular usage. And yes, clustered indexes only support a maximum of 16
columns or keys in SQL 200x.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Travis" <Travis@.discussions.microsoft.com> wrote in message
news:79F89C55-B258-40D3-93EE-B02C12676125@.microsoft.com...
> Hi ,
> When I creare a non clustered index , I get error as show below ,
> Server: Msg 1904, Level 16, State 1, Line 1
> Cannot specify more than 16 column names for statistics or index key list.
> 21 specified.
> SQL Server only support up to 16 key values ?
> --
> Travis Tan
|||Yes. Why are you trying to create an index with 21 columns in it? That is
quite a bit beyond overkill.
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
"Travis" <Travis@.discussions.microsoft.com> wrote in message
news:79F89C55-B258-40D3-93EE-B02C12676125@.microsoft.com...
> Hi ,
> When I creare a non clustered index , I get error as show below ,
> Server: Msg 1904, Level 16, State 1, Line 1
> Cannot specify more than 16 column names for statistics or index key list.
> 21 specified.
> SQL Server only support up to 16 key values ?
> --
> Travis Tan
|||Hi
Wrong newsgroup, crossposted to microsoft.public.sqlserver.programming
Why would you want to create a compound index of 16 or more columns? Your
query has to be very specific to be able to use it and the overhead
maintaining it will be high too.
If you have a table, with columns A-Z and you build and index on A, B, C, D
(in that column sequence), a where clause on A, B, C could use the index, a
query on column B can't, neither can a query on C, D. Column A always has to
be involved as it is the 1st sort sequence for the column.
Maybe the DB design is not optimal if you need to go to such extremes.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Travis" <Travis@.discussions.microsoft.com> wrote in message
news:79F89C55-B258-40D3-93EE-B02C12676125@.microsoft.com...
> Hi ,
> When I creare a non clustered index , I get error as show below ,
> Server: Msg 1904, Level 16, State 1, Line 1
> Cannot specify more than 16 column names for statistics or index key list.
> 21 specified.
> SQL Server only support up to 16 key values ?
> --
> Travis Tan