Change Identify/Primary Key Column Data Type?

Hi ...

I've

taken over a project where the tables were created with

identity/primary key columns of type DECIMAL(12,0). The latest addition

to the project is to replicate data down to Pocket PC applications.

Replication requires that identify/primary key columns be of type

INT/BIGINT.

I've attempted to ALTER TABLE xxx ALTER COLUMN yyy

BIGINT; and it fails. Failed due to all the foreign key constraints that have been created. While it didn't give any error messages associated with the the field being the PRIMARY KEY - I'm assuming I may get that error as well.

I then did an ALTER TABLE xxx NOCHECK CONSTRAINT

ALL; for every table in the system to disable checking of foreign keys

and then attempted to alter the column to a bigint and it still failed.

How

can I change the column from Decimal to BIGINT - or do I have to create

new tables, import all the data, get rid of the original tables? Please

tell me I don't have to do the latter.

Thanks ...Unfortunately the only way is to recreate the table or drop & recreate the column. This would require removing the existing constraints (PKs, FKs) and recreating them. Alternatively, you can create a view on the table(s) that does the conversion and replicate those. But this may or may not work depending on your replication scheme and you will have to ask in the Replication newsgroup.

change datasource with sqlReportingService2005 class

hi pro , i created model project with .net 2005 after that , create report with reportbuilder that it use this model. it s ok.

i have project and i want to change datasource (connection string ) in runtime when User click on report ,i use sqlReportingService2005 class :

Dim rs As New ReportingService2005()

rs.Credentials = System.Net.CredentialCache.DefaultCredentials

Dim definition As New DataSourceDefinition()
definition.CredentialRetrieval = CredentialRetrievalEnum.Integrated
definition.ConnectString = "Data Source=" & serverName & ";Initial Catalog=" & DataBaseName & ";Integrated Security=True"
definition.Enabled = True
definition.EnabledSpecified = True
definition.Extension = "SQL"
definition.ImpersonateUser = False
definition.ImpersonateUserSpecified = False ' True
definition.Prompt = Nothing
definition.WindowsCredentials = True ' False

Try
rs.SetDataSourceContents("/Data Sources/" & DataSourceName, definition)

Catch ex As SoapException
Console.WriteLine(ex.Detail.OuterXml)
End Try
End Sub

error "An unhandled exception of type 'System.Net.WebException' occurred in System.Web.Services.dll

Additional information: The request failed with HTTP status 404: Object Not Found." happend

( i checked Enable Unmanged code debugging =true)

please help me

tanks

This error occurs only when the report server URL is given wrong or does not exists. Do check your report server URL assigned to the rs.Url property.

|||

tanks for reply. i was forgotten to say : i have 2 machin ,the first one uses Windows 2003 Server and i installed sql server on it , another machin uses windows a xp and i run project on it( it s like client). when i take project on sql server 2005 it s work nice but when i run project on xp it dosent work.

|||

Are you able to open the report server url in browser?

Change data type during INSERT INTO ?

I'm doing a data conversion project, moving data from one SQL app to
another.
I'm using INSERT INTO with Select and have the syntax correct. But when
executing the script I get:

Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to float.

Is it possible to change the data type during the INSERT INTO statement?

ThanksThe destination table should be compatible with the data type of select
table

Madhivanan|||"rdraider" <rdraider@.sbcglobal.net> wrote in message
news:eRu_d.11608$C47.6368@.newssvr14.news.prodigy.c om...
> I'm doing a data conversion project, moving data from one SQL app to
> another.
> I'm using INSERT INTO with Select and have the syntax correct. But when
> executing the script I get:
> Server: Msg 8114, Level 16, State 5, Line 1
> Error converting data type varchar to float.
> Is it possible to change the data type during the INSERT INTO statement?
> Thanks

As the insert into has to guess about the datatypes of the table to create,
the safest way is to specifically create the table prior to the select into.

You can use cast or convert to gain a little more control over the way the
table is created.
but since you didn't post and example I can't help you more than this.|||On Fri, 18 Mar 2005 06:48:42 GMT, rdraider wrote:

>I'm doing a data conversion project, moving data from one SQL app to
>another.
>I'm using INSERT INTO with Select and have the syntax correct. But when
>executing the script I get:
>Server: Msg 8114, Level 16, State 5, Line 1
>Error converting data type varchar to float.
>Is it possible to change the data type during the INSERT INTO statement?

Hi rdraider,

If you use
INSERT INTO tablename (col1, col2, ..., colN)
SELECT expr1, expr2, ..., exprN
FROM ...
WHERE ...

Then the result of each expression will implicitly be converted to the
datatype of the corresponding column before it's stored in the table.

Your message indicates that one of your expressions is of datetype
varchar, but the corresponding column is of datatype float, and the
implicit conversion failed. E.g. because the varchar value to be
converted happened to be 'xxhasiu'.

If you use INSERT without column list, or SELECT *, then the first thing
should be to add a column list - changes to the table structure might
change the number and order of columns in the INSERT or in the SELECT *,
inducing a mismatch; explicitly naming the columns ensures that this
won't happen.

If that doesn't fix it, then find out which column(s) in the destination
table are of datatype float; run the SELECT (without the INSERT INTO) to
see the results and inspect the output to find the offending value. If
the output has too many rows for visual inspection, you might try adding
AND ISNUMERIC(exprN) = 0
This will return only the rows where exprN can't be converted to int,
float, money or decimal.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

change authentication method in a local database file

I have made a small asp.net project which uses a local database file as a part of the project. The project is running fine om my local machine, but when I upload it to the remote server, the login fails for the server.

I suspect this is can be solved by using sqlserver authentication. But I have now spent a lot of time trying to configure the database file to use this authentication mode. As I see it there are three possible solutions to the problem.

use management studio express to configure the local mdf file (Ecxept that I cant find out how to connect to the mdf-file) and from here change the authentication method to sqlserver authentication. use Visual Web developer to change the authentication method (but how?) make the windows authentication work on the server (this would probably require that mannamgement studio express connects to the remote database. (Same problem as no 1)

Help will be higly appreciated.

Bjarke

hi Bjarke,

to make things run, the account running the application server must be one of the SQL Server registered logins (at the instance level and not database level).. if your solution is hosted, I do think the IT stuff of the hosting company provides that "by default" or nothing would run, and I do not think they will grant you a standard SQL Server login... did you contact them?

regards

|||

Hello Andrea!

Maybe I have misunderstood something?

When you make an asp.net 2.0 project there is a folder (App_Data) where sit is possible via Visual web developer to add a sql database file. As I have understood it this file works as a little database server as well. Does SQL server even have to be installed on the server to make these files work as a database?

I have not contacted the hosting company about this problem, because I didnt consider it a problem. But Maybe I am wrong.

Do you know how to change the autnentication method for a local database file? I have tried a lot of things, and searched the internet for an answer, but without luck.

Thanks Bjarke

|||

hi Bjarke,

bjarke wrote:

Hello Andrea!

Maybe I have misunderstood something?

When you make an asp.net 2.0 project there is a folder (App_Data) where sit is possible via Visual web developer to add a sql database file. As I have understood it this file works as a little database server as well.

perhaps you are intending User Istances "mode", a feature available only with SQLExpress edition of SQL Server 2005.. remember that this feature is not provided by hoster that do not have SQLExpress but "traditional" SQL Server 2005 editions...

Does SQL server even have to be installed on the server to make these files work as a database?

yes, SQLExpress must be installed on the computer running the application, in your case the web application, thus the application server... when the app start's up, a User Instance of SQLExpress is generated (if not already existing)...

I have not contacted the hosting company about this problem, because I didnt consider it a problem. But Maybe I am wrong.

Do you know how to change the autnentication method for a local database file? I have tried a lot of things, and searched the internet for an answer, but without luck.

Thanks Bjarke

the "traditional" authentication "setting" can be changed using SSMSE, accessing the instance property, in the security tab or modifying a Windows registry key.. stay with the first one

the service must be restarted to use the new settings..

as regard user instances, only Windows authentication can be used with this "access mode"...

if you mean how to modify the authentication settings in the connection strings, you can have a look here..

regards

Centralised Database

Hi All,
I've been tasked at looking into a solution for a global data project for my
company.
The company uses some software that requires a local database, the local
databases are going to be located in Australia, US, & UK, the local database
s
will have exact copies of each others schema. They would like the UK &
Australia databases to update the US database (effectively making the US
database centralized).
Conceptually whats the best way of doing this? My thoughts would be to have
the 3 local databases and create an additional database to centralize the
three using DTS or replication... ...there would be no overlapping data in
any of the three databases (except identity based keys), however data would
be updated. The only caviate I have to adhere to is the centralized databas
e
must be no more than 30 minutes out of date (preferably no more than 15
minutes).
I've had a good year and half experience of being a DBA, but have never
really had to touch on replication and distributed data, so any thoughts or
views you have would be greatly appreciated.
Kind regards
BenI think your case is the simplest one, no data overlapping, you can use any
way to update data.
I would like use webservice, it is easy to control and no problem on any
firewall.
"BenUK" <BenUK@.discussions.microsoft.com> wrote in message
news:60AC4511-63FA-4709-A5BD-84A091750BD6@.microsoft.com...
> Hi All,
> I've been tasked at looking into a solution for a global data project for
my
> company.
> The company uses some software that requires a local database, the local
> databases are going to be located in Australia, US, & UK, the local
databases
> will have exact copies of each others schema. They would like the UK &
> Australia databases to update the US database (effectively making the US
> database centralized).
> Conceptually whats the best way of doing this? My thoughts would be to
have
> the 3 local databases and create an additional database to centralize the
> three using DTS or replication... ...there would be no overlapping data in
> any of the three databases (except identity based keys), however data
would
> be updated. The only caviate I have to adhere to is the centralized
database
> must be no more than 30 minutes out of date (preferably no more than 15
> minutes).
> I've had a good year and half experience of being a DBA, but have never
> really had to touch on replication and distributed data, so any thoughts
or
> views you have would be greatly appreciated.
> Kind regards
> Ben

Centralised Database

Hi All,
I've been tasked at looking into a solution for a global data project for my
company.
The company uses some software that requires a local database, the local
databases are going to be located in Australia, US, & UK, the local databases
will have exact copies of each others schema. They would like the UK &
Australia databases to update the US database (effectively making the US
database centralized).
Conceptually whats the best way of doing this? My thoughts would be to have
the 3 local databases and create an additional database to centralize the
three using DTS or replication... ...there would be no overlapping data in
any of the three databases (except identity based keys), however data would
be updated. The only caviate I have to adhere to is the centralized database
must be no more than 30 minutes out of date (preferably no more than 15
minutes).
I've had a good year and half experience of being a DBA, but have never
really had to touch on replication and distributed data, so any thoughts or
views you have would be greatly appreciated.
Kind regards
Ben
I think your case is the simplest one, no data overlapping, you can use any
way to update data.
I would like use webservice, it is easy to control and no problem on any
firewall.
"BenUK" <BenUK@.discussions.microsoft.com> wrote in message
news:60AC4511-63FA-4709-A5BD-84A091750BD6@.microsoft.com...
> Hi All,
> I've been tasked at looking into a solution for a global data project for
my
> company.
> The company uses some software that requires a local database, the local
> databases are going to be located in Australia, US, & UK, the local
databases
> will have exact copies of each others schema. They would like the UK &
> Australia databases to update the US database (effectively making the US
> database centralized).
> Conceptually whats the best way of doing this? My thoughts would be to
have
> the 3 local databases and create an additional database to centralize the
> three using DTS or replication... ...there would be no overlapping data in
> any of the three databases (except identity based keys), however data
would
> be updated. The only caviate I have to adhere to is the centralized
database
> must be no more than 30 minutes out of date (preferably no more than 15
> minutes).
> I've had a good year and half experience of being a DBA, but have never
> really had to touch on replication and distributed data, so any thoughts
or
> views you have would be greatly appreciated.
> Kind regards
> Ben

Centralised Database

Hi All,
I've been tasked at looking into a solution for a global data project for my
company.
The company uses some software that requires a local database, the local
databases are going to be located in Australia, US, & UK, the local databases
will have exact copies of each others schema. They would like the UK &
Australia databases to update the US database (effectively making the US
database centralized).
Conceptually whats the best way of doing this? My thoughts would be to have
the 3 local databases and create an additional database to centralize the
three using DTS or replication... ...there would be no overlapping data in
any of the three databases (except identity based keys), however data would
be updated. The only caviate I have to adhere to is the centralized database
must be no more than 30 minutes out of date (preferably no more than 15
minutes).
I've had a good year and half experience of being a DBA, but have never
really had to touch on replication and distributed data, so any thoughts or
views you have would be greatly appreciated.
Kind regards
BenI think your case is the simplest one, no data overlapping, you can use any
way to update data.
I would like use webservice, it is easy to control and no problem on any
firewall.
"BenUK" <BenUK@.discussions.microsoft.com> wrote in message
news:60AC4511-63FA-4709-A5BD-84A091750BD6@.microsoft.com...
> Hi All,
> I've been tasked at looking into a solution for a global data project for
my
> company.
> The company uses some software that requires a local database, the local
> databases are going to be located in Australia, US, & UK, the local
databases
> will have exact copies of each others schema. They would like the UK &
> Australia databases to update the US database (effectively making the US
> database centralized).
> Conceptually whats the best way of doing this? My thoughts would be to
have
> the 3 local databases and create an additional database to centralize the
> three using DTS or replication... ...there would be no overlapping data in
> any of the three databases (except identity based keys), however data
would
> be updated. The only caviate I have to adhere to is the centralized
database
> must be no more than 30 minutes out of date (preferably no more than 15
> minutes).
> I've had a good year and half experience of being a DBA, but have never
> really had to touch on replication and distributed data, so any thoughts
or
> views you have would be greatly appreciated.
> Kind regards
> Ben

CE DB requires SQL Express install on client machines?

I'm working on a C# 2008 project, when i add a CE Database to it, then publish the app, the installer wants to download and install the entire SQL Server Express product on the client machines. My understanding is that I should be able to embed this database right into the app, but it defeats the purpose to have the installer download and install the entire express product (the actual project is 1 meg).

Is this expected behavior? or am I doing something wrong?

SQL Express is NOT required for SQL Compact projects. You must have a reference to a .mdf file or similar in your project that triggers this requirement.|||

I checked the prerequisite tab and sql express isnt even checked, i'll investigate further