Does MSSQL Have The Ability To Do Named Row Types?

Jul 20, 2005

Hi all. We have a mix of informix and mssql server and I want to know
if something we do in informix has an analogous feature in MSSQL. We
can define a "row type" in informix, like so:

create row type name_1(fname char(20),lname char(20));

The when we create any table that includes a first and last name, we
do so using this row type like so:

create table sometable(name name_1, some column,...etc)

This allows us to set a standard for certain common fields and avoids
having different developers build the same type of field in more than
one way, different lengths, etc.

Is there a similar function in MSSQL server?

View 1 Replies


ADVERTISEMENT

Is There A User Defined Row Type Ability In MSSQL Server?

Sep 9, 2004

Hi all. We have a mix of informix and mssql server and I want to know if something we do in informix has an analogous feature in MSSQL. We can define a "row type" in informix, like so:

create row type name_1(fname char(20),lname char(20));

The when we create any table that includes a first and last name, we do so using this row type like so:

create table sometable(name name_1, some column,...etc)

This allows us to set a standard for certain common fields and avoids having different developers build the same type of field in more than one way, different lengths, etc.

Is there a similar function in MSSQL server?

View 4 Replies View Related

Help With MSSQL Data Types

Dec 6, 2006

I currently run our company website using MS Access and ASP. I am trying to upgrade the database to MSSQL Server 2000. Unfortunately I'm not finding it that straight forward. I am trying to learn MSSQL but the learning curve is a bit steep.

I have a column in my MSSQL db called tbl_FULL which contains a full description of the product along with html formatting for line breaks, paragraphs and special characters (ie •).

I have set the data type to varchar 1800. All seemed to be well until I tried to enter new data, but unfortunately it only displays the first 50 or so characters.

Can anyone tell me what I am doing wrong?

View 2 Replies View Related

MSSQL: Storage Of Varying Data Types

Jun 2, 2001

I am developing an online web-based address book for multiple users. There are STANDARD FIELDS and CUSTOM FIELDS.

Standard fields include: Name,Street,City,State,Zip.
Custom fields are those defined by a specific user. For example:

User-A Custom fields:
Interest Rate <real>
Loan Amount <currency>
Start date <date>

User-B Custom fields:
Blood type <char 3>
Date of birth <date>
Referred by <varchar 50>

Different users can have different custom fields in their address book. As you can see, while the standard fields for each user can be

stored in a single table. However, I have several methods by which I can store the CUSTOM fields.

------------------------------------------------
Method 1: Create 2 separate tables called CustomField and CustomValue:

CustomField has fields:
FieldID <int>
FieldName <varchar 25>
UserID <int>

CustomValue has fields:
ValueID <int>
Value <varchar 50>
FieldID <int>

------------------------------------------------
Method 2: Create a separate Field and multiple Value tables for each data type:
CustomField, CustomCharValue, CustomIntValue, CustomMoneyValue, etc...

CustomField has fields:
FieldID <int>
FieldName <varchar 25>
FieldType <smallint> (determines which TABLE, below, contains the data)
UserID <int>

CustomCharValue
CharValueID <int>
IntValue <Varchar 50>
FieldID <int>

CustomIntValue
IntValueID <int>
IntValue <int>
FieldID <int>

etc....etc...


The structures of those tables would be similar to Method 1, but the data would be segregated based on their data type.

--------------------------------------------------

I'm thinking that while Method 1 will be easier to implement, Method 2 may offer me better performance if coded correctly. I'm going

to assume that I'll have at least 1-5 million records to work with over the course of my first year and I will need the ability to sort

records based on values in the custom fields as well.

My first question is: Which method should I be considering and is there an alternative or hybrid that I should be considering?

My second question is: What statements should I use in my stored procedure that will enable me to
retrieve a list of USERID, CustomFieldIDs and their values as one resulting table that I can query at will and with solid performance?

Gregory
email: sqlGuy@clubtel.com

View 1 Replies View Related

Desperate! - Need Simple MSSQL Server Monitoring Scripts, Ideas, Etc That Can Be Performed By Operator Types (non-programmers)

Jul 20, 2005

Does anyone have any basic, simple scripts of sp's that I can give mycomputer operators to use to monitor for serious conditions on our sqlservers? We are new in the ms-sql arena, a small shop and we cantreally purchase any tools to monitor these servers, but we need somebasic checks that we can use to make sure a server is performing in areasonable fashion, no blocks, cpu<xx%, memory not pegged, IO working,etc...and we need to be able to do this without bugging ourprogrammers every time we are wondering why things are slowwnig down.We are a split shop between as400s and sql servers, and our operationsstaff has no problem performing this type of duty on the 400s. I thinksince the sql servers are so easy to deploy into production, the ideaof how to monitor these things health got lost in the shuffle andallowing the programmers to do it just serves to destabilize ourenvironment.Please help! Our shop has turned to chaos since we went live withMSSQL centric applications and everytime one programmer "resolves anissue" some other programmers application starts to act up.

View 6 Replies View Related

Sql Types - Simple SQL Server Queries/handling Variable Types

May 26, 2005

SQL Server 2000, ASP.Net 1.1

I've been writing this stuff for a while, and can't seem to come to the
conclusion of how I should be retrieving data and assigning this data
to variables.

Since i'm using SQL Server, I'm convinced that I should be using the
datareaders GetSqlDouble (or whatever) function, but this would mean i
need my local variables to be one of the SQL types.  The problem
with that is, that there will have to be lots of conversions done by me
to be able to use a SQL type in my application.

For instance, I have a class where i'm retrieving dates.  In order
to retrieve them correctly (Null values included), I need to retrieve
them with GetSqlDateTime(), then when it comes time to display the date
in a table, i must first check for nulls, then convert to a
string.  This seems to be very cumbersome.  Would I be better
off just using GetDateTime(), and the .ToString method, and ignoring
Sql Types all together?

so, basically, how are you guys using your sql server data?  with
the supplied sql types, and doing all of the post-processing work
manually?  I feel like i'm having trouble conveying my
issue...hopefully someone knows what i mean....i'd just like some
direction to save trouble in the long run, since i feel like there's
got to be a better way...

Confused!

Thanks,
JJ

View 1 Replies View Related

Ability To Update SQL 7 Through ASP

Sep 13, 2001

Hello,
My problem is this. I did a dts of my old access database to a new sql 7
db. I used Access 97 as the frontened and connected the two databases using linked tables. I made sure all the indexes and such were correct in the SQL database before linking + made sure all the security was corret. When I open a table in the access frontend that is linked to the sql backend, I cannot update or change any data even though I am part of the NT administration group which is given full permissions to the database. Any reason why?

View 2 Replies View Related

Ability To Update SQL 7 Through Access

Sep 13, 2001

Hello,
My problem is this. I did a dts of my old access database to a new sql 7
db. I used Access 97 as the frontened and connected the two databases using linked tables. I made sure all the indexes and such were correct in the SQL database before linking + made sure all the security was corret. When I open a table in the access frontend that is linked to the sql backend, I cannot update or change any data even though I am part of the NT administration group which is given full permissions to the database. Any reason why?

View 1 Replies View Related

Using One SqlDataSource For Many DropDownLists, But Ability To Set Parameters?

Jan 3, 2007

If I have a lot of dropdowns of similar type but only differ in one field, is there a way to set parameters so that the dropdownlist can specify what the parameter values are?
 For example dropdowns: expenseaccount, incomeaccount, assetaccount
SqlDataSource:
SelectCommand="select accountid,accountname from accounts, accounttypest where accounts.accounttypeid=acounttypes.accounttypeid and accounttypes.accounttypename=? order by accountname"
<SelectParameters>
<asp:Parameter Name="accountTypeName" Type="string" />
</SelectParameters>
 -------
I would like expenseaccount to be able to specify "EXPENSE", incomeaccount to specify "INCOME", etc...
 Or is sqldatasource reusage bad practice?
(damn vs2k5 colors)

View 7 Replies View Related

Would You Like The Ability To Hide Columns In The Pipeline?

Jan 17, 2007

Alot of people complain, legitamately, that they wish to remove columns from the SSIS pipeline that they know are not going to be used again. This would help to avoid the "clutter" that can exist when there are alot of columns in the pipeline.

If you are one of those people then click-through below, vote and (most importantly) add a comment. The more people that do that - the more likely we are to get this functionality in a future version.

SSIS: Hide columns in the pipeline
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=252462



-Jamie



View 4 Replies View Related

Suggestions On Where To Jump-start One's Ability In Transact-SQL

Jul 12, 2006

Hi,

Can some participants in this forum provide ideas on suggested training for somebody with a basic knowledge of SQL (started working in this field 6 months ago and still feel like a newbie beginner).

I assume I should focus on getting T-SQL nailed down before delving into the SSIS piece of SQL Server 2005?

Maybe there are a few companies that seem to give good practical knowledge and are located in the greater Los Angeles metro area?

Is it better to get certified through a 1 week or 2 week course via companies like Trainingcamp - does that really give you the practical skills to jump up several levels at one does at work?

Hope I can get some suggestions or direction on where I could best post this question for answers.

Regards,

Patrick

View 4 Replies View Related

Lost Ability To Export After Print Control Install

May 21, 2007

I have a user that says that after she installed the ActiveX control for printing, that she can no longer export the reports. Has this happened to anyone else? What settings do I need to check on her machine to fix this problem?

View 2 Replies View Related

Ability To Use Variables In Package Configurations And Set Their Values At Runtime

Feb 2, 2007

Hi,

My scenario:

I am using a FTP Connection Manager and the configuration setting for it is being set in the package configuration xml file. So the xml file contains the Ftpserver, FTp server username and password. The package is picking up the values from the xml file and is executing successfully. I have to do this because I was not able to provide an expression to the Connection Manager Server Password property.

Now, I want to pick up the ftp details from a database table and set it in the xml file during runtime. Is this possible? OR something like using the

<Configuration ConfiguredType="Property" Path="Package.Connections[FTP Connection Manager].Properties[ServerPassword]" ValueType="Variable"><ConfiguredValue>@[user::FtpPassword]</ConfiguredValue></Configuration>

Kindly look at the items in bold. Is this possible? Then I can set the value of the variable in the package before the FTP connection manager task is executed.

Thanks for all the help.

 

$wapnil

 

 

View 10 Replies View Related

SQL Server Express Ability To Open Sybase File

Jun 13, 2006

Hello:

Can SQL Server Express open/read a .db database file that is Sybase?

View 1 Replies View Related

Sql Server Express Loses Ability To Write To Database

Jun 17, 2006

After a period of use (typically 1-2 hours), an application using SQL Server Express stops writing to the database, whilst still continuing to read from it. As far as I can tell, no error message is generated by any operation. The application is written in VB Express and is executed on several machines, with SQL Server Express on a central machine. Re-starting the application on the affected machine restores normal operation.

The problem on one machine does not cause a similar problem elsewhere at the same time (hence it appears to be an issue with the relationship between the application and SQL Server)

This does not appear to relate to the response to Q asked by sgcook on 9 may as we are not using user instances, but only a bare minimum connection string.

View 15 Replies View Related

Replication :: Ability To Use Query Hint In Transactional Filtered Articles

Jul 22, 2015

I am setting up filtered replication(tran repl) for a table that is 2+ TB in size. The filtered rows are less than 1% of total size.When I run the snapshot, sometimes it complete in 27 mins(I have already optimized the snapshot agent profile) and sometimes it runs for ever. Mostly it runs forever, so the chances of it finishing soon are really very less.I have infact created filtered index just to cater to the query in the filter i.e. on the filter predicates. The reason its flipping flopping is due to the incorrect plan that it chooses. I can force the right index and the filtered query runs just fine but. I cannot use the query hints when I am adding the filter in replication as its not allowed.

I then went ahead and created the plan guides to force the best plan. But the plan guide was not used because even if there is a single space in the actual query that replication runs in the background the plan guide will not be used(testing on plan guides has proved this). The query needs to be ditto same including whitespaces and carriage returns. I then ran profiler and found that there is a view that replication creates and executes to extract the filtered rows and BCP it. Got the text of the view and created the plan guide on it but it was still not used because sp_helptext output seems to be different than what is actually being fired by replication internally.

Further, I collected the verbose log with level 2 and tried to find out if the main query shows up there with no success.

(a) I can't use the plan guide because I do not know the exact syntax(I mean I have the query but I do not know the white spaces, carriage returns, etc) and

(b) I cannot use query hint while filtering the article.

1. I cannot afford to have downtime.
2. I cannot use backup and restore option either.

View 5 Replies View Related

Lost Ability To Debug CLR Stored Procs After SQL 2005 Upgrade To SP1

May 4, 2006

OS: Windows XP Pro, SP2
SQL: SQL Server 2005 (Server 9.0.2047) SP1
VS: Visual Studio 2005 RTM.050727-4200
--------

After upgrading our SQL Servers to SP1 we have lost the ability to debug .net CLR stored procedures. The debugging process worked fine until we installed the Service Pack. All other features we use test out fine after the upgrade. The only and very important feature that does not work is debugging CLR procs. In fact this problem is happening in every environment we have. Be it SQL2005 running on Windows XP SP2, Windows Server 2003 SP1, Window Server 2000 SP4. SQL Server remote or local on the machine doing the debugging. All 5 developer workstations with Visual Studio in virtual machines or installed on in base OS are behaving exactly the same. Every developer on our team has the exact same problem regardless of their configuration after the upgrade to the service pack 1. All developers and environments were working correctly before the upgrade.

Even building a new SQL box from scratch and installing all tools and creating a new clean database we fail with same error.

We have verified CLR Debugging is enabled for the database connection. We have dropped the connection and recreated it.

Here is an example of the output from the output window:

This is the output up to the CLR call

The thread 'sql_test2 [62]' (0x68c) has exited with code 0 (0x0).
The thread 'sql_test2 [62]' (0x68c) has exited with code 0 (0x0).
Auto-attach to process '[1072] [SQL] sql_test2' on machine 'sql_test2' succeeded.

Output AFTER the CLR Call

Auto-attach to process '[1072] sqlservr.exe' on machine 'sql_test2' succeeded.

'sqlservr.exe' (Managed): Loaded 'C:WINNTassemblyGAC_32mscorlib2.0.0.0__b77a5c561934e089mscorlib.dll', No symbols loaded.

'sqlservr.exe' (Managed): Loaded 'C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBinnSqlAccess.dll', No symbols loaded.

'sqlservr.exe' (Managed): Loaded 'C:WINNTassemblyGAC_32System.Data2.0.0.0__b77a5c561934e089System.Data.dll', No symbols loaded.

'sqlservr.exe' (Managed): Loaded 'C:WINNTassemblyGAC_MSILSystem2.0.0.0__b77a5c561934e089System.dll', No symbols loaded.

'sqlservr.exe' (Managed): Loaded 'C:WINNTassemblyGAC_32System.Transactions2.0.0.0__b77a5c561934e089System.Transactions.dll', No symbols loaded.

'sqlservr.exe' (Managed): Loaded 'C:WINNTassemblyGAC_MSILSystem.Security2.0.0.0__b03f5f7f11d50a3aSystem.Security.dll', No symbols loaded.

'sqlservr.exe' (Managed): Loaded 'C:WINNTassemblyGAC_MSILSystem.Xml2.0.0.0__b77a5c561934e089System.Xml.dll', No symbols loaded.

'sqlservr.exe' (Managed): Loaded 'CIS_Release6a', No symbols loaded.

'sqlservr.exe' (Managed): Loaded 'C:WINNTassemblyGAC_MSILMicrosoft.VisualBasic8.0.0.0__b03f5f7f11d50a3aMicrosoft.VisualBasic.dll', No symbols loaded.

'sqlservr.exe' (Managed): Loaded 'C:WINNTassemblyGAC_MSILSystem.Configuration2.0.0.0__b03f5f7f11d50a3aSystem.Configuration.dll', No symbols loaded.

Program Locks up with blank screen and hangs. If we stop the Debugging then the correct results will appear in the output window.

Column1

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

<CIS><Header><MessageType>FosterParent</MessageType><Status>-1</Status><ErrorMsg><Code>-100</Code><Description>Requested Record Not Available</Description></ErrorMsg></Header></CIS>

No rows affected.

(1 row(s) returned)

@RETURN_VALUE =

Finished running [dbo].[qsp_FF_FD_FP_FosterParent].


Any ideas? TIA




View 1 Replies View Related

Custom Code/function To Format Seconds To Hh:mm:ss With Ability To Go Over 24 Hours

Aug 4, 2007



Hello,

I am trying to get this to work - but it only returns minutes & seconds:

Function Seconds2mmss(ByVal seconds As Integer) As String
Dim ss As Integer = seconds Mod 60
Dim mm As Integer = (seconds - ss) / 60
Seconds2mmss = String.Format("{0:0}:{1:00}", mm, ss)
End Function

Can anyone help me out? I am not that familiar with VB.

Thanks,
Deb

View 2 Replies View Related

SQL 2012 :: Ability To View Server Level Logins And Agent Jobs?

Sep 16, 2014

In one of my environments, I need to grant the ability to view all the logins and agent jobs to an account, but I don't want to give him "sysadmin" or "securityadmin".

View 2 Replies View Related

One Or More Columns Do Not Have Supported Data Types, Or Their Data Types Do Not Match.

Oct 20, 2007



Hi,

I´m exporting an ms-excel file, then I use a lookup transformation to get a field from a SQL Server 2005 table. The Lookup transformation editor, after selecting the table, shows a warning that says:

at least one mapping between a column from available input columns ans a column from available lookup columns must be defined on the columns page.

So I try to make a relationship in the Lookup transformation editor's column tab where I find the Available input columns and the available lookup columns but I get the following error:

The following columns cannot be mapped:
[Department, DEP_CLEGALCODE]
One or more columns do not have supported data types, or their data types do not match.

The field in SLQ Server is varchar(10) and the input field is a derived column transformation; I have tried different Data Types but I always have the same error.

The DataFlow is: ExcelSource --> Derived Column --> Lookup --> Flat file destination

thanks.

View 6 Replies View Related

FEATURE REQUEST: Ability To Disable Clickthrough Reports (Live Links) At The Report And Export Function Level

Jul 19, 2007

Please consider enhancing SQL 2005 Reporting Services to provide the ability to disable Clickthrough Reports (Live Links) at the Report and Export Function level. Today you can only enable or disable Clickthrough Reports at the View/Model Level for all Report Builder reports.

View 1 Replies View Related

How Many Result-rows Does Mssql Return Should Be Used Asynchronous Method To Use Mssql Cursor?

Aug 11, 2004

How many result-rows does mssql return should be used asynchronous method to use mssql cursor, can get the best performance in any time in any result offset?

i want to make the cursor fast in any time whatever how many results returned

View 2 Replies View Related

Database Migration Plan - (mssql/msde To -&> Pgsql/mssql)

Feb 10, 2008

Hi,

i was planning to create a database migration tool ..
its a certain database of a DMS (document management system) to
another DMS (two different DMS)... from DMS using msde 2000 server .. and tranfer to a DMS using a postgre sql or mssql .. depends ..

they have different table structures and names . . :D

i was thing of what language shall i use.. or what language is the best to work on this kind of project :)

hoping for your kind help guys. thanks :)



br
Frozenice

View 1 Replies View Related

Server Configuration For MSSQL 2000 And MSSQL 2005

Sep 6, 2006

Does enabling/disabling Data Execution Prevention have a performanceimpact on SQL 2000 or SQL 2005?For SQL best performance - how should I configure for:Processor Scheduling:Programs or Background servicesMemory Usage:Programs or System Cache

View 9 Replies View Related

Creating Index In MSSQL 2000 From MSSQL 2005

Mar 24, 2008

Hi,

I am a bit new to the MSSQL server. In our application, we use so many SQL queries. To imporve the performance, we used the Database enigine Tuning tool to create the indexes. The older version of the application supports MSSQL 2000 also. To re-create these new indexes, I have an issue in running these "CREATE INDEX" commands as the statements generated for index creation are done in MSSQL 2005. The statements include "INCLUDES" keyword which is supported in MSSQL 2005 but not in MSSQL 2000.

Ex:-

CREATE INDEX IND_001_PPM_PA ON PPM_PROCESS_ACTIVITY

(ACTIVITY_NAME ASC, PROCESS_NAME ASC, START_TIME ASC, ISMONITORED ASC)

INCLUDE

(INSTANCE_ID, ACTIVITY_TYPE, STATUS, END_TIME, ORGANIZATION);


Any help in creating such indexes in 2000 version is welcome.

Thanks,
Suresh.

View 2 Replies View Related

Generating Script For MSSQL 2000 From MSSQl 2005?

May 3, 2008

Hello
We are using SQL 2005 and now we are planning to use SQL 2000. what are the ways to do the process.

We taken the script spcificall for 2000 and run it in SQL 200. But we are getting the error in SCRIPT?

Could you please give me the step to do?

Thanks,
Sankar R

View 6 Replies View Related

Lost Ability To Manage SQL Server At Server Explorer In Visual Studio .NET

Dec 27, 2004

Good day all,

I am no longer able to manage SQL Server at the server explorer in Visual studio. i.e. create, modify and delete for Diagrams, Tables, Views, Stored Procs & Functions. Further more, it missing Diagram and function folder. I can only view or read data.

But I can access local MSDE as usual.

Whats I have done wrong? Please Help. Thanks.

View 2 Replies View Related

Reporting Services :: SSRS Loses Ability To Delegate User Credentials To Database Time To Time?

Apr 30, 2015

we have problems with our SQL Reporting Service 2012 (SSRS) server . We have setup Kerberos delegation between SSRS and the database server (SQL Server Always-on cluster) so users are authenticated down to the database. The issue occurs from time to time that SSRS loses the ability to delegate the user credentials to the database. At this point in time the Report Server logs contain rejected database connections because of ANONYMOUS logon. After restarting SSRS the problem is gone.

View 2 Replies View Related

Run Mssql 6.5 Client And Mssql 7.0 Server On Same Nt Workstaton?

Oct 26, 1999

Hello:

I am currently work on mssql 6.5. On my workstation, I have mssql 6.5
cient software.

However, I would like to install mssql 7.0 server on my nt workstation
and work with it to become familiar with 7.0. Can I install mssql 7.0
server on my nt workstation? Can mssql 6.5 client coexist with mssql 7.0
on the same machine if they are in different directories?

Thanks.

David Spaisman

View 1 Replies View Related

MSSQL Express 2005 Vs. MSSQL 2000

Jun 15, 2006

Ben writes "I have a sql script that doesn't function very well when it's executed on a SQL 2000 server.

The scrpt looks like this:


---------------------------------------------------------------------------------------------------
USE [master]
GO
IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname = N'SSDBUSERNAME')
EXEC sp_addlogin N'SSDBUSERNAME', N'SSDBPASSWORD'
GO
GRANT ADMINISTER BULK OPERATIONS TO [SSDBUSERNAME]
GO
GRANT AUTHENTICATE SERVER TO [SSDBUSERNAME]
GO
GRANT CONNECT SQL TO [SSDBUSERNAME]
GO
GRANT CONTROL SERVER TO [SSDBUSERNAME]
GO
GRANT CREATE ANY DATABASE TO [SSDBUSERNAME]
GO
USE [master]
GO
If EXISTS (Select * FROM master.dbo.syslogins WHERE loginname = N'SSDBUSERNAME')
ALTER LOGIN [SSDBUSERNAME] WITH PASSWORD=N'SSDBPASSWORD'
GO
GRANT ADMINISTER BULK OPERATIONS TO [SSDBUSERNAME]
GO
GRANT AUTHENTICATE SERVER TO [SSDBUSERNAME]
GO
GRANT CONNECT SQL TO [SSDBUSERNAME]
GO
GRANT CONTROL SERVER TO [SSDBUSERNAME]
GO
GRANT CREATE ANY DATABASE TO [SSDBUSERNAME]
GO
USE [master]
GO
IF EXISTS (select * from dbo.sysdatabases where name = 'ISIZ')
DROP DATABASE [ISIZ]
GO
USE [SurveyData]
GO
exec sp_adduser 'SSDBUSERNAME'
GRANT INSERT, UPDATE, SELECT, DELETE
TO SSDBUSERNAME
GO
USE [SurveyManagement]
GO
exec sp_adduser 'SSDBUSERNAME'
GRANT INSERT, UPDATE, SELECT, DELETE
TO SSDBUSERNAME
---------------------------------------------------------------


I need to be converted to a script that can be executed on both MSSQL 2000 and MSSQL 2005.

I was wondering if somebody there could help me with this problem?!

Thanks,
Ben"

View 1 Replies View Related

Migration MSSQL 2000 32-bit To MSSQL 2005 64-bit

Nov 17, 2007

I've been tasked to move our production databases on MSSQL 2000 to 2005. I've supported MSSQL since version 6.5 and performed migrations to successor versions.

Current Environment is MSSQL 2000 32-bit with current Service Packs.

I've performed mock migrations on Test servers upgrading all Production instances simultaneously from MSSQL 2000 to 2005 32-bit. The Test environment is identical to Production minus server name, IP etc. Also I have a separate server with MSSQL 2005 installed where I use the DETACH / ATTACH and BACKUP / RESTORE method for migration / acceptance testing. There are approximately 30 databases totaling 70 GB. This has gone as expected and fairly successful. Vendors have been coordinated with to update code and staff for acceptance testing.

I'd prefer going directly to MSSQL 2005 64-bit instead if possible due to memory benefits etc. This is where I'd like some feedback prior to borrowing a 64-bit server for testing.

Upgrade options:

1. Is it better to migrate from MSSQL 2000 32-bit to 2005 64-bit via:
a. DETACH / ATTACH
b. BACKUP / RESTORE
c. Is one method more advantageous relating to the end result?
2. Regarding XP clients, have issues been experienced with the default SQL Server driver or is an alternate recommended for XP clients to connect to a MSSQL 64-bit server databases?
3. If you have performed this migration and have relevant experience please pass them along.

View 3 Replies View Related

Delay Between CREATE DATABASE And Ability To Connect To That Database

Oct 9, 2006

I have an application that creates a new database during installation,and I've noticed some strange behavior. Once I've created thedatabase, I am able to immediately create tables and populate lookupdata, provided I remain connected to the server. If, however, Idisconnect and attempt to reconnect immediately, I'll get an errorsaying that my login is invalid for the new database.I can get around this by having my code simply wait 5 seconds beforeattempting to reconnect, but I'm curious to see if anybody here cangive an explaination for why this is happening. Here is a bit ofpseudo code to explain what I'm seeing:open new connectioncreate databasecreate tablespopulate tablesclose connection// open new connection /* can't do this yet, as it would break */for (int a=0; a<5; a++){Thread.Sleep(2000)try{open new connectionbreak;}catch{Debug("still waiting...");}}Running my version of this code, I'll see that "still waiting..."message go past 2-3 times before SQL Server wakes up and realizes thatI'm allowed to connect to it. Anybody know why?Thanks,Jason KesterExpat Software Consulting Serviceshttp://www.expatsoftware.com/---Get your own Travel Blog, with itinerary maps and photos!http://www.blogabond.com/

View 1 Replies View Related

Deploying MSSQL 2005 Express DB To MSSQL 2005 WKGP Errors

Sep 29, 2006

DB is developed on local computer with MSSQL 2005 Express. My host is on MSSQL 2005 workgroup. Are they compatible, because I am getting errors? Is my approach wrong?

I have tried several approaches.

A) I created a backup of database on my local, then placed a copy on the server. Then I tried to restore through Server Management Studio. I get this error.

TITLE: Microsoft SQL Server Management Studio

------------------------------

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

ADDITIONAL INFORMATION:

The backed-up database has on-disk structure version 611. The server supports version 539 and cannot restore or upgrade this database.

RESTORE FILELIST is terminating abnormally. (Microsoft SQL Server, Error: 3169)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=08.00.2039&EvtSrc=MSSQLServer&EvtID=3169&LinkId=20476

------------------------------

BUTTONS:

OK

------------------------------



B: I also have tried copying the database. I put it in the same path as the other databases that can be read with server management studio on the server. Then, tried to get to it through server managements studio and it did not appear. So I tried to attach it. Then I received this error:

TITLE: Microsoft SQL Server Management Studio

------------------------------

Attach database failed for Server 'MROACH1'. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476

------------------------------

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

Could not find row in sysindexes for database ID 10, object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexes.

Could not open new database 'LodgingDB'. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 602)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=602&LinkId=20476

------------------------------

BUTTONS:

OK

------------------------------

C: I have also tried opening the Database, and back up file through Server Management Studio. without success.

D: I also tried Windows and Software update at microsoft update, but no updates were recommended for Version on Server.

I'm surprised this is so hard. My original data base was created in same family of software. 2005 MS SQL Express. I could use some direct help from someone experienced with this. Am I doing it wrong or are the DB versions incompatible.

Mark Roach





View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved