Solved

Ambiguous column name

Posted on 2004-09-09
12
1,144 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
  • 4
  • 4
  • 3
  • +1
12 Comments
 
LVL 17

Accepted Solution

by:
BillAn1 earned 150 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
 
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 150 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 200 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
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…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

932 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now