How to use Import and Export wizard (DTS) to transfer data to or from a SQL Azure database?

A lot of people are starting with experiments to look to the possibilities of SQL Azure. One of the things to start is a database with content. As described in one of my previous blogs, you can use the SQL Azure migration wizard. This is a nice tool, but most people are already aware of the Import and Export Wizard in SQL Server Management Studio (SSMS), also known in the past as Data Transformation Services DTS.

In this blog I will describe how you can use the Import and Export Wizard to transfer data from an on-premise SQL Server database to an SQL Azure database. What do you need to have to start using the Import and Export wizard.

1. Windows Azure subscription
2. Create a SQL Azure server and database
3. Configure the SQL Azure firewall
4. On premise SQL Server with SQL 2008 R2 with SSMS.
5. Create the tables you need in the SQL Azure database.
6. Create a clustered index on each table to which you want transfer data. Without a clustered index you CAN'T transfer data to a SQL Azure database.

Now you have done all preperations we can start the Import and Export Wizard. Click Start, All Programs, SQL Server 2008 R2, Import and Export Data. The Import and Export wizard is now started.

1. Choose a datasource. This is your on-premise SQL Server.
2. Choose a destination. This will be your SQL Azure database.
1. Destination: .NET Framework Data Provider for SqlServer
2. Encrypt: True
3. Integrated Security: False
4. Password: SQL Azure administrator password
5. TrustedServer: Certificate: True
6. User ID: SQL Azure administrator name. [username]@[sqlAzureservername]
7. Data Source: FQDN of your SQL Azure Server
8. Inintial Catalog: The SQL Azure database you have created to insert data.

3. Press next and execute the same steps as you normally do in the Import and Export wizard.
4. In case you forgot to create a clustered index you will get next message:
1. Messages
   
   Error 0xc020844b: Data Flow Task 1: An exception has occurred during data insertion, the message returned from the provider is: Tables without a clustered index are not supported in this version of SQL Server. Please create a clustered index and try again. (SQL Server Import and Export Wizard.

Enjoy using the Import and Export wizard to transfer data to your SQL Azure database.
Tweet

Msg 567, Level 16, State 7, Line 1 File 'D:\TraceFiles\vrijdag8161700.trc' either does not exist or is not a recognizable trace file. Or there was an error opening the file. fn_trace_gettable

If you have a SQL Profiler trace file which you want to open in the query analyzer you can make use of the fn_trace_gettable function.  Assume you are running this script from a server named: ServerA and you have a SQL Server named: ServerSQL. On ServerA you have stored a trace file in the folder D:\TraceFiles.

On ServerA you executed next command to open the trace file in the SQL Server Management Studio.

SELECT eventclass,TextData,ApplicationName,NTUserName,
LoginName,CPU,Reads,Writes, Duration, ClientProcessID,
SPID,StartTime,EndTime,BinaryData
FROM :: fn_trace_gettable('D:\TraceFiles\vrijdag8161700.trc',default )

This will return:
Msg 567, Level 16, State 7, Line 1


File 'D:\TraceFiles\vrijdag8161700.trc' either does not exist or is not a recognizable trace file. Or there was an error opening the file.

The error is caused by the fact that the function fn_trace_gettable is executed on the SQL Server named: ServerSQL. This server has no acces to the file in 'D:\TraceFiles\vrijdag8161700.trc' on ServerA.

To solve this share the folder D:\Tracefiles for the user who need access to it.
SELECT eventclass,TextData,ApplicationName,NTUserName,
LoginName,CPU,Reads,Writes, Duration, ClientProcessID,
SPID,StartTime,EndTime,BinaryData
FROM :: fn_trace_gettable('\\ServerA\TraceFiles\vrijdag8161700.trc',default )

Now the ServerSQL has access to the file on ServerA.
Enjoy using fn_trace_gettable in your SSMS.

: Tweet

How to update your PowerPivot Field List after a change in the datamodel?

It can happen that you have made a nice PowerPivot dashboard with a lot of usefull pivots and charts. After a while some changes are made in the datamodel of the database on which you have build your PowerPivot dashboard. For instance one column is added to an existing table or view. In this blog I will describe how you can update your PowerPivot Field List with the new column, so you can make use of it.

• Open the Excel sheet with your PowerPivot dashboard.
• Select PowerPivot tab in the Ribbon.
• Press the button PowerPivot Window button to launch the PowerPivot window.
• Select the table or view on which a column is added

• Press the button Table Properties.

• Be sure to select Column names from Source.
• Scroll to the right. At the end you will see the new added column. Select this column. In this example Zipcode
• The added column ZIPCode is now added to the PowerPivot Window.
• Select the Excel sheet with you rPowerPivot dashboard.
• Show the Field List.
• The PowerPivot Field list has detected that a change is made. Press the refresh button.

• Select the Data tab in the Ribbon of your Excel sheet and press the refresh all button. This update your excel sheet with the information of the added column.
  

• Now look in the PowerPivot Field List and you will see the added column in the table or view.

Enjoy using your PowerPivot dashboard with the new column(s)

How to change the database for your PowerPivot sheet.

As of Office 2010, you can use PowerPivot in combination with Excel. The PowerPivot is a very powerfull tool to analyze the data in your database. After having build some nice pivots on one of your databases, it can happen that you want to re-use the same sheet on a different database.

In this blog I will explain how you can update your PowerPivot sheet with data from a different database.

• Open your Excell sheet.
• Select the PowerPivot ribbon tab.

• Select the PowerPivot Window button in the ribbon. The PowerPivot window is now started.
• Select the design tab.
• Select button: Existing Connections.

• Select your PowerPivot data connection.

• Press edit button.
• Change the server and or database you want to use.
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  

• Press the 'test connection' button to see if you can connect to the database.
• Press save.
• Press Close
• Select the Home tab in the PowerPivot ribbon.

• Select Refresh all button. Now the data refresh window is displayed.

 

• Press Close when data is updated successfully.

Now we need to update the excell sheet with the data in the power pivot sheet.
 

• Go back to your excell sheet.
• Select the Data tab in the ribbon.
• Select the refresh all button.

Now all data in your excell sheet is updated with the data of the selected database.
Enjoy analyzing your data with the PowerPivot.