Tuesday, 17 May 2016

Creating a Link Server to MYSQL from MSSQL

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 instance.

Once installed open Windows->Control Panel\All Control Panel Items\Administrative Tools\
Look for the ODBC  Datasource and double click. Verify that the driver is installed.


Click on the system tab and click Add

Select MYSQL ODBC  x.x Driver


Enter the details of your MYSQL installation. Pay attention to the name given to the data source name as this would be the name used under link services. Enter the IP Address of the MYSQL server, Username and password. Test the connection. If all goes well the allowed schemas would appear in the list of databases as shown below. Tick the boxes for multiple queries. Leave the rest of the tabs default.Could experiment later based on additional needs.

Troubleshooting Steps
If the above did not work. Verify that you have a telnet client installed on your computer.(Google this one  if needed as this is one of the setting under windows programs). Please verify that remote connection is enabled on the mysql server. To do this open a command prompt using cmd. (Open with Administrator rights). enter

c:\>telnet  x.x.x.x 3308

where the x.x.x.x is the IP address and 3308 is the default port MySQL listens too for requests. Also check any firewall settings.

You are now done with the ODBC setup.


Create a Link Service on your MSSQL

To do this open my SSMSO with sa privileges and click on Server Objects - > Linked Servers
Right click to create a new Link service.


Enter the following information. Note the data source name is the ODBC name given during driver setup. Verify the provider matches as below:

Select the security tab and enter the MYSQL user name and password.

 Server option as below


You are done.  If the setup was successful, refresh the screen F5 then expand the MYSQL object which should display the database and table names as below:


Writing a query to access the MYSQL Data. Note some fields are not compatible with MSSQL and will need a cast to convert this to a varchar type. I will explain this later in another post. For now, open a New Query window and enter the following select statement.
select * from openquery(MYSQL,'select * from schoolboxdev.chat')

There should be a result similar to the following



Your Link server is up and running. See My next post on doing insert, update and delete statements.


Thursday, 27 November 2014

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
-- ================================
USE AdventureWorks
GO

-- Create the data type
CREATE TYPE dbo.MY_TVP AS TABLE
(
       c1 int NOT NULL,
       c2 char(10) NULL,
       c3 datetime NULL,
    PRIMARY KEY (c1)
)
GO

--the table we want the values inserted into note the identity column which we need after the insert is done.
CREATE TABLE [dbo].[tbltest](
       [ID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
       [c1] [int] NULL,
       [c2] [char](10)  NULL,
       [c3] [datetime]  NULL
      

Once the above is done define your proc and utilize the above TVP as a type as below:

-- =============================================
-- Define a proc with the TVP value
-- =============================================
create PROCEDURE [dbo].[spSave]
       -- Add the parameters for the stored procedure here
       @Mytable MY_TVP Readonly

AS
BEGIN
-- Use this table to hold the inserted Identity values of table 1

Declare @IDList Table(ID int);

       -- this is a general insert, just work with the table variable as per normal table.
             Insert into tbltest (c1,C2,C3)
Output INSERTED.ID Into @IDList(ID)
                    select  * from
                    @Mytable
                     
             insert into relatedTable
                    select ID,@SomeID from @IDList  
      

END

Wednesday, 10 September 2014

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


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’. If they are not backed up regularly, they will eventually fill the disk
  • a full backup alone will not do the trick – that does not touch the transaction log
  • transaction logs must be backed up via ‘BACKUP LOG’
  • if the dba does not require transaction log backups, it is advisable to move to Simple Recovery
A little more info here:



Tuesday, 13 August 2013

Joomla: “There was an error uploading this file

This may be caused by Joomla hitting the maximum upload file size settings in php.ini, which is the configuration file for PHP settings. By default this can be as low as 2MB and easy to hit. I encountered this error while trying to install the VirtueMart component which is over 2MB in size.
The best way to determine your php.ini settings is through the Joomla Administration interface through the Help > System Info menu and then by clicking on the PHP Information link.
Joomla System Information - PHP Information
Joomla System Information - PHP Information
Search the information display for the value upload_max_filesize. Note that there are two columns shown – the first column is the local value (from the local php.ini file) and the second column is the master value (from the master php.ini file). If the value is only 2M then you will probably have issues.
There are a few ways to increase this setting. The option you use will depend on what access you have to your hosting:
Option 1: Edit the upload_max_filesize parameter directly in master php.ini. Simply change the value to something more appropriate, for example from 2M to 32M. Remember that this will be a global change. If you are on shared web hosting, you probably don’t have access to do this anyway.
Option 2: Specify the parameter in your .htaccess file if you are using PHP loaded as an Apache module. Do this in your Joomla administrator folder. The parameter looks like this:
php_value upload_max_filesize 32M.
Don’t rename the delivered htaccess.txt as this contains other directives. If this doesn’t work or you get the following error: Invalid command 'php_value', perhaps misspelled or defined by a module not included in the server configuration then PHP is not loaded as an Apache module. Try the next option.
Option 3: Is to override settings in your master php.ini file with a local php.ini file in your Joomla administrator folder. To do this, copy the entire master php.ini. You can find the location of your master php.ini by looking at the Loaded COnfiguration File in the Joomla PHP Information page. Then edit the local php.ini file in your Joomla administrator folder and change the upload_max_filesize paramter accordingly. This is essentially the same as Option 1, but you will be using a local php.ini file which is specific to just your Joomla installation and doesn’t impact on anything else. Use the PHP Information page to confirm Joomla has loaded your local php.ini file and that the upload maximum file size parameter has been adjusted accordingly.

The last thing to check is the TEMP folder utilized for file uploads. On IIS7 this is typically the c:\windows\TEMP\

Ensure that the IUSER,IIS_USER or the account used by the web server has RW permissions to this folder.

Troubleshooting Database Mail: General steps

SQL Server 2008 R2

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 enabled

  1. In SQL Server Management Studio, connect to an instance of SQL Server by using a query editor window, and then execute the following code:
    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.
  2. If you decide that it is appropriate to enable Database Mail, execute the following code:
    sp_configure 'Database Mail XPs', 1; 
    GO
    RECONFIGURE;
    GO
    
  3. To restore the sp_configure procedure to its default state, which does not show advanced options, execute the following code:
    sp_configure 'show advanced', 0; 
    GO
    RECONFIGURE;
    GO
    

To determine if users are properly configured to send Database Mail

  1. To send Database Mail, users must be a member of the DatabaseMailUserRole. Members of the sysadmin fixed server role and msdb db_owner role are automatically members of the DatabaseMailUserRole role. To list all other members of the DatabaseMailUserRole execute the following statement:
    EXEC msdb.sys.sp_helprolemember 'DatabaseMailUserRole';
    
  2. To add users to the DatabaseMailUserRole role, use the following statement:
    sp_addrolemember @rolename = 'DatabaseMailUserRole'
       ,@membername = '';
    
  3. To send Database Mail, users must have access to at least one Database Mail profile. To list the users (principals) and the profiles to which they have access, execute the following statement.
    EXEC msdb.dbo.sysmail_help_principalprofile_sp;
    
  4. Use the Database Mail Configuration Wizard to create profiles and grant access to profiles to users.

To confirm that the Database Mail is started

  1. The Database Mail External Program is activated when there are e-mail messages to be processed. When there have been no messages to send for the specified time-out period, the program exits. To confirm the Database Mail activation is started, execute the following statement.
    EXEC msdb.dbo.sysmail_help_status_sp;
    
  2. If the Database Mail activation is not started, execute the following statement to start it:
    EXEC msdb.dbo.sysmail_start_sp;
    
  3. If the Database Mail external program is started, check the status of the mail queue with the following statement:
    EXEC msdb.dbo.sysmail_help_queue_sp @queue_type = 'mail';
    
    The mail queue should have the state of RECEIVES_OCCURRING. The status queue may vary from moment to moment. If the mail queue state is not RECEIVES_OCCURRING, try stopping the queue using sysmail_stop_sp and then starting the queue using sysmail_start_sp.
NoteNote
Use the length column in the result set of sysmail_help_queue_sp to determine the number of e-mails in the Mail queue.

To determine if problems with Database Mail affect all accounts in a profile or only some accounts

  1. If you have determined that some but not all profiles can send mail, then you may have problems with the Database Mail accounts used by the problem profiles. To determine which accounts are successful in sending mail, execute the following statement:
    SELECT sent_account_id, sent_date FROM msdb.dbo.sysmail_sentitems;
    
  2. If a profile which is not working does not use any of the accounts listed, then it is possible that all the accounts available to the profile are not working properly. To test individual accounts, use the Database Mail Configuration Wizard to create a new profile with a single account, and then use the Send Test E-Mail dialog box to send mail using the new account.
  3. To view the error messages returned by Database Mail, execute the following statement:
    SELECT * FROM msdb.dbo.sysmail_event_log;
    
NoteNote
Database Mail considers mail to be sent, when it is successfully delivered to a SMTP mail server. Subsequent errors, such as an invalid recipient e-mail address, can still prevent mail from being delivered, but will not be contained in the Database Mail log.

To configure Database Mail to retry mail delivery

  1. If you have determined that the Database Mail is failing because the SMTP server cannot be reliably reached, you may be able to increase your successful mail delivery rate by increasing the number of times Database Mail attempts to send each message. Start the Database Mail Configuration Wizard, and select the View or change system parameters option. Alternatively, you can associate more accounts to the profile so upon failover from the primary account, Database Mail will use the failover account to send e-mails.
  2. On the Configure System Parameters page, the default values of 5 times for the Account Retry Attempts and 60 seconds for the Account Retry Delay means that message delivery will fail if the SMTP server cannot be reached in 5 minutes. Increase these parameters to lengthen the amount of time before message deliver fails.
NoteNote
When large numbers of messages are being sent, large default values may increase reliability, but will substantially increase the use of resources as many messages are attempted to be delivered over and over again. Address the root problem by resolving the network or SMTP server problem that prevents Database Mail from contacting the SMTP server promptly.
You must be a member of the sysadmin fixed server role to troubleshoot all aspects of Database Mail. Users who are not members of the sysadmin fixed server role can only obtain information about the e-mails they attempt to send, not about e-mails sent by other users.

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:

create table #CSVLists (   ID          int  ,Description varchar(30)  ,CSVList     varchar(200)
)insert #CSVLists select 1,'Stooges'  ,'Moe,Larry,Curly'       union all select 2,'Castaways','Gilligan,Skipper,Thurston,Lovey,'                                     +'Ginger,Professor,MaryAnne'       union all select 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. I know that… Don’t bother e-mailing me about it… save a virtual tree).

There is a handy .nodes() method which you can apply to an XML datatype that will shred it into relational data. So our first step is to convert our CSVList column into a valid XML representation of the data. We do that this way:

select ID,Description      ,XmlListfrom #CSVListscross apply (select TagsList='<x>'+replace(CSVList,',','</x><x>')+'</x>') F1cross apply (select XmlList=cast(TagsList as xml)) F2/*
ID Description XmlList
-- ----------- -----------------------------------------------------------
 1 Stooges     <x>Moe</x><x>Larry</x><x>Curly</x>
 2 Castaways   <x>Gilligan</x><x>Skipper</x><x>Thurston</x><x>Lovey</x>...
 3 Monkees     <x>Davy</x><x>Micky</x><x>Peter</x><x>Mike</x>
*/ 
The first CROSS APPLY surrounded each item in the CSVList with the tags <x></x>. And the second CROSS APPLY converted that into an actual XML datatype.

Now that we have an XML column, we can apply the .nodes() method to it. We will pass 'x' to the .nodes() method so that it knows the tag (<x></x>) to shred. And let’s take a look at that result:

select ID,Description      ,XMLNodefrom #CSVListscross apply (select TagsList='<x>'+replace(CSVList,',','</x><x>')+'</x>') F1cross apply (select XmlList=cast(TagsList as xml)) F2cross apply XmlList.nodes('x') F3(XmlNode)/*
Msg 493, Level 16, State 1, Line 1
The column 'XmlNode' that was returned from the nodes() method cannot be used 
directly. It can only be used with one of the four xml data type methods, 
exist(), nodes(), query(), and value(), or in IS NULL and IS NOT NULL checks.
*/
Oops… This new XmlNode object we created is not something that we can actually look at or use directly. So, as the error message indicated, we will use the .value() method against that XmlNode object to pull out an actual VARCHAR(20) value:

select ID,Description      ,ListItemfrom #CSVListscross apply (select TagsList='<x>'+replace(CSVList,',','</x><x>')+'</x>') F1cross apply (select XmlList=cast(TagsList as xml)) F2cross apply XmlList.nodes('x') F3(XmlNode)
cross apply (select ListItem=XmlNode.value('.','varchar(20)')) F4/*
ID Description ListItem
-- ----------- ---------
 1 Stooges     Moe
 1 Stooges     Larry
 1 Stooges     Curly
 2 Castaways   Gilligan
 2 Castaways   Skipper
 2 Castaways   Thurston
 2 Castaways   Lovey
 2 Castaways   Ginger
 2 Castaways   Professor
 2 Castaways   MaryAnne
 3 Monkees     Davy
 3 Monkees     Micky
 3 Monkees     Peter
 3 Monkees     Mike
*/
Wow, it works!

But, as is often the case, you know there’s more. Just like I did in my previous blog entry, I’m going to do something to purposely trip things up. Let’s add a list of TV Networks:

insert #CSVListsselect 4,'Networks','ABC,NBC,CBS,FX,TNT,A&E'
And let’s try the same query:

select ID,Description      ,ListItemfrom #CSVListscross apply (select TagsList='<x>'+replace(CSVList,',','</x><x>')+'</x>') F1cross apply (select XmlList=cast(TagsList as xml)) F2cross apply XmlList.nodes('x') F3(XmlNode)
cross apply (select ListItem=XmlNode.value('.','varchar(20)')) F4/*
ID Description ListItem
-- ----------- ---------
 1 Stooges     Moe
 1 Stooges     Larry
 1 Stooges     Curly
 2 Castaways   Gilligan
 2 Castaways   Skipper
 2 Castaways   Thurston
 2 Castaways   Lovey
 2 Castaways   Ginger
 2 Castaways   Professor
 2 Castaways   MaryAnne
 3 Monkees     Davy
 3 Monkees     Micky
 3 Monkees     Peter
 3 Monkees     Mike
Msg 9411, Level 16, State 1, Line 1
XML parsing: line 1, character 56, semicolon expected
*/
Ouch! Once again, we’ve run into the problem with the way that XML encodes and decodes special characters like ampersand (&).

We cannot CAST something as XML if it has an ampersand as part of the data, because XML will try to interpret it as an entity that must end in a semicolon:

select Info=cast('<x>A&E</x>' as xml)/*
Msg 9411, Level 16, State 1, Line 1
XML parsing: line 1, character 7, semicolon expected
*/
In order for it to be accepted, we have to encode that ampersand as follows, so that XML will understand it to represent an actual ampersand character:

select Info=cast('<x>A&amp;E</x>' as xml)/*
Info
--------------
<x>A&amp;E</x>
*/
And, as we learned in my previous blog entry, we can then take the .value() of that to un-encode it:

select Info=cast('<x>A&amp;E</x>' as xml).value('.','varchar(10)')/*
Info
----
A&E
*/
So in our shredding query, we have to FIRST encode any special characters in our CSVList column, and THEN we can successfully CAST it as an XML column.

One might be tempted to use multiple REPLACE() functions to convert all ampersands to &amp; and less-thans to &lt; and greater-thans to &gt;, but who knows what others characters are out there that will create problems? We already have a tool at our disposal that will encode the characters for us… the FOR XML clause.

select ID,Description      ,XMLEncodedfrom #CSVListscross apply (select XMLEncoded=(select [*]=CSVList for xml path(''))) F0/*
ID Description XMLEncoded
-- ----------- ---------------------------------------------------------
 1 Stooges     Moe,Larry,Curly
 2 Castaways   Gilligan,Skipper,Thurston,Lovey,Ginger,Professor,MaryAnne
 3 Monkees     Davy,Micky,Peter,Mike
 4 Networks    ABC,NBC,CBS,FX,TNT,A&amp;E
*/
We learned in my previous blog entry that we can eliminate tags being created by explicitly specifying a column a name of [*]. If we hadn’t done that, then we would end up with our lists surrounded by <CSVList></CSVList> tags. And we eliminated yet another tag surrounding our data by passing the empty string to FOR XML PATH. So our lists are really the same as they were originally, except for the special characters being automatically encoded for us.

Now we can incorporate that CROSS APPLY into our query:

select ID,Description      ,ListItemfrom #CSVListscross apply (select XMLEncoded=(select [*]=CSVList for xml path(''))) F0cross apply (select TagsList='<x>'+replace(XMLEncoded,',','</x><x>')+'</x>') F1cross apply (select XmlList=cast(TagsList as xml)) F2cross apply XmlList.nodes('x') F3(XmlNode)
cross apply (select ListItem=XmlNode.value('.','varchar(20)')) F4/*
ID Description ListItem
-- ----------- ---------
 1 Stooges     Moe
 1 Stooges     Larry
 1 Stooges     Curly
 2 Castaways   Gilligan
 2 Castaways   Skipper
 2 Castaways   Thurston
 2 Castaways   Lovey
 2 Castaways   Ginger
 2 Castaways   Professor
 2 Castaways   MaryAnne
 3 Monkees     Davy
 3 Monkees     Micky
 3 Monkees     Peter
 3 Monkees     Mike
 4 Networks    ABC
 4 Networks    NBC
 4 Networks    CBS
 4 Networks    FX
 4 Networks    TNT
 4 Networks    A&E
*/
Ahhh… Success!

By the way, you may have noticed that the making and un-making (shredding) of these comma-delimited lists is like PIVOTing and UNPIVOTing. PIVOT converts rows to columns and UNPIVOT converts columns to rows. We have done something similar, except in making a list, we converted rows into a single column, and in shredding a list, we converted a single column into rows.

I’ll leave you with one final demonstration. Our #CSVLists table that we created had some comma-separated lists. What if we want to sort the items in the lists?

We would first shred the items into rows, and then, using the FOR XML logic we put together in my previous blog entry, we will re-assemble the lists and use an ORDER BY to sort them:

;with ShreddedLists as(  select ID,ListItemfrom #CSVListscross apply (select XMLEncoded=(select [*]=CSVList for xml path(''))) F0cross apply (select TagsList='<x>'+replace(XMLEncoded,',','</x><x>')+'</x>') F1cross apply (select XmlList=cast(TagsList as xml)) F2cross apply XmlList.nodes('x') F3(XmlNode)  cross apply (select ListItem=XmlNode.value('.','varchar(20)')) F4)select ID,Description      ,SortedCSVListfrom #CSVListscross apply (select CSVStringXML=(select ','+ListItemfrom ShreddedListswhere ID=#CSVLists.IDorder by ListItemfor xml path(''),type)) F1cross apply (select CSVString=CSVStringXML.value('.','varchar(max)')) F2cross apply (select SortedCSVList=stuff(CSVString,1,1,'')) F3/*
ID Description SortedCSVList
-- ----------- ---------------------------------------------------------
 1 Stooges     Curly,Larry,Moe
 2 Castaways   Gilligan,Ginger,Lovey,MaryAnne,Professor,Skipper,Thurston
 3 Monkees     Davy,Micky,Mike,Peter
 4 Networks    A&E,ABC,CBS,FX,NBC,TNT

Alternative method of querying the calendar using the CTE method

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(Friday) Friday,
MAX(Saturday) Saturday
FROM CTE
GROUP BY wd
Here is the CTE output for generating sql calendar dates on a MS SQL Server 2005 or later (SQL Server 2008).
t-sql cte command to format dates as calendar