January 19, 2012 at 2:03 pm
Hi, I am getting an error with the following Insert Statement
INSERT INTO [AnalystsDB].[dbo].[tblTopLevel]
([Account Number],[Customer Name],[Top Level],[Top Level Cust Name],[Top Parent Customer Account],
[Collector ID],[Full Name],[Team],[Market Class])
SELECT DISTINCT rpt_tblOracleCustomers.[Account Number], rpt_tblOracleCustomers.[Customer Name], rpt_tblOracleCustomers.[Top Parent Customer Account] AS [Top Level], rpt_tblOracleCustomers_1.[Customer Name] AS [Top Level Cust Name], rpt_tblOracleCustomers.[Top Parent Customer Account], rpt_tblOracleCustomers.[Collector ID], rpt_tblOracleCollectors.[Full Name], rpt_tblOracleCustomers.Team, [Market Class Bump].[Market Class]
FROM ((rpt_tblOracleCustomers LEFT JOIN rpt_tblOracleCollectors ON rpt_tblOracleCustomers.[Collector ID] = rpt_tblOracleCollectors.[Collector ID]) INNER JOIN rpt_tblOracleCustomers AS rpt_tblOracleCustomers_1 ON rpt_tblOracleCustomers.[Top Parent Customer Account] = rpt_tblOracleCustomers_1.[Account Number]) LEFT JOIN [Market Class Bump] ON rpt_tblOracleCustomers.Class = [Market Class Bump].Class
WHERE (((rpt_tblOracleCustomers.[MCO Customer Account]) Is Null))
GROUP BY rpt_tblOracleCustomers.[Account Number], rpt_tblOracleCustomers.[Customer Name], rpt_tblOracleCustomers.[Top Parent Customer Account], rpt_tblOracleCustomers_1.[Customer Name], rpt_tblOracleCustomers.[Top Parent Customer Account], rpt_tblOracleCustomers.[Collector ID], rpt_tblOracleCollectors.[Full Name], rpt_tblOracleCustomers.[Team], [Market Class Bump].[Market Class]
HAVING (((rpt_tblOracleCustomers.[Team]) Not Like '*Can*'));
Error message is:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'rpt_tblOracleCustomers'.
Anyone know where the error might be? Thanks
January 19, 2012 at 2:07 pm
Either table rpt_tblOracleCustomers doesn't exist or is sitting in a different schema or your account has no privileges on it - SQL Server can't see it.
Try fully qualifying the table, be sure the name is correct and that you can "see" it.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.January 19, 2012 at 3:01 pm
rpt_tblOracleCustomers.[Account Number]
Is incorrect. It is either:
databaseName.rpt_tblOracleCustomers.[Account Number]
OR
rpt_tblOracleCustomers.schemaName.[Account Number]
Jared
CE - Microsoft
January 19, 2012 at 3:01 pm
I added the linked server addresses to the syntax. Code now looks like this:
SELECT DISTINCT [server01\prod01].[ctsreports].rpt.tbloraclecustomers.[Account Number], [server01\prod01].[ctsreports].rpt.tbloraclecustomers.[Customer Name], [server01\prod01].[ctsreports].rpt.tbloraclecustomers.[Top Parent Customer Account] AS [Top Level], [server01\prod01].[ctsreports].rpt.tbloraclecustomers_1.[Customer Name] AS [Top Level Cust Name], [server01\prod01].[ctsreports].rpt.tbloraclecustomers.[Top Parent Customer Account], [server01\prod01].[ctsreports].rpt.tbloraclecustomers.[Collector ID], [server01\prod01].[ctsreports].rpt.tbloraclecollectors.[Full Name], [server01\prod01].[ctsreports].rpt.tbloraclecustomers.Team, [Market Class Bump].[Market Class]
FROM (([server01\prod01].[ctsreports].rpt.tbloraclecustomers LEFT JOIN [server01\prod01].[ctsreports].rpt.tbloraclecollectors ON [server01\prod01].[ctsreports].rpt.tbloraclecustomers.[Collector ID] = [server01\prod01].[ctsreports].rpt.tbloraclecollectors.[Collector ID]) INNER JOIN [server01\prod01].[ctsreports].rpt.tbloraclecustomers AS [server01\prod01].[ctsreports].rpt.tbloraclecustomers_1 ON [server01\prod01].[ctsreports].rpt.tbloraclecustomers.[Top Parent Customer Account] = [server01\prod01].[ctsreports].rpt.tbloraclecustomers_1.[Account Number]) LEFT JOIN [Market Class Bump] ON [server01\prod01].[ctsreports].rpt.tbloraclecustomers.Class = [Market Class Bump].Class
WHERE ((([server01\prod01].[ctsreports].rpt.tbloraclecustomers.[MCO Customer Account]) Is Null))
GROUP BY [server01\prod01].[ctsreports].rpt.tbloraclecustomers.[Account Number], [server01\prod01].[ctsreports].rpt.tbloraclecustomers.[Customer Name], [server01\prod01].[ctsreports].rpt.tbloraclecustomers.[Top Parent Customer Account], [server01\prod01].[ctsreports].rpt.tbloraclecustomers_1.[Customer Name], [server01\prod01].[ctsreports].rpt.tbloraclecustomers.[Top Parent Customer Account], [server01\prod01].[ctsreports].rpt.tbloraclecustomers.[Collector ID], [server01\prod01].[ctsreports].rpt.tbloraclecollectors.[Full Name], [server01\prod01].[ctsreports].rpt.tbloraclecustomers.Team, [Market Class Bump].[Market Class]
HAVING ((([server01\prod01].[ctsreports].rpt.tbloraclecustomers.Team) Not Like "*Can*"));
Now I am receiving the following errors:
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '.'.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'server01\prod01'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'server01\prod01'.
January 19, 2012 at 3:03 pm
EDIT... I counted wrong... Removed statement.
Jared
CE - Microsoft
January 19, 2012 at 3:04 pm
What would the proper syntax be to point to that table?
January 19, 2012 at 3:08 pm
SQLKnowItAll (1/19/2012)
rpt_tblOracleCustomers.[Account Number]Is incorrect. It is either:
databaseName.rpt_tblOracleCustomers.[Account Number]OR
rpt_tblOracleCustomers.schemaName.[Account Number]
First... Use aliases so your select statement is shorter:
SELECT alias.column1, alias.column2
FROM server.database.schema.table alias
Second... Text should be enclosed in single quotes and the wildcard you are looking for is % not *:
Not Like '%Can%'
Jared
CE - Microsoft
January 19, 2012 at 3:30 pm
Is this the right syntax for "FROM server.database.schema.table alias":
"FROM [server01/prod01].[ctsreports].schema.rpt.tbloraclecustomers alias1"
January 19, 2012 at 5:36 pm
guerillaunit (1/19/2012)
Is this the right syntax for "FROM server.database.schema.table alias":"FROM [server01/prod01].[ctsreports].schema.rpt.tbloraclecustomers alias1"
Assuming that server01/prod01 is your server name, ctsreports is the database, rpt is the schema, and tbloraclecustomers is the table name... No. remove the "schema" section. Example, if you drag and drop the table name to the query editor window it will be in this format "schemaName.tableName"
Then add the serverName.databaseName. to the front of that.
Jared
CE - Microsoft
January 23, 2012 at 10:13 am
I've made some changes, but I am still getting the errors highlighted in red
SELECT DISTINCT [server01/prod01].[ctsreports].rpt.tbloraclecustomers.[Account Number],
[server01/prod01].[ctsreports].rpt.tbloraclecustomers.[Customer Name],
[server01/prod01].[ctsreports].rpt.tbloraclecustomers.[Top Parent Customer Account] AS [Top Level],
[server01/prod01].[ctsreports].rpt.tbloraclecustomers_1.[Customer Name] AS [Top Level Cust Name],
[server01/prod01].[ctsreports].rpt.tbloraclecustomers.[Top Parent Customer Account],
[server01/prod01].[ctsreports].rpt.tbloraclecustomers.[Collector ID],
[server01/prod01].[ctsreports].rpt.tbloraclecollectors.[Full Name],
[server01/prod01].[ctsreports].rpt.tbloraclecustomers.Team, [Market Class Bump].[Market Class]
FROM (([server01/prod01].[ctsreports].rpt.tbloraclecustomers LEFT JOIN [server01/prod01].[ctsreports].rpt.tbloraclecollectors
ON [server01/prod01].[ctsreports].rpt.tbloraclecustomers.[Collector ID] = [server01/prod01].[ctsreports].rpt.tbloraclecollectors.[Collector ID])
INNER JOIN [server01/prod01].[ctsreports].rpt.tbloraclecustomers AS 'rpt.tbloraclecustomers_1'
ON [server01/prod01].[ctsreports].rpt.tbloraclecustomers.[Top Parent Customer Account] = [server01/prod01].[ctsreports].rpt.tbloraclecustomers_1.[Account Number])
LEFT JOIN [Market Class Bump] ON [server01/prod01].[ctsreports].rpt.tbloraclecustomers.Class = [tblMarketClassBump].Class
WHERE ((([server01/prod01].[ctsreports].rpt.tbloraclecustomers.[MCO Customer Account]) Is Null))
GROUP BY [server01/prod01].[ctsreports].rpt.tbloraclecustomers.[Account Number],
[server01/prod01].[ctsreports].rpt.tbloraclecustomers.[Customer Name],
[server01/prod01].[ctsreports].rpt.tbloraclecustomers.[Top Parent Customer Account],
[server01/prod01].[ctsreports].rpt.tbloraclecustomers_1.[Customer Name],
[server01/prod01].[ctsreports].rpt.tbloraclecustomers.[Top Parent Customer Account],
[server01/prod01].[ctsreports].rpt.tbloraclecustomers.[Collector ID],
[server01/prod01].[ctsreports].rpt.tbloraclecollectors.[Full Name],
[server01/prod01].[ctsreports].rpt.tbloraclecustomers.Team, [tblMarketClassbump].[Market Class]
HAVING ((([server01/prod01].[ctsreports].rpt.tbloraclecustomers.[Team]) Not Like '%Can%'));
The red parts look correct. Any idea why it's erroring out?
January 23, 2012 at 10:22 am
This: 'rpt.tbloraclecustomers_1' should not have a . in it and really should not have the single quotes around it. Try an alias for this AS tblOracleCustomers_1
Jared
CE - Microsoft
January 23, 2012 at 10:25 am
What red parts?
Please, please use table aliases to make that easier to read.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 23, 2012 at 10:25 am
Your joins are a bit funny as well. Why are you joining to the same table so many times? Also, why are you not using aliases like I suggested? Finally, look at the order of your joins...
Jared
CE - Microsoft
January 23, 2012 at 10:30 am
Ok, I'm looking at this more closely and really trying to figure out what you are trying to do, as it is not clear. Can you please provide the DDL for the tables included in the query, some sample data for each, and finally the expected results of this query. Also, I don't know why you have so many parentheses and other garbage in the query. Is this a generated query or did you write this yourself?
Jared
CE - Microsoft
January 23, 2012 at 10:43 am
Does this query accomplish what you were trying to do?
SELECT DISTINCT toc.[Account Number],
toc.[Customer Name],
toc.[Top Parent Customer Account] AS [Top Level],
toc1.[Customer Name] AS [Top Level Cust Name],
toc.[Top Parent Customer Account],
toc.[Collector ID],
tocoll.[Full Name],
toc.Team,
mcb.[Market Class]
FROM [server01/prod01].[ctsreports].rpt.tbloraclecustomers toc
INNER JOIN [server01/prod01].[ctsreports].rpt.tbloraclecustomers AS toc1
ON [toc.[Top Parent Customer Account] = toc1.[Account Number])
LEFT JOIN [server01/prod01].[ctsreports].rpt.tbloraclecollectors tocoll
ON toc.Collector ID = tocoll.[Collector ID])
LEFT JOIN [Market Class Bump] mcb
ON toc.Class = mcb.Class
WHERE toc.[MCO Customer Account] IS NULL AND toc.Team Not Like '%Can%'
Jared
CE - Microsoft
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply