Programmabilities Blog

 
SQL Server 2005 Reports: tips
SSRS:

• Page Header:
  • TextBox via Expression...: =Globals.ReportFolder & Globals.ReportName
• Page Footer:
  • Page Footer: TextBox via Expression...: =Format(Globals!PageNumber) + " of " + Format(Globals!TotalPages) + " pages"
  • ="Printed by " + User!UserID + " on " + DateTime.Now.ToString()
  • ="Execution Time: " + IIf(System.DateTime.Now.Subtract(Globals!ExecutionTime).TotalSeconds < 1, "0 seconds", ( IIf(System.DateTime.Now.Subtract(Globals!ExecutionTime).Hours > 0, System.DateTime.Now.Subtract(Globals!ExecutionTime).Hours & " hour(s), ", "") + IIf(System.DateTime.Now.Subtract(Globals!ExecutionTime).Minutes > 0, System.DateTime.Now.Subtract(Globals!ExecutionTime).Minutes & " minute(s), ", "") + IIf(System.DateTime.Now.Subtract(Globals!ExecutionTime).Seconds > 0, System.DateTime.Now.Subtract(Globals!ExecutionTime).Seconds & " second(s)", "")) )
• Body:
  • TextBox via Expression...: =Format(Sum(Fields!Account.Value), "C0") (Works if column is numeric data type.)
  • Alternate rows by adding the following to the row via Properties > BackgroundColor expression of your detail row: =iif(RowNumber(Nothing) Mod 2, "White", "Gainsboro")

• *http://www.ssw.com.au/Ssw/Standards/Rules/RulesToBetterSQLReportingServices.aspx
• http://download.microsoft.com/download/1/3/4/134644fd-05ad-4ee8-8b5a-0aed1c18a31e/reporting_services_design.doc
• http://notethat.blogspot.com/2007/10/reporting-services-tips-tricks-and.html

Labels: sql

- posted by programmabilities @ 12:51 0 comments links to this post

 
SSIS > VS Export wizard: table to Excel
After using the SSIS > VS Export wizard for table to Excel, you must add DROP TABLE Query to the Event Handlers tab or else it will error the second time it is reRun.

SSIS > VS Import wizard for Excel to table:

• Be sure to only checkmark Results$.
• Be sure to Select Delete table option.
• Be sure to Change Results$ name to Destination table name.
• If there is an error, it is often because a row in Excel has a NULL but the table column is NOT NULL (so add a blank to the Excel field).

Labels: sql

- posted by programmabilities @ 17:23 0 comments links to this post

 
Create SSIS to copy tables

An SSIS to copy tables from a Source to a Destination. Directions:

1. From Start, select SQL Server Business Intelligence Development Studio
2. File
   1. New Project…
      1. Project types: Business Intelligence Projects
      2. Integration Services Project…
      3. Click “OK”
3. Toolbox
   1. Drag "Transfer SQL Server Objects Task" to "Control Flow" tab's pane.
      1. Right-click choose Edit…
         1. Objects
            1. SourceConnection ‹New connection…› = NWDSQL
            2. SourceDatabase = NIS_empl_wage
            3. DestinationConnection ‹New connection…› = TESTSQL
            4. DestinationDatabase = NIS_empl_wage
            5. CopyData = True
            6. ExistingData = Replace (so not append (dup errors))
            7. ObjectsToCopy (expand)
               1. CopyAllTables = True (so not sprocs)
            8. Note: Leave all other options False. Ex., Table Options—Can leave all these False because it will just move data so Destination’s Primary Keys will remain.
      2. Click “OK”
4. Save and Run

Note 1: Your Properties for the Transfer SQL Server Objects Task will now look like this:

1. CopyAllTables = True (so not sprocs)
2. CopyData = True
3. DestinationConnection = TESTSQL
4. DestinationDatabase = NIS_empl_wage
5. ExistingData = Replace (so not append (dup errors))
6. SourceConnection = NWDSQL
7. SourceDatabase = NIS_empl_wage

Note 2: A DB "restore" by the DBA would nix Destination tables that do not exist in Source tables; but not this technique.

Labels: sql

- posted by programmabilities @ 12:28 0 comments links to this post

 
CASE: This way (3) NULLs get put in tbl if data is blank.

command = New SqlCommand("INSERT INTO tblFoo (UIAcctNum, WorkZip, CheckRouteDesc, Agency, Division, RUN, Rpt_Unit_RUNDesc, StartDate,EndDate ,CheckRouteCode, Rpt_Unit_RUNDesc__old,NAICS) " & _ 
" VALUES (@UIAcctNum, @WorkZip, @CheckRouteDesc, @Agency, @Division, @RUN, " & _ 
"CASE WHEN ltrim(@Rpt_Unit_RUNDesc) = '' THEN NULL ELSE @Rpt_Unit_RUNDesc END, " & _ 
"@StartDate, " & _ 
"CASE WHEN ltrim(@EndDate) = '' THEN NULL ELSE @EndDate END, " & _ 
"@CheckRouteCode, " & _ 
"CASE WHEN ltrim(@Rpt_Unit_RUNDesc__old) = '' THEN NULL ELSE @Rpt_Unit_RUNDesc__old END, " & _ 
" CASE WHEN ltrim(@NAICS) = '' THEN NULL ELSE @NAICS END)", connection) ' This way (3) NULLs get put in tbl if data is blank.

Labels: sql

- posted by programmabilities @ 18:42 0 comments links to this post

 
SQL notes

• SELECT DISTINCT * INTO ##temp3 from ##temp2 --Use this if I want to INSERT rows AND create the destination table.
• WHERE ##temp1.Area <> ##temp2.Area -- The 'WHERE <>' stops a cartesian join.
• SELECT * INTO ##temp3 FROM (SELECT * FROM ##temp1 UNION SELECT * FROM ##temp2) un
• strSQL = "IF OBJECT_ID('tblTempNIS_Qtrly_SumByRUN_and_crc', 'U') IS NOT NULL DROP TABLE tblTempNIS_Qtrly_SumByRUN_and_crc" ' This is not really a temp table because no #; so must DROP. It's a helper table. Couldn't use a temp because mData's Clear lose the data for the next query's use of it. ' The ", 'U' " is optional; it just insures that the object is a user table and not, for example, a sproc or other type of object.

• 'To get all information about the @@Error value that is returned in the output parameter 
  'From Master database 
  'SELECT * FROM sysmessages WHERE Error = 2627 'type value in rdoQy(3) here

• --NWDSQL.BmrkSource.PendingPublish is empty for 2003-2004. Thus do this to populate it with rows from the other table:
  INSERT INTO BmrkSource.dbo.PendingPublish
  SELECT NEArea,IdentifierCode,EndDate,NumberOfPeople,Week,ItemCodeID,Ratio --Note: Published has 1 extra column at the end so I must list these out.
  FROM NELausData.dbo.Published
  WHERE NELausData.dbo.Published.EndDate LIKE '2004%'
  OR NELausData.dbo.Published.EndDate LIKE '2003%'

Labels: sql

- posted by programmabilities @ 22:00 1 comments links to this post

 
How to get a DB and send it....

You can backup the DB from the server and then restore it on your local machine with installed MS SQL Server 2000. Then you can change or delete any pruned info from the DB on your local machine. Then you can backup the modified DB again and send that backup file (*.bak file). You can send this file with Gmail. There is about 3Gb available space.

How to backup...
1. Open Enterprise Manager
2. Select source DB
3. Right click on the selected DB. Select All Tasks->Backup Database... from the popup menu.
4. Select File and filegroup and add destination file name in the Backup dialog.
5. Press OK

How to restore...
1. Open Enterprise Manager
2. Select Databases node
3. Right click on this node. Select All Tasks->Restore Database... from the popup menu.
4. Type database name in the Restore as database textbox
5. Select Restore From Device
6. Press Select Device
7. Add backup file name
8. Press OK
9. In the Options tab check file pathes where your database will be restored.
10. Press OK
11. Select File and filegroup and add destination file name in the Backup dialog.
12. Press OK

Labels: sql

- posted by programmabilities @ 14:07 0 comments links to this post

 
Excel: compare two Worksheets

Excel Macro
http://exceltip.com/st/Compare_two_worksheets_using_VBA_in_Microsoft_Excel/477.html
Compares the 2 Worksheets and pops up a workbook with the differences:
Steps: >Tools > Macro >Macros >Run

Excel Function
Function for comparing data: http://office.microsoft.com/en-us/excel/HP100625641033.aspx?pid=CH100645341033
Here's the Excel formula: =Exact(ColumnRow,ColumnRow)
An example: =EXACT(G5,P5) [First put both worksheets on the same worksheet, side by side.]
If the result is True data matches; if False data does not match

Also, I was told there is a way to recieve as output each row that was different using a "Filter".

Labels: sql

- posted by programmabilities @ 16:59 0 comments links to this post

 
SQL compare two tables
Difference in data stored in both tables (compare the difference between two tables)? You can use SQL to see rows which don't have a match in one table or the other:

Select col_a,col_b from table1 
where not exists 
    (select * from table2 
     where table1.col_a = table2.col_a and 
           table1.col_b = table2.col_b)

Repeat for table2.

further notes: --http://windowsitpro.com/Articles/ArticleID/14368/14368.html?Ad=1
--http://webmasterworld.com/databases_sql_mysql/3241540.htm

Labels: sql

- posted by programmabilities @ 17:37 0 comments links to this post

 
Debug sproc in VS

In VS at the Sever Explorer pane I right-click on my data connection that I added and select Modify Connection. Then in the Modify Connection dialog I clicked Advanced... and then in the Advanced Properties dialog I changed Pooling from True to False. Now it works. Also, in the configuration file, the Enable SQL Server debugging must be checked. Also, I have to add cmd.CommandTimeout=1800 to the VB's sproc area else it will time-out while clicking thru the sproc in the debugger.

But, when not debugging, in order to get the app to run to completion without crashing with the error Error while executing 'a batch cmd' at line 0 , I have to uncheck Enable SQL Server debugging and set Pooling back to True. --Because not pooling the connections hurts performance.

Labels: sql

- posted by programmabilities @ 09:30 0 comments links to this post

 
To export DTS package, do the following:

1. Open MS SQL Server Enterprise Manager
2. Go to Local Packages
3. Open some DTS Package
4. In the DTS Package window from menu select item Package->Save As...
5. In the Save DTS Package dialog
   • in the Location field select Structured Storage
   File
   • in the File Name field set destination path of saved file
6. Press OK button

Labels: sql

- posted by programmabilities @ 07:56 0 comments links to this post

 
Server 2000: Query Analyzer Tips & Tricks
You probably know that SQL Server stores metadata about all of the objects in a database. The system tables contain a wealth of information about column names, data types, identity seeds, and so on. But did you know that you can get that information with a single keystroke via Query Analyzer? Highlight the object name in any SQL statement and press Alt+F1. Figure 1 shows the results for a SQL Server table. If you don't have anything highlighted, Alt+F1 will give you information about the database itself. For an equally neat trick, highlight a SQL keyword and press Shift+F1; you'll go straight to the Books Online page that describes that keyword.

Labels: sql

- posted by programmabilities @ 12:33 0 comments links to this post

 
SQL: Bulk import

Bulk import (save file.txt as csv): LOAD DATA INFILE 'file.txt' INTO TABLE 'tblFoo' (col1, col2, col3)

or
BULK LOAD IMPORT ('c:\file.txt') INTO TABLE 'tblFoo' (col1, col2, col3)

Labels: sql

- posted by programmabilities @ 21:53 0 comments links to this post

 
SQL Server 2000: make table backup with DTS

• Right-click the table
  • choose Export Data
• Choose a destination
  • Server: "nwdsql" and Database: NELausData
• copy table(s)
• UserXRef"_bkup" (Destination)

Done, then refresh explorer to view.

Labels: sql

- posted by programmabilities @ 18:13 0 comments links to this post

 
Test SQL Injection Attack on ASP
Username: foo
Password: abc' OR 'x' = 'x

Labels: asp, programmabilities, sql

- posted by programmabilities @ 18:25 0 comments links to this post