Custom SSMS Shortcuts for ETL Developer. Part 3: Source-Target Mapping
-
Comments posted to this topic are about the item Custom SSMS Shortcuts for ETL Developer. Part 3: Source-Target Mapping
Olga Klimova
BI Consultant, MCITP
www.returnonintelligence.ca -
I so much enjoyed the lecture. I mean this is simply beautiful. I cannot wait to have it up and running in my environment.
I ran the query [sp_SourceTargetMapping] and then drag the Person.Person Table unto the query window after setting up the shortcut for execution but got the following error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '.'.
Does it matter which database is used to run the proc?
Is there anything that I should change in the s. proc e.g @tablename?
Please help out.
Thank you
-
First time I've actually done anything with the short cuts, pretty cool!
-
Thanks a lot for the lovely teaching.But how do I implement it to work in my environment?
-
Thank you!
Please make sure you're enclosing the entire table name in delimited identifiers (quotation marks or brackets - the same measures required as I commented in http://www.sqlservercentral.com/Forums/Topic1179153-3010-3.aspx).
And yes, you need to connect to the 'source' database this table belongs to (AdventureWorks2008R2 in this case).
Olga Klimova
BI Consultant, MCITP
www.returnonintelligence.ca -
Thanks a lot for the lovely teaching.
This proc was ran against Adventureworks2008 without changing anything in the original code.
Do I need to change @TableName to any name in the Adventureworks2008 table? Because when I ran the code I got an error like this:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '.'.
I'd love to know why it didn't work.
Please elaborate on this.
Thank you.
-
Thank you!
In regard to Incorrect syntax near '.':
Please make sure you're enclosing the entire table name in delimited identifiers - use quotation marks or brackets: "Person.Person" (the same measures required as I commented earlier).
And yes, you need to connect to the 'source' database this table belongs to (AdventureWorks2008R2 in this case).
Olga Klimova
BI Consultant, MCITP
www.returnonintelligence.ca -
Beautiful!
It works. Olga, you rock!
-
Wow! thanks a lot Olga. It works.
Nice one.
-
I get SELECT * FROM [Person].[Contact] when I run it.
Thanks,
Thomas
Thomas LeBlanc, MVP Data Platform Consultant
-
Thomas LeBlanc (9/29/2011)
I get SELECT * FROM [Person].[Contact] when I run it.Thanks,
Thomas
Make sure your table is within the double quote, e.g: "HumanResources.Employee"
-
The problem was I created the SP in our DBA database on the instance, then the shortcut was DBA.dbo.sp_xxx.
The SP and table have to be in the same database and the shortcut.
Thanks,
Thomas
Thomas LeBlanc, MVP Data Platform Consultant
-
Works great! Thanks very much for sharing!
I made a couple modifications to the stored procedure for tables with large number of columns.
In the Declaration section, increase size from 4000 to Max:
DECLARE @cmd NVARCHAR (Max)
DECLARE @cmd_loc NVARCHAR (Max)
Then because Print @Cmd is limited to 4000 characters:
-- To see @Cmd when longer than 4,000 characters
If LEN(@Cmd)>4000
BEGIN
Print '@Cmd exceeds 4,000 characters.'
Select @cmd as Cmd Into #tmpCmd
Select Cmd from #tmpCmd
Drop Table #tmpCmd
END
Else
BEGIN
PRINT @cmd -- Max length for Print is 4,000 Characters
END
--EXEC sp_executesql @cmd
Joe Settles, Nashville, TN
-
Looks like the perfect tool for following the ETL Standards as outlined by Joy and Warren in chapter 7. Good job
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply