I am migrating databases to SQL Server 2019, I have a program that reads files to update views in a database, and I am getting an error like 'the name of the object dbo.MyView is not valid (this is translated from Spanish, so in English the exact message may differ a little).
The point is, if I take the query the program is running against the server (a SQL Server 2019) and paste it in SSMS (the MS SQL Server Management Studio) it runs perfectly.
The query is:
ALTER VIEW dbo.MyView AS
SELECT *
FROM DBNAME_C1.dbo.Names
The program is written in C# and runs all ALTER PROCEDURE and ALTER FUNCTION scripts read from files perfectly (there are hundreds), but I get get the error running the ALTER VIEW files.
As I say, if I copy and paste the contents of the file to the SSMS window it runs perfectly.
I am connecting the database with SQL Authentication using the sa user and I am using the same user connecting SSMS to the database (so I know it is not a different user and permissions are the same).
Is there any know reason this should occur ? I am out of ideas. Thanks.
Just to add some background, I have to migrate many client company databases to the cloud and I have to rename the databases (because they are going to be on the same server, and are the same database, so they will have to be DBNAME_C1, DBNAME_C2 and so on, as currently they all connect to DBNAME (each on its own company server).
Make sure you're getting connected to the correct db from C#. It sounds like the view doesn't exist (as if, perhaps, the query is running in the master db??).
The most obvious thing to ask, is your C# program using the correct databases when it ALTERs the views in question? Also, could any of your views be referencing objects in another database, and is that reference valid on the new server?
FYI the sqlcmd utility can also run scripts and has parameters for server and database names:
If you are still having issues with your C# program then sqlcmd might be a useful workaround. It's trivially easy to run multiple script files against multiple servers and databases, possibly even in parallel.
Thanks, it was indeed an error on my side. Views seem to have no limitations in comparison with procedures or functions. All is working now.
As you say, most of my views and stored procedures do have references to other DB objects. I was trying with examples using no references but I guess I was mixing up credentials or databases and was thinking it was something with updating views. (sorry long day). And I have to migrate over 50 databases from nine different companies to one Database server, the main DB of each company is around 12 Gb and smaller DBs are much smaller, so I think I got overwhelmed.
Thanks for that information. I made a C# program because I have to transform a lot of the data. I have to change many stored procedures that access other databases by name, such as queries accessing DBA.dbo.TableName that should now be DBA_C1.dbo.TableName for the company C1 and the same for other companies.
I guess I could have used SQLCMD with PowerShell but I found it easier in C#. The program is processing around 700 database objects for each company.