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 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 Query Type ?(Mangagement Studio)

Why the "Change Type" Button of the "Query Designer Toolbar " is alwayse Disable(gray color) ,then how can i switch the query between "selelct","update","Insert" and "Delete"? I am using Nov 7 released 180 Days Trial Version.(Sql Server Management Studio)

Thannks!

The Query toolbar is associated with Query Designer documents. The only way to display such a document in Management Studio is using the Open Table/Open View functionality for tables or views. The change type drop down is disabled in Open Table because we don't allow the Visual Studio Query Designer (which is displaying the query) to change the query type for Open Table. In Visual Studio, this restriction is lifted for Query Designer documents.

You can do what you want to do by creating a new query (click the New Query button), typing some text (say, "select"), highlighting the text you typed, right-clicking to bring up a context menu, and selecting "Design Query in Editor." In the Query Designer dialog that appears, you can right click in the upper pane and add tables. You can also right click and change the query type from the context menu.|||I see,Thank you for your Reply

How to change or alter a user defined data type?

Hi all,
I already read this: http://vyaskn.tripod.com/administration_faq.htm#q12
and it's ok for the table/columns but I can NOT drop my user defined data
type using sp_droptype because it's already used in some Stored procs :(
Is there a solution WITHOUT a "delete & re-create" of these stored procs ?
Lilian.This example should demonstrate how to swap out the type. Note that the
rename affects tables, but not stored procs (so you won't have to recompile
your procedures, just hope they don't get called during the brief period
where the type doesn't exist).
-- originally, we create a datatype called fax,
-- and made it able to accept 32 characters
EXEC sp_addType 'fax', 'VARCHAR(32)'
GO
-- so we created a table that uses this datatype
CREATE TABLE dbo.foobar0
(
[fax] fax
)
GO
-- and a simple stored procedure as well
CREATE PROC dbo.foobar1
@.fax fax
AS
BEGIN
DECLARE @.fax2 fax
END
GO
-- now, we realize that fax numbers on
-- jupiter can contain 64 characters, so we
-- have to increase the size of the UDT
-- but we can't just alter the type, and we
-- can't drop it and re-create it either, without
-- altering or dropping / re-creating tables,
-- stored procedures, etc. that use the UDT
-- but we can use a little rename trick to
-- create an interim UDT that meets our needs
-- first, rename the existing UDT. This will
-- change table definitions but it will not alter
-- the text for a procedure / function
EXEC sp_rename 'fax', 'oldfax', 'USERDATATYPE'
GO
-- let's just make sure that it affected our table
-- but not our procedure:
EXEC sp_help foobar0
EXEC sp_helptext foobar1
GO
-- okay, now let's add the larger fax UDT back
-- into the system
EXEC sp_addtype 'fax', 'VARCHAR(64)'
GO
-- alter any tables / views that reference the old
-- fax UDT
ALTER TABLE foobar0 ALTER COLUMN [fax] fax
GO
-- now we should be able to drop the interim
-- UDT:
EXEC sp_droptype 'oldfax'
GO
-- (now let's clean up my silly example)
DROP TABLE dbo.foobar0
DROP PROCEDURE dbo.foobar1
EXEC sp_droptype 'fax'
GO
"Lilian Pigallio" <lpigallio@.nospam.com> wrote in message
news:#pSUDo9PDHA.2036@.TK2MSFTNGP10.phx.gbl...
> Hi all,
> I already read this: http://vyaskn.tripod.com/administration_faq.htm#q12
> and it's ok for the table/columns but I can NOT drop my user defined data
> type using sp_droptype because it's already used in some Stored procs :(
> Is there a solution WITHOUT a "delete & re-create" of these stored procs
?
> Lilian.
>|||Sorry, you will have to recompile stored procedures that use the datatype as
in/out parameters, but not those that only use the type for local
variables...|||Very interesting...but do you "recompile" a stored proc by script ?
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:elXMq29PDHA.2676@.TK2MSFTNGP10.phx.gbl...
> Sorry, you will have to recompile stored procedures that use the datatype
as
> in/out parameters, but not those that only use the type for local
> variables...
>

How to change license type from Per Proc to CAL ?

We had a production server installed with a Per Processor license by mistake
and need to change it to 'per seat' ie CAL. Is there a way to do this without
reinstalling SQL on the server (as it is a production server now) ? The
'Choose Licensing Mode' in control panel has the 'per seat' option grayed out
and only allows to change the number of processor licenses.
Thanks.Hi
"Pranil" wrote:
> We had a production server installed with a Per Processor license by mistake
> and need to change it to 'per seat' ie CAL. Is there a way to do this without
> reinstalling SQL on the server (as it is a production server now) ? The
> 'Choose Licensing Mode' in control panel has the 'per seat' option grayed out
> and only allows to change the number of processor licenses.
> Thanks.
With a production server it is better to be safe as downtime could be
costly, but you could try http://tinyurl.com/25rvyo
John|||Awesome - thanks!
"John Bell" wrote:
> Hi
> "Pranil" wrote:
> > We had a production server installed with a Per Processor license by mistake
> > and need to change it to 'per seat' ie CAL. Is there a way to do this without
> > reinstalling SQL on the server (as it is a production server now) ? The
> > 'Choose Licensing Mode' in control panel has the 'per seat' option grayed out
> > and only allows to change the number of processor licenses.
> >
> > Thanks.
> With a production server it is better to be safe as downtime could be
> costly, but you could try http://tinyurl.com/25rvyo
> John

How to change license type from Per Proc to CAL ?

We had a production server installed with a Per Processor license by mistake
and need to change it to 'per seat' ie CAL. Is there a way to do this withou
t
reinstalling SQL on the server (as it is a production server now) ? The
'Choose Licensing Mode' in control panel has the 'per seat' option grayed ou
t
and only allows to change the number of processor licenses.
Thanks.Hi
"Pranil" wrote:

> We had a production server installed with a Per Processor license by mista
ke
> and need to change it to 'per seat' ie CAL. Is there a way to do this with
out
> reinstalling SQL on the server (as it is a production server now) ? The
> 'Choose Licensing Mode' in control panel has the 'per seat' option grayed
out
> and only allows to change the number of processor licenses.
> Thanks.
With a production server it is better to be safe as downtime could be
costly, but you could try http://tinyurl.com/25rvyo
John|||Awesome - thanks!
"John Bell" wrote:

> Hi
> "Pranil" wrote:
>
> With a production server it is better to be safe as downtime could be
> costly, but you could try http://tinyurl.com/25rvyo
> John

How to change license type from Per Proc to CAL ?

We had a production server installed with a Per Processor license by mistake
and need to change it to 'per seat' ie CAL. Is there a way to do this without
reinstalling SQL on the server (as it is a production server now) ? The
'Choose Licensing Mode' in control panel has the 'per seat' option grayed out
and only allows to change the number of processor licenses.
Thanks.
Hi
"Pranil" wrote:

> We had a production server installed with a Per Processor license by mistake
> and need to change it to 'per seat' ie CAL. Is there a way to do this without
> reinstalling SQL on the server (as it is a production server now) ? The
> 'Choose Licensing Mode' in control panel has the 'per seat' option grayed out
> and only allows to change the number of processor licenses.
> Thanks.
With a production server it is better to be safe as downtime could be
costly, but you could try http://tinyurl.com/25rvyo
John
|||Awesome - thanks!
"John Bell" wrote:

> Hi
> "Pranil" wrote:
>
> With a production server it is better to be safe as downtime could be
> costly, but you could try http://tinyurl.com/25rvyo
> John
sql

how to change language code of client executing the report dynamic?

Our customer want to use a different language type of his Regional option.
So the datetime type at the Parameter Bar of reporting service could
no show as stander code. (For example short date of his Regional is
2000.1.1 but he want to show as 1.1.2000 when he click the calendor
@.Parameter Bars and he refuse to change his regional options)He shouldn't have to change his regional params.
On the report just set the 'language' to '=User!Language' (this is available
in the Globals in the expression editor)
If you wish to add specific formatting on a cell or other item you can still
do this.
See the follow for this and other best practices:
scroll down to "93" to see my poin
http://www.ssw.com.au/ssw/Standards/Rules/RulesToBetterSQLReportingServices.aspx
"lieshengren" wrote:
> Our customer want to use a different language type of his Regional option.
> So the datetime type at the Parameter Bar of reporting service could
> no show as stander code. (For example short date of his Regional is
> 2000.1.1 but he want to show as 1.1.2000 when he click the calendor
> @.Parameter Bars and he refuse to change his regional options)
>
>
>

How to Change Graph Type in a .rpt File

Hi all,

I'm working with Crystal report and a java Swing application. The interaction between the two is made up by a simple wrapper that starts the .rpt file when a button is pressed, passing just a few parameters.

i'd like to know if it's possible (and how :D ) to pass the type of chart to be displayed to the .rpt file as a parameter, letting the java application user to choose the chart type inside the Swing environment.

thanks for the help

EnricoI think you need to create as many reports as the number of charts and show corresponding reports as what the user chooses

How to change font type for annotations?

Hi all of you,

I'd like to find where can I change the fonttype for annotations done in Control Flow. I mean, every time that I do that appears the one I chose by default

TIA

In a short, set by default the font for comments

|||

Tools -> Options... : Environment/Fonts and Colors/Business Intelligence Designers

Thanks,

Bob

sql

how to change default font for ALL reports

My company had decreed that all documents, emails, etc should use the Verdana
font type. Report Svcs default is Ariel. Does anyone know where I would go to
change the default fontfamily type to Verdana from Ariel so that i dont have
to go into every property of every item in every report to change it
manually?
I did look in the Tools-->options-->font area, but it didnt list out Reports
directly, although I may have missed something. I didnt see anything set to
Ariel in there at all.
THanks in advance!!!Hey Carl,
When you get into the "Tools > Fonts" section, you need to use the drop
down box at the top right of the window to select "Business Intellegence",
and then it will apply those to your BI development.
Michael C.
"Carl Henthorn" wrote:
> My company had decreed that all documents, emails, etc should use the Verdana
> font type. Report Svcs default is Ariel. Does anyone know where I would go to
> change the default fontfamily type to Verdana from Ariel so that i dont have
> to go into every property of every item in every report to change it
> manually?
> I did look in the Tools-->options-->font area, but it didnt list out Reports
> directly, although I may have missed something. I didnt see anything set to
> Ariel in there at all.
> THanks in advance!!!|||The label of the drop down is "Show Settings For:"
Micahel C
"Michael C" wrote:
> Hey Carl,
> When you get into the "Tools > Fonts" section, you need to use the drop
> down box at the top right of the window to select "Business Intellegence",
> and then it will apply those to your BI development.
> Michael C.
>
> "Carl Henthorn" wrote:
> > My company had decreed that all documents, emails, etc should use the Verdana
> > font type. Report Svcs default is Ariel. Does anyone know where I would go to
> > change the default fontfamily type to Verdana from Ariel so that i dont have
> > to go into every property of every item in every report to change it
> > manually?
> >
> > I did look in the Tools-->options-->font area, but it didnt list out Reports
> > directly, although I may have missed something. I didnt see anything set to
> > Ariel in there at all.
> >
> > THanks in advance!!!

How to change data type of column from int to bigint in replication

Hi,
Currently, we have a table in sql server 2000 which has one column as
int data-type and which is part of transactional replication we
replicate this table to oracle. The oracle version is 9.2. Now we want
to change the data type of this column to bigint what would be the
best way to alter this table so that we don't have to drop the
replication?
Thanks
If this is the pk you will have to drop the subscribers and publications. If
it is a non-key column you should try to use sp_repladdcolumn and
sp_repldropcolumn.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<kulkarni.ninad@.gmail.com> wrote in message
news:1171302064.597470.203130@.s48g2000cws.googlegr oups.com...
> Hi,
> Currently, we have a table in sql server 2000 which has one column as
> int data-type and which is part of transactional replication we
> replicate this table to oracle. The oracle version is 9.2. Now we want
> to change the data type of this column to bigint what would be the
> best way to alter this table so that we don't have to drop the
> replication?
> Thanks
>
|||Please take a look at http://www.replicationanswers.com/AddColumn.asp
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
sql

How to change a field's type?

Hi,
How could I change the field type through T-SQL?
I have tried Using the ALTER:
ALTER TABLE tblName ALTER COLUMN myID int
It didn't work...

And also, how could you rename a fieldname?
cheers,
Paul June A. DomagThe syntax you have listed is correct, although you may need to specify NULL/NOT NULL:
Alter Table dbo.SomeTable
Alter Column SomeField Varchar(101) NULL
For the rename funtion, use sp_rename you can find the syntax in books online...
Edit: If you are getting an error, please post the details of the error so we can dig a little deeper...
|||Hi,

I tried placing a NOT NULL statement and still it doesn't work...

ALTER TABLE myTable ALTER COLUMN myCol int NOT NULL

What is wrong with my statement?
BTW, the myCol field is numeric (I don't know if it matters)

ALSO, is there another alternative in renaming the field? Coz Im trying to achieve this in SQLServerCE and there is no sp_rename function in SQLServerCE...
cheers,
Paul June A. Domag|||Hey Paul,
I'm not familiar with SQL Server CE, so not sure if the T-SQL is any different.
In the absence of sp_rename, the following should work
-- Create the renamed column
Alter Table myTable with Check Add newColname varchar(100) not null
Go
-- copy the data to the new column
Update myTable set newColumn = oldColumn
Go
-- delete the old column
Alter Table myTable Drop Column oldColumn
Go
For the Alter statement that isn't working, can you please post the error message that you receive?
Thanks,
Tyler|||Hi,
Whew! Why didn't I think about that? tsk....
Thank you very much! You really made my day...

Here's the error message that I keep on getting in SQLServerCE:

FAILED: ALTER TABLE CustomerAddresses
ALTER COLUMN IDNew int
Error: 0x80040e14 DB_E_ERRORSINCOMMAND
Native Error: (25501)
Description: There was an error parsing the query. [Token line number,Token line offset,,Token in error,,]
Interface defining error: IID_ICommand
Param. 0: 2
Param. 1: 7
Param. 2: 0
Param. 3: IDNew
Param. 4:
Param. 5:

Cheers,
Paul June A. Domag|||Hi,
In addition to that, how could I modify a field to make it an IDENTITY column?
I tried:
ALTER TABLE mytable ALTER COLUMN IDNew IDENTITY

It generates an error saying that my field could not be made identity column after it was made...

BTW, the column that im trying to rename is an IDENTITY column, so by using the solution that you have provided, I need to restore the Autonumbering function on my field...
cheers,
Paul June A. Domag|||Hi Paul,
I think that Identity may be the issue. I suppose for an easy answer you could use the same method to change the column type, i.e., create a new column, copy the values and delete the orginal column.
To restore the numbering just do it when you declare the column:
Alter Table SomeTable
With Check Add
MyColumn INT IDENTITY (x, y) NOT NULL
Where x is the SEED value (i.e., what the next number will start at) and y is the INCREMENT (usually 1 to increment by one)
So if you where to declare the column as IDENTITY (1500, 1) the next record inserted would automatically have a value of 1500, etc.
Let me know if that makes sense.
Thanks!
Tyler|||Hi,
Sorry, but it didn't work. It seems that Identity columns couldn't be updated...
I used your steps:

ALTER TABLE t ADD myNewCol int IDENTITY(1,1)
UPDATE t SET myNewCol = ID ' Error Here
ALTER TABLE t DROP COLUMN ID

BTW, my ID field (w/c is the identity column) is not entirely sequential. What I mean is, there have been some deletions and the numbering is somewhat jumbled. eg 1,2,3,8,11,13,21,22,25...

In this case I can't even use the new Identity Column that I would create coz the newly created identity would be sequential and thus would break my relationship with other tables...

Any other bright ideas?
cheers,
Paul June A. Domag|||Hi Paul
I think you cannot update an Identity column unless you set the identity insert on
ALTER TABLE t ADD myNewCol int IDENTITY(1,1)
go
SET IDENTITY_INSERT t ON
UPDATE t SET myNewCol = ID ' Error Here
SET IDENTITY_INSERT t OFF
ALTER TABLE t DROP COLUMN ID
GO|||Hi,
Im currently using SQLServerCE and I doubt that this function/variable exists... Im trying to do this purely in SQL Statement...
Any Other Bright Ideas?
cheers,
Paul June A. Domag|||With a column set to Identity you cannot insert values unless your basically turn off the Identity feature, which is done as Eisa states.
If this doesn't work in SQL CE then we'll need to find a SQL CE Expert to answer this question.
Have you tried turning off the IDENTITY_INSERT? Also, make sure you insert GO statements after each line.
|||Hi,
Sad to say, it didn't work. I searched the BOL of SQLServerCE and the IDENTITY_INSERT switch isn't available. Guess, I'll have to find other means to solve this problem...

Thanks a lot Tyler Free and Eisa for your ideas and comments...
cheers,
Paul June A. Domag

How to capture changes in a table

Hi Freinds,
SQL 2000
I would like to capture all the changes ( updates. insert, delete) in my
tables. this is a type of audit and capturing the changes of data.
we can't do this in GUI section, so I need to develop smt to capture the
changes.
Does anybody did smt like this before? any help? hint?
Thank in advance,
Pathttp://www.aspfaq.com/2496
http://www.aspfaq.com/2448
"Patrick" <patriarck@.gmail.com> wrote in message
news:eK9IhW0VGHA.5468@.TK2MSFTNGP14.phx.gbl...
> Hi Freinds,
> SQL 2000
> I would like to capture all the changes ( updates. insert, delete) in my
> tables. this is a type of audit and capturing the changes of data.
> we can't do this in GUI section, so I need to develop smt to capture the
> changes.
> Does anybody did smt like this before? any help? hint?
> Thank in advance,
> Pat
>|||Based on what you are describing, you can try using a trigger, the option FO
R
INSERT, DELETE, UPDATE. Check Books Online for details. You can also capture
the username of anyone who performs an INSERT, DELETE or UPDATE. You may nee
d
to create another table to store all the changes captured by the trigger.
Please be aware that depending on how often this table is being accessed,
you may experience a impact in performance, since every operation will fire
the trigger.
"Patrick" wrote:

> Hi Freinds,
> SQL 2000
> I would like to capture all the changes ( updates. insert, delete) in my
> tables. this is a type of audit and capturing the changes of data.
> we can't do this in GUI section, so I need to develop smt to capture the
> changes.
> Does anybody did smt like this before? any help? hint?
> Thank in advance,
> Pat
>
>|||This is exacly what I want to do.
I am trying to develope a function or sp to use it in my trigger for same
exact purpose.
does anybody have done this before?
Thanks again,
Pat
"Edgardo Valdez, MCSD, MCDBA"
<EdgardoValdezMCSDMCDBA@.discussions.microsoft.com> wrote in message
news:A4AC389F-D7BA-4D6A-BEFB-769F2131A625@.microsoft.com...
> Based on what you are describing, you can try using a trigger, the option
> FOR
> INSERT, DELETE, UPDATE. Check Books Online for details. You can also
> capture
> the username of anyone who performs an INSERT, DELETE or UPDATE. You may
> need
> to create another table to store all the changes captured by the trigger.
> Please be aware that depending on how often this table is being accessed,
> you may experience a impact in performance, since every operation will
> fire
> the trigger.
> "Patrick" wrote:
>|||> I am trying to develope a function or sp to use it in my trigger for same
> exact purpose.
> does anybody have done this before?
YES! Did you see the links I posted?|||there were no links in your message !!
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OrD0wz0VGHA.4740@.TK2MSFTNGP14.phx.gbl...
> YES! Did you see the links I posted?
>|||Yes, there were !!
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ud7qqb0VGHA.4336@.TK2MSFTNGP14.phx.gbl...
> http://www.aspfaq.com/2496
> http://www.aspfaq.com/2448
"Patrick" <patriarck@.gmail.com> wrote in message
news:Omyf$70VGHA.4424@.TK2MSFTNGP15.phx.gbl...
> there were no links in your message !!|||ok , I got it and thanks a lot :)
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23WiTIA1VGHA.5664@.TK2MSFTNGP15.phx.gbl...
> Yes, there were !!
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:ud7qqb0VGHA.4336@.TK2MSFTNGP14.phx.gbl...
>
>
> "Patrick" <patriarck@.gmail.com> wrote in message
> news:Omyf$70VGHA.4424@.TK2MSFTNGP15.phx.gbl...
>

How to calculate record size

Hi,
Can anyone help me to calculate record size for a table.
1. Lets say I have a table with 10 fields, each of them is type int. Now,
will record size be diferent if I save in each field value 0 versus if I
save value NULL?
2. What if table has all column as NOT NULL? Will then SQL Server still
create NULL bitmap for record:
Null Bitmap (Null_Bitmap) = 2 + (( Num_Cols + 7) / 8 )
3. BOL has different size calculations for fixed-length columns and variable
length column. Is NUMERIC(19,2) NOT NULL field considered to be fixed length
or variable length? Which data types are variable length? I do know that
varchar, varbinary, nvarchar is variable. But are there any other?
Thank you for helpI should add that I am using SQL SErver 2000.
Thanks
"NoSpam" <NoSpam@.NoSpam.com> wrote in message
news:uvsHEJMzEHA.2040@.tk2msftngp13.phx.gbl...
> Hi,
> Can anyone help me to calculate record size for a table.
> 1. Lets say I have a table with 10 fields, each of them is type int. Now,
> will record size be diferent if I save in each field value 0 versus if I
> save value NULL?
> 2. What if table has all column as NOT NULL? Will then SQL Server still
> create NULL bitmap for record:
> Null Bitmap (Null_Bitmap) = 2 + (( Num_Cols + 7) / 8 )
> 3. BOL has different size calculations for fixed-length columns and
> variable length column. Is NUMERIC(19,2) NOT NULL field considered to be
> fixed length or variable length? Which data types are variable length? I
> do know that varchar, varbinary, nvarchar is variable. But are there any
> other?
>
> Thank you for help
>
>|||Kalen Delaney's "Inside SQL server 2000" will give you the full story on
this but in summary:
1. NO, Size will always be the same.
2. If I remember right if there are NO nullable columns then the null bitmap
is not stored in the row.
3. Add text (and ntext) as effectively variable length. How much is stored
in the row is dependant on whether the text is to be stored within the row,
or a pointer to seperate text pages, and hence could vary from 16 bytes
upwards. Your specific example of numeric (19,2) is fixed length. BOL has
defined physical sizes for all the different data types.
Mike John
"NoSpam" <NoSpam@.NoSpam.com> wrote in message
news:e5fliKMzEHA.3184@.TK2MSFTNGP10.phx.gbl...
>I should add that I am using SQL SErver 2000.
> Thanks
> "NoSpam" <NoSpam@.NoSpam.com> wrote in message
> news:uvsHEJMzEHA.2040@.tk2msftngp13.phx.gbl...
>> Hi,
>> Can anyone help me to calculate record size for a table.
>> 1. Lets say I have a table with 10 fields, each of them is type int. Now,
>> will record size be diferent if I save in each field value 0 versus if I
>> save value NULL?
>> 2. What if table has all column as NOT NULL? Will then SQL Server still
>> create NULL bitmap for record:
>> Null Bitmap (Null_Bitmap) = 2 + (( Num_Cols + 7) / 8 )
>> 3. BOL has different size calculations for fixed-length columns and
>> variable length column. Is NUMERIC(19,2) NOT NULL field considered to be
>> fixed length or variable length? Which data types are variable length? I
>> do know that varchar, varbinary, nvarchar is variable. But are there any
>> other?
>>
>> Thank you for help
>>
>|||Mike,
Thanks for the information.
"Mike John" <Mike.John@.knowledgepool.com> wrote in message
news:e2BQoSPzEHA.1564@.TK2MSFTNGP09.phx.gbl...
> Kalen Delaney's "Inside SQL server 2000" will give you the full story on
> this but in summary:
> 1. NO, Size will always be the same.
> 2. If I remember right if there are NO nullable columns then the null
> bitmap is not stored in the row.
> 3. Add text (and ntext) as effectively variable length. How much is stored
> in the row is dependant on whether the text is to be stored within the
> row, or a pointer to seperate text pages, and hence could vary from 16
> bytes upwards. Your specific example of numeric (19,2) is fixed length.
> BOL has defined physical sizes for all the different data types.
> Mike John
> "NoSpam" <NoSpam@.NoSpam.com> wrote in message
> news:e5fliKMzEHA.3184@.TK2MSFTNGP10.phx.gbl...
>>I should add that I am using SQL SErver 2000.
>> Thanks
>> "NoSpam" <NoSpam@.NoSpam.com> wrote in message
>> news:uvsHEJMzEHA.2040@.tk2msftngp13.phx.gbl...
>> Hi,
>> Can anyone help me to calculate record size for a table.
>> 1. Lets say I have a table with 10 fields, each of them is type int.
>> Now, will record size be diferent if I save in each field value 0 versus
>> if I save value NULL?
>> 2. What if table has all column as NOT NULL? Will then SQL Server still
>> create NULL bitmap for record:
>> Null Bitmap (Null_Bitmap) = 2 + (( Num_Cols + 7) / 8 )
>> 3. BOL has different size calculations for fixed-length columns and
>> variable length column. Is NUMERIC(19,2) NOT NULL field considered to be
>> fixed length or variable length? Which data types are variable length? I
>> do know that varchar, varbinary, nvarchar is variable. But are there any
>> other?
>>
>> Thank you for help
>>
>>
>

How to calculate record size

Hi,
Can anyone help me to calculate record size for a table.
1. Lets say I have a table with 10 fields, each of them is type int. Now,
will record size be diferent if I save in each field value 0 versus if I
save value NULL?
2. What if table has all column as NOT NULL? Will then SQL Server still
create NULL bitmap for record:
Null Bitmap (Null_Bitmap) = 2 + (( Num_Cols + 7) / 8 )
3. BOL has different size calculations for fixed-length columns and variable
length column. Is NUMERIC(19,2) NOT NULL field considered to be fixed length
or variable length? Which data types are variable length? I do know that
varchar, varbinary, nvarchar is variable. But are there any other?
Thank you for helpI should add that I am using SQL SErver 2000.
Thanks
"NoSpam" <NoSpam@.NoSpam.com> wrote in message
news:uvsHEJMzEHA.2040@.tk2msftngp13.phx.gbl...
> Hi,
> Can anyone help me to calculate record size for a table.
> 1. Lets say I have a table with 10 fields, each of them is type int. Now,
> will record size be diferent if I save in each field value 0 versus if I
> save value NULL?
> 2. What if table has all column as NOT NULL? Will then SQL Server still
> create NULL bitmap for record:
> Null Bitmap (Null_Bitmap) = 2 + (( Num_Cols + 7) / 8 )
> 3. BOL has different size calculations for fixed-length columns and
> variable length column. Is NUMERIC(19,2) NOT NULL field considered to be
> fixed length or variable length? Which data types are variable length? I
> do know that varchar, varbinary, nvarchar is variable. But are there any
> other?
>
> Thank you for help
>
>|||Kalen Delaney's "Inside SQL server 2000" will give you the full story on
this but in summary:
1. NO, Size will always be the same.
2. If I remember right if there are NO nullable columns then the null bitmap
is not stored in the row.
3. Add text (and ntext) as effectively variable length. How much is stored
in the row is dependant on whether the text is to be stored within the row,
or a pointer to seperate text pages, and hence could vary from 16 bytes
upwards. Your specific example of numeric (19,2) is fixed length. BOL has
defined physical sizes for all the different data types.
Mike John
"NoSpam" <NoSpam@.NoSpam.com> wrote in message
news:e5fliKMzEHA.3184@.TK2MSFTNGP10.phx.gbl...
>I should add that I am using SQL SErver 2000.
> Thanks
> "NoSpam" <NoSpam@.NoSpam.com> wrote in message
> news:uvsHEJMzEHA.2040@.tk2msftngp13.phx.gbl...
>|||Mike,
Thanks for the information.
"Mike John" <Mike.John@.knowledgepool.com> wrote in message
news:e2BQoSPzEHA.1564@.TK2MSFTNGP09.phx.gbl...
> Kalen Delaney's "Inside SQL server 2000" will give you the full story on
> this but in summary:
> 1. NO, Size will always be the same.
> 2. If I remember right if there are NO nullable columns then the null
> bitmap is not stored in the row.
> 3. Add text (and ntext) as effectively variable length. How much is stored
> in the row is dependant on whether the text is to be stored within the
> row, or a pointer to seperate text pages, and hence could vary from 16
> bytes upwards. Your specific example of numeric (19,2) is fixed length.
> BOL has defined physical sizes for all the different data types.
> Mike John
> "NoSpam" <NoSpam@.NoSpam.com> wrote in message
> news:e5fliKMzEHA.3184@.TK2MSFTNGP10.phx.gbl...
>

How to calculate record size

Hi,
Can anyone help me to calculate record size for a table.
1. Lets say I have a table with 10 fields, each of them is type int. Now,
will record size be diferent if I save in each field value 0 versus if I
save value NULL?
2. What if table has all column as NOT NULL? Will then SQL Server still
create NULL bitmap for record:
Null Bitmap (Null_Bitmap) = 2 + (( Num_Cols + 7) / 8 )
3. BOL has different size calculations for fixed-length columns and variable
length column. Is NUMERIC(19,2) NOT NULL field considered to be fixed length
or variable length? Which data types are variable length? I do know that
varchar, varbinary, nvarchar is variable. But are there any other?
Thank you for help
I should add that I am using SQL SErver 2000.
Thanks
"NoSpam" <NoSpam@.NoSpam.com> wrote in message
news:uvsHEJMzEHA.2040@.tk2msftngp13.phx.gbl...
> Hi,
> Can anyone help me to calculate record size for a table.
> 1. Lets say I have a table with 10 fields, each of them is type int. Now,
> will record size be diferent if I save in each field value 0 versus if I
> save value NULL?
> 2. What if table has all column as NOT NULL? Will then SQL Server still
> create NULL bitmap for record:
> Null Bitmap (Null_Bitmap) = 2 + (( Num_Cols + 7) / 8 )
> 3. BOL has different size calculations for fixed-length columns and
> variable length column. Is NUMERIC(19,2) NOT NULL field considered to be
> fixed length or variable length? Which data types are variable length? I
> do know that varchar, varbinary, nvarchar is variable. But are there any
> other?
>
> Thank you for help
>
>
|||Kalen Delaney's "Inside SQL server 2000" will give you the full story on
this but in summary:
1. NO, Size will always be the same.
2. If I remember right if there are NO nullable columns then the null bitmap
is not stored in the row.
3. Add text (and ntext) as effectively variable length. How much is stored
in the row is dependant on whether the text is to be stored within the row,
or a pointer to seperate text pages, and hence could vary from 16 bytes
upwards. Your specific example of numeric (19,2) is fixed length. BOL has
defined physical sizes for all the different data types.
Mike John
"NoSpam" <NoSpam@.NoSpam.com> wrote in message
news:e5fliKMzEHA.3184@.TK2MSFTNGP10.phx.gbl...
>I should add that I am using SQL SErver 2000.
> Thanks
> "NoSpam" <NoSpam@.NoSpam.com> wrote in message
> news:uvsHEJMzEHA.2040@.tk2msftngp13.phx.gbl...
>
|||Mike,
Thanks for the information.
"Mike John" <Mike.John@.knowledgepool.com> wrote in message
news:e2BQoSPzEHA.1564@.TK2MSFTNGP09.phx.gbl...
> Kalen Delaney's "Inside SQL server 2000" will give you the full story on
> this but in summary:
> 1. NO, Size will always be the same.
> 2. If I remember right if there are NO nullable columns then the null
> bitmap is not stored in the row.
> 3. Add text (and ntext) as effectively variable length. How much is stored
> in the row is dependant on whether the text is to be stored within the
> row, or a pointer to seperate text pages, and hence could vary from 16
> bytes upwards. Your specific example of numeric (19,2) is fixed length.
> BOL has defined physical sizes for all the different data types.
> Mike John
> "NoSpam" <NoSpam@.NoSpam.com> wrote in message
> news:e5fliKMzEHA.3184@.TK2MSFTNGP10.phx.gbl...
>

How to build an Analysis Services project

I have have created a analysis services project on my development machine, but I now want to be able to build this type of project on my build machine as part of my automated build process. I have installed the Business Intelligence Development Studio, but Visual Studio does not recognize the project type. What else do I need to install?

Thanks,

Anthony

Hi,

This command line does the build for me:

devenv "Analysis Services Project1.sln" /build Development

Having BI Development Studio installed should be enough, also please make sure that you run the 'devenv.exe' from '%ProgramFiles%\Microsoft Visual Studio 8\Common7\IDE' (in case you have multiple installed).

Adrian Dumitrascu

|||

Yes I am running devenv from the correct location. I have also tried to load the project into Visual Studio and all it does it open it like a text file.

Anthony

|||

In VS -> Help -> About, do you have an entry for 'SQL Server Analysis Services' ?

It seems that the BI Development Studio is not properly installed (or it was broken by something).

Adrian

|||My build problem was do to a bad path. My scripts were still looking at VS2002 instead of VS2005.

I have corrected my script, but I now have a new problem. When running the devenv command I am getting the following error.
Catastrophic failure (Exception from HRESULT: 0x8000FFFF (E_UNEXPECTED))

If I open the project using the same command line, but I exclude the /build option and open it in the IDE, the project builds fine.
Any ideas as to why this project would not build from the command line?

Anthony