Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Ambiguous column name

Posted on 2004-09-09
12
Medium Priority
?
1,246 Views
Last Modified: 2007-12-19
I am running my sql code to output to a text file.  But get the error

SQLState = 37000, NativeError = 209
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Ambiguous column name 'Val'.
SQLState = 37000, NativeError = 8180
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.
NULL

When I try and run the code.  Can anyone help!


-- Populate Temp Database with information from Text File
BULK INSERT DOCSDB..Temp FROM 'c:\SER\test.txt'
WITH (
   DATAFILETYPE = 'char',
   FIELDTERMINATOR = ',',
   ROWTERMINATOR = '\n'
)

--Declare variables
declare @DB_NAME varchar(128)
declare @DB_Match varchar(128)
declare @ColNameSearchingFor varchar(128)
declare @TabNamec varchar (128)
declare @TabName varchar (128)
declare @tablen varchar (128)
--declare cursor
declare SERCur cursor
--Start Loop
for
--Select table name when starts with vtab and add to cursor
     select table_name from information_schema.tables where table_name like 'vtab%'
--open cursor
open SERCur
--Get First record in cursor and save to variable DB_NAME
fetch next from SERCur into @DB_NAME

--Loop unti end of records in cursor
while @@FETCH_STATUS = 0

     begin
     set @TabName = @DB_Name
     set @TabNamec = 'docsdb.dbo.' + @DB_Name + '.val'
     Declare @script varchar(255)
 


     --Select account and date from database

Set @script = 'BCP "SELECT distinct val FROM docsdb.dbo.' + @DB_Name + ',docsdb.dbo.temp where ' + @TabNamec + ' = docsdb.dbo.TEMP.val"  queryout c:\Account.txt -U sa -P cfds -S -c'
Exec master.dbo.xp_cmdshell @script

       
           --get next table name from cursor
         fetch next from SERCur into @DB_NAME
           
     end

--Close Cursor
close SERCur
deallocate SERCur


0
Comment
Question by:running32
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 3
  • +1
12 Comments
 
LVL 17

Accepted Solution

by:
BillAn1 earned 600 total points
ID: 12016536
in your SELECT SQL there are 2 cols, both called val (in the two tables) you need to specify which one you want -

Set @script = 'BCP "SELECT distinct docsdb.dbo.TEMP.val FROM docsdb.dbo.' + @DB_Name + ',docsdb.dbo.temp where ' + @TabNamec + ' = docsdb.dbo.TEMP.val"  queryout c:\Account.txt -U sa -P cfds -S -c'
Exec master.dbo.xp_cmdshell @script
0
 
LVL 5

Expert Comment

by:hkamal
ID: 12016557
This simply means that there are columns called val in both tables :
docsdb.dbo.' + @DB_Name + '  and  docsdb.dbo.temp



You can overcome it by aliasing the tables and referrencing val viz:

SELECT distinct a.val FROM docsdb.dbo.' + @DB_Name + ' a,docsdb.dbo.temp b where ' + @TabNamec + ' = docsdb.dbo.TEMP.val

H
0
 
LVL 6

Expert Comment

by:OlegP
ID: 12016561
try
change
Set @script = 'BCP "SELECT distinct val FROM docsdb.dbo.' + @DB_Name + ',docsdb.dbo.temp where ' + @TabNamec + ' = docsdb.dbo.TEMP.val"  queryout c:\Account.txt -U sa -P cfds -S -c'
to
Set @script = 'BCP "SELECT distinct docsdb.dbo.val FROM docsdb.dbo.' + @DB_Name + ',docsdb.dbo.temp where ' + @TabNamec + ' = docsdb.dbo.TEMP.val"  queryout c:\Account.txt -U sa -P cfds -S -c'
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 6

Expert Comment

by:OlegP
ID: 12016589
BillAn1 is right
0
 

Author Comment

by:running32
ID: 12016607
hkamal, the changes you made output a file but it is a 0 byte file and it give me this error.  Do you have any idea what I'm doing wrong?  thanks

NULL
Starting copy...
NULL
0 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.): total        1
NULL
0
 

Author Comment

by:running32
ID: 12016656
Sorry got that wrong I used BillAn1 suggestion.  
0
 
LVL 5

Expert Comment

by:hkamal
ID: 12016661
Test the query separately if you can (in QA without the BCP). It is possible your result set is 0 (ie no matches between the two tables).
Copy my last query and replace the variables with the literal values
If you get 0 rows returned, your synatx and BCP are correct
H

0
 
LVL 6

Assisted Solution

by:OlegP
OlegP earned 600 total points
ID: 12016703
change
Exec master.dbo.xp_cmdshell @script
to
print @script

and check received scripts using QA (only select instruction)
0
 

Author Comment

by:running32
ID: 12017076
There are 17 tables with the name starting with VTAB.  Some of my tables do not have a match but I know that at least 3 of them have matches.  But I keep getting the error

NULL
Starting copy...
NULL
0 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.): total        1
NULL

when I do a print it gives me

BCP "SELECT distinct docsdb.dbo.VTAB0001.val FROM docsdb.dbo.VTAB0001,docsdb.dbo.temp where docsdb.dbo.VTAB0001.val = docsdb.dbo.TEMP.val"  queryout c:\Account.txt -U sa -P cfds -S -c
BCP "SELECT distinct docsdb.dbo.VTAB0002.val FROM docsdb.dbo.VTAB0002,docsdb.dbo.temp where docsdb.dbo.VTAB0002.val = docsdb.dbo.TEMP.val"  queryout c:\Account.txt -U sa -P cfds -S -c
BCP "SELECT distinct docsdb.dbo.VTAB0003.val FROM docsdb.dbo.VTAB0003,docsdb.dbo.temp where docsdb.dbo.VTAB0003.val = docsdb.dbo.TEMP.val"  queryout c:\Account.txt -U sa -P cfds -S -c
BCP "SELECT distinct docsdb.dbo.VTAB0004.val FROM docsdb.dbo.VTAB0004,docsdb.dbo.temp where docsdb.dbo.VTAB0004.val = docsdb.dbo.TEMP.val"  queryout c:\Account.txt -U sa -P cfds -S -c
BCP "SELECT distinct docsdb.dbo.VTAB0005.val FROM docsdb.dbo.VTAB0005,docsdb.dbo.temp where docsdb.dbo.VTAB0005.val = docsdb.dbo.TEMP.val"  queryout c:\Account.txt -U sa -P cfds -S -c
BCP "SELECT distinct docsdb.dbo.VTAB0006.val FROM docsdb.dbo.VTAB0006,docsdb.dbo.temp where docsdb.dbo.VTAB0006.val = docsdb.dbo.TEMP.val"  queryout c:\Account.txt -U sa -P cfds -S -c
BCP "SELECT distinct docsdb.dbo.VTAB0007.val FROM docsdb.dbo.VTAB0007,docsdb.dbo.temp where docsdb.dbo.VTAB0007.val = docsdb.dbo.TEMP.val"  queryout c:\Account.txt -U sa -P cfds -S -c
BCP "SELECT distinct docsdb.dbo.VTAB0008.val FROM docsdb.dbo.VTAB0008,docsdb.dbo.temp where docsdb.dbo.VTAB0008.val = docsdb.dbo.TEMP.val"  queryout c:\Account.txt -U sa -P cfds -S -c
BCP "SELECT distinct docsdb.dbo.VTAB0009.val FROM docsdb.dbo.VTAB0009,docsdb.dbo.temp where docsdb.dbo.VTAB0009.val = docsdb.dbo.TEMP.val"  queryout c:\Account.txt -U sa -P cfds -S -c
BCP "SELECT distinct docsdb.dbo.VTAB0010.val FROM docsdb.dbo.VTAB0010,docsdb.dbo.temp where docsdb.dbo.VTAB0010.val = docsdb.dbo.TEMP.val"  queryout c:\Account.txt -U sa -P cfds -S -c
BCP "SELECT distinct docsdb.dbo.VTAB0011.val FROM docsdb.dbo.VTAB0011,docsdb.dbo.temp where docsdb.dbo.VTAB0011.val = docsdb.dbo.TEMP.val"  queryout c:\Account.txt -U sa -P cfds -S -c
BCP "SELECT distinct docsdb.dbo.VTAB0012.val FROM docsdb.dbo.VTAB0012,docsdb.dbo.temp where docsdb.dbo.VTAB0012.val = docsdb.dbo.TEMP.val"  queryout c:\Account.txt -U sa -P cfds -S -c
BCP "SELECT distinct docsdb.dbo.VTAB0013.val FROM docsdb.dbo.VTAB0013,docsdb.dbo.temp where docsdb.dbo.VTAB0013.val = docsdb.dbo.TEMP.val"  queryout c:\Account.txt -U sa -P cfds -S -c
BCP "SELECT distinct docsdb.dbo.VTAB0014.val FROM docsdb.dbo.VTAB0014,docsdb.dbo.temp where docsdb.dbo.VTAB0014.val = docsdb.dbo.TEMP.val"  queryout c:\Account.txt -U sa -P cfds -S -c
BCP "SELECT distinct docsdb.dbo.VTAB0015.val FROM docsdb.dbo.VTAB0015,docsdb.dbo.temp where docsdb.dbo.VTAB0015.val = docsdb.dbo.TEMP.val"  queryout c:\Account.txt -U sa -P cfds -S -c
BCP "SELECT distinct docsdb.dbo.VTAB0016.val FROM docsdb.dbo.VTAB0016,docsdb.dbo.temp where docsdb.dbo.VTAB0016.val = docsdb.dbo.TEMP.val"  queryout c:\Account.txt -U sa -P cfds -S -c
BCP "SELECT distinct docsdb.dbo.VTAB0017.val FROM docsdb.dbo.VTAB0017,docsdb.dbo.temp where docsdb.dbo.VTAB0017.val = docsdb.dbo.TEMP.val"  queryout c:\Account.txt -U sa -P cfds -S -c


so I know it is picking up the table names.  
0
 
LVL 5

Assisted Solution

by:hkamal
hkamal earned 800 total points
ID: 12017326
Good. The let's just extend it a bit to see which ines should be returning rows but aren't:

--Declare variables
DECLARE @SQL VARCHAR(200)
DECLARE @Tab VARCHAR(32)

SELECT table_name INTO #Tabs FROM information_schema.tables
WHERE table_name LIKE 'vtab%'

WHILE EXISTS (SELECT 1 FROM #Tabs)
BEGIN
  SELECT @Tab=table_name FROM #Tabs -- no need for rowcount=1
  SET @TabC= "docsdb.dbo." + @Tab + ".val"
  SET @SQL="SELECT DISTINCT a.val FROM docsdb.dbo."+ @Tab + " a, docsdb.dbo.temp b WHERE a.val=b.val"
  SELECT @SQL AS "@SQL"
  EXEC (@SQL)
  DELETE #Tabs WHERE table_name = @Tab
END
       
This way each query's output is preceeded by it's description so you can see which one is guilty

H
0
 
LVL 5

Expert Comment

by:hkamal
ID: 12017345
Oops, clip out the line SET @TabC= "docsdb.dbo." + @Tab + ".val"

It's redundant
0
 

Author Comment

by:running32
ID: 12017701
Thank you all for your help.    Every answer helped me get one step closer.
0

Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how the fundamental information of how to create a table.

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question