Manually modifying a SQL Server *.bacpac file prior to importing

I encountered a situation where I needed to export a data-tier application (i.e. *.bacpac) from an Azure SQL Database and import it into a local instance of SQL Server. The *.bacpac created just fine; however, during import, it failed with a collation error. For various reasons, I did not want to modify the Azure SQL database and regenerate the *.bacpac. Instead, I found this helpful post:

http://inworksllc.com/editing-sql-database-azure-bacpac-files/

Those instructions utilize a file called dacchksum.exe that is available at:

https://github.com/gertd/dac/tree/master/drop/debug

Here is the process that I used:

• Unzip the *.bacpac file (I use 7-zip, but any normal zip tool should work)
• Open the model.xml file
• Find the offending stored procedure, comment the body of the procedure, and add a return statement
• Save and close model.xml
• Zip all of the content into a new *.zip
• Rename the new *.zip into a *.bacpac (e.g. newfile.bacpac)
• Run the following command:
  dacchksum.exe /i:newfile.bacpac
• Mark and copy the new checksum value
• Open Origin.xml (from the unzipped folder)
• Find the checksum (towards the bottom of the file) and replace it with the new checksum
• Save and close Origin.xml
• Zip all of the content into a new *.zip
• Rename the new *.zip into a *.bacpac (e.g. newfile2.bacpac)
• Import the new *.bacpac (using SQL Server Management Studio)

This process was successful ... except that importing a *.bacpac fails on the first error (it does not report all errors on the first run). After I fixed the first import error, it failed a second time on a different error, so I had to repeat the process multiple times. In my case, the *.bacpac was over 750 MB, so zipping everything multiple times was taking several seconds each time. I wondered if there was a more efficient process (where I did not need to zip, compute checksum, alter checksum, then zip again). I opened dacchksum.exe with ILSpy and found that the checksum is just a SHA256 checksum. Conveniently, PowerShell has a Get-FileHash cmdlet that does exactly that. Using Get-FileHash instead of dacchksum.exe, the revised steps are as follows:

• Unzip the *.bacpac file
• Open the model.xml file
• Find the offending stored procedure, comment the body of the procedure, and add a return statement
• Save and close model.xml
• Run the following PowerShell command:
• Get-FileHash fullpathto\model.xml | Format-List
• Copy the new checksum value
• Open Origin.xml
• Find the checksum (towards the bottom of the file) and replace it with the new checksum
• Save and close Origin.xml
• Zip all of the content into a new *.zip
• Rename the new *.zip into a *.bacpac (e.g. newfile2.bacpac)
• Import the new *.bacpac (using SQL Server Management Studio)

This process was also successful, and was quicker because I only had to zip the content once.

Using both 32-bit and 64-bit Oracle Client

While trying to resolve Oracle Client bitness conflicts, I came across this post:

http://realfiction.net/2009/11/26/Use-32-and-64bit-Oracle-Client-in-parallel-on-Windows-7-64-bit-for-eg-NET-Apps/

I followed those instructions, using the 12c Instant Client downloads, and it resolved my error. In case that post disappears, here is a summary of the instructions (credit to Frank Quednau for his post):

1. Download both Oracle Client packages (32-bit and 64-bit). I downloaded the following files (from Oracle.com):
   instantclient-basiclite-nt-12.1.0.2.0.zip
   instantclient-basiclite-windows.x64-12.1.0.2.0.zip
2. Unblock both downloaded files before continuing
3. Each of those .zip files contains a folder called instantclient_12_1; extract and rename that folder somewhere on the machine, e.g.:
   C:\oracle\12c_32   (this is instantclient_12_1 extracted from instantclient-basiclite-nt-12.1.0.2.0.zip)
   C:\oracle\12c_64  (this is instantclient_12_1 extracted from instantclient-basiclite-windows.x64-12.1.0.2.0.zip)
4. Start an elevated Command Prompt (aka Run as Administrator)
5. Change to %WINDIR%\system32, e.g.:
   C:\Windows\System32>
6. Make a soft link that points to the 12c_64 folder:
   C:\Windows\System32>mklink /d 12c C:\oracle\12c_64
   symbolic link created for 12c <<==>> C:\oracle\12c_64
7. Change to %WINDIR%\SysWOW64, e.g.:
   C:\Windows\SysWOW64>
8. Make a soft link that points to the 12c_32 folder:
   C:\Windows\SysWOW64>mklink /d 12c C:\oracle\12c_32
   symbolic link created for 12c <<==>> C:\oracle\12c_32
9. NOTE: Make sure that both symbolic links have the same name, e.g.:
   C:\Windows\System32\12c
   C:\Windows\SysWOW64\12c
10. Edit your system %PATH% environment variable and add the full path (do not use %WINDIR%) to the first symbolic link:
    ...;C:\Windows\System32\12c;
11. Restart your machine, or at least restart whatever application is connecting to Oracle

With this setup, TNSNAMES.ORA does not seem to work. Instead, put the full descriptor directly in the connection string:

Data Source=(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XE)));User ID=someuser;Password=yourpassword;

Comparing triggers (or other objects) in two Oracle schemas

We had a customer ask a question recently about whether the trigger DDL in two different Oracle schemas was the same. While this is not 100% accurate, it is a simple way to see if there are any discrepancies:

select t.name       ,sum(coalesce(length(trim(replace(replace(t.text,chr(10)),chr(13)))),0)) ddl_chars

  from user_source t

 where type = 'TRIGGER'

 group by t.name

 order by t.name

It counts the number of characters in the trigger DDL. You can then compare the output in Excel or Beyond Compare or something similar (or an OUTER JOIN).

ORA-24344: success with compilation error

I have spent time recently working on some fairly lengthy Oracle PL/SQL scripts. The scripts are run via SQL*Plus (e.g. "SQL> @CreateOracle.sql"), and the main script references several other external scripts. One of the scripts creates a trigger on a table. The output shown in the log file is:

ORA-24344: success with compilation error 

However, by the time the rest of the scripts completed, SQL*Plus reported no errors on that trigger. It became a frustrating endeavor to try and find out what the actual compile error was. I finally had to remove all of the scripts that followed the failing script. With the failing script executing last, I was then able to use the USER_ERRORS view to see the actual compile errors (thanks to this post for the suggestion):

select * from user_errors

The actual error itself was very easy to fix ... once I was finally able to find it.

What is a "web service"?

I was recently asked a question at work about an application, EQuIS Professional, that uses TCP/IP to connect to Microsoft SQL Server (aka ADO.NET Sql Provider). The answer was somewhat lengthy, but may be useful for some discussions:

That is a great question. The answer to that question can be somewhat technical, and may vary depending on the context of the question. The term "web service" or "web service interface" can be defined in a variety of ways. Most definitions include the transfer of data between the client and the server. Often the term "web" implies HTTP (Hyper Text Transfer Protocol). One way of defining "web service" is a service that facilitates the transfer of data, typically in some text-based representation (e.g. XML or JSON), over the Internet via the HTTP protocol. The main benefit of sending the data in a text-based representation is to allow application integration. For example, text-based representation of data allows communication between a client application developed separately from and independent of the server application. Converting the data to/from the text-based representation incurs processing overhead; but that overhead may be a worthwhile cost for this type of web service.

When it comes to security, HTTP is no more secure than any other protocol. In fact, running a web service directly over HTTP would be very insecure (and not recommended). The security of a web service typically comes from SSL (Secure Sockets Layer). Using HTTP with SSL (i.e. HTTPS) is secure because all of the packets sent over the Internet are encrypted with SSL. That is why all secure websites use HTTPS; you would not, for example, want to use an online banking site that uses HTTP.

All network communications happen over a given port. There are "standard" ports used for specific protocols. For HTTP, the standard port is 80. For HTTPS, the standard port is 443. One reason that HTTPS-based web services are popular is because port 443 is typically already open in most firewalls; so using HTTPS does not require any additional firewall configuration. So an HTTPS-based web service takes advantage of the already open port. For a generic web service designed to facilitate the transfer of data between disparate systems, HTTPS is typically the best choice.

However when both the client and the server are designed to work together (e.g. EQuIS Professional and SQL Server), a text-based representation of the data is inefficient. HTTP is built on top of TCP/IP, and it is more efficient to transfer the data in well-defined binary representation that is known by both the client and the server. This type of communication is typically done directly over TCP/IP (without the additional HTTP layer). At this lower level, a "web service" may be a service that facilitates the transfer of data (in some mutually agreed binary representation) over the Internet via TCP/IP.

While transferring the data in binary form over TCP/IP is more secure than HTTP, it is still not inherently secure. So a web service using TCP/IP should still be encrypted with SSL. Microsoft SQL Server natively supports SSL encryption for all cient/server communications. When SQL Server acts as a web service using TCP/IP via forced SSL encryption, the communication is essentially as secure, and more efficient than, an HTTPS web service.

Because TCP/IP is lower in the network stack than HTTP/HTTPS, it does not have a "standard" port. Many different network protocols run over TCP/IP on different ports. When configuring SQL Server as an SSL-encrypted web service, it is important to consider the port that will be used. The default port for SQL Server is 1433. However, that is not the best choice for an Internet-facing instance of SQL Server (even if it is SSL-encrypted). One option is to use port 443, for the same reasons that an HTTPS-based web service uses port 443. Since most firewalls already have port 443 open, it greatly reduces the potential firewall related issues when establishing connections. Having SQL Server listen to port 443 on a public facing IP address is not much different than having IIS (aka HTTPS server) listen on port 443 on a public facing IP address. Another option is to have the public facing machine (or the firewall) use port forwarding from the public IP/port to the internal IP/port.

Given this background (I apologize if I have provided too much detail), let me answer your original question. If your question is "Can EQuIS Pro consume an HTTPS-based web service?", the answer is "not easily".  It would require a fair amount of implementation and testing to make something like that work, and the performance would likely degrade. Also, using HTTPS does not actually remove the dependency on TCP/IP ... it simply wraps TCP/IP in HTTP and in SSL (two additional layers) and runs it on a known port (port 443).


However, if your question is "Can EQuIS Pro (running externally) make a secure connection without requiring additional open firewall ports?", then the answer is "yes". By configuring SQL Server to force SSL encryption and to listen on port 443 (either directly or via port forwarding), you can achieve the same benefits of an HTTPS-based web service.

SQL Server Express - Adding sysadmin

I had a situation today where I needed to get into SQLEXPRESS. I was logged onto the machine as an Administrator, but SQLEXPRESS had been installed such that Builtin\Administrators was not a valid login with sysadmin server role. I found this post from Blipsalt that helped me:

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/76fc84f9-437c-4e71-ba3d-3c9ae794a7c4/sql-express-2008-r2-create-database-permission-denied-in-database-master

1.  shut down SQL Server from services

2.  open cmd window (as admin) and run single-user mode as local admin with this command:

"c:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Binn\sqlservr.exe" -m -s SQLEXPRESS

3.  open another cmd window (as admin)

4.  open sqlcmd:

sqlcmd -S .\SQLEXPRESS

Now add the sysadmin user:

a.  sp_addsrvrolemember 'domain\user', 'sysadmin'

b.  GO

5.  now Ctrl+C the single-user mode from the first cmd window to kill SQL Server.  Now restart it from services the normal way.  Log into Management Studio and the user you created should be listed under logins with the credential of "sysadmin."

Cannot resolve the collation conflict

Consider this SQL Server error message:

Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.

Suppose you install SQL Server using Latin1_General_CI_AS collation, then restore a database (from a different instance) that uses SQL_Latin1_General_CP1_CI_AS. Later, you create a temp table and then try and do a join from the database to the temp table on a char/varchar field ... you will get an error message similar to the message shown above.

You cannot join char/varchar fields that use different collations. Changing the collation of the tempdb is quite difficult ... typically requiring a complete reinstall of SQL Server. However, the COLLATE database_default option provides a relatively simple solution to the problem. Here is an example:

create table #tbl (
  fld1 varchar(20) collate database_default null
)

However, you should only use this technique when you are populating the temp table with the same data you are joining to. Blindly using COLLATE database_default can lead to some unexpected results.

Use BCP to export BLOBs to files

I came across a need to view all of the files stored in the BLOB (aka "image" column of a table in a SQL Server database. In this particular case, the files were all zip files, but the process should work the same for any type of file.

Step 1: Enable xp_cmdshell

This process relies on xp_cmdshell to run BCP. By default xp_cmdshell is disabled and must be enabled as follows:

-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO

You probably want to disable xp_cmdshell again (set the value to 0) when you are finished using it.

Step 2: Create the bcpFormat.fmt file

Inspired by this response to this post by duckworth, I created a bcpFormat.fmt file with the following content:

8.0
1
1 SQLIMAGE 0 0 "" 1 Image ""

Make sure that there is an empty line at the end of the file (thanks to this post by Jon Raynor).

Step 3: Query the BLOB table

This step is the trickier ... run a simple SELECT query that uses string concatenation to generate SQL statements as output. Here is an an example:

select 'EXEC master.dbo.xp_CmdShell ''BCP "select blobField FROM blobTable where idField = ' + CAST(idField as varchar(10)) + '" QUERYOUT C:\TEMP\' + filenameField + ' -T -fC:\TEMP\bcpFormat.fmt''' from blobTable 

Some items to note:

• Make sure to use the proper field (blobField, idField, filenameField) and table names (blobTable)
• You could add a WHERE clause to filter the rows that you export
• Make sure that the output path and *.fmt file exist and have necessary permissions 

Step 4: Execute the BCP statements

After running the query in Step 3 in SQL Server Management Studio, do the following:

1. Select all of the rows in the result grid 
2. Copy
3. Open a new (empty) query window
4. Paste
5. Execute

All of the BCP commands will run and export all of the files to the designated folder. 

Visualizing SQL Server Data Files

Here are a couple of interesting posts/tools that allow you to visualize the contents of a SQL Server database. They also help to illustrate the importance of physical page layout with regards to importance (e.g. be careful of AutoShrink in production databases!)

Visualizing Data File Layout I, by Merrill Aldrich
Visualizing Data File Layout II, by Merrill Aldrich
Internals Viewer for SQL Server, by Danny Gould

Script SQL Server Database (schema and data)

This blog post explains how to use SQL Server Management Studio to generate scripts (schema and data) for SQL Server 2008:

http://blog.sqlauthority.com/2009/07/29/sql-server-2008-copy-database-with-data-generate-t-sql-for-inserting-data-from-one-table-to-another-table/

In SQL Server 2008 R2, the options have changed slightly. There is no "Script Data = True/False" option. Instead, look under General options for "Types of data to script". The default is "Schema only", but the other options are "Data only" and "Schema and data". Choose the "Schema and data" option to generate scripts that create the tables and then populate the tables.

1. Login to SQL Server Management Studio 2008 R2
2. Navigate to your database
3. Right-click your database and choose Tasks ... Generate Scripts
4. Click Next> past the Introduction
5. Choose the entire database or specific tables/objects
6. Click Next>
7. Choose the desire file location and options
8. Click Advanced
9. Under General options, find Types of data to script
1. Change value to Schema and data
2. Click OK to close options
10. Click Next>
11. Review Summary
12. Click Next>
13. Click Finish

SSMS Tools

Here is a nice tool that works with Microsoft SQL Server Management Studio:

http://www.ssmstoolspack.com/

It includes several nifty features. One feature I used today was the ability to automatically generate INSERT statements from records in a table.

TSQL - counter/increment/rownumber

Here is a fairly easy way to get a row number in SQL Server (2005 or later):

DECLARE @seed INT = 100

SELECT

@seed + ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS RowNumber

,*

FROM sys.sysobjects