SQL Server 2005 error: "Invalid object name" / "Incorrect syntax near"

Hi All,

This problem isent strictly to do with ASP.NET but I am creating an ASP.NET web application.

Just a quick overview, I have recently had to reinstall windows on my machine becuase of an unrelated matter so before I did so i created an SQL script using the SQL Server Database Publishing Wizard so that the SQL Database could be recreated exaclty as it was.

I have just reistalled SQL Server 2005 and the Managment Studio and have attempted to run the script.

Once the script is run there are literally thousands of the following two errors:

Invalid object name

and

Incorrect syntax near ...

It is just these two varieties of error that appear.

This is very odd as the script works perfectly on my remote SQL 2005 database server as the database was created exactly. So there clearly isent a problem with the script it must be something to do with the clean install on SQL Server 2005.

I am unsure why this error is appearing? Has anyone one else had this problem or know a quick fix?

Can anyone advise?

Many thanks.

0
oguh
3/1/2009 7:12:13 PM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

16 Replies
943 Views

Similar Articles

[PageSpeed] 10

Post one or two of the exact error messages.

I have a suspicion that you installed your SQL Server as case sensitive and thus having the problems, but we would be able to tell better if you post the exact error messages.


Beware of bugs in the above code; I have only proved it correct, not tried it.
(Donald Knuth)
0
Naom
3/1/2009 8:16:50 PM

The script is enourmous so below is just a snippet of the top of the script.

SQL Script Code snippet

/****** Object:  ForeignKey [FK__aspnet_Me__Appli__408F9238]    Script Date: 02/22/2009 17:04:27 ******/
IF  EXISTS
(SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK__aspnet_Me__Appli__408F9238]') AND parent_object_id = OBJECT_ID(N'[dbo].[aspnet_Membership]'))
ALTER TABLE
[dbo].[aspnet_Membership] DROP CONSTRAINT [FK__aspnet_Me__Appli__408F9238]
GO
/****** Object:  ForeignKey [FK__aspnet_Me__UserI__4183B671]    Script Date: 02/22/2009 17:04:27 ******/
IF  EXISTS
(SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK__aspnet_Me__UserI__4183B671]') AND parent_object_id = OBJECT_ID(N'[dbo].[aspnet_Membership]'))
ALTER TABLE
[dbo].[aspnet_Membership] DROP CONSTRAINT [FK__aspnet_Me__UserI__4183B671]
GO
/****** Object:  ForeignKey [FK__aspnet_Pa__Appli__7226EDCC]    Script Date: 02/22/2009 17:04:27 ******/
IF  EXISTS
(SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK__aspnet_Pa__Appli__7226EDCC]') AND parent_object_id = OBJECT_ID(N'[dbo].[aspnet_Paths]'))
ALTER TABLE
[dbo].[aspnet_Paths] DROP CONSTRAINT [FK__aspnet_Pa__Appli__7226EDCC]
GO

The first "Invalid Object" errors recieved are as follows:

Msg 208, Level 16, State 1, Line 2
Invalid object name 'sys.foreign_keys'.
Msg 208, Level 16, State 1, Line 2
Invalid object name 'sys.foreign_keys'.
Msg 208, Level 16, State 1, Line 2
Invalid object name 'sys.foreign_keys'.

...

 The first "Incorrect Syntax" errors recieved are as follows:

 Msg 170, Level 15, State 1, Line 22
Line 22: Incorrect syntax near '('.
Msg 170, Level 15, State 1, Line 9
Line 9: Incorrect syntax near '('.

 ...

 As I say the script works perfectly on my remote SQL Server 2005, I just recieve these errors when trying to run it on my local SQL Server 2005.

Thanks for your help.

0
oguh
3/1/2009 8:26:12 PM

Would you please verify what is your SQL Server instance collation?

Since it can not recognize sys.Foreign_Keys and I just ran it here, it looks like I was right.

Just for now try in the new query window

select * from sys.foreign_keys

This query worked for me, would it work for you as is?


Beware of bugs in the above code; I have only proved it correct, not tried it.
(Donald Knuth)
0
Naom
3/1/2009 8:37:24 PM

I ran the query "select * from sys.foreign_keys" and recieved the follwoing error...

Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.foreign_keys'.

 

What do you suggest?

 

Many thanks.

0
oguh
3/1/2009 8:41:49 PM

Too bad. It does sound like you made your SQL Server installation to be case-sensitive. I don't know if you can change the collation now or you would need to re-install.

Try to research on the SQL Server COLLATION and I'll do the same in the meantime. 

BTW, are you sure you have SQL Server 2005 and not SQL Server 2000?

Check http://www.mssqltips.com/tip.asp?tip=1040

What does

SELECT *

FROM master.sys.servers;

GO

return in regards to collation?


Beware of bugs in the above code; I have only proved it correct, not tried it.
(Donald Knuth)
0
Naom
3/1/2009 8:47:46 PM

Check http://marcellotonarelli.wordpress.com/2009/01/25/how-to-change-ms-sql-server-2005-server-collation/

 


Beware of bugs in the above code; I have only proved it correct, not tried it.
(Donald Knuth)
0
Naom
3/1/2009 9:00:13 PM

 Yes, I am pretty sure I have 2005 installed.

 This is copied from the "about"...

 Microsoft SQL Server Management Studio Express              9.00.2047.00
Microsoft Data Access Components (MDAC)                        2000.085.1132.00 (xpsp.080413-0852)
Microsoft MSXML                                                               2.6 3.0 4.0 5.0 6.0
Microsoft Internet Explorer                                                   6.0.2900.5512
Microsoft .NET Framework                                                  2.0.50727.3053
Operating System                                                              5.1.2600

 

The query returns the following:

 

Msg 208, Level 16, State 1, Line 1
Invalid object name 'master.sys.servers'.

0
oguh
3/1/2009 9:07:14 PM

 However this query does return results. :

 

SELECT *
FROM master.dbo.sysservers
GO

Does this indicated that I have an instance of SQL SERVER 2000 installed??

0
oguh
3/1/2009 9:08:58 PM

This query worked for me too.

Try

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

http://support.microsoft.com/kb/321185


Beware of bugs in the above code; I have only proved it correct, not tried it.
(Donald Knuth)
0
Naom
3/1/2009 9:21:47 PM

Product Version: 8.00.760

Product Level: SP3

Edition: Desktop Engine

 

So does that confim I have 2005 running and the problems lie else where?


 

0
oguh
3/1/2009 9:29:06 PM

Did you read the link I gave you?

To determine which version of SQL Server 2000 is running, connect to SQL Server 2000 by using Query Analyzer, and then run the following code:

SELECT  SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

The following results are returned:

  • The product version (for example, 8.00.534).
  • The product level (for example, "RTM" or "SP2").
  • The edition (for example, "Standard Edition"). For example, the results resemble the following:

    8.00.534 SP2 Standard Edition

See also

http://berezniker.com/content/pages/sql/microsoft-sql-server/sql-server-versions

Number 8 means you're running SQL Server 2000.


Beware of bugs in the above code; I have only proved it correct, not tried it.
(Donald Knuth)
0
Naom
3/1/2009 9:35:27 PM

 Thanks, thats great! Yeh have read the link. That confims it then that I do in fact have SQL SERVER 2000.

Have been looking around but im unsure what I require to download/install to upgrade to SQL Server 2005.

Can you give a link to download what is required?

Thanks again!

 

 

0
oguh
3/1/2009 9:46:53 PM

Do you want to install Express version (free Download) or do you have a CD?

I installed SQL Server Express 2008 at home and like it. I would not want to go with 2005 now even if I have my own Developer's version.

Anyway, here is a link for SQL Server 2005 Express

http://www.microsoft.com/downloads/details.aspx?FamilyId=220549b5-0b07-4448-8848-dcc397514b41&displaylang=en

Don't forget to apply SP after Download and installation and be careful to install case-insensitive (default) collation.


Beware of bugs in the above code; I have only proved it correct, not tried it.
(Donald Knuth)
0
Naom
3/1/2009 10:09:18 PM

No, I dont have the CD. Thanks for the link!

I shall be careful to install case-insensitive collation. How is it you apply the SP after the install? Is that a seperate download?

Lets hope there are no other problems running the script once it is installed.

Thanks for you time and patience - You have been a great help!

0
oguh
3/1/2009 10:16:58 PM

You're welcome. Here is a link to SP2 download

http://www.microsoft.com/DOWNLOADS/details.aspx?familyid=31711D5D-725C-4AFA-9D65-E4465CDFF1E7&displaylang=en

Let me know if everything works out for you, I have a feeling you somehow messed your original installation.


Beware of bugs in the above code; I have only proved it correct, not tried it.
(Donald Knuth)
0
Naom
3/1/2009 10:30:56 PM

 Everything seems to fine. The install has worked correct and it appears I already have SP2 so its alright.

Yeh, im not sure how SQL2000 was installed. Clearly a schoolboy error on my part somewhere along the line.

 The script has executed correctly and my database is there!! Brilliant!!

 Again, thanks for your help - will mark the posts as answer!!


Big Smile 

0
oguh
3/1/2009 10:37:40 PM
Reply:

Similar Artilces:

SQL Server 2005 error: "Incorrect syntax near "-" "
Hello, I have an application written in visual basic which connects to a SQL Server 2005In DB of SQL Server I have many tables. Table names can contain chars like: "-",  "\",  ".", etc. When I execute the instruction in visual basic sTableName= "Table-3" ' or ""Table-3"" or "Table_1" or sTableName= """" + "Table_1" + """"   -> """" = 4 quates (2 for string identify and two for writing in string a quate chr(34)->" strQu...

SQL syntax opertators: "= any" and "= all"
Hi all, Does anyone ever use the operator of ANY and ALL in the data source of the datawindow? If yes, can you tell me what is the benefit and the context of how to use it? Thanks, Kevin Kevin, Guess you're using Oracle...Just saw that in my oracle Book.. For ANY..when testing for equivalence (= ANY (a,b,c..etc)), this is the same as IN For ALL, my docs say tests that relationship specified is true for every element of the list provided... HTH -- Evita R. Chapa Senior Systems Analyst II Command Technologies, Inc. "kdinh" <kdinh@cayenta...

What´s the purpose of "classifieds-categories.sql" "classifieds-add.sql""classifieds-remove.sql"?
 Hello all, what´s the purpose of the scripts I enunciated above on the classified starter kit?  "classifieds-categories.sql"  seems to create the initial parent categories, the others 2 names sugest they are used to add and remove ads, but i cant get how.There are 10 types of people in this world:Those who understand binary and those who don't. The classifieds starter kit is ready for upload right out of the box. Any .sql files you find in App_Data are scripts of the database.They are there to make it easy for uploading your site. So if you were not mak...

Precedence of "where" ("of", "is", "will")?
Nobody on #perl6 today could answer this one. Is: Str | Int where { $_ } the same as: (Str | Int) where { $_ } or: Str | (Int where { $_ }) ? Followup questions, Mr. President: What kind of operators are "where", "of", "is", and "will"? Is there a reason that S03 doesn't list them? What are their precedence(s)? -- Chip Salzenberg - a.k.a. - <chip@pobox.com> Open Source is not an excuse to write fun code then leave the actual work to others. Chip Salzenberg writes: &...

.ALLCOL("%COLUMN%", " ", ", ", ", ")
Do you know anyway for me to exclude a subset of columns returned by this function. We have two columns (rec_user and rec_datetime) which are in all of our tables, but when generating triggers I want automatically generate a script which does not include those two columns but does include all other columns in that table. Bruce I should add that I am using PD 9.0.0.580. Bruce "Bruce Lamb" <lamb.bruce@mayo.edu> wrote in message news:6HgI315nCHA.155@forums.sybase.com... > Do you know anyway for me to exclude a subset of columns returned by this > function. ...

"Mricrosoft SQL Server" or "Mricrosoft SQL Server Dababase File
Hi: Trying to understand this logic: We can choose: "Mricrosoft SQL Server" or "Mricrosoft SQL Server Dababase File" as our data source. I understand that if we choose: "Mricrosoft SQL Server", the database is located in server, such as: c:\program files\Microsoft SQL Server\MSSQL1\Data\Northwind.mdf But if we choose: "Mricrosoft SQL Server Dababase File", what will it happen? does that mean it will create a copy of a database (say Northwind.mdf) to my current web application foler, say in: App_Data foder? What is the main advantage (or purpose) ...

SQL: Column names, can ONLY get "name" instead of "table.name"
Hey all, When iterating through column names returned from a query, I need to get "table.name" rather than just "name" Check it out: If I have an SQL statement "SELECT table1.ID, table2.ID FROM table1, table2;" And table1 has a column name 'ID' and table2 has a column name 'ID' aswell, and i run the query, the dataset will contain column names 'ID' and 'ID' instead of 'table1.ID' and 'table2.ID' It is extremely important that I can differenciate between the 2 fields, and know where each of them come from. The same thing happen...

regarding "in" and "=" in a SQL query
Hi, I am using a sybase database client . When i use "in" in a complex nested query say "select * from emp where emp_id in (select emp_id ..........)" then the execution time is really slow but in the same nested query when i use = as in "select * from emp where emp_id = (select emp_id ..........)", then the query is fast. Is there any particular reason for the same? Regards, Supreeth The obvious difference is that the "=" implies that the subquery can only return a single value as opposed to the "in" which implies that th...

How to give "And" and "OR" in my sql query?
      My layout (.aspx) have 3 TextBox and 2 Dropdownlist and 1 Button which just looks like Search KeyWord 【Texbox1】DorpDownList1 (have 2 items "And" and "Or")  【TextBox2】DorpDownList2 (have 2 items "And" and "Or") 【TextBox3】 Button ( this Button is for submit query)   1 "txtQuery" style="Z-INDEX: 101; LEFT: 152px; POSITION: absolute; TOP: 32px" runat="server" 2 Width="264px"> 3 "ddlBoolean" style="Z-INDEX: 102; LEFT: 152px; ...

quotes, quotes, quotes...
I am getting this error and I know what is causing it, but I have no idea how to fix it, any help would be great. The script steps through the /var/log/messages file on a linux server and puts The entries into a mysql database. However when it gets to the 'hlt' line in the messages file it just barfs. The single quotes are freaking it out. I know about quotes but not how to use in this situation. Thanks, Paul Error: May 27 17:53:00 localhost kernel: Checking 'hlt' instruction... OK. <----- doesn't like this in the messages file DBD::mysql::st exec...

sql for Financial Reporting periods "This Month", "Last Month", "This Quarter", "Last Quarter", "This Year", "Last Year"
Does anyone know of a way to use a funtion for returning records based on fiscal reporting periods like Quickbooks uses for example "This Month", "Last Month", "This Quarter", "Last Quarter", "This Year", "Last Year". While I realize that I can create a very long date time parsing routine  for this but it is not very elegant or useful. I thought there might be a way to do this already with an existing function.I have created a stored procedure that I pass a @ViewRange Parameter to and it returns the records that I want but I ne...

"Error 156: Incorrect syntax near the keyword "WHEN".
Hi i have the following code in my sql database;CASE dbo.tblWorkSchedule.WorkScheduleType_ID WHEN 1 THENCASE IsNull(dbo.tblSurvey.WorkScheduleOverallStatus_ID,0) WHEN 4 THENdbo.tblWorkSchedule.UpliftedRate ELSE dbo.GetSWT_PropertyYearPeriodRate (IsNull(tblRateSchedule.WorkType_ID,0)  ,tblWorkSchedule.WorkSchedule_ID  , tblSurvey.PropertyYear_ID , tblSurvey.PropertyPeriod_ID )  END   I want to add the following lines to it, however it gives me an error "Error 156: Incorrect syntax near the keyword "WHEN". What do i need to do, thank you.  ...

Getting "When connecting to SQL Server 2005 Error" using MS SQL 2000?
I'm new to using VS2005 and this is my first project connecting to our MS SQL 2000. App worked fine on my dev PC, connection to the same MS SQL Server. Published the web site to the web server (same server running MS SQL Server) and getting the below error. Is this a permission setup problem on SQL Server or does it really think it's connecting with a MS SQL 2005 database? An error has occurred while establishing a connection to the server.  When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remot...

How to adopt: Dim rowValues As Object() = {"","",""}?
Hi guys! I'm trying to adopt the code from the Editable Data Grid template. My underlying table has 3 columns: 1st: int(4), identity, autoincrement; 2nd:datetime(8); 3rd:varchar. What do I need to specify in Dim rowValues As Object() = {"","",""} instead of the first and second "" to get rid of the System.FormatException: Input string was not in a correct format… Thanks ...

Web resources about - SQL Server 2005 error: "Invalid object name" / "Incorrect syntax near" - asp.net.sql-datasource

Resources last updated: 1/16/2016 8:09:50 AM