Posts

Creating a Link Server to MYSQL from MSSQL

Image
There might be situations where you need to apply CRUD  operations (
Create,Read,Update,Delete) on data residing in a MYSQL server database from SQL server. In order to do this you will need to create a link service from SQL server to MYSQL. The Steps are outlined below:

Steps:
1. Install the MYSQL ODBC driver on the server with the MSSQL server installation.
2. Enter details for the MYSQL ODBC settings.
3. Create a link service to MYSQL using SSMS ( SQL Server Management Studio)
4. You will need an user account on the mysql server which can perform CRUD operations
 along with enable remote connections to the MYSQL installation. (See Troubleshooting steps below)
4. Test the connection.


Install MYSQL ODBC Driver Download  (http://dev.mysql.com/downloads/connector/odbc/) and install the latest MYSQL ODBC driver from the MYSQL server site. Select the version which corresponds to your installation of MSSQL usually the 64bit version. Note This needs to be done on the server with the MSSQL installed…

How to pass a data table into a stored procedure as a Table Value Parameter (TVP) in SQL server 2008 and above.

In some instances there might be a need to manipulate an entire data set rather than passing individual values as variables into a database. To do this I explain how to start by creating the proc with the parameters then obtaining the inserted identity values from the insert statement to update a related table. Note the special table inserted which is an SQL table which stores the values of an insert just before this hits your table. Refer MSDN on inserted and deleted Tables 1.To start with define the table value parameter under your database ->programmability->Types-> User-Defined Table Types 2.Right click on the User Defined Table Types Create a data table definition of the set needed to be passed. Clicking on CTRL-Shift-M would bring up the Dialog to create them visually. The script should look like the following -- ================================ -- Create User-defined Table Type -- ================================ USEAdventureWorks GO
-- Create the data type CREATETYPEdbo.MY_TV…

Growing Log file issues - How to shrink a SQL server Log and data file.

Image
Sometimes it is necessary to do this via a script as the UI does not work for reasons such as the DB is being in used. This method sets the recover mode and then shrinks the log and data file.

Before doing this backup your databases using Management studio Open up a query window associated with the database with the large transaction log. (Right-click on the DB and choose new query.)

Get the logical name of the transaction log file. (Right-click on the DB, select Properties, then in the Files screen, grab the Logical Name… probably ends in something like _Log.)



Execute the following, substituting with the appropriate logical name of the database log file, no quotes needed:

Use [Mydatabase]

ALTER DATABASE [mydatabase] SET RECOVERY SIMPLE
DBCC SHRINKFILE(<log_file_name_Log>)
ALTER DATABASE [mydatabase] SET RECOVERY FULL



The following screen shows the log file before the shrink. Log file after shrink Points of interest Transaction logs have to be backed up in order for them to ‘truncate…

Troubleshooting Database Mail: General steps

Image
SQL Server 2008 R2 Other Versions SQL Server "Denali"SQL Server 2008SQL Server 2005
Troubleshooting Database Mail involves checking the following general areas of the Database Mail system. These procedures are presented in a logical order, but can be evaluated in any order. To determine if Database Mail is enabledIn SQL Server Management Studio, connect to an instance of SQL Server by using a query editor window, and then execute the following code:
Copy sp_configure 'show advanced', 1; GO RECONFIGURE; GO sp_configure; GO In the results pane, confirm that the run_value for Database Mail XPs is set to 1.
If the run_value is not 1, Database Mail is not enabled. Database Mail is not automatically enabled to reduce the number of features available for attack by a malicious user. For more information, see Understanding Surface Area Configuration.If you decide that it is appropriate to enable Database Mail, execute the following code:
Copy sp_configure 'Database Mail XPs&…

Method of grouping strings with XML

The following are some fine articles I found on  Brads blog page.
http://bradsruminations.blogspot.com.au/2009/10/making-list-and-checking-it-twice.html

In my previous blog entry, I described in detail how you could use the FOR XML clause to create a column with a comma-separated list of entries acquired from rows in a table somewhere.

In this article, we will do the opposite… We will take a comma-separated list and “shred” the individual items into rows.

Let’s start by creating and populating a test table:

createtable #CSVLists ( ID int,Descriptionvarchar(30),CSVList varchar(200) )insert #CSVLists select 1,'Stooges','Moe,Larry,Curly'union allselect 2,'Castaways','Gilligan,Skipper,Thurston,Lovey,'+'Ginger,Professor,MaryAnne'union allselect 3,'Monkees','Davy,Micky,Peter,Mike' (Okay, I know you Gilligan’s Island fanatics are chomping at the bit to tell me that the Skipper’s name was Jonas and the Professor’s name was Roy…

Alternative method of querying the calendar using the CTE method

Image
I have also developed a solution of mine, but using simple t-sql CTE instead of t-sql PIVOT statements. Both methods are actually ending with the same result set.
Here is the sample t-sql code for the t-sql calendar listing.
WITH CTE AS
(
SELECT
wd = DATEPART(WEEK, [date]),
[date],
Sunday = CASE WHEN [days] = 'Sunday' THEN [date] ELSE NULL END,
Monday = CASE WHEN [days] = 'Monday' THEN [date] ELSE NULL END,
Tuesday = CASE WHEN [days] = 'Tuesday' THEN [date] ELSE NULL END,
Wednesday = CASE WHEN [days] = 'Wednesday' THEN [date] ELSE NULL END,
Thursday = CASE WHEN [days] = 'Thursday' THEN [date] ELSE NULL END,
Friday = CASE WHEN [days] = 'Friday' THEN [date] ELSE NULL END,
Saturday = CASE WHEN [days] = 'Saturday' THEN [date] ELSE NULL END
FROM dbo.getFullmonth ('20080229')
)
SELECT
MAX(Sunday) Sunday,
MAX(Monday) Monday,
MAX(Tuesday) Tuesday,
MAX(Wednesday) Wednesday,
MAX(Thursday) Thursday,
MAX…

How to Query SQL server for T-SQL Calendar using GetFullMonth using Pivot

Image
After listing sql calendar date values in a month as rows, we need to display them now in a format similar to frequently used in printed calendars.
The day names are listed as column names and dates are shown under the related columns.
The first solution is supplied by Plamen Ratchev from SQLStudio.com using the t-sql PIVOT. Since I liked the t-sql code he coded much I want to share it with you here.
SELECT
[Sunday],
[Monday],
[Tuesday],
[Wednesday],
[Thursday],
[Friday],
[Saturday]
FROM
(
SELECT
DATEPART(WEEK, [date]) AS week_number,
[date],
[days]
FROM dbo.getFullmonth('20080229')
) AS Dates
PIVOT
(
MIN([date])
FOR
[days] IN (
[Sunday],
[Monday],
[Tuesday],
[Wednesday],
[Thursday],
[Friday],
[Saturday]
)
) AS Pivots And the output of this sql pivot statement will return the following result set as output which is displaying sql dates in a calendar format.