ASP and SQL Server

I am moving from Access 2000 to SQL Server. I am using ASP. Is there anything I should change in the ASP while migrating? Any ideas would haelp. Thanks.Hi,
I passed through the same situation, so I can be a bit of usefull for you.

Two changes can be done when moving from Access to SQL Server.

The first one is required, it is the connection string to oped the database.

The second is optional, you can move some of the procedures to the SQL Server database it self as a Stored Procedures so things will run faster. This step is not required but it helps.

Now, start by changing the connection string and use the IMPORT EXPORT wizard in SQL Server to transfer your existing data, or use the UPSIZE wizard in Access 2000 to transfer your existing data.

Hope this helps as a first step.

good luck|||Thanks a lot, this really helps.
So do you think it's better to move only the data to SQL Server instead of migrating everything?
I am using forms to enter data, and I am wondering if it is better to leave the forms in Access to enter data in SQL Server. Thanks.|||Well, I am glad that my experience could be usefull for you.
As I know, you can't have forms in SQL Server, so you can migrate:
Databases
Tables
Queries

Thats all

If you have more data to be intered you can use your forms to enter it in Access DB first then, migrate it to SQL Server DB.

Good luck

AS400 and SELECT *

I am migrating from 2000 to 2005 and dts to SSIS on several projects. I have not been able to successfully (without error) pull from an AS400 table using "SELECT *". If I reference all field names, it works fine. SELECT * actually works in the sense that it pulls over all the data, but it fails at the end, almost like an unepected end of stream. Normally it wouldn't be a problem tp qualify each field, but when I do the data gets sorted. In this instance I can't have it sorted because (without going into the stupid details) the production needs to match the development to suit my boss.

Connection = .Net Provider for OleDb/IBM DB2 UDB for iSeries IBMDA400 OLE DB Provider

Data Source = DataReader Source (I have yet to be able to use and OLE Source for AS400 without error, although my colleagues can - weird)

Here is the error I get when I change:

SELECT FieldName1, FieldName2, FieldName3 FROM LIBRARY.FILENAME

to

SELECT * FROM LIBRARY.FILENAME

Error: 0xC0047038 at DTF_LoadSqlServer_BOSS_Tebosspf8H, DTS.Pipeline: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "OLE_SRC_Tebosspf8H" (4492) returned error code 0x80004003. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.

Error: 0xC0047021 at DTF_LoadSqlServer_BOSS_Tebosspf8H, DTS.Pipeline: SSIS Error Code DTS_E_THREADFAILED. Thread "SourceThread0" has exited with error code 0xC0047038. There may be error messages posted before this with more information on why the thread has exited.

I

Any suggestions? Is there a property on the task or connection that will fix this? Or any idea why I can't use an OLE Source to pull from AS400?

I know it may not be an answer you're looking for, but I've had great luck using the Microsoft OLE DB for DB2 driver. It's included with one of their feature packs.

article on Windows 2003 + sql

I came across an article long time ago and cannot seem to track it now.
It talked about the advantages of migrating your SQL 2000 from Windows 2000
to windows 2003.
Can someone who knows about this, send me a linkHi
I don't know of any SQL Server specific articles, but you will gain all the
generic advantages from using this version:
Top 10:
http://tinyurl.com/bqnx
Why upgrade:
http://tinyurl.com/2u2j9
Performance comparison:
http://tinyurl.com/x7ma
Upgrade site:
http://tinyurl.com/2yhgj
Analyst reports:
http://tinyurl.com/8fnxj
Background:
http://tinyurl.com/adhaf
John
"Hassan" wrote:
> I came across an article long time ago and cannot seem to track it now.
> It talked about the advantages of migrating your SQL 2000 from Windows 2000
> to windows 2003.
> Can someone who knows about this, send me a link
>
>

article on Windows 2003 + sql

I came across an article long time ago and cannot seem to track it now.
It talked about the advantages of migrating your SQL 2000 from Windows 2000
to windows 2003.
Can someone who knows about this, send me a link
Hi
I don't know of any SQL Server specific articles, but you will gain all the
generic advantages from using this version:
Top 10:
http://tinyurl.com/bqnx
Why upgrade:
http://tinyurl.com/2u2j9
Performance comparison:
http://tinyurl.com/x7ma
Upgrade site:
http://tinyurl.com/2yhgj
Analyst reports:
http://tinyurl.com/8fnxj
Background:
http://tinyurl.com/adhaf
John
"Hassan" wrote:

> I came across an article long time ago and cannot seem to track it now.
> It talked about the advantages of migrating your SQL 2000 from Windows 2000
> to windows 2003.
> Can someone who knows about this, send me a link
>
>

article on Windows 2003 + sql

I came across an article long time ago and cannot seem to track it now.
It talked about the advantages of migrating your SQL 2000 from Windows 2000
to windows 2003.
Can someone who knows about this, send me a linkHi
I don't know of any SQL Server specific articles, but you will gain all the
generic advantages from using this version:
Top 10:
http://tinyurl.com/bqnx
Why upgrade:
http://tinyurl.com/2u2j9
Performance comparison:
http://tinyurl.com/x7ma
Upgrade site:
http://tinyurl.com/2yhgj
Analyst reports:
http://tinyurl.com/8fnxj
Background:
http://tinyurl.com/adhaf
John
"Hassan" wrote:

> I came across an article long time ago and cannot seem to track it now.
> It talked about the advantages of migrating your SQL 2000 from Windows 200
0
> to windows 2003.
> Can someone who knows about this, send me a link
>
>

Array data type in SQL Server 2005

Hi,

I was migrating from Oracle to SQL Server 2005 using SSMA (SQL Server Migration Assistant) but i`ve found an issue, i can′t find how to fix it. In my stored procedure in PL/SQL exists this lines:

TYPE T_ARRAY_COL IS VARRAY (1000 ) OF VARCHAR2 (50);
A_COLUMNS T_ARRAY_COL := T_ARRAY_COL();

Somebody know how can i simulate this data type ARRAY. I was reading http://msdn.microsoft.com/msdnmag/issues/04/02/TSQLinYukon/ but some things are not clear for me.... please help me, give me one hand.

Thank you

David

There is no array data type in SQL Server. You can simulate one using the built-in data types and a table (table variable or temporary table or permanent table).

declare @.t_array_col table( i int not null identity primary key, v varchar(50) not null )

insert into @.t_array_col values('a')

insert into @.t_array_col values('b')

....

select v from @.t_array_col where i = 1

select v from @.t_array_col where i = 2

...

Alternatively, you can just have fixed-length strings that represent the entire array and split them using the built-in functions.

declare @.array_str varchar(max)

set @.array_str = replicate(replicate('x', 2), 100) -- array of size 100, value is of fixed length - 2 bytes

select substring(@.array_str, (@.n * 2) + 1, 2) -- n varies from 0 through 99

set @.array_str = stuff(@.array_str, (@.n * 2) + 1, 2, replicate('z', 2)) -- initialize nth element

You can use varbinary(max) to store numeric values although it is tricky to convert from binary to one of the numeric data types & forth.

Also, there are more efficient ways to do this in SQL than trying to simulate arrays. You will get better performance by using set-based logic.