Database Compatibilty with ASP.net

Hi:

If I will export my SQL express based database to SQL 2000 database - would I will be facing any compatibiltiy issues with my application. Do I just need to change the connection string in my config file and everything will work?

Danka

No, it won't just "work". It is possible for it to work though. You will need to ensure that your SQL Compatibility level on your express database is set to 80. Even then, I am not sure about express. It may not be possible at all.

Data types

I need to export the name of certain user tables along with the names of their columns and which type the column contains ie. varchar / integer and so on. I can figure out the table and column names - but how do i retrieve information about the data-types in each column ?

My query so far:

--------
select obj.Name as Tbl,Col.Name as Col
from sysobjects obj, syscolumns col
where obj.xtype='U' and obj.Name like 'netop%' and obj.id=col.id
--------

Thx. in advanceSELECT A.TABLE_NAME, B.COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.TABLES AS A
INNER JOIN INFORMATION_SCHEMA.COLUMNS AS B
ON A.TABLE_NAME = B.TABLE_NAME
WHERE A.TABLE_NAME = 'yourTable'

Very useful views and I strongly recommend that your read more about them on BOL.|||WOW - that was fast - thanks a lot|||Beware of objects with the same name and different owners! Include a join on TABLE_SCHEMA to be safe:

SELECT A.*, A.TABLE_NAME, B.COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.TABLES AS A
INNER JOIN INFORMATION_SCHEMA.COLUMNS AS B
ON A.TABLE_NAME = B.TABLE_NAME
AND A.TABLE_SCHEMA = B.TABLE_SCHEMA

blindman|||Good point and well spotted.

Data type int to char

Hi again everyone,
I build a temp table with a data type of int on one column. I then use BCP
to export this table to a TXT file. I use the -c option and also the -t""
(no column delimiter).
The problem I am having is that I am limited to 5 characters for this column
by the company the data is being sent too (archaic I know, I think they are
reading it with Cobol) and the int exports to about 9 characters.
Is there a way to change an int to a char on the fly with INSERT? Something
like CAST?
Beating my brains out, thanks for any suggestions.
George
george collins wrote:
> Hi again everyone,
> I build a temp table with a data type of int on one column. I then
> use BCP to export this table to a TXT file. I use the -c option and
> also the -t"" (no column delimiter).
> The problem I am having is that I am limited to 5 characters for this
> column by the company the data is being sent too (archaic I know, I
> think they are reading it with Cobol) and the int exports to about 9
> characters.
> Is there a way to change an int to a char on the fly with INSERT?
> Something like CAST?
> Beating my brains out, thanks for any suggestions.
> George
You can optionally use a query with BCP. That would give you the
opportunity to convert the INT into a char(5). But you may have problems
if the values in the INT are greater than 99,999 since you'll run into 6
digit numbers.
David Gugick
Imceda Software
www.imceda.com
|||Yeah, I ran in to that kind of issue, what my main problem was the original
character type was a float which would not convert. I changed that to an
int and I am off and running.
Thanks.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:%23enR57ooEHA.3464@.tk2msftngp13.phx.gbl...
> george collins wrote:
> You can optionally use a query with BCP. That would give you the
> opportunity to convert the INT into a char(5). But you may have problems
> if the values in the INT are greater than 99,999 since you'll run into 6
> digit numbers.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>

Data type int to char

Hi again everyone,
I build a temp table with a data type of int on one column. I then use BCP
to export this table to a TXT file. I use the -c option and also the -t""
(no column delimiter).
The problem I am having is that I am limited to 5 characters for this column
by the company the data is being sent too (archaic I know, I think they are
reading it with Cobol) and the int exports to about 9 characters.
Is there a way to change an int to a char on the fly with INSERT? Something
like CAST?
Beating my brains out, thanks for any suggestions.
Georgegeorge collins wrote:
> Hi again everyone,
> I build a temp table with a data type of int on one column. I then
> use BCP to export this table to a TXT file. I use the -c option and
> also the -t"" (no column delimiter).
> The problem I am having is that I am limited to 5 characters for this
> column by the company the data is being sent too (archaic I know, I
> think they are reading it with Cobol) and the int exports to about 9
> characters.
> Is there a way to change an int to a char on the fly with INSERT?
> Something like CAST?
> Beating my brains out, thanks for any suggestions.
> George
You can optionally use a query with BCP. That would give you the
opportunity to convert the INT into a char(5). But you may have problems
if the values in the INT are greater than 99,999 since you'll run into 6
digit numbers.
--
David Gugick
Imceda Software
www.imceda.com|||Yeah, I ran in to that kind of issue, what my main problem was the original
character type was a float which would not convert. I changed that to an
int and I am off and running.
Thanks.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:%23enR57ooEHA.3464@.tk2msftngp13.phx.gbl...
> george collins wrote:
>> Hi again everyone,
>> I build a temp table with a data type of int on one column. I then
>> use BCP to export this table to a TXT file. I use the -c option and
>> also the -t"" (no column delimiter).
>> The problem I am having is that I am limited to 5 characters for this
>> column by the company the data is being sent too (archaic I know, I
>> think they are reading it with Cobol) and the int exports to about 9
>> characters.
>> Is there a way to change an int to a char on the fly with INSERT?
>> Something like CAST?
>> Beating my brains out, thanks for any suggestions.
>> George
> You can optionally use a query with BCP. That would give you the
> opportunity to convert the INT into a char(5). But you may have problems
> if the values in the INT are greater than 99,999 since you'll run into 6
> digit numbers.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>

Data Transformation Services Revisited

I am trying to export a table from SQL Server to Oracle8i home edition. I want to schedule the export package to run at a certain time. The package gets created, but when the time is reached for the package to execute, it doesn't execute. There are no errors at all. It just doesn't run. The package is there because I see it in the Data Transformation Services->Local Packages list. Any ideas? Thanks.If you scheduled the package to run you should see it Enterprise Manager under the server in Management/SQL Server Agent/Jobs.
There you will see last run status among other things.

Of course the SQL Server Agent needs to be running.|||Its not in the list of jobs. I got no error that the SQL Server Agent isn't running. How do I check to make sure it is running?
Thanks|||Originally posted by exdter
Its not in the list of jobs. I got no error that the SQL Server Agent isn't running. How do I check to make sure it is running?
Thanks

Do a refresh on the jobs. If it isn't there, then that is your problem. After you right-click the DTS package and do schedule, you should see the job in the "Jobs" panel.|||I don't think you would get an error that it is not running

In Enterprise Manage you see if it is running by the small green "play" icon on it. You can start it from there with right clicking on it.
Or you can start it with Service Manager.

It would probably be a good thing to set it to always start automatically.|||The jobs were there, all with error messages. The Server Agent is running as well.|||Then you know the schedule works at least :-)

Can you run the task manually, and does it work without errors then?

Originally posted by exdter
The jobs were there, all with error messages. The Server Agent is running as well.|||Originally posted by exdter
The jobs were there, all with error messages. The Server Agent is running as well.

OK, that's a different error than what you were seeing before (no jobs).

Try double-clicking the job, click the "steps" tab, select the only step, click edit, cut and paste the command to a command prompt and see what you get.

That will probably fail and give you a more detailed error. Otherwise, you probably have a permissions issue.|||Manually the job works. Thanks for your help.|||When I put the line in a command prompt, everything worked. The table was created in Oracle.|||Originally posted by exdter
When I put the line in a command prompt, everything worked. The table was created in Oracle.

If the job reliably fails when automated and reliably works when executed manually, it is almost definitely a permissions issue. Right-click the job, do "Start Job", wait a few minutes, do a refresh, and check if the job does reliably fail when scheduled.

Check who owns that job. Try changing that to sa or local Admin. You also may want to try deleting and recreating the job (I've seen that help).|||I created the job logged in as administrator. I deleted and recreated the job more than once, and it still doesn't run on a schedule. Thanks for your help.|||The scheduled job will run under the credentials the SQL Server Agent has if using Windows integrated security.
For the connection with Oracle I guess you set that in the Oracle connection in the DTS package. (I don't know much about Oracle security).|||I did set the connection to Oracle also in the DTS. Its strange that the package will run manually and not on the schedule.|||Originally posted by exdter
I did set the connection to Oracle also in the DTS. Its strange that the package will run manually and not on the schedule.

This is an almost sure sign of a permissions issue. Did you look at the Windows Event Log? The error message for scheduled jobs should be in there.|||I couldn't find anything in there about it.|||Just to make sure: Have you installed (Oracle) SQL*Net on the SQL Server? SQL*Net is similar to Client Connectivity in SQL Server. It installs all the underlying DLL's, executables, and such that any application (DTS included) would need to connect to an Oracle Database.

If so, can you do a TNSPing to the SID that you are trying to reach? DTS is horribly client bound, so running the package manually would mean that you are running the package from your lap/desk-top.|||I didn't have the SQL* Net installed. Thanks.

Data Transformation Services

When I use the DTS to export data from SQL Server to Oracle 8i, I have no trouble doing it by exporting a whole table. However, if I try to export to Oracle using an SQL query, the table in Oracle gets created, but the table doesn't get populated by the data in my query. I get an ok on the creation of the table, but on transferring of data, I get an error that the table in Oracle doesn't exist. Any ideas? Thanks.Is the creation of the table and pumping of data separate tasks?
If so can you execute them one at a time in the DTS package designer?

I don't know if there is a good way of debugging DTS packages, if anyone knows of it I would appreciate to know how too.