Invoking or sending SQL queries using dOS

Hello Folks,
I want to create a simple batch DOS script to query mi SQL Server 2000 database. How I could do this?
I just want to run a select for a table but I dont want people to interct with sql query analyzer or enterprise manager to avoid any issues.
Regards,Look up OSQL or ISQL in Books Online.

Basically:
OSQL -S <name of your server/instance> -U <UID> -P <PWD> -d <Database to use> -Q"<your select statment>" -n -b

I do this all the time so post back if you have more questions.|||Originally posted by Paul Young
Look up OSQL or ISQL in Books Online.

Basically:
OSQL -S <name of your server/instance> -U <UID> -P <PWD> -d <Database to use> -Q"<your select statment>" -n -b

I do this all the time so post back if you have more questions.

UID stands for? PWD I assume is the pasword, isnt.

How I can redirect the results to a txt file,

OSQL -S OSQL -S <name of your server/instance> -U <UID> -P <PWD> -d <Database to use> -Q"<your select statment>" -n -b >> query.txt

Am I right?

Thanks for your prompt reply and help|||UID = User ID

You could pipe the output to a text file but the -o parm would be more usefull.|||How I can get the instance name. I have tested in my environment and I got the next error:

[DBNETLIB] Sql Server does not exist or access denied
[DBNETLIB] ConnectionOpen <Connect(())

As per this result I checked the server name I used typing osql -L. I got the names and used them but with the same result.

When I open the Sql query analyzer I connect using windows authentication option to connect to my databases.

Please suggest..|||For Windows Security I think you need to use -E option instead of -U and -P options.

Tim Ssql

INvariant part inside SELECT

All,
Can we do this trick and if yes then how? Just schematically: the SP should
return the number of records if the parameter @.Count=1, if not, then the
records themselves. The problem is that there is some complicated JOIN and
the whole set of WHERE clauses that I wouldn't like to repeat in two
different queries looking almost identically excluding the main SELECT part.
The idea described below doesn't work.
--Parameter
Declare @.Count bit
SET @.Count = 1
SELECT
CASE
WHEN @.Count = 1
THEN pe.*, pn.*
ELSE COUNT(*)
END
...
FROM ...
INNER JOIN ... ON ...
WHERE ...
Any ideas?
Just D.Just D (no@.spam.please) writes:
> Can we do this trick and if yes then how? Just schematically: the SP
> should return the number of records if the parameter @.Count=1, if not,
> then the records themselves. The problem is that there is some
> complicated JOIN and the whole set of WHERE clauses that I wouldn't like
> to repeat in two different queries looking almost identically excluding
> the main SELECT part. The idea described below doesn't work.
The best is probably to put the whole JOIN-WHERE business in an
inline table-valued function. Then the procedure can read:
IF @.count = 1
SELECT COUNT(*) FROM tblfunc(@.par1, @.par2, ...)
ELSE
SELECT col1, col2, ...
FROM tblfunc (@.par1, @.par2, ...)
You could also bounce the data over a temp tble, but that would be more
expensive in terms of performance, not the least for the COUNT. (Since for
the COUNT(*) SQL Server may find a quicker query plan when it does not have
to read all data pages.)
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|||Hi
Using SELECT * in production code is not a good idea. The only way to do
what you are doing without writing the query twice would be to use dynamic
SQL.
John
"Just D" wrote:

> All,
> Can we do this trick and if yes then how? Just schematically: the SP shoul
d
> return the number of records if the parameter @.Count=1, if not, then the
> records themselves. The problem is that there is some complicated JOIN and
> the whole set of WHERE clauses that I wouldn't like to repeat in two
> different queries looking almost identically excluding the main SELECT par
t.
> The idea described below doesn't work.
> --Parameter
> Declare @.Count bit
> SET @.Count = 1
>
> SELECT
> CASE
> WHEN @.Count = 1
> THEN pe.*, pn.*
> ELSE COUNT(*)
> END
> ...
> FROM ...
> INNER JOIN ... ON ...
> WHERE ...
> Any ideas?
> Just D.
>
>|||Erland,
Correct me if I am wrong. I don't see any performance benifit by using the
table valued function over using the actual query, except for the fact that
the stored procedure looks better :)
The execution plan is not stored for the TVF but is stored in the calling
SP. And the plan will be recomplied everytime the condition changes. I would
say it would be better performance wise, if we have two stored procedures on
e
for returning the row count and one for returning the result set and call
these two SPs from the main SP based on the condition.So that only the main
SP will get recompiled and will not be much of an overhead.
If its a query with a simple execution plan, then what you suggest will be
fine.
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/
"Erland Sommarskog" wrote:

> Just D (no@.spam.please) writes:
> The best is probably to put the whole JOIN-WHERE business in an
> inline table-valued function. Then the procedure can read:
> IF @.count = 1
> SELECT COUNT(*) FROM tblfunc(@.par1, @.par2, ...)
> ELSE
> SELECT col1, col2, ...
> FROM tblfunc (@.par1, @.par2, ...)
> You could also bounce the data over a temp tble, but that would be more
> expensive in terms of performance, not the least for the COUNT. (Since for
> the COUNT(*) SQL Server may find a quicker query plan when it does not hav
e
> to read all data pages.)
> --
> 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
>|||>> Just schematically: the SP should return the number of records [sic] if the parame
ter @.Count=1, if not, then the records [sic] themselves. <<
Did you ever have a software engineering course? Remember cohesion?
The idea that a properly designed code module will perform one
well-defined task. Good programmers do not write things that return
the square root of a number or translate Flemish depending on a
parameter.
Why did you make your low-level BIT flag a reserved word? Why are you
thinking in terms of assembly language style flags and variant records
instead of rows?
DECLARE @.Count bit
SET @.Count = 1
SELECT
CASE
WHEN @.Count = 1
THEN pe.*, pn.*
ELSE COUNT(*)
END
..
FROM ...
INNER JOIN ... ON ...
WHERE ... ; <<
CASE is an expression and not a control flow device. You can use an
IF-THEN-ELSE construct in T-SQL to mimic procedural coding with variant
records instead of using declarative coding.
Did you also notice that you want to return one column and then want to
return two columns? Arow in a relational table always has a fixed
number of columns, unlike records in a file. Basically, you are still
writing COBOL or some other procedural file-oriented language, but you
are doing it in SQL.
This is a simple matter of cut & paste, not the end of the world.
However, if you are just looking for a newsgroup kludge instead of a
real answer in one query, try:
SELECT
CASE WHEN @.assembly_language_flag = 1
THEN 'violated cohesion'
ELSE COUNT(*) END AS foobar,
CASE WHEN @.assembly_language_flag = 1
THEN PA.x
ELSE 'violated cohesion' END AS x,
etc.
FROM ..
Boy that is awful, isn't it?|||You're so kind as usual writing that in this style. :) Let me guess, you're
from the Western Ukraine, aren't you?
"--CELKO--" <jcelko212@.earthlink.net> wrote in message

> Did you ever have a software engineering course? Remember cohesion?
> The idea that a properly designed code module will perform one
> well-defined task. Good programmers do not write things that return
Tell that to the MS coders (mostly contractors from India:)) who were
usually adding 20 and more parameters like NULL (reserved) to the method
parameter list.overriding one method tons of times. That was always MS
style.

> the square root of a number or translate Flemish depending on a
> parameter.
Yea-yea, pretty close.
The flame is closed.|||Omnibuzz (Omnibuzz@.discussions.microsoft.com) writes:
> Correct me if I am wrong. I don't see any performance benifit by using
> the table valued function over using the actual query, except for the
> fact that the stored procedure looks better :)
Correct, but the presumption was that Just D wanted to the procedure
to look better. That is, he did not want repeat the conditions. And I can
think of four ways to achieve this aim:
1) view/inlined table function.
2) bounce over temp table.
3) dynamic SQL.
4) pre-processor.
In my post I only discussed the first two options, and of these the
TVF gives better performance than the temp table.
In my opinion, using dynamic SQL introduces another level of complexity
which is not worth the pain in this case.
And preprocessor? Well, we have one in our environment, but most
people doesn't.

> The execution plan is not stored for the TVF but is stored in the calling
> SP. And the plan will be recomplied everytime the condition changes.
As I understood it, the JOIN and WHERE conditions of the query are
stable. As for the condition on whether to return COUNT or result set,
that should lead to any recompilation.
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|||Erland, thanks for your answer.
Yes, the main idea was to make the SP more flexible and maintainable. The
conditions are complex enough to repeat them more than one time, and that's
especially bad if we need to improve/modify them in future, we can easily
make a simple mistake doing that in two places, OR we will have to
copy/paste each time we need to change something. That's why this idea
appeared. But from another side any change like that should not seriously
affect the speed of the code or the whole complexity because having this
divantage we're getting into another trouble which is not acceptable.
That's why I asked this newsgroup for a new, better idea. To implement the
function - then we'll need to maintain this function and provide the
required set of tables and parameters that should be cached in a different
way I guess if we call the function inside our SP. Temporary table - it's
even the worst scenario. Many different ways are able to change the whole
idea and to do one thing crashing all around.
Just D.
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97E0F409CDF8DYazorman@.127.0.0.1...
> Omnibuzz (Omnibuzz@.discussions.microsoft.com) writes:
> Correct, but the presumption was that Just D wanted to the procedure
> to look better. That is, he did not want repeat the conditions. And I can
> think of four ways to achieve this aim:
> 1) view/inlined table function.
> 2) bounce over temp table.
> 3) dynamic SQL.
> 4) pre-processor.
> In my post I only discussed the first two options, and of these the
> TVF gives better performance than the temp table.
> In my opinion, using dynamic SQL introduces another level of complexity
> which is not worth the pain in this case.
> And preprocessor? Well, we have one in our environment, but most
> people doesn't.
>
> As I understood it, the JOIN and WHERE conditions of the query are
> stable. As for the condition on whether to return COUNT or result set,
> that should lead to any recompilation.
>
>
> --
> 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|||Just D (no@.spam.please) writes:
> That's why I asked this newsgroup for a new, better idea. To implement the
> function - then we'll need to maintain this function and provide the
> required set of tables and parameters that should be cached in a different
> way I guess if we call the function inside our SP.
Not really sure what you mean here. An inline-table function does not have
any query plan of its own. An inline table function is really a macro that
the optimizer pastes in before building the query plan. (Note that this
does not apply to multi-statement functions nor to scalar functions.)
As for the maintenance, you would move that to the function. The procedure
would just be a wrapper on the function.
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

Invalide object name 'INSERTED'

In an after insert/update trigger I have the following:
IF UPDATE(CustName)
BEGIN
SET @.iCustID = (SELECT CustID FROM INSERTED)
..
END
It compiles but when I run it, I get a message: "Invalide object name
'INSERTED'"
But if I take the SET out of the IF like this, it works fine:
SET @.iCustID = (SELECT CustID FROM INSERTED)
IF UPDATE(CustName)
BEGIN
..
END
Can someone explain why?
Thanks,
KeithSorry. My mistake. It doesn't work either way. What does work is if I do
this (I mean it runs without errors):
SELECT CustID FROM INSERTED
IF UPDATE(CustName)
BEGIN
..
END
I need to get CustID into a variable so that I can pass it to a stored
procedure as follows:
Keith
IF UPDATE(CustName)
BEGIN
SET @.iCustID = (SELECT CustID FROM INSERTED)
EXEC @.bSomeVar = spTest @.iCustID
..
END|||Strange, never had that. I would have suggested that the problem was case
sensitivity (BOL lists the table as 'inserted', not 'INSERTED') but you say
it works when you move the SET out of the IF block
Even if this worked, you would have a problem anyway if more than 1 row was
updated in one go, because you'd be trying to set a numbers of rows to a
scalar variable.
Dan
Keith wrote on Wed, 26 Apr 2006 11:33:59 -0400:

> In an after insert/update trigger I have the following:
> IF UPDATE(CustName)
> BEGIN
> SET @.iCustID = (SELECT CustID FROM INSERTED)
> ...
> END
> It compiles but when I run it, I get a message: "Invalide object name
> 'INSERTED'"
> But if I take the SET out of the IF like this, it works fine:
> SET @.iCustID = (SELECT CustID FROM INSERTED)
> IF UPDATE(CustName)
> BEGIN
> ...
> END
> Can someone explain why?
> Thanks,
> Keith
>|||Geeze. Never mind. Not enough sleep last night. I moved some code from the
trigger to a stored procedure and didnt' change "INSERTED" to the actual
table name in the stored procedure. The error was there, not in the trigger.
Keithsql

Invalid Udate SQL statement DOES NOT cause error... Does anyone know why?

Here's my update statement:

UPDATE Item1
SET reviewloop = 1, currentreviewstate=5
WHERE itemid in
(SELECT itemid FROM Item2)

The thing is: the table Item2 DOES NOT HAVE a field called itemid.
So, I should receive an error, right? Not so.Instead, every single
record in Item1 was updated.

Does anyone know why SQL Serverr does not trown an error?

Thanks guys,

-Silvio SouzaBecause the sub query can reference fields from the update. itemid in this
case will be retrieved from Item1.|||The rule for subqueries is that a column name that can't be resolved to
column within the subquery is assumed to reference a column in the outer
query. If in doubt, use the two-part column name including the table
name/alias.

--
David Portas
SQL Server MVP
--|||"no spam" <chuck@.sheckmedia.com> wrote in message news:<vCWhc.71068$Lh2.5553@.bignews1.bellsouth.net>...
> Because the sub query can reference fields from the update. itemid in this
> case will be retrieved from Item1.

I don't think so. SQL certainly doesn't say to itself "Since I can't
find that value in Item2 I'll assume that they must mean the value in
Item1" - that would be catastrophic.

I've just tried this myself, and whilst it didn't give any error, it
didn't update any rows in Item1 either. This makes sense, because
the subquery is simply evaluating to FALSE, so 0 rows are updated in
the main query.|||> I don't think so. SQL certainly doesn't say to itself "Since I can't
> find that value in Item2 I'll assume that they must mean the value in
> Item1" - that would be catastrophic.

The problem isn't to do with *values* it's to do with resolution of *column
names*. Substitute the word "column" for "value" and your statement
describes exactly what SQL does.

Assuming the column Itemid doesn't exist in Item2, the UPDATE statement you
posted is equivalent to:

UPDATE Item1
SET reviewloop = 1, currentreviewstate=5
WHERE Item1.itemid IN
(SELECT Item1.itemid FROM Item2)

As long as there is at least one row in Item2, every row in Item1 should get
updated.

--
David Portas
SQL Server MVP
--|||Any field reference in a sub query will always look for the field internally
first and if not found it will look in the outer query. The reason for this
behaviour is that the sub query can use values from the outer query as
selection criteria, in case statements etc.

This is not a bug, it is by design. By always using table qualifiers in all
sql it will never cause a problem even if the developer mistypes a field
name.
Sloppy SQL (without proper table qualifiers etc) may behave funny as in the
example provided by the OP.

Also, if you look at the execution plan for this and similar queries it will
be more clear why. The optimizer usually turn sub queries like this into
joins.

invalid syntax near keyword default (was "Why do I get the following error?")

select * from CurrencyMaster where default=true
invalid syntax near keyword default.You cannot have a reserved word as a field name without enclosing it in brackets.
Therefore :

select * from CurrencyMaster where default=true
doesn't work, and
select * from CurrencyMaster where [default]=true

will probably work. (Haven't tested it myself tho...)

Invalid Syntax in my sproc?!?!

Hi I have a gridview that is being populated from a method that gets it's data from a table view.

SELECT dbo.cis_AlumniContact.Street, dbo.cis_AlumniContact.City, dbo.cis_AlumniContact.State, dbo.cis_AlumniContact.Telephone,
dbo.cis_AlumniContact.Occupation, dbo.cis_AlumniContact.Description, dbo.cis_AlumniContact.Zip, dbo.cis_AlumniContact.FirstName,
dbo.cis_AlumniContact.LastName, dbo.cis_AlumniContact.YearGraduate, dbo.cis_AlumniContact.Email, dbo.cis_AlumniContact.Contact,
dbo.aspnet_Users.UserName, dbo.cis_StudentId.UaaStudentId
FROM dbo.aspnet_Users INNER JOIN
dbo.cis_AlumniContact ON dbo.aspnet_Users.UserId = dbo.cis_AlumniContact.UserId INNER JOIN
dbo.cis_StudentId ON dbo.aspnet_Users.UserId = dbo.cis_StudentId.UserId

No big deal, works great. Now when I click update I call this method

<DataObjectMethod(DataObjectMethodType.Update)>

PublicSub UpdateAlumni(ByVal StreetAsString,ByVal CityAsString,ByVal StateAsString,ByVal TelephoneAsString,ByVal OccupationAsString,ByVal DescriptionAsString,ByVal ZipAsString,ByVal FirstNameAsString,ByVal LastNameAsString,ByVal YearGraduateAsString,ByVal EmailAsString,ByVal ContactAsBoolean,ByVal original_UserNameAsString,ByVal UaaStudentIdAsString)TryDim connxAsNew SqlConnection(getConnectionString)

connx.Open()

Dim sqlCmdAsNew SqlCommand("cis_UpdateAlumniContact", connx)

sqlCmd.Parameters.Add(

New SqlParameter("@.UserName", SqlDbType.NVarChar))

sqlCmd.Parameters(

"@.UserName").Value = original_UserName

sqlCmd.Parameters.Add(

New SqlParameter("@.FirstName", SqlDbType.NVarChar))

sqlCmd.Parameters(

"@.FirstName").Value = FirstName

sqlCmd.Parameters.Add(

New SqlParameter("@.LastName", SqlDbType.NVarChar))

sqlCmd.Parameters(

"@.LastName").Value = LastName

sqlCmd.Parameters.Add(

New SqlParameter("@.Email", SqlDbType.NVarChar))

sqlCmd.Parameters(

"@.Email").Value = Email

sqlCmd.Parameters.Add(

New SqlParameter("@.Street", SqlDbType.NVarChar))

sqlCmd.Parameters(

"@.Street").Value = Street

sqlCmd.Parameters.Add(

New SqlParameter("@.City", SqlDbType.NVarChar))

sqlCmd.Parameters(

"@.City").Value = City

sqlCmd.Parameters.Add(

New SqlParameter("@.State", SqlDbType.NVarChar))

sqlCmd.Parameters(

"@.State").Value = State

sqlCmd.Parameters.Add(

New SqlParameter("@.Occupation", SqlDbType.NVarChar))

sqlCmd.Parameters(

"@.Occupation").Value = Occupation

sqlCmd.Parameters.Add(

New SqlParameter("@.Description", SqlDbType.NVarChar))

sqlCmd.Parameters(

"@.Description").Value = Description

sqlCmd.Parameters.Add(

New SqlParameter("@.Telephone", SqlDbType.NChar))

sqlCmd.Parameters(

"@.Telephone").Value = Telephone

sqlCmd.Parameters.Add(

New SqlParameter("@.Zip", SqlDbType.NChar))

sqlCmd.Parameters(

"@.Zip").Value = Zip

sqlCmd.Parameters.Add(

New SqlParameter("@.Contact", SqlDbType.Bit))

sqlCmd.Parameters(

"@.Contact").Value = Contact

sqlCmd.Parameters.Add(

New SqlParameter("@.YearGraduate", SqlDbType.NVarChar))

sqlCmd.Parameters(

"@.YearGraduate").Value = YearGraduateDim cmdAs SqlDataReader = sqlCmd.ExecuteReaderCatch exAs ExceptionDim erAsNew cis_ODS_Error

er.InsertError(

"cis_ODS_Alumni - UpdateAlumni: " + ex.Message.ToString)EndTryEndSub

The sproc it calls is:

ALTER PROCEDURE

dbo.cis_UpdateAlumniContact

@.UserName

as nvarchar(50),

@.Street

as nvarchar(50),

@.City

as nvarchar(50),

@.State

as nvarchar(2),

@.Telephone

as nvarchar(50),

@.Occupation

as nvarchar(50),

@.Description

as nvarchar(50),

@.Zip

as nvarchar(50),

@.FirstName

as nvarchar(50),

@.LastName

as nvarchar(50),

@.YearGraduate

as nvarchar(4),

@.Email

as nvarchar(50),

@.Contact

as bit

AS

UPDATEcis_AlumniContact

SETStreet = @.Street, City = @.City, State = @.State, Telephone = @.Telephone, Occupation = @.Occupation, Description = @.Description, Zip = @.Zip, FirstName = @.FirstName, LastName = @.LastName, YearGraduate = @.YearGraduate, Email = @.Email, Contact = @.Contact

FROMaspnet_UsersINNER JOINcis_AlumniContact

ONcis_AlumniContact.UserId = aspnet_Users.UserId

WHERE@.UserName = aspnet_Users.UserName

RETURN

I get this vague error

cis_ODS_Alumni - UpdateAlumni: Incorrect syntax near 'cis_UpdateAlumniContact'

If I execute the SQL from the editor it works fine. The only thing different about this sproc and my other update sprocs is the inner join. Any idea? Thanks

I see two possible problems.

You need to set the command type to:StoredProcedure

You are calling ExecuteReader, I think you need to call ExecuteNonQuery.

|||Holy cow man. Thanks so much. I copied and pasted from another method and didn't include that line. Thanks again.

Invalid operator for data type.

Kudos to y'all experts out there. I kinda needed your help. I'm trying to run a query...

SELECT a.AUF_POS AS Pos, c.ZL_STR AS Panel, a.POS_TEXT AS Description, a.BREITE AS W1, a.HOEHE

AS H1, a.BREITE2 AS W2, a.HOEHE2 AS H2, SUM(b.ANZ) AS Qty, SUM(b.LIEFER_ANZ) AS Dlvd,

SUM(b.RG_ANZ) AS Inv, (a.BREITE*a.HOEHE/CAST(1000000 AS NUMERIC)) AS UnitSQM,

(a.BREITE*a.HOEHE*SUM(b.ANZ)/CAST(1000000 AS NUMERIC)) as TotPosSQM

FROM liorder..LIORDER.AUF_POS a INNER JOIN liorder..LIORDER.AUF_STAT b ON a.AUF_NR = b.AUF_NR

AND a.AUF_POS = b.AUF_POS INNER JOIN liorder..LIORDER.AUF_TEXT c ON a.AUF_NR = c.AUF_NR AND

b.AUF_POS = c.AUF_POS

WHERE (c.ZL_MOD = 0) AND (b.AUF_NR = '86260')

GROUP BY a.AUF_POS, a.POS_TEXT, a.BREITE, a.BREITE2, a.HOEHE, a.HOEHE2, a.SFORM_NR, c.ZL_STR

...and I keep getting this error: Invalid operator for data type. Operator equals multiply, type equals nvarchar. I've tried every possible CAST and CONVERT but I just can't make it work. I'm pretty sure that the data types for the columns I mentioned on the mathematical equation are all numeric. Please help...Its going to be difficult for us to help without the DDL. Your query does alot of aggregrations (i.e. sum/avg/etc), thus you'd want to ensure that the column datatype is numeric.

Invalid operator for data type

What is wrong with this select statement?

SELECT lastName + ", " + firstName + " " + middleName as Name
FROM [users]
WHERE ([usrID] = 100)
I kept getting this error:

Msg 403, Level 16, State 1, Line 1
Invalid operator for data type. Operator equals add, type equals text.

Help is appreciated.

SELECT lastName + ', ' + firstName + ' ' + middleName as Name
FROM [users]
WHERE ([usrID] = 100)

|||Thanks! I tried that and got the same error.|||Please check your table definition for these columns. It seems that the data type for these columns cannot do the operation. If that is the case, change them to nvarchar(50) and then give it a try. I tested the code and it works.|||

mychucky:

What is wrong with this select statement?

Msg 403, Level 16, State 1, Line 1
Invalid operator for data type. Operator equals add, type equals text.

Take a look at the error message, it seems that your column(s) is defined to TEXT type, which can not be used with '+' operator. I agreed withlimno, you should change the data type to nvarchar if the column is not used to store large text. To manage TEXT data, you should use some functions. Take a look at 'Usingtext, ntext, and image Functions' topic in SQL2000 Books Online, or go to this link:

http://msdn.microsoft.com/library/en-us/acdata/ac_8_con_11_7zox.asp?frame=true

|||Many thanks for the help. Yes, I do have Text and varachar as thedatatype. So what is the best way to concatenate these columns?|||Change both data types to Varchar. Hope this helps.

Invalid object name sysperfinfo

When I execute this statement through ASP.NET

select cntr_value FROM sysperfinfo

I get this error,

System.Data.SqlClient.SqlException: Invalid object name 'sysperfinfo'.

Any ideas why?

It sounds to me like your connectionstring (the string that points to the database which you are querying) is pointing to a database that does not have a table entitled 'sysperfinfo'|||

To add to the previous post some things have changes about sysperfinfo. Try the links below for more. Hope this helps.

sysperfinfo

In SQL Server 2005,sysperfinfo returns abigint value for thecntr_value column. Modify applications that usesysperfinfo to make sure that they can handle thebigint values of thecntr_value column.

In SQL Server 2005,sysperfinfo is a compatibility view. You should use thesys.dm_os_performance_counters dynamic management view instead.

http://msdn2.microsoft.com/en-us/library/ms143179.aspx

http://www.sqlservercentral.com/columnists/jsack/troubleshootingsqlserverwiththesysperfinfotable.asp

|||

Maybe you're in the wrong database, try:

select cntr_value FROM master..sysperfinfo

Invalid object name 'information_schema.constraint_table_usage'

I created a new database on a SQL 2005 server, which also contains other
databases. I just tried to run "select * from
information_schema.constraint_table_usage" and got an error Msg 208, Level
16, State 1, Line 1
Invalid object name 'information_schema.constraint_table_usage'.
However I can run the select statement on the other databases and get the
information back.
Anyone have any idea why it wouldn't work on one database but does on other's.
Thanks in advance.
Possibly a permissions issue?
"scuba79" <scuba79@.discussions.microsoft.com> wrote in message
news:4835E0D8-D980-4C60-A2D2-A6BDFAD2D91D@.microsoft.com...
>I created a new database on a SQL 2005 server, which also contains other
> databases. I just tried to run "select * from
> information_schema.constraint_table_usage" and got an error Msg 208, Level
> 16, State 1, Line 1
> Invalid object name 'information_schema.constraint_table_usage'.
> However I can run the select statement on the other databases and get the
> information back.
> Anyone have any idea why it wouldn't work on one database but does on
> other's.
> Thanks in advance.
|||> My guess is that the database is case-sensitive. The info schema view
> names are all upper-case.
Of course, duh. I was going to suggest compatibility level next. :-)
|||That was the issue, thought I created it case-insensitive...
"Tibor Karaszi" wrote:

> My guess is that the database is case-sensitive. The info schema view names are all upper-case.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "scuba79" <scuba79@.discussions.microsoft.com> wrote in message
> news:4835E0D8-D980-4C60-A2D2-A6BDFAD2D91D@.microsoft.com...
>
>

Invalid object name 'information_schema.constraint_table_usage'

I created a new database on a SQL 2005 server, which also contains other
databases. I just tried to run "select * from
information_schema.constraint_table_usage" and got an error Msg 208, Level
16, State 1, Line 1
Invalid object name 'information_schema.constraint_table_usage'.
However I can run the select statement on the other databases and get the
information back.
Anyone have any idea why it wouldn't work on one database but does on other's.
Thanks in advance.Possibly a permissions issue?
"scuba79" <scuba79@.discussions.microsoft.com> wrote in message
news:4835E0D8-D980-4C60-A2D2-A6BDFAD2D91D@.microsoft.com...
>I created a new database on a SQL 2005 server, which also contains other
> databases. I just tried to run "select * from
> information_schema.constraint_table_usage" and got an error Msg 208, Level
> 16, State 1, Line 1
> Invalid object name 'information_schema.constraint_table_usage'.
> However I can run the select statement on the other databases and get the
> information back.
> Anyone have any idea why it wouldn't work on one database but does on
> other's.
> Thanks in advance.|||My guess is that the database is case-sensitive. The info schema view names are all upper-case.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"scuba79" <scuba79@.discussions.microsoft.com> wrote in message
news:4835E0D8-D980-4C60-A2D2-A6BDFAD2D91D@.microsoft.com...
>I created a new database on a SQL 2005 server, which also contains other
> databases. I just tried to run "select * from
> information_schema.constraint_table_usage" and got an error Msg 208, Level
> 16, State 1, Line 1
> Invalid object name 'information_schema.constraint_table_usage'.
> However I can run the select statement on the other databases and get the
> information back.
> Anyone have any idea why it wouldn't work on one database but does on other's.
> Thanks in advance.|||> My guess is that the database is case-sensitive. The info schema view
> names are all upper-case.
Of course, duh. I was going to suggest compatibility level next. :-)|||That was the issue, thought I created it case-insensitive...
"Tibor Karaszi" wrote:
> My guess is that the database is case-sensitive. The info schema view names are all upper-case.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "scuba79" <scuba79@.discussions.microsoft.com> wrote in message
> news:4835E0D8-D980-4C60-A2D2-A6BDFAD2D91D@.microsoft.com...
> >I created a new database on a SQL 2005 server, which also contains other
> > databases. I just tried to run "select * from
> > information_schema.constraint_table_usage" and got an error Msg 208, Level
> > 16, State 1, Line 1
> > Invalid object name 'information_schema.constraint_table_usage'.
> >
> > However I can run the select statement on the other databases and get the
> > information back.
> >
> > Anyone have any idea why it wouldn't work on one database but does on other's.
> >
> > Thanks in advance.
>
>sql

Invalid object name 'information_schema.constraint_table_usage'

I created a new database on a SQL 2005 server, which also contains other
databases. I just tried to run "select * from
information_schema.constraint_table_usage" and got an error Msg 208, Level
16, State 1, Line 1
Invalid object name 'information_schema.constraint_table_usage'.
However I can run the select statement on the other databases and get the
information back.
Anyone have any idea why it wouldn't work on one database but does on other'
s.
Thanks in advance.Possibly a permissions issue?
"scuba79" <scuba79@.discussions.microsoft.com> wrote in message
news:4835E0D8-D980-4C60-A2D2-A6BDFAD2D91D@.microsoft.com...
>I created a new database on a SQL 2005 server, which also contains other
> databases. I just tried to run "select * from
> information_schema.constraint_table_usage" and got an error Msg 208, Level
> 16, State 1, Line 1
> Invalid object name 'information_schema.constraint_table_usage'.
> However I can run the select statement on the other databases and get the
> information back.
> Anyone have any idea why it wouldn't work on one database but does on
> other's.
> Thanks in advance.|||My guess is that the database is case-sensitive. The info schema view names
are all upper-case.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"scuba79" <scuba79@.discussions.microsoft.com> wrote in message
news:4835E0D8-D980-4C60-A2D2-A6BDFAD2D91D@.microsoft.com...
>I created a new database on a SQL 2005 server, which also contains other
> databases. I just tried to run "select * from
> information_schema.constraint_table_usage" and got an error Msg 208, Level
> 16, State 1, Line 1
> Invalid object name 'information_schema.constraint_table_usage'.
> However I can run the select statement on the other databases and get the
> information back.
> Anyone have any idea why it wouldn't work on one database but does on othe
r's.
> Thanks in advance.|||> My guess is that the database is case-sensitive. The info schema view
> names are all upper-case.
Of course, duh. I was going to suggest compatibility level next. :-)

Invalid Object Name - Grr...

This is what I have. It works fine until I get to the select statement, then it tells me that I have an invalid object name. What am I missing? Thanks!

DECLARE @.SvrName varchar(100)

if @.@.SERVERNAME='pubs' begin
set @.SvrName=pubs.books.isbn
print @.SvrName
end
if @.@.SERVERNAME='MGMFILENET' begin
set @.SvrName=store.books.isbn
print@.SvrName
end

print @.@.SERVERNAME
PRINT @.SvrName
SELECT * FROM "@.SvrName"Doesn't work that way

DECALRE @.SQL
SET @.SQL = 'SELECT * FROM ' + @.SvrName
EXEC(@.SQL)

But why do this...uhh dynamic sql...|||Even in a stored proc?

What I am trying to do is find out what the server is, then point the rest of the gazillion SQL statements to follow to that server.|||Originally posted by acral
Even in a stored proc?

What I am trying to do is find out what the server is, then point the rest of the gazillion SQL statements to follow to that server.

Huh?|||This will be running as a stored proc... the proc takes many steps in moving data around, but first I need to determine what environment the user is in (i.e. what server)...

The there will be a series of statements such as Update this table, email a percentage to that group, make a temp table over there, and so on. In one environment, all of the databases are on one server, in another environment, the database are on different servers. In both cases, they have to interact.

So if YOU are logged into the system, when the stored proc executes, it will see which server you are logged to then point you from there by way of the rest of the statements.

Make sense?|||Not to me, but that's not saying much..

What's the application layer?|||Wouldn't it just be simpler to write a stored procedure that does what you need on each server, then call the stored procedure on the appropriate server? This gets about a gazillion RPC calls and cross-server queries (with potential cross-server joins) out of the way.

That way each box can call one stored procedure (you could even make it an sp_ if you wanted to make thing simple), and there are so many fewer moving parts.

-PatP|||Okay, here's what I ended up doing...

using a string like

Exec(@.SQL) was not going to cut it, so I have an if/then scenario that checks @.@.SERVERNAME then sets a variable. The contents of that variable in turn point to the right server for the right instance.

The reasoning is this.. in one evironment the databases referenced are on the same server, in another environment they are on different servers.

Thanks for the help... I would have kept pounding on that stupid "string" half the night had you guys not set me straight. :)

invalid object name

In my database I have created a table called "com01" owned
by "lisa" and if I try to select the rows with the
statement "select * from lisa.com01" it works fine !
But if I submit the statement "select * from com01" it
return the error:
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'com01'.
Why '
In both cases I'm connected to the database with
user "lisa" who is the owner of the table. There isn't
other table called "com01" in the database.
Help me please !!!Is 'lisa' a member of the sysadmin role? In this case, the default owner
will be 'dbo' instead of 'lisa' when resolving object names. You can
determine the name used for object name resolution with SELECT USER.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"paolo" <paolo.ricci@.gidi.it> wrote in message
news:746e01c494d7$50f799a0$a501280a@.phx.gbl...
> In my database I have created a table called "com01" owned
> by "lisa" and if I try to select the rows with the
> statement "select * from lisa.com01" it works fine !
> But if I submit the statement "select * from com01" it
> return the error:
> Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name 'com01'.
> Why '
> In both cases I'm connected to the database with
> user "lisa" who is the owner of the table. There isn't
> other table called "com01" in the database.
> Help me please !!!
>|||HI
Read Books Online: object names -> Object Visibility and Qualification Rules
Andras Jakus MCDBA
"paolo" wrote:
> In my database I have created a table called "com01" owned
> by "lisa" and if I try to select the rows with the
> statement "select * from lisa.com01" it works fine !
> But if I submit the statement "select * from com01" it
> return the error:
> Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name 'com01'.
> Why '
> In both cases I'm connected to the database with
> user "lisa" who is the owner of the table. There isn't
> other table called "com01" in the database.
> Help me please !!!
>
>|||Thank you for your help Andras !
I've just read the documentation as you suggest me, but
unfortunately the problem is not solved:
"lisa" is the owner of the table "com01" and is the user
connected to the database, but if I want to select from
that table I've to specified the owner_name dot table_name
(lisa.com01) and not only the table_name (com01). Is the
same also for other tables owned by "lisa" !
If I try to select from table owned by "dbo" only with
table_name (sysobjects) it works fine !
"lisa" is db_owner of the database.
Any other suggestions '
Thank you!
Bye Paolo.
>--Original Message--
>HI
>Read Books Online: object names -> Object Visibility and
Qualification Rules
>Andras Jakus MCDBA
>"paolo" wrote:
>> In my database I have created a table called "com01"
owned
>> by "lisa" and if I try to select the rows with the
>> statement "select * from lisa.com01" it works fine !
>> But if I submit the statement "select * from com01" it
>> return the error:
>> Server: Msg 208, Level 16, State 1, Line 1
>> Invalid object name 'com01'.
>> Why '
>> In both cases I'm connected to the database with
>> user "lisa" who is the owner of the table. There isn't
>> other table called "com01" in the database.
>> Help me please !!!
>>
>.
>|||Bingo Dan ! "lisa" is a member of sysadmin role and the
statement "select user" return "dbo".
Is it possible to select from tables owned by "lisa"
without specified the owner ? I can't disable sysadmin
role for lisa !
Thank you !!!
Bye Paolo.
>--Original Message--
>Is 'lisa' a member of the sysadmin role? In this case,
the default owner
>will be 'dbo' instead of 'lisa' when resolving object
names. You can
>determine the name used for object name resolution with
SELECT USER.
>--
>Hope this helps.
>Dan Guzman
>SQL Server MVP
>"paolo" <paolo.ricci@.gidi.it> wrote in message
>news:746e01c494d7$50f799a0$a501280a@.phx.gbl...
>> In my database I have created a table called "com01"
owned
>> by "lisa" and if I try to select the rows with the
>> statement "select * from lisa.com01" it works fine !
>> But if I submit the statement "select * from com01" it
>> return the error:
>> Server: Msg 208, Level 16, State 1, Line 1
>> Invalid object name 'com01'.
>> Why '
>> In both cases I'm connected to the database with
>> user "lisa" who is the owner of the table. There isn't
>> other table called "com01" in the database.
>> Help me please !!!
>>
>
>.
>|||You could create a view
create view dbo.com01 as select * from lisa.com01
then
select * from com01
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"paolo" <paolo.ricci@.gidi.it> wrote in message
news:746e01c494d7$50f799a0$a501280a@.phx.gbl...
> In my database I have created a table called "com01" owned
> by "lisa" and if I try to select the rows with the
> statement "select * from lisa.com01" it works fine !
> But if I submit the statement "select * from com01" it
> return the error:
> Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name 'com01'.
> Why '
> In both cases I'm connected to the database with
> user "lisa" who is the owner of the table. There isn't
> other table called "com01" in the database.
> Help me please !!!
>|||Hopefully, Wayne's view suggestion will address your issue.
--
Hope this helps.
Dan Guzman
SQL Server MVP
<paolo.ricci@.gidi.it> wrote in message
news:75b501c494df$edd69f20$a301280a@.phx.gbl...
> Bingo Dan ! "lisa" is a member of sysadmin role and the
> statement "select user" return "dbo".
> Is it possible to select from tables owned by "lisa"
> without specified the owner ? I can't disable sysadmin
> role for lisa !
> Thank you !!!
> Bye Paolo.
>>--Original Message--
>>Is 'lisa' a member of the sysadmin role? In this case,
> the default owner
>>will be 'dbo' instead of 'lisa' when resolving object
> names. You can
>>determine the name used for object name resolution with
> SELECT USER.
>>--
>>Hope this helps.
>>Dan Guzman
>>SQL Server MVP
>>"paolo" <paolo.ricci@.gidi.it> wrote in message
>>news:746e01c494d7$50f799a0$a501280a@.phx.gbl...
>> In my database I have created a table called "com01"
> owned
>> by "lisa" and if I try to select the rows with the
>> statement "select * from lisa.com01" it works fine !
>> But if I submit the statement "select * from com01" it
>> return the error:
>> Server: Msg 208, Level 16, State 1, Line 1
>> Invalid object name 'com01'.
>> Why '
>> In both cases I'm connected to the database with
>> user "lisa" who is the owner of the table. There isn't
>> other table called "com01" in the database.
>> Help me please !!!
>>
>>
>>.

invalid object name

In my database I have created a table called "com01" owned
by "lisa" and if I try to select the rows with the
statement "select * from lisa.com01" it works fine !
But if I submit the statement "select * from com01" it
return the error:
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'com01'.
Why ?
In both cases I'm connected to the database with
user "lisa" who is the owner of the table. There isn't
other table called "com01" in the database.
Help me please !!!
Is 'lisa' a member of the sysadmin role? In this case, the default owner
will be 'dbo' instead of 'lisa' when resolving object names. You can
determine the name used for object name resolution with SELECT USER.
Hope this helps.
Dan Guzman
SQL Server MVP
"paolo" <paolo.ricci@.gidi.it> wrote in message
news:746e01c494d7$50f799a0$a501280a@.phx.gbl...
> In my database I have created a table called "com01" owned
> by "lisa" and if I try to select the rows with the
> statement "select * from lisa.com01" it works fine !
> But if I submit the statement "select * from com01" it
> return the error:
> Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name 'com01'.
> Why ?
> In both cases I'm connected to the database with
> user "lisa" who is the owner of the table. There isn't
> other table called "com01" in the database.
> Help me please !!!
>
|||HI
Read Books Online: object names -> Object Visibility and Qualification Rules
Andras Jakus MCDBA
"paolo" wrote:

> In my database I have created a table called "com01" owned
> by "lisa" and if I try to select the rows with the
> statement "select * from lisa.com01" it works fine !
> But if I submit the statement "select * from com01" it
> return the error:
> Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name 'com01'.
> Why ?
> In both cases I'm connected to the database with
> user "lisa" who is the owner of the table. There isn't
> other table called "com01" in the database.
> Help me please !!!
>
>
|||Thank you for your help Andras !
I've just read the documentation as you suggest me, but
unfortunately the problem is not solved:
"lisa" is the owner of the table "com01" and is the user
connected to the database, but if I want to select from
that table I've to specified the owner_name dot table_name
(lisa.com01) and not only the table_name (com01). Is the
same also for other tables owned by "lisa" !
If I try to select from table owned by "dbo" only with
table_name (sysobjects) it works fine !
"lisa" is db_owner of the database.
Any other suggestions ?
Thank you!
Bye Paolo.

>--Original Message--
>HI
>Read Books Online: object names -> Object Visibility and
Qualification Rules[vbcol=seagreen]
>Andras Jakus MCDBA
>"paolo" wrote:
owned
>.
>
|||Bingo Dan ! "lisa" is a member of sysadmin role and the
statement "select user" return "dbo".
Is it possible to select from tables owned by "lisa"
without specified the owner ? I can't disable sysadmin
role for lisa !
Thank you !!!
Bye Paolo.

>--Original Message--
>Is 'lisa' a member of the sysadmin role? In this case,
the default owner
>will be 'dbo' instead of 'lisa' when resolving object
names. You can
>determine the name used for object name resolution with
SELECT USER.[vbcol=seagreen]
>--
>Hope this helps.
>Dan Guzman
>SQL Server MVP
>"paolo" <paolo.ricci@.gidi.it> wrote in message
>news:746e01c494d7$50f799a0$a501280a@.phx.gbl...
owned
>
>.
>
|||You could create a view
create view dbo.com01 as select * from lisa.com01
then
select * from com01
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"paolo" <paolo.ricci@.gidi.it> wrote in message
news:746e01c494d7$50f799a0$a501280a@.phx.gbl...
> In my database I have created a table called "com01" owned
> by "lisa" and if I try to select the rows with the
> statement "select * from lisa.com01" it works fine !
> But if I submit the statement "select * from com01" it
> return the error:
> Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name 'com01'.
> Why ?
> In both cases I'm connected to the database with
> user "lisa" who is the owner of the table. There isn't
> other table called "com01" in the database.
> Help me please !!!
>
|||Hopefully, Wayne's view suggestion will address your issue.
Hope this helps.
Dan Guzman
SQL Server MVP
<paolo.ricci@.gidi.it> wrote in message
news:75b501c494df$edd69f20$a301280a@.phx.gbl...[vbcol=seagreen]
> Bingo Dan ! "lisa" is a member of sysadmin role and the
> statement "select user" return "dbo".
> Is it possible to select from tables owned by "lisa"
> without specified the owner ? I can't disable sysadmin
> role for lisa !
> Thank you !!!
> Bye Paolo.
> the default owner
> names. You can
> SELECT USER.
> owned

Invalid Object

I'm running a simple select statement.
Select TestSchema From TestDB where DbName = 'TEST'
It runs fine when I run it in Enterprise Manager.
But I get an error message when I run it in Query Analyser.
Error: Invalid object name 'TestDB'
Help!DBNAME is a built-in function. If your table has a column of that name,
try:
Select TestSchema From TestDB where [DbName] = 'TEST'
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"sql" <sql@.discussions.microsoft.com> wrote in message
news:DC485C69-107A-4A63-8B1B-6B71BCBA75AE@.microsoft.com...
I'm running a simple select statement.
Select TestSchema From TestDB where DbName = 'TEST'
It runs fine when I run it in Enterprise Manager.
But I get an error message when I run it in Query Analyser.
Error: Invalid object name 'TestDB'
Help!|||Tom:
I tried with the square brackets, but I still get the same error message.
"Tom Moreau" wrote:
> DBNAME is a built-in function. If your table has a column of that name,
> try:
> Select TestSchema From TestDB where [DbName] = 'TEST'
>
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> "sql" <sql@.discussions.microsoft.com> wrote in message
> news:DC485C69-107A-4A63-8B1B-6B71BCBA75AE@.microsoft.com...
> I'm running a simple select statement.
> Select TestSchema From TestDB where DbName = 'TEST'
> It runs fine when I run it in Enterprise Manager.
> But I get an error message when I run it in Query Analyser.
>
> Error: Invalid object name 'TestDB'
> Help!
>|||Tom,
Isn't the function db_name()?
I'm suspicious that the table may be under a different owner name. Try owner qualify the table to see what you get.
Richard
--
Message posted via http://www.sqlmonster.com|||Oops, you're right. I'd be tempted to run the profiler and see what's being
sent when he runs it (successfully) through Enterprise Manager. That may
give a clue.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Richard Ding via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:d534a166a789418e873d7ab25b41a525@.SQLMonster.com...
Tom,
Isn't the function db_name()?
I'm suspicious that the table may be under a different owner name. Try owner
qualify the table to see what you get.
Richard
--
Message posted via http://www.sqlmonster.com

Invalid Object

I'm running a simple select statement.
Select TestSchema From TestDB where DbName = 'TEST'
It runs fine when I run it in Enterprise Manager.
But I get an error message when I run it in Query Analyser.
Error: Invalid object name 'TestDB'
Help!
DBNAME is a built-in function. If your table has a column of that name,
try:
Select TestSchema From TestDB where [DbName] = 'TEST'
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"sql" <sql@.discussions.microsoft.com> wrote in message
news:DC485C69-107A-4A63-8B1B-6B71BCBA75AE@.microsoft.com...
I'm running a simple select statement.
Select TestSchema From TestDB where DbName = 'TEST'
It runs fine when I run it in Enterprise Manager.
But I get an error message when I run it in Query Analyser.
Error: Invalid object name 'TestDB'
Help!
|||Tom:
I tried with the square brackets, but I still get the same error message.
"Tom Moreau" wrote:

> DBNAME is a built-in function. If your table has a column of that name,
> try:
> Select TestSchema From TestDB where [DbName] = 'TEST'
>
> --
> Tom
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> "sql" <sql@.discussions.microsoft.com> wrote in message
> news:DC485C69-107A-4A63-8B1B-6B71BCBA75AE@.microsoft.com...
> I'm running a simple select statement.
> Select TestSchema From TestDB where DbName = 'TEST'
> It runs fine when I run it in Enterprise Manager.
> But I get an error message when I run it in Query Analyser.
>
> Error: Invalid object name 'TestDB'
> Help!
>
|||Tom,
Isn't the function db_name()?
I'm suspicious that the table may be under a different owner name. Try owner qualify the table to see what you get.
Richard
Message posted via http://www.sqlmonster.com
|||Oops, you're right. I'd be tempted to run the profiler and see what's being
sent when he runs it (successfully) through Enterprise Manager. That may
give a clue.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Richard Ding via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:d534a166a789418e873d7ab25b41a525@.SQLMonster.c om...
Tom,
Isn't the function db_name()?
I'm suspicious that the table may be under a different owner name. Try owner
qualify the table to see what you get.
Richard
Message posted via http://www.sqlmonster.com

Invalid column name ''prov''. For me, very very strange!

Can someone se whats wrong here!

DECLARE @.TEMP table (ID int, FILENAME nvarchar(255), GOgo nvarchar(5))

INSERT INTO @.TEMP

select * , SUBSTRING(FILENAME, len(FILENAME) -1, 1) as GOgo

from mytable

group by GOgo

order by ID desc

Msg 207, Level 16, State 1, Procedure GET_STAT, Line 102

Invalid column name 'prov'.

For me, very very strange!

Does the SELECT work when you're not doing an insert? Does "SELECT * FROM mytable" work?

|||

Always mention the column name explicitly to avoid these kind of confusion

INSERT INTO @.TEMP (ID,Filename,GoGo)

select Col1,col2,SUBSTRING(FILENAME, len(FILENAME) -1, 1) as GOgo

from mytable

group by GOgo

order by ID desc

Check this code

Madhu

Invalid column name ''prov''. For me, very very strange!

Can someone se whats wrong here!

DECLARE @.TEMP table (ID int, FILENAME nvarchar(255), GOgo nvarchar(5))

INSERT INTO @.TEMP

select * , SUBSTRING(FILENAME, len(FILENAME) -1, 1) as GOgo

from mytable

group by GOgo

order by ID desc

Msg 207, Level 16, State 1, Procedure GET_STAT, Line 102

Invalid column name 'prov'.

For me, very very strange!

Does the SELECT work when you're not doing an insert? Does "SELECT * FROM mytable" work?

|||

Always mention the column name explicitly to avoid these kind of confusion

INSERT INTO @.TEMP (ID,Filename,GoGo)

select Col1,col2,SUBSTRING(FILENAME, len(FILENAME) -1, 1) as GOgo

from mytable

group by GOgo

order by ID desc

Check this code

Madhu

Invalid column name ''prov''. For me, very very strange!

Can someone se whats wrong here!

DECLARE @.TEMP table (ID int, FILENAME nvarchar(255), GOgo nvarchar(5))

INSERT INTO @.TEMP

select * , SUBSTRING(FILENAME, len(FILENAME) -1, 1) as GOgo

from mytable

group by GOgo

order by ID desc

Msg 207, Level 16, State 1, Procedure GET_STAT, Line 102

Invalid column name 'prov'.

For me, very very strange!

Does the SELECT work when you're not doing an insert? Does "SELECT * FROM mytable" work?

|||

Always mention the column name explicitly to avoid these kind of confusion

INSERT INTO @.TEMP (ID,Filename,GoGo)

select Col1,col2,SUBSTRING(FILENAME, len(FILENAME) -1, 1) as GOgo

from mytable

group by GOgo

order by ID desc

Check this code

Madhu