Another BI & Programming Blog - Jason Yousef

Introduction This is a review of a great utility that I evaluated as a possible solution for replacing the Intellisense in SQL server management studio named dbForge SQL Complete which could be found at http://www.devart.com/dbforge/sql/sqlcomplete/ . I was surfing the SQL Server newsgroups for help with some T-SQL issues and I saw a post with a link to that utility. I went to their website and downloaded a free evaluation for 30 days. I could not believe how simple it was to use and how thoroughly it solved my code writing problems. I am still a fan and hooked and it's one of the tools that I need to install on each machine that I’m working on, wherever I go! The Environment The evaluation was done on various operating systems and various SQL server versions, windows vista 32 BIT with SQL server 2008, windows 7 enterprise 64Bit with SQL server 2008R2 and windows 7 enterprise 64Bit with SQL server 2005. Download The best thing ever that companies could offer a free trial of their p...

Today I came across a question in EE  of how to run a VBscript using SQL server. Well, it could be done using the SQL server agent or using SSIS, but the easiest way would be the SQL agent. Here how: 1-Right click on "SQL Server Agent" and select "New Job" 2-Name the job according to your needs. 3-From the "Steps" , select New. 4-Name it to your needs and select the "Type" as "Operating System CmdExec" as shown in the screenshot. 4-Set the schedule as desired and that's it!!

Today I came across a question in EE: The person gets a unique file everyday, file name in the format 1234_Test.txt and the first four digits changes everyday.  Also need to place the output file and rename it based on a column value. For ex: If a column named code is 2, then my output file should be 2_1234_TEST.txt So it could be done by using a "File Watcher Task" to check for the file when it's available and put the file name into a variable. Or using SSIS native task, using a "Foreah Loop" task to get the file name into variable, use a "script task" into the data flow to get the column value into another variable. The a 3rd variable to join both of them and the destination folder. The package could be downloaded from http://www.box.net/shared/lx4j79a39z steps are: 3 variables -  type (STRING)  Foreach Loop config  File system task config  Expression on the variable  Clearer View  Data Flow Task Script component con...

I came across a question today about how to process only the first 10000 rows from a flat file. It could be done by 3 ways, either using the "RowNumber transformation" from http://sqlis.com/post/Row-Number-Transformation.aspx  . or by using a "Script task" which I'll blog about it later or by using the "Row Sampling" Task. "Row Sampling" would be the easiest way if you need to grab the FIRST 1000 or the FIRST any number. But using a "Script Task" or the "RowNumber transformation" will grab any selective rows, such as "Grab rows 150 to 400". Practice  package can be downloaded from : http://www.box.net/shared/ajzt5nccue I'm using here the Data Generator source Task which could be downloaded for free from http://sqlis.com/post/Data-Generator-Source-Adapter.aspx just to generate 5000 rows, you can use a flat file as normal.  Row Sampling configuration.  And the results... Hope that helps. 

Ever wondered when that index was created ! Select  s.name, t.name, t.create_date, t.modify_date,i.name, c.name 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) -- clustered & nonclustered only and i.is_primary_key = 0 -- do not include PK indexes and i.is_unique_constraint = 0 -- do not include UQ and i.is_disabled = 0 and i.is_hypothetical = 0 and ic.key_ordinal > 0 Order by ic.key_ordinal well I'm not sure what's the best way, or if there's any way to get this info. but you could get the table's creation date and use that as a reference...

I came across a question today about how to process only the first 10000 rows from a flat file. It could be done by 3 ways, either using the "RowNumber transformation" from http://sqlis.com/post/Row-Number-Transformation.aspx  . or by using a "Script task" which I'll blog about it later or by using the "Row Sampling" Task. "Row Sampling" would be the easiest way if you need to grab the FIRST 1000 or the FIRST any number. But using a "Script Task" or the "RowNumber transformation" will grab any selective rows, such as "Grab rows 150 to 400". Practice  package can be downloaded from : http://www.box.net/shared/ajzt5nccue I'm using here the Data Generator source Task which could be downloaded for free from http://sqlis.com/post/Data-Generator-Source-Adapter.aspx just to generate 5000 rows, you can use a flat file as normal. I'm here selecting only the first 1000 rows or whatever number, using a...

I came across a question today about how to process only the first 10000 rows from a flat file. It could be done by 3 ways, either using the "RowNumber transformation" from http://sqlis.com/post/Row-Number-Transformation.aspx  . or by using a "Script task" which I'll blog about it later or by using the "Row Sampling" Task. "Row Sampling" would be the easiest way if you need to grab the FIRST 1000 or the FIRST any number. But using a "Script Task" or the "RowNumber transformation" will grab any selective rows, such as "Grab rows 150 to 400". Practice  package can be downloaded from : http://www.box.net/shared/ajzt5nccue I'm using here the Data Generator source Task which could be downloaded for free from http://sqlis.com/post/Data-Generator-Source-Adapter.aspx just to generate 5000 rows, you can use a flat file as normal.  RowNumber Transformation, adds a column named 'RowNumber' or any n...