September 27, 2016 at 7:21 am
When I execute Truncate Table [FS-JIREMSQL].JIREM10.dbo.JIRABConcentration in SSMS query window, it fails. The error code is:
Msg 4701, Level 16, State 1, Line 1
Cannot find the object "JIRABConcentration" because it does not exist or you do not have permissions.
From the same query window I am able to execute:
select * from [FS-JIREMSQL].JIREM10.dbo.JIRABConcentration
Also, the following query returns all the fields.
SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE T.name = 'JIRABConcentration'
ORDER BY schema_name, table_name;
Background:
I have moved this database which contains this table from one server to a new server. This was done by backing up the original database in SSMS, copying the .bak file to the new server and using SSMS to Restore the database to the new server. A couple of more details. The old server was running SQL 2008 R2. The new server is running SQL 2014. I created a user on the new server and assigned that user the Public and SysAdmin roles. The same user name existed on the old server and had "similar" roles (it has been so long since I set up that user I do not remember exactly how I created it). I am connected to the server in SSMS as this new user. I am executing the query from SSMS on the new server. I am a VB programmer and not a SQL administrator so a detailed explanation would be greatly appreciated.
How do I correct my setup to eliminate this error?
Any assistance with my problem would be greatly appreciated.
Thanks in advance,
pat
September 27, 2016 at 11:05 am
Update. It does not seem to be a permissions issue. I believe it is a syntax issue.
Truncate Table [FS-JirEMSQL].JIREM10.dbo.JIRABConcentration Fails
But
Truncate Table JIREM10.dbo.JIRABConcentration is successful.
It seems to dislike the full qualification of the connection. Does anyone have an idea what is the correct way to fully qualify the table, including the Server name?
Thanks.
September 27, 2016 at 1:01 pm
mpdillon (9/27/2016)
Update. It does not seem to be a permissions issue. I believe it is a syntax issue.Truncate Table [FS-JirEMSQL].JIREM10.dbo.JIRABConcentration Fails
But
Truncate Table JIREM10.dbo.JIRABConcentration is successful.
It seems to dislike the full qualification of the connection. Does anyone have an idea what is the correct way to fully qualify the table, including the Server name?
Thanks.
When using 4 part naming the first value is NOT the server name, it is the name of the link server. If this is on the same server there is no benefit of using the link server name. And if it is on the same database there is no need to specify the database name (although it is fine). And of course if you have already truncated the table why is there is a need to truncate it again?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 27, 2016 at 1:12 pm
you are already doing that.
if you are using 3 values in the object declaration, you are declaring the database, the schema and the object.
so in your case:
JIREM10.dbo.JIRABConcentration
JIREM10 is the database
dbo is the schema
JIRABConcentration is the object (table, view, stored procedure, etc).
If you are adding a 4th value that would be an instance name which you would need to set up a linked server for (if you wanted to use that).
SQL Experts - please correct me if I am wrong on this.
EDIT - Also, the reason that you are getting that error is that you cannot truncate a table across a database link. BUT you can select, insert, update and delete. So when you specify the first value ([FS-JirEMSQL]) you are using a database link. If you did:
DELETE FROM [FS-JirEMSQL].JIREM10.dbo.JIRABConcentration
it would succeed, but delete is not quite the same as truncate. Presuming the link account has delete permissions.
But if you don't need to use a database link, I would recommend avoiding them.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
September 27, 2016 at 1:36 pm
bmg002 (9/27/2016)
you are already doing that.if you are using 3 values in the object declaration, you are declaring the database, the schema and the object.
so in your case:
JIREM10.dbo.JIRABConcentration
JIREM10 is the database
dbo is the schema
JIRABConcentration is the object (table, view, stored procedure, etc).
If you are adding a 4th value that would be an instance name which you would need to set up a linked server for (if you wanted to use that).
SQL Experts - please correct me if I am wrong on this.
EDIT - Also, the reason that you are getting that error is that you cannot truncate a table across a database link. BUT you can select, insert, update and delete. So when you specify the first value ([FS-JirEMSQL]) you are using a database link. If you did:
DELETE FROM [FS-JirEMSQL].JIREM10.dbo.JIRABConcentration
it would succeed, but delete is not quite the same as truncate. Presuming the link account has delete permissions.
But if you don't need to use a database link, I would recommend avoiding them.
You are correct but it is not a database link. It is a linked server. Pedantic perhaps but it is critical to understand you are talking about another sql server, not a different database. And the database on the other end does not have to be sql server, it can be any OLE DB datasource.
https://msdn.microsoft.com/en-us/library/ff772782.aspx
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 27, 2016 at 1:45 pm
Sean Lange (9/27/2016)
bmg002 (9/27/2016)
you are already doing that.if you are using 3 values in the object declaration, you are declaring the database, the schema and the object.
so in your case:
JIREM10.dbo.JIRABConcentration
JIREM10 is the database
dbo is the schema
JIRABConcentration is the object (table, view, stored procedure, etc).
If you are adding a 4th value that would be an instance name which you would need to set up a linked server for (if you wanted to use that).
SQL Experts - please correct me if I am wrong on this.
EDIT - Also, the reason that you are getting that error is that you cannot truncate a table across a database link. BUT you can select, insert, update and delete. So when you specify the first value ([FS-JirEMSQL]) you are using a database link. If you did:
DELETE FROM [FS-JirEMSQL].JIREM10.dbo.JIRABConcentration
it would succeed, but delete is not quite the same as truncate. Presuming the link account has delete permissions.
But if you don't need to use a database link, I would recommend avoiding them.
You are correct but it is not a database link. It is a linked server. Pedantic perhaps but it is critical to understand you are talking about another sql server, not a different database. And the database on the other end does not have to be sql server, it can be any OLE DB datasource.
Yes. I used "linked server" in the original post, but "database link" in the edit. Corrected myself the first time, but not the second.
Thanks for clairifying. And yay me; I was correct on this.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
September 28, 2016 at 6:04 am
mpdillon (9/27/2016)
I created a user on the new server and assigned that user the Public and SysAdmin roles. The same user name existed on the old server and had "similar" roles (it has been so long since I set up that user I do not remember exactly how I created it). I am connected to the server in SSMS as this new user.
Since it sounds like you have it working, I have to ask about this statement in your original post. I have to ask if this user you created is what's being used to connect from the application. If so, it should not have sysadmin privs. Allowing an application to connect with those privs is just asking for trouble. The login should have the minimum privs necessary to do whatever it is that it does and no more.
October 5, 2016 at 7:21 pm
bmg002 (9/27/2016)
Sean Lange (9/27/2016)
bmg002 (9/27/2016)
you are already doing that.if you are using 3 values in the object declaration, you are declaring the database, the schema and the object.
so in your case:
JIREM10.dbo.JIRABConcentration
JIREM10 is the database
dbo is the schema
JIRABConcentration is the object (table, view, stored procedure, etc).
If you are adding a 4th value that would be an instance name which you would need to set up a linked server for (if you wanted to use that).
SQL Experts - please correct me if I am wrong on this.
EDIT - Also, the reason that you are getting that error is that you cannot truncate a table across a database link. BUT you can select, insert, update and delete. So when you specify the first value ([FS-JirEMSQL]) you are using a database link. If you did:
DELETE FROM [FS-JirEMSQL].JIREM10.dbo.JIRABConcentration
it would succeed, but delete is not quite the same as truncate. Presuming the link account has delete permissions.
But if you don't need to use a database link, I would recommend avoiding them.
You are correct but it is not a database link. It is a linked server. Pedantic perhaps but it is critical to understand you are talking about another sql server, not a different database. And the database on the other end does not have to be sql server, it can be any OLE DB datasource.
Yes. I used "linked server" in the original post, but "database link" in the edit. Corrected myself the first time, but not the second.
Thanks for clairifying. And yay me; I was correct on this.
Does "yay me; I was correct on this" mean that you got it working. If so, can you post the code please. I'm interested in the solution because TRUNCTATE will only accept 3 part object names, not 4.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 6, 2016 at 8:43 am
Jeff Moden (10/5/2016)
bmg002 (9/27/2016)
Sean Lange (9/27/2016)
bmg002 (9/27/2016)
you are already doing that.if you are using 3 values in the object declaration, you are declaring the database, the schema and the object.
so in your case:
JIREM10.dbo.JIRABConcentration
JIREM10 is the database
dbo is the schema
JIRABConcentration is the object (table, view, stored procedure, etc).
If you are adding a 4th value that would be an instance name which you would need to set up a linked server for (if you wanted to use that).
SQL Experts - please correct me if I am wrong on this.
EDIT - Also, the reason that you are getting that error is that you cannot truncate a table across a database link. BUT you can select, insert, update and delete. So when you specify the first value ([FS-JirEMSQL]) you are using a database link. If you did:
DELETE FROM [FS-JirEMSQL].JIREM10.dbo.JIRABConcentration
it would succeed, but delete is not quite the same as truncate. Presuming the link account has delete permissions.
But if you don't need to use a database link, I would recommend avoiding them.
You are correct but it is not a database link. It is a linked server. Pedantic perhaps but it is critical to understand you are talking about another sql server, not a different database. And the database on the other end does not have to be sql server, it can be any OLE DB datasource.
Yes. I used "linked server" in the original post, but "database link" in the edit. Corrected myself the first time, but not the second.
Thanks for clairifying. And yay me; I was correct on this.
Does "yay me; I was correct on this" mean that you got it working. If so, can you post the code please. I'm interested in the solution because TRUNCTATE will only accept 3 part object names, not 4.
heh... no, I am not the OP. What I meant was that I am still learning SQL myself and I was correct about the issue but my terminology was incorrect (database link vs linked server. They are not the same thing).
And as for the code, you would either need to do a
DELETE FROM a.b.c.d
or
TRUNCATE TABLE b.c.d
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply