Database Project : Unresolved references towards objects

Question

Hi,

I have created a database project. Imported all database objects and settings. Once I added the objects, I got 200+ unresolved references. So, I have added the database references (the dbschema files) to the database project. Many of the errors have gone. But for some objects to give database reference, I dont have dbschema files. And they have 4-part names that means linked server objects. So, can someone please help me how to give reference when there is no dbschema file.

And also, after adding reference, the objects using OPENQUERY is not getting resolved. The format is as shown:

Before Reference:

SELECT * FROM OPENQUERY(BY2MTZ123,HealthDB.dbo.spAdmin_GetAllUsers)

After Adding Reference:

SELECT * FROM OPENQUERY([$(BY2MTZ123)],[$(HealthDB)].dbo.spAdmin_GetAllUsers)

The above statement still throws error.

Please help me,

Thanks,

Balu.

Answer 1

Hi Balu,

Try creating reference of Master database. You need right click on references on database project then select database reference.

Then from floowing location add reference for master.dbschema file

"%programfiles%\Microsoft Visual Studio 9.0\VSTSDB\Extensions\SqlServer\<SQL Version>\DBSchemas"

or

"%programfiles%\Microsoft Visual Studio 10.0\VSTSDB\Extensions\SqlServer\<SQL Version>\DBSchemas"

After adding these references, this unresolved reference error will be resolved.

But, project may take more time to load. If you are experiencing slow loading problem then kindly follow this link for reducing the dbschema size.

http://blogs.msdn.com/b/bahill/archive/2009/03/23/right-sizing-the-master-dbschema-file-for-better-design-time-performance.aspx

Hope this helps.

 

---

-Chintak

Answer 2

Hi Chintak,

Thanks for the reply. I have already added Master Database Schema and the schemas for other databases. But I dont have dbschemas for couple of databases. So, how to avoid reference errors for the objects which doesn't have dbschema references?

Thanks

Balu  

Answer 3

Hi Balu,

If database you are talking are not system database, then you can create .DBSCHEMA file for that database using database project and add it as reference.

If error still exits and error still occures then you can ignore this error using supress warnings as a workaround.  Refer this for more details on this http://msdn.microsoft.com/en-us/library/aa980442(VS.80).aspx

---

-Chintak

Answer 4

Dear Balu,

Thanks for your post. Besides on Chintak’s great reply, I also have some additional explanations, hopeful it helps.

1).For your first question, how to add reference when you haven’t got a .dbschema file.

As Chintak mentioned above, if you want to refer to objects in another database and you want to isolate that database from other developers, you would add a reference to a .dbschema file. You can create a database project for that database then you will get the .dbschema file.

However besides the master.dbschema file and .dbschema file, you also have other reference types: dbproj, SQL CLR project, SQLCLR assembly and an .xsd file. For more details information, please see Using References in Database Projects.

2).For your second question, after adding reference, the objects using OPENQUERY is not getting resolved.

I did a little more digging it is one of the restrictions on Database Reference. Dynamic queries using for example OpenQuery, OpenRowset or OpenDataSource can’t be solved in database reference because the shape of the output can only be determined by executing the actual query against the data source. Database references do not solve all causes for warnings and errors cause by cross database reference, only those caused by explicit 3 and 4-part name usage, not for any of the dynamic query executions strategies. For more details information, please see Restrictions part of the blog.

Let me know if you have any further concerns.

Thanks and Best Regards,

Vicky Song

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help

Answer 5

Thanks Vicky and Chintak,

Thank you very much for your replies. I need some more help. My DB project has linked server objects (4-part names). So how do we add a database reference for a linked server database.

And, as you said, I have to create dbschema file for the database which I can use it as a reference. Even that database project is also throwing these reference errors. So, how to create a .dbschema file is my primary question.

Please help me out.

Thanks,

Balu.

 

Answer 6

Dear Balu,

Sorry for the late reply. If you want to reference linked server objects I have some suggestions, hopeful it can help you.

1).Create a linked server definition in your local instance of SQL Server (the name should be the same as the one you used).

In your local SQL Server Management Studio->Server Objects-> Linked Servers-> right click->New Linked Server…->New Linked Server page shows-> On General Pane, type Linked Server, select Server Type->On Security pane, add a Local Login by clicking the ADD button-> On Server Options  pane, set RPC TRUE, RPC Out TRUE.

For more details information about how to create a linked server definition, please see the following article.

http://msdn.microsoft.com/en-us/library/aa560998(BTS.10).aspx

Note: You should have the permissions (Connect and View definition) to the linked server database, or you would not connect it or see it.

You can achieve these permissions by doing this:

On your remote SQL Server Management Studio-> the database you would like to link->Properties-> on Permissions pane-> click Search Button (at the right side of the Users or roles)->Select Users or Roles page shows->Browse->select [guest] ->Permission for guest->Explicit->check Connect and View definition Grant->OK.

2). Then you can create a database project of the Linked Server database.

Hopeful it helps, Let me know if you have any further concerns.

Thanks and Best Regards,

Vicky Song

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help

Answer 7

You can use vsdbcmd.exe to generate a dbschema file for an existing database.

See http://msdn.microsoft.com/en-us/library/dd193283.aspx. 

Run VS command console and type

vsdbcmd.exe /Action:Import /ConnectionString:"Your connection string here" /ModelFile:"D:\YourSchema.dbschema"

Answer 8

Votes for Rory and he was the only one addressing the posters question!