Data Conversion supported on Standard Edition?

I created an Integration Services Package that runs fine from my local computer using BIDS. However when I imported into our SQL Server and try to run it from there I get the following error:

DTS_E_PRODUCTLEVELTOLOW. The product level is insufficient for component "Data Conversion"

We are running SQL Server 2005 Standard Edition 64-bit.

We have integration services installed on the server. Is data conversion something that is not supported on Standard Edition?

Also have a similar message for "Send Mail Task."

Is there anywhere that outlines what features are supported on each version?

What version of SQL Server are you running on your developer's workstation?|||

I'm running BIDS on my workstation which is connecting to our SQL Server. The same SQL Server where the integration package will not run from when imported into. So I'm actually not running a full blown SQL Server database on my workstation, just using BIDS on it along with Studio.

The Management Studio on my workstation is 9.00.3042.

SQL Server Integration Services is 9.00.3042. (Found by going to About > Help in Visual Studio)

Our SQL Server version is 9.00.3050

|||So both your workstation and the server are running SQL Server Standard edition? Not developer/enterprise edition?|||

Server is definitely Standard Edition. When I installed the workstation components on my workstation I honestly don't remember if I installed them from the Standard edition or Developer edition. Most likely I installed the workstation components using the Developer edition. Is there a way to check?

I guess that would explain why it works on my workstation but not on the server?

|||

Erikk Ross wrote:

Server is definitely Standard Edition. When I installed the workstation components on my workstation I honestly don't remember if I installed them from the Standard edition or Developer edition. Most likely I installed the workstation components using the Developer edition. Is there a way to check?

Run this query on the server and again on your local version:

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')|||

Yeah, like I said I don't have the actual Database engine installed on my workstation. But our server version is SP2: 9.00.3050.

So I guess Data Conversion is not supported in Standard Edition? Or send mail? Is there a matrix somewhere that shows what features in Integration Services are supported in each version of SQL Server? It would seem to me that data conversion is something that is pretty common...I'm a little suprised it would require the Enterprise edition.

|||Alright, well, I do think it's because you're developing a package in a Developer environment, which is a higher level than the Standard edition that your server runs. If your server was an Enterprise version, you wouldn't have a problem (of course).

Uninstall and reinstall the SSIS components from the Standard Edition CDs and you should be fine. The Data Conversion component should work in the Standard Edition.|||

Phil Brammer wrote:

Alright, well, I do think it's because you're developing a package in a Developer environment, which is a higher level than the Standard edition that your server runs. If your server was an Enterprise version, you wouldn't have a problem (of course).

Uninstall and reinstall the SSIS components from the Standard Edition CDs and you should be fine. The Data Conversion component should work in the Standard Edition.

That was indeed the solution. I just did a quick test and creating the package from a Standard edition version did allow the data conversion to work correctly. Thank you!!

|||

Well, after uninstalling SQL Server workstation components on my development machine and reinstalling the Standard version it did not fix my problem. Any package created on my local development machine still does not work when imported into SQL Server. Get the same Product level to low error.

At least the good news is that I can create packages on the SQL Server itself and they seem to work fine. My best guess is that uninstalling the developer edition and reinstalling the standard edition just wasn't enough. I would suspect that if I was to completely wipe my machine clean then install Standard Edition it would probably be ok. But that is more hassle than it's worth.

|||

Erikk Ross wrote:

Well, after uninstalling SQL Server workstation components on my development machine and reinstalling the Standard version it did not fix my problem. Any package created on my local development machine still does not work when imported into SQL Server. Get the same Product level to low error.

At least the good news is that I can create packages on the SQL Server itself and they seem to work fine. My best guess is that uninstalling the developer edition and reinstalling the standard edition just wasn't enough. I would suspect that if I was to completely wipe my machine clean then install Standard Edition it would probably be ok. But that is more hassle than it's worth.

Any NEW packages created still don't work?|||

Correct, I created a brand new package. Well I first tried to rebuild my old package but that didn't work either, so I just created a new one. Same problems, runs fine on my local workstation, but when imported into SQL Server gives the same error.

I am currently installing standard edition workstation components on a new workstation with Visual Studio and will try to create a package from there and import it and see if that works.

|||Run this on the server, please:
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')|||

Phil Brammer wrote:

Run this on the server, please:
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

9.00.3050.00 SP2 Standard Edition (64-bit)

Well now I am completely lost. I just installed the standard edition workstation components on a brand new machine. A machine that has never had SQL Server installed on it. I created a new package in Visual Studio, imported it into our SQL Server, ran it, and still get the same ProductLevelToLow errors.

Error: SSIS Error Code DTS_E_PRODUCTLEVELTOLOW. The product level is insufficient for component "Data Conversion".

|||

After reading this I realized my problem: http://blogs.msdn.com/michen/archive/2006/08/11/package-exec-location.aspx

I was running the package from my workstation using Management Studio, unaware that the package was actually running on my local workstation and not on the server. Since it was running on my workstation it required Integration Services to be installed which it wasn't, so that is why I got those error messages. As soon as I ran it directly from the server it worked fine.

*sigh* I wish this was more obvious. I thought that by running it in Management Studio it was just automatically running it on the server.

Data Connection

So after a few hours of banging my head against my computer, here I am. :)

Backstory: A few weeks ago I installed web matrix and MSDE Rel A so I could create some .NET pages from my local machine, connect to a remote SQL Server 2000 database, and then post the .NET pages on the web server. All went well, I could connect to the datasource it brought up the table column names, everything was perfectly working (should've been my first hint to leave well enough alone). Well today, I think, "hey you know I want to give this new Visual Studio 2005 Express Edition a try". So I install it. I thought that if web matrix was that simple to connect, create and post "surely" VSE would be too, but with more features. I also read how I should go ahead and install SQL Server express and SQL Server Managment Studio Express, so I did.

So I go to create a datasource in VSE, but VSE had other plans. An error message popped up. Something to the effect, "An error has occured while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be cuased by the fact that under the default settings SQL Server does not allow remote connections..." I noticed that it said something about SQL Server 2005 so I figured I'd need to change that so I used the Microsoft SQL Server (OLE DB). It returned the error "[DBNETLIB][ConnectionOpen(Connect()).]SQL Server does not exist or access denied". I flipped back over to web matrix and now it doesn't work either. I have tried referencing the server by servername\db_name and ip address as well. All result in the same.

Any thoughts on how to get me going?

Try .\SQLEXPRESS

Data Available in a Trigger

I am creating a transaction log trigger for a table.

I would like to log the following data

The user login id: SYSTEM_USER

The User's Computer name: ?

The servers time and date: GetDate()

The trigger Action: Update, Delete, or Insert

The unique record ID for the affected table:

One column for the deleted row in xml raw:

One column for the inserted row in xml raw:

Is there a way to get the users computer name?

Consider this:

"select * from inserts for xml raw"

This is nice because i want to store the inserted and/or deleted table information as xml columns. But I would like to handle the transaction log in a way that created one transaction row add per row in the inserted or deleted table.

My end goal is to insert into the transaction log table as follows:

Lets say that my update trigger contains an inserted table with two rows and the deleted table would have the same, two rows.

I would like to insert two rows into my transaction log. with the username, date time, action and one column for the inserted row as xml raw, and one column for the deleted row as xml raw.

Anyone know how to do this?

It would be nice if i could impliment something like this:

'select * as xml raw from inserted' And it ould return as many rows as is in the inserted table, each row having one column wich represents that row in xml format.

'select * from inserted for xml raw' This is not good because it returns one row with one column whose value is an xml representation of the entire inserted table.

Thanx

Jerry Cicierega

The host_name() function will return this information, but it is not always set. Try looking this up in books online.|||

Thank you for responding. Yes this works on my system. The computer name part of my issue is solved.

The xml part of my question still stands.

Thank you !

Data Access to SQL Server 2005 from remote computer.

We have a program (Hindsite) that we're using on our LAN. It uses ODBC to access data on the server from client PC. Hindsite directory on the server is mapped drive on each client in the office.

We would really like to be able to access the same program database on the server from the remote PC over the internet.

Can SQL Native Client do that?

What would be the best way to accomplish it?

How to make this application to be able to access it's directory on the server over the Internet like it was a mapped drive on the remote PC?

Thank you very much in advance.

SQL native can do that.

You need to make sure your server is accessible from remote site, either via direct internet connection or via "middlebox", such as NAT. As long as you can ping and telnet to your server machine from the remote site, you can use SQL native client to connect to your sql server.

|||

thank you very much for your advice.

I'll try that, hopefully it works.

one thing I don't understand - when the program asks for a directory on server what do I need to direct it to when I install it on the remote computer?

thank you again.

|||Can you be more specific.

dafult database is not master

Hello,

I'm a c# programmer and a SQL Server 2005 newby so...be gentle with me !
Here is my problem. I used to log on my computer with login A. while using this login, I've created a couple of databases on SQL SERVER Express with SQLServer managment studio express (FolkTaleDb, AdventureWork, etc...). One day, the ITManager changed my login access to my computer to LoginB (for some reason, LoginA is still activ in ActivDirectory so I can still use it). With that new login, I've only created one new database called TestSpatial. Well now, when I connect to SQLServer without explicitly mentionning the initial catalog in the connectionstring (I connect from my VS application), the default database is master when logged as LoginA, but TestSpatial when logged as LoginB ! I have to type explicitly 'master' (in my connectionstring) in order to connect to master instead of TestSpatial. How can I sort this out ?

Any help is welcomed !

David HILPIPRESue either the GUI to change your default database or the command sp_defaultdb.

Jens K. Suessmeyer:

http://www.sqlserver2005.de