Showing posts with label SQL. Show all posts
Showing posts with label SQL. Show all posts

Sunday, February 03, 2019

Unable to Enlist in the Transaction

I recently received an error while trying to run a SSIS package in Visual Studio. The full error is seen as below.

Error: The SSIS Runtime has failed to enlist the OLE DB connection in a distributed transaction with error 0x8004D024 "The transaction manager has disabled its support for remote/network transactions.".

This error is happening when a component with transactions try to connect and manipulate data on a remote server.

To avoid this error, two things can be done.

* Remove the transaction handling from the component. – This should be done only if the transactions are not required or as a temporary measure.

You can do this by selecting the properties of the component, also check the package properties to make sure transactions are not enabled at the package level.

For an explanation of the options please visit the URL.

* Configure the Distributed Transaction Coordinator (DTC).

For a component to work correctly with transactions, below two things are required.
1. Running Distributed Transaction Coordinator (DTC) Service

This service should be running on the database server the component connects to and on  the machine where the package is running. This will coordinate the work between two machines / servers.

This can be checked and started by going to Windows Services.

2. Properly configuring the security of DTC.

To make the DTC service work properly, it should be given permission as required. This can be done by opening the Component Services window. In the component services window, navigate to Component Services –> Computers –> My Computer –> Distributed Transaction Coordinator. Right click Local DTC and click on Properties to open properties window.

If you are intending to run any workloads with transactions via any external servers you should enable “Network DTC Access”.

Depending on the situation you should allow Inbound, Outbound or both transaction manager communication options.

For complete list of explanations on each property please refer the following URL.

Thursday, February 15, 2018

Filtering Results of a Stored Procedure

Recently I needed to filter the results of a Stored Procedure, After some searching I found 2 options for this as below.


1. Using OpenRowSet Command

SELECT * FROM OPENROWSET ('SQLOLEDB', 'Server=ServerName;TRUSTED_CONNECTION=YES;', 'EXEC sp_Who2')

Remember in this method this feature should be enabled in the server.

2. Using Temporary Tables.

-- Creating a temporary table.
CREATE TABLE #tblSPWho2
(SPId INT, Status NVARCHAR(200), LoginName NVARCHAR(200), HostName NVARCHAR(200), BlockedBy NVARCHAR(200), DBName NVARCHAR(200),
Command NVARCHAR(200), CPUTime BIGINT, DiskIO BIGINT, LastBatch NVARCHAR(200), ProgramName NVARCHAR(200), SPId2 INT, RequestId NVARCHAR(200))

-- Inserting the results of the Stored Procedure into the temporary table.
INSERT INTO #tblSPWho2
EXEC sp_Who2

-- Doing the required filtering using the temporary table.
SELECT * FROM #tblSPWho2 WHERE LoginName = 'Domain\UserName'

-- Removing the temporary table.
DROP TABLE #tblSPWho2

SP_Who2 Stored Procedure will bring all the processes that is currently active in SQL server with the relevant information, the above query will filter the results and will only display the processes that are initiated by the given username (Domain\UserName).

Sunday, March 19, 2017

SQL – The target principal name is incorrect. Cannot generate SSPI context.

Recently in one of my Virtual Machine (VM)s I received the above error message when trying to connect to a SQL Server which I used to connect on other times.

image

After having a look I found that the trust between my virtual machine and the domain was broken. You can find the status of the secure channel by using the PowerShell command Test-ComputerSecureChannel.

image

This will also be evident when you try to login to the computer using a domain account. It will generate the following message.

image[6]

To fix this you can try using the below methods.

1. PowerShell

Use the command Test-ComputerSecureChannel.

If your current login has the required access in the domain you can use the below command.

Test-ComputerSecureChannel –Repair

If you need to use another account than the current logged user then you need to use the –Credential parameter when calling the command.

Test-ComputerSecureChannel –Repair –Credential MyDomain\MyUser

2. Joining the domain again.

This will also get fixed by removing the machine from the domain and adding it back. Before removing the computer from the domain make sure you have access to a local administrator account on the computer. Otherwise you will not have a way to login to the computer.

This can be achieved by going to computer system properties,

  1. removing the computer from the current domain,
  2. restarting the computer.
  3. adding the computer to the domain again
  4. restarting the computer

To avoid the two restarts you can try using the following PowerShell commands.

$myPC = Get-WmiObject Win32_ComputerSystem
$myPC.UnjoinDomainOrWorkGroup("Account Password", "Account Username”, 0)
$myPC.JoinDomainOrWorkGroup("Domain", "Account Password", "Account Username", $null, 3)
Restart-Computer -Force

Tuesday, April 15, 2014

Microsoft.Ace.OLEDB.12.0 Provider Not Registered

Recently while trying to import some data from Excel onto SQL on a new machine I got the following error at SQL Server Import and Export Wizard Step 2. After doing some searching found the solution is to install Office 2007 Data Connectivity Components System Driver.

image

If you need to  get it fixed use the following link to get it from Microsoft Download Center.

http://www.microsoft.com/en-us/download/details.aspx?id=23734

Sunday, January 05, 2014

Finding Number of Records in SQL Tables

By using the following query you will be able to get all the tables with at least one record in the selected database. It will be handy when you needed to get an idea of the number of records in a database. This uses two SQL Server system views, namely SysObjects and SysIndexes.





  1. SELECT sysobjects.name AS [Table Name],
  2.         MAX(sysindexes.rows) AS [Number of Records],
  3.         sysobjects.crdate AS [Created Date],
  4.         sysobjects.refdate AS [Referenced Date]
  5. FROM sysobjects
  6. INNER JOIN sysindexes ON sysobjects.id = sysindexes.id
  7. WHERE sysobjects.xtype = 'U' -- Filtering all the User Tables.
  8.     AND sysindexes.rows > 0 -- Getting all the tables having at least one reoord.
  9. GROUP BY sysobjects.name,
  10.     sysobjects.crdate,
  11.     sysobjects.refdate
  12. ORDER BY 2 DESC -- Ordering by the number of records in table.




 

Above query will bring the following results on the Northwind database.

image

Sunday, November 25, 2012

SQL Truncate vs. Delete

By now as you know, in this post I am going to discuss about two SQL commands we use regularly to clear data in our tables.

Usage





  1. DELETE FROM TABLENAME
  2. TRUNCATE TABLE TABLENAME




Even though they do a similar thing, there are some differences which makes them unique commands.

 

DELETE

TRUNCATE

1. Considered as Data Mining Language (DML) statement. Considered as Data Definition Language (DDL) statement.
2. Can be used to delete all or part of data in a table. Will delete all the data in a table.
3. Will log the actions. There will be no logging on record deletion.
4. Will use more locks. Will use fewer locks.
5. Will use more resources. Will use less resources.
6. Slow. Fast.
7. Will not reset the identity seed. Will reset the identity seed to 0.
8. Can be used to delete data in tables having relationships. Cannot use in tables having relationships.
9. Can be used in tables involved in log shipping or replication. Cannot be used in tables involved in log shipping or replication.
10. Transaction can be rolled back. No rollback.
11. Table may keep the empty pages. Can be released by running, SHRINKDATABASE (Database Name). Data pages related to the table will be de allocated and returned to the system.
12. Related Triggers are fired. Triggers are not fired.

 

If you used DELETE to fully remove all the records, you can reset the identity value by running the following command.





  1. DBCC CHECKIDENT (TABLENAME, RESEED, 0)




If you do have some rows left in table, simply replace 0 with the last identity column value. For example if you put 5 instead of 0 then the next record inserted into the table will have an identity column value of 6.

If you jus need to check the current identity value just use the following command.





  1. DBCC CHECKIDENT (TABLENAME, NORESEED)




Sunday, September 30, 2012

Importing Data into SQL

If you need to import data from a file, this can be achieved by using SQL Bulk Insert command. Recently I did use this method to import some 500 000 data from few comma separated value (CSV) files and thought to share it with you. What you need to remember is, if you are importing data from more than one file the data should have the same format through out the files.

This is a sample set of data I imported into my table from the file named File1.csv.

Login,Name,Date,Result,Pass

U0001,Roman Silva,1/10/2010 17:23,100,TRUE

U0002,Anthony Don,28/09/2010 10:01,70,TRUE

U0003,Saman Perera,16/09/2010 11:31,90,TRUE

U0004,Silvia Raz,26/09/2010 22:11,40,FALSE

U0005,Rebecca Maine,18/09/2010 11:30,100,TRUE

I used the following script to create a temporary table for my imported data.

  1. SET ANSI_NULLS ON
  2. GO
  3.  
  4. SET QUOTED_IDENTIFIER ON
  5. GO
  6.  
  7. CREATE TABLE [dbo].[TABLENAME_ImportedData](
  8.     [LoginId] [NVARCHAR](50) NOT NULL,
  9.     [Name] [NVARCHAR](200) NULL,
  10.     [Date] [DATETIME] NULL,
  11.     [Result] [INT] NULL,
  12.     [Pass] [NVARCHAR](8) NULL
  13. ) ON [PRIMARY]
  14.  
  15. GO

To fetch data from the file I used the following script.

  1. BULK INSERT [TABLENAME_ImportedData]
  2. FROM 'D:\DataFiles\File1.csv'
  3. WITH (
  4.          FIELDTERMINATOR =',', -- Since my columns are seperated using commas (,).
  5.          ROWTERMINATOR ='\n',  -- Since each data row is in its own line.
  6.          FIRSTROW = 2          -- Since my first row is having column names. Please note FIRSTROW is not recommended to skip the column names.
  7.       )

While running the script I faced an issue with the date since my server was set to US English as the default language. In US English the dates should be in MDY format. So I had 3 choices, either to change the date formats on my data files, change the server default language to another language which has its date format as DMY or change the date format of the server. I used the easy way to change the date format of the server by running  the following command.

  1. SET DATEFORMAT dmy

If you like to change SQL server default language and need help please read my article on that.

Saturday, September 29, 2012

Changing SQL Server Default Language

If you needed to change the default language of a SQL Server login you can do so by using sp_defaultlanguage command.

First determine the current default language by using the @@language variable as follows.

  1. SELECT @@language

Then choose the language you want to change to from the available languages. You can find the available languages by the following command.

  1. EXEC sys.sp_helplanguage

After selecting the language wanted to change, run the following command to change the default language, I am changing it to British using the following command.

  1. DECLARE @Login NVARCHAR(30)
  2. SET @Login = SYSTEM_USER
  3. EXEC sp_defaultlanguage @Login, 'British'

Run the @@language to see whether the default language is changed as you wanted. Do not forget to use a new query window to see the changes.

Sunday, July 01, 2012

Creating Database Table and Moving Data

When the need comes to port tables from one server / database to another server / database there is an easy way than creating the tables manually and importing data into table.
 
By using the following query you can import the table structure and the data of the required table.
  1. SELECT *
  2. INTO [DestinationServerName\SQLServerInstanceName].[DestinationDatabaseName].[OwnerName].[DestinationTableName]
  3. FROM [SourceServerName\SQLServerInstanceName].[SourceDatabaseName].[OwnerName].[SourceTableName]
 
Using the above method you can transfer tables between different databases, Server instances or different database servers.
Consider the following example in which I am transferring the Customers table from Northwind database to my Test database.
 
  1. SELECT *
  2. INTO [Test].[dbo].[Customers]
  3. FROM [Northwind].[dbo].[Customers]
 
Here SELECT statement will behave the same way as in a normal SELECT * FROM TableName statement meaning you can use all the techniques used in SELECT statements to filter the data needed to be ported. For example following query will only bring customers who are from UK.
  1. SELECT *
  2. INTO [Test].[dbo].[Customers]
  3. FROM [Northwind].[dbo].[Customers]
  4. WHERE Country = 'UK'
 
One thing to remember is, this method will not import the keys, meaning if you had a primary key set to CustomerId it will not exist in your new table. So you need to add the keys (primary and secondary) to the new table to match it with the source table. If you had identity specification turned on in your source table this will set it for you, so you do not need to set it again.

Monday, March 05, 2012

Dynamically Creating Computed Columns in SQL

If you are into programming you should have definitely worked with calculated fields in SQL. But for others I will briefly explain what they are.

Calculated columns are columns which depend on other columns. It gets its value by calculating which can involve values of other columns. The calculation formula is the only thing stored in the column.

So each time the column is referenced the calculation is done. But if you use the keyword “persisted” while creating the column then the values will be kept in the table. Whenever a referenced value is updated the computed column is also automatically updated to highlight the change. Also by using persisted you can index a computed column.

You can create a table with persisted computed column as follows.

  1. CREATE TABLE Customer
  2. (
  3. CustomerId INT IDENTITY(1, 1) NOT NULL,
  4. CustomerFirstName NVARCHAR(100) NOT NULL,
  5. CustomerLastName NVARCHAR(100) NOT NULL,
  6. CustomerFullName AS CustomerFirstName + ' ' + CustomerLastName PERSISTED
  7. )

One thing to remember is that you cannot directly insert values to a computed column.

I think you got a basic idea of computed columns. Now I would like to show how to create a computed column dynamically. For example think that you need to add a computed column to a table using a stored procedure. It is not a big deal.

I need to insert a TotalOrder column to the table named FoodOrder.

  1. CREATE TABLE FoodOrder
  2. (
  3. OrderId INT IDENTITY(1, 1) NOT NULL,
  4. OrderDate SMALLDATETIME NULL,
  5. CustomerName NVARCHAR(100) NOT NULL,
  6. TotalStarter INT NULL,
  7. TotalMainCourse INT NULL,
  8. TotalSoftBevarage INT NULL,
  9. TotalLiquer INT NULL,
  10. TotalDessert INT NULL
  11. )

This can be done using the following query.

  1. DECLARE @sExecuteCommand VARCHAR(250), --Keepa the command to be executed.
  2. @sColumns VARCHAR(150) --Keeps the columns to be included in the formula.
  3.  
  4. SET @sColumns = 'TotalStarter+TotalMainCourse+TotalSoftBevarage+TotalLiquer+TotalDessert+'
  5. SET @sExecuteCommand = 'ALTER TABLE FoodOrder ADD TotalOrder AS ' + SUBSTRING(@sColumns, 1, LEN(@sColumns)-1) -- Creating the computed column.
  6. EXEC (@sExecuteCommand)

Note that a cursor or a loop can be easily used to populate the variable “sColumns” with the columns required for the formula.

Hope this helps.

Monday, January 30, 2012

UPDATE with JOIN

If you need to update data of a table (DestinationTable) with data of another table (SourceTable) there are 3 ways you can get this done in SQL.

Method 1

This is the most common and simple way with using a sub query.





  1. UPDATE DestinationTable SET DestinationColumn =
  2.     (SELECT ValueColumn FROM SourceTable WHERE DestinationTable.MappingColumn = SourceTable.MappingColumn)




 

Method 2

This users the most common FROM clause to join the two tables as shown below.





  1. UPDATE DestinationTable SET DestinationColumn = SourceTable.ValueColumn
  2. FROM SourceTable
  3. WHERE DestinationTable.MappingColumn = SourceTable.MappingColumn




 

Method 3

Last method mentioned below uses the join clause to join both the tables to make the update happen properly.





  1. UPDATE DestinationTable SET DestinationColumn = SourceTable.ValueColumn
  2. FROM DestinationTable
  3. INNER JOIN SourceTable ON DestinationTable.MappingColumn = SourceTable.MappingColumn




 

Hope these helps.

Saturday, September 17, 2011

TechEd Australia – Videos

As you might know TechEd Australia ended few weeks back. I am sure lot of you might not have the chance to visit it. The good thing is you can watch the recorded sessions online now by visiting the following site.

http://channel9.msdn.com/events/teched/australia/tech-ed-australia-2011

I think this will be mostly useful to people who didn’t visit TechEd and also to people who visited it to refresh their minds.

Tuesday, April 26, 2011

Database Not Accessible

Recently after restoring a database backup which I got from another server I repeatedly encountered an error when logged in using a user who is given permission. But when ‘sa’ account is used I could work with the database without any problem.

The error message was “The database DATABASE NAME is not accessible. (ObjectExplorer)” which was not helpful since it didn’t give any clue to figure out the issue.

image

Later I found that the issue is because the user in the restored database is not properly mapped to the user in the new server and the resolution for this is to run the stored procedure sp_change_users_login to correct the orphaned user.

  • sp_change_users_login 'update_one', 'USER', 'LOGIN' – Links the given user in the current database to the specified login.
  • sp_change_users_login 'auto_fix', 'USER' – Links the given user in the current database to the login having the same name in the current server.

After running this you will be able to access the restored database without any issue using the mentioned database user.

Saturday, November 13, 2010

Configuring SQL Server Instance for Azure Development Storage

If you have not configured Azure Development Storage to use your SQL Server, then while running your application targeted for Azure you will see an error similar to the following.

“Windows Azure Tools: Failed to initialize Development Storage service. Unable to start Development Storage. Failed to start Development Storage: the SQL Server instance ‘localhost\SQLExpress’ could not be found. Please configure the SQL Server instance for Development Storage using the ‘DSInit’ utility in the Windows Azure SDK.”

To overcome this you need to run the DSInit command using Windows Azure SDK Command Prompt.

Step 1 – Start the Windows Azure SDK Command Prompt.

Step 2 – Type DSInit /sqlinstance:. on the prompt and press enter.

The dot (.) at the end denotes the local SQL Server instance running on your machine. DSInit command details are as follows.

Syntax:

DSInit [/sqlinstance:<SQL server instance>] [/forceCreate] [/user:<Windows account name>]

Description:

Initialize the development store for usage by

Reserving http ports for the blob, table and queue services for the current user, and

Creating the database needed by the blob and queue services

Options:

sqlinstance : the name of the SQL Server instance on the local machine that should

be used for Development Storage. The default is SQLExpress.

Use "." for unnamed instance.

forceCreate : recreate the database even if it already exists.

user : user for whom ports are to be reserved. By default it is the current user.

.

After doing the configurations it will display a success screen similar to the following.

Then when you run your Azure ready application the Azure Development Storage and Azure Development Fabric will get started and notify you on your system tray as shown below.

After a while your application will also start without complaining if it is Azure ready.

Tuesday, November 02, 2010

Saving Table Changes in SQL Server 2008 R2

Have you noticed that with the default settings of SQL Server 2008 R2 you cannot use the design view to edit and save table changes which requires table to be recreated. But if you used earlier SQL Server installations you should remember that this functionality was possible. When you try to save such a table you will see an error similar to the following in SQL Server 2008 R2.

But the good news is that we can enable this functionality by going to SQL Server options.

Click Tools –> Options and expand Designers node and select ‘Table and Database Designers’.

Then uncheck the option ‘Prevent saving changes that require table re-creation’ and press OK.

Now if you try to save the table changes SQL will do the alteration without any complains.

Monday, May 31, 2010

Changing the Shared Feature Directory in SQL Server Setup

Today when I tried to install SQL Server 2008 R2 I couldn’t change the shared feature installation directory in the setup. Both the textbox and browse button were disabled restricting me to select the desired path.

After sometime I found the cause for this. It is because there were some SQL related programs already installed in my computer, because they are there the SQL setup uses the same directory to setup the rest of the programs.

If you need to relocate the install directory then simply you need to uninstall all the SQL related programs using Programs and Features and re-run the SQL Server setup. Then the browse buttons will be active enabling you to select an alternate location.

Thursday, May 06, 2010

"Login failed. The login is from an untrusted domain and cannot be used with Windows authentication."

If you are getting the above error while trying to connect to a database, the reason is you are using Windows Authentication to login to the SQL Server while being in another untrusted domain.

For example if the SQL Server machine is a member of the CompanyDomain and if you are in MyDomain or if you are in a Workgroup the you will face the above issue while trying to connect to the SQL Server.

The connection string used in the web.config while the above error is generated is as follows.





  1. <connectionStrings>
  2.   <add name="cnnStr" connectionString="Data Source=BI-SVR;Initial Catalog=BBIDatabase;Integrated Security=True"/>
  3. </connectionStrings>




 

There are three ways to fix this problem.

1. Use the SQL Authentication to login to the SQL Server.

You can get this done by changing the connection string to use SQL authentication while connecting. But you need to know the credentials of an account which is having permissions to your required database or the System Administrator (SA) password. For simplicity I will use SA account details in the connection string.





  1. <connectionStrings>
  2.   <add name="cnnStr" providerName="System.Data.OleDb" connectionString="Data Source=BI-SVR;Persist Security Info=True;Password=YourPassword;User ID=sa;Initial Catalog=BBIDatabase"/>
  3. </connectionStrings>




 

2. Login to your machine using the same Domain.

If you login to your machine using a domain account which the SQL Server is added to, then this error will vanish. But for this you need to add your machine to the same domain which the SQL Server machine is added to (CompanyDomain) also to properly get authenticated the account used should have proper permissions set to access the database in the SQL Server.

3. Make the account trusted in SQL Server.

By making the account you use to login to your machine trusted account in SQL Server and giving it appropriate permissions to access databases will also permit you to fix this error.

Monday, April 12, 2010

SQL Server Reporting Services Error - The report server cannot open a connection to the report server database. A connection to the database is required for all requests and processing. (rsReportServerDatabaseUnavailable)

Recently in one of my virtual servers I got the above error when trying to open the SQL Server Reporting Services (SSRS).

If this is happening to you I recommend checking the following things in your server.

 

1. Remote Connections in SQL Server.

Go to Start –> All Programs –> Microsoft SQL Server 200x –> Configuration Tools and open SQL Server Configuration Manager.

Now check whether the TCP/IP and Named Pipes are enabled in the 3 Protocol sections.

If they are disabled then enable them then check whether the remote connections are enabled using the SQL Server Management Studio by right clicking on the SQL Server (Parent node in the Object Explorer) and selecting properties.

Then go to the Connections and make sure that the Allow remote connections to this server checkbox is checked.

Then restart the SQL Server Service using the SQL Server Services section in the SQL Server Configuration Manager or by going to the machine services by Start –> Administrative Tools –> Services.

 

2. Check the SSRS Service Account.

Go to Start –> All Programs –> Microsoft SQL Server 200x –> Configuration Tools and open Reporting Services Configuration Manager. Then click on the Service Account section and verify its details. If you are not sure about the details it’s always good to re-enter them since there is nothing to loose.

3. Check the SSRS Database and Credentials.

Click the Database section and see whether all details are correct. This is very important since SSRS need to get connected to its SQL database to function properly. Pay good attention to validate the SQL Server Name, verify the database mentioned under Database Name exists in the specified SQL Server. If you are not sure of the existing settings just click on the Change Database button and it will open a wizard to step through.

If you already have reports in the SSRS then always try to Choose an existing report server database before Creating a new report server database since when you create a new database you will loose existing customizations you might have.

Then go to the bottom section to see the Credentials provided are correct. I am used to validate these by opening the management studio and trying to connect using the provided account. After validating the account using the management studio, to be in the safe side you can set those settings using the Change Credentials button.

 

4. Check your Firewall.

If the you have enabled a firewall such as Windows Firewall or a 3rd party firewall it might be blocking the functionalities required. Just try switching off the firewall, if that solves then try creating a rule for the firewall to authorize the required connections.

 

Hope this helps to correct the error, if not let me know some times I may be able to help.

Friday, March 05, 2010

Project has Stopped Working

If you create a .NET application to use SQL CE database when you try to run your application on a Windows Vista or Windows 7 machine you might get an error saying your application did stop working, and the error details might show that your are having a problem with System.Data.SQLServerCE.

The reason for this is that your system is not having the SQL Server CE runtime in your machine. To fix it what you need to do is to install the Microsoft SQL Server Compact 3.5 Service Pack 1 on your system. You can download it from the following Microsoft link.

http://www.microsoft.com/downloads/details.aspx?FamilyId=DC614AEE-7E1C-4881-9C32-3A6CE53384D9&displaylang=en

Thursday, January 17, 2008

Getting Time part only from SQL DateTime Value

Have you tried getting the time value only from a SQL DateTime value?

I have seen many people struggling with datetime fields in their programming life. Some people face problems getting the date only from datetime field. If you are one who is struggling please read my article on April 2007.

In this article I would like to mention how you can get the time only from a datetime value.

SELECT CAST(CAST(GETDATE() AS FLOAT) - FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME)

Here what I am doing is simply casting the datetime value returned by GETDATE() into FLOAT then I am substracting the full value (value without the fractions) from that. So I will get the fraction part of the float value.

Note that when you cast a datetime value to a float, the full part represents the date and the fraction part represents the time.

2008-01-18 18:22:15.640
39463.7654587963

Then I will cast the result back to the datetime which brings me the time.

This method of casting datetime value to a float value is always handy when working with datetime values.