?
Solved

Ambiguous column name

Posted on 2004-09-09
12
Medium Priority
?
1,218 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

762 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