Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Querying the INFORMATION_SCHEMA Expand / Collapse
Author
Message
Posted Friday, March 7, 2008 10:11 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, October 15, 2013 8:59 AM
Points: 62, Visits: 57
It appears that whatever you hightlight is passed to the stored procedure as a single string. It doesn't parse out commas, and positively objects to quotes.
So I bent to the wind, and simply parsed out the values from the single input parameter. This makes it uglier to use when not using the keyboard shortcut, but when will that happen?

Cheers!
Post #466005
Posted Monday, March 10, 2008 12:27 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, February 25, 2014 11:28 AM
Points: 145, Visits: 193
Yes, indeed, it treats the whole comma delim'd parameter as a string - BUT ONLY IN SSMS.

It works perfectly in SQA. I can't figure out why. One forum poster said it worked for him in SSMS.
Post #466877
Posted Tuesday, March 11, 2008 3:10 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, August 14, 2014 2:22 AM
Points: 2,684, Visits: 2,438
Mike DiRenzo (3/10/2008)
One forum poster said it worked for him in SSMS.


Yes and it still does!...
even with multiple parameters and ones delimited by single quotes

create proc kev_test
(
@param1 varchar(50),
@param2 varchar(50)
)
as set nocount on
select 'the first parameter is ' + @param1 + ' the second is ' + @param2

assign Ctrl-5 to kev_test

new window..

'first param','second param'

highlight it, press Ctrl-5 and results are
the first parameter is first param the second is second param

Post #467238
Posted Tuesday, March 11, 2008 7:00 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, October 15, 2013 8:59 AM
Points: 62, Visits: 57
Definitely there's some option/setup in SSMS -- even on the builtins, if I put single quotes around the string and press Alt-F1, it gives me the error:

Incorrect syntax near 'sp_help'.

I wouldn't have a clue where to go from that, though. MSDN Help has, as it often is, been unhelpful, and I haven't found the magic words for Google.
Post #467345
Posted Tuesday, March 11, 2008 7:03 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, October 15, 2013 8:59 AM
Points: 62, Visits: 57
Microsoft SQL Server Management Studio 9.00.1399.00
Microsoft Analysis Services Client Tools 2005.090.1399.00
Microsoft Data Access Components (MDAC) 2000.085.1117.00 (xpsp_sp2_rtm.040803-2158)
Microsoft MSXML 2.6 3.0 4.0 5.0 6.0
Microsoft Internet Explorer 7.0.5730.11
Microsoft .NET Framework 2.0.50727.832
Operating System 5.1.2600 (XP Pro 2002 SP2)
Post #467350
Posted Tuesday, March 11, 2008 7:06 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, August 14, 2014 2:22 AM
Points: 2,684, Visits: 2,438
looks like you haven't applied SP2 to your client tools

Microsoft SQL Server Management Studio 9.00.3042.00
Microsoft Analysis Services Client Tools 2005.090.3042.00
Microsoft Data Access Components (MDAC) 2000.085.1117.00 (xpsp_sp2_rtm.040803-2158)
Microsoft MSXML 2.6 3.0 4.0 5.0 6.0
Microsoft Internet Explorer 7.0.5730.13
Microsoft .NET Framework 2.0.50727.1433
Operating System 5.1.2600
Post #467354
Posted Tuesday, March 11, 2008 7:42 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, February 25, 2014 11:28 AM
Points: 145, Visits: 193
I have other similar macro scripts that work in SQA and not SSMS. It has been really frustrating not to be able to use them effectively in SSMS - especially if you are a type and typo centric TSQL'r.

I will apply SP2 and see if it works. Thanks for the posts and the insight re: SP2.

-Mike
Post #467394
Posted Tuesday, March 11, 2008 8:50 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, October 15, 2013 8:59 AM
Points: 62, Visits: 57
That does the trick!
Thanks!
Post #467474
Posted Tuesday, March 18, 2008 4:34 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, November 5, 2012 1:00 PM
Points: 128, Visits: 32
Top tip for working with the INFORMATION_SCHEMA views: use Excel to view them.

In Excel, go to Data, Import External Data..., then either Import Data or New Database Query. Use or create a DSN and fish out the INFORMATION_SCHEMA view that your interested in. COLUMNS is the one I use the most.

Then use Excel's Autofilter and you got yourself a Data Dictionary that you can filter to find, say, all fields with the same name, all fields of the same datatype (use char instead of nchar and have a feeling you've done it somewhere else too?), or whatever.




Post #471278
Posted Tuesday, March 18, 2008 8:56 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 3:51 PM
Points: 36,959, Visits: 31,469
Andrew_Webster (3/18/2008)
Top tip for working with the INFORMATION_SCHEMA views: use Excel to view them.

In Excel, go to Data, Import External Data..., then either Import Data or New Database Query. Use or create a DSN and fish out the INFORMATION_SCHEMA view that your interested in. COLUMNS is the one I use the most.

Then use Excel's Autofilter and you got yourself a Data Dictionary that you can filter to find, say, all fields with the same name, all fields of the same datatype (use char instead of nchar and have a feeling you've done it somewhere else too?), or whatever.


Hadn't thought about that... that's a very clever and useful idea! Thanks, Andrew!


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #471342
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse