/* ------------------------ My Meta Content Here SEO ------------------------ */

Pages

Main Menu

Tuesday, December 30, 2014

Friday, December 19, 2014

Example Uses of the PARSENAME Function in SQL Server

Example Uses of the PARSENAME Function

The PARSENAME function returns the specified part of an object name.  The parts of an object that can be retrieved are the object name, owner name, database name and server name.  ThePARSENAME function does not indicate whether an object by the specified name exists.  PARSENAME just returns the specified part of the specified object name.
PARSENAME ( 'object_name' , object_part )
The  object_name parameter is the name of the object for which to retrieve the specified object part.  The object_part parameter, which is of int data type, is the object part to return and can have a value of 1 for the object name, 2 for the schema name, 3 for the database name and 4 for the server name.
DECLARE @ObjectName SYSNAME
SET @ObjectName = 'MyServer.SQLServerHelper.dbo.Customer'


SELECT PARSENAME(@ObjectName, 1) AS [ObjectName],
       PARSENAME(@ObjectName, 2) AS [SchemaName],
       PARSENAME(@ObjectName, 3) AS [DatabaseName],
       PARSENAME(@ObjectName, 4) AS [ServerName]


ObjectName  SchemaName  DatabaseName     ServerName
----------- ----------- ---------------- -----------
Customer    dbo         SQLServerHelper  MyServer
Sort IP Addresses with PARSENAME
One use of the PARSENAME is with sorting IP addresses.  Similar to a fully qualified object name, an IP address is made of 4 parts separated by a period.  Here’s an example on how to sort IP addresses using the PARSENAME function:
DECLARE @IPAddresses TABLE ( [IPAddress] VARCHAR(20))

INSERT INTO @IPAddresses VALUES ('10.0.0.1')
INSERT INTO @IPAddresses VALUES ('255.255.255.255')
INSERT INTO @IPAddresses VALUES ('192.123.545.12')
INSERT INTO @IPAddresses VALUES ('1.2.3.4')

SELECT * FROM @IPAddresses
ORDER BY CAST(PARSENAME([IPAddress], 4) AS INT),
         CAST(PARSENAME([IPAddress], 3) AS INT),
         CAST(PARSENAME([IPAddress], 2) AS INT),
         CAST(PARSENAME([IPAddress], 1) AS INT)

IPAddress
----------------
1.2.3.4
10.0.0.1
192.123.545.12
255.255.255.255
Split Full Name Into First Name and Last Name with PARSENAME
Another use of the PARSENAME function is to split a 2-part full name into first name and last name.
DECLARE @FullName VARCHAR(50)
SET @FullName = 'Donald Duck'
SELECT PARSENAME(REPLACE(@FullName, ' ', '.'), 2) AS [FirstName],
    PARSENAME(REPLACE(@FullName, ' ', '.'), 1) AS [LastName]
FirstName LastName
----------- ---------- 
Donald Duck
http://www.sql-server-helper.com/
Read More »

Thursday, December 18, 2014

Dynamically create mutiple users login in sql server

Dynamically create multiple users login in sql server by passing dynamic value



DECLARE @site_value INT;
DECLARE @login varchar(20);
DECLARE @password varchar(20);
DECLARE @Sql varchar(max);
SET @site_value = 0;

WHILE @site_value <= 3
BEGIN  
   SET @site_value = @site_value + 1;
   SET @login = 'user' + cast(@site_value as varchar(10));
   SET @password = 'password@' + cast(@site_value as varchar(10));
   SET @Sql = 'CREATE LOGIN '+@login+' WITH PASSWORD = '+''''+@password+''''+'';
   PRINT(@Sql)
   exec (@Sql)
END;

PRINT 'Done WHILE LOOP';
GO

Result

CREATE LOGIN user1 WITH PASSWORD = 'password@1'
CREATE LOGIN user2 WITH PASSWORD = 'password@2'
CREATE LOGIN user3 WITH PASSWORD = 'password@3'
CREATE LOGIN user4 WITH PASSWORD = 'password@4'
Done WHILE LOOP


Read More »

Wednesday, December 17, 2014

How to recover SA password on Microsoft SQL Server 2008 R2

How to recover SA password on Microsoft SQL Server 2008 R2


When you are using MS SQL Server in mixed mode, it is very important that you know your SA password.

There can be different reasons you lost the password
Person who installed the SQL Server knows the password but has left the building.
You did not write down the password in your password file
Password file is lost

Steps to recover the SA password
Start SQL Server Configuration Manager


Stop the SQL services


Edit the properties of the SQL Service


Change the startup parameters of the SQL service by adding a –m; in front of the existing parameters


Start the SQL services. These are now running in Single User Mode.
Start CMD on tthe SQL server
Start the SQLCMD command. Now you will see following screen


Now we create a new user. Enter following commands
CREATE LOGIN recovery WITH PASSWORD = ‘TopSecret 1′ (Remember SQL server has default strong password policy
Go
Now this user is created
Now we grant the user a SYSADMIN roles using the same SQLCMD window.
sp_addsrvrolemember ‘recovery’, ‘sysadmin’
go
Stop the SQL service again
Change the SQL service properties back to the default settings


Start the SQL service again and use the new created login (recovery in my example)
Go via the security panel to the properties and change the password of the SA account.


Now write down the new SA password.
Read More »

Wednesday, December 3, 2014

CKEditor Toolbar in asp.net

CKEditor Toolbar


Toolbar Customization


CKEDITOR.editorConfig = function( config )
{
// Define changes to default configuration here. For example:
// config.language = 'fr';
        // config.uiColor = '#AADC6E';


    config.toolbar_Full =
    [
   { name: 'document', items: ['Source', '-', 'Save', 'NewPage', 'DocProps', 'Preview', 'Print', '-', 'Templates'] },
   { name: 'clipboard', items: ['Cut', 'Copy', 'Paste', 'PasteText', 'PasteFromWord', '-', 'Undo', 'Redo'] },
   { name: 'editing', items: ['Find', 'Replace', '-', 'SelectAll', '-', 'SpellChecker', 'Scayt'] },
   { name: 'forms', items: ['Form', 'Checkbox', 'Radio', 'TextField', 'Textarea', 'Select', 'Button', 'ImageButton',
            'HiddenField']
   },
   '/',
   { name: 'basicstyles', items: ['Bold', 'Italic', 'Underline', 'Strike', 'Subscript', 'Superscript', '-', 'RemoveFormat'] },
   { name: 'paragraph', items: ['NumberedList', 'BulletedList', '-', 'Outdent', 'Indent', '-', 'Blockquote', 'CreateDiv',
   '-', 'JustifyLeft', 'JustifyCenter', 'JustifyRight', 'JustifyBlock', '-', 'BidiLtr', 'BidiRtl']
   },
   { name: 'links', items: ['Link', 'Unlink', 'Anchor'] },
   { name: 'insert', items: ['Image', 'Flash', 'Table', 'HorizontalRule', 'Smiley', 'SpecialChar', 'PageBreak', 'Iframe'] },
   '/',
   { name: 'styles', items: ['Styles', 'Format', 'Font', 'FontSize'] },
   { name: 'colors', items: ['TextColor', 'BGColor'] },
   { name: 'tools', items: ['Maximize', 'ShowBlocks', '-', 'About'] }
    ];

//    config.toolbar_Basic =
//    [
//    ['Bold', 'Italic', '-', 'NumberedList', 'BulletedList', '-', 'Link', 'Unlink', '-', 'About']
//    ];

};

Source:  http://docs.cksource.com/CKEditor_3.x/Developers_Guide/Toolbar
http://docs.cksource.com/CKEditor_3.x/Developers_Guide

Another Way:

 ID="CKEditor1" BasePath="/ckeditor/" runat="server" Width="100%" Toolbar="Basic"
ToolbarBasic="|Bold|Italic|Underline|Strike|-|NumberedList|BulletedList|Outdent|Indent|-|JustifyLeft|JustifyCenter|JustifyRight|JustifyBlock|
|Link|Unlink|-|TextColor|-|Undo|Redo|Cut|Copy|Paste|PasteText|PasteFromWord|
/
|Find|Replace|SelectAll|-|Image|Table|HorizontalRule|SpecialChar|-|Format|" >
Read More »

Tuesday, December 2, 2014

All About Client Script

Page redirection script
///
    /// This method is used to redirect into another page
    ///
    ///
    private void RedirectionScript(string pageUrl)
    {
        string redirectionScript = "";
        Page.ClientScript.RegisterStartupScript(this.GetType(), "Startup", redirectionScript);
    }

________________________________________________________________________________


Read More »

Wednesday, November 26, 2014


Read More »

Linked-Server Settings for SQL Server 2008 R2 to Excel 2010 Spreadsheet

Linked-Server Settings for SQL Server 2008 R2 to Excel 2010 Spreadsheet…

STEPS TO CREATING A LINKED-SERVER FROM SQL 2008R2 (64 bit) TO AN EXCEL 2010 SPREADSHEET:

REMOTE (whichever RDP client you prefer) directly into your SQL Server 2008R2 server with an ADMINISTRATOR-level account (you’re downloading and installing stuff);
LAUNCH SQL Server 2008R2 Management Studio
CONNECT to your local SQL Server instance (often connects by default)
NAVIGATE to SQL Server (instance) > Server Objects > Linked Servers > Providers


CHECK if provider “Microsoft.ACE.OLEDB.12.0″ provider is already installed
IF (NOT Installed)
·         
·         DOWNLOAD the AccessDatabaseEngine_64.exe Provider Installer to your SQL Server’s hard drive, say C:\Application Installers  is a location I create for such activities – makes it easy to see what “extra” stuff I’ve installed;
·         LAUNCH the AccessDatabaseEngine_64.exe installer
    => the Microsoft.ACE.OLEDB.12.0 provider will now be added to SQL Server’s list of Data Access Providers
·         REFRESH the Providers node in SQL Management Studio (cool how you did NOT need to close/restart the studio!)
   => you will now SEE the Microsoft.ACE.OLEDB.12.0 provider listed with all the other built-in providers
RIGHT-MOUSE-CLICK on Linked Servers > New Linked Server
   => New Linked Server dialogue box displays
TYPE or SELECT the following information:
·         Linked Server (name) = “EXPORT”     (ANYTHING is acceptable, but shorter names are preferred)
·         Server Type = Other Data Source (select this radio button)
·         Provider = Microsoft Office 12 Access Database Engine OLE DB Provider
·         Product Name = Excel
·         Data Source = H:\Data\MySpreadsheet.xlsm  
   note: you could also use a UNC convention like \\BogusServer123\K$\Shared\MySpreadsheet.xlsm  convention as well
·         Provider String = Excel 12.0 Macro
·         Location = blank (don’t need it)
·         Catalog = blank (don’t need it)
CLICK OK

=> SQL Managment Studio uses the info to create & test a new connecton string, and completes creating the new Linked Server
NAVIGATE into the new Linked Server
NAVIGATE down to a table
RIGHT-MOUSE-CLICK > Script Table > Select to
   => a canned SELECT query is generated
EXECUTE query

   => you’ll see your table contents returned – CONFIRMATION that you now have a working Linked Server! 
That’s it!
Cheers,
SELECT DATA FROM EXCEL IN TO SQL SERVER DATABASE

INSERT DATA INTO EXCE SHEET FROM SQL SERVER

Source:http://vogtland.ws/markedwardvogt/?p=991
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO
EXEC sp_configure 'ad hoc distributed queries', 1
RECONFIGURE
GO
--sp_helptext sp_configure
-------------Success connection for 2007-2010---------------   
DROP TABLE TEMP
select * into temp
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0 Xml;HDR=YES;Database=C:\data\data.xlsx',
    'SELECT * FROM [Sheet1$]');
    select * FROM temp     
------------------------------------------------------------   
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0 Xml;HDR=YES;Database=C:\data\data.xlsx',
'SELECT * FROM [Sheet1$]')

---------------------- For 2003-2007 -----------------------  
------ SELECT DATA FROM AN EXCEL SHEET IN SQL SERVER -------
SELECT * --INTO #productlist
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
    'Excel 8.0;HDR=YES;Database=C:\data\Book1.xls',
    'select * from [Sheet1$]');  
   
------ INSERT DATA IN TO EXCEL SHEET FROM SQL SERVER -------
INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',
    'Excel 8.0;HDR=YES;Database=C:\testing.xls;',
'SELECT * FROM [Sheet1$]')SELECT GETDATE()
The Basics of Excel Data Import to SQL Server

To import data from Microsoft Excel to SQL Server OPENROWSET and OPENDATASOURCE functions with OLE DB data source can be used.
The basic format for the Microsoft.Jet.OLEDB.4.0 provider is:
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
  'Excel 8.0;Database=C:\excel-sql-server.xls', [Sheet1$])
SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
  'Data Source=C:\excel-sql-server.xls;Extended Properties=Excel 8.0')...[Sheet1$]
The basic format for the Microsoft.ACE.OLEDB.12.0 provider is:
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
  'Excel 12.0;Database=C:\excel-sql-server.xlsx', [Sheet1$])
SELECT * FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
  'Data Source=C:\excel-sql-server.xlsx;Extended Properties=Excel 12.0')...[Sheet1$]
The Microsoft.Jet.OLEDB.4.0 provider is used with 32-bit SQL Server for Excel 2003 files.
The Microsoft.ACE.OLEDB.12.0 provider is used with 64-bit SQL Server for any Excel files or 32-bit SQL Server for Excel 2007 files.
Configuration Steps for Excel Data Import to SQL Server
#
Step
SQL Server x86
for Excel 2003
files *.xls
SQL Server x86
for Excel 2007
files *.xlsx, etc.
SQL Server x64
for any Excel
version files
1
Install Microsoft.ACE.OLEDB.12.0 driver
not needed
x86
x64
2
Configure Ad Hoc Distributed Queries
yes
yes
yes
3
Grant rigths to TEMP directory
yes
yes
not needed
4
Configure ACE OLE DB properties
not needed
yes
yes

Install Microsoft.ACE.OLEDB.12.0 driver
To import Excel 2007/2010 files to SQL Server Microsoft.ACE.OLEDB.12.0 driver should be installed.
To download the driver use the following link:
Don't worry about "Access" in the name.
Warning! x64 driver can not be installed if Microsoft Office 2007/2010 x86 is already installed!
So there is no way to import Excel data to SQL Server x64 using OPENROWSET/OPENDATASOURCE functions on a machine with Microsoft Office x86!
The SQL Server Error Message if Microsoft.ACE.OLEDB.12.0 is not installed
Msg 7403, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" has not been registered.
Configure Ad Hoc Distributed Queries
To configure Ad Hoc Distributed Queries use the following code:
sp_configure 'Show Advanced Options', 1;
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO
The SQL Server Error Message if Ad Hoc Distributed Queries component is turned off
Msg 15281, Level 16, State 1, Line 1
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component
'Ad Hoc Distributed Queries' because this component is turned off as part of
the security configuration for this server.
A system administrator can enable the use of 'Ad Hoc Distributed Queries'
by using sp_configure.
For more information about enabling 'Ad Hoc Distributed Queries',
see "Surface Area Configuration" in SQL Server Books Online.
Grant rigths to TEMP directory
This step is required only for 32-bit SQL Server with any OLE DB provider.
The main problem is that an OLE DB provider creates a temporary file during the query in the SQL Server temp directory using credentials of a user who run the query.
The default directory for SQL Server is a default directory for SQL Server service account.
If SQL Server is run under Network Service account the temp directory is like:
C:\Windows\ServiceProfiles\NetworkService\AppData\Local\Temp
If SQL Server is run under Local Service account the temp directory is like:
C:\Windows\ServiceProfiles\LocalService\AppData\Local\Temp
Microsoft recommends two ways for the solution:
1.      A change of SQL Server TEMP directory and a grant of full rights for all users to this directory.
2.      Grant of read/write rights to the current SQL Server TEMP directory.
Usually only few accounts are used for import operations. So we can just add rights for these accounts.
For example, icacls utility can be used for the rights setup:
icacls C:\Windows\ServiceProfiles\NetworkService\AppData\Local\Temp /grant vs:(R,W)
if SQL Server is started under Network Service and login "vs" is used to run the queries.
The SQL Server Error Message if a user have no rights for SQL Server TEMP directory
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
Configure ACE OLE DB properties
This step is required only if the Microsoft.ACE.OLEDB.12.0 provider is used.
Use the following T-SQL code:
EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
GO
EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
GO
The SQL Server Error Messages if OLE DB properties are not configured
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
How-To: Import Excel 2003 to SQL Server x86
Step 1. Configure Ad Hoc Distributed Queries
sp_configure 'Show Advanced Options', 1;
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO
Step 2. Grant rigths to TEMP directory
icacls C:\Windows\ServiceProfiles\\AppData\Local\Temp /grant :(R,W)
The most commonly used pathes:
C:\Windows\ServiceProfiles\NetworkService\AppData\Local\Temp
C:\Windows\ServiceProfiles\LocalService\AppData\Local\Temp
Use Microsoft.Jet.OLEDB.4.0 OPENROWSET format
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
  'Excel 8.0;Database=C:\excel-sql-server.xls', [Sheet1$])
Use Microsoft.Jet.OLEDB.4.0 OPENDATASOURCE format
SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
  'Data Source=C:\excel-sql-server.xls;Extended Properties=Excel 8.0')...[Sheet1$]

How-To: Import Excel 2007 to SQL Server x86
Step 1. Install 32-bit Microsoft.ACE.OLEDB.12.0 driver
Step 2. Configure Ad Hoc Distributed Queries
sp_configure 'Show Advanced Options', 1;
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO
Step 3. Grant rigths to TEMP directory
icacls C:\Windows\ServiceProfiles\\AppData\Local\Temp /grant :(R,W)
The most commonly used pathes:
C:\Windows\ServiceProfiles\NetworkService\AppData\Local\Temp
C:\Windows\ServiceProfiles\LocalService\AppData\Local\Temp
Step 4. Configure OLE DB properties
EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
GO
EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
GO
Use Microsoft.ACE.OLEDB.12.0 OPENROWSET format
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
  'Excel 12.0;Database=C:\excel-sql-server.xlsx', [Sheet1$])
Use Microsoft.ACE.OLEDB.12.0 OPENDATASOURCE format
SELECT * FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
  'Data Source=C:\excel-sql-server.xlsx;Extended Properties=Excel 12.0')...[Sheet1$]

How-To: Import Excel 2003/2007 to SQL Server x64
Step 1. Install 64-bit Microsoft.ACE.OLEDB.12.0 driver
Step 2. Configure Ad Hoc Distributed Queries
sp_configure 'Show Advanced Options', 1;
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO
Step 3. Configure OLE DB properties
EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
GO
EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
GO
Use Microsoft.ACE.OLEDB.12.0 OPENROWSET format
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
  'Excel 12.0;Database=C:\excel-sql-server.xlsx', [Sheet1$])
Use Microsoft.ACE.OLEDB.12.0 OPENDATASOURCE format

SELECT * FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
  'Data Source=C:\excel-sql-server.xlsx;Extended Properties=Excel 12.0')...[Sheet1$]
http://www.accelebrate.com/library/tutorials/ssis-2008
Read More »

My Blog List

  • काश - काश मुझे भी पीने की आदत होती,मैं कब का मुर्दा हो गया होता। छुटकारा मिलता आज के आतंकवाद से, किसी संतान भूमि में सो गया होता। मेरा एतबार कौन करेगा, मैंने मुर...
    2 months ago
  • काश - काश मुझे भी पीने की आदत होती,मैं कब का मुर्दा हो गया होता। छुटकारा मिलता आज के आतंकवाद से, किसी शमशान भूमि में सो गया होता। मेरा एतबार कौन करेगा, मैंने मुर...
    2 months ago
  • Kumaon University Nainital B.Ed entrance exam test result 2012 - कुमाऊँ विश्वविधालय, नैनीताल (उत्तराखण्ड)
    10 years ago