Direct Connection to Local SQL express gives error

I'm just starting out here.

I created a simple formview connected to a local file copy of the database by adding the database into the project. It worked fine.

However, I noticed that this copy of the database was not synch'ing with the database with the same name in SQL express. So I created a new connections string to access the SQL database directly out of SQL express. Its just a simple Select * from column. However, this gives an error. Why?

Server Error in '/NETCatMgr' Application.

---

The data types ntext and nvarchar are incompatible in the equal to operator.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: The data types ntext and nvarchar are incompatible in the equal to operator.

This is the connection string

<

addname="CatalogMgrConnectionString"connectionString="Data Source=mymachine\sqlexpress;Initial Catalog=CatalogSQL;Integrated Security=True"providerName="System.Data.SqlClient" />

Doesn't make sense. How can the formview work fine with the SQL integrated into the project but not working with a connection string to the SQL Express proper?

The error seems to be a data operation error other than a connection error. It indicates your code was trying to compare nvarchar value and ntext using "=" operator. Please make sure you choose proper data for comparation. To manipulate text/ntext/image data in SQL Server, you should use specific functions:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_fa-fz_0gs3.asp

|||Its not me -- the problem appears to be some kind of bug in the GridView. The database column is a type nText and the GridView doesn't seem to be able to write code for SQL databases with column types nText. It writes the code with a string comparison and then it crashes... I didn't write any code at all.

Difficulty uninstalling Native Client

I have been running SQL Server 2005 Express (basic) Instance MSSMLBIZ in conjunction with Microsoft Office Accounting 2007 Professional.

I want to make a "clean install" of SQL Server 2005 Express Advanced, SQL Server2005 Express Tools, in order to create additional Reports in Microsoft Office Accounting 2007 Professional.

I ran uininstall on the basic SQL Server 2005 Express, but Native Client did not uninstall; the following message was posted:

"An installation package for the product Microsoft SQL Native Client cannot be found. Try the installation again using a valid copy of the installation package 'sqlncli.msi'."

I then attempted to install SQL Server 2005 Express Advanced, but it failed to load Native Client. I then uninstalled all instances, deleted the SQL Server Express Files in Programs, and ran "CC Cleaner" to clear the Registry.

I then attempted to uninstall Native Client again, and received the following message:

"This action is only valid for products that are currently installed."

I the returned to CCCleaner Tools to attempt to delete the residuals and received the following message

"Cannot delete msi installer."

I ran "Search - *.msi" and there is no instance of sqlncli.msi visible.

I have Removed Registry Entries for SQL Express,
except HKEY_CLASSES_ROOT instances.

Should I delete these?

Have I forgotten or omitted something?

Please Help.

Try the link below for another option to remove Express from your box , another option is to use System Restore to roll your system back to the point before your first install now I am not sure if your registry utility have removed entries that may not allow the roll back. Hope this helps.

http://msdn.microsoft.com/vstudio/express/sql/download/advanced/default.aspx|||

hi,

you can try manually "fixing" SQL Native Client installing the last package you can find here.. then you can go on installing SQLExpress with Advanced Services..

regards

|||

I am trying to manually "fix" but I don't know where to go next, hence my post.

I simply want to remove all traces of SQL Server 2005 Express from my machine before I Install the SP2 Advanced version.

I need some real help, not more download options that are not identified.

|||

hi,

I do understand your comprehensible irritation, but often comes you can not completely remove a piece of software.. my advice is to fix the Native Client component, as it often causes problems when installing SQL Server as well..

the provided link i not "not identified" as it is a supported and well known Microsoft link to a Microsoft released package.. it's then up to you..

with kind regards

Difficulty in writing a DateTime to a table

I'm developing an application in VB 2005 Express using SQL 2005 Express. I need to put a timestamp into my table each time I create a row...

The following is a snippet...

Dim DDate As [SqlDateTime] = Now()

Dim TheQuery As String = "INSERT INTO Groups (PC_Name_Stamp, OperatorNo, Group_Type, Date_Time) VALUES ('Development', '2', 'Test',' " & DDate & " ')"

Which won't work as I am attempting to concatinate a SqlDateTime into a string.

My best guess is that I need to somehow to use a DEFAULT value in the table that persists so each time a row is created the datetime it was created is saved with the row, rather than being re-calculated each time the table is opened. There are probably several other ways of doing it and this may not be the easiest.

I'm not a programmer, just an Engineer, so I can only read Help for 5 minutes at a time.

Hi Ian,

What is the data type of the field named Date_Time? What is the error you are getting?

I would suggest removing the quotes from your query and just letting SQL handle the data type change from SqlDateTime into what ever Date_Time is defined as. If it is a string, the conversion will likely happen automatically. (At least it does in T-SQL.)

If that doesn't work, consider using a type conversion function rather than just wraping the value in quotes. You should be able to find information about data type conversion functions in the VB help documentations.

One final consideration, if you only need to determine if a row has been updated and the actual time is not really important, you might consider the timestamp data type. This data type automatically updates when a row is added or changed, but it stores a relative time, no the time associated with a clock.

Regards,

Mike Wachal
SQL Express team

-
Mark the best posts as Answers!

|||

Mike,

In order: "datetime"

The error I get is a VB pre-complier error warning that I am attempting to mix "String" with "SqlDateTime".

Removing the quotes does not help as the connection to the database is made with the following command:

Dim TheCommand As SqlCommand = New SqlCommand(TheQuery, TheConnection)

Where both "TheQuery" and "TheConnection" are strings.

Unfortunately I need to know the time and date, this will be used for locating information later.

My most recent attempts have used :

Dim TheQuery As String = "INSERT INTO Groups (PC_Name_Stamp, OperatorNo, Group_Type, Date_Time) VALUES ('Development', '2', 'Test',' GETDATE() ')"

Which throws "Cannot insert an explicit value into a timestamp column. Use INSERT with a column list to exclude the timestamp column, or insert a DEFAULT into the timestamp column." on execution (of the VB program).

I have attempted to set the default on the Date_Time column in "Groups" to be calculated as Getdate() and persistent, but then I get the following error: "Computed column 'DateTime' in table 'Tmp_Groups' cannot be persisted because the column is non-deterministic." I note that a calculated column in the table has no "format", which makes sense if it is not to be saved.

I am assuming that this is the correct forum, I think that the problem is related to SQL rather than .NET or VB.

I will be off the air now for 54 hours due to the weekend.

Thanks for your time Mike.

Ian

|||

It's interesting that you're getting an error specifying a timestamp field. Timestamp is different than datetime, you should make sure that you really have a datetime field.

That said, you're still trying to create string values by putting quotes around them. That isn't how you do it, you have to conver non-string values into strings using a conversion function, in this case CStr(). I managed to write an insert from VB by converting the output of Now() to a string and passing that in my query. I've povided the table definition and VB code below for you to examine.

Table Def-

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[Shippers](

[ShipperID] [int] IDENTITY(1,1) NOT NULL,

[CompanyName] [nvarchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[Phone] [nvarchar](24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[tsTime] [datetime] NULL,

[sTime] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

CONSTRAINT [aaaaaShippers_PK] PRIMARY KEY NONCLUSTERED

(

[ShipperID] ASC

)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

-- End Table -

- Code--

Sub Main()

Dim cnn As New SqlConnection

cnn.ConnectionString = "Data Source=.\sqlexpress;Initial Catalog=NorthwindSQL_Upsize;Integrated Security=True"

cnn.Open()

Dim cmd As New SqlCommand

cmd.CommandText = "INSERT INTO Shippers (CompanyName, Phone, tsTime) VALUES('VB Shippers','555-1212','" & CStr(Now()) & "')"

cmd.Connection = cnn

cmd.ExecuteNonQuery()

End Sub

- End Code --

|||

Mike,

A very astute observation, I do have an issue with the column format.

A search of my harddisk shows a total of 8 copies of this database, two I have as backups, one is a copy I was importing data into using Management Studio, the rest have uncertain pedigree, (some I no doubt created in error).

I long for the days of SQL Server 2000 when I only kept two copies of the database, (one live & one copy). Having a cut-back latest version makes it very tempting to develop & use the latest, but I wonder if SQL Server Express has been emasculated to the extent that it is no longer worthy of the title "Database". Yes, the mistake was mine, I think it was an easier mistake to make due to the "over enthusiastic feature removal".

Thank you for you help Mike.

Ian

sql

Differing collations for database and server - How big a problem is it?

I am using 3 servers. They are SQL all Server Express. I just noticed
that they are all outof sync wrt. Versions and Collation. has there
been a SQL Server Express service pack released? I seem to remember one.
Live
====
Version:9.00.3042.00
Collation:Latin1_General_CI_AS
Development
===========
Version:9.00.3054.00
Collation:SQL_Latin1_General_CP1_CI_AS
Stage
=====
Version:9.00.2047.00
Collation:Latin1_General_CI_AS
1. What is the best solution to this collation problem? To alter the
Development server? - I guess so, but there's another related problem.
My main application database is using 'SQL_Latin1_General_CP1_CI_AS'!
[I have script to convert the collations so I don't need to
re-install]. Yep that's a database with SQL_Latin1_General_CP1_CI_AS
collation sitting on a server using Latin1_General_CI_AS collation.
Does it matter which of these 2 collations I use:Latin1_General_CI_AS
or SQL_Latin1_General_CP1_CI_AS?
2. Two of these servers (Live, Development) have had the SQL service
pack installed but why do those two have different version numbers? Is
it the case that some of the ordinary microsoft updates also update SQL
Server Express? I noticed that the provider who runs the Live site does
not add all the microsoft service packs and even when adding service
packs waits a while until they are 'proven in the field'. The Stage
server has probably not has the service pack - so I guess that explains
the version number.
Hi,
The collation is more important in database. Since sql 2000 I can have a
different server collation from database collation.
If you want link diferent servers, You must be in mind the collations of
databases.
The important is (again) that the databases in differents enviroments, have
the same collation.
Regards,
"mark4asp" wrote:

> I am using 3 servers. They are SQL all Server Express. I just noticed
> that they are all outof sync wrt. Versions and Collation. has there
> been a SQL Server Express service pack released? I seem to remember one.
> Live
> ====
> Version:9.00.3042.00
> Collation:Latin1_General_CI_AS
> Development
> ===========
> Version:9.00.3054.00
> Collation:SQL_Latin1_General_CP1_CI_AS
> Stage
> =====
> Version:9.00.2047.00
> Collation:Latin1_General_CI_AS
> 1. What is the best solution to this collation problem? To alter the
> Development server? - I guess so, but there's another related problem.
> My main application database is using 'SQL_Latin1_General_CP1_CI_AS'!
> [I have script to convert the collations so I don't need to
> re-install]. Yep that's a database with SQL_Latin1_General_CP1_CI_AS
> collation sitting on a server using Latin1_General_CI_AS collation.
> Does it matter which of these 2 collations I use:Latin1_General_CI_AS
> or SQL_Latin1_General_CP1_CI_AS?
> 2. Two of these servers (Live, Development) have had the SQL service
> pack installed but why do those two have different version numbers? Is
> it the case that some of the ordinary microsoft updates also update SQL
> Server Express? I noticed that the provider who runs the Live site does
> not add all the microsoft service packs and even when adding service
> packs waits a while until they are 'proven in the field'. The Stage
> server has probably not has the service pack - so I guess that explains
> the version number.
>
|||Another thing:
the version must be the same, is very important especially when you try to
do maintenance tasks. The results can be differents between servers.
Put Your servers in the same version
9.00.3054
Regards,
"Carlos A." wrote:
[vbcol=seagreen]
> Hi,
> The collation is more important in database. Since sql 2000 I can have a
> different server collation from database collation.
> If you want link diferent servers, You must be in mind the collations of
> databases.
> The important is (again) that the databases in differents enviroments, have
> the same collation.
>
> Regards,
>
> "mark4asp" wrote:

Differing collations for database and server - How big a problem is it?

I am using 3 servers. They are SQL all Server Express. I just noticed
that they are all outof sync wrt. Versions and Collation. has there
been a SQL Server Express service pack released? I seem to remember one.
Live
==== Version: 9.00.3042.00
Collation: Latin1_General_CI_AS
Development
=========== Version: 9.00.3054.00
Collation: SQL_Latin1_General_CP1_CI_AS
Stage
===== Version: 9.00.2047.00
Collation: Latin1_General_CI_AS
1. What is the best solution to this collation problem? To alter the
Development server? - I guess so, but there's another related problem.
My main application database is using 'SQL_Latin1_General_CP1_CI_AS'!
[I have script to convert the collations so I don't need to
re-install]. Yep that's a database with SQL_Latin1_General_CP1_CI_AS
collation sitting on a server using Latin1_General_CI_AS collation.
Does it matter which of these 2 collations I use: Latin1_General_CI_AS
or SQL_Latin1_General_CP1_CI_AS?
2. Two of these servers (Live, Development) have had the SQL service
pack installed but why do those two have different version numbers? Is
it the case that some of the ordinary microsoft updates also update SQL
Server Express? I noticed that the provider who runs the Live site does
not add all the microsoft service packs and even when adding service
packs waits a while until they are 'proven in the field'. The Stage
server has probably not has the service pack - so I guess that explains
the version number.Hi,
The collation is more important in database. Since sql 2000 I can have a
different server collation from database collation.
If you want link diferent servers, You must be in mind the collations of
databases.
The important is (again) that the databases in differents enviroments, have
the same collation.
Regards,
"mark4asp" wrote:
> I am using 3 servers. They are SQL all Server Express. I just noticed
> that they are all outof sync wrt. Versions and Collation. has there
> been a SQL Server Express service pack released? I seem to remember one.
> Live
> ====> Version: 9.00.3042.00
> Collation: Latin1_General_CI_AS
> Development
> ===========> Version: 9.00.3054.00
> Collation: SQL_Latin1_General_CP1_CI_AS
> Stage
> =====> Version: 9.00.2047.00
> Collation: Latin1_General_CI_AS
> 1. What is the best solution to this collation problem? To alter the
> Development server? - I guess so, but there's another related problem.
> My main application database is using 'SQL_Latin1_General_CP1_CI_AS'!
> [I have script to convert the collations so I don't need to
> re-install]. Yep that's a database with SQL_Latin1_General_CP1_CI_AS
> collation sitting on a server using Latin1_General_CI_AS collation.
> Does it matter which of these 2 collations I use: Latin1_General_CI_AS
> or SQL_Latin1_General_CP1_CI_AS?
> 2. Two of these servers (Live, Development) have had the SQL service
> pack installed but why do those two have different version numbers? Is
> it the case that some of the ordinary microsoft updates also update SQL
> Server Express? I noticed that the provider who runs the Live site does
> not add all the microsoft service packs and even when adding service
> packs waits a while until they are 'proven in the field'. The Stage
> server has probably not has the service pack - so I guess that explains
> the version number.
>|||Another thing:
the version must be the same, is very important especially when you try to
do maintenance tasks. The results can be differents between servers.
Put Your servers in the same version
9.00.3054
Regards,
"Carlos A." wrote:
> Hi,
> The collation is more important in database. Since sql 2000 I can have a
> different server collation from database collation.
> If you want link diferent servers, You must be in mind the collations of
> databases.
> The important is (again) that the databases in differents enviroments, have
> the same collation.
>
> Regards,
>
> "mark4asp" wrote:
> > I am using 3 servers. They are SQL all Server Express. I just noticed
> > that they are all outof sync wrt. Versions and Collation. has there
> > been a SQL Server Express service pack released? I seem to remember one.
> >
> > Live
> > ====> > Version: 9.00.3042.00
> > Collation: Latin1_General_CI_AS
> >
> > Development
> > ===========> > Version: 9.00.3054.00
> > Collation: SQL_Latin1_General_CP1_CI_AS
> >
> > Stage
> > =====> > Version: 9.00.2047.00
> > Collation: Latin1_General_CI_AS
> >
> > 1. What is the best solution to this collation problem? To alter the
> > Development server? - I guess so, but there's another related problem.
> > My main application database is using 'SQL_Latin1_General_CP1_CI_AS'!
> > [I have script to convert the collations so I don't need to
> > re-install]. Yep that's a database with SQL_Latin1_General_CP1_CI_AS
> > collation sitting on a server using Latin1_General_CI_AS collation.
> >
> > Does it matter which of these 2 collations I use: Latin1_General_CI_AS
> > or SQL_Latin1_General_CP1_CI_AS?
> >
> > 2. Two of these servers (Live, Development) have had the SQL service
> > pack installed but why do those two have different version numbers? Is
> > it the case that some of the ordinary microsoft updates also update SQL
> > Server Express? I noticed that the provider who runs the Live site does
> > not add all the microsoft service packs and even when adding service
> > packs waits a while until they are 'proven in the field'. The Stage
> > server has probably not has the service pack - so I guess that explains
> > the version number.
> >

Different versions of SSMS?

Hi.
Does it exist different versions of SSMS?
I'm now working with SSMS Express and SQL Server 2005 Express. How can I
make a scheduled backup from SSMS?
___________________________
Best regards - Anders
Sql Server Express does not include a SQL Agent, so you will have to
use an external solution for scheduling your backups. My suggestion
would be to run a script with SQLCMD on a scheduled basis (e.g. with
the at command).
Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||Or use below two:
http://sqldbatips.com/showarticle.asp?ID=27
http://sqldbatips.com/showarticle.asp?ID=29
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1180341388.388646.126450@.u30g2000hsc.googlegr oups.com...
> Sql Server Express does not include a SQL Agent, so you will have to
> use an external solution for scheduling your backups. My suggestion
> would be to run a script with SQLCMD on a scheduled basis (e.g. with
> the at command).
> Jens K. Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>

Different Server Name

Hi all,

I have a database created with MS SQL 2000 and I want to restore the database in SQL 2005 Express Edition. The problem is that both of the Sql servers are located on different computers so they have different server names. Is that possible to do it? I have tried to restore using the usual way and it failed. Thank you very much for your help.

Yes, you should be able to restore the database on a new machine (regardless of machine name). Are you hitting an error? If so, what does it say?

Thanks,
Sam Lester (MSFT)

|||TITLE: Microsoft SQL Server Management Studio Express

Restore failed for Server 'KID\SQLEXPRESS'. (Microsoft.SqlServer.Express.Smo)

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

ADDITIONAL INFORMATION:

System.Data.SqlClient.SqlError: The backup set holds a backup of a database other than the existing 'Inventory List' database. (Microsoft.SqlServer.Express.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&LinkId=20476

BUTTONS:

OK

The error message is something like above. Any idea how to solve that? Thanks a lot|||

Here's a thread showing how to work around this error:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=694693&SiteID=1

Thanks,
Sam Lester (MSFT)

|||

Hi Sam,

Thank you very much for the link. Now, I have understood what the problem is.

Different Restore Error

I get the following error when I try to restore a database on a SQL 2005 server
TITLE: Microsoft SQL Server Management Studio Express
--
Restore failed for Server '172.27.101.60'. (Microsoft.SqlServer.Express.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=Restore+Server&LinkId=20476
--
ADDITIONAL INFORMATION:
System.Data.SqlClient.SqlError: The backup set holds a backup of a database
other than the existing 'Mirror_Test' database.
(Microsoft.SqlServer.Express.Smo)
For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&LinkId=20476
--
BUTTONS:
OK
--
--
MG"MGeles" <michael.geles@.thomson.com> wrote in message
news:87ECF046-E857-4682-AF97-8F6425F13AD9@.microsoft.com...
> I get the following error when I try to restore a database on a SQL 2005
server
Is is possible the backup file contains multiple backups within it.
> TITLE: Microsoft SQL Server Management Studio Express
> --
> Restore failed for Server '172.27.101.60'.
(Microsoft.SqlServer.Express.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=Restore+Server&LinkId=20476
> --
> ADDITIONAL INFORMATION:
> System.Data.SqlClient.SqlError: The backup set holds a backup of a
database
> other than the existing 'Mirror_Test' database.
> (Microsoft.SqlServer.Express.Smo)
> For help, click:
>
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&LinkId=20476
> --
> BUTTONS:
> OK
> --
> --
> MG

Differences between 2005, 2000, and Express - documentation, whitepapers?

Can anyone point me in the direction of some NON-sales documentation on the differences between these product? I am sure, especially with Express, there are considerable functionality and architecture differences.

I've looked in BOL, and I've done searches online.

All i seem to get is sales related stuff.

I'm curious about the architecture of SQL 2005, SQL express. In 2000, there was some fairly detailed documentaiton on this subject, but 2005 BOL seems REALLY diffucult to find things.

I may just need to try different keywords...

Here is a good page for 2005: http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx|||

Hello all, two questions that perhaps are related:

Is it a bad idea to use sqlexpress for systemtests if prod-environment is sql2005? I am using stored procs against tables, simple techniques. No fancy stuff like external functions, webservices or similar .

I have to use standard security with a sqluser but I get authorisation failure. Do I need to set sqlexpress to allow mixed mode? How do I do that?

Thanks in advance!

/Rasmus

|||

yacrumb2,

Yes, you can use SQL Express for testing; you wouldn't be able to test SQL Agent Jobs and some of the other more 'advanced' functionality. But for simple stored procedure testing, there should be no problem. However, I would recommend that your test environment closely 'match' the production environment. (Or that you use the Developer's Edition -which cost only about $50 US, and is the same as Enterprise Edition)

Any version can use SQL Authentication, just set for 'Mixed Mode' on the security tab of the Server Properties window in SSMS (or SSMSE). If you are having problems, you may wish to post the error message and how you are trying to connect so we can help you.