Sql Case sensitivity: March 2012

Thursday, March 29, 2012

Change Logical File Name Containing a Hyphen

I'm running SQL Server 2000. I have a DB with a logical file name containing
a hyphen. When trying to change the size of the db and log files by running
the following SQL statement it throws an error stating the file name is not
listed in sysfiles:
-- Modify db file size and max size
alter database DB_NAME_DS
MODIFY FILE
(
NAME = [DB_NAME-DS_DATA],
SIZE = 50MB,
MAXSIZE = 100MB,
FILEGROWTH = 50MB
)
-- Modify log file size and max size
alter database DB_NAME_DS
MODIFY FILE
(
NAME = [DB_NAME-DS_Log],
SIZE = 50MB,
MAXSIZE = 100MB,
FILEGROWTH = 50MB
)
GO
I've verified the logical file names using SP_HELPDB. When the DB was
created I did not specify any logical file names. SQL Server assigned them
automatically and put the hyphens in. What is causing the problem with the
SQL command and how can I get around this problem.
Thanks for any help!Please post result of following:
sp_helpfile
"Skippy, DOT DBA" wrote:
> I'm running SQL Server 2000. I have a DB with a logical file name containing
> a hyphen. When trying to change the size of the db and log files by running
> the following SQL statement it throws an error stating the file name is not
> listed in sysfiles:
> -- Modify db file size and max size
> alter database DB_NAME_DS
> MODIFY FILE
> (
> NAME = [DB_NAME-DS_DATA],
> SIZE = 50MB,
> MAXSIZE = 100MB,
> FILEGROWTH = 50MB
> )
> -- Modify log file size and max size
> alter database DB_NAME_DS
> MODIFY FILE
> (
> NAME = [DB_NAME-DS_Log],
> SIZE = 50MB,
> MAXSIZE = 100MB,
> FILEGROWTH = 50MB
> )
> GO
> I've verified the logical file names using SP_HELPDB. When the DB was
> created I did not specify any logical file names. SQL Server assigned them
> automatically and put the hyphens in. What is causing the problem with the
> SQL command and how can I get around this problem.
> Thanks for any help!|||SP_HELPFILE results:
DB_NAME-DS_Data
1 <drive
letter>:\data\DB_NAME-DS_Data.MDF
PRIMARY 32896 KB Unlimited 10% data only
DB_NAME-DS_Log
2 <drive
letter>:\Logs\DB_NAME-DS_Log.LDF
NULL 102400 KB Unlimited 25600 KB log only
"Absar Ahmad" wrote:
> Please post result of following:
> sp_helpfile
> "Skippy, DOT DBA" wrote:
> > I'm running SQL Server 2000. I have a DB with a logical file name containing
> > a hyphen. When trying to change the size of the db and log files by running
> > the following SQL statement it throws an error stating the file name is not
> > listed in sysfiles:
> >
> > -- Modify db file size and max size
> > alter database DB_NAME_DS
> > MODIFY FILE
> > (
> > NAME = [DB_NAME-DS_DATA],
> > SIZE = 50MB,
> > MAXSIZE = 100MB,
> > FILEGROWTH = 50MB
> > )
> > -- Modify log file size and max size
> > alter database DB_NAME_DS
> > MODIFY FILE
> > (
> > NAME = [DB_NAME-DS_Log],
> > SIZE = 50MB,
> > MAXSIZE = 100MB,
> > FILEGROWTH = 50MB
> > )
> > GO
> >
> > I've verified the logical file names using SP_HELPDB. When the DB was
> > created I did not specify any logical file names. SQL Server assigned them
> > automatically and put the hyphens in. What is causing the problem with the
> > SQL command and how can I get around this problem.
> >
> > Thanks for any help!sql

Change logfile location durring setup SQL 2000

Is there a possibility to change the default instalation directory for
the logfiles for sql server 2000? I would like the default database
files to be placed on teh d: drive and the logfiles on the e: drive?

Sjaak van Esdonksjaakvanesdonk@.hotmail.com (Sjaak van Esdonk) wrote in message news:<7479e65c.0311130551.56e84f0c@.posting.google.com>...
> Is there a possibility to change the default instalation directory for
> the logfiles for sql server 2000? I would like the default database
> files to be placed on teh d: drive and the logfiles on the e: drive?
> Sjaak van Esdonk

Setup doesn't have a separate path for the log files, however in
Enterprise Manager you can go into the Server Properties, and on the
Database Settings tab you can select a default location for new
database files. These values are saved in registry keys, so if you
need to do it programmatically, you can edit the registry directly:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ MSSQLServer\DefaultData
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\ MSSQLServer\DefaultLog

Simon|||Thanks Simon, that seems to work !!

Change location of RS log files?

Hi,
How can I change the location where the RS log files are
stored/created? The .config file seems to have options to change the
filename, but will this allow me to specify a different folder as
well?
We would like to put these files on a seperate hard drive from our
main application drive, in case the log files ever happen to grow
exponentially and fill the entire DB again.
Matt BrownEach of the following files for MS Reporting Services has a section called
RStrace that contains the settings for the log files.
...\ReportServer\bin\ReportingServicesService.exe.config
â?¦\ReportServer\web.config
â?¦\ReportManager\web.config
A default installation writes the log files to the directory where Reporting
Services is installed under the LogFiles folder. In order to change the
default location of the log files you need to add the following line under
the RStrace section for each of the above files. Once you have added the
directory line you will need to restart the ReportServer service.
<add name=â'Directoryâ' value=â'your dirâ' />
Example:
<RStrace>
<add name="Directory" value="d:\RSLogs" />
<add name="FileName" value="ReportServerService_" />
<add name="FileSizeLimitMb" value="32" />
<add name="KeepFilesForDays" value="14" />
<add name="Prefix" value="tid, time" />
<add name="TraceListeners" value="debugwindow, file"
/>
<add name="TraceFileMode" value="unique" />
<add name="Components" value="all" />
</RStrace>

Change Location of Logging

What parameter should be changed where in order to send the logging to a separate directory.

THx

Harry,

You can control this by using a "Directory" element in the web.config file for the RS web service and the bin\ReportingtServicesService.exe.config file for the RS Windows service:

default:

custom log file path:

Note that a folder called "LogFiles" will be created under whatever folder you specify. If the path you specify doesn't exist, it will be created. If there's some problem writing log files to that path, the log files will end up in %Temp%.

BTW, this is a "lightly-documented" feature, so it's subject to change without notice in future versions, and it doesn't get the same level of testing that other more well-documented features get.

Change location of an Existing DB in MSDE

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

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

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

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

Worth a try

Ye Hah !

GW

Change 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:20:48 -0600, Otto Porter wrote:

>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

Hi Otto,

It's not possible to change the value of a variable during the execution
of a SELECT statement. At least not the way you are trying to do it.

You can of course do
SELECT @.var = ..., @.var = ...
FROM table
WHERE ...
but I assume that this is not what you want. You can't mix this format of
the SELECT statement with a SELECT that outputs a result set.

The way I read your example, it would be very easy to have queries where
the result would be dependent on the order in which rows are processed by
SQL Server. Since SQL Server is entirely free in it's choice of processing
order, the results would be unexpected and might even vary from execution
to execution.

Check out the following link to find some good examples of the possible
effects of unexpected processing order on assignments with the SELECT
statement:
http://groups.google.com/groups?hl=...FTNGP12.phx.gbl

Best, Hugo
--

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

Change Local Subscription to Global

Does anyone know how to change a local pull subscription in merge
replication to a global subscription? I tried using
"sp_changemergesubscription" which is executed at the publisher according to
BOL and set a priority other than zero, but I get an error that says a local
subscription cannot be changed to a global subscription.
Is there something I need to execute at the subscriber?
Thanks.
Bill
Hi Bill,
From your descriptions, I understood that you would like to change local
subscription to global subscription. Have I understood you? Correct me if I
was wrong.
Based on my knowledge, unfortunately, we are not able to perform such
changes. We could select local subscription or gloabl subscription when
creating the subscription, however as the error message shows, we cannot
modify it. If you want to do so, you will have to delete the subscription
and then recreate it.
Thank you for your patience and corporation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Thank you, Michael. I suspected this was the case, but it is good to get it
verified.
I am doing some testing on setting up an alternate synchronization partner,
and having some trouble with it, so I'll likely have another post on that
issue soon.
Thanks again.
Bill
"Michael Cheng [MSFT]" <v-mingqc@.online.microsoft.com> wrote in message
news:VAcGvVSJFHA.132@.TK2MSFTNGXA02.phx.gbl...
> Hi Bill,
> From your descriptions, I understood that you would like to change local
> subscription to global subscription. Have I understood you? Correct me if
> I
> was wrong.
> Based on my knowledge, unfortunately, we are not able to perform such
> changes. We could select local subscription or gloabl subscription when
> creating the subscription, however as the error message shows, we cannot
> modify it. If you want to do so, you will have to delete the subscription
> and then recreate it.
> Thank you for your patience and corporation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
|||Hi Bill,
Thank you for the kindly separate the issue. I have taken that one and am
doing some research on it, I will update you as soon as possible when I
find anything useful to add.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/tec...rview/40010469
Others: https://partner.microsoft.com/US/tec...pportoverview/
If you are outside the United States, please visit our International
Support page: http://support.microsoft.com/common/international.aspx
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.

Change local connection string on remote server

Hello,

I have searched every post and tried every connection string there but can't figure out how to connect to my database on my remote server. I am using Visual Developer 2005 Express Edition in C# and uploading it to a Windows server running asp.net 2.0. The domain has not resolved yet.

I am so new to this and have followed many tutorials step by step but none of them address this issue. They just show how to upload it to the server.

Do I need to use the SQL server provided by my host or can the database stay in the App_Data folder?

My local connection string works locally:

When I uploaded to my server I changed \SQLEXPRESS to (local) as advised in the forum.

<add name="ConnectionString" connectionString="Data Source=(local);AttachDbFilename=|DataDirectory|\add_newSQL.mdf;Integrated Security=True;User Instance=True"

providerName="System.Data.SqlClient" />

When I debug a page I get the <customErrors mode="Off"/>
error message even thought I have already set it in my remote web.config file which may be causing problems itself:

<add name="ConnectionString" connectionString="Data Source=(local);AttachDbFilename=|DataDirectory|\add_newSQL.mdf;Integrated Security=True;User Instance=True"

providerName="System.Data.SqlClient" />

</connectionStrings>

<system.web>

</system.web>

</configuration>

Thanks for any help you can offer. I wish I could find someone to hire to do this part for me or teach me how over the phone. Any suggestions?

You are running SQL Express on your development system (your local computer). Are you running SQL Express on the web server?

Thanks,
Bryan

|||Having this same, problem!!!!!|||So the same question to you, as the question wasn′t answered by the original poster, are you running SQL Server Express on the WebServer ?

Jens K. Suessmeyer.

http://www.sqlserver2005.de

Change Lincense Key

We currently have a SQL cluster which was built on pirated license keys.
This was a managerial mistake let me point out.~
Can we change the license key to legal copy without re-installing SQL? I
see that they license key is stored in the registry, but it appears that the
Product ID is generated randomly.
Any help is appreciated.Hi
You may want to try the rebuild registry option in the setup program or see
http://tinyurl.com/ad484
John
"Andrew" wrote:
> We currently have a SQL cluster which was built on pirated license keys.
> This was a managerial mistake let me point out.~
> Can we change the license key to legal copy without re-installing SQL? I
> see that they license key is stored in the registry, but it appears that the
> Product ID is generated randomly.
> Any help is appreciated.
>

Change Lincense Key

We currently have a SQL cluster which was built on pirated license keys.
This was a managerial mistake let me point out.~
Can we change the license key to legal copy without re-installing SQL? I
see that they license key is stored in the registry, but it appears that the
Product ID is generated randomly.
Any help is appreciated.
Hi
You may want to try the rebuild registry option in the setup program or see
http://tinyurl.com/ad484
John
"Andrew" wrote:

> We currently have a SQL cluster which was built on pirated license keys.
> This was a managerial mistake let me point out.~
> Can we change the license key to legal copy without re-installing SQL? I
> see that they license key is stored in the registry, but it appears that the
> Product ID is generated randomly.
> Any help is appreciated.
>

Change licensing mode

Hello

Can someone tell me which is the quickest method to change the licensing mode after RS has been installed (per seat -> per processor)

Many thanks!

Worf

Well apparently you can't. The only way you can is by reinstalling RS.

Regards,

Worf

sql

change license mode on SQL 2k cluster

I need to change the SQL server license mode on an installed SQL server
2000 cluster from processor license to per seat.
Does anyonw know?
http://support.microsoft.com/default...b;en-us;870617
http://www.tkdinesh.com/faq/ans/license.html
Simon

change license mode on SQL 2k cluster

I need to change the SQL server license mode on an installed SQL server
2000 cluster from processor license to per seat.

Does anyonw know?http://support.microsoft.com/defaul...kb;en-us;870617
http://www.tkdinesh.com/faq/ans/license.html

Simon

change isolation level server wide

Just curious to know how one can change isolation level server wide ? I know
the default is read committed but if i wanted any other isolation level, how
may one do so ? Using SQL 2000You can only change it at session level, using the SET TRANSACTION ISOLATION
LEVEL command.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uxGOCqebEHA.3684@.TK2MSFTNGP09.phx.gbl...
Just curious to know how one can change isolation level server wide ? I know
the default is read committed but if i wanted any other isolation level, how
may one do so ? Using SQL 2000|||Hi Hassan
Unfortunately, there is no way to do this server wide. It must be set in
each connection for which you want to change from the default.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uxGOCqebEHA.3684@.TK2MSFTNGP09.phx.gbl...
> Just curious to know how one can change isolation level server wide ? I
know
> the default is read committed but if i wanted any other isolation level,
how
> may one do so ? Using SQL 2000
>

change isolation level server wide

Just curious to know how one can change isolation level server wide ? I know
the default is read committed but if i wanted any other isolation level, how
may one do so ? Using SQL 2000
Hi Hassan
Unfortunately, there is no way to do this server wide. It must be set in
each connection for which you want to change from the default.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uxGOCqebEHA.3684@.TK2MSFTNGP09.phx.gbl...
> Just curious to know how one can change isolation level server wide ? I
know
> the default is read committed but if i wanted any other isolation level,
how
> may one do so ? Using SQL 2000
>
|||You can only change it at session level, using the SET TRANSACTION ISOLATION
LEVEL command.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uxGOCqebEHA.3684@.TK2MSFTNGP09.phx.gbl...
Just curious to know how one can change isolation level server wide ? I know
the default is read committed but if i wanted any other isolation level, how
may one do so ? Using SQL 2000

change isolation level server wide

Just curious to know how one can change isolation level server wide ? I know
the default is read committed but if i wanted any other isolation level, how
may one do so ? Using SQL 2000Hi Hassan
Unfortunately, there is no way to do this server wide. It must be set in
each connection for which you want to change from the default.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uxGOCqebEHA.3684@.TK2MSFTNGP09.phx.gbl...
> Just curious to know how one can change isolation level server wide ? I
know
> the default is read committed but if i wanted any other isolation level,
how
> may one do so ? Using SQL 2000
>|||You can only change it at session level, using the SET TRANSACTION ISOLATION
LEVEL command.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uxGOCqebEHA.3684@.TK2MSFTNGP09.phx.gbl...
Just curious to know how one can change isolation level server wide ? I know
the default is read committed but if i wanted any other isolation level, how
may one do so ? Using SQL 2000sql

Change IP in SQL Cluster

Hi all,
We need to change the IP address of server SQL.
We use SQL 2000 Enterprise, Windows 2000 Advanced Server, Cluster Service
with two nodes.
What must we do?
Thanks a lot,
Ednaldo
Please check the following articles
Changing the IP Address of Network Adapters in Cluster Server
http://support.microsoft.com/default...b;en-us;230356
Changing the IP Address of a Cluster Adapter May Result in Failover
http://support.microsoft.com/kb/241828/EN-US/
How to change the network IP addresses of SQL Server virtual servers
http://support.microsoft.com/kb/244980
"Ednaldo" wrote:

> Hi all,
> We need to change the IP address of server SQL.
> We use SQL 2000 Enterprise, Windows 2000 Advanced Server, Cluster Service
> with two nodes.
> What must we do?
> Thanks a lot,
> Ednaldo

Change IP in SQL Cluster

Hi all,
We need to change the IP address of server SQL.
We use SQL 2000 Enterprise, Windows 2000 Advanced Server, Cluster Service
with two nodes.
What must we do?
Thanks a lot,
EdnaldoPlease check the following articles
Changing the IP Address of Network Adapters in Cluster Server
http://support.microsoft.com/defaul...kb;en-us;230356
Changing the IP Address of a Cluster Adapter May Result in Failover
http://support.microsoft.com/kb/241828/EN-US/
How to change the network IP addresses of SQL Server virtual servers
http://support.microsoft.com/kb/244980
"Ednaldo" wrote:

> Hi all,
> We need to change the IP address of server SQL.
> We use SQL 2000 Enterprise, Windows 2000 Advanced Server, Cluster Service
> with two nodes.
> What must we do?
> Thanks a lot,
> Ednaldo

Change IP in SQL Cluster

Hi all,
We need to change the IP address of server SQL.
We use SQL 2000 Enterprise, Windows 2000 Advanced Server, Cluster Service
with two nodes.
What must we do?
Thanks a lot,
EdnaldoPlease check the following articles
Changing the IP Address of Network Adapters in Cluster Server
http://support.microsoft.com/default.aspx?scid=kb;en-us;230356
Changing the IP Address of a Cluster Adapter May Result in Failover
http://support.microsoft.com/kb/241828/EN-US/
How to change the network IP addresses of SQL Server virtual servers
http://support.microsoft.com/kb/244980
"Ednaldo" wrote:
> Hi all,
> We need to change the IP address of server SQL.
> We use SQL 2000 Enterprise, Windows 2000 Advanced Server, Cluster Service
> with two nodes.
> What must we do?
> Thanks a lot,
> Ednaldo

Tuesday, March 27, 2012

Change IP address and subnet of Cluster nodes

I have two node SQL 2005 Cluster on Windows 2003 SP1. Now, I have to change
both IP address and subnet Windows Cluster servers.
is it possible? or What can I do step-by-step?
thanks
"TUFAN ODUNCU" <TUFANODUNCU@.discussions.microsoft.com> wrote in message
news:AF919426-25AC-4D68-AE1D-214729F868EE@.microsoft.com...
>I have two node SQL 2005 Cluster on Windows 2003 SP1. Now, I have to change
> both IP address and subnet Windows Cluster servers.
> is it possible? or What can I do step-by-step?
> thanks
Per Ryan in the other cluster newsgroup...
Changing the IP address of network adapters in cluster server
http://support.microsoft.com/kb/230356
How to change the network IP addresses of SQL Server failover cluster
instances
http://support.microsoft.com/kb/244980
Russ Kaufmann
MVP - Windows Server - Clustering
ClusterHelp.com, a Microsoft Certified Gold Partner
Web http://www.clusterhelp.com
Blog http://msmvps.com/clusterhelp
The next ClusterHelp classes are:
July 10-13 in Denver
July 16-19 in New York
Sep 3 - 6 in Copenhagen

change ip address and machine name

I need to change the ip address and machine name on a server which I have sql server 7.0 enterprise edition running on. Is there any specifics to be concerned over (sql registration, etc.)
thanksHi,
AFAIK SQL Server Wont bother about the IP Address. Once
you change the machine name of SQL BOx, you have to delete
the previous registration and re-register with the new
server name.
Run SQL Server 7.0 Setup from the Product CD. This will
just update SQL Server Internally to reflect new machine
name. Once you are done with that you need to update the
SQL Server by the below two stored procedures
sp_dropserver <old_name> go
sp_addserver <newname>, local go
Stop and Restart the SQL Service.
HTH
--
Regards
THIRUMAL REDDY MARAM
Sys Admin / SQL Server DBA
>--Original Message--
>I need to change the ip address and machine name on a
server which I have sql server 7.0 enterprise edition
running on. Is there any specifics to be concerned over
(sql registration, etc.)?
>thanks
>.
>sql

change instance name?

Is it easy to rename an instance, or change an instance to default instance?
Basically I have an SQL 2000 Standard and MSDE installed on same machine,
and I would like to swap around their instances.
Thanks
cj
No, you cannot rename an instance or change default to named or vice versa. Backup, (re)install and
restore is what you have to do. some links that might be useful:
Have a look at this list compiled by Andrew Kelly:
Moving DB's between Servers
http://www.support.microsoft.com/?id=314546
Moving SQL Server Databases to a New Location with Detach/Attach
http://www.support.microsoft.com/?id=224071
Using WITH MOVE in a Restore
http://support.microsoft.com/?id=221465
How To Transfer Logins and Passwords Between SQL Servers
http://www.support.microsoft.com/?id=246133
Mapping Logins & SIDs after a Restore
http://www.support.microsoft.com/?id=298897
Utility to map logins to users
http://www.dbmaint.com/SyncSql Logins.asp
User Logon and/or Permission Errors After Restoring Dump
http://www.support.microsoft.com/?id=168001
How to Resolve Permission Issues When a Database Is Moved Between SQL
Servers
http://www.support.microsoft.com/?id=240872
Restoring a .mdf
http://www.sqlservercentral.com/scri...p?scriptid=599
Disaster Recovery Articles for SQL Server
http://www.support.microsoft.com/?id=307775
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"wcccj" <NOSPAMcjohnson@.wccREMOVE.qld.edu.au> wrote in message
news:eTFhRmIKGHA.3408@.TK2MSFTNGP12.phx.gbl...
> Is it easy to rename an instance, or change an instance to default instance?
> Basically I have an SQL 2000 Standard and MSDE installed on same machine,
> and I would like to swap around their instances.
> Thanks
> cj
>
>
>
|||AFAIK, you can not change instance name, you have to install new
instance and dettach dbs from old instance and attach them to new
instance.
Also you can not change named instance to default instance.
look at
http://vyaskn.tripod.com/administration_faq.htm#q13
Regards
Amish Shah.
|||Ok, that's what I thought :-(
BTW thanks for the list, it is a helpful resource!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Oh3zzoJKGHA.500@.TK2MSFTNGP15.phx.gbl...
> No, you cannot rename an instance or change default to named or vice
> versa. Backup, (re)install and restore is what you have to do. some links
> that might be useful:
> Have a look at this list compiled by Andrew Kelly:
> Moving DB's between Servers
> http://www.support.microsoft.com/?id=314546
> Moving SQL Server Databases to a New Location with Detach/Attach
> http://www.support.microsoft.com/?id=224071
> Using WITH MOVE in a Restore
> http://support.microsoft.com/?id=221465
> How To Transfer Logins and Passwords Between SQL Servers
> http://www.support.microsoft.com/?id=246133
> Mapping Logins & SIDs after a Restore
> http://www.support.microsoft.com/?id=298897
> Utility to map logins to users
> http://www.dbmaint.com/SyncSql Logins.asp
> User Logon and/or Permission Errors After Restoring Dump
> http://www.support.microsoft.com/?id=168001
> How to Resolve Permission Issues When a Database Is Moved Between SQL
> Servers
> http://www.support.microsoft.com/?id=240872
> Restoring a .mdf
> http://www.sqlservercentral.com/scri...p?scriptid=599
> Disaster Recovery Articles for SQL Server
> http://www.support.microsoft.com/?id=307775
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "wcccj" <NOSPAMcjohnson@.wccREMOVE.qld.edu.au> wrote in message
> news:eTFhRmIKGHA.3408@.TK2MSFTNGP12.phx.gbl...
>

change instance name?

Is it easy to rename an instance, or change an instance to default instance?
Basically I have an SQL 2000 Standard and MSDE installed on same machine,
and I would like to swap around their instances.
Thanks
cjNo, you cannot rename an instance or change default to named or vice versa.
Backup, (re)install and
restore is what you have to do. some links that might be useful:
Have a look at this list compiled by Andrew Kelly:
Moving DB's between Servers
http://www.support.microsoft.com/?id=314546
Moving SQL Server Databases to a New Location with Detach/Attach
http://www.support.microsoft.com/?id=224071
Using WITH MOVE in a Restore
http://support.microsoft.com/?id=221465
How To Transfer Logins and Passwords Between SQL Servers
http://www.support.microsoft.com/?id=246133
Mapping Logins & SIDs after a Restore
http://www.support.microsoft.com/?id=298897
Utility to map logins to users
http://www.dbmaint.com/SyncSql Logins.asp
User Logon and/or Permission Errors After Restoring Dump
http://www.support.microsoft.com/?id=168001
How to Resolve Permission Issues When a Database Is Moved Between SQL
Servers
http://www.support.microsoft.com/?id=240872
Restoring a .mdf
http://www.sqlservercentral.com/scr...sp?scriptid=599
Disaster Recovery Articles for SQL Server
http://www.support.microsoft.com/?id=307775
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"wcccj" <NOSPAMcjohnson@.wccREMOVE.qld.edu.au> wrote in message
news:eTFhRmIKGHA.3408@.TK2MSFTNGP12.phx.gbl...
> Is it easy to rename an instance, or change an instance to default instanc
e?
> Basically I have an SQL 2000 Standard and MSDE installed on same machine,
> and I would like to swap around their instances.
> Thanks
> cj
>
>
>|||AFAIK, you can not change instance name, you have to install new
instance and dettach dbs from old instance and attach them to new
instance.
Also you can not change named instance to default instance.
look at
http://vyaskn.tripod.com/administration_faq.htm#q13
Regards
Amish Shah.|||Ok, that's what I thought :-(
BTW thanks for the list, it is a helpful resource!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Oh3zzoJKGHA.500@.TK2MSFTNGP15.phx.gbl...
> No, you cannot rename an instance or change default to named or vice
> versa. Backup, (re)install and restore is what you have to do. some links
> that might be useful:
> Have a look at this list compiled by Andrew Kelly:
> Moving DB's between Servers
> http://www.support.microsoft.com/?id=314546
> Moving SQL Server Databases to a New Location with Detach/Attach
> http://www.support.microsoft.com/?id=224071
> Using WITH MOVE in a Restore
> http://support.microsoft.com/?id=221465
> How To Transfer Logins and Passwords Between SQL Servers
> http://www.support.microsoft.com/?id=246133
> Mapping Logins & SIDs after a Restore
> http://www.support.microsoft.com/?id=298897
> Utility to map logins to users
> http://www.dbmaint.com/SyncSql Logins.asp
> User Logon and/or Permission Errors After Restoring Dump
> http://www.support.microsoft.com/?id=168001
> How to Resolve Permission Issues When a Database Is Moved Between SQL
> Servers
> http://www.support.microsoft.com/?id=240872
> Restoring a .mdf
> http://www.sqlservercentral.com/scr...sp?scriptid=599
> Disaster Recovery Articles for SQL Server
> http://www.support.microsoft.com/?id=307775
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "wcccj" <NOSPAMcjohnson@.wccREMOVE.qld.edu.au> wrote in message
> news:eTFhRmIKGHA.3408@.TK2MSFTNGP12.phx.gbl...
>

change instance name?

Is it easy to rename an instance, or change an instance to default instance?
Basically I have an SQL 2000 Standard and MSDE installed on same machine,
and I would like to swap around their instances.
Thanks
cjNo, you cannot rename an instance or change default to named or vice versa. Backup, (re)install and
restore is what you have to do. some links that might be useful:
Have a look at this list compiled by Andrew Kelly:
Moving DB's between Servers
http://www.support.microsoft.com/?id=314546
Moving SQL Server Databases to a New Location with Detach/Attach
http://www.support.microsoft.com/?id=224071
Using WITH MOVE in a Restore
http://support.microsoft.com/?id=221465
How To Transfer Logins and Passwords Between SQL Servers
http://www.support.microsoft.com/?id=246133
Mapping Logins & SIDs after a Restore
http://www.support.microsoft.com/?id=298897
Utility to map logins to users
http://www.dbmaint.com/SyncSql Logins.asp
User Logon and/or Permission Errors After Restoring Dump
http://www.support.microsoft.com/?id=168001
How to Resolve Permission Issues When a Database Is Moved Between SQL
Servers
http://www.support.microsoft.com/?id=240872
Restoring a .mdf
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
Disaster Recovery Articles for SQL Server
http://www.support.microsoft.com/?id=307775
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"wcccj" <NOSPAMcjohnson@.wccREMOVE.qld.edu.au> wrote in message
news:eTFhRmIKGHA.3408@.TK2MSFTNGP12.phx.gbl...
> Is it easy to rename an instance, or change an instance to default instance?
> Basically I have an SQL 2000 Standard and MSDE installed on same machine,
> and I would like to swap around their instances.
> Thanks
> cj
>
>
>|||AFAIK, you can not change instance name, you have to install new
instance and dettach dbs from old instance and attach them to new
instance.
Also you can not change named instance to default instance.
look at
http://vyaskn.tripod.com/administration_faq.htm#q13
Regards
Amish Shah.|||Ok, that's what I thought :-(
BTW thanks for the list, it is a helpful resource!
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Oh3zzoJKGHA.500@.TK2MSFTNGP15.phx.gbl...
> No, you cannot rename an instance or change default to named or vice
> versa. Backup, (re)install and restore is what you have to do. some links
> that might be useful:
> Have a look at this list compiled by Andrew Kelly:
> Moving DB's between Servers
> http://www.support.microsoft.com/?id=314546
> Moving SQL Server Databases to a New Location with Detach/Attach
> http://www.support.microsoft.com/?id=224071
> Using WITH MOVE in a Restore
> http://support.microsoft.com/?id=221465
> How To Transfer Logins and Passwords Between SQL Servers
> http://www.support.microsoft.com/?id=246133
> Mapping Logins & SIDs after a Restore
> http://www.support.microsoft.com/?id=298897
> Utility to map logins to users
> http://www.dbmaint.com/SyncSql Logins.asp
> User Logon and/or Permission Errors After Restoring Dump
> http://www.support.microsoft.com/?id=168001
> How to Resolve Permission Issues When a Database Is Moved Between SQL
> Servers
> http://www.support.microsoft.com/?id=240872
> Restoring a .mdf
> http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
> Disaster Recovery Articles for SQL Server
> http://www.support.microsoft.com/?id=307775
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "wcccj" <NOSPAMcjohnson@.wccREMOVE.qld.edu.au> wrote in message
> news:eTFhRmIKGHA.3408@.TK2MSFTNGP12.phx.gbl...
>> Is it easy to rename an instance, or change an instance to default
>> instance?
>> Basically I have an SQL 2000 Standard and MSDE installed on same machine,
>> and I would like to swap around their instances.
>> Thanks
>> cj
>>
>>
>

Change Indentity Increment

I need to change the seed and increment for an identity field in an existing
table and would like to make the change in a script rather then in Enterpris
e
Manager. I understand that I can use DBCC CHECKIDENT to reseed the field.
Is there a function to change the identity seed? My question applies to MS
SQL 2000.
Thanks in advance for any help.
JeremySounds scary!
Without thinking, one way to do it is to move the data off to a temporary
location, truncate your table, alter the ID column to be identity(x,y), and
then repopulate it (in the appropriate order).
I repeat my earlier comment - sounds scary!
Rob|||Yeah, especially if you have any foreign key constraints set up...
"Rob Farley" <RobFarley@.discussions.microsoft.com> wrote in message
news:4F74A9C4-559C-49FF-A36E-159CBF2022AF@.microsoft.com...
> Sounds scary!
> Without thinking, one way to do it is to move the data off to a temporary
> location, truncate your table, alter the ID column to be identity(x,y),
> and
> then repopulate it (in the appropriate order).
> I repeat my earlier comment - sounds scary!
> Rob|||Jeremy - do you mind explaining WHY you want to do such a thing?|||Thanks for the reply Rob.
One the requirements I've been given for this database project is that new
identity values in TableA should be even, and in TableB should be odd.
"Rob Farley" wrote:

> Jeremy - do you mind explaining WHY you want to do such a thing?|||What about the records that currently exist? Are you going to divvy them up
between even records in one table, odd in the other? Or are you going to
re-number all existing records? This whole thing sounds a little painful
from here...
"jeremy@.nospamwardlawclaims.com"
< jeremynospamwardlawclaimscom@.discussions
.microsoft.com> wrote in message
news:1A3467C3-CFF3-427A-A4FB-EE2C8FEA58A1@.microsoft.com...
> Thanks for the reply Rob.
> One the requirements I've been given for this database project is that new
> identity values in TableA should be even, and in TableB should be odd.
> "Rob Farley" wrote:
>|||Thanks for the reply. The identity values for existing rows would remain th
e
same. New records for TableA would have an even identity value with a new
seed of 1002 and increment of 2. TableB would have a new seed of 1002 and
increment 2 to have odd identity values for new records.
"Michael C#" wrote:

> What about the records that currently exist? Are you going to divvy them u
p
> between even records in one table, odd in the other? Or are you going to
> re-number all existing records? This whole thing sounds a little painful
> from here...
>
> "jeremy@.nospamwardlawclaims.com"
> < jeremynospamwardlawclaimscom@.discussions
.microsoft.com> wrote in message
> news:1A3467C3-CFF3-427A-A4FB-EE2C8FEA58A1@.microsoft.com...
>
>|||OK. Do both of these tables currently exist, and do both currently have
both even and odd identity values in them? Would it be easier to just add
another column to your PK (CHAR(1) possibly) indicating different identity
value sources? This solution just sounds a little shaky... what happens if
they add a third table in the future? Re-define all identities to +3
beginning with record x,xxxx?
Thanks.
"jeremy@.nospamwardlawclaims.com"
< jeremynospamwardlawclaimscom@.discussions
.microsoft.com> wrote in message
news:3603F85C-41FB-4930-8E7A-6822E763058F@.microsoft.com...
> Thanks for the reply. The identity values for existing rows would remain
> the
> same. New records for TableA would have an even identity value with a new
> seed of 1002 and increment of 2. TableB would have a new seed of 1002 and
> increment 2 to have odd identity values for new records.
> "Michael C#" wrote:
>|||To me it sounds like you actually want a view, with an insert trigger. The
trigger puts the inserted values into either TableA or TableB, depending on
which one has the higher ID field. But the view shows all of them.
This is the type of thing you might do if you wanted to set up replication
scenarios, or divide the tables between two separate databases and devices.
I think you create the new tables from scratch. Create the view (which is
just a 'select * from TableA union all select * from TableB') and the
trigger, and then populate the view from your old table. Let the trigger
handle the distribution of records.
Of course, you will need to make sure that the trigger handles the situation
where there are a whole bunch of rows in the 'Inserted' table. But there are
easy ways around this.
Rob

Change Increment Value for existing Identity Column

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

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

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

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

Change included columns when modifying table

Is there a reason you can't modify included columns when modifying an index on a table?

Well, the primary reason would be due to the fact that adding additional included columns would touch all of the leaf pages in an index, and probably force a fair majority of pages to split multiple times, so in reality most of the time recreating the index from scratch would probably be faster and more efficient once completed. Additionally, a heavy heavy number of disk seeks would be incurred jumping from the position last updated in the index to the table data to read the data to be added and then jump back again.

Basically, I'm sure you'd find that rebuilding the index will almost always give you both faster results, and additionally a more efficient (clean) index when the operation completes.

HTH,

|||I full appreciate that the index needs to be rebuilt, however SSMS manages that for you when you change the columns, clustering, file groups etc. So why can't I change the included columns and SSMS just do a drop_existing.

Change in windows platform from XP to NT

hello... my SQL database is being not able to be accessed by application when i run through VS2005 . I created the database in XP sys now brought to a NT systeem..what shd i do ..? pls help

Hi,

Could you show us the error message so that we can solve the problem more quickly. In your case, I think there's no relationship with Windows platform.Maybe you haven't attached your database into your current SQLServer properly.You may backup your database from your old SQLServer and restore the backup file into the new one. Thanks.

Change in Statistics

I added several indexes and statistics to a SQL 2000
database, and the next day I found that 4 of the
statistics no longer exist. Is there any way that the
auto create/update statistics could have caused this? Or
anything else, other than dropping them?
In addition, what effect would this have on my query
performance? I'm a little unsure about how the indexes
and statistics work together, but any help would be
greatly appreciated.
Thank you,
HeidiI guess somebody or some job deleted that statistics. SQL dose not delete
index or statistics by itself.
Since, SQL server dose cost based optimization, the statistics is very
important to decide the good plan. it must be up to date.
"hdsjunk" <anonymous@.discussions.microsoft.com> wrote in message
news:103d01c48bc9$682e5210$a301280a@.phx.gbl...
> I added several indexes and statistics to a SQL 2000
> database, and the next day I found that 4 of the
> statistics no longer exist. Is there any way that the
> auto create/update statistics could have caused this? Or
> anything else, other than dropping them?
> In addition, what effect would this have on my query
> performance? I'm a little unsure about how the indexes
> and statistics work together, but any help would be
> greatly appreciated.
> Thank you,
> Heidi|||Heidi,
It is likely that a job or other process, or even a person deleted these
statistics. SQL Server won't delete statistics without being asked.
If you do not have statistics on larger tables, SQL Server finds it
difficult to know what indexes to choose when compiling the query plan.
It will quite often choose the wrong index if you do not have
statistics, or if your statistics are wildly inaccurate (out-of-date).
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
hdsjunk wrote:
> I added several indexes and statistics to a SQL 2000
> database, and the next day I found that 4 of the
> statistics no longer exist. Is there any way that the
> auto create/update statistics could have caused this? Or
> anything else, other than dropping them?
> In addition, what effect would this have on my query
> performance? I'm a little unsure about how the indexes
> and statistics work together, but any help would be
> greatly appreciated.
> Thank you,
> Heidi

Change in Statistics

I added several indexes and statistics to a SQL 2000
database, and the next day I found that 4 of the
statistics no longer exist. Is there any way that the
auto create/update statistics could have caused this? Or
anything else, other than dropping them?
In addition, what effect would this have on my query
performance? I'm a little unsure about how the indexes
and statistics work together, but any help would be
greatly appreciated.
Thank you,
Heidi
I guess somebody or some job deleted that statistics. SQL dose not delete
index or statistics by itself.
Since, SQL server dose cost based optimization, the statistics is very
important to decide the good plan. it must be up to date.
"hdsjunk" <anonymous@.discussions.microsoft.com> wrote in message
news:103d01c48bc9$682e5210$a301280a@.phx.gbl...
> I added several indexes and statistics to a SQL 2000
> database, and the next day I found that 4 of the
> statistics no longer exist. Is there any way that the
> auto create/update statistics could have caused this? Or
> anything else, other than dropping them?
> In addition, what effect would this have on my query
> performance? I'm a little unsure about how the indexes
> and statistics work together, but any help would be
> greatly appreciated.
> Thank you,
> Heidi
|||Heidi,
It is likely that a job or other process, or even a person deleted these
statistics. SQL Server won't delete statistics without being asked.
If you do not have statistics on larger tables, SQL Server finds it
difficult to know what indexes to choose when compiling the query plan.
It will quite often choose the wrong index if you do not have
statistics, or if your statistics are wildly inaccurate (out-of-date).
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
hdsjunk wrote:
> I added several indexes and statistics to a SQL 2000
> database, and the next day I found that 4 of the
> statistics no longer exist. Is there any way that the
> auto create/update statistics could have caused this? Or
> anything else, other than dropping them?
> In addition, what effect would this have on my query
> performance? I'm a little unsure about how the indexes
> and statistics work together, but any help would be
> greatly appreciated.
> Thank you,
> Heidi
sql

Change in Statistics

I added several indexes and statistics to a SQL 2000
database, and the next day I found that 4 of the
statistics no longer exist. Is there any way that the
auto create/update statistics could have caused this? Or
anything else, other than dropping them?
In addition, what effect would this have on my query
performance? I'm a little unsure about how the indexes
and statistics work together, but any help would be
greatly appreciated.
Thank you,
HeidiI guess somebody or some job deleted that statistics. SQL dose not delete
index or statistics by itself.
Since, SQL server dose cost based optimization, the statistics is very
important to decide the good plan. it must be up to date.
"hdsjunk" <anonymous@.discussions.microsoft.com> wrote in message
news:103d01c48bc9$682e5210$a301280a@.phx.gbl...
> I added several indexes and statistics to a SQL 2000
> database, and the next day I found that 4 of the
> statistics no longer exist. Is there any way that the
> auto create/update statistics could have caused this? Or
> anything else, other than dropping them?
> In addition, what effect would this have on my query
> performance? I'm a little unsure about how the indexes
> and statistics work together, but any help would be
> greatly appreciated.
> Thank you,
> Heidi|||Heidi,
It is likely that a job or other process, or even a person deleted these
statistics. SQL Server won't delete statistics without being asked.
If you do not have statistics on larger tables, SQL Server finds it
difficult to know what indexes to choose when compiling the query plan.
It will quite often choose the wrong index if you do not have
statistics, or if your statistics are wildly inaccurate (out-of-date).
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
hdsjunk wrote:
> I added several indexes and statistics to a SQL 2000
> database, and the next day I found that 4 of the
> statistics no longer exist. Is there any way that the
> auto create/update statistics could have caused this? Or
> anything else, other than dropping them?
> In addition, what effect would this have on my query
> performance? I'm a little unsure about how the indexes
> and statistics work together, but any help would be
> greatly appreciated.
> Thank you,
> Heidi

Change in Server Name for SQL Server 2000

Hi,
I am having a SQL Server 2000 EE SP3 with default instance .
This Server's name need to be changed.
Could any tell me whether there needs to be any configuration done on
the
SQL Server prior to the change. and also after the name change.
Thanks in Advance!!Renaming a SQL server is no big effort.
Simply run the following SQL once you have renamed the server against the
master database.
sp_dropserver OLDSERVERNAME
go
sp_addserver NEWSERVERNAME, local
go
If you have any remote logins or replication setup, you may experience an
error dropping the server. If you do, you will need to drop the remote
logins first.
You will know this by running the above SQL either way. If it works, you're
done.
If not - look up "issues with remote logins and replication" in BOL.
Immy
"sasiraj" <vardhansasi@.gmail.com> wrote in message
news:1158655746.621017.22620@.i42g2000cwa.googlegroups.com...
> Hi,
> I am having a SQL Server 2000 EE SP3 with default instance .
> This Server's name need to be changed.
> Could any tell me whether there needs to be any configuration done on
> the
> SQL Server prior to the change. and also after the name change.
> Thanks in Advance!!
>|||... and some additional thoughts here: http://www.karaszi.com/SQLServer/info_change_server_name.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Immy" <therealasianbabe@.hotmail.com> wrote in message news:OoAeN$82GHA.4932@.TK2MSFTNGP02.phx.gbl...
> Renaming a SQL server is no big effort.
> Simply run the following SQL once you have renamed the server against the master database.
> sp_dropserver OLDSERVERNAME
> go
> sp_addserver NEWSERVERNAME, local
> go
> If you have any remote logins or replication setup, you may experience an error dropping the
> server. If you do, you will need to drop the remote logins first.
> You will know this by running the above SQL either way. If it works, you're done.
> If not - look up "issues with remote logins and replication" in BOL.
> Immy
> "sasiraj" <vardhansasi@.gmail.com> wrote in message
> news:1158655746.621017.22620@.i42g2000cwa.googlegroups.com...
>> Hi,
>> I am having a SQL Server 2000 EE SP3 with default instance .
>> This Server's name need to be changed.
>> Could any tell me whether there needs to be any configuration done on
>> the
>> SQL Server prior to the change. and also after the name change.
>> Thanks in Advance!!
>

Change in Server Name for SQL Server 2000

Hi,
I am having a SQL Server 2000 EE SP3 with default instance .
This Server's name need to be changed.
Could any tell me whether there needs to be any configuration done on
the
SQL Server prior to the change. and also after the name change.
Thanks in Advance!!
Renaming a SQL server is no big effort.
Simply run the following SQL once you have renamed the server against the
master database.
sp_dropserver OLDSERVERNAME
go
sp_addserver NEWSERVERNAME, local
go
If you have any remote logins or replication setup, you may experience an
error dropping the server. If you do, you will need to drop the remote
logins first.
You will know this by running the above SQL either way. If it works, you're
done.
If not - look up "issues with remote logins and replication" in BOL.
Immy
"sasiraj" <vardhansasi@.gmail.com> wrote in message
news:1158655746.621017.22620@.i42g2000cwa.googlegro ups.com...
> Hi,
> I am having a SQL Server 2000 EE SP3 with default instance .
> This Server's name need to be changed.
> Could any tell me whether there needs to be any configuration done on
> the
> SQL Server prior to the change. and also after the name change.
> Thanks in Advance!!
>
|||... and some additional thoughts here: http://www.karaszi.com/SQLServer/inf...erver_name.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Immy" <therealasianbabe@.hotmail.com> wrote in message news:OoAeN$82GHA.4932@.TK2MSFTNGP02.phx.gbl...
> Renaming a SQL server is no big effort.
> Simply run the following SQL once you have renamed the server against the master database.
> sp_dropserver OLDSERVERNAME
> go
> sp_addserver NEWSERVERNAME, local
> go
> If you have any remote logins or replication setup, you may experience an error dropping the
> server. If you do, you will need to drop the remote logins first.
> You will know this by running the above SQL either way. If it works, you're done.
> If not - look up "issues with remote logins and replication" in BOL.
> Immy
> "sasiraj" <vardhansasi@.gmail.com> wrote in message
> news:1158655746.621017.22620@.i42g2000cwa.googlegro ups.com...
>

Change in Server Name for SQL Server 2000

Hi,
I am having a SQL Server 2000 EE SP3 with default instance .
This Server's name need to be changed.
Could any tell me whether there needs to be any configuration done on
the
SQL Server prior to the change. and also after the name change.
Thanks in Advance!!Renaming a SQL server is no big effort.
Simply run the following SQL once you have renamed the server against the
master database.
sp_dropserver OLDSERVERNAME
go
sp_addserver NEWSERVERNAME, local
go
If you have any remote logins or replication setup, you may experience an
error dropping the server. If you do, you will need to drop the remote
logins first.
You will know this by running the above SQL either way. If it works, you're
done.
If not - look up "issues with remote logins and replication" in BOL.
Immy
"sasiraj" <vardhansasi@.gmail.com> wrote in message
news:1158655746.621017.22620@.i42g2000cwa.googlegroups.com...
> Hi,
> I am having a SQL Server 2000 EE SP3 with default instance .
> This Server's name need to be changed.
> Could any tell me whether there needs to be any configuration done on
> the
> SQL Server prior to the change. and also after the name change.
> Thanks in Advance!!
>|||... and some additional thoughts here: http://www.karaszi.com/SQLServer/in...server_name.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Immy" <therealasianbabe@.hotmail.com> wrote in message news:OoAeN$82GHA.4932@.TK2MSFTNGP02.ph
x.gbl...
> Renaming a SQL server is no big effort.
> Simply run the following SQL once you have renamed the server against the
master database.
> sp_dropserver OLDSERVERNAME
> go
> sp_addserver NEWSERVERNAME, local
> go
> If you have any remote logins or replication setup, you may experience an
error dropping the
> server. If you do, you will need to drop the remote logins first.
> You will know this by running the above SQL either way. If it works, you'r
e done.
> If not - look up "issues with remote logins and replication" in BOL.
> Immy
> "sasiraj" <vardhansasi@.gmail.com> wrote in message
> news:1158655746.621017.22620@.i42g2000cwa.googlegroups.com...
>

Change in 'sa' password, scheduled jobs failing

Hi all,
I have never come across this one before. I recently changed the 'sa' password. Now all of my scheduled jobs are failing with the error:
DTSRun: Loading... Error: -2147217843 (80040E4D); Provider Error: 18456 (4818) Error string: Login failed for user 'sa'. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0. Process Exit Code
1. The step failed.
I strange thing is that the 'sa' password is not referenced in any connection in any of my DTS Packages. Can anyone shed some light on this. I have hundreds of scheduled jobs and I don't want to have to recreate them all. Thanks.
The sa login and password are likely referenced in the
DTSRun command for the job. One common scenario for this is
when you have Enterprise Manager registered using the sa
login and you right click on the package, select Schedule
Package, this will create an encrypted DTSRun command in the
job's schedule which uses sa as that's what you would
currently be logged in as through Enterprise Manager.
I can't think of a fast way to redo hundreds of these.
Seems a better idea to have the packages run using trusted
connections via the service account instead.
-Sue
On Tue, 20 Apr 2004 09:56:02 -0700, "Greg"
<anonymous@.discussions.microsoft.com> wrote:

>Hi all,
>I have never come across this one before. I recently changed the 'sa' password. Now all of my scheduled jobs are failing with the error:
>DTSRun: Loading... Error: -2147217843 (80040E4D); Provider Error: 18456 (4818) Error string: Login failed for user 'sa'. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0. Process Exit Cod
e 1. The step failed.
>I strange thing is that the 'sa' password is not referenced in any connection in any of my DTS Packages. Can anyone shed some light on this. I have hundreds of scheduled jobs and I don't want to have to recreate them all. Thanks.
|||Are you referring to the SQL Server Agent jobs that are no longer working?
If so, try changing the "owner" of each job to one that has proper access
permissions.
Steve
"Greg" <anonymous@.discussions.microsoft.com> wrote in message
news:E0D9E05D-45F8-4312-A9B5-2E99E0D3BFA0@.microsoft.com...
> Hi all,
> I have never come across this one before. I recently changed the 'sa'
password. Now all of my scheduled jobs are failing with the error:
> DTSRun: Loading... Error: -2147217843 (80040E4D); Provider Error:
18456 (4818) Error string: Login failed for user 'sa'. Error
source: Microsoft OLE DB Provider for SQL Server Help file:
Help context: 0. Process Exit Code 1. The step failed.
> I strange thing is that the 'sa' password is not referenced in any
connection in any of my DTS Packages. Can anyone shed some light on this.
I have hundreds of scheduled jobs and I don't want to have to recreate them
all. Thanks.
sql

Change in 'sa' password, scheduled jobs failing

Hi all,
I have never come across this one before. I recently changed the 'sa' passw
ord. Now all of my scheduled jobs are failing with the error:
DTSRun: Loading... Error: -2147217843 (80040E4D); Provider Error: 18
456 (4818) Error string: Login failed for user 'sa'. Error source
: Microsoft OLE DB Provider for SQL Server Help file: Help cont
ext: 0. Process Exit Code
1. The step failed.
I strange thing is that the 'sa' password is not referenced in any connectio
n in any of my DTS Packages. Can anyone shed some light on this. I have hu
ndreds of scheduled jobs and I don't want to have to recreate them all. Tha
nks.The sa login and password are likely referenced in the
DTSRun command for the job. One common scenario for this is
when you have Enterprise Manager registered using the sa
login and you right click on the package, select Schedule
Package, this will create an encrypted DTSRun command in the
job's schedule which uses sa as that's what you would
currently be logged in as through Enterprise Manager.
I can't think of a fast way to redo hundreds of these.
Seems a better idea to have the packages run using trusted
connections via the service account instead.
-Sue
On Tue, 20 Apr 2004 09:56:02 -0700, "Greg"
<anonymous@.discussions.microsoft.com> wrote:

>Hi all,
>I have never come across this one before. I recently changed the 'sa' pass
word. Now all of my scheduled jobs are failing with the error:
>DTSRun: Loading... Error: -2147217843 (80040E4D); Provider Error: 18456 (48
18) Error string: Login failed for user 'sa'. Error source: Microsoft OL
E DB Provider for SQL Server Help file: Help context: 0. Process Exit
Cod
e 1. The step failed.
>I strange thing is that the 'sa' password is not referenced in any connection in an
y of my DTS Packages. Can anyone shed some light on this. I have hundreds of sched
uled jobs and I don't want to have to recreate them all. Thanks.|||Are you referring to the SQL Server Agent jobs that are no longer working?
If so, try changing the "owner" of each job to one that has proper access
permissions.
Steve
"Greg" <anonymous@.discussions.microsoft.com> wrote in message
news:E0D9E05D-45F8-4312-A9B5-2E99E0D3BFA0@.microsoft.com...
> Hi all,
> I have never come across this one before. I recently changed the 'sa'
password. Now all of my scheduled jobs are failing with the error:
> DTSRun: Loading... Error: -2147217843 (80040E4D); Provider Error:
18456 (4818) Error string: Login failed for user 'sa'. Error
source: Microsoft OLE DB Provider for SQL Server Help file:
Help context: 0. Process Exit Code 1. The step failed.
> I strange thing is that the 'sa' password is not referenced in any
connection in any of my DTS Packages. Can anyone shed some light on this.
I have hundreds of scheduled jobs and I don't want to have to recreate them
all. Thanks.

Change in MHTML rendering in SP1?

Greetings.
I recently installed SP1, and am having some trouble with my Custom Delivery
Extension.
My extension renders to MHTML, converts the resulting stream to a
CDO.Message, then manipulates the resulting object. After installing SP1, I
get an exception during this process.
I also noticed that after installing SP1, my report snapshots have gone from
about 55K to 15K.
Has the MHTML rendering changed between RTM and SP1? Is there any
documentation for how it's changed?
Regards, Jonathan
http://kerblog.com/earlyedition/archive/2004/12/09/258.aspxHi Jonathan,
What's the exactly exception you encounter? Have you installed Reporting
Services Services Pack 1 successfully? Even the size the snapshot seems go
down, does the snapshot looks exactly as you expected?
Based on my knowledge, there is only one issue about rendering mhtml fixed
in SP1, see the following knowledge base articles below and no other
changes in rendering were made.
FIX: Bookmark links in a report that is rendered to the Web archive file
format (.mhtml) may not work correctly
http://support.microsoft.com/kb/842534
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Michael,
Thanks for your quick reply.
"Michael Cheng [MSFT]" wrote:
> What's the exactly exception you encounter?
After I Render the report, I convert it to a CDO.Message and then try to
open the body using CDO.IBodyPart.GetDecodedContentStream(). The exception
is basically a COMException.
> Have you installed Reporting
> Services Services Pack 1 successfully?
Yes.
> Even the size the snapshot seems go
> down, does the snapshot looks exactly as you expected?
Yes, it looks fine.
Reporting Services RTM used to render MHTML into a "text/html" type message
if there were no graphics in the report. It looks to me like SP1 now always
renders as "multipart/related." I'm able to traverse the "multipart/related"
message to find the "text/html" part.
No problem, but it would have been nice to document. I only found out after
the change broke my delivery extension.
Regards,
Jonathan
http://kerblog.com/earlyedition/archive/2004/12/09/260.aspx|||Hi Jonathan,
I will try to find somebody from development team to see whether he knows
the answer and there is any documents about this available. I will keep you
updated.
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||HI Jonathan,
It seems the snapshot size (in the report server catalog) has decreased,
because they are compressed in SP1 (unless snapshot compression is turned
off). However, I am not sure why SP1 always renders as "multipart/related"
as you said and no relative documents.
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

Change in legend of chart based on values?

I am developing a chart with type as column and subtype as stacked. The values (different columns from my dataset) are shown as series in the chart. If I have an entire column with no values, nothing in shown in the graph but the column name comes in the legend. I do not want to show the particular column in legend if all the values in that column are 0 or null. Is it possible through an expression or any other way?

Please let me know.

Thanks in advance !!!

If you use a dynamic series grouping in the chart and you want to get rid of a particular series grouping instance, you could use a filter expression on the series grouping, e.g.
Filter expression: =Sum(Fields!Name.Value, "DynamicSeriesGroupingName")
Filter operator: >
Filter value: =0

Again, the filter approach will only work in the case of dynamic series groupings.

-- Robert

Change in Job Execution - SQL Server 2005?

I just converted a SQL Server 2000 database to 2005. I have a job whose only
step does the following:
Declare @.Command char(240)
Set @.Command = '\SDBS\Scheduler\Scheduler.Exe ' +
Convert(char(50),Convert(uniqueidentifier,[JOBID])) + ' ' + (Select
Replace([name],' ','~')
From msdb..sysjobs where job_id=[JOBID])
exec master..xp_cmdshell @.Command, NO_OUTPUT
Notice the [JOBID] portion. Under SQL Server 2000, the job id of the job was
passed to the job step. Under 2005, I get an error when I run the job saying
'JOBID' is undefined. Does anyone know what change I need to make?
Amos.Please don't post independently in separate newsgroups. You can add
multiple newsgroups to the header and then all the answers appear as one.
See my reply in the other newsgroup.
Andrew J. Kelly SQL MVP
"Amos Soma" <amos_j_soma@.yahoo.com> wrote in message
news:eIE1j7s5FHA.2576@.TK2MSFTNGP09.phx.gbl...
>I just converted a SQL Server 2000 database to 2005. I have a job whose
>only step does the following:
> Declare @.Command char(240)
> Set @.Command = '\SDBS\Scheduler\Scheduler.Exe ' +
> Convert(char(50),Convert(uniqueidentifier,[JOBID])) + ' ' + (Select
> Replace([name],' ','~')
> From msdb..sysjobs where job_id=[JOBID])
> exec master..xp_cmdshell @.Command, NO_OUTPUT
> Notice the [JOBID] portion. Under SQL Server 2000, the job id of the job
> was passed to the job step. Under 2005, I get an error when I run the job
> saying 'JOBID' is undefined. Does anyone know what change I need to make?
> Amos.
>|||Thank you.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e8AeOpt5FHA.3296@.TK2MSFTNGP09.phx.gbl...
> Please don't post independently in separate newsgroups. You can add
> multiple newsgroups to the header and then all the answers appear as one.
> See my reply in the other newsgroup.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Amos Soma" <amos_j_soma@.yahoo.com> wrote in message
> news:eIE1j7s5FHA.2576@.TK2MSFTNGP09.phx.gbl...
>>I just converted a SQL Server 2000 database to 2005. I have a job whose
>>only step does the following:
>> Declare @.Command char(240)
>> Set @.Command = '\SDBS\Scheduler\Scheduler.Exe ' +
>> Convert(char(50),Convert(uniqueidentifier,[JOBID])) + ' ' + (Select
>> Replace([name],' ','~')
>> From msdb..sysjobs where job_id=[JOBID])
>> exec master..xp_cmdshell @.Command, NO_OUTPUT
>> Notice the [JOBID] portion. Under SQL Server 2000, the job id of the job
>> was passed to the job step. Under 2005, I get an error when I run the job
>> saying 'JOBID' is undefined. Does anyone know what change I need to make?
>> Amos.
>sql

Change in Job Execution - SQL Server 2005?

I just converted a SQL Server 2000 database to 2005. I have a job whose only
step does the following:
Declare @.Command char(240)
Set @.Command = '\SDBS\Scheduler\Scheduler.Exe ' +
Convert(char(50),Convert(uniqueidentifier,[JOBID])) + ' ' + (Select
Replace([name],' ','~')
From msdb..sysjobs where job_id=[JOBID])
exec master..xp_cmdshell @.Command, NO_OUTPUT
Notice the [JOBID] portion. Under SQL Server 2000, the job id of the job was
passed to the job step. Under 2005, I get an error when I run the job saying
'JOBID' is undefined. Does anyone know what change I need to make?
Amos.
Please don't post independently in separate newsgroups. You can add
multiple newsgroups to the header and then all the answers appear as one.
See my reply in the other newsgroup.
Andrew J. Kelly SQL MVP
"Amos Soma" <amos_j_soma@.yahoo.com> wrote in message
news:eIE1j7s5FHA.2576@.TK2MSFTNGP09.phx.gbl...
>I just converted a SQL Server 2000 database to 2005. I have a job whose
>only step does the following:
> Declare @.Command char(240)
> Set @.Command = '\SDBS\Scheduler\Scheduler.Exe ' +
> Convert(char(50),Convert(uniqueidentifier,[JOBID])) + ' ' + (Select
> Replace([name],' ','~')
> From msdb..sysjobs where job_id=[JOBID])
> exec master..xp_cmdshell @.Command, NO_OUTPUT
> Notice the [JOBID] portion. Under SQL Server 2000, the job id of the job
> was passed to the job step. Under 2005, I get an error when I run the job
> saying 'JOBID' is undefined. Does anyone know what change I need to make?
> Amos.
>
|||Thank you.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e8AeOpt5FHA.3296@.TK2MSFTNGP09.phx.gbl...
> Please don't post independently in separate newsgroups. You can add
> multiple newsgroups to the header and then all the answers appear as one.
> See my reply in the other newsgroup.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Amos Soma" <amos_j_soma@.yahoo.com> wrote in message
> news:eIE1j7s5FHA.2576@.TK2MSFTNGP09.phx.gbl...
>

Change in Job Execution - SQL Server 2005?

I just converted a SQL Server 2000 database to 2005. I have a job whose only
step does the following:
Declare @.Command char(240)
Set @.Command = '\SDBS\Scheduler\Scheduler.Exe ' +
Convert(char(50),Convert(uniqueidentifie
r,[JOBID])) + ' ' + (Select
Replace([name],' ','~')
From msdb..sysjobs where job_id=[JOBID])
exec master..xp_cmdshell @.Command, NO_OUTPUT
Notice the [JOBID] portion. Under SQL Server 2000, the job id of the job
was
passed to the job step. Under 2005, I get an error when I run the job saying
'JOBID' is undefined. Does anyone know what change I need to make?
Amos.Please don't post independently in separate newsgroups. You can add
multiple newsgroups to the header and then all the answers appear as one.
See my reply in the other newsgroup.
Andrew J. Kelly SQL MVP
"Amos Soma" <amos_j_soma@.yahoo.com> wrote in message
news:eIE1j7s5FHA.2576@.TK2MSFTNGP09.phx.gbl...
>I just converted a SQL Server 2000 database to 2005. I have a job whose
>only step does the following:
> Declare @.Command char(240)
> Set @.Command = '\SDBS\Scheduler\Scheduler.Exe ' +
> Convert(char(50),Convert(uniqueidentifie
r,[JOBID])) + ' ' + (Select
> Replace([name],' ','~')
> From msdb..sysjobs where job_id=[JOBID])
> exec master..xp_cmdshell @.Command, NO_OUTPUT
> Notice the [JOBID] portion. Under SQL Server 2000, the job id of the j
ob
> was passed to the job step. Under 2005, I get an error when I run the job
> saying 'JOBID' is undefined. Does anyone know what change I need to make?
> Amos.
>|||Thank you.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e8AeOpt5FHA.3296@.TK2MSFTNGP09.phx.gbl...
> Please don't post independently in separate newsgroups. You can add
> multiple newsgroups to the header and then all the answers appear as one.
> See my reply in the other newsgroup.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Amos Soma" <amos_j_soma@.yahoo.com> wrote in message
> news:eIE1j7s5FHA.2576@.TK2MSFTNGP09.phx.gbl...
>

Change in indexes/relationships

We are having problems with the speed of our SQL Server. One of the
things I'd like to look at is the changes in the various indexes and
relationships. Is there any easy way to get the structure of the
entire db, or do I have to just go table by table and use the Ent
Manager "copy" command?
-Johnhttp://www.aspfaq.com/2209
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"John Baima" <john@.nospam.com> wrote in message
news:8dct51lafs8g8cnisgrumksknqif6d03fd@.
4ax.com...
> We are having problems with the speed of our SQL Server. One of the
> things I'd like to look at is the changes in the various indexes and
> relationships. Is there any easy way to get the structure of the
> entire db, or do I have to just go table by table and use the Ent
> Manager "copy" command?
> -John|||Right-click the database in EM and there you have scripting options. Or use
any of the options
mentioned here:
http://www.karaszi.com/SQLServer/in...rate_script.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John Baima" <john@.nospam.com> wrote in message news:8dct51lafs8g8cnisgrumksknqif6d03fd@.
4ax
.com...
> We are having problems with the speed of our SQL Server. One of the
> things I'd like to look at is the changes in the various indexes and
> relationships. Is there any easy way to get the structure of the
> entire db, or do I have to just go table by table and use the Ent
> Manager "copy" command?
> -John

Change in connection string

I have a SSIS Package which I developed on my own my database server.

But now the package is to deployed on 3-4 different computers pointing it to different databases.

I do not want to change the connection string for each and every instance because in future there may be more number of instances running.

I tried using the Configuration file and add that file in to the configuration window but that also did not work.

Please can anybody help me?
We're going to need more information here... You said you tried using a configuration file, "but that also did not work."

How did you set it up? What exactly happened that "did not work"? If running locally for testing, you'll have to get out of the package and reopen it for it to pick up the new configuration if that makes a difference.|||

Phil Brammer wrote:
We're going to need more information here... You said you tried using a configuration file, "but that also did not work."
How did you set it up? What exactly happened that "did not work"? If running locally for testing, you'll have to get out of the package and reopen it for it to pick up the new configuration if that makes a difference.

[Apologies for jumping in on this thread. If I get a reply then I'll delete it later.]

Phil,

Please could you email me - (jamieDOTthomsonATconchangoDOTcom). Just got something I want to ask you offline.

thanks

Jamie

|||I have file named dbConfig.dtsConfig. It had the following code in it

<DTSConfiguration>
<Configuration ConfiguredType="Property" Path="\Package.Connections[SERVERNAMEdbname].Properties[ConnectionString]" ValueType="String">
<ConfiguredValue>Data Source=Av-int-sj-db01\instance_b;Initial Catalog=AvaTaxAccount;User ID=avaservice;password=kennwort.2005;Provider=SQLNCLI.1;Persist Security Info=True;Auto Translate=False</ConfiguredValue>
</Configuration>
</DTSConfiguration>

While I double click the package exe the Execute Package Utility pop ups and in that I select the configuration option and add the above file in it.

Then try executing the package. Its gives an error message of the login not found.

Is this the right way to change the connection string while executing the package.

Change Image using parameter

I need to change the logo on my reports according to a parameter that I pass from VB.NET.

What is the best way to load the image into the report?

I have attempted to use a dataset:

I created a logo field in my dataset
I loaded the correct image in VB.NET
I then assigned the 'logo' field in the dataset to this image

My problem is that I don't know what the data type of the logo field should be - I have created the dataset manually and the images are not in a database.

This is my code:

'Load image into ByteArray
Dim fs As New System.IO.FileStream("C:\temp\test.jpg", IO.FileMode.Open)
Dim nBytes As Integer = fs.Length
Dim ByteArray(nBytes) As Byte
Dim nBytesRead As Integer = fs.Read(ByteArray, 0, nBytes)
fs.Read(ByteArray, 0, Convert.ToInt32(fs.Length.ToString(), 10) - 1)

'Set logo field to this image
Dim tbl As New mySchemas.myDataTable
Dim rw As DataRow = tbl.NewRow
rw("logo") = ByteArray 'AN ERROR OCCURS ON THIS LINE
tbl.Rows.Add(rw)
Dim ds As New DataSet
ds.Tables.Add(tbl)

Is this the best way to do load an image into crystal? If so what should the datatype of the logo field be in the dataset? I tried Byte but that did not work.

Thanks for any help
ElaineElaine,
I'll preface this by saying: I don't use CR10, and don't use .NET with CR.
From your code you are declaring a row object to hold the picture.
Shouldn't you be using a "field" object, or whatever the .NET equivalent is?
Field type should be OLE Object/BLOB(BinaryLargeOBject)/BitStream or similar.

Dave|||Thanks for your reply Dave. All sorted now.

The data type I needed turned out to be 'hexbinary'
Incase anyone has the same problem this is the solution:

Dim data As New DataSet()
Dim row As DataRow

data.Tables.Add("Images")
data.Tables(0).Columns.Add("img", System.Type.GetType("System.Byte[]"))

Dim fs As New FileStream("C:\test.jpg", FileMode.Open)
Dim br As New BinaryReader(fs)

row = data.Tables(0).NewRow()
row(0) = br.ReadBytes(br.BaseStream.Length)
data.Tables(0).Rows.Add(row)

br.close
fs.close

br = Nothing
fs = Nothing

' CrystalReport1 is a strongly typed report created in Visual Studio.
Dim cr As New CrystalReport1()
cr.SetDataSource(data)
CrystalReportViewer1.ReportSource = cr|||Are you using the version of crystal reports that came with visual net??
I create the typed dataset with a field hexBinary and load the pictur there but how can I put that picture in the report it shows as a number and no image apearssql

Change image properties at runtime

Hi,
I'm using Reporting Services for SQL2K. My report will need to dynamically
call a custom assembly for each row in a dataset, and then show the image
file that the custom assembly creates. I was thinking of using an Image
control in the report and then changing the path to the image file at
runtime. But I can not figure out how to access the image1.Value property.
Any tips on this or perhaps an alternative solution?
TIA
JonasI found the answer, and it was very easy ;-)
When the control is selected in the Report Designer, it it possible to edit
the properties as usual. The property for Value can also use an Expression,
and voila, this can point to a variable which contains a dynamically created
path.
Brgds
Jonas
"Jonas" <Jonas@.nospam.pl> wrote in message
news:%23z53z3cHGHA.240@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I'm using Reporting Services for SQL2K. My report will need to dynamically
> call a custom assembly for each row in a dataset, and then show the image
> file that the custom assembly creates. I was thinking of using an Image
> control in the report and then changing the path to the image file at
> runtime. But I can not figure out how to access the image1.Value property.
> Any tips on this or perhaps an alternative solution?
> TIA
> Jonas
>
>

Change Image

I'm using Crystal Report 7 ,use storc prcdure n vb 6. my prblem : let say in my system have company and their staff. how can i display one company and their staffname+ their picture. e.g: company A and their staff photo will appear n for cmpny B their staff photo will appear. means that the staff photo will appear based on their company.tnkiu..I'm using Crystal Report 7 ,use storc prcdure n vb 6. my prblem : let say in my system have company and their staff. how can i display one company and their staffname+ their picture. e.g: company A and their staff photo will appear n for cmpny B their staff photo will appear. means that the staff photo will appear based on their company.tnkiu..

simple sort the data by company name|||I'm using Crystal Report 7 ,use storc prcdure n vb 6. my prblem : let say in my system have company and their staff. how can i display one company and their staffname+ their picture. e.g: company A and their staff photo will appear n for cmpny B their staff photo will appear. means that the staff photo will appear based on their company.tnkiu..

Just sort the data by company name|||what do you mean by sort data by company name?

Change Identity value

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

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

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

Change Identify/Primary Key Column Data Type?

Hi ...

I've

taken over a project where the tables were created with

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

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

Replication requires that identify/primary key columns be of type

INT/BIGINT.

I've attempted to ALTER TABLE xxx ALTER COLUMN yyy

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

I then did an ALTER TABLE xxx NOCHECK CONSTRAINT

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

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

How

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

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

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

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

Change how command runs

This should be an easy one. I want to change the command to use the
DBID number instead of the database name to run the process. The
current loop does not keep the order if you add or remove databases
beecuse i am using the min() function.
DECLARE @.sql varchar(4000)
DECLARE @.db varchar(64)
SET @.db=''
SELECT @.db=min(name)
FROM master.dbo.sysdatabases
WHERE dbid > 4 and name > @.db
WHILE @.db is not null
BEGIN
SET @.sql='use ' +@.db + ' exec dbo.sp_UD_RebuildIndexes 30, 100'
EXEC (@.sql)
SELECT @.db=min(name)
FROM master.dbo.sysdatabases
WHERE dbid > 4 and name > @.db
END
Thanks
-Matt-not sure what are you exactly looking for.
but if you want to loop through the databases on the basis of DBID rather
than database name , then you can try following procedural code.
DECLARE @.sql varchar(4000)
DECLARE @.db int
SELECT @.db=min(dbid)
FROM master.dbo.sysdatabases
WHERE dbid > 4
WHILE @.db is not null
BEGIN
select @.sql='use ' + name + ' exec dbo.sp_UD_RebuildIndexes 30, 100'
from master..sysdatabases where dbid =@.db
EXEC (@.sql)
SELECT @.db=min(dbid)
FROM master.dbo.sysdatabases
WHERE dbid > @.db
END
"MKruer@.gmail.com" wrote:

> This should be an easy one. I want to change the command to use the
> DBID number instead of the database name to run the process. The
> current loop does not keep the order if you add or remove databases
> beecuse i am using the min() function.
> DECLARE @.sql varchar(4000)
> DECLARE @.db varchar(64)
> SET @.db=''
> SELECT @.db=min(name)
> FROM master.dbo.sysdatabases
> WHERE dbid > 4 and name > @.db
> WHILE @.db is not null
> BEGIN
> SET @.sql='use ' +@.db + ' exec dbo.sp_UD_RebuildIndexes 30, 100'
> EXEC (@.sql)
> SELECT @.db=min(name)
> FROM master.dbo.sysdatabases
> WHERE dbid > 4 and name > @.db
> END
> Thanks
> -Matt-
>|||That was it. The Query is to run a stored procedure that auto reindex
the entire Database. The only problem with the stored procedure is that
it only works on the database that it was executed on. This is more or
a temporary work around. Ideally I want to be able to specify
individual indexes directly using a table.
Creating a Stored Procedure to collect DB information and then run
another Stored Procedure using that information
http://groups.google.com/group/micr...bdb43d49fed239e
Query for Database, Table, Index name all on one list.
http://groups.google.com/group/micr...17098a35404820c|||That was it. The Query is to run a stored procedure that auto reindex
the entire Database. The only problem with the stored procedure is that
it only works on the database that it was executed on. This is more or
a temporary work around. Ideally I want to be able to specify
individual indexes directly using a table.
Creating a Stored Procedure to collect DB information and then run
another Stored Procedure using that information
http://groups.google.com/group/micr...bdb43d49fed239e
Query for Database, Table, Index name all on one list.
http://groups.google.com/group/micr...17098a35404820csql

Thursday, March 29, 2012

Tuesday, March 27, 2012

Blog Archive

About Me