Solved

Ambiguous column name

Posted on 2004-09-09
12
1,133 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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how the fundamental information of how to create a table.

760 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

22 Experts available now in Live!

Get 1:1 Help Now