The Temple of Fu

Man one of these days MS will make this easy. I am one of the ones that came from DTS land where after a few years things were comfortable. So in an effort to make things easier MS gave us SSIS to overcome DTS shortcomings. Well they made my life a living hell in trying to learn this /rant.

The latest in my struggles is trying to deduce from the garbage that is SSIS messages what I need to do. The latest being:
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E14 Description: "Could not bulk load because SSIS file mapping object 'Global\DTSQLIMPORT ' could not be opened. Operating system error code 2(The system cannot find the file specified.). Make sure you are accessing a local server via Windows security.".

What was I trying to do I will represent in this complicated flowchart.
Excel document –> SQL Server

Confused yet? Me neither, this in DTS land would have taken an hour tops! I am going on 4 hours! Not only am I battling Excel and it’s ‘here let me make this easier for you and guess what types or lengths your columns can hold’ productivity enhancements. I am also needing to deduce these bs error messages that are given at freaking runtime from SSIS designer!
/rant #2

Ok so the actual fix now is, is instead of using a SQL Destination (even though you are inserting into SQL wtf) use an OLE DB Destination. The reason being to use a SQL destination you need to run the package on the machine that hosts SQL Server (destination). If you want to use a remote SQL Server, use OLEDB destination.

Hope that helps.

You’ve created a temp table in a stored procedure and now you are testing it which involves dropping a temp table manually if you are not executing the stored procedure in it’s entirety (common if you are testing chunks of it).

So here’s the easy solution. We need to check if the temp table exists within the TempDB database and if it does, we need to drop it. Place the following at the top of your procedure, obviously before creating the temp table =] Now everytime it runs it will drop the table and then recreate it, no more manually dropping the temp table!

IF OBJECT_ID('tempdb..#Temp') IS NOT NULL
DROP TABLE #Temp

Replace #Temp with the name of your temp table.

Hope this helps!

So while developing some applications I have the need to clean a table and reset identity fields. Let it be known that if any of the fields are foreign keys in other tables those tables need truncated as well, working from last linked table forward to the Primary key table.

In the below example make sure to put in place of myDatabase and myTable your database that contains the table to be truncated. =]


use myDatabase
truncate table myTable



use myDatabase
DBCC CHECKIDENT ('myTable', RESEED, 0)


I was setting up a new database on a new SQL Server 2005 installation that has a few linked servers configured (which happen to be SQL Server 2000), while testing execution of a remote stored procedure it failed with the error:

Msg 7411, Level 16, State 1, Line 1
Server 'psp6new' is not configured for RPC.

The problem was that RPC was not configured for the linked server. this is not set by default, you can see what settings are configured with
exec sp_helpserver

If ‘rpc and rpc out’ are not in the results, then the the linked server isn’t configured for RPC. To do so:

exec sp_serveroption @server='myserver', @optname='rpc', @optvalue='true'
exec sp_serveroption @server='myserver', @optname='rpc out', @optvalue='true'

Or alternatively you can right click on your linked server bring up it’s properties and select True for the value from the RPC and RPC Out rows.

Hope this helps!

Ok I am not a SQL god and make no claims of even being a SQL guru so this will be short and sweet.

I received this error on a new SQL Server 2000 install (hey some have legacy systems to support still!) while trying to connect from a web application.

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 – No connection could be made because the target machine actively refused it.)

After a day or so of trouble shooting and testing different configurations, the standard tests Named Instance vs TCP/IP, firewalls, user name password, connection strings, tel-netting etc, it boiled down to SQL server SP4 not being installed on the SQL server. After we installed SP4 all was good.

Hope this helps someone!

A developer at work today encountered the error below when trying to insert stored procedure results into a temp table where the stored procedure contains a reference to a linked server. Well fortunately he fixed it and shared his information so I am sharing it with you.

The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].

He enabled the MSDTC service but still received the error. In order to completely fix this error he had to perform the following to enable network access, by default the network access settings of MSDTC are disabled on new installations of SQL Server 2000 on computers that are running Windows Server 2003.

To work around this behavior, install network DTC access on both servers:

1. Click Start, and then click Control Panel.
2. Click Add or Remove Programs, and then click Add/Remove Windows Components.
   In the Components box, click Application Server, and then click Details.
3. Click to select the Enable network DTC access check box, and then click OK.
4. Click Next, and then follow the instructions that appear on the screen to complete the installation process.
5. Stop and then restart the Distributed Transaction Coordinator service.
6. Stop and then restart any resource manager services that participates in the distributed transaction (such as Microsoft SQL Server or Microsoft Message Queue Server).

References:
Microsoft Support

In order to start the MySQL daemon at boot time one must add it to the system’s boot up process. Note this only works on SysV init runlevel systems.

Run the following command as root.

chkconfig mysqld on
or the more verbose where you specify the runlevel
chkconfig mysqld --level 2345 on

To clarify, a runlevel determines what services are started when a Linux computer is booted. When you boot into runlevel 1, only the core system files are loaded similar to Windows Safe Mode. Runlevel 2 is there if you want to create a custom runlevel the same as Runlevel 4. Runlevel 3 is what most servers boot to and it starts all of your services and other things required for a true multi-user environment. Runlevel 5 is for starting a GUI and this is what most desktop systems boot to.

You can then check that MySQL is enabled to start by running the following command. Again run this command as root.

chkconfig --list

That’s it!


#service mysqld stop
#/usr/bin/mysqld_safe --skip-grant-tables &
#mysql --user=root mysql
mysql>update user set Password=PASSWORD('new-password-here') WHERE User='root';
mysql>flush privileges;
mysql>exit

Kill the mysqld_safe process

#fg
Ctrl+z

Then restart MySQL
#service mysqld restart