Connection String Differences In SQL 2005

Apr 15, 2008

Hiya,

I'm a SQL n000b, and have just joined the forum 5 minutes ago seeking an answer to this problem that I've encountered. It seems that the way connection strings are handled in SQL 2005 have changed. This connection string used to work, but is now borked:

<add key="DBNAME.ConnectionString" value="packet size=4096;integrated security=SSPI;data source='sqlvirdev0035dev35';persist security info=False;initial catalog=DBNAME;connection timeout=60" />

Has something explicitly changed in SQL 2005 that would break this <add key>? Does something need to be set server side in order to handle these connection strings now? I have no idea why this won't work anymore, I just know that it doesn't work.

Ty for any help,
~Chipley

View 4 Replies


ADVERTISEMENT

SQL Server 2005 Connection String In Visual Studio 2005

Nov 14, 2006

Hey
I need to know what shall i specify in this connection string in visual studio web.confug file inorder to connect to ma registered server (locally) not directly to my dedicated server ....cuz i am facing network connection problems ....
b4 i used to use this connection string :
<add key="conn" value="data source=IP addresseel,1433;initial catalog=xxx;user id=username on the dedicated server;Password=password on the dedicated server;timeout=60"></add>
So how can i change it ???? to be able to connect locally ????
Please Help ???!
Thanks in advance
Hiba

View 13 Replies View Related

Connection String To Sql 2005

Mar 21, 2007

Dear,
I am trying to connect from a vb.net application (VS 2005) to a sql server 2005 database located remotely,what is the connection string that I should use?I used the same one that I was using for connecting to sql 2000 but it is not working.Thanks.

View 3 Replies View Related

Help! On Connection String To SQL 2005

May 24, 2007

anybody here who can tell me of what is the syntax or sample connection string to SQL 2005 server from MSAccess 2000,


Thanks
Ben

View 1 Replies View Related

Connection String For VBEE To SQL 2005

Dec 1, 2006

I have SQL 2005 and I wanted to write a little program in Visual Basic 2005 Express Edition (VBEE) to carry out some processes. While away I built my user form, but when I got back I found that VBEE refuses to connect to SQL Server 2005 and is only configured to link to SQL Express.

The data source is set to .SQLEXPRESS in the advanced properties of the Add Connection form in the Data Source Configuration Wizard and you can not change it.

I read in MSDN ID 329721 that you can create a data layer that will allow you to use the wizards, but that is as far as it went and is above my programming skills.

So my only options are use VB.Net 2003 Standard edition, use connected mode or give up and it was only supposed to be a quick fix. Had anyone else had this same issue and developed a work around?

View 7 Replies View Related

SSAS 2005 Connection String

Mar 20, 2007

i am trying to connect to ssas 2005 -- i can get to it through management studio no problem. i cant seem to get my program to connect

can anyone see anything wrong with this connection string

Provider=MSOLAP;User ID=myid;Password=mypass;Data Source=mysrvr;Initial Catalog=mycat;Client Cache Size=25;Auto Synch Period=10000;

View 2 Replies View Related

SQL EXPRESS 2005 Connection String

Dec 25, 2006

I try to use this conn string but it is not working I think the problem is with the provider
DataBaseSource = "Provider=SQLOLEDB; Password=sa2006; User ID=sa;Initial Catalog=coupon; Data Source=YAELSQLEXPRESS;"
I get error message
Microsoft OLE DB Provider for SQL Server (0x80004005)
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.

do you know which provider can I use?
or maybe it is a sacurity problem

Yael

View 1 Replies View Related

Microsoft SQL Server 2005, Connection String

Jul 27, 2006

<connectionStrings>
<add name="AppConnectionString1" connectionString="Data Source=&quot;.SQLEXPRESS&quot;;AttachDbFilename=&quot;C:Program FilesMicrosoft SQL ServerMSSQLDataAdventureWorks2000_Data.mdf&quot;;Integrated Security=True"
providerName="System.Data.SqlClient" />
</connectionStrings>
I'm running Microsoft SQL server 2005. what is the error in the above code.

View 2 Replies View Related

SQL Server 2005 Enterprise Connection String.

Jan 2, 2007

Hi,
I have just uninstalled SQLEXPRESS and installed the 180 day trial SQL server 2005 enterprise edition.
I have since started to have problems with my ASP.Net Configuration Tool., can not connect to the database.
I think that the connection string in my machine.config file is incorrect, before installing I had,..:
datasource=./SQLEXPRESS,....
So i decided to change that part to the following. Would anybody know what I have put in the datasource part for the SQL server 2005 enterprise edition? At the moment I tried SQLSERVER2005 but that is incorrect,..
<add name="LocalSqlServer" connectionString="data source=./SQLSERVER2005;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true" providerName="System.Data.SqlClient" />
 

View 1 Replies View Related

Connection String For Sql Server 2005 Express

Jan 10, 2008

does anyone kbnow the connection string used to connect to the database in sql server 2005 express?
can someone one give me sample codes for it? thanks.


thanks

View 1 Replies View Related

Connection String Question - SQL Server 2005

Mar 12, 2007

What is the impact of the differences in the following 2 connection strings:

Initial Catalog=xxx;Data Source=xxxxxx;Trusted_Connection=True;

Driver={SQL Server};Server=xxxxx;database=xxx;Trusted_Connection=yes;

To outline the differences I'm concerned with are


Missing Driver on first string

Difference in Trusted_Connection parameter (true vs. yes)

Initial Catalog vs. database parameter

View 6 Replies View Related

SqlExpress Instance In Connection String Instead Of Full Sql 2005

Sep 27, 2006

Hello, I had started my project with the express edition, then I switched to the pro one. The problem is that in my webconfig I still have  .SQLEXPRESS and I don't know how to modify that to use the full version of Sql server. <add name="MyConnectionString" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|MyDataBase.mdf;Integrated Security=True;User Instance=True"providerName="System.Data.SqlClient" /> When I look at the surface area manager I can see that I have 2 instances running : MSSQLSERVER and SQLEXPRESS.When starting Sql Server Management Studio I can connect to 2 servers: NEWDELL and NEWDELLSQLEXPRESS.And when I try to replace SQLEXPRESS by MSSQLSERVER in the connectionstring, then it doesn't connect.I remember having lost connection already when playing with all these tools, so I pay attention now ! That's why any help would be really appreciated ! Thank you. 

View 5 Replies View Related

Connection String To SQL Server 2005 Express Database

Apr 10, 2008

Hi, all. I am using VS.NET 2005 and SQL Server 2005 Express. I tried to use the following code to connect to a database named "AMSPROD" that I attached to SQL Server 2005 Express:
 dbConn = new OleDbConnection(Data Source=.SQLEXPRESS;AttachDbFilename="C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataAMSPROD_Data.MDF";Integrated Security=True;Connect Timeout=30;User Instance=True);
dbConn.Open();
I got an error asking for provider with the above connection string, then I changed the conncection string to be like follows:
Data Source=.SQLEXPRESS;AttachDbFilename="C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDataAMSPROD_Data.MDF";Provider=SQLNCLI;Integrated Security=True;Connect Timeout=30;User Instance=True
I got error: No error message available, result code: DB_E_ERRORSOCCURRED(0x80040E21)
Then I changed the Provider to be like: Provider=SQLOLEDB, now I am getting the following error:
Message: "Invalid authorization specification
Invalid connection string attribute"
Source: "Microsoft SQL Native Client"
StackTrace: " at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)
at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.OleDb.OleDbConnection.Open()
at ISESAMSData.DbConnections.MakeConnection() in C:\ISES\ISESCommon\ISESAMSData\DbConnections.cs:line 74"
TargetSite: {Void .ctor(System.Data.OleDb.OleDbConnectionString, System.Data.OleDb.OleDbConnection)}
Strange thing is, when I use the Connect To Database under Tool menu of VS.NET 2005, I could make the connection to this database. Then I copied this connection string to the code, the connection couln't be made. I feel very confused.
I am wondering if I need to add ASP.NET user to SQLExpress. But I don't know how to do it?
Can anyone please help me out! Many thanks!

View 7 Replies View Related

SQL Server 2005 Express Edition Connection String (dns-less)

Jun 12, 2006

Junifer writes "Hello guys,

Can you give me a simple example of a dns-less connection to SQL Server 2005 Express Edition?



Thanks."

View 1 Replies View Related

Connection String For Classic ASP Global.asa To SQL Server 2005

Dec 1, 2005

I migrated my SQL Server 7 database to the new SQL Server 2005 that I installed on my PC.  I have classic ASP programs on my PC that used to access the SQL Server 7 database.  However, global.asa and these ASP programs can no longer connect to the new SS 2005 database.

View 9 Replies View Related

SQL Express 2005 Connection String Vs SQL Server (latest)

Oct 28, 2007

I'm a student at Boise State university and we're using SQL Express as part of one of our projects for ASP.NET programming. I liked it and I'm trying to use it to power a wiki that I'll be hosting from my home internet connection. The wiki is compatible with MSSQL server, but it won't connect to SQL Express.

I suspect that the problem is in the connection string, mainly because the apps we're developing for class use a connection string like:
<add name="AspNetForumConnectionString" connectionString="Data Source=localhostsqlexpress;Initial Catalog=gaming.forum.nxnw;User ID=*********;Password=*********;" providerName="System.Data.SqlClient"/>

and note the Data Source. The wiki I'm using is PHP based and uses ADObd to connect databases and has a driver for MSSQL, but not SQL Express (no surprise, really). I know just enough php to be dangerous to code and I've tried modifying the adodb-mssql.inc.php, but I can't seem to make the right change.

So, all that said, my question is: Is there a way to change what SQL Express 2005 will accept as a connection string? I've been looking, but I'm not even sure what the setting would be called.

Thanks,
Les

View 6 Replies View Related

A Simple Question On The Connection String Format On Sql Server 2005!

Feb 22, 2006

Hi everyone!
I am new to visual studio 2005 and sql server 2005.
Up to now i was using visual studio 2003 and ms access.

When i wanted to create  a web application that communicates with
an access database,  i just had to take the *.mdb file and put it
into a specific folder inside my application's folder and then use
Server.MapPath on the connection string (so it can run on any pc).

Now, i want to do the same with sql server 2005 and visual studio 2005.
I use the connection string: "Server=MYSERVER;Initial
Catalog=test_db;Integrated Security=SSPI".

My problems are:
1) Can i just take the *.mdf file put it inside the App_Data folder like i did on access?
2) What should be the connection string so that my application can run on any pc (with sql server 2005)?

Thanx in advance guys!

View 1 Replies View Related

Do I Need To Change Connection String If I Upgrage SQL Server From 2000 To 2005?

Apr 5, 2006

Do I need to change connection string if I upgrage SQL server from 2000 to 2005 in ASP.NET 2.0?

View 1 Replies View Related

How To Set Connection String Value By Using SET Switch Of Dtexec Sql Server 2005 Command

May 2, 2007

hi

I need to load a text file into sql server table using SSIS package.

the idea is to load this file with the help of dtexec command by giving the file path and name in the SET switch of dtexec command.



anyone having an idea; would be of great help.

Regards,

Salman Shehbaz.

View 4 Replies View Related

Help With Connection String After Clickonce Install From Visual Studio 2005

Apr 7, 2008

I have a sql express .mdf that is located in c:appdirectorymydb.mdf whilst developing an application. During development my connection string uses the above path to connect to the db. e.g.

"server = .sqlexpress; Integrated Security=True; database=C:appdirectorymydb.mdf"

I have published the app using click once in visual studio.net which places the db in : C:Documents and Settingsuser.nameLocal SettingsApps2.0DataJN1WL1DJ.X7B ... 08ed2810aab2abData


When i am executing the application following a clickonce install, what should I use in my connection string for the path to the db ? or where can I find the above path (e.g. in the registry) so that I can provide it to the connection string ?


Alternatively, can I specify where the DB is going to be installed (via click once). e.g. in my app directory under program files ?

I have tried searching what seems like everywhere for a solution to this problem and as a last resort thought it worth posting the question.

Many thanks in advance.

Simon

View 1 Replies View Related

CONNECTION STRING TO A REMOTE SERVER WITH SQL EXPRESS 2005, FIXED IP ADDRESS

Jan 28, 2008



I'M HAVING AN ISSUE, UNDERSTANDING, THE CONNECTION STRING.
I WANT TO CONNECT TO AN INSTANCE OF SQLEXPRESS ON A REMOTE SERVER WITH A FIXED IP ADDRESS
THE TCP PORT IS OPEN TO 1433
I OPENED THE PORT ON THE ROUTER AND THE WINDOWS 2003 FIREWALL

MY CODE IS AS FOLLOWS:
S = "Provider=SQLNCLI;"
S = S & "DATA SOURCE=44.66.777.888,1433SQLEXPRESS;"
S = S & "INITIAL CATALOG=TESTDB;"
S = S & "Persist Security Info=false;"
S = S & "UID=TEST999;"
S = S & "Pwd="TEST999888;"
CnMgt.ConnectionString = S
CnMgt.Open S

I'VE FOLLOWED THE STEPS OUTLINED IN ARTICLE 914277

CAN SOMEONE HELP?
THANK YOU


PS. IF THE CLIENT IS LOCAL, I HAVE NO ISSUE OPENING THE DATABASE.
I DO NEED TO OPEN THE DB FROM FROM CLIENTS.

View 5 Replies View Related

Making SQL 2005 Developer Accept The Connection String For SQL Express Edition

Feb 18, 2007

Hi, I have installed on my laptop SQL server 2005 Developer edition, I can change ANY of the settings of the SQL server and OS.

I am writing code with a team using continuous integration where the connection string is the following:
"Provider=SQLNCLI;Server=.SQLEXPRESS;Database=Secret;Trusted_Connection=Yes;"

is there a way (create an alias,rename server, other?) to make my local dev SQL server accept the connection string above, as at the moment I have to rename the connection string every time before checking in code and after downloading the latest version from the code repository.

any advice or tips will be greatly appreciated

Thanks

JW.

View 1 Replies View Related

Cannot Set Connection Property Of Backup Database Task If Connection String Is Customized In Connection Object

Aug 23, 2006

I added a connection (ADO.NET) object by name testCon in the connection manager - I wanted to programmatically supply the connection string. So I used the "Expressions" property of the connection object and set the connectionstring to one DTS variable. The idea is to supply the connection string value to the variable - so that the connection object uses my connection string.

Then I added a "Backup Database Task" to my package with the name BkpTask. Now whenever I try to set the connection property of BkpTask to the testCon connection object, by typing testCon, it automatically gets cleared. I am not able to set the connection value.

Then after spending several hours I found that this is because I have customized the connection string in testCon. If I don't customize the connection string, I am able to enter the "testCon" value in the connection property of the BkpTask.

Is this an intrinsic issue?

View 2 Replies View Related

Differences Between SQL 2005 Thats Comes With Vs2005 And SQL 2005 Standalone ??

Nov 2, 2005

I just want to run a webiste off my home computer with a dynamic address.It seems I can do all I want with the SQL that comes with vs2005 except being able to backup the DBIs the SQL 2005 that comes with vs2005 good enoh for everything I should want to do?? Is there some chart somewhere that explains the differences ?

View 4 Replies View Related

Named SQL 2005 Instance Multiple Active Recordsets-C# Stored Procedure-Connection String

May 7, 2008

I am working on a C# stored procedure for SQL 2005, and i've uncovered a couple questions.

First a description of the procedure:

I have a series of equations taking place to calculate a score based on activities in which the user participated in, that will give them an over all grade or rating. The calculations currently take place in the database, and I am moving this from T-SQL to C# CRL.

1. In order to connect the stored procedure to the database I use a SqlConnection and a SqlCommand to execute either dynamic sql or a stored procedure to return data to a data reader. Is there an easier way to connect to the database? In SSMS if i open up the query it knows what database i am connected to. Do I have to make a sql connection in C# stored Procedures?

2. I have multiple functions within the main C# Stored Procedure that I'm working on. This ends up requiring Multiple Active Recordsets. I must set this withing the connection string. Seeing as I'm using a named instance of SQL 2005, I now must put the userid, password, and server name into the code. Is there a more secure way to connect to SQL Server in a C# Stored procedure that allows MARS?

3. I encryped the connection string, and put it into the assembly, I wrote a decryption class, and in the procedure itself everytime I need to refrence the connection string, I call it, decrypt it, and pass it along. But my code to decrypt the connection string is in the compiled DLL, if the server was ever compromised the encrypted connection string and the key to decrypt it are sitting in the DLL. Is there a config file that I can use for C# Stored Procedures?

4. If I have to keep the connection string in the file, then I need to change that per environment. Example I have 3 test environments before production. So I would need to change the connection string for each file. That may be fine for one procedure, but what if I have 20, that will quickly get of hand?

5. Along the security lines, can the assembly for a C# Stored Procedure be called from outside the assembly? From a command prompt, or by a maliceous program? Or could it be called directly by a .NET application instead of going through a T-SQL Stored Procedure that is using
WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [PROJECTNAME].[CLASSNAME].[METHODNAME]


Thanks

View 1 Replies View Related

Differences Between SQL Server 2005 X64 Vs X86?

Mar 12, 2007

Hi there,

I'm trying to find what are the main differences between SQL Server 2005 64-bits and 32-bits. So far, I've found some articles about TPC-C performance but I would like to know some response time or execution time of a batch or SSIS packages.

Any information about this 2 versions is appreciated.

Thanks!

View 3 Replies View Related

Differences Between MS SQL Server 2000 And 2005?

Jan 20, 2006

Firslty, my aplogies if this is documented elsewhere - I am a new user to SQL Team and not yet found everything! I hope someone here can help me...

In brief, I am making the foolish mistake of embarking on my third and final year of an MS(c) degree in forensic computing. For my final year project I am intending to study and document (for forensic computing purposes) the forensic capture and investigation of data from a MS SQL Server database.

However, my experience is mostly from MySQL! In other words, I know very little about the internal structure of MS SQL Server 2000 or 2005.

Which leads me to my question....

Can anyone point me in the direction of a technical pagedocumentpdf (whatever) that details what the core fundamental differences are between 2000 and 2005. I'm not talking about an MS publicity paper - no, I need a non-bias technical guide which states the differences as fact.

If the differences in 2005 are mostly just cosemtic (the GUI etc) then I'll study 2000 because lots is already known and documented about it it seems. However, if it's much more than that and the differences are specific to what I'm studying (the forensic capture) then I'll probably have to go with 2005 because that's what we will be encountering more of in the next few years and the differences will effect the investigator.

Your time and responses much appreciated.

Regards

Ted

(BTW - Having looked at the description of 'Inside Microsoft SQL Server 2000', it seems like it might be a good book for my project (if I do 2000). Would you guys agree?)



www.f3.org.uk

View 5 Replies View Related

Differences Betwee 2000 And 2005 SQL

Jul 9, 2007

Hi,

I have a simple sql statement that used to work in SQL 2000 that isn't working in SQL 2005. The order by clause doesn't seem to have any effect on the result set. The sql statement is:



ALTER VIEW dbo.SELECT_PP_END
AS
SELECT TOP 100 PERCENT

PP_PERIOD_ID,

CONVERT(VARCHAR, PP_END_DATE, 101) AS PP
FROM dbo.PP_PERIODS
ORDER BY PP_END_DATE DESC



The period end date is appearing in ascinding order on sql server 2005 and in the correct order in sql 2000. Any idea? Thank you for your help



- T.A.

View 8 Replies View Related

What Is The Differences Between Having And Where Clause In MS SQL 2000or MS SQL 2005 ?

Mar 11, 2008

Can I know about the above answer with 1 or examples if anybody can ?

View 6 Replies View Related

What Are The Differences Between MS SQL Server 2005 &&amp; Oracle 9i ?

Aug 5, 2006

Hi guyz,
I have basic knowledge of Sql Server 2005 and now i wanna move ahead in Oracle 9i !
So, i have 2 questions here -

a)Whats the Difference between Sql Server 2005 and Oracle 9 i ?

Note: Please keep the discussion general so that student like me can understand. I have never seen Oracle but the industry requirements suggest that Oracle is way better than than SQL Server 2005. But thats what i think

b)I am Running Windows XP SP 2 and i would like to practise Oracle 9i. So, Where i can download it for free ? I know i have checked there website but they don't mention the difference between each version. Oracle does't market their products as well as Microsoft.

Please , I am Student .. so i request you to make the discussion general .
Thank you for your time.

View 7 Replies View Related

UPDATE Statement Differences Between 2000 And 2005

Jun 21, 2007

I just wanted to post a difference I found between SQL 2000 and SQL 2005 regarding UDPATE statements that are done on a join. I understand that if tables are designed correctly this won't be a problem. But, when you inherit a bad design, you are unfortunately stuck with it. Hopefully this will help ease data differences in your migration from SQL 2000 to SQL 2005.



Run this code on a SQL 2000 connection, then run on SQL 2005. My guess on the behavior difference is strictly performance based since 2005 pulls the top result. Either way it can cause a lot of head scratching if you're not aware of it.



IF OBJECT_ID('tempdb..#UpdateTestA') IS NOT NULL

DROP TABLE #UpdateTestA



IF OBJECT_ID('tempdb..#UpdateTestB') IS NOT NULL

DROP TABLE #UpdateTestB



CREATE TABLE #UpdateTestA(

UpdateTestA int identity(1, 1),

FullName varchar(20),

UpdateData varchar(10))



CREATE TABLE #UpdateTestB(

UpdateTestB int identity(1, 1),

FullName varchar(20),

UpdateData varchar(10))



INSERT INTO #UpdateTestA(

FullName)

VALUES ('Barney Rubble')



INSERT INTO #UpdateTestB(

FullName,

UpdateData)

VALUES ('Barney Rubble', 'First')



INSERT INTO #UpdateTestB(

FullName,

UpdateData)

VALUES ('Barney Rubble', 'Second')



SELECT * FROM #UpdateTestA



UPDATE a

SET a.UpdateData = b.UpdateData

FROM #UpdateTestA a

INNER JOIN #UpdateTestB b on b.FullName = a.FullName



SELECT * FROM #UpdateTestA



DROP TABLE #UpdateTestA

DROP TABLE #UpdateTestB



Hope this solves a problem that you were having too.

View 3 Replies View Related

View SELECT Differences Between SQL 2000 &&amp; 2005

Apr 8, 2008

I have the following a view on a SQL2K box that uses the following SELECT statement:

SELECT



SF.SKU,
SAT.PublicationDate AS SATPubDate,
SAM.PublicationDate AS SAMPubDat
FROM SkuFlags SF
LEFT OUTER JOIN SpringArbor_ttlsparb SAT ON SF.ISBN = SAT.ISBN
LEFT OUTER JOIN SpringArbor_music SAM ON SF.ISBN = SAM.PrimaryKey
WHERE (
(
( SAT.PublicationDate IS NOT NULL ) AND
( SAT.PublicationDate <> '010001' ) AND
( GETDATE() <= DATEADD(day, -1, ( CAST(LEFT(SAT.PublicationDate, 2) + '/01/' + RIGHT(SAT.PublicationDate, 4) AS DATETIME) )))
)
OR (
( SAM.PublicationDate <> '010001' ) AND
( SAM.PublicationDate IS NOT NULL ) AND
( GETDATE() <= DATEADD(day, -1, ( CAST(LEFT(SAM.PublicationDate, 2) + '/01/' + RIGHT(SAM.PublicationDate, 4) AS DATETIME)))
)
)


The view works in SQL2K. When I try to run it under SQL2K5, I get a "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value." error. I know what the error is, the SAM.PublicationDate field has NULL values in it (and this is vendor supplied data that is updated frequently, so not dealing with NULL values isn't an option), so during the CAST function it's try to CAST NULL + /01/ + NULL into a DATETIME value and crashing.

My question is why this works in SQL2K and not SQL2K5?

Thanks,
Kevin

View 1 Replies View Related

Merge Replication Differences Between SQL 2000 And SQL 2005

Jun 27, 2006

We have developed a mobile system that uses merge replication for SQL Mobile to SQL 2005. Previously we have developed mutliple mobile systems using merge replication for SQL Ce to SQL 2000.

Based on the knowledge we had gathered over about 4 years, we applied the synchronisation parameters for the SQL 2005 solution as we would for the SQL 2000 solution.

We have found there are some differences. Not too surprising I suppose, only some of these have us a little baffled.

For instance, there was a little flag called keep_partition_changes in SQL 2000 that is supposedly superceded by the use_partition_groups flag. However, if you don't set up your filtering to conform to the standards required by the use_pre_computed_partitions flag if you want it set to true, then the use_partition_groups flag gets set to false - also the @partition_options falg gets set back to 0 (static or non-unique data) when we want it at 3 (Single Parition, One subscriber).

To top it all off, when you get the use_partition_groups flag working, there are restrictions on which columns you can update on the device. WTF? This seems ludicrous, to be unable to update data at the subscriber - particularly information that allows you to effectively "delete" data from your subscription.

Examples of the current behaviour are as follows,

On initialize for a subscriber, the subscriber will receive their own data as inserts, plus exact multiples of that data as updates. Say there are 100 rows in TableA, the subscriber gets 100 inserts, plus 6000 updates. TableB has 20 rows, the subscriber gets 20 inserts, 1200 updates.

Further to this, performance goes out the window when synchronising changes. Typically the data flow will be between 5 and 200 changes in both directions for a synchronisation. We are seeing sync times in the replication monitor of over 20 seconds per user. Surely the calculations do not take that long. The tables in the database are not very large.

This behaviour gets significantly worse as we load the system. The application has an auto sync function which is timed to operate evry 10 minutes. However, now that there is in excess of 50 or so users on the system, those synchronisation times blow out to multiple minutes and the server starts to thrash. We have looked at indexing and maintenance but to no avail.

Everything still points to the merge replication setup.

So, it seems obvious to me that we are mising some key information about how to set up merge replication in SQL 2005. We woudl be very gratefull if someone could point out the errors of our ways.

Sorry for the convoluted post. Hope someone can help us.

Cheers
Steve

View 6 Replies View Related







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