June 1, 2015 at 8:49 am
Hello everyone. I am new to T-SQL but I have been working on a project. I am attempting to use a mix of dynamic SQL and the CMDExecute functions to import data from my .err files as a flat file source. The files have multiple delimiters and need to be parsed on import into columns with a data type for each column in my database Tables. I used an example of code from Big SQL or something like that and I ended up with this code after my modifications. I want to import the data and store it into the database table and truncate the temp table. Each time this stored procedure is used it will clear out the previous .err files and start over again with fresh data for the newest day available. this is my code.
USE QMLOGS
GO
---- Begin: Code starts here
Create Table QABSNew (
ID Int Identity(1, 1),
logTime datetime,
fromServer Varchar(20),
toServer VARCHAR(20),
service varchar(20),
messageId varchar(Max),
filePath varchar(max)
)
TRUNCATE TABLE dbo.QABSNew
Go
--- Begin: Input Data From File into Temporary / Staging Table
Truncate Table temp_LogImporter
Declare @Command Varchar(2000)
Declare @Result Int
-- Note: Make changes below according to your needs
Set @Command = 'bcp TestDB..temp_LogImporter in C:\Users\Public\Documents\QMLogFiles.err -fC:\Users\Public\Documents\QMLogFiles.FMT -SLapTopFarhanKhan -Usa -P123' -- Change This section according to your needs
Exec @Result = Master..xp_cmdshell @Command, NO_OutPut
-- Begin: Inserting Records (Which are new in file)
Insert INTO dbo.QABSNew(
[logTime],
[fromServer],
[toServer],
[service] ,
[messageId] ,
[filePath])
SELECT T.logTime, T.filePath, T.fromServer, T.toServer, T.service, T.messageId
FROM QABSNew T
LEFT JOIN dbo.QABSNew E on T.logTime = E.logTime
WHERE ISNULL (E.logTime, '') = ''
Select * From dbo.QABSNew
-- Begin: Updating Records
Update dbo.QABSNew
SET logTime = T.logTime,
fromServer = T.fromServer,
toServer = T.toServer,
filePath = T.filePath,
Service = T.Service,
messageId = T.messageId
From QABSNew E
Inner Join QABSNew T On E.logTime = T.logTime
June 1, 2015 at 8:53 am
Create Table QABSNew(
logTime datetime,
fromServer varchar(20),
toServer varchar(20),
Service varchar(20),
messageId varchar(MAX),
filePath Varchar(MaX)
)
June 1, 2015 at 8:56 am
Why is Line 29 Ambiguous???
June 1, 2015 at 9:10 am
dunn_Stephen (6/1/2015)
Why is Line 29 Ambiguous???
What is "Line 29", and what is the error message you're getting ?
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
June 1, 2015 at 9:13 am
I am getting the error dbo.QABSNew is ambiguous line 29 level 16
June 1, 2015 at 9:14 am
I don't think I referenced the temp table correctly. I am trying to import the data using the BCP and parse the data into columns then import into the QABSNew Table in the database
June 1, 2015 at 9:16 am
Im not even getting returned data in my rows either. I made a mistake just don't know where. Probaby referencing the BCP to my filepath locations
June 1, 2015 at 9:20 am
I don't understand what you're trying to do here
SELECT T.logTime, T.filePath, T.fromServer, T.toServer, T.service, T.messageId
FROM QABSNew T
LEFT JOIN dbo.QABSNew E on T.logTime = E.logTime
WHERE ISNULL (E.logTime, '') = ''
Why are you joining the table to itself with identical columns on both sides and then applying a filter that can't ever return rows? Since you're joining the table to itself, every LogTime must match (it's the same rows), so unless you have any with '' as values, that where clause will filter out all rows.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 1, 2015 at 9:28 am
Im trying to insert data into the QABSNew E table where E.logtime isNull
June 1, 2015 at 9:37 am
No, still doesn't make sense.
You're joining a table to itself, with a filter that cannot return any rows, then trying to insert back into the exact same table...
I think you need to go back several steps, do this piece by piece, get each small portion working one by one. It looks like you've downloaded some code which you're trying to adapt without fully understanding it, and that's just going to make things much harder.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 1, 2015 at 9:40 am
It was kinda the reason I came here to get answers. I need help understanding it but that's ok. Thank you for your help.
June 1, 2015 at 9:41 am
Actually should have looked more like this.
Insert INTO dbo.QABSNew(
[logTime],
[fromServer],
[toServer],
[service],
[messageId],
[filePath])
SELECT T.logTime, T.filePath, T.fromServer, T.toServer, T.service, T.messageId
FROM QABSNew T
LEFT JOIN temp_LogImporter E on T.logTime = E.logTime
WHERE ISNULL (E.logTime, '') = ''
June 1, 2015 at 9:56 am
I'm not sure what are you doing and why you're using so many self-joins.
But, to answer your question, dbo.QABSNew is ambiguous because you are referencing it twice in your FROM clause. A better option is to use an alias.
Update E
SET logTime = T.logTime,
fromServer = T.fromServer,
toServer = T.toServer,
filePath = T.filePath,
Service = T.Service,
messageId = T.messageId
From QABSNew E
Inner Join QABSNew T On E.logTime = T.logTime;
I'm just mentioning this to help you understand the error. As others have mentioned, you should review the logic for your process and code.
June 1, 2015 at 9:57 am
Now that looks a bit more sensible.
Don't try to debug as a whole. Work in small pieces, make sure that each small piece works, repeat until everything is working correctly.
And please remember that we can't see your screen and we don't know what you're doing, so if you need help, be explicit and detailed. I still don't know the exact error message you got (not a paraphrased message, the exact text), nor which line it's referring to (you however can double-click the error and be shown the exact line)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 1, 2015 at 10:04 am
Ok. Thanks. I will revise and work on smaller pieces and get back to the forum. Thanks.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply