How to export/import SQL Server tables with constraints/indexes

The Need: I have to copy the data in tables belonging to a schema user from one database in SQL Server 2005 to another. Note: the target table has all the indexes and constraints already, and I just wanted to refresh the data from source to target.

The Problem: The export/import wizard fails miserably when trying this task due to all the indexes and constraints on the target SQL tables (these are tables for a vendor software application which uses different schema users vs different databases for separating dev/test/prod environments). Additionally, I tried using SQL Server 2008 R2 SSMS's generate script functionality to generate both schema and data according to this post. This resulted in a 8GB file which failed when I ran sqlcmd with it on the target server. It seems to me there's some bug with either the generate script wizard or SQLCMD in dealing with strings that span multiple lines.
The errors when running SQLCMD were:
Msg 105, Level 15, State 1, Server , Line 
Unclosed quotation mark after the character string ... 
Msg 102, Level 15, State 1, Server , Line 
Incorrect syntax near ... 

The solution: I ended up doing the following: 
 1. Run script to generate SQL to drop all the tables belonging to a schema user on the target database. (this will drop all the dependent indexes/constraints etc) 

select 'DROP table [' + table_schema + '].[' + table_name + ']' 
from information_schema.tables 
where table_schema = 'mySchema' 

2. run the script generated from step 1 on target server to drop the tables. 
3. Run export/import wizard in SQL Server 2005 SSMS to copy all the desired tables from source to target database 
4. Run SQL Server 2005 SSMS to generate schema script of all the desired tables in source database, making sure to script all the indexes, constraints, and also script the if exists option. 
5. Run the script generated from step 4 on target database to recreate the indexes and constraints. 

Hope this helps!

SSIS connection to Oracle server

Really want to get this down before I forget and hopefully this helps someone out there.

Issue: Connecting to an Oracle 10gR1 (can be other versions of 10 and 11 server) source from SSIS, development on Windows 7 x64 bit environment.

Solution: You will have to download the Oracle 11gR2 32-bit client from Oracle web site, do a custom install to only install the OLE DB provider and .NET provider.
Remember that if you want to run the SSIS package on a SQL Server 64-bit server, you will have to download the Oracle 11gR2 64-bit client and install that wherever the SSIS pkg will run.
In SSIS, choose the "Oracle provider for OLE DB", make sure you specify the Oracle servername, port and service name like this in "server or file name":
servername:1521/servicename
This way you DO NOT need to configure tnsnames.ora file. I repeat, you do not have to use archaic methods of configuring connection string.

Additional issue(s): My PATH environment variable is longer than 1023 characters due to many SQL versions. During Oracle client installation which complains about the max length of PATH variable, just ignore it, Windows 7 can handle more that 1023 chars in PATH var.

SSIS OLE DB source missing records in SQL Server 2005 SP2

Issue: I found out recently that a SSIS package I was developing was missing random records/rows when extracting data from a complex database view from SQL Server 2005 SP3. I was using the "table or view" data access mode and picking the view from the dropdown list.

Solution/Workaround: After troubleshooting and verifying that nothing was interfering with the extraction (i.e. anti-virus software, etc.), I found a workaround by changing the data access mode to "SQL command" and just typing "select * from " instead. I am pretty sure this is a bug with SSIS, but I don't know if it is caused by my use of common table expressions and linked server in the database view.

xcopy - Insufficient memory error

Today, noticed errors were occurring with some of my backup xcopy jobs. When I run the xcopy command via command line, it had something like this at the end of it:

.
.
.
Insufficient memory
376 File(s) copied

C:\>

I did a bit of research and realized that the cause was that some of the filenames in folders to be xcopied had really long names. And coupled with the long path, they were exceeding 254 characters in length. This is a Windows 2003 server, so I downloaded and installed the Windows Server 2003 Resource Kit Tools (http://www.microsoft.com/download/en/details.aspx?id=17657) to use the robocopy.exe along with the /MIR (mirroring switch) .

Now the job runs smoothly without the 254 limitations in path plus filename string .

Magento USPS string is not in UTF-8 error

Issue: After migrating Magento from dev to production environment, USPS shipping method stopped working.

in system.log file, the following error is shown:
Warning: SimpleXMLElement::addAttribute() [simplexmlelement.addattribute]: string is not in UTF-8 in {magento install dir}/app/code/local/Mage/Usa/Model/Shipping/Carrier/Usps.php

Resolved by:
going to Magento Admin console -> Configuration -> System -> Shipping Methods -> USPS, and re-enter/paste your USPS User ID supplied by USPS to you (usually thru email when you first signed up for the account). and hit save configuration.

Magento Error: Base table or view already exists: 1050 Table ’sales_order_status’ already exists

Scenario: After changing permissions on folders/files in var/cache/, the Magento site was crashing with this error:

Base table or view already exists: 1050 Table ’sales_order_status’ already exists

________
It looks as if the software is trying to upgrade from 1.4 to 1.5, but the new tables are already there.

Workaround: Log into database with the MySQL account and run these SQL statements (in the following order to avoid foreign key constraints) to drop the new tables:

drop table sales_order_status_label;
drop table sales_order_status_state;
drop table sales_order_status;
drop table paypal_cert;

After executing these statements, hit the site again via web browser, and the software should recreate the 4 tables along with finishing the upgrade.

Note: I've locked down the permissions on folders and files, but looks like Magento upgrade creates new folder/files with 777 permission owned by apache user. You may have to change the permissions on these new folder/files to your liking...

Weird permissions error when changing password through SQL Server Management Studio

Ran into a case this week when a business superuser could not change other users' passwords. I checked and the superuser had the securityadmin role. Basically after sleuthing around, I came to the conclusion that the issue is caused by incompatible service pack levels between the database server (which is at SQL Server 2005 SP3) and the business superuser who had recently had to reinstall management studio (but installed a non SP version of the client).

This is not the first time something strange happens when using a lower service pack client against a higher service pack server. I've ran into weird maintenance plan issues before with this scenario...

I just wish Microsoft can just warn users when connecting to a higher service pack SQL Server to help track down issues or to remind to update management studio clients.