Changing config table location on the fly

Hi everyone,

I am trying to create a proof of concept to show that we can have packages deployed on 3 different servers and all we need to do is tell the package, upon execution/scheduled task, where to go fetch it's configurations from.

The configurations are using SQL Server as the package configuration. This makes it easier for DBA's to maintain since DBA's are responsible for package executions and job scheduling.

For example, the configuration database and all package configurations would be found on server1, server2 and server3 The difference in values is that on Server1, the configuration for the source data and the destination data point to Server1\DatabaseSource and Server1\DatabaseDestination and on server2, the configs point the source to Server2\DatabaseSource and Server2\DatabaseDestination and for server 3, the same thing but pointing to server3.

There is also a connection for the SSIS_Config database we're getting the configurations from. In theory, if we specify a different server where this SSIS_Config database is found, it should override the settings in the package. No?

When I schedule OR execute the package, it's always getting it's configs from the config specified in the package independent of wether I specify it in the Connection Managers of the Execute Package Utility when I manually execute it OR in the data sources tab when I configure the package to be run as a job in SQL Server 2005.

Am I missing something here?

Thanks,

Rob

You use an XML file to set the "config database" location dynamically. You can't use a SQL Server based config table to control where the "config database" resides because that is what you're trying to change.

So, use an XML file, place it on all of the servers, each pointing to the correct "config database" for that environment.|||

Rob,

it sounds like you want to decide, at execution-time, which set of configurations it should use. Right?

So, in essence you need a way of "parameterising" your package so it needs which set of configurations to use. Right?

The best way to do this is to use the /SET option of dtexec.exe

Regards

Jamie

|||

Thats the one!

Thank you Jamie

|||

Jamie,

I've tried using the /SET option but I can't get it to change the set of configurations to use.

This is the command line options I've used, did I miss something?

/FILE "D:\Documents and Settings\forestr\Desktop\Deployment Test\Deployment Test\bin\Deployment\Package.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EWCDI /SET "\Package.Connections[b-ncrsql1.SSIS_CONFIG].Properties[ConnectionString]";"Data Source=b-ncrsql1\test;Initial Catalog=SSIS_CONFIG;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;"

|||

Jamie,

I've tried using the /SET option but I can't get it to change the set of configurations to use.

This is the command line options I've used, did I miss something?

/FILE "D:\Documents and Settings\forestr\Desktop\Deployment Test\Deployment Test\bin\Deployment\Package.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EWCDI /SET "\Package.Connections[b-ncrsql1.SSIS_CONFIG].Properties[ConnectionString]";"Data Source=b-ncrsql1\test;Initial Catalog=SSIS_CONFIG;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;"

|||

Hmmm, I wonder if the order in which things get applied is a problem? Davide Mauri talks a little avout this here: http://weblogs.sqlteam.com/dmauri/archive/2006/04/02/9489.aspx but I don't think it'll help in your case.

Sorry. I'm a bit stumped. Phil...any ideas?

-Jamie

|||

I've tried a different approach.

Storing the source and destination servers in my SSIS_Config tables on all three servers and storing the source server name for the package configurations in an xml file.

After altering between server1, server2 and server3 in the xml config file, the package always goes directly to the initial location set up in the package: server1.

So I'm confused, no matter what method I use, the configuration I tell the package to use is overridden with the initial configuration in the package.

If I edit the package and set the configuration table to point to server 2, that works but now I can't get it pointing to server 1 or server 3.

There's got to be something I am missing... but what?!

|||

r4enterprises wrote:

I've tried a different approach.

Storing the source and destination servers in my SSIS_Config tables on all three servers and storing the source server name for the package configurations in an xml file.

After altering between server1, server2 and server3 in the xml config file, the package always goes directly to the initial location set up in the package: server1.

So I'm confused, no matter what method I use, the configuration I tell the package to use is overridden with the initial configuration in the package.

If I edit the package and set the configuration table to point to server 2, that works but now I can't get it pointing to server 1 or server 3.

There's got to be something I am missing... but what?!

Make sure that you have that config file listed under "Package Configurations" and that you have package configurations enabled. It works, trust me! I use it all of the time. If it doesn't work for you, then something is wrong.

Know that you must use an absolute file path when pointing to the XML config file.|||If you're going to change the connection string of a connection, I'd use the /CONN switch.|||

Phil,

thanks for your insight. the absolute path might be the problem as when I would build the package, I'd go change the config settings of the xml file that is found in the /bin/Deployment directory and not in the project's folder. I never thought that it would reference an absolute path like that... kinda dumb since that would make packages non-movable between locations.

Please note that when you execute a package with the /conn, that didn't seem to work either. its still pointing to its original location.

I'll try a combination of things when I get to that and keep you guys posted.

Thanks for the help,

Robin

|||

r4enterprises wrote:

I never thought that it would reference an absolute path like that... kinda dumb since that would make packages non-movable between locations.

What if the package is stored in SQL Server? What is a "relative path" and what should it point to? This is why absolute paths are required.sql

Changing "Connection String" in SSIS package ?

Hi!

I create a SSIS Package for ETL on my own machine. During development database was also on my machine. For access to this database an OLE DB connection was defined within a package in BI Development Studio. Everything worked well both in debug mode and testing package itself.

Finally I need to load data to a database on a different machine using this package.

I used several scenaries:

1) simply copied the package-file to estination machine, open it for execution, in section "Connection Managers" I edited connection string manually - changed server name and Initial Catalog. And try to execute.

2) on the destination machine I manually created an OLE DB connection (using Microsoft Data Link) to a different database (test succeded), Changed the extention of the connection file 'udl' for ' txt ' and copied its connection string to the field connection string in section "Connection Managers" (pointed in variant 1) ).

3) use Package Configurations, copied the deployment to destination machine, installed the package the way like written here - http://msdn2.microsoft.com/en-us/library/ms365338.aspx. Changed exported properties - Server name, Initial Catalog and also the whole Connection String. Also try to execute.

In all cases I recieved the same error execution message :

"Errors in the metadata manager. Either the database with ID of " OLD_DATABASE_NAME " does not exist in the server with ID of " NEW_SERVER_NAME " or the user does not have permissions to access the object."

As for access (username/pass) settings they are the same for both of them, I have the same administrative rights on both machines. And more with the same rights the ole db connection made was made manually in variant 2 - succeded!!! So I don't think the problem is here.

As for Error message - I think somewhere the OLD name of database (Initial Catalog) is saved, though I tried to change it. Though the NEW value for the server name is substituted.

Please, help me. I don't know what else can I try. And it is not a single case for my practice. So I think - something wrong in my actions.

Don't store the ConnectionString property AND all of the other properties as configurations. Store EITHER the ConnectionString property OR all of the other properties (my advice would be to store only the ConnectionString property)

-Jamie

|||

Jamie Thomson - why to store only the ConnectionString?

Then config-file will overwrite only Connection String value itself, written in the package. But other properties would not be changed, but they will also been included into the package (the content of the package is independent with what properties I decided to include into configurations. Am I right? ).

Will you explain me? Thanks.

|||

The reply, posted before Jamie Thomson's one, was very helpfull to me. I marked it as it was helpful, but suddenly your post disapeared ? Sorry, I am new to this forum, but anywhere you help me.

I tried to watch the package file in text editor, and find the property to which the old database name value was saved, then include this property to configurations to make it changable at run time.

Thanks, it works!!!!

|||

Was this the post that disappeared?

sachin.rao wrote:

Hi Iris

The only place it could be persisting this information is in the package itself. Is the catalog name being stored in the dtsx file? Open it up in your favourite text editor and check it out.

Sachin

|||

Rafael Salas - yes, it is! Thanks.

Please, tell me what I did wrong and how can I mark a post if it was helpful and/or was a reply/solution to my question.

|||For some reason that post was deleted...only the author or a moderator can do that...I don't know who did it. You should have a buton 'Mark as anserwer'...|||

IrisFresco wrote:

Jamie Thomson - why to store only the ConnectionString?

Then config-file will overwrite only Connection String value itself, written in the package. But other properties would not be changed, but they will also been included into the package (the content of the package is independent with what properties I decided to include into configurations. Am I right? ).

Will you explain me? Thanks.

Because the ConnectionString is made up of allthose individual properties (e.g. CatalogName, Username, password, ServerName etc...). Changing ConnectionString automatically changes allthe other properties. With ConnectionString you only need to maintain one thing however and I see that as an adavantage.

-Jamie

changes to sp's

If I create a new sp in EM I have the date of creation; however, if I modify
it, it retains the original date. Is there any way of finding out the most
recent modification date of an sp inside sql server 2000?
Thanks for any help.
Bernie Yaeger> Is there any way of finding out the most recent modification date of an sp
> inside sql server 2000?
No.
You should be using a source control softwware for that.
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Bernie Yaeger" <berniey@.optonline.net> wrote in message
news:u05XzeFrFHA.2996@.tk2msftngp13.phx.gbl...
> If I create a new sp in EM I have the date of creation; however, if I
> modify it, it retains the original date. Is there any way of finding out
> the most recent modification date of an sp inside sql server 2000?
> Thanks for any help.
> Bernie Yaeger
>|||you can explore the sql server transaction logs
how ever its not an easy thing to do
you can however use the lumigent log explorer
or apex sql log
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"Bernie Yaeger" wrote:

> If I create a new sp in EM I have the date of creation; however, if I modi
fy
> it, it retains the original date. Is there any way of finding out the mos
t
> recent modification date of an sp inside sql server 2000?
> Thanks for any help.
> Bernie Yaeger
>
>|||If you are deciplined about the use of comments in the SP header, then that
is easy to tell what has been done. If you checking your scripts into Visual
Source Safe, then you can generate scripts from the current database to a
local folder and do a fiel comparison against what is in the VSS project.
Also, you can use a file comparison tool like like WinMerge to perform the
file comparisons against folders that contain different versions of scripts.
You can configure to list only scripts and individual lines that are
different.
http://groups.google.com/group/micr...f9cfe0c46abfa76
"Bernie Yaeger" <berniey@.optonline.net> wrote in message
news:u05XzeFrFHA.2996@.tk2msftngp13.phx.gbl...
> If I create a new sp in EM I have the date of creation; however, if I
> modify it, it retains the original date. Is there any way of finding out
> the most recent modification date of an sp inside sql server 2000?
> Thanks for any help.
> Bernie Yaeger
>

Change xp_cmdshell working directory?

Hello,
xp_cmdshell seems to be using c:\windows\system32 as some sort of "working"
directory. When we excute commands that create temporary files (such as FTP
for example), they write their temporary files to c:\windows\system32. If
we're ftping a large file, this causes problems.
Can this be changed? When FTP.exe is run from a normal command window, it
seems to respect the TEMP/TMP environment variable settings. When run from
xp_cmdshell, it doesn't seem to respect those settings.
Does anyone have any ideas?
You need to set the TEMP/TMP environment variable settings for the user that
SQL Server runs as. SQL Server does not run as *your* account, typically,
so changing your environment variables does absolutely nothing for the SQL
Server process.
A
"Daniel Peterson" <pythas@.hotmail.com> wrote in message
news:B2531A19-F787-4365-9F5E-3B29AE3D7956@.microsoft.com...
> Hello,
> xp_cmdshell seems to be using c:\windows\system32 as some sort of
> "working"
> directory. When we excute commands that create temporary files (such as
> FTP
> for example), they write their temporary files to c:\windows\system32. If
> we're ftping a large file, this causes problems.
> Can this be changed? When FTP.exe is run from a normal command window, it
> seems to respect the TEMP/TMP environment variable settings. When run
> from
> xp_cmdshell, it doesn't seem to respect those settings.
> Does anyone have any ideas?
|||Hello,
The environment variables are set properly for the service account.
It looks like this FTP procedure is part of a defined job that's scheduled
to run a couple of times a week. That step that uses xp_cmdshell to call FTP
is set to run as "Self".
Any other ideas?
"Aaron Bertrand [SQL Server MVP]" wrote:

> You need to set the TEMP/TMP environment variable settings for the user that
> SQL Server runs as. SQL Server does not run as *your* account, typically,
> so changing your environment variables does absolutely nothing for the SQL
> Server process.
> A
>
> "Daniel Peterson" <pythas@.hotmail.com> wrote in message
> news:B2531A19-F787-4365-9F5E-3B29AE3D7956@.microsoft.com...
>
>
|||> It looks like this FTP procedure is part of a defined job that's scheduled
> to run a couple of times a week. That step that uses xp_cmdshell to call
> FTP
> is set to run as "Self".
> Any other ideas?
Oh, you didn't mention this was a scheduled job. What is the proxy/account
in use for the SQL Server Agent service (not SQL Server itself)? Who is the
owner of the job?
|||SQL Service Account is running as the same service account as the SQL Server
itself. If I login as that service account user, environment variables look
like they get set properly. Looks like the server isn't configured with a
proxy account.
Didn't realize it was a scheduled job either, our developers left that out
of the email they sent me, and I just noticed it when I was pawing through
the job definition more.
Looks like the owner of the job is sa.
"Aaron Bertrand [SQL Server MVP]" wrote:

> Oh, you didn't mention this was a scheduled job. What is the proxy/account
> in use for the SQL Server Agent service (not SQL Server itself)? Who is the
> owner of the job?
>
>

Change xp_cmdshell working directory?

Hello,
xp_cmdshell seems to be using c:\windows\system32 as some sort of "working"
directory. When we excute commands that create temporary files (such as FTP
for example), they write their temporary files to c:\windows\system32. If
we're ftping a large file, this causes problems.
Can this be changed? When FTP.exe is run from a normal command window, it
seems to respect the TEMP/TMP environment variable settings. When run from
xp_cmdshell, it doesn't seem to respect those settings.
Does anyone have any ideas?You need to set the TEMP/TMP environment variable settings for the user that
SQL Server runs as. SQL Server does not run as *your* account, typically,
so changing your environment variables does absolutely nothing for the SQL
Server process.
A
"Daniel Peterson" <pythas@.hotmail.com> wrote in message
news:B2531A19-F787-4365-9F5E-3B29AE3D7956@.microsoft.com...
> Hello,
> xp_cmdshell seems to be using c:\windows\system32 as some sort of
> "working"
> directory. When we excute commands that create temporary files (such as
> FTP
> for example), they write their temporary files to c:\windows\system32. If
> we're ftping a large file, this causes problems.
> Can this be changed? When FTP.exe is run from a normal command window, it
> seems to respect the TEMP/TMP environment variable settings. When run
> from
> xp_cmdshell, it doesn't seem to respect those settings.
> Does anyone have any ideas?|||Hello,
The environment variables are set properly for the service account.
It looks like this FTP procedure is part of a defined job that's scheduled
to run a couple of times a week. That step that uses xp_cmdshell to call FTP
is set to run as "Self".
Any other ideas?
"Aaron Bertrand [SQL Server MVP]" wrote:
> You need to set the TEMP/TMP environment variable settings for the user that
> SQL Server runs as. SQL Server does not run as *your* account, typically,
> so changing your environment variables does absolutely nothing for the SQL
> Server process.
> A
>
> "Daniel Peterson" <pythas@.hotmail.com> wrote in message
> news:B2531A19-F787-4365-9F5E-3B29AE3D7956@.microsoft.com...
> > Hello,
> >
> > xp_cmdshell seems to be using c:\windows\system32 as some sort of
> > "working"
> > directory. When we excute commands that create temporary files (such as
> > FTP
> > for example), they write their temporary files to c:\windows\system32. If
> > we're ftping a large file, this causes problems.
> >
> > Can this be changed? When FTP.exe is run from a normal command window, it
> > seems to respect the TEMP/TMP environment variable settings. When run
> > from
> > xp_cmdshell, it doesn't seem to respect those settings.
> >
> > Does anyone have any ideas?
>
>|||> It looks like this FTP procedure is part of a defined job that's scheduled
> to run a couple of times a week. That step that uses xp_cmdshell to call
> FTP
> is set to run as "Self".
> Any other ideas?
Oh, you didn't mention this was a scheduled job. What is the proxy/account
in use for the SQL Server Agent service (not SQL Server itself)? Who is the
owner of the job?|||SQL Service Account is running as the same service account as the SQL Server
itself. If I login as that service account user, environment variables look
like they get set properly. Looks like the server isn't configured with a
proxy account.
Didn't realize it was a scheduled job either, our developers left that out
of the email they sent me, and I just noticed it when I was pawing through
the job definition more.
Looks like the owner of the job is sa.
"Aaron Bertrand [SQL Server MVP]" wrote:
> > It looks like this FTP procedure is part of a defined job that's scheduled
> > to run a couple of times a week. That step that uses xp_cmdshell to call
> > FTP
> > is set to run as "Self".
> >
> > Any other ideas?
> Oh, you didn't mention this was a scheduled job. What is the proxy/account
> in use for the SQL Server Agent service (not SQL Server itself)? Who is the
> owner of the job?
>
>|||You can verify the account and the environment variables by
executing a job with the same job owner to execute
xp_cmdshell 'SET' . Have the job step output to a file and
then check that file.
Check the system variables for the temporary directory as
well. Control Panel -> System. Select the Advanced tab - you
can check environment variables there. Not everything
related to a profile is loaded when the account is running a
service.
-Sue
On Fri, 16 Feb 2007 15:07:03 -0800, Daniel Peterson
<pythas@.hotmail.com> wrote:
>SQL Service Account is running as the same service account as the SQL Server
>itself. If I login as that service account user, environment variables look
>like they get set properly. Looks like the server isn't configured with a
>proxy account.
>Didn't realize it was a scheduled job either, our developers left that out
>of the email they sent me, and I just noticed it when I was pawing through
>the job definition more.
>Looks like the owner of the job is sa.
>"Aaron Bertrand [SQL Server MVP]" wrote:
>> > It looks like this FTP procedure is part of a defined job that's scheduled
>> > to run a couple of times a week. That step that uses xp_cmdshell to call
>> > FTP
>> > is set to run as "Self".
>> >
>> > Any other ideas?
>> Oh, you didn't mention this was a scheduled job. What is the proxy/account
>> in use for the SQL Server Agent service (not SQL Server itself)? Who is the
>> owner of the job?
>>

Change xp_cmdshell working directory?

Hello,
xp_cmdshell seems to be using c:\windows\system32 as some sort of "working"
directory. When we excute commands that create temporary files (such as FTP
for example), they write their temporary files to c:\windows\system32. If
we're ftping a large file, this causes problems.
Can this be changed? When FTP.exe is run from a normal command window, it
seems to respect the TEMP/TMP environment variable settings. When run from
xp_cmdshell, it doesn't seem to respect those settings.
Does anyone have any ideas?You need to set the TEMP/TMP environment variable settings for the user that
SQL Server runs as. SQL Server does not run as *your* account, typically,
so changing your environment variables does absolutely nothing for the SQL
Server process.
A
"Daniel Peterson" <pythas@.hotmail.com> wrote in message
news:B2531A19-F787-4365-9F5E-3B29AE3D7956@.microsoft.com...
> Hello,
> xp_cmdshell seems to be using c:\windows\system32 as some sort of
> "working"
> directory. When we excute commands that create temporary files (such as
> FTP
> for example), they write their temporary files to c:\windows\system32. If
> we're ftping a large file, this causes problems.
> Can this be changed? When FTP.exe is run from a normal command window, it
> seems to respect the TEMP/TMP environment variable settings. When run
> from
> xp_cmdshell, it doesn't seem to respect those settings.
> Does anyone have any ideas?|||Hello,
The environment variables are set properly for the service account.
It looks like this FTP procedure is part of a defined job that's scheduled
to run a couple of times a week. That step that uses xp_cmdshell to call FT
P
is set to run as "Self".
Any other ideas?
"Aaron Bertrand [SQL Server MVP]" wrote:

> You need to set the TEMP/TMP environment variable settings for the user th
at
> SQL Server runs as. SQL Server does not run as *your* account, typically,
> so changing your environment variables does absolutely nothing for the SQL
> Server process.
> A
>
> "Daniel Peterson" <pythas@.hotmail.com> wrote in message
> news:B2531A19-F787-4365-9F5E-3B29AE3D7956@.microsoft.com...
>
>|||> It looks like this FTP procedure is part of a defined job that's scheduled
> to run a couple of times a week. That step that uses xp_cmdshell to call
> FTP
> is set to run as "Self".
> Any other ideas?
Oh, you didn't mention this was a scheduled job. What is the proxy/account
in use for the SQL Server Agent service (not SQL Server itself)? Who is the
owner of the job?|||SQL Service Account is running as the same service account as the SQL Server
itself. If I login as that service account user, environment variables look
like they get set properly. Looks like the server isn't configured with a
proxy account.
Didn't realize it was a scheduled job either, our developers left that out
of the email they sent me, and I just noticed it when I was pawing through
the job definition more.
Looks like the owner of the job is sa.
"Aaron Bertrand [SQL Server MVP]" wrote:

> Oh, you didn't mention this was a scheduled job. What is the proxy/accoun
t
> in use for the SQL Server Agent service (not SQL Server itself)? Who is t
he
> owner of the job?
>
>|||You can verify the account and the environment variables by
executing a job with the same job owner to execute
xp_cmdshell 'SET' . Have the job step output to a file and
then check that file.
Check the system variables for the temporary directory as
well. Control Panel -> System. Select the Advanced tab - you
can check environment variables there. Not everything
related to a profile is loaded when the account is running a
service.
-Sue
On Fri, 16 Feb 2007 15:07:03 -0800, Daniel Peterson
<pythas@.hotmail.com> wrote:
[vbcol=seagreen]
>SQL Service Account is running as the same service account as the SQL Serve
r
>itself. If I login as that service account user, environment variables loo
k
>like they get set properly. Looks like the server isn't configured with a
>proxy account.
>Didn't realize it was a scheduled job either, our developers left that out
>of the email they sent me, and I just noticed it when I was pawing through
>the job definition more.
>Looks like the owner of the job is sa.
>"Aaron Bertrand [SQL Server MVP]" wrote:
>

Change what machine name (local) points to

I imaged my machine and moved that image to a new machine with a different
machine name. Now I try to create a database in Local and receive the
following error:
"Could not obtain exclusive lock on database 'MODEL'. Retry operation
later. CREATE Database failed. Some file names could not be created. Check
previous errors."
I'm useing SQL Server 2000 on an XP machine.
Has anyone seen a problem like this before?
Nevermind, had to re-register the local server then restart the service. I
missed the service part...
It's fixed.
"nate axtell" <naxtell at progeny dot net> wrote in message
news:Ob6COEl$EHA.2700@.TK2MSFTNGP14.phx.gbl...
>I imaged my machine and moved that image to a new machine with a different
>machine name. Now I try to create a database in Local and receive the
>following error:
> "Could not obtain exclusive lock on database 'MODEL'. Retry operation
> later. CREATE Database failed. Some file names could not be created.
> Check previous errors."
> I'm useing SQL Server 2000 on an XP machine.
> Has anyone seen a problem like this before?
>

Change what machine name (local) points to

I imaged my machine and moved that image to a new machine with a different
machine name. Now I try to create a database in Local and receive the
following error:
"Could not obtain exclusive lock on database 'MODEL'. Retry operation
later. CREATE Database failed. Some file names could not be created. Check
previous errors."
I'm useing SQL Server 2000 on an XP machine.
Has anyone seen a problem like this before?Nevermind, had to re-register the local server then restart the service. I
missed the service part...
It's fixed.
"nate axtell" <naxtell at progeny dot net> wrote in message
news:Ob6COEl$EHA.2700@.TK2MSFTNGP14.phx.gbl...
>I imaged my machine and moved that image to a new machine with a different
>machine name. Now I try to create a database in Local and receive the
>following error:
> "Could not obtain exclusive lock on database 'MODEL'. Retry operation
> later. CREATE Database failed. Some file names could not be created.
> Check previous errors."
> I'm useing SQL Server 2000 on an XP machine.
> Has anyone seen a problem like this before?
>

Change what machine name (local) points to

I imaged my machine and moved that image to a new machine with a different
machine name. Now I try to create a database in Local and receive the
following error:
"Could not obtain exclusive lock on database 'MODEL'. Retry operation
later. CREATE Database failed. Some file names could not be created. Check
previous errors."
I'm useing SQL Server 2000 on an XP machine.
Has anyone seen a problem like this before?Nevermind, had to re-register the local server then restart the service. I
missed the service part...
It's fixed.
"nate axtell" <naxtell at progeny dot net> wrote in message
news:Ob6COEl$EHA.2700@.TK2MSFTNGP14.phx.gbl...
>I imaged my machine and moved that image to a new machine with a different
>machine name. Now I try to create a database in Local and receive the
>following error:
> "Could not obtain exclusive lock on database 'MODEL'. Retry operation
> later. CREATE Database failed. Some file names could not be created.
> Check previous errors."
> I'm useing SQL Server 2000 on an XP machine.
> Has anyone seen a problem like this before?
>

Change the total (sum generated function) in a matrix

Hi,

Can somebody help me ? I really need to create a matrix with totals but I don't need a to use a sum

I need another function (AVG, Count). Can it be done ?

How can I change the Total function to put another aggregate function ?

Thanks

cyprix,

Yes, you can change the SUM function to any aggregate, the BIDS just automatic places SUM for you but you can alter it.

Ham

|||

Hi hammer

I'd love 2 know where to alter it.
Fine i click on the little green traingle,
I can still only set the properties.

No option for an expression.

G

|||

Please tell me how to make the change. If I click the green triangle I cannot edit the formula.

Thanks,

|||

cyprix,

I'm giving you a link to Robert B. MSFT solution for your problem.

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

Ham

|||

Thanks H

Good to see an alternate workaround to the one i'm using

G

CHANGE THE PROVIDERS TO USE SQL SERVER INSTEAD OF SQL EXPRESS

In step one of this Blog ,what's this mean

Step 1: Create or obtain a blank SQL database instance

and how do i go about doing it

http://weblogs.asp.net/scottgu/archive/2005/08/25/423703.aspx

That means to get a SQL database which does not have the schema (tables, stored procedures, functions, etc.) for membership, so that you can useaspnet_regsql.exe to create the required schema.|||

I'm a newbie so can you explain what u mean by getting a schema

Can u explain for example where do i start to make the config in VWD or using one of the SQL DATABASE, and if so which one. My provider is running SQL 2000

If you can break it down like the first step to do . My objective is to run a asp .net page

But i cannot figure out how to get in to run on the server But it works on my local machine. but when i upload it... i get the error message 40 about remote connection ,and i was told because i have to config VWD2005 to run on SQL 2000

Thanks for your help

|||

Hi bigmike40, let's discuss it in this post:

http://forums.asp.net/thread/1362712.aspx

|||

Ok

Change Table from Namespace

I have a table like this : dbo.Table, and I recently create a namespace, and that table belongs there. So I want the table to be created on the new namespace, Namespace.Table, which is not big deal because I can script the table as a Create, and changes the [dbo].[table] to [Namespace].[Table]. But I need to import all the rows already on the old table. How can I achieve that?
Thanks

YOu can simply do a

INSERT INTO SchemaName.NewTable
SELECT * FROM SchemaOld.OldTable

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Sweet! Thanks a lot for yur help!!

Change Report Owner

We had a person create over 200 report subscriptions and they are
showing up as the report onwer. No that this person has left and their
domain account has been deleted we have found that the SSRS 2005
report subscriptions are now failing.
How do you change the report subscriptions owner to someone that still
exists within the organisation? Or what else would be causing this
issue?
TIAOn Sep 5, 1:56 am, white...@.gmail.com wrote:
> We had a person create over 200 report subscriptions and they are
> showing up as the report onwer. No that this person has left and their
> domain account has been deleted we have found that the SSRS 2005
> report subscriptions are now failing.
> How do you change the report subscriptions owner to someone that still
> exists within the organisation? Or what else would be causing this
> issue?
> TIA
You are correct in assuming the subs are failing due to deletion of
the report owner's account. The same thing happened to us.
You will need to do one of two things: 1) have a current valid user
recreate all of the affected subs or 2) update the OwnerID value in
the Subscriptions table to a current valid user id.
What we did to eliminate this happening every time someone left was
create a generic "SubscriptionOwner" user account and amended the
stored procs (CreateSubscription And UpdateSubscription) that create
subscriptions so that the creating OwnerID value in the Subscriptions
table is replaced with that of the "SubscriptionOwner".|||On Sep 6, 2:55 am, toolman <t...@.infocision.com> wrote:
> On Sep 5, 1:56 am, white...@.gmail.com wrote:
> > We had a person create over 200 report subscriptions and they are
> > showing up as the report onwer. No that this person has left and their
> > domain account has been deleted we have found that the SSRS 2005
> > report subscriptions are now failing.
> > How do you change the report subscriptions owner to someone that still
> > exists within the organisation? Or what else would be causing this
> > issue?
> > TIA
> You are correct in assuming the subs are failing due to deletion of
> the report owner's account. The same thing happened to us.
> You will need to do one of two things: 1) have a current valid user
> recreate all of the affected subs or 2) update the OwnerID value in
> the Subscriptions table to a current valid user id.
> What we did to eliminate this happening every time someone left was
> create a generic "SubscriptionOwner" user account and amended the
> stored procs (CreateSubscription And UpdateSubscription) that create
> subscriptions so that the creating OwnerID value in the Subscriptions
> table is replaced with that of the "SubscriptionOwner".
Thanks for the advice.
However, I have changed the OwnerID in the Subscriptions table but
when I edit the subscription through report manager it still shows the
old username as "Owner:", but if performing a search through the
databse itself it doesn't show any where. I even tried renaming the
UserName field in the Users table, but the name still shows under
"Owner:" in Report Manager.
Any more help would be appreciated.

Change Print Defaults

I have noticed that all the reports I create in VS 2000 for RS 2000 all have the default print set to potrait with 1" margins. I was wondering if there was a way to make the default different for different reports. Some I only want a .5" and some and I want to print landscape and still other I need both. Does anyone know how to go about this? Thanks

You can set the print settings for every report individually. In VS 2003 got to the Report Menu and choose Report Properties. Go to the layout tab and adjust the width, height and margins as per your printing requirements. Note there is no specific Portrait/Landscape option here, you control it using width and the height.

One technique that I use when I start a new project is I set up 2 blank report templates (portrait and landscape) with the necessary branding, logo and print settings already set up. Then when I create a new report I just copy paste the relevant template.

The layout settigns are also applied when exporting to PDF.

|||That worked great thanks

change Login Name

I just create a new DB under SQL 2000 and the Login Name is my network id
How can i change this to be SA like it should beCan you just disconnect the server in Enterprise Manager, then re-register
the server selecting SQL Server Authentication, and using the username 'sa'
"Mike" <csharpcoder@.sbcglobal.net> wrote in message
news:eqMH7O05DHA.1672@.TK2MSFTNGP12.phx.gbl...
> I just create a new DB under SQL 2000 and the Login Name is my network id
> How can i change this to be SA like it should be
>
>|||no, that don't work, it is still my name under the Login Name, i need it to
be sa
"Ryan Breakspear" <r.breakspear@.removespamfdsltd.co.uk> wrote in message
news:uRIL2R05DHA.2416@.TK2MSFTNGP10.phx.gbl...
> Can you just disconnect the server in Enterprise Manager, then re-register
> the server selecting SQL Server Authentication, and using the username
'sa'
> "Mike" <csharpcoder@.sbcglobal.net> wrote in message
> news:eqMH7O05DHA.1672@.TK2MSFTNGP12.phx.gbl...
> > I just create a new DB under SQL 2000 and the Login Name is my network
id
> > How can i change this to be SA like it should be
> >
> >
> >
>|||So if you look at the Users list, the Name says sa, and the Login Name is
your user name? That sounds a bit strange. I know that if you try and log
in as 'sa', and get the password wrong, then SQL Server will try and log you
on using integrated login without you knowing. I always thought that sa
couldn't be an integrated login.
"Mike" <csharpcoder@.sbcglobal.net> wrote in message
news:eKiSpY05DHA.2580@.TK2MSFTNGP11.phx.gbl...
> no, that don't work, it is still my name under the Login Name, i need it
to
> be sa
> "Ryan Breakspear" <r.breakspear@.removespamfdsltd.co.uk> wrote in message
> news:uRIL2R05DHA.2416@.TK2MSFTNGP10.phx.gbl...
> > Can you just disconnect the server in Enterprise Manager, then
re-register
> > the server selecting SQL Server Authentication, and using the username
> 'sa'
> > "Mike" <csharpcoder@.sbcglobal.net> wrote in message
> > news:eqMH7O05DHA.1672@.TK2MSFTNGP12.phx.gbl...
> > > I just create a new DB under SQL 2000 and the Login Name is my network
> id
> > > How can i change this to be SA like it should be
> > >
> > >
> > >
> >
> >
>|||Use the sp_changedbowner stored procedure. It is documented in Books on
Line.
Rand
This posting is provided "as is" with no warranties and confers no rights.

change Login Name

I just create a new DB under SQL 2000 and the Login Name is my network id
How can i change this to be SA like it should beCan you just disconnect the server in Enterprise Manager, then re-register
the server selecting SQL Server Authentication, and using the username 'sa'
"Mike" <csharpcoder@.sbcglobal.net> wrote in message
news:eqMH7O05DHA.1672@.TK2MSFTNGP12.phx.gbl...

quote:

---

> I just create a new DB under SQL 2000 and the Login Name is my network id
> How can i change this to be SA like it should be
>
>

---

|||no, that don't work, it is still my name under the Login Name, i need it to
be sa
"Ryan Breakspear" <r.breakspear@.removespamfdsltd.co.uk> wrote in message
news:uRIL2R05DHA.2416@.TK2MSFTNGP10.phx.gbl...

quote:

---

> Can you just disconnect the server in Enterprise Manager, then re-register
> the server selecting SQL Server Authentication, and using the username

---

'sa'

quote:

---

> "Mike" <csharpcoder@.sbcglobal.net> wrote in message
> news:eqMH7O05DHA.1672@.TK2MSFTNGP12.phx.gbl...
id[QUOTE]
>

---

|||So if you look at the Users list, the Name says sa, and the Login Name is
your user name? That sounds a bit strange. I know that if you try and log
in as 'sa', and get the password wrong, then SQL Server will try and log you
on using integrated login without you knowing. I always thought that sa
couldn't be an integrated login.
"Mike" <csharpcoder@.sbcglobal.net> wrote in message
news:eKiSpY05DHA.2580@.TK2MSFTNGP11.phx.gbl...

quote:

---

> no, that don't work, it is still my name under the Login Name, i need it

---

to

quote:

---

> be sa
> "Ryan Breakspear" <r.breakspear@.removespamfdsltd.co.uk> wrote in message
> news:uRIL2R05DHA.2416@.TK2MSFTNGP10.phx.gbl...
re-register[QUOTE]
> 'sa'
> id
>

---

|||Use the sp_changedbowner stored procedure. It is documented in Books on
Line.
Rand
This posting is provided "as is" with no warranties and confers no rights.