running32
asked on
Ambiguous column name
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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'
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'
BillAn1 is right
ASKER
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
NULL
Starting copy...
NULL
0 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.): total 1
NULL
ASKER
Sorry got that wrong I used BillAn1 suggestion.
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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
BCP "SELECT distinct docsdb.dbo.VTAB0002.val FROM docsdb.dbo.VTAB0002,docsdb
BCP "SELECT distinct docsdb.dbo.VTAB0003.val FROM docsdb.dbo.VTAB0003,docsdb
BCP "SELECT distinct docsdb.dbo.VTAB0004.val FROM docsdb.dbo.VTAB0004,docsdb
BCP "SELECT distinct docsdb.dbo.VTAB0005.val FROM docsdb.dbo.VTAB0005,docsdb
BCP "SELECT distinct docsdb.dbo.VTAB0006.val FROM docsdb.dbo.VTAB0006,docsdb
BCP "SELECT distinct docsdb.dbo.VTAB0007.val FROM docsdb.dbo.VTAB0007,docsdb
BCP "SELECT distinct docsdb.dbo.VTAB0008.val FROM docsdb.dbo.VTAB0008,docsdb
BCP "SELECT distinct docsdb.dbo.VTAB0009.val FROM docsdb.dbo.VTAB0009,docsdb
BCP "SELECT distinct docsdb.dbo.VTAB0010.val FROM docsdb.dbo.VTAB0010,docsdb
BCP "SELECT distinct docsdb.dbo.VTAB0011.val FROM docsdb.dbo.VTAB0011,docsdb
BCP "SELECT distinct docsdb.dbo.VTAB0012.val FROM docsdb.dbo.VTAB0012,docsdb
BCP "SELECT distinct docsdb.dbo.VTAB0013.val FROM docsdb.dbo.VTAB0013,docsdb
BCP "SELECT distinct docsdb.dbo.VTAB0014.val FROM docsdb.dbo.VTAB0014,docsdb
BCP "SELECT distinct docsdb.dbo.VTAB0015.val FROM docsdb.dbo.VTAB0015,docsdb
BCP "SELECT distinct docsdb.dbo.VTAB0016.val FROM docsdb.dbo.VTAB0016,docsdb
BCP "SELECT distinct docsdb.dbo.VTAB0017.val FROM docsdb.dbo.VTAB0017,docsdb
so I know it is picking up the table names.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Oops, clip out the line SET @TabC= "docsdb.dbo." + @Tab + ".val"
It's redundant
It's redundant
ASKER
Thank you all for your help. Every answer helped me get one step closer.
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