build application: March 2012

Friday, March 30, 2012

how to change the login for connect to the report database?

when we install report server database, a login and password is needed to
connect to the RS server database.
but my boss change the password in the server so i cant access to the report
server again as the report server said login in failed.
how can i tell teh reporting service the new password?
thanks in advance> how can i tell teh reporting service the new password?
Use the rsconfig.exe utility. Look for the syntax in RS Books OnLine.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.comsql

How to change the localtion of WSS_Content Database (sharepoint3.0)

Hi, i am using sharepoint 3.0 and default Embedded Version of SQL express.

Now I would like to change the directory of WSS Content and Search database

(instead of C drive)

When I was using WSS2, I changed it using OSQL command.

For WSS3.0 it is a little different.

Could you help me how to change database directory?

Thanks

That KB article should guide you, through it is for 2.0 > 3.0

http://support.microsoft.com/default.aspx/kb/925190

Jens K. Suessmeyer.

http://www.sqlserver2005.de

How to change the letters of the cutline in the CrystalReport?

I ask some help for two problems .

How to change the letters of the cutline in the CrystalReport?
How to change the letters on the cakychart bigger in the CrystalReport?

If you know some methods,please write back to me as soon as quickly.
My Email is Nanty201@.hotmail.com .
Expect your letter.
Thank you very much!No One Knows?|||Don't know what either one of those are.
GJ

How to change the language settings of an rdl

Hi,

Can we change the language settings for a report by modifying the rdl code?

For example:- If my report is in English US, how do I change it to English UK?

Thanks in advance

In Solution Explorer Right Click on the Report for which you want to change the language and select "View Code".

In the Code that opened search for "Language" there you can change the language

How to change the instance name?

Hello DBA's

I have installed SQL Server 2005 on a machine with named instance, but later I noticed that I created it with the wrong name than desired. Now I need to change the instace name of the SQL Server that I have installed. How can I do that

Thanks

Satya

There is not a supported way to rename an instance. You need to uninstall and then reinstall.

Thanks,

Peter Saddow

|||

Thanks Peter

Satya

how to change the instance name back to default of SQL2k5

hi,

While installing SQL SERVER 2005, I had opted for providing custom name for the SQL Server and named it as for eg. as 'xyz' . Now I would prefer to change it back to default instance so that I can use server=localhost in my connection string of my ASP.NET page. With the custom instance name everytime I have to give
server="Machinename\xyz" which is annoying as I will have to change the connection strings in so many places for my exisiting ASP.NET page.

For e.g.

strConnection="server="Machinename\xyz";database=test;Integrated Security=SSPI;";

I tried using server=(local). It did not work...:(

Also on my another machine which has SQL2k5 installed with default instance I am able to use this string:
strConnection="server=localhost;database=test;Integrated Security=SSPI;";

while the same string I cannot use on the one in which I provided the instance name.

Guess uninstall is the only way.

anyone knows how can I change it back to default instance?

I'm not sure if there is a way to change an instance name. If you must do this, install another copy of SQL Server with the default instance name. Then backup your databases in the xyz instance. Restore them in the default instance. Then uninstall xyz instanace. As for the programs with changing connections strings in web apps, I would recommend pointing them to datasources instead of putting connections string in there. Then, if you modify your sql server configuration you simply have to change the datasouce and not all of your web.config files.|||

Thanks JonM. I know how to create datasources but I don;t know what needs to be put in the ASP.NET file or web.config files to establish the connection.

Suppose I have database called 'test' with DSN name as 'test' in the 'xyz' instance of SQL Server on my machine named 'Development'

How do I achieve this?

Thank you once again for your help.

|||

Basically you change your connection string to 'Datasource=xyx', and remove all of the other stuff, you may still have to specify a username and password if you didnt specify it in the datasouce.

sql

How to change the Identity value

I have a database table and it has a column called ttt_id which has an identity seed. Now the Id value is 2845746 and if I insert the new record the value would be 2845747. But I want to change that to 2865740 instead of 2845747. How can do that?
Thanks.Check out DBCC CHECKIDENT in BOL. It should give you everything you need.|||-- SET IDENTITY_INSERT to ON.
SET IDENTITY_INSERT <TableName> ON
GO

INSERT INTO <TableName> (<FieldName>) VALUES(2845747)
GO

How to Change the Host Computer Name on W2K3R2 SQL 2K5 Server?

I am having some application issues installing an Ultipro Server on W2K3R2 w/
SP2 and SQL 2K5 w/ SP1 when the Host Name was changed after SQL was installed.
I saw a post about this and thought there should be a good way to check and
fix?
Hi
If SELECT @.@.SERVERNAME returns the old server name see:
http://support.microsoft.com/kb/303774/en-us
John
"Geoff Hughes" wrote:

> I am having some application issues installing an Ultipro Server on W2K3R2 w/
> SP2 and SQL 2K5 w/ SP1 when the Host Name was changed after SQL was installed.
> I saw a post about this and thought there should be a good way to check and
> fix?

How to Change the Host Computer Name on W2K3R2 SQL 2K5 Server?

I am having some application issues installing an Ultipro Server on W2K3R2 w/
SP2 and SQL 2K5 w/ SP1 when the Host Name was changed after SQL was installed.
I saw a post about this and thought there should be a good way to check and
fix?Hi
If SELECT @.@.SERVERNAME returns the old server name see:
http://support.microsoft.com/kb/303774/en-us
John
"Geoff Hughes" wrote:
> I am having some application issues installing an Ultipro Server on W2K3R2 w/
> SP2 and SQL 2K5 w/ SP1 when the Host Name was changed after SQL was installed.
> I saw a post about this and thought there should be a good way to check and
> fix?

How to change the height of a row using expressions?

Is it possible to have a report parameter that will change the height of rows in a report? The height property of a matrix row does not seem to support expressions.

My goal is to instruct a single row sub-report to grow in height until it fits the height of the parent report cell.I thought that I could pass the height of the parent row as a parameter to the sub-report and then change the height of the sub-report using an expression.

Any help will be appreciated,

Dom.

No, currently row height can not be dynamically changed through expression. This feature is on our wish list for a future release.|||

Thanks for the reply, a lot of posts in this section are left unanswered.

Do you know then how I could tell a sub-report to grow in height until it fits the parent cell of the main report?

How to change the error log location?

How can you change the location of the Server's error log? I need to get it
off of the c: drive!
Thanks!
It is in the registry. To change from EM: Right-click the server, properties, startup parameters.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:475F8B64-872F-4285-AE4C-14D6255B0379@.microsoft.com...
> How can you change the location of the Server's error log? I need to get it
> off of the c: drive!
> Thanks!
|||Hi ,
I found the following article that talks about the same. Read the summary
below for breif info .
SUMMARY:
==============
This article describes how to change the location of the data and log files
for any SQL Server 7.0 or SQL Server 2000 database.
Moving SQL Server databases to a new location with Detach/Attach :
================================================== ===============
Microsoft Knowledge Base Article - 224071 :
http://support.microsoft.com/default...b;EN-US;224071
Moving the master database:
===========================
Change the path for the master data and log files in SQL Server Enterprise
Manager.
Note You may optionally change the location of the error log here as well.
Right-click the SQL Server in Enterprise Manager and click Properties.
Click the Startup Parameters button and you will see the following entries:
-dD:\MSSQL7\data\master.mdf
-eD:\MSSQL7\log\ErrorLog
-lD:\MSSQL7\data\mastlog.ldf
-d is the fully qualified path for the master database data file.
-e is the fully qualified path for the error log file.
-l is the fully qualified path for the master database log file.
Change these values as follows:
Remove the current entries for the Master.mdf and Mastlog.ldf files.
Add new entries specifying the new location: -dE:\SQLDATA\master.mdf
-lE:\SQLDATA\mastlog.ldf
Stop SQL Server.
Copy the Master.mdf and Mastlog.ldf files to the new location (E:\Sqldata).
Restart SQL Server.
Regards,
Venkat.

How to change the directories used by the "SQL Server Analysis Services"

I had to change the directories used by the SQL Server Analysis Services. This is what I have done.

I have changed the directories for the SQL Server Analysis Services through the properties window Next I stopped the SQL Server Analysis Services and moved the entire OLAP directory to a different directory on the same drive Then I tried to start the SQL Server Analysis Services which fails

In the Event log I only see the following entry:

Event Type: Error
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 8355
Date: 18/04/2007
Time: 13:02:53
User: N/A
Computer: SQLSERVER
Description:
Server-level event notifications can not be delivered. Either Service Broker is disabled in msdb, or msdsb failed to start. Event notifications in other databases could be affected as well. Bring msdb online, or enable Service Broker.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Data:
0000: a3 20 00 00 10 00 00 00 ￡ ......
0008: 0a 00 00 00 49 00 53 00 ....I.S.
0010: 4f 00 41 00 50 00 50 00 O.A.P.P.
0018: 32 00 35 00 34 00 00 00 2.5.4...
0020: 07 00 00 00 6d 00 61 00 ....m.a.
0028: 73 00 74 00 65 00 72 00 s.t.e.r.
0030: 00 00 ..

By changing the directories back to their original values in the msmdsrv.ini file and moving the entire directory back to its original location I was able to start the SQL Server Analysis Services, but I really need to move the directories, any help is appreciated.

As with any application, after it is installed, it is not easy to move it to different location. I would strongly recommend that you consider re-installing SSAS.

But, there is a way for you to move data folder, that is usually the biggest folder.

For that go to SSAS properties, change parameter: DataDir to your desired location, then stop SSAS, move your data folder to new location and start SSAS.

Vidas Matelis

How to change the directories used by the "SQL Server Analysis Services"

I had to change the directories used by the SQL Server Analysis Services. This is what I have done.

In the Event log I only see the following entry:

Event Type: Error
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 8355
Date: 18/04/2007
Time: 13:02:53
User: N/A
Computer: SQLSERVER
Description:
Server-level event notifications can not be delivered. Either Service Broker is disabled in msdb, or msdsb failed to start. Event notifications in other databases could be affected as well. Bring msdb online, or enable Service Broker.

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Data:
0000: a3 20 00 00 10 00 00 00 ￡ ......
0008: 0a 00 00 00 49 00 53 00 ....I.S.
0010: 4f 00 41 00 50 00 50 00 O.A.P.P.
0018: 32 00 35 00 34 00 00 00 2.5.4...
0020: 07 00 00 00 6d 00 61 00 ....m.a.
0028: 73 00 74 00 65 00 72 00 s.t.e.r.
0030: 00 00 ..

As with any application, after it is installed, it is not easy to move it to different location. I would strongly recommend that you consider re-installing SSAS.

But, there is a way for you to move data folder, that is usually the biggest folder.

For that go to SSAS properties, change parameter: DataDir to your desired location, then stop SSAS, move your data folder to new location and start SSAS.

Vidas Matelis

How to change the Default value of a Column

What's the SQL or is there a stored procedure
to replace the DEFAULT value of a COLUMN?
Dropping and re-adding the column seems overkill to me.
thanks in advance
You do an alter table to drop the constraint and then alter table to add it
back. You don't drop/add the column.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
..
"Baranggay Ginebra" <d@.d.com> wrote in message
news:3aed85F6bv1ejU1@.individual.net...
What's the SQL or is there a stored procedure
to replace the DEFAULT value of a COLUMN?
Dropping and re-adding the column seems overkill to me.
thanks in advance
|||ALTER TABLE <table_name> DROP CONSTRAINT <constraint name>
ALTER TABLE <table_name> ADD CONSTRAINT <constraint name> DEFAULT
<expression> FOR <column name>
If you have auto named default names, you can use the following script,
substituting <table name> and <column name>:
DECLARE @.constraint_name SYSNAME
-- remove all the defaults
WHILE 1=1
BEGIN
SET @.constraint_name = (SELECT TOP 1 c_obj.name
FROM sysobjects t_obj
INNER JOIN sysobjects c_obj
ON t_obj.id = c_obj.parent_obj
INNER JOIN syscolumns cols
ON cols.colid = c_obj.info
AND cols.id = c_obj.parent_obj
WHERE t_obj.id = OBJECT_ID('<table name>')
AND c_obj.xtype = 'D'
AND cols.[name]IN ('<column names>'))
IF @.constraint_name IS NULL BREAK
EXEC ('ALTER TABLE <table name> DROP CONSTRAINT ' + @.constraint_name)
END
Jacco Schalkwijk
SQL Server MVP
"Baranggay Ginebra" <d@.d.com> wrote in message
news:3aed85F6bv1ejU1@.individual.net...
> What's the SQL or is there a stored procedure
> to replace the DEFAULT value of a COLUMN?
> Dropping and re-adding the column seems overkill to me.
>
> thanks in advance
>
|||excellent !
thank a lot : )

How to change the Default value of a Column

What's the SQL or is there a stored procedure
to replace the DEFAULT value of a COLUMN?
Dropping and re-adding the column seems overkill to me.
thanks in advanceYou do an alter table to drop the constraint and then alter table to add it
back. You don't drop/add the column.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Baranggay Ginebra" <d@.d.com> wrote in message
news:3aed85F6bv1ejU1@.individual.net...
What's the SQL or is there a stored procedure
to replace the DEFAULT value of a COLUMN?
Dropping and re-adding the column seems overkill to me.
thanks in advance|||ALTER TABLE <table_name> DROP CONSTRAINT <constraint name>
ALTER TABLE <table_name> ADD CONSTRAINT <constraint name> DEFAULT
<expression> FOR <column name>
If you have auto named default names, you can use the following script,
substituting <table name> and <column name>:
DECLARE @.constraint_name SYSNAME
-- remove all the defaults
WHILE 1=1
BEGIN
SET @.constraint_name = (SELECT TOP 1 c_obj.name
FROM sysobjects t_obj
INNER JOIN sysobjects c_obj
ON t_obj.id = c_obj.parent_obj
INNER JOIN syscolumns cols
ON cols.colid = c_obj.info
AND cols.id = c_obj.parent_obj
WHERE t_obj.id = OBJECT_ID('<table name>')
AND c_obj.xtype = 'D'
AND cols.[name]IN ('<column names>'))
IF @.constraint_name IS NULL BREAK
EXEC ('ALTER TABLE <table name> DROP CONSTRAINT ' + @.constraint_name)
END
Jacco Schalkwijk
SQL Server MVP
"Baranggay Ginebra" <d@.d.com> wrote in message
news:3aed85F6bv1ejU1@.individual.net...
> What's the SQL or is there a stored procedure
> to replace the DEFAULT value of a COLUMN?
> Dropping and re-adding the column seems overkill to me.
>
> thanks in advance
>|||excellent !
thank a lot : )

How to change the Default value of a Column

What's the SQL or is there a stored procedure
to replace the DEFAULT value of a COLUMN?
Dropping and re-adding the column seems overkill to me.
thanks in advanceYou do an alter table to drop the constraint and then alter table to add it
back. You don't drop/add the column.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Baranggay Ginebra" <d@.d.com> wrote in message
news:3aed85F6bv1ejU1@.individual.net...
What's the SQL or is there a stored procedure
to replace the DEFAULT value of a COLUMN?
Dropping and re-adding the column seems overkill to me.
thanks in advance|||ALTER TABLE <table_name> DROP CONSTRAINT <constraint name>
ALTER TABLE <table_name> ADD CONSTRAINT <constraint name> DEFAULT
<expression> FOR <column name>
If you have auto named default names, you can use the following script,
substituting <table name> and <column name>:
DECLARE @.constraint_name SYSNAME
-- remove all the defaults
WHILE 1=1
BEGIN
SET @.constraint_name = (SELECT TOP 1 c_obj.name
FROM sysobjects t_obj
INNER JOIN sysobjects c_obj
ON t_obj.id = c_obj.parent_obj
INNER JOIN syscolumns cols
ON cols.colid = c_obj.info
AND cols.id = c_obj.parent_obj
WHERE t_obj.id = OBJECT_ID('<table name>')
AND c_obj.xtype = 'D'
AND cols.[name]IN ('<column names>'))
IF @.constraint_name IS NULL BREAK
EXEC ('ALTER TABLE <table name> DROP CONSTRAINT ' + @.constraint_name)
END
Jacco Schalkwijk
SQL Server MVP
"Baranggay Ginebra" <d@.d.com> wrote in message
news:3aed85F6bv1ejU1@.individual.net...
> What's the SQL or is there a stored procedure
> to replace the DEFAULT value of a COLUMN?
> Dropping and re-adding the column seems overkill to me.
>
> thanks in advance
>|||excellent !
thank a lot : )

How to change the Default value of a Column

What's the SQL or is there a stored procedure
to replace the DEFAULT value of a COLUMN?
Dropping and re-adding the column seems overkill to me.
thanks in advanceYou do an alter table to drop the constraint and then alter table to add it
back. You don't drop/add the column.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Baranggay Ginebra" <d@.d.com> wrote in message
news:3aed85F6bv1ejU1@.individual.net...
What's the SQL or is there a stored procedure
to replace the DEFAULT value of a COLUMN?
Dropping and re-adding the column seems overkill to me.
thanks in advance|||ALTER TABLE <table_name> DROP CONSTRAINT <constraint name>
ALTER TABLE <table_name> ADD CONSTRAINT <constraint name> DEFAULT
<expression> FOR <column name>
If you have auto named default names, you can use the following script,
substituting <table name> and <column name>:
DECLARE @.constraint_name SYSNAME
-- remove all the defaults
WHILE 1=1
BEGIN
SET @.constraint_name = (SELECT TOP 1 c_obj.name
FROM sysobjects t_obj
INNER JOIN sysobjects c_obj
ON t_obj.id = c_obj.parent_obj
INNER JOIN syscolumns cols
ON cols.colid = c_obj.info
AND cols.id = c_obj.parent_obj
WHERE t_obj.id = OBJECT_ID('<table name>')
AND c_obj.xtype = 'D'
AND cols.[name]IN ('<column names>'))
IF @.constraint_name IS NULL BREAK
EXEC ('ALTER TABLE <table name> DROP CONSTRAINT ' + @.constraint_name)
END
Jacco Schalkwijk
SQL Server MVP
"Baranggay Ginebra" <d@.d.com> wrote in message
news:3aed85F6bv1ejU1@.individual.net...
> What's the SQL or is there a stored procedure
> to replace the DEFAULT value of a COLUMN?
> Dropping and re-adding the column seems overkill to me.
>
> thanks in advance
>|||excellent !
thank a lot : )

how to change the default table locking type ?

Hi guys,
Is there anyway to change the default recode locking type (Table
Locking) to row locking !! ?
Thanks in help
-SniperSniper
As far as I know SQL Server begins with a low type (row locking) and
increase if it needs.
"Sniper" <kakopappa@.hotmail.com> wrote in message
news:FE3110FF-AB74-42F4-92F9-C2E68CBD5C56@.microsoft.com...
> Hi guys,
> Is there anyway to change the default recode locking type (Table
> Locking) to row locking !! ?
> Thanks in help
> -Sniper|||Using the sp_indexoption stored procedure you can play with the options:
AllowRowLocks, AllowPageLocks, DisAllowRowLocks, DisAllowPageLocks for each
index.
By default, SQL Server considers the locking granularity automatically, and
the default choices it makes are usually best.
BG, SQL Server MVP
www.SolidQualityLearning.com
"Sniper" <kakopappa@.hotmail.com> wrote in message
news:FE3110FF-AB74-42F4-92F9-C2E68CBD5C56@.microsoft.com...
> Hi guys,
> Is there anyway to change the default recode locking type (Table
> Locking) to row locking !! ?
> Thanks in help
> -Sniper|||In addition to the other posts, supporting the query with good and usable in
dexes reduces the risk
of getting table locks.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sniper" <kakopappa@.hotmail.com> wrote in message
news:FE3110FF-AB74-42F4-92F9-C2E68CBD5C56@.microsoft.com...
> Hi guys,
> Is there anyway to change the default recode locking type (Table
> Locking) to row locking !! ?
> Thanks in help
> -Sniper|||Hi Tibor,
Thanks for your reply. it's like this, When I do a start transaction and do
a row modification, it locks the hole table, it there anyway to lock only th
e
modified row ?
Thanks alot for u r time and response,
-Aruna
"Tibor Karaszi" wrote:

> In addition to the other posts, supporting the query with good and usable
indexes reduces the risk
> of getting table locks.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Sniper" <kakopappa@.hotmail.com> wrote in message
> news:FE3110FF-AB74-42F4-92F9-C2E68CBD5C56@.microsoft.com...
>
>|||Not directly. As I said, make sure that you support the WHERE clauses you us
e in the UPDATE with
proper indexes and you are most likely to end up with row locks. How did you
determine that the
whole table is locked?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sniper" <kakopappa@.hotmail.com> wrote in message
news:ABBBC0D2-0273-435A-88FA-135E1F554DB4@.microsoft.com...
> Hi Tibor,
> Thanks for your reply. it's like this, When I do a start transaction and d
o
> a row modification, it locks the hole table, it there anyway to lock only
the
> modified row ?
> Thanks alot for u r time and response,
> -Aruna
>
> "Tibor Karaszi" wrote:
>|||When I open the table from the Enterprise Manager it doesn't show the table
data. or when I do a SELECT * FROM table it's waitting till the table lock t
o
be released.
thanks,
Sniper
"Tibor Karaszi" wrote:

> Not directly. As I said, make sure that you support the WHERE clauses you
use in the UPDATE with
> proper indexes and you are most likely to end up with row locks. How did y
ou determine that the
> whole table is locked?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Sniper" <kakopappa@.hotmail.com> wrote in message
> news:ABBBC0D2-0273-435A-88FA-135E1F554DB4@.microsoft.com...
>
>|||Use sp_who, sp_who2, sp_lock etc to determine what types of locks the connec
tion which has modified
the data has on the table. Also, you still haven't provided us with the tabl
e structure and what
indexes you have on the table and what your UPDATE statement look like.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sniper" <kakopappa@.hotmail.com> wrote in message
news:5923EEE1-890F-454C-B694-7E9279D605F3@.microsoft.com...
> When I open the table from the Enterprise Manager it doesn't show the tab
le
> data. or when I do a SELECT * FROM table it's waitting till the table lock
to
> be released.
> thanks,
> Sniper
> "Tibor Karaszi" wrote:
>|||Also, if you have updated some row, and haven't committed that transaction,
any process which tries
to read *all* rows will be blocked.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sniper" <kakopappa@.hotmail.com> wrote in message
news:5923EEE1-890F-454C-B694-7E9279D605F3@.microsoft.com...
> When I open the table from the Enterprise Manager it doesn't show the tab
le
> data. or when I do a SELECT * FROM table it's waitting till the table lock
to
> be released.
> thanks,
> Sniper
> "Tibor Karaszi" wrote:
>

How to change the default port number

hai all,
I need to change the default port number of the SQL server from 1433 to some other port number. i know that it can be done thru SERVER NETWORK UTILITY. Can some one confirm that. It will be helpful.
Also i want to make sure that port to given is free port. how can i find that particular port is free.

Thanking in Advance, awaiting Quick response.

Signing Off
Venkatesh.VYes, SERVER NETWORK UTILITY can do it.
U can make a program to test if the port is not be used.sql

How to change the default no. of errorlogs from 7 to new value?

How to change the default no. of errorlogs from 7 to new value?This is a multi-part message in MIME format.
--=_NextPart_000_0D2C_01C393E9.9D7E29A0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
In EM, click on Management. Right-click on SQL Server Logs->Configure.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Samir" <samir@.yahoo.com> wrote in message =news:07dd01c39407$d988a9c0$a001280a@.phx.gbl...
--=_NextPart_000_0D2C_01C393E9.9D7E29A0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

In EM, click on Management. =Right-click on SQL Server Logs->Configure.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Samir" wrote in message news:07dd01c39407$d9=88a9c0$a001280a@.phx.gbl...

--=_NextPart_000_0D2C_01C393E9.9D7E29A0--

How to change the default dateformat?

My Sql Server connection has default dateformat to "ymd".
I have to change the dateformat to "dmy" each time when I insert or
update a datetime table column e.g.with '31.12.2005' using the
"set dateformat dmy" command. Does anybody know, how to change the
default dateformat to avoid to perform the set dateformat on
each connection?
thanks
Libor
Look here:
You can change the settings of the server, but if yor provider will
change the settings during the login process and sets it back to the
default language settings of the user you have to convert the date in a
explicit format or change the codepage of the user:
http://groups.google.de/group/micros...2557646f8b9a9b
http://groups.google.de/group/micros...144e7348a7b6ff
HTH, Jens Suessmeyer.

How to change the default dateformat?

My Sql Server connection has default dateformat to "ymd".
I have to change the dateformat to "dmy" each time when I insert or
update a datetime table column e.g.with '31.12.2005' using the
"set dateformat dmy" command. Does anybody know, how to change the
default dateformat to avoid to perform the set dateformat on
each connection?
thanks
LiborLook here:
You can change the settings of the server, but if yor provider will
change the settings during the login process and sets it back to the
default language settings of the user you have to convert the date in a
explicit format or change the codepage of the user:
http://groups.google.de/group/microsoft.public.sqlserver.programming/browse_frm/thread/6496fbdbbd4cffd9/952557646f8b9a9b?lnk=st&q=serversettings+date+format&rnum=1&hl=en#952557646f8b9a9b
http://groups.google.de/group/microsoft.public.sqlserver.server/browse_frm/thread/6f543ebd963f6e20/46144e7348a7b6ff?lnk=st&q=%22Jens+Suessmeyer%22+boss+tibor&rnum=1&hl=en#46144e7348a7b6ff
HTH, Jens Suessmeyer.

How to change the default dateformat?

My Sql Server connection has default dateformat to "ymd".
I have to change the dateformat to "dmy" each time when I insert or
update a datetime table column e.g.with '31.12.2005' using the
"set dateformat dmy" command. Does anybody know, how to change the
default dateformat to avoid to perform the set dateformat on
each connection?
thanks
LiborLook here:
You can change the settings of the server, but if yor provider will
change the settings during the login process and sets it back to the
default language settings of the user you have to convert the date in a
explicit format or change the codepage of the user:
http://groups.google.de/group/micro...52557646f8b9a9b
http://groups.google.de/group/micro...6144e7348a7b6ff
HTH, Jens Suessmeyer.

How to change the default Dateformat for Database

Hi,

I want to change the default date format from 'mdy' to 'dmy'. I can do this for session by using

SET DATEFORMAT 'dmy'

but I want to this permanent on Database level (preferred) or SQL instance level how can I do this?

Thanks in advance

Try the link below for the correct SQL Server DateTime guide.

http://www.karaszi.com/SQLServer/info_datetime.asp

sql

How to change the default database folder

When we restore an .bak to SqlServer 2005, it will place the .mdb file in
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data
How can I change this default database folder?By default the restore database command will try to restore the database
files to their original locations when the database was backed up. If you ar
e
using Management Studio on the Restore Database window select the Options ta
b
and update the 'Restore As' section as needed.
If you already restored the database you can still move the files to some
other location by using, for example, detach, move the database files and th
e
attach the database again.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"ad" wrote:

> When we restore an .bak to SqlServer 2005, it will place the .mdb file in
> C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data
> How can I change this default database folder?
>
>|||You can change default database locations setting from SSMS. Go to Instance'
s Server Properties\Database Settings. There, you'll see "Database default
locations" at the bottom of the window.
Ekrem nsoy
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:ud5iIjINIHA.5040@.TK2MSFTNGP04.phx.gbl...
> When we restore an .bak to SqlServer 2005, it will place the .mdb file in
> C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data
> How can I change this default database folder?
>

How to change the default database folder

When we restore an .bak to SqlServer 2005, it will place the .mdb file in
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data
How can I change this default database folder?By default the restore database command will try to restore the database
files to their original locations when the database was backed up. If you are
using Management Studio on the Restore Database window select the Options tab
and update the 'Restore As' section as needed.
If you already restored the database you can still move the files to some
other location by using, for example, detach, move the database files and the
attach the database again.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"ad" wrote:
> When we restore an .bak to SqlServer 2005, it will place the .mdb file in
> C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data
> How can I change this default database folder?
>
>|||You can change default database locations setting from SSMS. Go to Instance'
s Server Properties\Database Settings. There, you'll see "Database default
locations" at the bottom of the window.
--
Ekrem Önsoy
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:ud5iIjINIHA.5040@.TK2MSFTNGP04.phx.gbl...
> When we restore an .bak to SqlServer 2005, it will place the .mdb file in
> C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data
> How can I change this default database folder?
>

How to change the default database folder

When we restore an .bak to SqlServer 2005, it will place the .mdb file in
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data
How can I change this default database folder?
By default the restore database command will try to restore the database
files to their original locations when the database was backed up. If you are
using Management Studio on the Restore Database window select the Options tab
and update the 'Restore As' section as needed.
If you already restored the database you can still move the files to some
other location by using, for example, detach, move the database files and the
attach the database again.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"ad" wrote:

> When we restore an .bak to SqlServer 2005, it will place the .mdb file in
> C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data
> How can I change this default database folder?
>
>
|||You can change default database locations setting from SSMS. Go to Instance'
s Server Properties\Database Settings. There, you'll see "Database default
locations" at the bottom of the window.
Ekrem nsoy
"ad" <flying@.wfes.tcc.edu.tw> wrote in message
news:ud5iIjINIHA.5040@.TK2MSFTNGP04.phx.gbl...
> When we restore an .bak to SqlServer 2005, it will place the .mdb file in
> C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data
> How can I change this default database folder?
>

How to change the default backup data directory in SQL Server 2000?

Hi,

I had SQL Server 2000 installed like below:

Installation Directory = C:\Program Files\Microsoft SQL Server

Default data directory = D:\

Default log directory = D:\

I found out that the data is stored in D:\Program Files\Microsoft SQL Server\MSSQL\data and backup is done in D:\Program Files\Microsoft SQL Server\MSSQL\backup

However, Microsoft says we should never put the ..\backup and ..\data folders in the same partition.

My question is: How can I move the path of the backup directory?

I checked the registy but this is what I found:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer

..BackupDirectory=C:\Program Files\Microsoft SQL Server\MSSQL\backup (<-- note it is pointing to C:\ not D:\)

..DefaultData=D:\

Can somebody help please?

http://search.msn.com/results.aspx?q=Introduction+to+Microsoft+SQL+Server%e2%84%a2+2000+Reporting+Services

Beneficial or non-beneficial link above

http://support.microsoft.com/kb/185663/

|||

Hi,

As you reach to the correct part change the values of .BackupDirectory=<pathyouwanttoSoreBackUp>

or/and if you are using Schedule/Maintanance Plan you may specify the path their too.

Hemantgiri S. Goswami

How to change the default "filter" on Executionlog?

In the ReportServer database there is a table named Executionlog, which
contains data on the reports executed. But at any given time, it only seems
to contain data for the last 2 months. How do I change this filter?Found it!
Open SQL Server Management Studio and connect to Reporting Services. Right
click on the server name in the Object Explorer and select Properties. Under
the "Logging" folder you can change days of history.
"Soren" wrote:
> In the ReportServer database there is a table named Executionlog, which
> contains data on the reports executed. But at any given time, it only seems
> to contain data for the last 2 months. How do I change this filter?

how to change the decimal symbol

Hi , i have a problem i live in Europe (Spain) and we use
the " , " symbol instead of " . " as decimal symbol for
money. Actually my problem is that i have a table with
some varchar fields storing money values but when i try to
use them in stored procedures as money using Cast(field as
money) sql Server don't use the " , " symbol as decimal
and the values i get are useless.
How can i set up the sql server to change the , for the .?
thanks in advance.
hi Jorge
"Jorge Lozano" <capikirk@.terra.es> ha scritto nel messaggio
news:189101c4bc43$eb5bcff0$a501280a@.phx.gbl
> Hi , i have a problem i live in Europe (Spain) and we use
> the " , " symbol instead of " . " as decimal symbol for
> money. Actually my problem is that i have a table with
> some varchar fields storing money values but when i try to
> use them in stored procedures as money using Cast(field as
> money) sql Server don't use the " , " symbol as decimal
> and the values i get are useless.
> How can i set up the sql server to change the , for the .?
> thanks in advance.
first of all, you should not pass decimal as chars, but you should treat
them as numeric values..
SQL Server correctly understand only the dot "." as decimal place holder
and, as it's not a reporting tool but a data tool, it just pass them in and
out that way.. no thousend placeholder and "." for decimal
befor casting it, you should convert decimal sep to "."..
SET NOCOUNT ON
DECLARE @.n VARCHAR(14)
DECLARE @.dec DECIMAL (14,4)
PRINT 'this number, 123456.1234 is formatte in "italian" style =;-D'
SET @.n = '123.456,1234'
SELECT @.n AS [Original]
PRINT 'remove thousend placeholder'
SET @.n = REPLACE( @.n , '.' , '')
SELECT @.n AS [thousand stripped]
PRINT 'replace decimal placeholder'
SET @.n = REPLACE( @.n , ',' , '.')
SELECT @.n AS [Final string]
PRINT 'cast it to numeric value'
SET @.dec = CONVERT(DECIMAL(14,4), @.n)
SELECT @.dec AS [Casted decimal value]
PRINT 'in one single step'
SET @.n = '123.456,1234'
SELECT CONVERT(DECIMAL(14,4), REPLACE( REPLACE( @.n , '.' , '') , ',' , '.'))
AS [Final value]
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

How to change the Datatype of a column in SQL 7

Hi Everyone,

I am trying to change a column for a table by using Transact-SQL 7

The table name is ActionLog and the column name is ActiveSys

Before change:

ActiveSys datatype char(12)

After change:

ActiveSys datatype varchar(30)

I use this command:

alter table ActionLog alter column ActiveSys varchar(30) not null

but the system keeps giving me this error message:

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'column'.

I follow the syntax provide by the MS: (and I search on Google and found none of the standard SQL syntax works.)

Syntax

ALTER TABLE table
{ [ALTER COLUMN column_name
{ new_data_type [ (precision[, scale] ) ]
[ NULL | NOT NULL ]
| {ADD | DROP} ROWGUIDCOL
}
]
| ADD
{ [ <column_definition> ]
| column_name AS computed_column_expression
}[,...n]
| [WITH CHECK | WITH NOCHECK] ADD
{ <table_constraint> }[,...n]
| DROP
{ [CONSTRAINT] constraint_name
| COLUMN column
}[,...n]
| {CHECK | NOCHECK} CONSTRAINT
{ALL | constraint_name[,...n]}
| {ENABLE | DISABLE} TRIGGER
{ALL | trigger_name[,...n]}
}

Can anyone help me? or Point out what is wrong with my code?

Thanks in advance.

dont use the keyword column, just use

alter table ActionLog alter ActiveSys varchar(30) not null

|||Thanks Shyam. I found either your code or my code are wrong. the reason our code does not work because the compatibility issues. before running the code, we should run:

EXEC sp_dbcmptlevel '<Database name>', 70
alter table ActionLog alter ActiveSys varchar(30) not null
EXEC sp_dbcmptlevel '<Database name>', 65

Cheers.

How to change the datasource view of a mining model.

Hi,

I created on model in Business Intelligence Development studio.I wants to change the datasourceview(dsv) assigned for that view.for e.g

My mining Model is StudModel.dmm.

The dsv for that model is StudDSV.dsv.

I wants to change the dsv for that model to marksDSV.dsv.

How to do that in Business Intelligence Development studio.

Thanks,

Karthik.

The BI Dev Studio does not provide you with much support for this task.

You can change the DSV manually. It may be very easy or more difficult, depending on how similar the new DSV is compared to the original DSV.

Here is something you can do, manually:

- close the Mining structure designer window

- open the DMM file in BI Dev Studio (right click and select the View Code option)

- look for the <DataSourceViewID> element in the XML. Replace the content of the element with the id of your new data source view

- close and save the file

- double click on the DMM file to load it in the designer and validate the change

Now, if your new data source view contains different tables, or if those tables have differrent columns, more work needs to be done.

Note that the changes will only be visible after you re-deploy and process fully your mining structure

Hope this helps

sql

How to change the DataBase Collation!

Hello all:
I try to change the Collation of database, but it not work in
vb.net 2005, have any one do it before?
MatchSQL,
Use "alter database", but this does not change the collation for all char /
nchar / varchar / nvarchar / etc. columns.
How to transfer a database from one collation to another collation in SQL
Server
[url]http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?dg=microsoft.public.sqlserver.server& cat=en_US_671e06d0-f20d-4bb3-9c6a-42c825ddb1dc&lang=en&cr=US[/url]
AMB
"MatchSQL" wrote:

> Hello all:
> I try to change the Collation of database, but it not work in
> vb.net 2005, have any one do it before?
>

How to change the DataBase Collation!

Hello all:
I try to change the Collation of database, but it not work in
vb.net 2005, have any one do it before?MatchSQL,
Use "alter database", but this does not change the collation for all char /
nchar / varchar / nvarchar / etc. columns.
How to transfer a database from one collation to another collation in SQL
Server
http://www.microsoft.com/communitie...n&cr=US

AMB
"MatchSQL" wrote:

> Hello all:
> I try to change the Collation of database, but it not work in
> vb.net 2005, have any one do it before?
>

How to change the DataBase Collation!

Hello all:
I try to change the Collation of database, but it not work in
vb.net 2005, have any one do it before?MatchSQL,
Use "alter database", but this does not change the collation for all char /
nchar / varchar / nvarchar / etc. columns.
How to transfer a database from one collation to another collation in SQL
Serve
http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?dg=microsoft.public.sqlserver.server&cat=en_US_671e06d0-f20d-4bb3-9c6a-42c825ddb1dc&lang=en&cr=US
AMB
"MatchSQL" wrote:
> Hello all:
> I try to change the Collation of database, but it not work in
> vb.net 2005, have any one do it before?
>

How to change the data type of an IDENTITY column

Hi.
I have an existing table with an INT IDENTITY column; it contains some data
rows.
I want to change the identity column type to smallint (the data values are
within range) but am stumped. Here's what I'm trying to do (assume the
identity column is called Id):
Create tempId smallint column
save Id values in tempId
Drop Id column
Recreate Id column as identity smallint.
*** Update Id column with values from tempId
Drop tempId column
As you know, it fails on the Update step above: cannot update identity
column (I've tried set identity_insert on and that makes no difference - now
where's the set identity_update function...)
Can anyone suggest a way of doing it?
Thanks
Michael
Why are you re-creating the column as an IDENTITY? Just create it as
SMALLINT; you can't have it both ways.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Mic" <micspam@.jadegroup.co.uk> wrote in message
news:223A4008-4C07-4AF3-BA56-20D93FA17570@.microsoft.com...
> Hi.
> I have an existing table with an INT IDENTITY column; it contains some
data
> rows.
> I want to change the identity column type to smallint (the data values are
> within range) but am stumped. Here's what I'm trying to do (assume the
> identity column is called Id):
> Create tempId smallint column
> save Id values in tempId
> Drop Id column
> Recreate Id column as identity smallint.
> *** Update Id column with values from tempId
> Drop tempId column
> As you know, it fails on the Update step above: cannot update identity
> column (I've tried set identity_insert on and that makes no difference -
now
> where's the set identity_update function...)
> Can anyone suggest a way of doing it?
> Thanks
> Michael
>
>
|||Adam,
Thanks, but it needs to be an identity column. And I can have it both ways!
I've just found a way to do what I want:
Save the data into a temp table
delete the data
drop the int identity column
recreate the identity column as smallint
identity_insert on
insert rows from temp table
identity_insert off
drop temp table.
A bit long winded, but it works. (I've left out minor detail re. constraints
etc.)
Thanks
Michael
"Adam Machanic" wrote:

> Why are you re-creating the column as an IDENTITY? Just create it as
> SMALLINT; you can't have it both ways.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Mic" <micspam@.jadegroup.co.uk> wrote in message
> news:223A4008-4C07-4AF3-BA56-20D93FA17570@.microsoft.com...
> data
> now
>
>
|||Yes, that works.
Keep in mind that you'll have to insert those rows one-by-one with a cursor
or loop if you want to guarantee the same order.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Mic" <micspam@.jadegroup.co.uk> wrote in message
news:419FB142-5006-4418-8C59-CCEAFBD440A8@.microsoft.com...
> Adam,
> Thanks, but it needs to be an identity column. And I can have it both
ways!
> I've just found a way to do what I want:
> Save the data into a temp table
> delete the data
> drop the int identity column
> recreate the identity column as smallint
> identity_insert on
> insert rows from temp table
> identity_insert off
> drop temp table.
> A bit long winded, but it works. (I've left out minor detail re.
constraints
> etc.)
> Thanks
> Michael
>
|||Adam,
Sorry I don't understand.
What do you mean by order and why would this be important to me?
Thanks Michael
"Adam Machanic" wrote:

> Yes, that works.
> Keep in mind that you'll have to insert those rows one-by-one with a cursor
> or loop if you want to guarantee the same order.
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Mic" <micspam@.jadegroup.co.uk> wrote in message
> news:419FB142-5006-4418-8C59-CCEAFBD440A8@.microsoft.com...
> ways!
> constraints
>
>
|||"Mic" <micspam@.jadegroup.co.uk> wrote in message
news:BE612CBA-EA74-492B-B157-30B17F4DB131@.microsoft.com...
> Adam,
> Sorry I don't understand.
> What do you mean by order and why would this be important to me?
Sorry, I was thinking of something else totally unrelated! Disregard.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
sql

How to change the data type of an IDENTITY column

Hi.
I have an existing table with an INT IDENTITY column; it contains some data
rows.
I want to change the identity column type to smallint (the data values are
within range) but am stumped. Here's what I'm trying to do (assume the
identity column is called Id):
Create tempId smallint column
save Id values in tempId
Drop Id column
Recreate Id column as identity smallint.
*** Update Id column with values from tempId
Drop tempId column
As you know, it fails on the Update step above: cannot update identity
column (I've tried set identity_insert on and that makes no difference - now
where's the set identity_update function...)
Can anyone suggest a way of doing it?
Thanks
MichaelWhy are you re-creating the column as an IDENTITY? Just create it as
SMALLINT; you can't have it both ways.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Mic" <micspam@.jadegroup.co.uk> wrote in message
news:223A4008-4C07-4AF3-BA56-20D93FA17570@.microsoft.com...
> Hi.
> I have an existing table with an INT IDENTITY column; it contains some
data
> rows.
> I want to change the identity column type to smallint (the data values are
> within range) but am stumped. Here's what I'm trying to do (assume the
> identity column is called Id):
> Create tempId smallint column
> save Id values in tempId
> Drop Id column
> Recreate Id column as identity smallint.
> *** Update Id column with values from tempId
> Drop tempId column
> As you know, it fails on the Update step above: cannot update identity
> column (I've tried set identity_insert on and that makes no difference -
now
> where's the set identity_update function...)
> Can anyone suggest a way of doing it?
> Thanks
> Michael
>
>|||Adam,
Thanks, but it needs to be an identity column. And I can have it both ways!
I've just found a way to do what I want:
Save the data into a temp table
delete the data
drop the int identity column
recreate the identity column as smallint
identity_insert on
insert rows from temp table
identity_insert off
drop temp table.
A bit long winded, but it works. (I've left out minor detail re. constraints
etc.)
Thanks
Michael
"Adam Machanic" wrote:
> Why are you re-creating the column as an IDENTITY? Just create it as
> SMALLINT; you can't have it both ways.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Mic" <micspam@.jadegroup.co.uk> wrote in message
> news:223A4008-4C07-4AF3-BA56-20D93FA17570@.microsoft.com...
> > Hi.
> > I have an existing table with an INT IDENTITY column; it contains some
> data
> > rows.
> > I want to change the identity column type to smallint (the data values are
> > within range) but am stumped. Here's what I'm trying to do (assume the
> > identity column is called Id):
> >
> > Create tempId smallint column
> > save Id values in tempId
> > Drop Id column
> > Recreate Id column as identity smallint.
> > *** Update Id column with values from tempId
> > Drop tempId column
> >
> > As you know, it fails on the Update step above: cannot update identity
> > column (I've tried set identity_insert on and that makes no difference -
> now
> > where's the set identity_update function...)
> >
> > Can anyone suggest a way of doing it?
> > Thanks
> > Michael
> >
> >
> >
>
>|||Yes, that works.
Keep in mind that you'll have to insert those rows one-by-one with a cursor
or loop if you want to guarantee the same order.
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Mic" <micspam@.jadegroup.co.uk> wrote in message
news:419FB142-5006-4418-8C59-CCEAFBD440A8@.microsoft.com...
> Adam,
> Thanks, but it needs to be an identity column. And I can have it both
ways!
> I've just found a way to do what I want:
> Save the data into a temp table
> delete the data
> drop the int identity column
> recreate the identity column as smallint
> identity_insert on
> insert rows from temp table
> identity_insert off
> drop temp table.
> A bit long winded, but it works. (I've left out minor detail re.
constraints
> etc.)
> Thanks
> Michael
>|||Adam,
Sorry I don't understand.
What do you mean by order and why would this be important to me?
Thanks Michael
"Adam Machanic" wrote:
> Yes, that works.
> Keep in mind that you'll have to insert those rows one-by-one with a cursor
> or loop if you want to guarantee the same order.
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Mic" <micspam@.jadegroup.co.uk> wrote in message
> news:419FB142-5006-4418-8C59-CCEAFBD440A8@.microsoft.com...
> > Adam,
> > Thanks, but it needs to be an identity column. And I can have it both
> ways!
> > I've just found a way to do what I want:
> > Save the data into a temp table
> > delete the data
> > drop the int identity column
> > recreate the identity column as smallint
> > identity_insert on
> > insert rows from temp table
> > identity_insert off
> > drop temp table.
> >
> > A bit long winded, but it works. (I've left out minor detail re.
> constraints
> > etc.)
> > Thanks
> > Michael
> >
>
>|||"Mic" <micspam@.jadegroup.co.uk> wrote in message
news:BE612CBA-EA74-492B-B157-30B17F4DB131@.microsoft.com...
> Adam,
> Sorry I don't understand.
> What do you mean by order and why would this be important to me?
Sorry, I was thinking of something else totally unrelated! Disregard.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--

How to change the Data Path specified in the "SQL Server (MSSQLSERVER)" service proper

I have connected to the SQL Server 2005 instance usign the SQL Server Management Studio; I have changed the default locations for the database and log files. I have also rebooted the machine. When I look in the SQL Server Configuration Manager in the properties for the SQL Server (MSSQLSERVER) I see that the data path is still set to the old value and this field is read-only. How can this can changed without going through the registry?

What path are you talking about?

Changing the default database path does not change the path of the SQL Server binarys.

|||I am referring to the path that you see in the Service Properties through the SQL Server Configuration Manager, the tooltip and the Online Help is a bit contradictory concerning this path. Nevertheless I figured out where to change it in the registry but instead I did a full reinstall of SQL Server 2005.|||

You don't want to change this path. If you do the SQL Server won't start. The "Binary Path" which is listed is the path to the actualy exe file which is the SQL Server service. The only way to change this would be to uninstall and reinstall the SQL Server.

What is the end result that you are trying to get?

|||If you look closely and the tooltip for that path and the same explanation in the help when you hit F1 then you will notice that it is not really clear what this path is for. This path is not the binary path because I installed SQL Server in C:\Program Files\... and during the installation I changed the location for the databases to D:\Databases. The path I am talking about was set to D:\Databases but afterwards I had to move the data files to E:\Databases so in SQL Server I changed the database location to E:\Databases but for some reason the in the SQL Server it still pointed to D:\Databases which in the meantime no longer existed. I finally had to reinstall SQL Server anyway because the binaries had to be in the D:\Program Files\ and not on the C-drive.

How to change the Data Path specified in the "SQL Server (MSSQLSERVER)" service proper

What path are you talking about?

Changing the default database path does not change the path of the SQL Server binarys.

What is the end result that you are trying to get?

how to change the data label in SQL?

I have the following SQL statement..

select month(dbo.udfAddUTCBias(start_date_time, 180)) as report_month, direction as direction, count(*) as total_records from traffic with (nolock) where dbo.udfAddUTCBias(start_date_time, 180) >= '2/24/2007' and dbo.udfAddUTCBias(start_date_time, 180) < DateAdd(dd, 1, '2/24/2007') group by month(dbo.udfAddUTCBias(start_date_time, 180)), direction

but the result of the direction field is 1 and 0..

however, I need to show inbound when the result is 0 and outbound when the result is 1 without changing the actual data in the table.

You could use CASE keyword: http://msdn2.microsoft.com/en-us/library/aa258235(SQL.80).aspx

select month(dbo.udfAddUTCBias(start_date_time, 180)) as report_month

, case direction

when 0 then 'inbound'

when 1 then 'outbound'

end

as direction

, count(*) as total_records from traffic with (nolock) where dbo.udfAddUTCBias(start_date_time, 180) >= '2/24/2007' and dbo.udfAddUTCBias(start_date_time, 180) < DateAdd(dd, 1, '2/24/2007') group by month(dbo.udfAddUTCBias(start_date_time, 180)), direction

How to change the data connection for another DB using different DSN name

Hi,

i have 2 MSSQL DBs.
im using DSN name to connect MSSQL DB to generate the report. for example, i have designed a report CR1 from the DB using dsn, named DSN1 which refers
the DB named Master1.

but now i want to connect the same report CR1 in a another dsn name called as
DSN2 which refers MSSQL DB named as Master2.
(because the both DB's have the same table, but i want the report to print from another DB's table. )

but at runtime if i do the coding from vb application like,

crystalreport1.connect = "DSN=DSN1"

(this coding works if the dsn name as DSN1)

its giving error msg like "error detected by database dll".

How could i make the data connectivity at runtime to point the different dsn name ie, DSN2. (consider i design the report manually at design mode of CR and connect the DB thru DSN ie, DSN1 refers Master1 DB and call it in vb application at runtime.):confused:
Hello

We have developed a report in crystal that connects with our local database. After development we want to deploy the same to our clients with different data soruce name and data base name.

Presently we are taking each report file and configuring the client database by replacing the existing data source.

How can we achieve this programmatically thru VB?

Thanks & Regards
subramanisql

how to change the crystal report database location at runtime in vb.net

Hello,

I am using the following codings to change the databse location at runtime. But i found an error 'invalid field name in formula' in some of the reports. The error occurs when we use the databse fields in 'formula field editor' of crystal reports. let me know the reason why its happen? find and send a solution to solve this problem.

Dim logOnInfo As New TableLogOnInfo()
Dim crtableLogoninfo As New TableLogOnInfo()
Dim crConnectionInfo As New ConnectionInfo()
Dim TableLocation As String

Dim vTable As Table
crConnectionInfo.ServerName = gConnServerName
crConnectionInfo.DatabaseName = gConnDatabase
crConnectionInfo.UserID = gConnUserName
crConnectionInfo.Password = gConnPassword

RptDoc.SetDatabaseLogon(gConnUserName, gConnPassword, gConnServerName, gConnDatabase)

For Each vTable In RptDoc.Database.Tables
crtableLogoninfo = vTable.LogOnInfo
crtableLogoninfo.ConnectionInfo = crConnectionInfo
vTable.ApplyLogOnInfo(crtableLogoninfo)
'' vTable.TestConnectivity()
'' MsgBox(vTable.TestConnectivity)
'' MsgBox(vTable.Fields(0).Name())

vTable.Location = Trim(crConnectionInfo.DatabaseName) & ".dbo." & Trim(vTable.Location.Substring(vTable.Location.LastIndexOf(".") + 1))

Next

thank you .
shanthipl reply

Wednesday, March 28, 2012

How to change the Credential for the replication

Dear Friends
we are having mearge replication between two domains. Now our policy for the
SA is changed and we do not use the SA account anymore for the replication.
As we have changed the password for the SA now the replication is failed
with the follwoing error.
The process could not connect to Subscriber 'SERVER'.
(Source: SERVER (Agent); Error number: 20084)
------
Login failed for user 'sa'.
(Source: SERVER (Data source); Error number: 18456)
------
Please suggest how i can change the credential so that the new username will
be used to connect the other server for the replication.
YOur earlier reply would be a great help to me.
Best regards
Sharad
It would be nice to know if this is from SQL 2000 or SQL 2005. It would
also be good to know if you are running the merge agent via a SQL Agent job,
an API call, or via the command line. Most likely you are using a SQL 2000
SQL Agent job to sync a pull merge subscriber. In this case, you need to
examine the properties of the SQL Agent job that is failing to execute the
merge agent. In the step called "Run Agent", check out the "command", this
contains the parameters used to run the merge agent in the job. You should
be able to find -SubscriberSecurityMode 0 -SubscriberLogin
sa -SubscriberPassword XXXXXX. This is where you can change the subscriber
credential information.
Hope this helps,
Tom
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sharad" <Sharad@.discussions.microsoft.com> wrote in message
news:01EA29E2-D2DA-4748-BE15-218EDEF5625D@.microsoft.com...
> Dear Friends
> we are having mearge replication between two domains. Now our policy for
> the
> SA is changed and we do not use the SA account anymore for the
> replication.
> As we have changed the password for the SA now the replication is failed
> with the follwoing error.
> The process could not connect to Subscriber 'SERVER'.
> (Source: SERVER (Agent); Error number: 20084)
> ------
> Login failed for user 'sa'.
> (Source: SERVER (Data source); Error number: 18456)
> ------
> Please suggest how i can change the credential so that the new username
> will
> be used to connect the other server for the replication.
> YOur earlier reply would be a great help to me.
> Best regards
> Sharad

How to change the content?

Hi there !

I'm quite desperate as I can't find a quick method to partially change the content of a field.

I explain:
In a field named 'notes', PART of the content is:
<a href='mailto:"abc@.domain.com"'>abc@.domain.com</a>
This content is wrong.

It must be:
<a href="http://links.10026.com/?link=mailto:abc@.domain.com">abc@.domain.com</a>

How can I make the change with a SQL Query instead of manually make the change row by row (approximately 1500 rows contain the error!!!) considering the fact that abc@.domain.com is different in each of the rows?

Many thanks in advance for any help!

Best regards,

Gerardupdate daTable
set notes=replace(replace('href=''mailto:"','"mailto:'),'''>','">')
where notes like '%href=''mailto:"%">%'|||update daTable
set notes=replace(replace('href=''mailto:"','"mailto:'),'''>','">')
where notes like '%href=''mailto:"%">%'
Hi !

Thank you for replying. I appreciate.
But, as I'm quite old and learning, I want to really understand the syntax I'll write.

Some questions arise:
1 - Why have we "replace" repeated twice?
2 - The single and double quotes you wrote in your example doesn't correspond to those I put in my example.
3 - What will happen to the email addresses which are differents?

Be sure, professor, I certainly do not want to bother you! :-)

Best regards,

Gerard|||did you try it? what results did it give?|||Yes, I tried... on a copy of the table!
After two attempts, this worked fine:
UPDATE `table_name` SET column_name = REPLACE(column_name, '"', '') WHERE column_name LIKE '%href=\'mailto:"%';

I thank you for your valuable help.

Best.|||all you have done is remove the double quotes

in other words, you've changed this --<a href='mailto:"abc@.domain.com"'>abc@.domain.com</a>to this --<a href='mailto:abc@.domain.com'>abc@.domain.com</a>which is not what you asked

you asked for this --<a href="http://links.10026.com/?link=mailto:abc@.domain.com">abc@.domain.com</a>|||YES you are right about the kind of quotes but the principle remained the same: remove the extra quotes just before and after the address, no matter if the quotes are single or double. I opted for the single quotes which are more "orthodox"! :-)|||either single or double can be used

see http://www.w3.org/TR/html4/intro/sgmltut.html#attributes

if you look at all the web sites out there (okay, just look at some, you don't really have to look at all of them) you will find that most of them use double quotes

also, the incidence of a single quote required inside a double-quoted string is more frequent than a double quote inside a single-quoted string

:)|||You are right.
I have read about attributes in the page you mentioned.
And I tried to follow your advice and to replace the single quotes with double quotes.

For example, I tried to replace where it is necessary
<a href='mailto:abc@.domain.com'>
with
<a href="http://links.10026.com/?link=mailto:abc@.domain.com">
(Of course, this text isn't the only text contained in the field...)

I wrote:
UPDATE `table_name` SET column_name = REPLACE(column_name, '', "") WHERE column_name LIKE '%mailto:\'%';
This query has no effect!

What's wrong?|||REPLACE(column_name, '''', '"')|||Still no effect !
And I perfectly know that more than a thousand rows must be affected...

I wrote:
UPDATE `table_name` SET column_name = REPLACE(column_name, '''', '"') WHERE column_name LIKE '%mailto:\'%';|||you keep messing up in different places
WHERE column_name LIKE '%mailto:''%'|||Maybe it's because I'm French but I don't understand what you mean: "you keep messing up in different places".

I wrote:
UPDATE `table_name` SET column_name = REPLACE(column_name, '''', '"') WHERE column_name LIKE '%mailto:''%';
...and nothing happens, right or wrong!|||what do you get for this query:select count(*) from table_name where column_name LIKE '%mailto:''%'and what do you get for this query:select count(*) from table_name where column_name LIKE '%mailto:"%'this should tell you why

:)|||Both queries return zero !!!!!

Which isn't true !
Here is the copy of the REAL full content of the field in one the rows:
Autres adresses de courriel : <a href='mailto:jr.reverte@.laposte.fr'><font color='#FF3300'>jr.reverte@.laposte.fr</font></a> et <a href='mailto:entsoa23@.fr.st'><font color='#FF3300'>entsoa23@.fr.st</font> (cliquables).</a>

and I want to change it to:
Autres adresses de courriel : <a href="http://links.10026.com/?link=mailto:jr.reverte@.laposte.fr"><font color='#FF3300'>jr.reverte@.laposte.fr</font></a> et <a href="http://links.10026.com/?link=mailto:entsoa23@.fr.st"><font color='#FF3300'>entsoa23@.fr.st</font> (cliquables).</a>

This is driving me quite mad... :-) GRRRR !!!|||i believe you are not copying my exact code, but rather typing it in yourself, and making errors while typing

you must make very sure that you understand the difference between two single quotes in a row:''and a doublequote:"|||Obviously, yes. I know the difference.
And I copied the exact code you posted.

But I found one error.
It isn't
'%mailto:\'%'
but
'%\'mailto:%'
we are looking for.

The position of the quote was wrong.
And this change makes the query returning a number!

Using this change, I unsuccessfully (no row affected) tried to apply the modifying query this way (real names of table and column):
UPDATE `_annuairecopy` SET notes = REPLACE ( notes, '', "") WHERE notes LIKE 'href=%\'mailto:%\'%';
(please report to the REAL content of field I previously posted)|||please stop using those horrible backslashes!!!
UPDATE `_annuairecopy`
SET notes = REPLACE ( notes, '''', '"')
WHERE notes LIKE '%href=''mailto:%'|||Yes indeed, they are horrible.
But I was so tired to get error messages each time I used an even number of single quotes without a backslash!

The query has worked fine !!!
Could you imagine how happy I am?

I thank you for your patience and all the time you spent helping me.

Best from Gerard.

How to change the connection string in a web.config file

I've recently uploaded my website, http://www.bigredsongbus.com, to my host. Unfortunately, they don't support the use of SQL Server Express. So, I've purchased an addon through my host - discountasp.net. I've attached my database file to their server, now all that I need to do is to change the connection string in my .config file so that it points to the SQL Server. I don't know how to do this. Any help, please?

Jeffrey Way

<connectionStrings>
<add name="<CONNECTIONSTRINGNAME>" connectionString="Data Source=<SERVERAME or IPADRESSPROVIDED BY HOST>;Initial Catalog=<DATABASENAME>;User ID=<USERNAME>;Password=<PASSWORD>" providerName="System.Data.SqlClient"/>
</connectionStrings>|||

connectionStrings>

clear/>

addname="LocalSQLServer"connectionString="Data Source=serverName;Initial Catalog=DatanaseName;Persist Security Info=True;User ID=sa;Password=sa"/></connectionStrings>

Hope this is what ur looking for

How to change the compatibility level to 9.0

I have a SQL 2005 server that has more than 90 databases, and I need to change the compatibality level to 9.0. Is there an easy way to do this in SQL? any help is greatly appreciated.

Emad

I don't know if it can be called easy, but the following script should do the work, by using the stored procedure sp_dbcmptlevel , to update all databases whose compatibility_level is not equal to 90.

Hope this Helps!!!

Roberto Hernandez-Pou
http://community.rhpconsulting.net

DECLARE DATABASES_80 CURSOR
READ_ONLY
FOR SELECT NAME FROM SYS.DATABASES WHERE COMPATIBILITY_LEVEL != '90'

DECLARE @.DATABASENAME NVARCHAR(255)
DECLARE @.COUNTER INT

-- INITIALIZE VARIABLES
SET @.COUNTER = 1

-- OPEN CURSOR
OPEN DATABASES_80

FETCH NEXT FROM DATABASES_80 INTO @.DATABASENAME
WHILE (@.@.fetch_status <> -1)
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN

-- CHANGE DATABASE COMPATIBILITY
EXECUTE sp_dbcmptlevel @.DATABASENAME , '90'

-- PRINT
PRINT '(' + RIGHT(('000' + CAST(@.COUNTER AS NVARCHAR(255))),3) + ') - CHANGED DATABASE COMPATIBILITY LEVEL FOR: ' + @.DATABASENAME

-- INCREASE COUNTER
SET @.COUNTER = @.COUNTER + 1

END

FETCH NEXT FROM DATABASES_80 INTO @.DATABASENAME
END

CLOSE DATABASES_80
DEALLOCATE DATABASES_80

sql

How to change the column measure into Row Measure in Reporting services

Hi,

I am wondering how to create a matrix that contains 1 dimension for Top Label (Column), let's say "Year-Month"

and then 2 Measure to be in the row format rather than columnar format.

Example as below :

Year-Month on the column, and the measure is on the row :

2007-04 2007-05 2007-06 Amount Sales 1000 2000 3000 Unit Sales 10 20 30 Total 1010 2020 3030

Please share with me if you have this solution in Reporting services as it works in excel, hyperion brio, bo, cognos but somehow cannot see that function in Reporting Services.

Thanks

best regards,

Tanipar

This is easily supported (no need to quote every other tool under the sun to prove your piont).

You just have to drag the field from the dataset window to the right area and drop when you see a horizontal bar

How to change the color of a bar in a bar chart?

I am having a problem setting the colors of the bars in a bar chart. It seems like it would be the simplest thing, but apparently it's not.

The "series" is grouped on whether or not the value is positive -- if so, the bars are blue (by default) and if not, green (by default). I want to change it so the positive color is blue and the negative color is red.

I found that I can change ALL the bars to one color by going to chart properties, Data tab, Values Edit, Appearance tab, Series Style, Fill. There I set the color to red and ALL the bars became red. But this is not what I want.

I tried using a conditional statement like IIf(Fields!IsPositive.Value=True,Blue,Red) for the color, but (not surprisingly), it told me I hadn't defined Blue and Red.

Does anyone know exactly how to do this?

Nobody has even a clue how to do this? I can't find it documented anywhere .. is it impossible?|||

hi there

there is some information on the following link - not sure if it will help but look at figure 14 P10

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/MoreSSRSCharts.asp

cheers

|||

IIf(Fields!IsPositive.Value=True,Blue,Red)

Try to use blue and red in double quotes, it should solve the problem.

i hope by now you figured how to change the colors of the bars , the previous link has all the information, but to be in depth, for your case, you can write a code snippet in the code tab of the report properties by writing a simple vb code. for the logic you want to do it.

you can do something like this, pass a parameter ( i mean the value : fields!Positive.Value )

If (so and so.. )

Color = '03938x'

-- something like this.

Let me know if this helps.

and call this method in the data field properties.

=Code.YourFunc(Parameters)

Kishore

|||Yes, thank you very much! Putting the colors in quotes does it (duh!). And that link has great information. Thanks again!|||

Hi!!

I do it in this way: in the "Chart Properties", data tab, I edit the value and in "Appearance" I go to "Series style..." and in fill tab, in Color, I set the next sentence:

=Code.GetColor(Fields!Criticity.Value)

And in the Report Properties, Code tab:

Public Function GetColor(ByVal strCriticity As String) As String
Select Case strCriticity
Case 0
Return "LemonChiffon"
Case 1
Return "Gold"
Case 2
Return "DarkOrange"
Case 3
Return "DarkRed"
End Select
End Function

Well, it works... but this has a problem: if in the bar chart appear the 4 colors in the different rows but the last bar only has 2 of them, in the legend only will appear that two colors, and the other 2 will be by default (a blue and a green, it will depend of the selected palette)...

How can I do it to show the legend with the correct colors?

If I put the code like cedubose:

= Switch (Fields!Criticity.Value = 0, "LemonChiffon", Fields!Criticity.Value = 1, "Gold", Fields!Criticity.Value = 2, "DarkOrange", Fields!Criticity.Value = 3, "DarkRed")

it works different but not correctly, i means, in 3 of the 4 legends appear the same color and in the other legend the another color...

All suggestions will be wellcome... thx!!

|||

Problem solved!!!

If somebody has the same problem, here is the solution:

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

|||

The only way I've found of doing it is to

1) Select Tools/Options/Color

2) Modify the colours in "Chart Fill"

...should be easier, but.

How to change the color of a bar in a bar chart?

I am having a problem setting the colors of the bars in a bar chart. It seems like it would be the simplest thing, but apparently it's not.

I tried using a conditional statement like IIf(Fields!IsPositive.Value=True,Blue,Red) for the color, but (not surprisingly), it told me I hadn't defined Blue and Red.

Does anyone know exactly how to do this?

Nobody has even a clue how to do this? I can't find it documented anywhere .. is it impossible?|||

hi there

there is some information on the following link - not sure if it will help but look at figure 14 P10

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/MoreSSRSCharts.asp

cheers

|||

IIf(Fields!IsPositive.Value=True,Blue,Red)

Try to use blue and red in double quotes, it should solve the problem.

you can do something like this, pass a parameter ( i mean the value : fields!Positive.Value )

If (so and so.. )

Color = '03938x'

-- something like this.

Let me know if this helps.

and call this method in the data field properties.

=Code.YourFunc(Parameters)

Kishore

|||Yes, thank you very much! Putting the colors in quotes does it (duh!). And that link has great information. Thanks again!|||

Hi!!

I do it in this way: in the "Chart Properties", data tab, I edit the value and in "Appearance" I go to "Series style..." and in fill tab, in Color, I set the next sentence:

=Code.GetColor(Fields!Criticity.Value)

And in the Report Properties, Code tab:

How can I do it to show the legend with the correct colors?

If I put the code like cedubose:

= Switch (Fields!Criticity.Value = 0, "LemonChiffon", Fields!Criticity.Value = 1, "Gold", Fields!Criticity.Value = 2, "DarkOrange", Fields!Criticity.Value = 3, "DarkRed")

it works different but not correctly, i means, in 3 of the 4 legends appear the same color and in the other legend the another color...

All suggestions will be wellcome... thx!!

|||

Problem solved!!!

If somebody has the same problem, here is the solution:

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

|||

The only way I've found of doing it is to

1) Select Tools/Options/Color

2) Modify the colours in "Chart Fill"

...should be easier, but.

How to change the color of a bar in a bar chart?

I am having a problem setting the colors of the bars in a bar chart. It seems like it would be the simplest thing, but apparently it's not.

I tried using a conditional statement like IIf(Fields!IsPositive.Value=True,Blue,Red) for the color, but (not surprisingly), it told me I hadn't defined Blue and Red.

Does anyone know exactly how to do this?

Nobody has even a clue how to do this? I can't find it documented anywhere .. is it impossible?|||

hi there

there is some information on the following link - not sure if it will help but look at figure 14 P10

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/MoreSSRSCharts.asp

cheers

|||

IIf(Fields!IsPositive.Value=True,Blue,Red)

Try to use blue and red in double quotes, it should solve the problem.

you can do something like this, pass a parameter ( i mean the value : fields!Positive.Value )

If (so and so.. )

Color = '03938x'

-- something like this.

Let me know if this helps.

and call this method in the data field properties.

=Code.YourFunc(Parameters)

Kishore

|||Yes, thank you very much! Putting the colors in quotes does it (duh!). And that link has great information. Thanks again!|||

Hi!!

I do it in this way: in the "Chart Properties", data tab, I edit the value and in "Appearance" I go to "Series style..." and in fill tab, in Color, I set the next sentence:

=Code.GetColor(Fields!Criticity.Value)

And in the Report Properties, Code tab:

How can I do it to show the legend with the correct colors?

If I put the code like cedubose:

= Switch (Fields!Criticity.Value = 0, "LemonChiffon", Fields!Criticity.Value = 1, "Gold", Fields!Criticity.Value = 2, "DarkOrange", Fields!Criticity.Value = 3, "DarkRed")

it works different but not correctly, i means, in 3 of the 4 legends appear the same color and in the other legend the another color...

All suggestions will be wellcome... thx!!

|||

Problem solved!!!

If somebody has the same problem, here is the solution:

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

|||

The only way I've found of doing it is to

1) Select Tools/Options/Color

2) Modify the colours in "Chart Fill"

...should be easier, but.

How to change the color of a bar in a bar chart

For the life of me, I can't find how to change the color of the bar in a bar chart.

It seems like it would be a simple thing, but I can't find anything in the properties that seems to relate to it.

I have a bar chart with subtype as "bar chart" -- i.e., just a bar for each data point, either positive or negative. The positive should be blue; the negative red. As it is now, the default color for positive is blue, for negative green, but management does not like that color combo.

Can anyone help?

Thanks in advance...

Hi,

I'm talking out of my hat a bit on this one, but I believe that what you want to look at is the use of "skins" and "themes." I'm guessing two things, that you are talking about a barchart control that you're using and that you are using ASP.Net 2.0 (Skins and Themes aren't available in 1.1). I'm also guessing that you have used CSS to sytle html, but can't find a way to get it work on the control - you need to use skins and themes. Here's a vid to look athttp://msevents.microsoft.com/cui/WebCastEventDetails.aspx?EventID=1032278423&EventCategory=5&culture=en-US&CountryCode=US

There's one other thing to look athttp://asp.net/CSSAdapters/Default.aspx. I haven't tried them myself, but looks like it might help you to get the control to play nice with CSS. Hope it helps. BRN..

|||

Well thanks Brian, but it looks like what you're talking about is HTML -- my problem is with a chart control inside a report using SQL Server Reporting Service.

I did find a way to get it to change the color of ALL the bars -- properties dialog, data tab, values edit, Appearance tab, Fill tab (something like that -- I don't have it in front of me right now). If I change the color of the fill, ALL the bars change to that color.

However, I don't want ALL the bars to be the same color. I have the "series" grouped according to whether the value is positive or negative. By default, RS makes the positive bars blue and the negative bars green. I need the negative bars to be red.

I tried using an expression like IIf(Fields!IsPositive.Value=True,Blue,Red) but it told me (predictably) that I hadn't defined Blue and Red. But I think perhaps the right expression for the "fill" color might work. I just don't know what it is.

|||

Hi,

Not familiar with the control you mentioned (but I'll try to take a look if I have time). CSS does work on HTML, Themes and Skins were built to offer CSS-like capablities to ASP.Net server controls. Server controls don't always map directly to HTML controls, so something more than CSS is needed to set the properites for the controls, so they'll look the way you want when the page is renderedas HTML on the users browser.

If you're using VS 2005, you should be able to look at all the parts of the control that have properties that you can set. As color is so important to charts and graphs, I have to think that the colors of the bars will be accessable properties.

When you place the control on the page (in design view), does it have a little black arrowhead in the upper right? On a Calendar control (for example), clicking on that lets you select an Auto Format (one of a list of styles for that control). That's one way you might get what you want. If you look at the control in source view, and position the curosr just after the start tag for the control, and hit a space, VS's Intellisense will show a large list of properties. Dbl click one of theose and hit an equal sign, and you'll see a list of values that you can asign to that property. If your control is anything like the calendar control, you should be all set.

Good luck in any event. BRN..

|||

For the record, I have found the answer to this problem. It's detailed in this MSDN article: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/MoreSSRSCharts.asp.

There are ways you can set the color of the bars, but it's not at all intuitive, and doesn't seem to be documented too well (if at all) in the Books Online for SQL Server. But this is a great article for all kinds of chart issues.

|||

Hi,

Glad you got what you needed. Why don't you mark your own post as the answer - then others will see a solution, should they search the forums for an answer to a similar problem. BRN..

|||

I hate to resurrect an old post, but I could really use the information from that article. However, the link above is broken. Can anyone please point me to where the article is currently hiding. Thanks!

|||

Found it.http://msdn2.microsoft.com/en-us/library/aa964128.aspx

sql

Friday, March 30, 2012

Wednesday, March 28, 2012

Blog Archive

About Me