changing the sync_type option of a subscription

If I create a subscription with a sync_type set to 'replication support only' is it possible to change that option to 'automatic' down the road without destroying and recreating the
subscription?

-mike

You can look into the following articles on changing the subscription property.

How to: View and Modify Push Subscription Properties (SQL Server Management Studio)

http://msdn2.microsoft.com/en-us/library/ms151741.aspx

How to: View and Modify Push Subscription Properties (Replication Transact-SQL Programming)

http://msdn2.microsoft.com/en-us/library/ms147323.aspx

However, I don't see @.sync_type as one of the property you can change.

Do you mind I ask why you want to change from "automatic" to "replication support only" later? What is your scenario?

Gary

|||Automatic appears (and I need to do more reading on this) to give you a bit more flexability when adding items to a publication. For example, I have a publication and subscription (w/ sync_type = automatic) up and running and now I want to add another object to that publication. Once I've added the object all I need to do is regenerate the snapshot and that object is replicated to the subscriber.

So, that being said, I have a very large database that takes around four hours to deliver the initial snapshot. Due to the size and time of delivering that snapshot, it makes sense to create a publication with a subscription set to 'replication support only'. But down the road I may want to add objects to that publication and don't want to have to destroy and recreate the subscription and have to deliver the snapshot.|||

I guess no ..i have the same scenario still lurking and i am sure we have to drop and re-create the subscription to chnage the setting.

Script out replication run the script for subscription you want to change @.sync_type or u may use wizard to create the article and subscription for the same.

changing the sync_type option of a subscription

If I create a subscription with a sync_type set to 'replication support only' is it possible to change that option to 'automatic' down the road without destroying and recreating the
subscription?

-mike

You can look into the following articles on changing the subscription property.

How to: View and Modify Push Subscription Properties (SQL Server Management Studio)

http://msdn2.microsoft.com/en-us/library/ms151741.aspx

How to: View and Modify Push Subscription Properties (Replication Transact-SQL Programming)

http://msdn2.microsoft.com/en-us/library/ms147323.aspx

However, I don't see @.sync_type as one of the property you can change.

Do you mind I ask why you want to change from "automatic" to "replication support only" later? What is your scenario?

Gary

|||Automatic appears (and I need to do more reading on this) to give you a bit more flexability when adding items to a publication. For example, I have a publication and subscription (w/ sync_type = automatic) up and running and now I want to add another object to that publication. Once I've added the object all I need to do is regenerate the snapshot and that object is replicated to the subscriber.

So, that being said, I have a very large database that takes around four hours to deliver the initial snapshot. Due to the size and time of delivering that snapshot, it makes sense to create a publication with a subscription set to 'replication support only'. But down the road I may want to add objects to that publication and don't want to have to destroy and recreate the subscription and have to deliver the snapshot.|||

I guess no ..i have the same scenario still lurking and i am sure we have to drop and re-create the subscription to chnage the setting.

Script out replication run the script for subscription you want to change @.sync_type or u may use wizard to create the article and subscription for the same.

Changing the default location of a new database

If you enter "Create Database test", the database files (mdf file & log file) are created, by default, in:-
C:\Program Files\Microsoft SQL Server\MSSQL\Data
I want to change that to:-
D:\Database Files
I sucessfully moved the model database to this location (using the instructions in BOL) assuming that all new databases would now get created in the same location, but they don't. They still get created in:
C:\Program Files\Microsoft SQL Server\MSSQL\Data

So how do I change the default?
(It's not satisfactory to have to move each database after it's created)

Thanks, Andy Abelon 2005, this is a setting that you can change in SSMS via the Server Properties dialog. It's probably the same in 2000, but I can't verify as I don't have EM installed.

select the "database settings" tab and you can change the path were data and log files get created by default.

Under the covers, this setting is stored in the registry, here:

HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\<MSSQL.inst_number>\MSSQLServer

in the DefaultData and DefaultLog values.|||Yes, that works! Thanks!

Changing the DataSource at Runtime

I have to create a script for changing the datasource at runtime.

Here is my screnario, While development I am using Data source name called "DevDatasource1" and when I am deploying it to other evnironment the datasource name will change let us say "QADatasource".

I have to create a script for changing the datasource(i.e. DevDatasource1 to QADatasource). How I can achieve I this using the setItemdatasource?

You can also set a parameter in your report to accept the specific database or source and use this parameter within your datasource connection string.

Within your report:

My connection string looks like this: ="Data Source=nt33;Initial Catalog=" & Parameters!Database.Value & ";"

This allows me to change the database on the fly, but could change the entire connection string including server.

Location for the code would be Data Tab --> Dataset properties --> Datasource editor --> Connection String:

The bonus is that no matter where the report is run you have the ability to change the datasource.

My parameter is setup to load al list of available databases for the user.

Not what you were looking for specifcally, but it might help.

|||

hi simles

thanks for your reply.

Here is my problem. While deploying my reports on the other server we don't want to create the datasource. Already in the deployment server datasources are created.

what we have to do his while deploying the reports we have to map the existing datasources to the deploying rdl files. Can you explain me how we can achieve this using reports scripts.

|||

So, you have a pre-existing datasource on the server, and you want your newly-published report to use IT instead of the datasource the report was created with, right?

If so, try SetItemDataSources():

http://msdn2.microsoft.com/ru-ru/library/microsoft.wssux.reportingserviceswebservice.rsmanagementservice2005.reportingservice2005.setitemdatasources.aspx

|||

Russell,

Thanks for your reply.

I tried with that setitemdatasources, but it not setting the new datasource name for the rdl.

I don't know were i am making the mistake. please look into the code and correct me if the code is wrong.

Dim dataSources() As DataSource

dataSources = rs.GetItemDataSources("/Reports/Test Matrix")

Dim NewdataSources As new DataSource

Dim dsref As New DataSourceReference

dsref.Reference = "/Data Sources/TestDatasource"

NewdataSources.Item = CType(dsref,DataSourceDefinitionOrReference)

NewdataSources.Name = "NewDatasource"

dataSources(0) = NewdataSources

rs.SetItemDataSources("/Reports/Test Matrix", dataSources)

Simran Raj

Changing the DataSource at Runtime

I have to create a script for changing the datasource at runtime.

Here is my screnario, While development I am using Data source name called "DevDatasource1" and when I am deploying it to other evnironment the datasource name will change let us say "QADatasource".

I have to create a script for changing the datasource(i.e. DevDatasource1 to QADatasource). How I can achieve I this using the setItemdatasource?

You can also set a parameter in your report to accept the specific database or source and use this parameter within your datasource connection string.

Within your report:

My connection string looks like this: ="Data Source=nt33;Initial Catalog=" & Parameters!Database.Value & ";"

This allows me to change the database on the fly, but could change the entire connection string including server.

Location for the code would be Data Tab --> Dataset properties --> Datasource editor --> Connection String:

The bonus is that no matter where the report is run you have the ability to change the datasource.

My parameter is setup to load al list of available databases for the user.

Not what you were looking for specifcally, but it might help.

|||

hi simles

thanks for your reply.

Here is my problem. While deploying my reports on the other server we don't want to create the datasource. Already in the deployment server datasources are created.

what we have to do his while deploying the reports we have to map the existing datasources to the deploying rdl files. Can you explain me how we can achieve this using reports scripts.

|||

So, you have a pre-existing datasource on the server, and you want your newly-published report to use IT instead of the datasource the report was created with, right?

If so, try SetItemDataSources():

http://msdn2.microsoft.com/ru-ru/library/microsoft.wssux.reportingserviceswebservice.rsmanagementservice2005.reportingservice2005.setitemdatasources.aspx

|||

Russell,

Thanks for your reply.

I tried with that setitemdatasources, but it not setting the new datasource name for the rdl.

I don't know were i am making the mistake. please look into the code and correct me if the code is wrong.

Dim dataSources() As DataSource

dataSources = rs.GetItemDataSources("/Reports/Test Matrix")

Dim NewdataSources As new DataSource

Dim dsref As New DataSourceReference

dsref.Reference = "/Data Sources/TestDatasource"

NewdataSources.Item = CType(dsref,DataSourceDefinitionOrReference)

NewdataSources.Name = "NewDatasource"

dataSources(0) = NewdataSources

rs.SetItemDataSources("/Reports/Test Matrix", dataSources)

Simran Raj

sql

Changing table names. Navision 3.7 - need help

Hello
I am trying to create a report set for a company with changing table names. Meaning that for each company the erp system is in operation for, the table names are different.
Mening that for one company the GL table is named like this:
[Company X$GL] while for another company the GL table is like this:
[Company Y$GL]
Should I use a stored procedure to handle it, a function... any good ideas before I start experimenting..
I have the possibility of getting the company name from the system, so thats not a problem...
hope for some good advice.David,
You could acheive this by using two reports; The first would get the appropriate company/table name, this report would contain a sub-report, passing the company/table name as a parameter. The sub-report would use a dynamic query, built from the parameter, such as;
="Select * From " & Parameter!Company.Value
You may be able to do it from one report, depending on the order in which the datasets are handled.
Hope that helps
"david" wrote:
> Hello
> I am trying to create a report set for a company with changing table names. Meaning that for each company the erp system is in operation for, the table names are different.
> Mening that for one company the GL table is named like this:
> [Company X$GL] while for another company the GL table is like this:
> [Company Y$GL]
> Should I use a stored procedure to handle it, a function... any good ideas before I start experimenting..
> I have the possibility of getting the company name from the system, so thats not a problem...
> hope for some good advice.|||Terribly sorry for having bothered you all. I was missing a white space in my table name.... *ashamed beyond belief*
"david" wrote:
> Now I tried going another way. I found a text replacing program, that simply runs through all the rdl files and replaces the company X string with the company Y string, so as to fit the database tables names.
> Now when i open the reports and try to open a dataset in the data view:
> all tables are flat, and i can only mark the All columns. When I try running the command, i get the following error:
> ADO error: Invalid object name 'compname$Value Entry'.
> Invalid object name 'Compname@.Customer'.
> Staement(s) could not be prepared. Deferred prepare could not be completed.
>
> heeeeeeeelp
> "david" wrote:
> > Thanks for the reply.
> > I will elaborate a little more on the problem.
> >
> > a Select to get all accounts in account ledger is for example:
> >
> > SELECT No_, Name
> > FROM [Company X$G_L Account]
> >
> > There fore i need to concatenate the company name with the table name. And table names may have blank spaces, so I need the [ ]
> >
> > Besides that, in the books online for reporting services it specifically says that everything need to be inline, and I have som loong select statements... what 2 do?
> >
> >
> > "Chris McGuigan" wrote:
> >
> > > David,
> > > You could acheive this by using two reports; The first would get the appropriate company/table name, this report would contain a sub-report, passing the company/table name as a parameter. The sub-report would use a dynamic query, built from the parameter, such as;
> > > ="Select * From " & Parameter!Company.Value
> > > You may be able to do it from one report, depending on the order in which the datasets are handled.
> > >
> > > Hope that helps
> > >
> > > "david" wrote:
> > >
> > > > Hello
> > > > I am trying to create a report set for a company with changing table names. Meaning that for each company the erp system is in operation for, the table names are different.
> > > >
> > > > Mening that for one company the GL table is named like this:
> > > > [Company X$GL] while for another company the GL table is like this:
> > > > [Company Y$GL]
> > > > Should I use a stored procedure to handle it, a function... any good ideas before I start experimenting..
> > > >
> > > > I have the possibility of getting the company name from the system, so thats not a problem...
> > > >
> > > > hope for some good advice.|||David,
The 1 line restrictions was removed with SP1. Any way by 1 line they really mean one sentence, so;
="SELECT * " &
"FROM [" & Parameters!Company.Value & " X$G_L Account]"
would work OK.
Regards
Chris McGuigan
"david" wrote:
> Thanks for the reply.
> I will elaborate a little more on the problem.
> a Select to get all accounts in account ledger is for example:
> SELECT No_, Name
> FROM [Company X$G_L Account]
> There fore i need to concatenate the company name with the table name. And table names may have blank spaces, so I need the [ ]
> Besides that, in the books online for reporting services it specifically says that everything need to be inline, and I have som loong select statements... what 2 do?
>
> "Chris McGuigan" wrote:
> > David,
> > You could acheive this by using two reports; The first would get the appropriate company/table name, this report would contain a sub-report, passing the company/table name as a parameter. The sub-report would use a dynamic query, built from the parameter, such as;
> > ="Select * From " & Parameter!Company.Value
> > You may be able to do it from one report, depending on the order in which the datasets are handled.
> >
> > Hope that helps
> >
> > "david" wrote:
> >
> > > Hello
> > > I am trying to create a report set for a company with changing table names. Meaning that for each company the erp system is in operation for, the table names are different.
> > >
> > > Mening that for one company the GL table is named like this:
> > > [Company X$GL] while for another company the GL table is like this:
> > > [Company Y$GL]
> > > Should I use a stored procedure to handle it, a function... any good ideas before I start experimenting..
> > >
> > > I have the possibility of getting the company name from the system, so thats not a problem...
> > >
> > > hope for some good advice.sql

changing owner of stored procedure

Is there a way to change the owner of a stored procedure?
I used Enterprise Manager to create a stored procedure. But since it was
created under my login name, other users are denied permission. Rather than
explicitly giving permission to everyone, I would like to change the owner
to 'dbo'.
How?
Lisasp_changeobjectowner
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Lisa Pearlson" <no@.spam.plz> wrote in message
news:ubuJHdw7DHA.2924@.tk2msftngp13.phx.gbl...
> Is there a way to change the owner of a stored procedure?
> I used Enterprise Manager to create a stored procedure. But since it was
> created under my login name, other users are denied permission. Rather
than
> explicitly giving permission to everyone, I would like to change the owner
> to 'dbo'.
> How?
> Lisa
>

changing owner of stored procedure

Is there a way to change the owner of a stored procedure?
I used Enterprise Manager to create a stored procedure. But since it was
created under my login name, other users are denied permission. Rather than
explicitly giving permission to everyone, I would like to change the owner
to 'dbo'.
How?
Lisasp_changeobjectowner
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Lisa Pearlson" <no@.spam.plz> wrote in message
news:ubuJHdw7DHA.2924@.tk2msftngp13.phx.gbl...
> Is there a way to change the owner of a stored procedure?
> I used Enterprise Manager to create a stored procedure. But since it was
> created under my login name, other users are denied permission. Rather
than
> explicitly giving permission to everyone, I would like to change the owner
> to 'dbo'.
> How?
> Lisa
>

Changing order of columns at run time

Here's the scenario:
I want to be able to create a report (typically these reports are
"table like" reports ... in other words, several columns with a list
of data underneath) and I want to be able to re-order the columns (not
the rows) in these reports at runtime.
So, for instance, if the "report design" is as follows:
COLUMNA COLUMNB COLUMNC
... data goes here ...
... I'd like to be able to pass a parameter in to the report that may
be, for instance, "COLUMNC,COLUMNB,COLUMNA" ... and report columns
would re-order, like follows:
COLUMNC COLUMNB COLUMNA
... data goes here ...
... I don't really care what report designer object is used (Table,
Matrix, etc) ... but I don't yet see a good way to do this. It seems
like the Matrix object might be able to do this via the ColumnGroups
"Sorting" tab ... but I don't seem to be able to come up with an
expression that can make this occur.
Any help is greatly appreciated.
Thanks,
RichardOn Apr 27, 9:39 am, Richard.G...@.Pa-Tech.Com wrote:
> Here's the scenario:
> I want to be able to create a report (typically these reports are
> "table like" reports ... in other words, several columns with a list
> of data underneath) and I want to be able to re-order the columns (not
> the rows) in these reports at runtime.
> So, for instance, if the "report design" is as follows:
> COLUMNA COLUMNB COLUMNC
> ... data goes here ...
> ... I'd like to be able to pass a parameter in to the report that may
> be, for instance, "COLUMNC,COLUMNB,COLUMNA" ... and report columns
> would re-order, like follows:
> COLUMNC COLUMNB COLUMNA
> ... data goes here ...
> ... I don't really care what report designer object is used (Table,
> Matrix, etc) ... but I don't yet see a good way to do this. It seems
> like the Matrix object might be able to do this via the ColumnGroups
> "Sorting" tab ... but I don't seem to be able to come up with an
> expression that can make this occur.
> Any help is greatly appreciated.
> Thanks,
> Richard
If you are using a matrix report, you can control the column layout
(assuming you know the column names prior to runtime) by adding spaces
in front of the names of the columns (like values in the pivot column)
and ordering/sorting by ascending order. If you are using a table
control, you can allow the user to select a sort order (asc/desc) in
the report and then set the columns in the returned resultset
accordingly. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Just Like the report was built by Report Builder..
when click title of column ,the order will be change,right?
how to do it in vs2005?
"EMartinez" <emartinez.pr1@.gmail.com>
':1177733247.239100.15830@.n59g2000hsh.googlegroups.com...
> On Apr 27, 9:39 am, Richard.G...@.Pa-Tech.Com wrote:
>> Here's the scenario:
>> I want to be able to create a report (typically these reports are
>> "table like" reports ... in other words, several columns with a list
>> of data underneath) and I want to be able to re-order the columns (not
>> the rows) in these reports at runtime.
>> So, for instance, if the "report design" is as follows:
>> COLUMNA COLUMNB COLUMNC
>> ... data goes here ...
>> ... I'd like to be able to pass a parameter in to the report that may
>> be, for instance, "COLUMNC,COLUMNB,COLUMNA" ... and report columns
>> would re-order, like follows:
>> COLUMNC COLUMNB COLUMNA
>> ... data goes here ...
>> ... I don't really care what report designer object is used (Table,
>> Matrix, etc) ... but I don't yet see a good way to do this. It seems
>> like the Matrix object might be able to do this via the ColumnGroups
>> "Sorting" tab ... but I don't seem to be able to come up with an
>> expression that can make this occur.
>> Any help is greatly appreciated.
>> Thanks,
>> Richard
>
> If you are using a matrix report, you can control the column layout
> (assuming you know the column names prior to runtime) by adding spaces
> in front of the names of the columns (like values in the pivot column)
> and ordering/sorting by ascending order. If you are using a table
> control, you can allow the user to select a sort order (asc/desc) in
> the report and then set the columns in the returned resultset
> accordingly. Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||That's called interactive sort. click on the textbox or table textbox
properties and select the interactive sort tab and set your values.
Amarnath
"renhanyue" wrote:
> Just Like the report was built by Report Builder..
> when click title of column ,the order will be change,right?
> how to do it in vs2005?
> "EMartinez" <emartinez.pr1@.gmail.com>
> ':1177733247.239100.15830@.n59g2000hsh.googlegroups.com...
> > On Apr 27, 9:39 am, Richard.G...@.Pa-Tech.Com wrote:
> >> Here's the scenario:
> >>
> >> I want to be able to create a report (typically these reports are
> >> "table like" reports ... in other words, several columns with a list
> >> of data underneath) and I want to be able to re-order the columns (not
> >> the rows) in these reports at runtime.
> >>
> >> So, for instance, if the "report design" is as follows:
> >>
> >> COLUMNA COLUMNB COLUMNC
> >> ... data goes here ...
> >>
> >> ... I'd like to be able to pass a parameter in to the report that may
> >> be, for instance, "COLUMNC,COLUMNB,COLUMNA" ... and report columns
> >> would re-order, like follows:
> >>
> >> COLUMNC COLUMNB COLUMNA
> >> ... data goes here ...
> >>
> >> ... I don't really care what report designer object is used (Table,
> >> Matrix, etc) ... but I don't yet see a good way to do this. It seems
> >> like the Matrix object might be able to do this via the ColumnGroups
> >> "Sorting" tab ... but I don't seem to be able to come up with an
> >> expression that can make this occur.
> >>
> >> Any help is greatly appreciated.
> >>
> >> Thanks,
> >> Richard
> >
> >
> > If you are using a matrix report, you can control the column layout
> > (assuming you know the column names prior to runtime) by adding spaces
> > in front of the names of the columns (like values in the pivot column)
> > and ordering/sorting by ascending order. If you are using a table
> > control, you can allow the user to select a sort order (asc/desc) in
> > the report and then set the columns in the returned resultset
> > accordingly. Hope this helps.
> >
> > Regards,
> >
> > Enrique Martinez
> > Sr. Software Consultant
> >
>
>|||The issue is that I'm trying to change is the order of the columns
themselves (left to right) ... not the data "within" the column (which
would be the "top to bottom").
Normally, these reports would be simple "table" reports ... the
"extra" feature that I need to give to my users is the ability specify
the order of the columns (left to right)
To further clarify, if the report where a simple SQL statement, the
report designer might specify:
SELECT A, B, C FROM SOMETABLE
... but I want to give the report "user" the ability to say
SELECT C, B, A FROM SOMETABLE

Changing number of data files

Hello,
Uri's solution will work, however you will have to drop
and re-create tables to add data to the filegroups.
This is because when you create a table (or index) you
also state which filegroup you will be saving your data to.
J

quote:

---

>--Original Message--
>I have a 225Gb database on a test server. It is currently

---

in one file and I

quote:

---

>would like to split it into several files smaller files.

---

What is the best

quote:

---

>way of doing this?
>thanks
>Gav
>
>.
>

---

If I create another data file is it possible to move data from one file to
the other? I currently have a 220Gb and a 50Gb file I would like to fill up
the 50Gb file so I can resize the 220Gb file. Unfortunately I don't have
much spare storage space so I'm not sure how else I would do it. Only other
thing I can think of doing is dropping the entire database and recreating it
with more files, then I could restore a backup version.
Gav
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:09fc01c3da8a$b5e91800$a301280a@.phx.gbl...[QUOTE]
> Hello,
> Uri's solution will work, however you will have to drop
> and re-create tables to add data to the filegroups.
> This is because when you create a table (or index) you
> also state which filegroup you will be saving your data to.
> J
>
> in one file and I
> What is the best|||Yes, but that sort of depends where your data file is. for
instance you can create a data file on a different drive /
server than the one your using now, but if you create it
on a different server you will need to watch your network
speed.
The problem is not going to be your data files as such,
but filling them.
When you create a table/index you explicitly say
in T-SQL which data file any data the table is going to
store saves to.
Creating extra data files does will not move the data.
I don't think (and please someone tell me if I'm wrong)
whether a backup will work as a backup has in it the
database stucture so if you restore it you also restore
the number of datafiles you had before i.e 1.
There is a way of saving some time, and that it to do with
indexes.
About 1 third of all storeage space is used by indexes
(both clustered and non clusted) so hat you could do is
BACKUP YOUR DATABASE
(Sorry bit paraniod about data)
1. Create a new datafile called indexes.
2. Under EM Database - all tasks - generate SQL
Create 2 scripts one to remove all indexes and primary keys
one to re create them.
3. Drop all you indexes
4. In the script that has the create for the indexes
perform a search and replace for all the old data file
name to the new one.
5. Execute the script.
This should move about 70+ gb to your new file.
J

quote:

---

>--Original Message--
>If I create another data file is it possible to move data

---

from one file to

quote:

---

>the other? I currently have a 220Gb and a 50Gb file I

---

would like to fill up

quote:

---

>the 50Gb file so I can resize the 220Gb file.

---

Unfortunately I don't have

quote:

---

>much spare storage space so I'm not sure how else I would

---

do it. Only other

quote:

---

>thing I can think of doing is dropping the entire

---

database and recreating it

quote:

---

>with more files, then I could restore a backup version.
>Gav
>"Julie" <anonymous@.discussions.microsoft.com> wrote in

---

message

quote:

---

>news:09fc01c3da8a$b5e91800$a301280a@.phx.gbl...
to.[QUOTE]
currently[QUOTE]
files.[QUOTE]
>
>.
>

---

|||Gav
As Julie says, if you change the database and then restore
an old backup you will end up with the structure of the
old database.
If you want to create a second data file (or more) and
populate it you have serveral options.
If you want them poulated evenly, rebuild all your
clustered indexes(or in the case where you don't have any
build a clustered index and the drop it). SQL Server will
spread your tables evenly for you and you should end up
with two near equal data files.
If you want to control size/placement create a second file
group. Make the second data file part of the second file
group. You can then move tables to the new file group from
the design table pane in Enterprise manager.
You could also create a new database and use dts to
transfer the data. You would need more space though to do
that.
Hope this helps
John

quote:

---

>--Original Message--
>Yes, but that sort of depends where your data file is.

---

for

quote:

---

>instance you can create a data file on a different

---

drive /

quote:

---

>server than the one your using now, but if you create it
>on a different server you will need to watch your network
>speed.
>The problem is not going to be your data files as such,
>but filling them.
>When you create a table/index you explicitly say
>in T-SQL which data file any data the table is going to
>store saves to.
>Creating extra data files does will not move the data.
>I don't think (and please someone tell me if I'm wrong)
>whether a backup will work as a backup has in it the
>database stucture so if you restore it you also restore
>the number of datafiles you had before i.e 1.
>There is a way of saving some time, and that it to do

---

with

quote:

---

>indexes.
>About 1 third of all storeage space is used by indexes
>(both clustered and non clusted) so hat you could do is
>BACKUP YOUR DATABASE
>(Sorry bit paraniod about data)
>1. Create a new datafile called indexes.
>2. Under EM Database - all tasks - generate SQL
>Create 2 scripts one to remove all indexes and primary

---

keys

quote:

---

>one to re create them.
>3. Drop all you indexes
>4. In the script that has the create for the indexes
>perform a search and replace for all the old data file
>name to the new one.
>5. Execute the script.
>This should move about 70+ gb to your new file.
>J
>
>
data[QUOTE]
>from one file to
>would like to fill up
>Unfortunately I don't have
would[QUOTE]
>do it. Only other
>database and recreating it
>message
data[QUOTE]
>to.
>currently
>files.
>.
>

---

Changing name on Sql server

I change the name on my SQL2005 server, after that i run sp_dropserver and
sp_addserver, but when i tru do delete som sql agent job or create new one
iam getting error:"An error has occurred while establishing a connection to
the server. When connecting to SQL Server 2005, this failure may be caused
by the fact that under the default settings SQL Server does not allow remote
connections. (provider: Named Pipes Provider, error: 40 - Could not open a
connection to SQL Server) (.Net SqlClient Data Provider)"
any help please
Did you restarted SQL when you changed SQL names?
And also one thing ... did you executed
"sp_addserver 'servername', local"?
"Viktor" <serguienkov@.hotmail.com> wrote in message
news:ef9Sza%23yHHA.1776@.TK2MSFTNGP03.phx.gbl...
>I change the name on my SQL2005 server, after that i run sp_dropserver and
>sp_addserver, but when i tru do delete som sql agent job or create new one
>iam getting error:"An error has occurred while establishing a connection to
>the server. When connecting to SQL Server 2005, this failure may be caused
>by the fact that under the default settings SQL Server does not allow
>remote connections. (provider: Named Pipes Provider, error: 40 - Could not
>open a connection to SQL Server) (.Net SqlClient Data Provider)"
> any help please
>

Changing name on Sql server

I change the name on my SQL2005 server, after that i run sp_dropserver and
sp_addserver, but when i tru do delete som sql agent job or create new one
iam getting error:"An error has occurred while establishing a connection to
the server. When connecting to SQL Server 2005, this failure may be caused
by the fact that under the default settings SQL Server does not allow remote
connections. (provider: Named Pipes Provider, error: 40 - Could not open a
connection to SQL Server) (.Net SqlClient Data Provider)"
any help pleaseDid you restarted SQL when you changed SQL names?
And also one thing ... did you executed
"sp_addserver 'servername', local"?
"Viktor" <serguienkov@.hotmail.com> wrote in message
news:ef9Sza%23yHHA.1776@.TK2MSFTNGP03.phx.gbl...
>I change the name on my SQL2005 server, after that i run sp_dropserver and
>sp_addserver, but when i tru do delete som sql agent job or create new one
>iam getting error:"An error has occurred while establishing a connection to
>the server. When connecting to SQL Server 2005, this failure may be caused
>by the fact that under the default settings SQL Server does not allow
>remote connections. (provider: Named Pipes Provider, error: 40 - Could not
>open a connection to SQL Server) (.Net SqlClient Data Provider)"
> any help please
>

Changing name on Sql server

I change the name on my SQL2005 server, after that i run sp_dropserver and
sp_addserver, but when i tru do delete som sql agent job or create new one
iam getting error:"An error has occurred while establishing a connection to
the server. When connecting to SQL Server 2005, this failure may be caused
by the fact that under the default settings SQL Server does not allow remote
connections. (provider: Named Pipes Provider, error: 40 - Could not open a
connection to SQL Server) (.Net SqlClient Data Provider)"
any help pleaseDid you restarted SQL when you changed SQL names?
And also one thing ... did you executed
"sp_addserver 'servername', local"?
"Viktor" <serguienkov@.hotmail.com> wrote in message
news:ef9Sza%23yHHA.1776@.TK2MSFTNGP03.phx.gbl...
>I change the name on my SQL2005 server, after that i run sp_dropserver and
>sp_addserver, but when i tru do delete som sql agent job or create new one
>iam getting error:"An error has occurred while establishing a connection to
>the server. When connecting to SQL Server 2005, this failure may be caused
>by the fact that under the default settings SQL Server does not allow
>remote connections. (provider: Named Pipes Provider, error: 40 - Could not
>open a connection to SQL Server) (.Net SqlClient Data Provider)"
> any help please
>

changing logical file names

Using SS2000 SP4. I want to create a new test database from an existing
database backup. This is the sql that I use:
RESTORE DATABASE SMC_Test
FROM disk = '\\Depts\Technology\SQLServer\Backups\Co
lu\SMS\smc_936.bak'
WITH RECOVERY,
MOVE 'SMC_Data' TO 'S:\SQL\Data\SMCLMS_Test_Data.mdf',
MOVE 'SMC_Log' TO 'L:\SQL\Logs\SMCLMS_Test_Log.ldf'
Is there a way to change the logical file names at the same time? In another
post I saw this sql:
USE master
go
ALTER DATABASE XXX MODIFY FILE
( NAME='XX_old Name',NEWNAME='XX_New Name' )
go
Do I have to run that after I restore the database or is there another way?
Thanks,
--
Dan D.You have to do it after the restore.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:1A8C3983-25E6-4762-A6D6-3C78BCC89114@.microsoft.com...
> Using SS2000 SP4. I want to create a new test database from an existing
> database backup. This is the sql that I use:
> RESTORE DATABASE SMC_Test
> FROM disk = '\\Depts\Technology\SQLServer\Backups\Co
lu\SMS\smc_936.bak'
> WITH RECOVERY,
> MOVE 'SMC_Data' TO 'S:\SQL\Data\SMCLMS_Test_Data.mdf',
> MOVE 'SMC_Log' TO 'L:\SQL\Logs\SMCLMS_Test_Log.ldf'
> Is there a way to change the logical file names at the same time? In anoth
er
> post I saw this sql:
> USE master
> go
> ALTER DATABASE XXX MODIFY FILE
> ( NAME='XX_old Name',NEWNAME='XX_New Name' )
> go
> Do I have to run that after I restore the database or is there another way
?
> Thanks,
> --
> Dan D.|||Thanks.
--
Dan D.
"Tibor Karaszi" wrote:

> You have to do it after the restore.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:1A8C3983-25E6-4762-A6D6-3C78BCC89114@.microsoft.com...
>
>

changing logical file names

Using SS2000 SP4. I want to create a new test database from an existing
database backup. This is the sql that I use:
RESTORE DATABASE SMC_Test
FROM disk = '\\Depts\Technology\SQLServer\Backups\Colu\SMS\smc_936.bak'
WITH RECOVERY,
MOVE 'SMC_Data' TO 'S:\SQL\Data\SMCLMS_Test_Data.mdf',
MOVE 'SMC_Log' TO 'L:\SQL\Logs\SMCLMS_Test_Log.ldf'
Is there a way to change the logical file names at the same time? In another
post I saw this sql:
USE master
go
ALTER DATABASE XXX MODIFY FILE
( NAME='XX_old Name',NEWNAME='XX_New Name' )
go
Do I have to run that after I restore the database or is there another way?
Thanks,
--
Dan D.You have to do it after the restore.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:1A8C3983-25E6-4762-A6D6-3C78BCC89114@.microsoft.com...
> Using SS2000 SP4. I want to create a new test database from an existing
> database backup. This is the sql that I use:
> RESTORE DATABASE SMC_Test
> FROM disk = '\\Depts\Technology\SQLServer\Backups\Colu\SMS\smc_936.bak'
> WITH RECOVERY,
> MOVE 'SMC_Data' TO 'S:\SQL\Data\SMCLMS_Test_Data.mdf',
> MOVE 'SMC_Log' TO 'L:\SQL\Logs\SMCLMS_Test_Log.ldf'
> Is there a way to change the logical file names at the same time? In another
> post I saw this sql:
> USE master
> go
> ALTER DATABASE XXX MODIFY FILE
> ( NAME='XX_old Name',NEWNAME='XX_New Name' )
> go
> Do I have to run that after I restore the database or is there another way?
> Thanks,
> --
> Dan D.|||Thanks.
--
Dan D.
"Tibor Karaszi" wrote:
> You have to do it after the restore.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:1A8C3983-25E6-4762-A6D6-3C78BCC89114@.microsoft.com...
> > Using SS2000 SP4. I want to create a new test database from an existing
> > database backup. This is the sql that I use:
> >
> > RESTORE DATABASE SMC_Test
> > FROM disk = '\\Depts\Technology\SQLServer\Backups\Colu\SMS\smc_936.bak'
> > WITH RECOVERY,
> > MOVE 'SMC_Data' TO 'S:\SQL\Data\SMCLMS_Test_Data.mdf',
> > MOVE 'SMC_Log' TO 'L:\SQL\Logs\SMCLMS_Test_Log.ldf'
> >
> > Is there a way to change the logical file names at the same time? In another
> > post I saw this sql:
> > USE master
> > go
> > ALTER DATABASE XXX MODIFY FILE
> > ( NAME='XX_old Name',NEWNAME='XX_New Name' )
> > go
> >
> > Do I have to run that after I restore the database or is there another way?
> >
> > Thanks,
> > --
> > Dan D.
>
>

Changing local variable inside query

/*Given*/
CREATE TABLE [_T1sub] (
[PK] [int] IDENTITY (1, 1) NOT NULL ,
[FK] [int] NULL ,
[St] [char] (2) NULL ,
[Wt] [int] NULL ,
CONSTRAINT [PK__T1sub] PRIMARY KEY CLUSTERED
(
[PK]
) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO _T1sub (FK,St,Wt) VALUES (1,'id',10)
INSERT INTO _T1sub (FK,St,Wt) VALUES (2,'nv',20)
INSERT INTO _T1sub (FK,St,Wt) VALUES (3,'wa',30)
/*
Is something like the following possible.
The point is to change the value of the variable
inside the query and use it in the calculated field.
This doesn't compile of course, but is there
a way to accomplish the same thing?
*/
DECLARE @.ndx int
SET @.ndx = 1
SELECT
(a.FK+ (CASE WHEN @.ndx > 0
THEN (SELECT @.ndx = b.Wt
FROM _T1sub b
WHERE b.Wt = a.Wt)
ELSE 0 END)
) as FKplusWT
FROM _T1sub a
/*Output would look like this:*/
FKplusWT
11
22
33
/*
I know, I can get this output just by adding
FK+WT. This is not about that.
This is about setting vars inside a query
*/
thanks, Otto Porter
On Sat, 02 Oct 2004 12:21:54 -0600, Otto Porter wrote:
(snip)
Hi Otto,
I just answered this question in comp.databases.ms-sqlserver. Please do
not post the same question independently to multiple newsgroups.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Changing local variable inside query

/*Given*/
CREATE TABLE [_T1sub] (
[PK] [int] IDENTITY (1, 1) NOT NULL ,
[FK] [int] NULL ,
[St] [char] (2) NULL ,
[Wt] [int] NULL ,
CONSTRAINT [PK__T1sub] PRIMARY KEY CLUSTERED
(
[PK]
) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO _T1sub (FK,St,Wt) VALUES (1,'id',10)
INSERT INTO _T1sub (FK,St,Wt) VALUES (2,'nv',20)
INSERT INTO _T1sub (FK,St,Wt) VALUES (3,'wa',30)
/*
Is something like the following possible.
The point is to change the value of the variable
inside the query and use it in the calculated field.
This doesn't compile of course, but is there
a way to accomplish the same thing?
*/
DECLARE @.ndx int
SET @.ndx = 1
SELECT
(a.FK+ (CASE WHEN @.ndx > 0
THEN (SELECT @.ndx = b.Wt
FROM _T1sub b
WHERE b.Wt = a.Wt)
ELSE 0 END)
) as FKplusWT
FROM _T1sub a
/*Output would look like this:*/
FKplusWT
--
11
22
33
/*
I know, I can get this output just by adding
FK+WT. This is not about that.
This is about setting vars inside a query
*/
thanks, Otto PorterOn Sat, 02 Oct 2004 12:21:54 -0600, Otto Porter wrote:
(snip)
Hi Otto,
I just answered this question in comp.databases.ms-sqlserver. Please do
not post the same question independently to multiple newsgroups.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Changing Database within a stored procedure

I need to create a stored procedure in the master database that can
access info to dynamically create a view in another database. It
doesn't seem like it should be very hard, but I can't get it to work.
Here's an example of what I want to do.

CREATE PROCEDURE create_view @.dbname sysname
AS
BEGIN
DECLARE @.query varchar(1000)
SELECT @.query = 'use ' + @.dbname + ' go CREATE VIEW ......'
EXEC(@.query)
END

In this case, I get an error with the word "go". Without it, I get a
"CREATE VIEW must be the first statement in a batch" error. I tried a
semicolon in place of "GO" but that didn't help either.

Thanks"Bruce" <sandell@.pacbell.net> wrote in message
news:595024a5.0404122312.e0cf00f@.posting.google.co m...
> I need to create a stored procedure in the master database that can
> access info to dynamically create a view in another database. It
> doesn't seem like it should be very hard, but I can't get it to work.
> Here's an example of what I want to do.
> CREATE PROCEDURE create_view @.dbname sysname
> AS
> BEGIN
> DECLARE @.query varchar(1000)
> SELECT @.query = 'use ' + @.dbname + ' go CREATE VIEW ......'
> EXEC(@.query)
> END
> In this case, I get an error with the word "go". Without it, I get a
> "CREATE VIEW must be the first statement in a batch" error. I tried a
> semicolon in place of "GO" but that didn't help either.
> Thanks

It would probably be easier to do this from a client-side script - it's easy
to pass the database name to osql.exe, for example. In addition, you may
want to rethink your approach slightly, as it would be better to implement a
controlled deployment process for your code, so you can take a view script
from your source control system and create it in any database you want. See
this link also:

http://www.sommarskog.se/dynamic_sql.html#Dyn_DB

But if you really need to do it in TSQL, then this is one way:

CREATE PROCEDURE create_view @.dbname sysname
AS
BEGIN
DECLARE @.cmd varchar(1000)
set @.cmd = 'osql -E -d ' + dbname + ' -Q "CREATE VIEW..."'
exec master..xp_cmdshell @.cmd, NO_OUTPUT
END

Simon|||"Bruce" <sandell@.pacbell.net> wrote in message
news:595024a5.0404122312.e0cf00f@.posting.google.co m...
> I need to create a stored procedure in the master database that can
> access info to dynamically create a view in another database. It
> doesn't seem like it should be very hard, but I can't get it to work.
> Here's an example of what I want to do.
> CREATE PROCEDURE create_view @.dbname sysname
> AS
> BEGIN
> DECLARE @.query varchar(1000)
> SELECT @.query = 'use ' + @.dbname + ' go CREATE VIEW ......'
> EXEC(@.query)
> END
> In this case, I get an error with the word "go". Without it, I get a
> "CREATE VIEW must be the first statement in a batch" error. I tried a
> semicolon in place of "GO" but that didn't help either.
> Thanks

It would probably be easier to do this from a client-side script - it's easy
to pass the database name to osql.exe, for example. In addition, you may
want to rethink your approach slightly, as it would be better to implement a
controlled deployment process for your code, so you can take a view script
from your source control system and create it in any database you want. See
this link also:

http://www.sommarskog.se/dynamic_sql.html#Dyn_DB

But if you really need to do it in TSQL, then this is one way:

CREATE PROCEDURE create_view @.dbname sysname
AS
BEGIN
DECLARE @.cmd varchar(1000)
set @.cmd = 'osql -E -d ' + dbname + ' -Q "CREATE VIEW..."'
exec master..xp_cmdshell @.cmd, NO_OUTPUT
END

Simon|||Hi Simon,

Thanks for the help. This stored procedure is installed by customers,
so I have no idea what databases they have or what databases they will
want to create this view on. I will give your solution a try.

Thanks,
Bruce|||Hi Simon,

Thanks for the help. This stored procedure is installed by customers,
so I have no idea what databases they have or what databases they will
want to create this view on. I will give your solution a try.

Thanks,
Bruce

changing database owner to access diagrams

hello,

i recently changed the machine name of my development computer and am now no longer able to create or view any diagrams for the sql database that was created by the old machine name user. i receive an error where I cannot make myself "the dbo of this database." i can see the old name in the "owner" properties field of the mdf database, but the box is grayed out and i am unable to change it to the new machine/user name. is there a way to change the owner of the database to my new machine/user name? the new name has admin rights and the computer is a standalone workstation not connected to a network.

i am using sql server 2005 express edition with visual web developer.

thanks!

Hi,

you can achieve this by sp_changedbowner store procedure refer ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/516ef311-e83b-45c9-b9cd-0e0641774c04.htm in SQL BOL.

Hemantgiri S. Goswami

|||

thank you for the response. unfortunately, i am new to sql and do not know where or how to use this command. do i use VWD? Sql Server Management Studio Express does not allow me to navigate to the directory where the database resides. also, one additional piece of information: only the machine name changed.

old name: "old_machine_name/steve"

new name: "new_machine_name/steve"

which EXEC command should I use to change ownership of the database to the new name?

EXEC sp_changedbowner 'new_machine_name/steve'

or simply,

EXEC sp_changedbowner 'steve'

i am concerned that there is a larger problem with permissions and sql. anytime I add a new website and new database in VWD, the default "owner" field comes up with the old name, "old_machine_name/steve". how do I change this default so that all future databases and web sites have the new owner, "new_machine_name/steve"?

thanks!

|||

Hi,

When you rename your Server name it will reflect to your SQL Server too, your sql server's name also change refer http://blog.opsan.com/archive/2005/05/10/465.aspx for more. If you wants to change the DB Owner Open

SQL Server Management Studio -> connect -> Click on New Query -> run exec sp_changedbowner <schema> Refer http://weblogs.asp.net/dneimke/archive/2003/11/10/36691.aspx and http://weblogs.asp.net/eporter/archive/2004/10/29/249627.aspx for more on DB owner change.

Hemantgiri S. Goswami

|||

ok i tried using Sql Server Management Studio Express but was unable to navigate into the "C:\Documents and Settings\Steve\My Documents\My Web Sites\WebSite1\AppData" user directory. even though Steve is a user with admin rights, the Add database window would not open any directories below the Steve directory. therefore, i was unable to even see the mdf file to open.

i tried copying the complete web site to a c:\temp directory and was able to navigate to the AppData directory and open the database. when I ran the sql command in a query window:

EXEC sp_changedbowner 'Steve'

i received the error:

"Cannot find the principal 'Steve', because it does not exist or you do not have permission."

I received the same error when using this command: EXEC sp_changedbowner 'new_machine_name/Steve'

i did try renaming the computer name back to the old_machine_name, rebooted, and was successfully able to open the Diagrams directory of the database in the user Steve directory. Although I find this very strange, i do not know why it fails when I simply change the computer name to new_machine_name. do you have any insight to this? it still seems that there is a default setting using the old_machine_name/Steve stored somewhere in VWD or SQL Server 2005 Express.

thanks!

|||

Hi,

That is because of Schema refer Schema in BOL and below articles http://www.databasejournal.com/features/mssql/article.php/3481751 and http://www.sswug.org/see/SQL_Server_2005_-_Schema_Definition-19357

Hemantgiri S. Goswami

|||

i finally gave up and called Microsoft. the root cause was not found, but the fix was to create a new user with admin rights, then move all the files over to the new user account. the new machine name is now recognized and i am able to access the diagrams folder for the database. i hope this helps others with similar user account issues.

thanks for your help!

changing database owner to access diagrams

hello,

i recently changed the machine name of my development computer and am now no longer able to create or view any diagrams for the sql database that was created by the old machine name user. i receive an error where I cannot make myself "the dbo of this database." i can see the old name in the "owner" properties field of the mdf database, but the box is grayed out and i am unable to change it to the new machine/user name. is there a way to change the owner of the database to my new machine/user name? the new name has admin rights and the computer is a standalone workstation not connected to a network.

i am using sql server 2005 express edition with visual web developer.

thanks!

Hi,

you can achieve this by sp_changedbowner store procedure refer ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/516ef311-e83b-45c9-b9cd-0e0641774c04.htm in SQL BOL.

Hemantgiri S. Goswami

|||

thank you for the response. unfortunately, i am new to sql and do not know where or how to use this command. do i use VWD? Sql Server Management Studio Express does not allow me to navigate to the directory where the database resides. also, one additional piece of information: only the machine name changed.

old name: "old_machine_name/steve"

new name: "new_machine_name/steve"

which EXEC command should I use to change ownership of the database to the new name?

EXEC sp_changedbowner 'new_machine_name/steve'

or simply,

EXEC sp_changedbowner 'steve'

i am concerned that there is a larger problem with permissions and sql. anytime I add a new website and new database in VWD, the default "owner" field comes up with the old name, "old_machine_name/steve". how do I change this default so that all future databases and web sites have the new owner, "new_machine_name/steve"?

thanks!

|||

Hi,

When you rename your Server name it will reflect to your SQL Server too, your sql server's name also change refer http://blog.opsan.com/archive/2005/05/10/465.aspx for more. If you wants to change the DB Owner Open

SQL Server Management Studio -> connect -> Click on New Query -> run exec sp_changedbowner <schema> Refer http://weblogs.asp.net/dneimke/archive/2003/11/10/36691.aspx and http://weblogs.asp.net/eporter/archive/2004/10/29/249627.aspx for more on DB owner change.

Hemantgiri S. Goswami

|||

ok i tried using Sql Server Management Studio Express but was unable to navigate into the "C:\Documents and Settings\Steve\My Documents\My Web Sites\WebSite1\AppData" user directory. even though Steve is a user with admin rights, the Add database window would not open any directories below the Steve directory. therefore, i was unable to even see the mdf file to open.

i tried copying the complete web site to a c:\temp directory and was able to navigate to the AppData directory and open the database. when I ran the sql command in a query window:

EXEC sp_changedbowner 'Steve'

i received the error:

"Cannot find the principal 'Steve', because it does not exist or you do not have permission."

I received the same error when using this command: EXEC sp_changedbowner 'new_machine_name/Steve'

i did try renaming the computer name back to the old_machine_name, rebooted, and was successfully able to open the Diagrams directory of the database in the user Steve directory. Although I find this very strange, i do not know why it fails when I simply change the computer name to new_machine_name. do you have any insight to this? it still seems that there is a default setting using the old_machine_name/Steve stored somewhere in VWD or SQL Server 2005 Express.

thanks!

|||

Hi,

That is because of Schema refer Schema in BOL and below articles http://www.databasejournal.com/features/mssql/article.php/3481751 and http://www.sswug.org/see/SQL_Server_2005_-_Schema_Definition-19357

Hemantgiri S. Goswami

|||

i finally gave up and called Microsoft. the root cause was not found, but the fix was to create a new user with admin rights, then move all the files over to the new user account. the new machine name is now recognized and i am able to access the diagrams folder for the database. i hope this helps others with similar user account issues.

thanks for your help!