Change location of an Existing DB in MSDE

Hi everyone,
I have an MSDE database that was created using the osql.exe. By default it gets automatically created in the MSSQL7/data directory.
Does anyone know how i can detach and then re-attach the database from a new location?Refer to SP_ATTACH_DB & SP_DETACH_DB topics in books online.

Also you can mention path for data files while using CREATE DATABASE Statement.|||Have U thought of

1)Backup db
2)copy the mdf & ldf files
3)delete the db
4)create a new db with same name but data files in the new place
5)overwite the new files (s'be same name) with the original files
(Don't know if u have to stop the SQL Service to do this bit)

or do 1) 3) & 4) + restore db

Worth a try

Ye Hah !

GW

Change Increment Value for existing Identity Column

Hi,
How to Change Increment Value for existing Identity Column (MS SQL2000) ?

I know how to change the seed :
DBCC CHECKIDENT (activity, RESEED,4233596)

but I need the future id generated with step 2
4233596
4233598
4233600
I would like to do it using T-sql because I will need to do it every day after syncronising with another SQL server .

Thanks,
NataliaYou have to drop and recreate the table.sql

Change Identity value

I changed a column on an existing table to be a Identity column but when I
try to insert a record I get an error that the id already exists. Is there a
way for me to change the value so identity returns a higher #?Are you passing the identity value?
AMB
"Joe" wrote:

> I changed a column on an existing table to be a Identity column but when I
> try to insert a record I get an error that the id already exists. Is there
a
> way for me to change the value so identity returns a higher #?
>
>|||You could use identity_insert to insert a row with the highest value
and then turn it off. Any inserts after that will increment sequentially.
ie.
create table test ( id int identity (1,1), col varchar(10) )
insert into test (col) values ('row1')
insert into test (col) values ('row2')
insert into test (col) values ('row3')
set identity_insert test on
insert into test (id, col) values (10,'row4')
set identity_insert test off
insert into test (col) values ('row5')
select * from test
"Joe" <J_no_spam@._no_spam_Fishinbrain.com> wrote in message
news:O5mJERHEFHA.2700@.TK2MSFTNGP14.phx.gbl...
> I changed a column on an existing table to be a Identity column but when I
> try to insert a record I get an error that the id already exists. Is there
a
> way for me to change the value so identity returns a higher #?
>|||Joe,
Look at the DBCC CHECKIDENT command in the Books Online. If your table
containing the identity column was called jobs,
DBCC CHECKIDENT (jobs, RESEED, 30)
would force the new value to 30.
On Fri, 11 Feb 2005 14:59:57 -0500, "Joe"
<J_no_spam@._no_spam_Fishinbrain.com> wrote:

>I changed a column on an existing table to be a Identity column but when I
>try to insert a record I get an error that the id already exists. Is there
a
>way for me to change the value so identity returns a higher #?
>

change fonts existing reports

Is it possible to change the font, for example to font-family Arial, for all
the existing reports made in reporting services.
--
regards, pakamSorry, this is not supported.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"pakam" <pakam@.discussions.microsoft.com> wrote in message
news:A53D0F05-9BF1-4A6C-A42C-DBAB603B8392@.microsoft.com...
> Is it possible to change the font, for example to font-family Arial, for
> all
> the existing reports made in reporting services.
> --
> regards, pakam|||You could do it dynamically, by using getreportdefinition to get one of the
existing reports as xml, then use SelectNodeList to get all the <FontFamily>
nodes.
Then foreach node in the node list, change the InnerText to the new font &
republish the report
"Robert Bruckner [MSFT]" wrote:
> Sorry, this is not supported.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "pakam" <pakam@.discussions.microsoft.com> wrote in message
> news:A53D0F05-9BF1-4A6C-A42C-DBAB603B8392@.microsoft.com...
> > Is it possible to change the font, for example to font-family Arial, for
> > all
> > the existing reports made in reporting services.
> > --
> > regards, pakam
>
>|||Hi mark-s,
Thank you for your reaction. For your solution I have to write code ? I did
find the GetReportDefinition but not the SelectNodeList. Can you explain more
how to do this ? .
I did find out that it is possible to use Edit -> Find and Replace ->
Replace in Files. This is not perfect but it helps to avoid changing every
item. Then you can change all reports in the project to the font-family arial
for example.
Regards pakam
"mark-s" wrote:
> You could do it dynamically, by using getreportdefinition to get one of the
> existing reports as xml, then use SelectNodeList to get all the <FontFamily>
> nodes.
> Then foreach node in the node list, change the InnerText to the new font &
> republish the report
> "Robert Bruckner [MSFT]" wrote:
> > Sorry, this is not supported.
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> > "pakam" <pakam@.discussions.microsoft.com> wrote in message
> > news:A53D0F05-9BF1-4A6C-A42C-DBAB603B8392@.microsoft.com...
> > > Is it possible to change the font, for example to font-family Arial, for
> > > all
> > > the existing reports made in reporting services.
> > > --
> > > regards, pakam
> >
> >
> >|||Hi pakam
Yes, you will have to write code. SelectNodeList is in the System.Xml.XPath
namespace.
However, if you only want to change the font once, then Find/Replace might
be easier. I was thinking that you may need to change the font regularly.
"pakam" wrote:
> Hi mark-s,
> Thank you for your reaction. For your solution I have to write code ? I did
> find the GetReportDefinition but not the SelectNodeList. Can you explain more
> how to do this ? .
> I did find out that it is possible to use Edit -> Find and Replace ->
> Replace in Files. This is not perfect but it helps to avoid changing every
> item. Then you can change all reports in the project to the font-family arial
> for example.
> Regards pakam
>
> "mark-s" wrote:
> > You could do it dynamically, by using getreportdefinition to get one of the
> > existing reports as xml, then use SelectNodeList to get all the <FontFamily>
> > nodes.
> > Then foreach node in the node list, change the InnerText to the new font &
> > republish the report
> >
> > "Robert Bruckner [MSFT]" wrote:
> >
> > > Sorry, this is not supported.
> > >
> > > --
> > > This posting is provided "AS IS" with no warranties, and confers no rights.
> > >
> > > "pakam" <pakam@.discussions.microsoft.com> wrote in message
> > > news:A53D0F05-9BF1-4A6C-A42C-DBAB603B8392@.microsoft.com...
> > > > Is it possible to change the font, for example to font-family Arial, for
> > > > all
> > > > the existing reports made in reporting services.
> > > > --
> > > > regards, pakam
> > >
> > >
> > >

Change FK error: "Unable to delete relationship..."

Hello,
I have three tables in my existing database, (in a SQL Server 2000 server).
- a "Students" table,
- a "Classes" table, and
- a "StudentIsInClass" table.
The "StudentIsInClass" table is the relationship table linking a record in
the Students table to a record in the Classes table. This is effected with
two foreign keys:
FK_StudentIsInClass_Students
FK_StudentIsInClass_Classes
Recently, there was a problem in our application when changing the
"StudentNumber" for a Student. This is the primary key and so I get an erro
r
because of the relationship. The fix I want to apply is to change the
foreign key FK_StudentIsInClass_Students. I need to turn on the function of
"Cascade Update Related Fields". I use the GUI functions in SQL Server
Enterprise Manager to do this but, when I try to save the change, I get an
error:
'Students' table
- Unable to delete relationship 'FK_StudentIsInClass_Students'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL
Server]'FK_StudentIsInClass_Students' ''
[Microsoft][ODBC SQL Server Driver][SQL Server]'''
(The ' result because my SQL Server is running on a foreign language
server, so I can't read the exact error text.)
Does anyone have a clue why I would get this error? Any suggestions for
what to do about it? I appreciate any advice and help.
Many thanks in advance,
JustinJustin Little (JustinLittle@.discussions.microsoft.com) writes:
> Recently, there was a problem in our application when changing the
> "StudentNumber" for a Student. This is the primary key and so I get an
> error because of the relationship. The fix I want to apply is to change
> the foreign key FK_StudentIsInClass_Students. I need to turn on the
> function of "Cascade Update Related Fields". I use the GUI functions in
> SQL Server Enterprise Manager to do this but, when I try to save the
> change, I get an error:
> 'Students' table
> - Unable to delete relationship 'FK_StudentIsInClass_Students'.
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL
> Server]'FK_StudentIsInClass_Students' ''
> [Microsoft][ODBC SQL Server Driver][SQL Server]'''
> (The ' result because my SQL Server is running on a foreign language
> server, so I can't read the exact error text.)
> Does anyone have a clue why I would get this error? Any suggestions for
> what to do about it? I appreciate any advice and help.
It would certainly have helped to see the text of the error message.
I assume that the foreign language does not use the Latin script?
Anyway, rather than using Enterprise Manager for chaning tables, which
is dangerous as the table designer is buggy, use Query Analyzer and
type the DLL.
DECLARE @.err int
BEGIN TRANSACTION
ALTER TABLE StudentsIsInClass DROP CONSTRAINT FK_StudentIsInClass_Students
SELECT @.err = @.@.error
IF @.err = 0
BEGIN
ALTER TABLE StudentsIsInClass ADD
CONSTRAINT FK_StudentIsInClass_Students FOREIGN KEY (StudentID)
REFERECENS Students(StudentID)
ON UPDATE CASCADE
ON DELETE CASCADE
SELECT @.err = @.@.error
END
IF @.err = 0
COMMIT TRANSACTION
ELSE
ROLLBACK TRANSACTION
It is not unlikely that that this will fail too, but at least in that
case you will see an error number. You can then look up that number in
SQL Server Books Online, provided that you have an English version of
Books Online installed on your computer.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi Erland,
Many thanks for your advice on using Query Analyzer to get a readable error
message. (Thanks also for the tip about bugs in the Enterprise Manager GUI,
I'll bare that in mind in the future.)
I took your SQL procedure and used it in Query Analyzer. I find that there
is an error that occurs when I try to execute the line that reads "ALTER
TABLE StudentIsInClass DROP CONSTRAINT FK_StudentIsInClass_Students".
The error number I get is Msg 3728.
Server: Msg 3728, Level 16, State 1, Line 1
'dbo.FK_StudentIsInClass_Students' 並不是條件約束。
The server, as you guessed, does not use a Latin script. It's Chinese, so
the error message comes through in Chinese. On Query Analyzer I can see the
Chinese characters instead of a bunch of question marks. Books Online gives
the English message as:
'%.*ls' is not a constraint.
This seems strange to me. Also in Query Analyzer, in the Object Browser
I've expanded the tree beneath my "StudentIsInClass" table, and opened the
Constraints sub-section. There are three constraints listed there.
- PK_StudentIsInClass (StudentNumber, ClassID)
- FK_StudentIsInClass_Students (StudentNumber)
- FK_StudentIsInClass_Classes (ClassID)
If I try to right-click and delete any of these relationships I get the same
error message. (In fact, I can't delete any of constraints on any tables in
my entire database!)
Do you have any idea why I'm getting this confusing message?
Many thanks for your support.
Justin
PS: I'm connecting to the database using a user id called "bigbyte",
supplied to me by the ISP. The tables all belong to this user id (e.g.
"bigbyte.StudentIsInClass"), however I notice that the constraints are
members of the dbo group (e.g. "dbo.FK_StudentIsInClass_Students"). Do you
think that this could be the cause of my current problems?|||Justin Little (JustinLittle@.discussions.microsoft.com) writes:
> The error number I get is Msg 3728.
> Server: Msg 3728, Level 16, State 1, Line 1
> 'dbo.FK_StudentIsInClass_Students' ''
> The server, as you guessed, does not use a Latin script. It's Chinese,
> so the error message comes through in Chinese. On Query Analyzer I can
> see the Chinese characters instead of a bunch of question marks. Books
> Online gives the English message as: '%.*ls' is not a constraint.
Now, that was an interesting message...

> PS: I'm connecting to the database using a user id called "bigbyte",
> supplied to me by the ISP. The tables all belong to this user id (e.g.
> "bigbyte.StudentIsInClass"), however I notice that the constraints are
> members of the dbo group (e.g. "dbo.FK_StudentIsInClass_Students"). Do
> you think that this could be the cause of my current problems?
Most probably. I've tried to recreate this situation, but I was not
successful. When I create tables owned by a non-dbo user, and set up
a constraint, the constraint is owned by that user. The same happens
if I create the tables as owned by dbo, and then change ownership.
Maybe there was a bug in some previous version/service pack of SQL Server
that could cause this situation.
Since this is an abnormal situation, it is difficult to give sugestions
that I know work. I would try to change ownership of the table to
dbo, and then back to bigbyte.
You may have to talk to your ISP about this, as you may need someone
with dbo rights to perform this operation.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi Erland,
I got dbo rights from the ISP for the day. I tried what you suggested about
changing the ownership of the table. Now I'm no SQL Server guru but I found
out that the stored procedure for doing this is called sp_changeobjectowner.
So I tried running the following command:
EXEC sp_changeobjectowner 'StudentIsInClass', 'dbo'
I get an error message saying:
Server: Msg 15505, Level 16, State 1, Procedure sp_changeobjectowner, Line 6
3
無法變更物件 'StudentIsInClass' 的擁有人或其所屬的_物件
_為新的擁有人 'dbo' 已有相同名稱的物件。
or in English:
Cannot change owner of object '%ls' or one of its child objects because the
new owner '%ls' already has an object with the same name.
HOWEVER, when I try to run the same sp on a table that has no constraints,
it runs through just fine and changes the ownership. Could this mean that
the database has some inconsistencies in the constraints that it is storing?
When I first created these tables (about a year or more ago) I used the
"bigbyte" user. Later, I noticed that all the ownerships had changed to
"dbo". I guessed that the ISP had been mucking around and changed it all fo
r
reasons best known to them. (Like I said, I'm no guru so I don't know what
all this ownership stuff is really about anyway.) However, later I started
having trouble, particularly in using DTS scripts to export this live data t
o
my "dev" database and I asked them to change the ownership back to "bigbyte"
.
Now it seems all my tables are owned by "bigbyte" but all the constraints
are owned by "dbo".
Is there a stored procedure or something I can use to try to track down
multiple instances of the same constraint name, possibly owned by different
owners? Perhaps my original constraints are still bouncing around somewhere
inside the db's system tables, unknown to all of us?
Many thanks,
Justin
"Erland Sommarskog" wrote:

> Justin Little (JustinLittle@.discussions.microsoft.com) writes:
> Now, that was an interesting message...
>
> Most probably. I've tried to recreate this situation, but I was not
> successful. When I create tables owned by a non-dbo user, and set up
> a constraint, the constraint is owned by that user. The same happens
> if I create the tables as owned by dbo, and then change ownership.
> Maybe there was a bug in some previous version/service pack of SQL Server
> that could cause this situation.
> Since this is an abnormal situation, it is difficult to give sugestions
> that I know work. I would try to change ownership of the table to
> dbo, and then back to bigbyte.
> You may have to talk to your ISP about this, as you may need someone
> with dbo rights to perform this operation.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||Justin Little (JustinLittle@.discussions.microsoft.com) writes:
> I got dbo rights from the ISP for the day. I tried what you suggested
> about changing the ownership of the table. Now I'm no SQL Server guru
> but I found out that the stored procedure for doing this is called
> sp_changeobjectowner.
> So I tried running the following command:
> EXEC sp_changeobjectowner 'StudentIsInClass', 'dbo'
> I get an error message saying:
> Server: Msg 15505, Level 16, State 1, Procedure sp_changeobjectowner,
> Line 63 ' 'StudentIsInClass' '''' 'dbo' ''
> or in English: Cannot change owner of object '%ls' or one of its child
> objects because the new owner '%ls' already has an object with the same
> name.
> HOWEVER, when I try to run the same sp on a table that has no
> constraints, it runs through just fine and changes the ownership. Could
> this mean that the database has some inconsistencies in the constraints
> that it is storing?
You bet!
At this point, I would create a new set of tables copy all data over to
the new tables.
If you don't have a script saved for creating the tables, you can use
the Enterprise Manager to generate scripts. Make sure that you get triggers,
indexes and foreign keys.
Now, let's see what is the best way to have these two sets of tables in
parallel. I think the best is that youy edit the script so that all
table and trigger names are prefixed by "guest.". Constraints should not
be prefixed. Then use INSERT to copy data over. Take the occassion to add
ON UPDATE CASCADE.
Once this is done drop the existing tables - and let's hope *that* is
possible.
Finally, run sp_changeobjectowner to change ownership to bigbyte or dbo,
which ever you feel best.

> When I first created these tables (about a year or more ago) I used the
> "bigbyte" user. Later, I noticed that all the ownerships had changed to
> "dbo". I guessed that the ISP had been mucking around and changed it
> all for reasons best known to them. (Like I said, I'm no guru so I
> don't know what all this ownership stuff is really about anyway.)
Ownership of objects is like ownership of files in a file system.
But there is a second side of the coin. In SQL 2000, an owner is also a
schema. (This changes in SQL 2005). A schema is essentially a namespec.
A user has a default schema, which in SQL 2000 is always equal to his
user name. When you say "SELECT * FROM tbl" without specifying schema/owner,
SQL Server first looks in your default schema, then in the default schema of
the database, which in SQL 2000 always is dbo.
The guest user is a pre-defined user, and also has a schema, which I
suggested that you should use above, to permit you to have two sets of
tables at the same time.
I should add that in SQL 2000, having all objects owner by dbo - and thus
in the dbo schema - is what people do 99% of the time.

> Is there a stored procedure or something I can use to try to track down
> multiple instances of the same constraint name, possibly owned by
> different owners? Perhaps my original constraints are still bouncing
> around somewhere inside the db's system tables, unknown to all of us?
You would have to run queries on sysobjects to find out. But this database
seems a bit mashed, so you will need some brute force to get it working.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Change existing application to use sqlncli

I have a large application that currently uses SQLOLEDB as the provider.
The application needs to connect to SQL 7 thru SQL 2005.
I have tested changing the provider to SQLNCLI and everything still seems to
work.
I have a couple of questions ..
1) Is it really this easy ?
2) Can I still connect to SQL 7 and SQL 2000 using SQLNCLI ?
3) Are there any negative impacts to using SQLNCLI ?
Thanks.
The whole idea behind SQLNCLI is to provide updated capability to existing
ADO classic (COM-based) applications. I understand that it's supposed to
work with older versions, but if history is any teacher, I would thoroughly
test before betting my socks on it. See
http://blogs.msdn.com/dataaccess/archive/2005/07/29/445147.aspx for more
info.
Frankly, I would think (hard) about migrating to ADO.NET and the SqlClient
provider that can reach all current (and future?) versions of SQL
Server--and a lot faster with fewer surprises.
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
------
"Michael Tissington" <mtissington@.newsgroup.nospam> wrote in message
news:OOhR4DSXHHA.1636@.TK2MSFTNGP02.phx.gbl...
>I have a large application that currently uses SQLOLEDB as the provider.
> The application needs to connect to SQL 7 thru SQL 2005.
> I have tested changing the provider to SQLNCLI and everything still seems
> to work.
> I have a couple of questions ..
> 1) Is it really this easy ?
> 2) Can I still connect to SQL 7 and SQL 2000 using SQLNCLI ?
> 3) Are there any negative impacts to using SQLNCLI ?
> Thanks.
>

Change existing application to use sqlncli

I have a large application that currently uses SQLOLEDB as the provider.
The application needs to connect to SQL 7 thru SQL 2005.
I have tested changing the provider to SQLNCLI and everything still seems to
work.
I have a couple of questions ..
1) Is it really this easy ?
2) Can I still connect to SQL 7 and SQL 2000 using SQLNCLI ?
3) Are there any negative impacts to using SQLNCLI ?
Thanks.
The whole idea behind SQLNCLI is to provide updated capability to existing
ADO classic (COM-based) applications. I understand that it's supposed to
work with older versions, but if history is any teacher, I would thoroughly
test before betting my socks on it. See
http://blogs.msdn.com/dataaccess/archive/2005/07/29/445147.aspx for more
info.
Frankly, I would think (hard) about migrating to ADO.NET and the SqlClient
provider that can reach all current (and future?) versions of SQL
Server--and a lot faster with fewer surprises.
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
------
"Michael Tissington" <mtissington@.newsgroup.nospam> wrote in message
news:OOhR4DSXHHA.1636@.TK2MSFTNGP02.phx.gbl...
>I have a large application that currently uses SQLOLEDB as the provider.
> The application needs to connect to SQL 7 thru SQL 2005.
> I have tested changing the provider to SQLNCLI and everything still seems
> to work.
> I have a couple of questions ..
> 1) Is it really this easy ?
> 2) Can I still connect to SQL 7 and SQL 2000 using SQLNCLI ?
> 3) Are there any negative impacts to using SQLNCLI ?
> Thanks.
>
|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Change existing application to use sqlncli

I have a large application that currently uses SQLOLEDB as the provider.
The application needs to connect to SQL 7 thru SQL 2005.
I have tested changing the provider to SQLNCLI and everything still seems to
work.
I have a couple of questions ..
1) Is it really this easy ?
2) Can I still connect to SQL 7 and SQL 2000 using SQLNCLI ?
3) Are there any negative impacts to using SQLNCLI ?
Thanks.The whole idea behind SQLNCLI is to provide updated capability to existing
ADO classic (COM-based) applications. I understand that it's supposed to
work with older versions, but if history is any teacher, I would thoroughly
test before betting my socks on it. See
http://blogs.msdn.com/dataaccess/ar.../29/445147.aspx for more
info.
Frankly, I would think (hard) about migrating to ADO.NET and the SqlClient
provider that can reach all current (and future?) versions of SQL
Server--and a lot faster with fewer surprises.
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
----
---
"Michael Tissington" <mtissington@.newsgroup.nospam> wrote in message
news:OOhR4DSXHHA.1636@.TK2MSFTNGP02.phx.gbl...
>I have a large application that currently uses SQLOLEDB as the provider.
> The application needs to connect to SQL 7 thru SQL 2005.
> I have tested changing the provider to SQLNCLI and everything still seems
> to work.
> I have a couple of questions ..
> 1) Is it really this easy ?
> 2) Can I still connect to SQL 7 and SQL 2000 using SQLNCLI ?
> 3) Are there any negative impacts to using SQLNCLI ?
> Thanks.
>|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.

Change Edition of existing isntance?

I have a named instance of SQL Server 2005 Developer Edition. I want to install VS Team System Foundation Server on the same box, but this needs a default instance of Standard or Enterprise edition. I installed the Standard version alongside the existing Developer instance but now I can't start SSIS and without it VSTS Foundation Server won't install.

I have tried to uninstall SSIS and reisntall it from both the Standard Edition and Developer edition media, but it makes no difference, the service still won't start.

Could the SSIS failure be due to having different editions on the same box? If so, is it possible to change the edition of the Developer instance to Standard (or to change them both to Enterprise edition) without having to uninstall and start all over again. The developer instance is used by several team members, so I don't want to have to uninstall it if I can help.

Any ideas anyone?

No. I should not be something like that as different SQL Server 2005 SKUs(Editions) can be installed side by side successfully provided that they have unique instance names. What kind of processor architecture of your machine? If you install 32-bit and 64-bit SQL Server 2005 on an x64 machines side by side, which is also supported, then it is possible that only one version of SSIS works at one time.|||Both isntances are 32-bit. Only differnece is that named instance is Developer Edition and default instance is Standard Edition
|||

Having two different editions (Developer and Standard) on the same box should not cause SSIS to be unable to start. Do the event logs have any messages? When you installed the second instance, did you change any of the logon service accounts?

Thanks,
Sam Lester (MSFT)

|||

Here is a lengthy thread on SSIS not starting. Can you read through it and see if it applies to your situation?

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

Thanks,
Sam

sql

Change Edition of existing isntance?

I have a named instance of SQL Server 2005 Developer Edition. I want to install VS Team System Foundation Server on the same box, but this needs a default instance of Standard or Enterprise edition. I installed the Standard version alongside the existing Developer instance but now I can't start SSIS and without it VSTS Foundation Server won't install.

I have tried to uninstall SSIS and reisntall it from both the Standard Edition and Developer edition media, but it makes no difference, the service still won't start.

Could the SSIS failure be due to having different editions on the same box? If so, is it possible to change the edition of the Developer instance to Standard (or to change them both to Enterprise edition) without having to uninstall and start all over again. The developer instance is used by several team members, so I don't want to have to uninstall it if I can help.

Any ideas anyone?

No. I should not be something like that as different SQL Server 2005 SKUs(Editions) can be installed side by side successfully provided that they have unique instance names. What kind of processor architecture of your machine? If you install 32-bit and 64-bit SQL Server 2005 on an x64 machines side by side, which is also supported, then it is possible that only one version of SSIS works at one time.|||Both isntances are 32-bit. Only differnece is that named instance is Developer Edition and default instance is Standard Edition|||

Having two different editions (Developer and Standard) on the same box should not cause SSIS to be unable to start. Do the event logs have any messages? When you installed the second instance, did you change any of the logon service accounts?

Thanks,
Sam Lester (MSFT)

|||

Here is a lengthy thread on SSIS not starting. Can you read through it and see if it applies to your situation?

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

Thanks,
Sam

Change DataSource between Development and Production

I have some existing .NET app, and so I'd like to integrate the RS w/
them.
Reports created by RS is very impressive, now I need to think about
integration. Two choices for me:
1. URL Access
2. Web Services
Option 1 is the fastest and efficient way, has the followings:
1) DataSource in dev and prod are different, doesn't seem like easy to
change them w/ modifying in the project.
2) SessionID, UserName and Password and other parameters are exposed in
URL unless using SSL, otherwise not secure.
Option 2 needs more coding just like a normal .NET projects but w/o
security concerns, still have to worry about changing DataSource stuff.
For example for .rdl
<DataSourceID>413d324e-1ddf-4c4d-8f2e-2dd50bd8d4ac</DataSourceID>
Our existing .NET has its own web.config file works like a .ini, we put
in all data source connection there, when move to production,
Production Release team needs to change SQL box name, UserID and
password inside of the web.config. Now, how do they change them in
.rdl?
Thanks in advance.Couple of things to consider. First, datasource should be a non-problem if
you use shared datasources (which I recommend). The shared datasource has to
have the same name in both production and dev but that is it. The default
when you deploy is to not overwrite the datasource so once you set it up in
production it will be undisturbed. Second, I suggest using a specific
username and password for retrieving the data from wherever you are getting
it. I create a special readonly user that is used by RS only. This is great
for security since it is readonly and you benefit from connection polling
which you would not if the user account of the user requesting the report is
used. If you report needs to include the user in the where clause (or you
need to use a filter with the username) then user the global variable
User!UserID.
As I said, rdl does not change at all for the datasource.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
<hifchan@.yahoo.com> wrote in message
news:1108586517.346073.261520@.c13g2000cwb.googlegroups.com...
> I have some existing .NET app, and so I'd like to integrate the RS w/
> them.
> Reports created by RS is very impressive, now I need to think about
> integration. Two choices for me:
> 1. URL Access
> 2. Web Services
> Option 1 is the fastest and efficient way, has the followings:
> 1) DataSource in dev and prod are different, doesn't seem like easy to
> change them w/ modifying in the project.
> 2) SessionID, UserName and Password and other parameters are exposed in
> URL unless using SSL, otherwise not secure.
> Option 2 needs more coding just like a normal .NET projects but w/o
> security concerns, still have to worry about changing DataSource stuff.
> For example for .rdl
> <DataSourceID>413d324e-1ddf-4c4d-8f2e-2dd50bd8d4ac</DataSourceID>
> Our existing .NET has its own web.config file works like a .ini, we put
> in all data source connection there, when move to production,
> Production Release team needs to change SQL box name, UserID and
> password inside of the web.config. Now, how do they change them in
> .rdl?
> Thanks in advance.
>|||Bruce,
You meant
<DataSourceID>413d324e-1ddf-4c4d-8f2e-2dd50bd8d4ac</DataSourceID> will
not matter as long as the .rdl reference the same <Name> only?|||Yes this is correct. The data source ID is just an internal id used by
report designer only. That is also the reason why the DataSourceID element
is in the report designer namespace: <rd:DataSourceID>. The report server
ignores these ids.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
<hifchan@.yahoo.com> wrote in message
news:1108657866.193950.180150@.g14g2000cwa.googlegroups.com...
> Bruce,
> You meant
> <DataSourceID>413d324e-1ddf-4c4d-8f2e-2dd50bd8d4ac</DataSourceID> will
> not matter as long as the .rdl reference the same <Name> only?
>

Change Dataset on existing reports

Hi All,

I have a set of reports already built. I want to use the same reports using a different dataset(different server). I have tried to add a new datasource and data set, then set the data tab of the report to reflect the new dataset. All seems fine, but when I deploy the report, it is deployed with the new dataset into the new location, but the data displayed is from the original dataset. Any pointers?

regards

Have you tried getting out of report manager and going back in? Refreshing. Something sounds like it's pointing to the wrong server. The report is doing what you have told it to do. Check your data sources and check your report on the report manager to see where the data source is pointing. It may be pointing somewhere else.|||I have done what you suggested. Still not working. Odd thing is, if I go to the report properties in the RS website and look at the data sources, both data sources are listed. Let me be clear on what I am looking for here, so I dont waste anyones time. I have 2databases I want to access and have 1 RS web server. I want to set up 2 folders on the RS and in each of the folders are the same reports , pointing at different data sources. So in the report designer I created a report and deployed it for datasource#1. Then I added a new datasource (#2) and created a new dataset with the same query from #1, but used #2 as the datasource. This is now selectable from the dropdown on the data tab in the designer. When I run the query from the data tab in the designer, the results are correct. They reflect the data from the selected datasource. But in deployed and preview mode, the report is reporting the data from the original datasource.|||

Did you delete the original dataset from this. if not delete the original data set and keep only the new dataset you added.

Then select the layout tab and select the table properties and change the Datasetname to the new dataset name.

|||Problem is, I am trying to d this and keep both datasets. Any time I have to deploy report updates, I have to push it for both datasources. I would rather not delete and creaqte for each deployment. But, if thats the way it is... thats the way it is.|||

Often the report properties persist from old versions. You may want to delete the report on report server using the manager webpage, and then repost the report. As long as you are using two different named datasources, you should be ok. If the datasources have the same name, make sure the deploy path for the second one is going to a different folder. Remember that the deploy path for the report and datasource are separate properties of the VS2005 project.

Hope that helps.

sql

Change Column Ordinal Position with T-SQL

Hi Friends ...
I need to change the ordinal position of a column in an existing table using
T-SQL through QA - anybody know the proprer syntax?
Thanks for your help ...Hi,
If you don't use bad syntax as "select * from MyTable", you shouldn't have
tou deal with ordinal position.
Anyway, there's no solution to change ordinal position, because there
shouldn't be any need to.
The only way to do this is to drop - recreate the table.
JN.
"bill_morgan" wrote:

> Hi Friends ...
> I need to change the ordinal position of a column in an existing table usi
ng
> T-SQL through QA - anybody know the proprer syntax?
> Thanks for your help ...|||If you want to see the script involved with your table,
do this in Enterprise Manager but before saving the changes,
click on the icon to see the script.
"Jean-Nicolas BERGER" <JeanNicolasBERGER@.discussions.microsoft.com> wrote in
message news:09BBB5B8-2830-474F-BC84-471F33DAFCA7@.microsoft.com...
> Hi,
> If you don't use bad syntax as "select * from MyTable", you shouldn't have
> tou deal with ordinal position.
> Anyway, there's no solution to change ordinal position, because there
> shouldn't be any need to.
> The only way to do this is to drop - recreate the table.
> JN.
> "bill_morgan" wrote:
>|||> I need to change the ordinal position of a column in an existing table
> using
> T-SQL through QA - anybody know the proprer syntax?
There is no such thing, at least not in a single statement.
http://www.aspfaq.com/2528|||Actually, the local admin changed orginal position, and I wanted to make sur
e
there wasn't syntax in this regard I hadn't learned yet. He must have done i
t
through EM.
Thanks ...
"Jean-Nicolas BERGER" wrote:
> Hi,
> If you don't use bad syntax as "select * from MyTable", you shouldn't have
> tou deal with ordinal position.
> Anyway, there's no solution to change ordinal position, because there
> shouldn't be any need to.
> The only way to do this is to drop - recreate the table.
> JN.
> "bill_morgan" wrote:
>|||Thanks for your response ... mainly wanted to see if it's possible in QA
rather than having to use EM. Now I know ...
Thanks again ...
"Raymond D'Anjou" wrote:

> If you want to see the script involved with your table,
> do this in Enterprise Manager but before saving the changes,
> click on the icon to see the script.
> "Jean-Nicolas BERGER" <JeanNicolasBERGER@.discussions.microsoft.com> wrote
in
> message news:09BBB5B8-2830-474F-BC84-471F33DAFCA7@.microsoft.com...
>
>|||Don't forget, this is easily done on an empty table.
On a table with millions of rows, you can imagine the time this operation
can take.
But as others have said, column order should not be a concern.
"bill_morgan" <billmorgan@.discussions.microsoft.com> wrote in message
news:77D61C48-5CF5-4502-B35C-11D8ACA160B6@.microsoft.com...
> Actually, the local admin changed orginal position, and I wanted to make
> sure
> there wasn't syntax in this regard I hadn't learned yet. He must have done
> it
> through EM.
> Thanks ...
> "Jean-Nicolas BERGER" wrote:
>

Change collation for an existing database

Hi !
I would like to know how to change the collation of an imported database. I
tried something with DTS, but I'm quiet a beginner with this tool. So if
anyone could help me on this one, this would be great ! ;-)
Thanks in advance !
I just forgot to say that I use SQL 2000 SP4.
"Vincent D." wrote:

> Hi !
> I would like to know how to change the collation of an imported database. I
> tried something with DTS, but I'm quiet a beginner with this tool. So if
> anyone could help me on this one, this would be great ! ;-)
> Thanks in advance !
|||Seems that similar issue has been handled recently. Pls have a look at the 2
posts of 28/11/05.
http://www.microsoft.com/technet/com...8-B1DE-B6EB49F
31B7E&dglist=&ptlist=&exp=&sloc=en-us
"Vincent D." wrote:
[vbcol=seagreen]
> I just forgot to say that I use SQL 2000 SP4.
> "Vincent D." wrote:

Change collation for an existing database

Hi !
I would like to know how to change the collation of an imported database. I
tried something with DTS, but I'm quiet a beginner with this tool. So if
anyone could help me on this one, this would be great ! ;-)
Thanks in advance !I just forgot to say that I use SQL 2000 SP4. :)
"Vincent D." wrote:
> Hi !
> I would like to know how to change the collation of an imported database. I
> tried something with DTS, but I'm quiet a beginner with this tool. So if
> anyone could help me on this one, this would be great ! ;-)
> Thanks in advance !|||Seems that similar issue has been handled recently. Pls have a look at the 2
posts of 28/11/05.
http://www.microsoft.com/technet/community/newsgroups/dgbrowser/en-us/default.mspx?query=collation&dg=microsoft.public.sqlserver.server&cat=en-us-technet-sqlserv&lang=en&cr=US&pt=261BA873-F3AB-420E-96D6-E3004596A551&catlist=328BAFD2-1A81-4558-B1DE-B6EB49F31B7E&dglist=&ptlist=&exp=&sloc=en-us
"Vincent D." wrote:
> I just forgot to say that I use SQL 2000 SP4. :)
> "Vincent D." wrote:
> > Hi !
> >
> > I would like to know how to change the collation of an imported database. I
> > tried something with DTS, but I'm quiet a beginner with this tool. So if
> > anyone could help me on this one, this would be great ! ;-)
> >
> > Thanks in advance !

Change collation for an existing database

Hi !
I would like to know how to change the collation of an imported database. I
tried something with DTS, but I'm quiet a beginner with this tool. So if
anyone could help me on this one, this would be great ! ;-)
Thanks in advance !I just forgot to say that I use SQL 2000 SP4.
"Vincent D." wrote:

> Hi !
> I would like to know how to change the collation of an imported database.
I
> tried something with DTS, but I'm quiet a beginner with this tool. So if
> anyone could help me on this one, this would be great ! ;-)
> Thanks in advance !|||Seems that similar issue has been handled recently. Pls have a look at the 2
posts of 28/11/05.
http://www.microsoft.com/technet/co...r />
E-B6EB49F
31B7E&dglist=&ptlist=&exp=&sloc=en-us
"Vincent D." wrote:
[vbcol=seagreen]
> I just forgot to say that I use SQL 2000 SP4.
> "Vincent D." wrote:
>