Solved

Ambiguous column name

Posted on 2004-09-09
12
1,180 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
Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

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 article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

685 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