SharePoint, .Net, SQL, MS BI, Collaboration and Gadgets

Having difficulties to find help on reporting service’s functions like switch? You may not able to find any help on your local help. Go here to check on these:

http://msdn2.microsoft.com/en-us/library/ms157328.aspx

I got this error when trying to browse a cube. This happen once I installed MS SQL SP 2. From what i found, most likely the OWC component installation became corrupted. To solve this, just install OWC again. This works for me.

OWC installer.
http://www.microsoft.com/downloads/details.aspxFamilyID=7287252c-402e-4f72-97a5-e0fd290d4b76&DisplayLang=en

The following error text occur in the rigth pane:

Unexpected error occured: ‘Error in application.’

When trying to view the calculations tab (of any cube, including the samples that comes with SQL Server 2005) in Visual Studio (BI Development Studio) . This may cause by different versions of msmdlocal.dll and msmgdsrv.dll in %ProgramFiles%\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\ and %ProgramFiles%\Common Files\System\Ole DB\

If they are not the same, then try to update the ones in the PrivateAssemblies with the ones from ole db (Please backup the dlls just case…)

Do you ever get “Function sequence error” when running bcp with xp_cmdshell? The stored procedure executed fine.

SQLState = HY010, NativeError = 0
Error = [Microsoft][SQL Native Client]Function sequence error
NULL

I am not sure my solution can 100% solve the error, but it works in my machine. The trick is to turn on “Database Mail (Enable database stored procedures)” in “Surface Area Configuration for Features”. Try to run again and the error will gone!

*You may encounter this error while testing on the sample in “How to import multiple CSV files using MS SQL store procedure?“

“It is very troublesome to set up a link server to oracle”. I totally agree with that. Once you get any error from that, it is very hard to find out the actual problem out there. Here is a link that lists out steps to set up and troubleshoot a linked server to an Oracle database in SQL Server:

http://support.microsoft.com/kb/280106

If these steps do not able to solve your problem, you probably will stop at there. So far I do not find a better one online unless getting help for some guru.

You probably encounter issue where you have large transaction log but small data file, e.g 40GB transaction log and 60MB database.

Take a look at:  http://support.microsoft.com/kb/317375/en-us

Unreplicated transactions
The transaction log size of the publisher database can expand if you are using replication. Transactions that affect the objects that are replicated are marked as “For Replication.” These transactions, such as uncommitted transactions, are not deleted after checkpoint or after you back up the transaction log until the log-reader task copies the transactions to the distribution database and unmarks them. If an issue with the log-reader task prevents it from reading these transactions in the publisher database, the size of the transaction log may continue to expand as the number of non-replicated transactions increases. You can use the DBCC OPENTRAN Transact-SQL reference to identify the oldest non-replicated transaction.

For more information about troubleshooting unreplicated transactions, see the “sp_replcounters” and “sp_repldone” topics in SQL Server Books Online.

For more information, click the following article number to view the article in the Microsoft Knowledge Base:

306769 FIX: Transaction log of snapshot published database cannot be truncated
240039 FIX: DBCC OPENTRAN does not report replication information
198514 FIX: Restore to new server causes transactions to remain in log

These are some of the actions they took to solve the problems:

• Creating a new transaction log (other disk)
• Limiting the growth of the original log
• Triggering replication to clear the log.

If you trying to extract data from a flat file and got the following error:

Error 1 Validation error. Extract Interface Message ID Data: Extract Interface Message ID Data [1]: The data type for “output column “InterfaceMessageBody” (54)” is DT_NTEXT, which is not supported with ANSI files. Use DT_TEXT instead and convert the data to DT_NTEXT using the data conversion component.

It looks like one or more column datatype in the FlatFile Source has become DT_NTEXT instead of DT_STR.
You can try the following steps to rectify the error:

1. Right Click the Flat File Source Component that is generating this error.
2. Choose “Show Advanced Editor” from the context menu and then the “input and output properties” tab on the dialog box that appears.
3. Click on the + sign beside “Flat File Source Output” node in the treeview on the left side of the dialog box.
4. Click on the + sign beside “output columns” node in the treeview just below “Flat File Source Output” node. You would see all the columns available in the text file.
5. Select each column one after another and check their dataType property on the Right side of the Dialog box.
6. If the datatype is DT_NTEXT then change it to DT_STR.
7. Repeat step 6 for the all the columns where if columns datatype is DT_NTEXT.

* Thanks to Ritesh Modi for the solution.

We must use xp_cmdshell and an application which executes the command like sqlcmd or OSQL (and the –I switch). Here’s an example of how to do it:

osql -S%1 -U%2 -P%3 -d%4 -i”.\DataConversion.sql”

where –S,-U etc are the various options…

Just to share out some documentations here:

• http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/mysql.mspx
• http://www.microsoft.com/technet/interopmigration/sql.mspx
• http://www.convert-in.com/sql2mss.htm
• http://www.swissql.com/sql-server-migration.html  (This is a decent tool that helps in the migration.)
• http://www.bitpipe.com/detail/RES/1149540179_37.html (this is for SQL – MySQL but in reverse would work)

There are few ways we can backup individual tables. Here are some of them:

1. Use BCP or SSIS/DTS.
2. 3rd party vendors do it like litespeed (http://www.quest.com/litespeed_for_sql_server/ ).  Litespeed on the backend is just really doing a BCP for the most part. They’ve just made it easier by putting a GUI
3. Ditto for SQL BackTrack by BMC.