September 29, 2011 at 12:07 am
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
September 29, 2011 at 2:02 am
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
September 29, 2011 at 5:47 am
First time I've actually done anything with the short cuts, pretty cool!
September 29, 2011 at 5:49 am
Thanks a lot for the lovely teaching.But how do I implement it to work in my environment?
September 29, 2011 at 5:59 am
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
September 29, 2011 at 6:12 am
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.
September 29, 2011 at 6:30 am
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
September 29, 2011 at 6:49 am
Beautiful!
It works. Olga, you rock!
September 29, 2011 at 6:53 am
Wow! thanks a lot Olga. It works.
Nice one.
September 29, 2011 at 7:51 am
I get SELECT * FROM [Person].[Contact] when I run it.
Thanks,
Thomas
Thomas LeBlanc, MVP Data Platform Consultant
September 29, 2011 at 7:56 am
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"
September 29, 2011 at 8:07 am
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
October 10, 2011 at 2:03 pm
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
April 4, 2013 at 12:54 pm
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy