November 10, 2011

Document that Table!

By sqlches

Scenario: Being assigned to document an existing system, you need to get column level information on tables including descriptions if they exist.

Solution: The code snippet below brings back a listing of all the columns, column types and some other pertinent information for a given table. This could easily be put into a cursor to bring back information on multiple tables, or an SSIS Package or job to do some automatic documentation. (We used it while creating Word Docs about legacy systems)

Snippet:

Declare @Table Varchar(50)

Set @Table = 'TableName'

-- Get me Table Info
Select
  c.Name
  , Case
      When System_Type_Id = 56 Then 'INT'
      When System_Type_Id = 36 Then 'UniqueIdentifier'
      When System_Type_Id = 231 Then 'nVarchar (' + ')'
      When System_Type_ID = 167 Then 'Varchar(' + ')'
      When system_type_id = 175 Then 'Char (' + ')'
      When System_Type_Id = 61 Then 'DateTime'
      When System_Type_Id = 104 Then 'Bit'
      Else 'Unknown'
    End     As DataType
  , Case
      When Is_NullAble = 0 Then 'No'
      Else 'Yes'
    End     As NullAble
  , x.Value As Description
  , system_type_id
From   sys.Tables t With (NoLock)
       Join sys.Columns c With (NoLock)
         on t.Object_Id = c.Object_Id
       left outer Join ( Select
                objName
                , Value
              From   fn_ListExtendedProperty (Null, 'schema', 'dbo', 'table', @Table, 'column', Default) )x
         on c.Name = x.objName Collate DataBase_Default
Where  t.Name = @Table

-- Get me Index Info

Select
  i.Name As IndexName
, Case      When i.Type_desc = 'CLUSTERED' Then 'Yes'
      Else 'No'
    End As isClustered
  , c.Name As ColumnNames
From   sys.Tables t
       Inner Join sys.scHemAs s
         on t.scHema_Id = s.scHema_Id
       Inner Join sys.Indexes i
         on i.Object_Id = t.Object_Id
       Inner Join sys.Index_Columns ic
         on ic.Object_Id = t.Object_Id
       Inner Join sys.Columns c
         on c.Object_Id = t.Object_Id
            And ic.Column_Id = c.Column_Id
Where  i.Index_Id > 0
   And i.Type In ( 1, 2 )

   And ic.Key_Ordinal > 0
   and t.Name = @Table
Order By
  ic.Key_Ordinal

Leave a comment   |  tags: , | posted in Documentation, Legacy, T-SQL

October 6, 2011

What does this mean?!? DTSRun /~Z0x858D6D7A6A3E……..

By sqlches

Scenario: Going into legacy SQL 2000 systems to document or maintain jobs and DTS Packages, many times the command used to run a DTS Package is done encrypted like “DTSRun /~Z0x858D6D7A6A3E0E823BC268…”. A very long completely unreadable command.

Solution: To decode that command do the following…

1. Copy the DTSRUN line (everything including the DTSRUN)
2. Open a Windows Command Line window
3. Paste the DTSRUN line into the CMD window.
4. To the end of the line, add /!X /!C
/!X = do not execute /!C = copy results onto Windows Clipboard
5. Run the command
6. Open Notepad
7. Click Edit>Paste (This will paste the actual command into Notepad and it will show the name of the package.)

Source: Vipul T. Shah – TechNet Blogs

Leave a comment   |  tags: , , | posted in Jobs, Legacy

October 3, 2011

What day does this job run???

By sqlches

Scenario: Creating an automatic report or collecting data about existing jobs. In trying to translate the field freq_interval for freq_type 8 (weekly) to determine what days a job runs. SQL uses a code of Sunday=1, Monday=2, Tuesday=4, Wednesday=8, Thursday=16, Friday=32, Saturday=64. So for those days alone it’s an easy translation but what about Monday/Wednesday/Friday well that’s 42. They get that by adding 2+8+32. So to translate you have to determine what fits into each number from 1-127.

Solution: Working in excel to come up with all the possibilities, I created the following snippet for use as a table variable, this could easily be put into a function and just pass in the freq interval but, for my purposes it was valuable in a join statement.

Snippet:

Declare @Interval Table 
(freq_Interval Int, Interval_Days Varchar( 70 ))

Insert Into @Interval Select 1
, 'Sunday'
Insert Into @Interval Select 2
, 'Monday'
Insert Into @Interval Select 3
, 'Sunday, Monday'
Insert Into @Interval Select 4
, 'Tuesday'
Insert Into @Interval Select 5
, 'Sunday, Tuesday'
Insert Into @Interval Select 6
, 'Monday, Tuesday'
Insert Into @Interval Select 7
, 'Sunday, Monday, Tuesday'
Insert Into @Interval Select 8
, 'Wednesday'
Insert Into @Interval Select 9
, 'Sunday, Wednesday'
Insert Into @Interval Select 10
, 'Monday, Wednesday'
Insert Into @Interval Select 11
, 'Sunday, Monday, Wednesday'
Insert Into @Interval Select 12
, 'Tuesday, Wednesday'
Insert Into @Interval Select 13
, 'Sunday, Tuesday, Wednesday'
Insert Into @Interval Select 14
, 'Monday, Tuesday, Wednesday'
Insert Into @Interval Select 15
, 'Sunday, Monday, Tuesday, Wednesday'
Insert Into @Interval Select 16
, 'Thursday'
Insert Into @Interval Select 17
, 'Sunday, Thursday'
Insert Into @Interval Select 18
, 'Monday, Thursday'
Insert Into @Interval Select 19
, 'Sunday, Monday, Thursday'
Insert Into @Interval Select 20
, 'Tuesday, Thursday'
Insert Into @Interval Select 21
, 'Sunday, Tuesday, Thursday'
Insert Into @Interval Select 22
, 'Monday, Tuesday, Thursday'
Insert Into @Interval Select 23
, 'Sunday, Monday, Tuesday, Thursday'
Insert Into @Interval Select 24
, 'Wednesday, Thursday'
Insert Into @Interval Select 25
, 'Sunday, Wednesday, Thursday'
Insert Into @Interval Select 26
, 'Monday, Wednesday, Thursday'
Insert Into @Interval Select 27
, 'Sunday, Monday, Wednesday, Thursday'
Insert Into @Interval Select 28
, 'Tuesday, Wednesday, Thursday'
Insert Into @Interval Select 29
, 'Sunday, Tuesday, Wednesday, Thursday'
Insert Into @Interval Select 30
, 'Monday, Tuesday, Wednesday, Thursday'
Insert Into @Interval Select 31
, 'Sunday, Monday, Tuesday, Wednesday, Thursday'
Insert Into @Interval Select 32
, 'Friday'
Insert Into @Interval Select 33
, 'Sunday, Friday'
Insert Into @Interval Select 34
, 'Monday, Friday'
Insert Into @Interval Select 35
, 'Sunday, Monday, Friday'
Insert Into @Interval Select 36
, 'Tuesday, Friday'
Insert Into @Interval Select 37
, 'Sunday, Tuesday, Friday'
Insert Into @Interval Select 38
, 'Monday, Tuesday, Friday'
Insert Into @Interval Select 39
, 'Sunday, Monday, Tuesday, Friday'
Insert Into @Interval Select 40
, 'Wednesday, Friday'
Insert Into @Interval Select 41
, 'Sunday, Wednesday, Friday'
Insert Into @Interval Select 42
, 'Monday, Wednesday, Friday'
Insert Into @Interval Select 43
, 'Sunday, Monday, Wednesday, Friday'
Insert Into @Interval Select 44
, 'Tuesday, Wednesday, Friday'
Insert Into @Interval Select 45
, 'Sunday, Tuesday, Wednesday, Friday'
Insert Into @Interval Select 46
, 'Monday, Tuesday, Wednesday, Friday'
Insert Into @Interval Select 47
, 'Sunday, Monday, Tuesday, Wednesday, Friday'
Insert Into @Interval Select 48
, 'Thursday, Friday'
Insert Into @Interval Select 49
, 'Sunday, Thursday, Friday'
Insert Into @Interval Select 50
, 'Monday, Thursday, Friday'
Insert Into @Interval Select 51
, 'Sunday, Monday, Thursday, Friday'
Insert Into @Interval Select 52
, 'Tuesday, Thursday, Friday'
Insert Into @Interval Select 53
, 'Sunday, Tuesday, Thursday, Friday'
Insert Into @Interval Select 54
, 'Monday, Tuesday, Thursday, Friday'
Insert Into @Interval Select 55
, 'Sunday, Monday, Tuesday, Thursday, Friday'
Insert Into @Interval Select 56
, 'Wednesday, Thursday, Friday'
Insert Into @Interval Select 57
, 'Sunday, Wednesday, Thursday, Friday'
Insert Into @Interval Select 58
, 'Monday, Wednesday, Thursday, Friday'
Insert Into @Interval Select 59
, 'Sunday, Monday, Wednesday, Thursday, Friday'
Insert Into @Interval Select 60
, 'Tuesday, Wednesday, Thursday, Friday'
Insert Into @Interval Select 61
, 'Sunday, Tuesday, Wednesday, Thursday, Friday'
Insert Into @Interval Select 62
, 'Monday, Tuesday, Wednesday, Thursday, Friday'
Insert Into @Interval Select 63
, 'Sunday, Monday, Tuesday, Wednesday, Thursday, Friday'
Insert Into @Interval Select 64
, 'Saturday'
Insert Into @Interval Select 65
, 'Sunday, Saturday'
Insert Into @Interval Select 66
, 'Monday, Saturday'
Insert Into @Interval Select 67
, 'Sunday, Monday, Saturday'
Insert Into @Interval Select 68
, 'Tuesday, Saturday'
Insert Into @Interval Select 69
, 'Sunday, Tuesday, Saturday'
Insert Into @Interval Select 70
, 'Monday, Tuesday, Saturday'
Insert Into @Interval Select 71
, 'Sunday, Monday, Tuesday, Saturday'
Insert Into @Interval Select 72
, 'Wednesday, Saturday'
Insert Into @Interval Select 73
, 'Sunday, Wednesday, Saturday'
Insert Into @Interval Select 74
, 'Monday, Wednesday, Saturday'
Insert Into @Interval Select 75
, 'Sunday, Monday, Wednesday, Saturday'
Insert Into @Interval Select 76
, 'Tuesday, Wednesday, Saturday'
Insert Into @Interval Select 77
, 'Sunday, Tuesday, Wednesday, Saturday'
Insert Into @Interval Select 78
, 'Monday, Tuesday, Wednesday, Saturday'
Insert Into @Interval Select 79
, 'Sunday, Monday, Tuesday, Wednesday, Saturday'
Insert Into @Interval Select 80
, 'Thursday, Saturday'
Insert Into @Interval Select 81
, 'Sunday, Thursday, Saturday'
Insert Into @Interval Select 82
, 'Monday, Thursday, Saturday'
Insert Into @Interval Select 83
, 'Sunday, Monday, Thursday, Saturday'
Insert Into @Interval Select 84
, 'Tuesday, Thursday, Saturday'
Insert Into @Interval Select 85
, 'Sunday, Tuesday, Thursday, Saturday'
Insert Into @Interval Select 86
, 'Monday, Tuesday, Thursday, Saturday'
Insert Into @Interval Select 87
, 'Sunday, Monday, Tuesday, Thursday, Saturday'
Insert Into @Interval Select 88
, 'Wednesday, Thursday, Saturday'
Insert Into @Interval Select 89
, 'Sunday, Wednesday, Thursday, Saturday'
Insert Into @Interval Select 90
, 'Monday, Wednesday, Thursday, Saturday'
Insert Into @Interval Select 91
, 'Sunday, Monday, Wednesday, Thursday, Saturday'
Insert Into @Interval Select 92
, 'Tuesday, Wednesday, Thursday, Saturday'
Insert Into @Interval Select 93
, 'Sunday, Tuesday, Wednesday, Thursday, Saturday'
Insert Into @Interval Select 94
, 'Monday, Tuesday, Wednesday, Thursday, Saturday'
Insert Into @Interval Select 95
, 'Sunday, Monday, Tuesday, Wednesday, Thursday, Saturday'
Insert Into @Interval Select 96
, 'Friday, Saturday'
Insert Into @Interval Select 97
, 'Sunday, Friday, Saturday'
Insert Into @Interval Select 98
, 'Monday, Friday, Saturday'
Insert Into @Interval Select 99
, 'Sunday, Monday, Friday, Saturday'
Insert Into @Interval Select 100
, 'Tuesday, Friday, Saturday'
Insert Into @Interval Select 101
, 'Sunday, Tuesday, Friday, Saturday'
Insert Into @Interval Select 102
, 'Monday, Tuesday, Friday, Saturday'
Insert Into @Interval Select 103
, 'Sunday, Monday, Tuesday, Friday, Saturday'
Insert Into @Interval Select 104
, 'Wednesday, Friday, Saturday'
Insert Into @Interval Select 105
, 'Sunday, Wednesday, Friday, Saturday'
Insert Into @Interval Select 106
, 'Monday, Wednesday, Friday, Saturday'
Insert Into @Interval Select 107
, 'Sunday, Monday, Wednesday, Friday, Saturday'
Insert Into @Interval Select 108
, 'Tuesday, Wednesday, Friday, Saturday'
Insert Into @Interval Select 109
, 'Sunday, Tuesday, Wednesday, Friday, Saturday'
Insert Into @Interval Select 110
, 'Monday, Tuesday, Wednesday, Friday, Saturday'
Insert Into @Interval Select 111
, 'Sunday, Monday, Tuesday, Wednesday, Friday, Saturday'
Insert Into @Interval Select 112
, 'Thursday, Friday, Saturday'
Insert Into @Interval Select 113
, 'Sunday, Thursday, Friday, Saturday'
Insert Into @Interval Select 114
, 'Monday, Thursday, Friday, Saturday'
Insert Into @Interval Select 115
, 'Sunday, Monday, Thursday, Friday, Saturday'
Insert Into @Interval Select 116
, 'Tuesday, Thursday, Friday, Saturday'
Insert Into @Interval Select 117
, 'Sunday, Tuesday, Thursday, Friday, Saturday'
Insert Into @Interval Select 118
, 'Monday, Tuesday, Thursday, Friday, Saturday'
Insert Into @Interval Select 119
, 'Sunday, Monday, Tuesday, Thursday, Friday, Saturday'
Insert Into @Interval Select 120
, 'Wednesday, Thursday, Friday, Saturday'
Insert Into @Interval Select 121
, 'Sunday, Wednesday, Thursday, Friday, Saturday'
Insert Into @Interval Select 122
, 'Monday, Wednesday, Thursday, Friday, Saturday'
Insert Into @Interval Select 123
, 'Sunday, Monday, Wednesday, Thursday, Friday, Saturday'
Insert Into @Interval Select 124
, 'Tuesday, Wednesday, Thursday, Friday, Saturday'
Insert Into @Interval Select 125
, 'Sunday, Tuesday, Wednesday, Thursday, Friday, Saturday'
Insert Into @Interval Select 126
, 'Monday, Tuesday, Wednesday, Thursday, Friday, Saturday'
Insert Into @Interval Select 127
, 'Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday'

Select * from @Interval

Results:

freq_Interval Interval_Days
------------- ----------------------------------------------------------------------
1             Sunday
2             Monday
3             Sunday, Monday
4             Tuesday
5             Sunday, Tuesday
6             Monday, Tuesday
7             Sunday, Monday, Tuesday
8             Wednesday
9             Sunday, Wednesday
10            Monday, Wednesday
11            Sunday, Monday, Wednesday
12            Tuesday, Wednesday
13            Sunday, Tuesday, Wednesday
14            Monday, Tuesday, Wednesday
15            Sunday, Monday, Tuesday, Wednesday
16            Thursday
17            Sunday, Thursday
18            Monday, Thursday
19            Sunday, Monday, Thursday
20            Tuesday, Thursday
21            Sunday, Tuesday, Thursday
22            Monday, Tuesday, Thursday
23            Sunday, Monday, Tuesday, Thursday
24            Wednesday, Thursday
25            Sunday, Wednesday, Thursday
26            Monday, Wednesday, Thursday
27            Sunday, Monday, Wednesday, Thursday
28            Tuesday, Wednesday, Thursday
29            Sunday, Tuesday, Wednesday, Thursday
30            Monday, Tuesday, Wednesday, Thursday
31            Sunday, Monday, Tuesday, Wednesday, Thursday
32            Friday
33            Sunday, Friday
34            Monday, Friday
35            Sunday, Monday, Friday
36            Tuesday, Friday
37            Sunday, Tuesday, Friday
38            Monday, Tuesday, Friday
39            Sunday, Monday, Tuesday, Friday
40            Wednesday, Friday
41            Sunday, Wednesday, Friday
42            Monday, Wednesday, Friday
43            Sunday, Monday, Wednesday, Friday
44            Tuesday, Wednesday, Friday
45            Sunday, Tuesday, Wednesday, Friday
46            Monday, Tuesday, Wednesday, Friday
47            Sunday, Monday, Tuesday, Wednesday, Friday
48            Thursday, Friday
49            Sunday, Thursday, Friday
50            Monday, Thursday, Friday
51            Sunday, Monday, Thursday, Friday
52            Tuesday, Thursday, Friday
53            Sunday, Tuesday, Thursday, Friday
54            Monday, Tuesday, Thursday, Friday
55            Sunday, Monday, Tuesday, Thursday, Friday
56            Wednesday, Thursday, Friday
57            Sunday, Wednesday, Thursday, Friday
58            Monday, Wednesday, Thursday, Friday
59            Sunday, Monday, Wednesday, Thursday, Friday
60            Tuesday, Wednesday, Thursday, Friday
61            Sunday, Tuesday, Wednesday, Thursday, Friday
62            Monday, Tuesday, Wednesday, Thursday, Friday
63            Sunday, Monday, Tuesday, Wednesday, Thursday, Friday
64            Saturday
65            Sunday, Saturday
66            Monday, Saturday
67            Sunday, Monday, Saturday
68            Tuesday, Saturday
69            Sunday, Tuesday, Saturday
70            Monday, Tuesday, Saturday
71            Sunday, Monday, Tuesday, Saturday
72            Wednesday, Saturday
73            Sunday, Wednesday, Saturday
74            Monday, Wednesday, Saturday
75            Sunday, Monday, Wednesday, Saturday
76            Tuesday, Wednesday, Saturday
77            Sunday, Tuesday, Wednesday, Saturday
78            Monday, Tuesday, Wednesday, Saturday
79            Sunday, Monday, Tuesday, Wednesday, Saturday
80            Thursday, Saturday
81            Sunday, Thursday, Saturday
82            Monday, Thursday, Saturday
83            Sunday, Monday, Thursday, Saturday
84            Tuesday, Thursday, Saturday
85            Sunday, Tuesday, Thursday, Saturday
86            Monday, Tuesday, Thursday, Saturday
87            Sunday, Monday, Tuesday, Thursday, Saturday
88            Wednesday, Thursday, Saturday
89            Sunday, Wednesday, Thursday, Saturday
90            Monday, Wednesday, Thursday, Saturday
91            Sunday, Monday, Wednesday, Thursday, Saturday
92            Tuesday, Wednesday, Thursday, Saturday
93            Sunday, Tuesday, Wednesday, Thursday, Saturday
94            Monday, Tuesday, Wednesday, Thursday, Saturday
95            Sunday, Monday, Tuesday, Wednesday, Thursday, Saturday
96            Friday, Saturday
97            Sunday, Friday, Saturday
98            Monday, Friday, Saturday
99            Sunday, Monday, Friday, Saturday
100           Tuesday, Friday, Saturday
101           Sunday, Tuesday, Friday, Saturday
102           Monday, Tuesday, Friday, Saturday
103           Sunday, Monday, Tuesday, Friday, Saturday
104           Wednesday, Friday, Saturday
105           Sunday, Wednesday, Friday, Saturday
106           Monday, Wednesday, Friday, Saturday
107           Sunday, Monday, Wednesday, Friday, Saturday
108           Tuesday, Wednesday, Friday, Saturday
109           Sunday, Tuesday, Wednesday, Friday, Saturday
110           Monday, Tuesday, Wednesday, Friday, Saturday
111           Sunday, Monday, Tuesday, Wednesday, Friday, Saturday
112           Thursday, Friday, Saturday
113           Sunday, Thursday, Friday, Saturday
114           Monday, Thursday, Friday, Saturday
115           Sunday, Monday, Thursday, Friday, Saturday
116           Tuesday, Thursday, Friday, Saturday
117           Sunday, Tuesday, Thursday, Friday, Saturday
118           Monday, Tuesday, Thursday, Friday, Saturday
119           Sunday, Monday, Tuesday, Thursday, Friday, Saturday
120           Wednesday, Thursday, Friday, Saturday
121           Sunday, Wednesday, Thursday, Friday, Saturday
122           Monday, Wednesday, Thursday, Friday, Saturday
123           Sunday, Monday, Wednesday, Thursday, Friday, Saturday
124           Tuesday, Wednesday, Thursday, Friday, Saturday
125           Sunday, Tuesday, Wednesday, Thursday, Friday, Saturday
126           Monday, Tuesday, Wednesday, Thursday, Friday, Saturday
127           Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday

(127 row(s) affected)

3 Comments   |  tags: , , , , | posted in Automated-Documentation, Jobs, T-SQL

July 28, 2011

Shred XML Attributes in SQL 2008

By sqlches

Scenario:   Need to shred attribute values out of an XML Variable in SQL 2008

Solution:  Use XQuery and Nodes/Value to pull out the needed values.

Snippet:

declare @XMLMessage xml
set @XMLMessage = '<XMLMessageName xmlns:xsd = "http://www.w3.org/2001/XMLSchema" xmlns:xsi = "http://www.w3.org/2001/XMLSchema-instance" 
 IsResend = "false" StoreID= "195" PartnerID = "-1">
    <LogTime StartDateTime = "2011-06-24T08:47:40-07:00">
        <SalesEvents>
            <SaleEvent
                PartnerID = "-1"
                Comment = ""
                UPC = ""
                Keyword = ""
                Coop = ""
                Category= ""
                Region= "-1"
                InsertionType = "Normal"
                ClerkNumber= "0010068-001"
                Client = ""
                ISCI = ""
                Manufacturer = "Justin"
                Item= "Stuff"
                SaleType= "Credit"
                SaleNumber = "325077"
                BreakID = "-1"
                Sequence = "1"/>
        </SalesEvents>
    </LogTime>
</XMLMessageName >'

select
	ClerkNumber= t.o.value('@ClerkNumber', 'varchar(50)')
	, StoreID= t.o.value('../../../@StoreID', 'integer')

from @XMLMessage.nodes('XMLMessageName /LogTime/SalesEvents/SaleEvent') as t(o)
where t.o.value('@Sequence', 'integer') = 1

Results:

ClerkNumber                                        StoreID            
-------------------------------------------------- -------------------
0010068-001                                        195                
(1 row(s) affected)

Leave a comment   |  tags: , , | posted in Stored Procedures, T-SQL, XML

July 19, 2011

User is DB owner, can’t drop

By sqlches

Scenario:  Trying to drop a database user, and  get an error about not being able to change db_owner.

Solution:  Update the db_owner to be owned by sa.

Snippet:

EXEC sp_changedbowner 'sa'

 

 

Leave a comment   |  tags: , , | posted in Security, T-SQL, Users

July 17, 2011

Create a dbschema file for DB Project reference

By sqlches

Scenario:  When using a VSTS 2008 or VSTS2010 database project, stored procedures and views that reference system items or items in other databases throw warnings or invalid reference errors.

Solution:  Add a database reference to your project, the hardest part, getting the .dbschema file.

Snippet:

To get the schema file, from the Command prompt

Navigate to the Deploy folder under your visual studio installation.

cd C:\Program Files (x86)\Microsoft Visual Studio 10.0\VSTSDB\Deploy

Run the following comand replacing servername and  DBName as appropriate.

vsdbcmd /a:import /cs:"Server=servername;Database=DBName;Trusted_Connection=yes; " /dsp:SQL /ModelFile:"DBName.dbschema"

Once you have the file, copy it to a location you want to reference and use the wizard in VSTS to add the database reference.  For large shops or databases that are shared across multiple projects, a dbschema library on a file share everyone could get to might be appropriate.

1 Comment   |  tags: , , , | posted in DB Project, DBSchema, Project References

July 15, 2011

No column info from stored procedure

By sqlches

Scenario:  Using a stored procedure as the source in an OLE_DB Source for SSIS,  the package can’t detect the output of the stored proc.

Error:  “No column information was returned by the SQL command”

Solution:  Add a no-op statement at the beginning of the stored procedure to set up the pipeline.

Snippet:

If Object_Id('[dbo].[GenMetadata]', 'P') Is Not Null
Drop Procedure [dbo].[GenMetadata]
Go
Create Procedure [dbo].[genMetAdAta]
As
Set NoCount On
/*********** This is what to add ***********************/   If 1 = 0
     Begin
         -- Publish metadata
         Select
           Cast(Null As Int)           As Id
           , Cast(Null As Nchar( 10 )) As [Name]
           , Cast(Null As Nchar( 10 )) As SirName
     End

/*********** end add ***********************/
 -- Do real work starting here
Create Table #Test
 (
 [id]        [Int] Null ,  [Name]    [nChar]( 10 ) Null ,
[SirName] [nChar]( 10 ) Null )

 Insert Into #Test
 Select '1', 'A', 'Z'
 Union All
 Select '2', 'b', 'y'
 Select
 Id, [Name], SirName
 From   #Test
 Drop Table #Test

Return
Go

Source: jaegd – SQL Server Forums

Leave a comment   |  tags: , , | posted in OLE_DB Source, SSIS, Stored Procedures