On my report I want to place two matrix data regions, whereas the second
matrix data region is placed exactly below the first matrix data region -
without spacing.
The first matrix shows the data with the numbers for the year 2006 and the
second matrix shows exactly the same data but with the numbers for the year
2005 ! So I donâ't want to show the column header for the second matrix data
region again. Therefore I set the visibility of the column header textboxes
to hidden and the column header is not shown as expected.
But a spacing remains between the two matrix data regions and I donâ't know
how to to eliminate it. The spacing has the size (height) of the hidden
column header text boxes. Also rendering the report in Excel doesnâ't give the
correct result. I also want to avoid overlapping report items because this
will probably produce rendering problems too.
Who can help to get rid of the remaing spacing between the two matrix data
regions !?
Any hints are welcome !Try selecting the whole row of your column header, and set the Height (not
LineHeight) to 0.01cm (or whatever your measurements are.)
Also, make the background colour for the row the same as the cells under it,
making it look like the top cells are slightly bigger than the rest.
Kaisa M. Lindahl Lervik
"Timmo" <Timmo@.discussions.microsoft.com> wrote in message
news:A1D86284-DBE1-4BE8-9881-CED568E6D938@.microsoft.com...
> On my report I want to place two matrix data regions, whereas the second
> matrix data region is placed exactly below the first matrix data region -
> without spacing.
> The first matrix shows the data with the numbers for the year 2006 and the
> second matrix shows exactly the same data but with the numbers for the
> year
> 2005 ! So I don't want to show the column header for the second matrix
> data
> region again. Therefore I set the visibility of the column header
> textboxes
> to hidden and the column header is not shown as expected.
> But a spacing remains between the two matrix data regions and I don't know
> how to to eliminate it. The spacing has the size (height) of the hidden
> column header text boxes. Also rendering the report in Excel doesn't give
> the
> correct result. I also want to avoid overlapping report items because this
> will probably produce rendering problems too.
> Who can help to get rid of the remaing spacing between the two matrix data
> regions !?
> Any hints are welcome !
>|||Try selecting the whole row of your column header, and set the Height (not
LineHeight) to 0.01cm (or whatever your measurements are.)
Also, make the background colour for the row the same as the cells under it,
making it look like the top cells are slightly bigger than the rest.
Kaisa M. Lindahl Lervik
"Timmo" <Timmo@.discussions.microsoft.com> wrote in message
news:A1D86284-DBE1-4BE8-9881-CED568E6D938@.microsoft.com...
> On my report I want to place two matrix data regions, whereas the second
> matrix data region is placed exactly below the first matrix data region -
> without spacing.
> The first matrix shows the data with the numbers for the year 2006 and the
> second matrix shows exactly the same data but with the numbers for the
> year
> 2005 ! So I don't want to show the column header for the second matrix
> data
> region again. Therefore I set the visibility of the column header
> textboxes
> to hidden and the column header is not shown as expected.
> But a spacing remains between the two matrix data regions and I don't know
> how to to eliminate it. The spacing has the size (height) of the hidden
> column header text boxes. Also rendering the report in Excel doesn't give
> the
> correct result. I also want to avoid overlapping report items because this
> will probably produce rendering problems too.
> Who can help to get rid of the remaing spacing between the two matrix data
> regions !?
> Any hints are welcome !
>|||Hi Kaisa !
Thank you very much for your answer ! Unfortunately your hint didn't help. I
already tried to set the height of the row with the column headers to 0 cm
and also setting the height to 0.01 cm couldn't help because the system sets
the height automatically to 0.07937cm which seems to be some minimal height !?
Nevertheless, if another idea comes to your mind, I would appriciate it a lot.
Regards
Timmo
"Kaisa M. Lindahl Lervik" wrote:
> Try selecting the whole row of your column header, and set the Height (not
> LineHeight) to 0.01cm (or whatever your measurements are.)
> Also, make the background colour for the row the same as the cells under it,
> making it look like the top cells are slightly bigger than the rest.
> Kaisa M. Lindahl Lervik
> "Timmo" <Timmo@.discussions.microsoft.com> wrote in message
> news:A1D86284-DBE1-4BE8-9881-CED568E6D938@.microsoft.com...
> > On my report I want to place two matrix data regions, whereas the second
> > matrix data region is placed exactly below the first matrix data region -
> > without spacing.
> > The first matrix shows the data with the numbers for the year 2006 and the
> > second matrix shows exactly the same data but with the numbers for the
> > year
> > 2005 ! So I don't want to show the column header for the second matrix
> > data
> > region again. Therefore I set the visibility of the column header
> > textboxes
> > to hidden and the column header is not shown as expected.
> >
> > But a spacing remains between the two matrix data regions and I don't know
> > how to to eliminate it. The spacing has the size (height) of the hidden
> > column header text boxes. Also rendering the report in Excel doesn't give
> > the
> > correct result. I also want to avoid overlapping report items because this
> > will probably produce rendering problems too.
> >
> > Who can help to get rid of the remaing spacing between the two matrix data
> > regions !?
> > Any hints are welcome !
> >
>
>
Showing posts with label placing. Show all posts
Showing posts with label placing. Show all posts
Tuesday, March 20, 2012
placing table on the memory
Hello there
I have reference localAreaCode table with 3000 records , i need to check on
existing phone table that the area code exist on LocalAreaCode table.
So far the query worked very slow.
Is there a way to place the LocalAreaCode table at the memory, to inprove
perfomance?Hi Roy,
In SQL Server 2000 you can use:
1) DBCC PINTABLE
2) sp_tableoption
See Books Online for more info about this.
I'm pretty sure that DBCC PINTABLE does nothing in SQL Server 2005 and
for sp_tableoption the choice to pin a table in memory is no longer
there. The rationale is that using these options can cause bad things
to happen. For example, in SQL Server 2005 Books Online we have the
following in the DBCC PINTABLE entry:
==============================
This functionality was introduced for performance in SQL Server version
6.5. DBCC PINTABLE has highly unwanted side-effects. These include the
potential to damage the buffer pool. DBCC PINTABLE is not required and
has been removed to prevent additional problems. The syntax for this
command still works but does not affect the server.
==============================
Sorry I can't be of more help|||Maybe we should first take look at the query. Please post it.
Also check the execution plan to see whether indexes are used properly. The
table is indexed, isn't it?
ML
http://milambda.blogspot.com/|||You could do this in 2000, using sp_tableoption. This was misused, though so
I believe that removed
it in 2005. If the table is accessed frequently enough, it will be cache in
memory, especially with
only 3000 rows. Still, you want to make sure that queries against it are eff
icient (create
supporting indexes etc).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Roy Goldhammer" <roy@.hotmail.com> wrote in message news:uetz2fwfGHA.4276@.TK2MSFTNGP03.phx.
gbl...
> Hello there
> I have reference localAreaCode table with 3000 records , i need to check o
n existing phone table
> that the area code exist on LocalAreaCode table.
> So far the query worked very slow.
> Is there a way to place the LocalAreaCode table at the memory, to inprove
perfomance?
>|||With only 3000 rows this should never be slow, so I suspect problems
with the table design or the query. You need to post the table
design, all keys and indexes, and the query that is slow.
Roy Harvey
Beacon Falls, CT
On Wed, 24 May 2006 10:58:04 +0300, "Roy Goldhammer" <roy@.hotmail.com>
wrote:
>Hello there
>I have reference localAreaCode table with 3000 records , i need to check on
>existing phone table that the area code exist on LocalAreaCode table.
>So far the query worked very slow.
>Is there a way to place the LocalAreaCode table at the memory, to inprove
>perfomance?
>
I have reference localAreaCode table with 3000 records , i need to check on
existing phone table that the area code exist on LocalAreaCode table.
So far the query worked very slow.
Is there a way to place the LocalAreaCode table at the memory, to inprove
perfomance?Hi Roy,
In SQL Server 2000 you can use:
1) DBCC PINTABLE
2) sp_tableoption
See Books Online for more info about this.
I'm pretty sure that DBCC PINTABLE does nothing in SQL Server 2005 and
for sp_tableoption the choice to pin a table in memory is no longer
there. The rationale is that using these options can cause bad things
to happen. For example, in SQL Server 2005 Books Online we have the
following in the DBCC PINTABLE entry:
==============================
This functionality was introduced for performance in SQL Server version
6.5. DBCC PINTABLE has highly unwanted side-effects. These include the
potential to damage the buffer pool. DBCC PINTABLE is not required and
has been removed to prevent additional problems. The syntax for this
command still works but does not affect the server.
==============================
Sorry I can't be of more help|||Maybe we should first take look at the query. Please post it.
Also check the execution plan to see whether indexes are used properly. The
table is indexed, isn't it?
ML
http://milambda.blogspot.com/|||You could do this in 2000, using sp_tableoption. This was misused, though so
I believe that removed
it in 2005. If the table is accessed frequently enough, it will be cache in
memory, especially with
only 3000 rows. Still, you want to make sure that queries against it are eff
icient (create
supporting indexes etc).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Roy Goldhammer" <roy@.hotmail.com> wrote in message news:uetz2fwfGHA.4276@.TK2MSFTNGP03.phx.
gbl...
> Hello there
> I have reference localAreaCode table with 3000 records , i need to check o
n existing phone table
> that the area code exist on LocalAreaCode table.
> So far the query worked very slow.
> Is there a way to place the LocalAreaCode table at the memory, to inprove
perfomance?
>|||With only 3000 rows this should never be slow, so I suspect problems
with the table design or the query. You need to post the table
design, all keys and indexes, and the query that is slow.
Roy Harvey
Beacon Falls, CT
On Wed, 24 May 2006 10:58:04 +0300, "Roy Goldhammer" <roy@.hotmail.com>
wrote:
>Hello there
>I have reference localAreaCode table with 3000 records , i need to check on
>existing phone table that the area code exist on LocalAreaCode table.
>So far the query worked very slow.
>Is there a way to place the LocalAreaCode table at the memory, to inprove
>perfomance?
>
placing system table filegroup and Log files on same drive
Is it better to create a seperate filegroup for all user tables ? And if so
should all the system tables that belong to the Primary filegroup reside on
the same drive with the log file . I am using SQL 2000. Which system tables
get hit the most daily for any inserts,updates and deletes that may be
occuring on the server
ThanksDepends on the disk configuration.
I believe it is generally recommended to separate the log file from any data
files, i.e., separate physical disks... however, if you have a multiple
channel controller, with high thoroughput on each channel, you may be able
to get by w/ having the log and the data on the same logical disk.
I believe that all system tables must reside in PRIMARY, I'd have to verify.
If that is true, it doesn't matter what system tables get inserts, updates,
deletes. However, you are taking away some fault tolerance if you put the
system tables and the log files on the same physical disk.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23AKD5sEXDHA.1872@.TK2MSFTNGP12.phx.gbl...
> Is it better to create a seperate filegroup for all user tables ? And if
so
> should all the system tables that belong to the Primary filegroup reside
on
> the same drive with the log file . I am using SQL 2000. Which system
tables
> get hit the most daily for any inserts,updates and deletes that may be
> occuring on the server
> Thanks
>|||You also generally want log files on RAID 1 or 10 as they are continuously
sequentially writing.
Thanks,
Greg H
"Martin Schmid" <martinschmid@.sbcglobal.net.nospam> wrote in message
news:eoDKm2EXDHA.2212@.TK2MSFTNGP09.phx.gbl...
> Depends on the disk configuration.
> I believe it is generally recommended to separate the log file from any
data
> files, i.e., separate physical disks... however, if you have a multiple
> channel controller, with high thoroughput on each channel, you may be able
> to get by w/ having the log and the data on the same logical disk.
> I believe that all system tables must reside in PRIMARY, I'd have to
verify.
> If that is true, it doesn't matter what system tables get inserts,
updates,
> deletes. However, you are taking away some fault tolerance if you put the
> system tables and the log files on the same physical disk.
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:%23AKD5sEXDHA.1872@.TK2MSFTNGP12.phx.gbl...
> > Is it better to create a seperate filegroup for all user tables ? And if
> so
> > should all the system tables that belong to the Primary filegroup reside
> on
> > the same drive with the log file . I am using SQL 2000. Which system
> tables
> > get hit the most daily for any inserts,updates and deletes that may be
> > occuring on the server
> >
> > Thanks
> >
> >
>|||Hassan
If you are using SQL Server 7, it is a good idea to put
your system files in a seperate filegroup. If you get a
corrupted user table, if your system tables are in a
seperate filegroup you can still perform a transaction log
backup prior to invoking DR (If you have DR of course). If
they are in the same filegroup you may not be able to.
This is no longer an issue in 2000.
Hope this helps
John
should all the system tables that belong to the Primary filegroup reside on
the same drive with the log file . I am using SQL 2000. Which system tables
get hit the most daily for any inserts,updates and deletes that may be
occuring on the server
ThanksDepends on the disk configuration.
I believe it is generally recommended to separate the log file from any data
files, i.e., separate physical disks... however, if you have a multiple
channel controller, with high thoroughput on each channel, you may be able
to get by w/ having the log and the data on the same logical disk.
I believe that all system tables must reside in PRIMARY, I'd have to verify.
If that is true, it doesn't matter what system tables get inserts, updates,
deletes. However, you are taking away some fault tolerance if you put the
system tables and the log files on the same physical disk.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23AKD5sEXDHA.1872@.TK2MSFTNGP12.phx.gbl...
> Is it better to create a seperate filegroup for all user tables ? And if
so
> should all the system tables that belong to the Primary filegroup reside
on
> the same drive with the log file . I am using SQL 2000. Which system
tables
> get hit the most daily for any inserts,updates and deletes that may be
> occuring on the server
> Thanks
>|||You also generally want log files on RAID 1 or 10 as they are continuously
sequentially writing.
Thanks,
Greg H
"Martin Schmid" <martinschmid@.sbcglobal.net.nospam> wrote in message
news:eoDKm2EXDHA.2212@.TK2MSFTNGP09.phx.gbl...
> Depends on the disk configuration.
> I believe it is generally recommended to separate the log file from any
data
> files, i.e., separate physical disks... however, if you have a multiple
> channel controller, with high thoroughput on each channel, you may be able
> to get by w/ having the log and the data on the same logical disk.
> I believe that all system tables must reside in PRIMARY, I'd have to
verify.
> If that is true, it doesn't matter what system tables get inserts,
updates,
> deletes. However, you are taking away some fault tolerance if you put the
> system tables and the log files on the same physical disk.
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:%23AKD5sEXDHA.1872@.TK2MSFTNGP12.phx.gbl...
> > Is it better to create a seperate filegroup for all user tables ? And if
> so
> > should all the system tables that belong to the Primary filegroup reside
> on
> > the same drive with the log file . I am using SQL 2000. Which system
> tables
> > get hit the most daily for any inserts,updates and deletes that may be
> > occuring on the server
> >
> > Thanks
> >
> >
>|||Hassan
If you are using SQL Server 7, it is a good idea to put
your system files in a seperate filegroup. If you get a
corrupted user table, if your system tables are in a
seperate filegroup you can still perform a transaction log
backup prior to invoking DR (If you have DR of course). If
they are in the same filegroup you may not be able to.
This is no longer an issue in 2000.
Hope this helps
John
Placing Sub Total Coulmn to the left of the coulumn in Matrix report
Hi,
If I am taking a Matrix and right clicking on the column header and click on the SubTotal then it always place that column on the right of it .If I want to place that column to the left of my original column then I can't do it.
Adding manual column and then puuting the Expresstion =Sum(Fields!MyCol.Value) is not halping as it will give me the same value that is there in the column instead of giving me the column
-Thanks,
Digs
Putting the same expression in the Row Group Header would give you some sort of a SubTotal to the left of your Data Column. However, this will obviously only give you only the sum and will not follow the same formatting & properties etc. as that in the Data Column.
HTH.
-Aayush
placing ssis packages on server
I have created my packages and i want them to place them on the server.Do i need to place the entire project of dts packages on the server or is there any option to place executables...if so please explain....
And to run these packages on the server do i need to set them as new job at sql server agent or is there any other way i need to run on the server.
I want then to run whenever the text file gets updated is it possible to set anything for my packages to run as and when the text file gets updated..
Please help me with all my questions
Thanks in advance..
If you you are using file based packages (.dtsx); you just need to move the files to a server where Integration services service is running. Then, yes, you can use SQL Server agent to schedule its execution. You could include some conditional logic in your package to check is the text file has been updated and then run the package in a periodically basis.|||what or how can i check to see if file is updated...is there any task...Please help me with this..
and all my files are .dtsx files So i need to move all these files.Should the server have business intelligence studio installed or can i just take these dtsx packages and execute it through sql server agent..
Thanks
|||ok, one thing at the time. BIDS is a development tool; it is not required in the server in order to execute the packages. The server needs to have a SSIS instance up and running; the you can use dtexec (package execution utility) to run the package via command line.
Additionally, if you want to schedule a package; you need to install the DB engine and SQL Server agent components on the server.
Regarding, how to include some logic inside of the package to check if the file has been 'updated'; I know there have been similar discussions n this forum about that; here you have a couple:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1030485&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=502717&SiteID=1
|||>>The server needs to have a SSIS instance up and running; the you can use dtexec (package execution utility) to run the package via >>command line.
Could you please tell me what ssis instance mean and about package execution utility.Sorry for very basic questions.I am not experienced with this topic.
i want my package to run automatically so i want to schedule.please let me know how to install DB engine and SQL Server agent components on the server.
Thanks
|||Run the SQL Server setup program on the server. Choose Integration Services to get SSIS installed; choose Database Engine and Agent to get the other pieces.
Books Online has excellent help for setting up the components of SQL Server, and there are a number of walkthroughs available online if you search for "Setup SQL Server 2005".
Placing sp_executesql data into a var
I have a stored procedure that takes various parameters and performs simple selects using these. A quick summary of the db structure this is working on would be :
lookup_table1
table1_id
description
lookup_table2
table2_id
description
This stored procedure attempts to select the given description from the given table and return the id, if the id isnt present it then inserts the new value and returns the new id.
Now I have this working, ish, but the problem that I have is that in the case of a new insert the procedure returns two datasets, an empty one (as the initial select returned no results) and another with the id of the newly inserted value.
This procedure as it stands right now
CREATE PROCEDURE aida_lookup
@.lookup_table varchar(255),
@.lookup_id_name varchar(255),
@.lookup_description_col varchar(255),
@.lookup_value varchar(255)
AS
declare @.sql nvarchar(2048)
--
-- Check if the given lookup value exists in the give table / column
--
set @.sql ='SELECT ' + @.lookup_id_name + ' FROM ' + @.lookup_table + ' WHERE ' + @.lookup_description_col + ' = ''' + @.lookup_value + ''''
EXECUTE sp_executesql @.sql
if (@.@.rowcount = 0) goto new_value
if (@.@.error <> 0) goto on_error
return(1)
--
-- Insert new lookup value into the given table / column
--
new_value:
-- NEED TO CLEAR PREVIOUS NULL SELECT
begin transaction
set @.sql = 'INSERT INTO ' + @.lookup_table + ' (' + @.lookup_description_col + ') VALUES (''' + @.lookup_value + '''); SELECT SCOPE_IDENTITY()'
execute sp_executesql @.sql
if (@.@.error <> 0) goto on_error
commit transaction
return(1)
--
-- Error handler
--
on_error:
rollback transaction
return (0)
GO
The problem being is that if the value is inserted as a new value I need to remove the empty dataset so that regardless of how the procedure is run, it will always return the id at position row 0 column 0.
My attempted solution was to declare a @.temp var and place the return value of sp_executesql into this, and if it wasnt null then return it, and if it was then proceed to insert, e.g.
declare @.temp int
...
SET @.temp = EXECUTE sp_executesql @.SQL
if (@.@.temp = null) goto new_value
SELECT @.temp
return(1)
Obviously this doesnt work, so I am open to suggestions. To be completly honest I have run out of hair to rip out and I am sure this can probably be done in a much more elegant fashion, so any help will be greatly appreciatedHow about
CREATE TABLE #temp(Result vachar(8000))
INSERT INTO #temp(Result) EXEC(@.SQL)|||If it is at all possible I would like to avoid using temporary tables, as if I can do this with SQL memory objects the performance hit would (I assume be much lower)|||Your worried about performance, yet your using dynamic sql...hmmmm|||Exactly, using dynamic sql is a necessary evil in this case (long story), so i dont want to hit the system even more.|||I'm not sure exactly what your looking for...but here are three versions...
DECLARE @.sql nvarchar(4000), @.x int, @.y int, @.z int
PRINT 'Something that will work'
SELECT @.sql = N'SELECT * FROM INFORMATION_SCHEMA.TABLES'
EXECUTE @.y = sp_EXECuteSQL @.sql
SELECT @.z = @.@.ERROR, @.x = @.@.ROWCOUNT
SELECT @.x, @.y, @.z
PRINT 'Something that will return 0 rows'
SELECT @.sql = N'SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ' + '''' + 'Brett' + ''''
EXECUTE @.y = sp_EXECuteSQL @.sql
SELECT @.z = @.@.ERROR, @.x = @.@.ROWCOUNT
SELECT @.x, @.y, @.z
PRINT 'Something that will explode'
SELECT @.sql = N'SELECT * FROM INFORMATION_SCHEMA.BRETT'
EXECUTE @.y = sp_EXECuteSQL @.sql
SELECT @.z = @.@.ERROR, @.x = @.@.ROWCOUNT
SELECT @.x, @.y, @.z|||Originally posted by Brett Kaiser
PRINT 'Something that will explode'
SELECT @.sql = N'SELECT * FROM INFORMATION_SCHEMA.BRETT'
EXECUTE @.y = sp_EXECuteSQL @.sql
SELECT @.z = @.@.ERROR, @.x = @.@.ROWCOUNT
SELECT @.x, @.y, @.z
[/code]
Well .. brett... it did explode ...and it ruined my laptop ... i m gonna sue you for this one :)|||Originally posted by Enigma
Well .. brett... it did explode ...and it ruined my laptop ... i m gonna sue you for this one :)
I'll see if I can dig up an 8086 for you...I'm sure it'll be an improvement...
Did you want 1 or 2 5 1/4 drives?
And I think there's a huge 250k chip in there...
Black and green are you ok, right?|||Brett .. no need to ... i ve already got a 8086 with 1 MB RAM ...
dont bother yourself with the 256 K .. right now I am trying to get Windows 3.11 loaded on it .. help me out there if you can :)|||Is that vanilla or windows for workgroups?
and a meg...wow...
HUGE|||Just incase anybody is searching the forums for the same answer I was looking for the following stored procedure works :
CREATE PROCEDURE aida_lookup
@.lookup_table varchar(255),
@.lookup_id_name varchar(255),
@.lookup_description_col varchar(255),
@.lookup_value varchar(255)
AS
declare @.procedure_sql nvarchar(2048)
set @.procedure_sql = '
declare @.sql nvarchar(2048)
set @.sql = (SELECT ' + @.lookup_id_name + ' FROM ' + @.lookup_table + ' WHERE ' + @.lookup_description_col + ' = ''' + @.lookup_value +''')
if (@.sql = null)
begin
begin transaction
INSERT INTO ' + @.lookup_table + ' (' + @.lookup_description_col + ') VALUES (''' + @.lookup_value + ''')
SELECT SCOPE_IDENTITY()
if (@.@.error <> 0) goto on_error
commit transaction
return
on_error:
rollback transaction
return
end
else
begin
SELECT @.sql
end
return'
print @.procedure_sql;
EXEC( @.procedure_sql )
return(1)
GO|||You can return values from executesql by OUTPUT semantics on fully declared parameters:
declare @.sql nvarchar(4000)
declare @.params nvarchar(4000)
set @.sql = N'select @.OUT=@.IN * 2'
set @.params = N'@.in int, @.out int OUTPUT'
declare @.in int
declare @.out int
set @.in = 4
exec sp_executesql @.sql, @.params, @.in, @.out OUTPUT
select @.out
=8
lookup_table1
table1_id
description
lookup_table2
table2_id
description
This stored procedure attempts to select the given description from the given table and return the id, if the id isnt present it then inserts the new value and returns the new id.
Now I have this working, ish, but the problem that I have is that in the case of a new insert the procedure returns two datasets, an empty one (as the initial select returned no results) and another with the id of the newly inserted value.
This procedure as it stands right now
CREATE PROCEDURE aida_lookup
@.lookup_table varchar(255),
@.lookup_id_name varchar(255),
@.lookup_description_col varchar(255),
@.lookup_value varchar(255)
AS
declare @.sql nvarchar(2048)
--
-- Check if the given lookup value exists in the give table / column
--
set @.sql ='SELECT ' + @.lookup_id_name + ' FROM ' + @.lookup_table + ' WHERE ' + @.lookup_description_col + ' = ''' + @.lookup_value + ''''
EXECUTE sp_executesql @.sql
if (@.@.rowcount = 0) goto new_value
if (@.@.error <> 0) goto on_error
return(1)
--
-- Insert new lookup value into the given table / column
--
new_value:
-- NEED TO CLEAR PREVIOUS NULL SELECT
begin transaction
set @.sql = 'INSERT INTO ' + @.lookup_table + ' (' + @.lookup_description_col + ') VALUES (''' + @.lookup_value + '''); SELECT SCOPE_IDENTITY()'
execute sp_executesql @.sql
if (@.@.error <> 0) goto on_error
commit transaction
return(1)
--
-- Error handler
--
on_error:
rollback transaction
return (0)
GO
The problem being is that if the value is inserted as a new value I need to remove the empty dataset so that regardless of how the procedure is run, it will always return the id at position row 0 column 0.
My attempted solution was to declare a @.temp var and place the return value of sp_executesql into this, and if it wasnt null then return it, and if it was then proceed to insert, e.g.
declare @.temp int
...
SET @.temp = EXECUTE sp_executesql @.SQL
if (@.@.temp = null) goto new_value
SELECT @.temp
return(1)
Obviously this doesnt work, so I am open to suggestions. To be completly honest I have run out of hair to rip out and I am sure this can probably be done in a much more elegant fashion, so any help will be greatly appreciatedHow about
CREATE TABLE #temp(Result vachar(8000))
INSERT INTO #temp(Result) EXEC(@.SQL)|||If it is at all possible I would like to avoid using temporary tables, as if I can do this with SQL memory objects the performance hit would (I assume be much lower)|||Your worried about performance, yet your using dynamic sql...hmmmm|||Exactly, using dynamic sql is a necessary evil in this case (long story), so i dont want to hit the system even more.|||I'm not sure exactly what your looking for...but here are three versions...
DECLARE @.sql nvarchar(4000), @.x int, @.y int, @.z int
PRINT 'Something that will work'
SELECT @.sql = N'SELECT * FROM INFORMATION_SCHEMA.TABLES'
EXECUTE @.y = sp_EXECuteSQL @.sql
SELECT @.z = @.@.ERROR, @.x = @.@.ROWCOUNT
SELECT @.x, @.y, @.z
PRINT 'Something that will return 0 rows'
SELECT @.sql = N'SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ' + '''' + 'Brett' + ''''
EXECUTE @.y = sp_EXECuteSQL @.sql
SELECT @.z = @.@.ERROR, @.x = @.@.ROWCOUNT
SELECT @.x, @.y, @.z
PRINT 'Something that will explode'
SELECT @.sql = N'SELECT * FROM INFORMATION_SCHEMA.BRETT'
EXECUTE @.y = sp_EXECuteSQL @.sql
SELECT @.z = @.@.ERROR, @.x = @.@.ROWCOUNT
SELECT @.x, @.y, @.z|||Originally posted by Brett Kaiser
PRINT 'Something that will explode'
SELECT @.sql = N'SELECT * FROM INFORMATION_SCHEMA.BRETT'
EXECUTE @.y = sp_EXECuteSQL @.sql
SELECT @.z = @.@.ERROR, @.x = @.@.ROWCOUNT
SELECT @.x, @.y, @.z
[/code]
Well .. brett... it did explode ...and it ruined my laptop ... i m gonna sue you for this one :)|||Originally posted by Enigma
Well .. brett... it did explode ...and it ruined my laptop ... i m gonna sue you for this one :)
I'll see if I can dig up an 8086 for you...I'm sure it'll be an improvement...
Did you want 1 or 2 5 1/4 drives?
And I think there's a huge 250k chip in there...
Black and green are you ok, right?|||Brett .. no need to ... i ve already got a 8086 with 1 MB RAM ...
dont bother yourself with the 256 K .. right now I am trying to get Windows 3.11 loaded on it .. help me out there if you can :)|||Is that vanilla or windows for workgroups?
and a meg...wow...
HUGE|||Just incase anybody is searching the forums for the same answer I was looking for the following stored procedure works :
CREATE PROCEDURE aida_lookup
@.lookup_table varchar(255),
@.lookup_id_name varchar(255),
@.lookup_description_col varchar(255),
@.lookup_value varchar(255)
AS
declare @.procedure_sql nvarchar(2048)
set @.procedure_sql = '
declare @.sql nvarchar(2048)
set @.sql = (SELECT ' + @.lookup_id_name + ' FROM ' + @.lookup_table + ' WHERE ' + @.lookup_description_col + ' = ''' + @.lookup_value +''')
if (@.sql = null)
begin
begin transaction
INSERT INTO ' + @.lookup_table + ' (' + @.lookup_description_col + ') VALUES (''' + @.lookup_value + ''')
SELECT SCOPE_IDENTITY()
if (@.@.error <> 0) goto on_error
commit transaction
return
on_error:
rollback transaction
return
end
else
begin
SELECT @.sql
end
return'
print @.procedure_sql;
EXEC( @.procedure_sql )
return(1)
GO|||You can return values from executesql by OUTPUT semantics on fully declared parameters:
declare @.sql nvarchar(4000)
declare @.params nvarchar(4000)
set @.sql = N'select @.OUT=@.IN * 2'
set @.params = N'@.in int, @.out int OUTPUT'
declare @.in int
declare @.out int
set @.in = 4
exec sp_executesql @.sql, @.params, @.in, @.out OUTPUT
select @.out
=8
Placing of Indexes on seperate data file
Is it good practise to put large nonclustered of frequently queried tables on a seperate datafile to the actual table? I.e. a file using a different disk/s.
Thanks
www.SQLporn.co.uk
This was the best case, especially in DB2. I would suggest that in SQL Server it is generally not the case, especially if you choose your storage system carefully. (such as raid 1+0)
|||It's not a good idea to segregate data based only on access frequency. This
will tend to create an unbalanced workload on your i/o subsystem with
relatively few disks doing the lion's share of the work. It's better to
separate data based on sequential, random or mixed access. This will
maximize sequential throughput because random requests won't interfere with
sequential scanning.
Logs are always accessed sequentially and these should be placed on
dedicated drives. Indexes and data objects with mostly random access
patterns and objects with mostly sequential access should be segregated onto
different disks/arrays. The remaining data can be placed in a 'mixed'
filegroup.
However, unless you have predictable data access patterns, it's best to
distribute files evenly over all of your disks rather than micro-manage
object placement. In my experience, this usually provides the best overall
database performance. Run performance tests with a representative
application workload if you feel inclined to play with specialized
filegroups.
Hope this helps.
Dan Guzman
SQL Server MVP
"Rstubbs" <anonymous@.discussions.microsoft.com> wrote in message
news:7DBF9EE7-D750-4E1E-B6A3-3A7E9D28F9C8@.microsoft.com...
> Is it good practise to put large nonclustered of frequently queried tables
on a seperate datafile to the actual table? I.e. a file using a different
disk/s.
> Thanks
> www.SQLporn.co.uk
Thanks
www.SQLporn.co.uk
This was the best case, especially in DB2. I would suggest that in SQL Server it is generally not the case, especially if you choose your storage system carefully. (such as raid 1+0)
|||It's not a good idea to segregate data based only on access frequency. This
will tend to create an unbalanced workload on your i/o subsystem with
relatively few disks doing the lion's share of the work. It's better to
separate data based on sequential, random or mixed access. This will
maximize sequential throughput because random requests won't interfere with
sequential scanning.
Logs are always accessed sequentially and these should be placed on
dedicated drives. Indexes and data objects with mostly random access
patterns and objects with mostly sequential access should be segregated onto
different disks/arrays. The remaining data can be placed in a 'mixed'
filegroup.
However, unless you have predictable data access patterns, it's best to
distribute files evenly over all of your disks rather than micro-manage
object placement. In my experience, this usually provides the best overall
database performance. Run performance tests with a representative
application workload if you feel inclined to play with specialized
filegroups.
Hope this helps.
Dan Guzman
SQL Server MVP
"Rstubbs" <anonymous@.discussions.microsoft.com> wrote in message
news:7DBF9EE7-D750-4E1E-B6A3-3A7E9D28F9C8@.microsoft.com...
> Is it good practise to put large nonclustered of frequently queried tables
on a seperate datafile to the actual table? I.e. a file using a different
disk/s.
> Thanks
> www.SQLporn.co.uk
Placing of Indexes on seperate data file
Is it good practise to put large nonclustered of frequently queried tables on a seperate datafile to the actual table? I.e. a file using a different disk/s
Thank
www.SQLporn.co.ukIt's not a good idea to segregate data based only on access frequency. This
will tend to create an unbalanced workload on your i/o subsystem with
relatively few disks doing the lion's share of the work. It's better to
separate data based on sequential, random or mixed access. This will
maximize sequential throughput because random requests won't interfere with
sequential scanning.
Logs are always accessed sequentially and these should be placed on
dedicated drives. Indexes and data objects with mostly random access
patterns and objects with mostly sequential access should be segregated onto
different disks/arrays. The remaining data can be placed in a 'mixed'
filegroup.
However, unless you have predictable data access patterns, it's best to
distribute files evenly over all of your disks rather than micro-manage
object placement. In my experience, this usually provides the best overall
database performance. Run performance tests with a representative
application workload if you feel inclined to play with specialized
filegroups.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Rstubbs" <anonymous@.discussions.microsoft.com> wrote in message
news:7DBF9EE7-D750-4E1E-B6A3-3A7E9D28F9C8@.microsoft.com...
> Is it good practise to put large nonclustered of frequently queried tables
on a seperate datafile to the actual table? I.e. a file using a different
disk/s.
> Thanks
> www.SQLporn.co.uk
Thank
www.SQLporn.co.ukIt's not a good idea to segregate data based only on access frequency. This
will tend to create an unbalanced workload on your i/o subsystem with
relatively few disks doing the lion's share of the work. It's better to
separate data based on sequential, random or mixed access. This will
maximize sequential throughput because random requests won't interfere with
sequential scanning.
Logs are always accessed sequentially and these should be placed on
dedicated drives. Indexes and data objects with mostly random access
patterns and objects with mostly sequential access should be segregated onto
different disks/arrays. The remaining data can be placed in a 'mixed'
filegroup.
However, unless you have predictable data access patterns, it's best to
distribute files evenly over all of your disks rather than micro-manage
object placement. In my experience, this usually provides the best overall
database performance. Run performance tests with a representative
application workload if you feel inclined to play with specialized
filegroups.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Rstubbs" <anonymous@.discussions.microsoft.com> wrote in message
news:7DBF9EE7-D750-4E1E-B6A3-3A7E9D28F9C8@.microsoft.com...
> Is it good practise to put large nonclustered of frequently queried tables
on a seperate datafile to the actual table? I.e. a file using a different
disk/s.
> Thanks
> www.SQLporn.co.uk
Placing of Indexes on seperate data file
Is it good practise to put large nonclustered of frequently queried tables o
n a seperate datafile to the actual table? I.e. a file using a different dis
k/s.
Thanks
www.SQLporn.co.ukThis was the best case, especially in DB2. I would suggest that in SQL Serv
er it is generally not the case, especially if you choose your storage syste
m carefully. (such as raid 1+0)|||It's not a good idea to segregate data based only on access frequency. This
will tend to create an unbalanced workload on your i/o subsystem with
relatively few disks doing the lion's share of the work. It's better to
separate data based on sequential, random or mixed access. This will
maximize sequential throughput because random requests won't interfere with
sequential scanning.
Logs are always accessed sequentially and these should be placed on
dedicated drives. Indexes and data objects with mostly random access
patterns and objects with mostly sequential access should be segregated onto
different disks/arrays. The remaining data can be placed in a 'mixed'
filegroup.
However, unless you have predictable data access patterns, it's best to
distribute files evenly over all of your disks rather than micro-manage
object placement. In my experience, this usually provides the best overall
database performance. Run performance tests with a representative
application workload if you feel inclined to play with specialized
filegroups.
Hope this helps.
Dan Guzman
SQL Server MVP
"Rstubbs" <anonymous@.discussions.microsoft.com> wrote in message
news:7DBF9EE7-D750-4E1E-B6A3-3A7E9D28F9C8@.microsoft.com...
> Is it good practise to put large nonclustered of frequently queried tables
on a seperate datafile to the actual table? I.e. a file using a different
disk/s.
> Thanks
> www.SQLporn.co.uk
n a seperate datafile to the actual table? I.e. a file using a different dis
k/s.
Thanks
www.SQLporn.co.ukThis was the best case, especially in DB2. I would suggest that in SQL Serv
er it is generally not the case, especially if you choose your storage syste
m carefully. (such as raid 1+0)|||It's not a good idea to segregate data based only on access frequency. This
will tend to create an unbalanced workload on your i/o subsystem with
relatively few disks doing the lion's share of the work. It's better to
separate data based on sequential, random or mixed access. This will
maximize sequential throughput because random requests won't interfere with
sequential scanning.
Logs are always accessed sequentially and these should be placed on
dedicated drives. Indexes and data objects with mostly random access
patterns and objects with mostly sequential access should be segregated onto
different disks/arrays. The remaining data can be placed in a 'mixed'
filegroup.
However, unless you have predictable data access patterns, it's best to
distribute files evenly over all of your disks rather than micro-manage
object placement. In my experience, this usually provides the best overall
database performance. Run performance tests with a representative
application workload if you feel inclined to play with specialized
filegroups.
Hope this helps.
Dan Guzman
SQL Server MVP
"Rstubbs" <anonymous@.discussions.microsoft.com> wrote in message
news:7DBF9EE7-D750-4E1E-B6A3-3A7E9D28F9C8@.microsoft.com...
> Is it good practise to put large nonclustered of frequently queried tables
on a seperate datafile to the actual table? I.e. a file using a different
disk/s.
> Thanks
> www.SQLporn.co.uk
Placing Notes In Sql Statement
Anyone know how to place not functional "NOTES" in an SQL statements like the authors name or to note functionality?? thanks GRTTry using --
:D|||thanks works great!|||Just as an fyi, you can highlight entire lines in Query Analyzer and use these:
CTRL+SHIFT+C --Comments everything.
CTRL+SHIFT+R --Removes comments from everything.
This is great when you are troubleshooting or want to just type then highlight and comment.|||whenever you ask a question like this in the generic SQL forum, you need to specify which database system you're using
and you guys who give a database-specific answer to a generic question should preface your remarks that way
for example, if you're using microsoft access, you can't put comments in the sql at all
if you're using microsoft sql server, the double dash works, and so does /* comment */
guess which database allows //|||whenever you ask a question like this in the generic SQL forum, you need to specify which database system you're using
That's right. Repeat after me: "Microsoft SQL Server and SQL are not the same thing"...|||-- supposed to be ansi standard.
:rolleyes:|||PL SQL developer oracle version 9i
:D|||thanks works great!|||Just as an fyi, you can highlight entire lines in Query Analyzer and use these:
CTRL+SHIFT+C --Comments everything.
CTRL+SHIFT+R --Removes comments from everything.
This is great when you are troubleshooting or want to just type then highlight and comment.|||whenever you ask a question like this in the generic SQL forum, you need to specify which database system you're using
and you guys who give a database-specific answer to a generic question should preface your remarks that way
for example, if you're using microsoft access, you can't put comments in the sql at all
if you're using microsoft sql server, the double dash works, and so does /* comment */
guess which database allows //|||whenever you ask a question like this in the generic SQL forum, you need to specify which database system you're using
That's right. Repeat after me: "Microsoft SQL Server and SQL are not the same thing"...|||-- supposed to be ansi standard.
:rolleyes:|||PL SQL developer oracle version 9i
placing norms on a bar chart
Maybe someone can help. I'd like to do one of two things, depending on which is actually possible:
- Place one chart on top of another so that, in addition to having a simple bar chart, I can also have a way of tracking comparison data on the same chart. (My preference is a line chart with diamond markers on top of a bar chart.)Within one chart, have one of the series be a different chart type. Once series could be a bar and the next could be a line with diamond markers. The catch is the diamonds would have to be placed right on top of their corresponding bars.
Thanks in advance!
Krdjis
Did you look at the "plot data as line" option (in Report Designer, go to the Chart Properties dialog. Click on the Data tab, select the data point you want to make a line, click 'Edit..'. In the chart value dialog, click on the Appearance tab and select 'Plot Data as Line'.)?
You may also want to check out this MSDN article on RS charts: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/MoreSSRSCharts.asp
-- Robert
placing norms on a bar chart
Maybe someone can help. I'd like to do one of two things, depending on which is actually possible:
- Place one chart on top of another so that, in addition to having a simple bar chart, I can also have a way of tracking comparison data on the same chart. (My preference is a line chart with diamond markers on top of a bar chart.)Within one chart, have one of the series be a different chart type. Once series could be a bar and the next could be a line with diamond markers. The catch is the diamonds would have to be placed right on top of their corresponding bars.
Thanks in advance!
Krdjis
Did you look at the "plot data as line" option (in Report Designer, go to the Chart Properties dialog. Click on the Data tab, select the data point you want to make a line, click 'Edit..'. In the chart value dialog, click on the Appearance tab and select 'Plot Data as Line'.)?
You may also want to check out this MSDN article on RS charts: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/MoreSSRSCharts.asp
-- Robert
Placing multiple records on a single line (variables)
Hi, I am new to Crystal Reports, but I know Basic and other programming. I have Crystal Reports XI and am pulling data from our ERP/MRP system, Epicor Vista (Progress DB).
I've been asked to figure out a Crystal Reports for our company (I get thrown into these projects). I know what the report should look like and I know how I would go about some VB code in a macro in Excel if all the data was in worksheets(i.e. like tables).
Below is the data. Any help would be SO appreciated. So far I'm loving Crystal Reports and I can't wait to get some reports our company can start using but I'm stuck on understanding the timing and connection of formulas with the records.
Table1 "JobMtl"
Field "JobComplete":String
Field "JobNum":String
Table2 "JobOper"
Field "OpComplete":Boolean
Field "OprSeq":Number
{JobMtl.JobComplete}
False
True
{JobMtl.JobNum}
2010
2011
{JobOper.Complete}
False
True
{JobOper.OprSeq}
10
20
30
40
Let's say I dragged all 4 fields into a report. It would look like this.
JobNum JobComplete OprSeq OpComplete
2010 False 10 True
2010 False 20 True
2010 False 30 False
2010 False 40 False
2011 False 10 True
2011 False 20 False
2011 False 30 False
I would it to read like this
JobNum JobComplete PrevOp CurrOp NextOp
2010 False 20 30 40
2011 False 10 20 30
**Note: {JobMtl.JobComplete} will be used so I am only reporting jobs that are "not complete". I guess it means nothing to you guys, but I put it here because I was not sure if this will be involved in a formula.
Thanks,
Anthony
My email is ls1z282002_at_yahoo.com (replace "_at_" => "@.") if you would a *.RPT with the data I've shown.Check your e-mail.|||Come on
We all want to see the solution|||Here's what I got from her.
There is a second report that somone from another forum helped me on. I actually need to combine both of these into one because I like the report from SvB_NY because she mad the operations a single String. So I need to do some combining of the two.
I have another question that I'm posting below this.
Anthony|||After showing this to everyone at work, they of course asked for more detail in the report :)
What we have is for each operation {JobOper.OprSeq} it may be an Outsourced (There is a boolean field {JobOper.OprSeq}) that states whether that Operation is outsourced. If it is outsourced they want me to list the PONum & POLn. I know this is simple and I thought so too! I even got it too work! So here's the snag...if the Job does not have any outsourced operations it gets skipped in my report! My reasoning is our ERP software doesn't actually make a index for PO's if that job does not have any PO's against it. Makes sense to me. So how do I handle this?
In the report I attached there should be 3 jobs
2010
2011 => Not shown because no outsourced operations
2012
Am I going to have to create 2 seperate reports, save all the information in arrays. Then match up the arrays with some code and print out a report?
Thanks,
Anthony|||SvB_NY, Thanks for all the help!
I got my report to work and I published it into our ERP software. I appreciate all the help and attached is the final product, if anyone cares to look.
I did never get the PO's to work right because I found out we have multiple PO's to a given operation and that created multiple records for the operation, so my logic for getting the "Prev,Curr,Next" operation didn't work. This is OK though because I was running out of room for the data and I had to have a big comment field. If it was dire for us I'm sure I could get it to work, or I actually I would have posted the question here :)
Anthony
I've been asked to figure out a Crystal Reports for our company (I get thrown into these projects). I know what the report should look like and I know how I would go about some VB code in a macro in Excel if all the data was in worksheets(i.e. like tables).
Below is the data. Any help would be SO appreciated. So far I'm loving Crystal Reports and I can't wait to get some reports our company can start using but I'm stuck on understanding the timing and connection of formulas with the records.
Table1 "JobMtl"
Field "JobComplete":String
Field "JobNum":String
Table2 "JobOper"
Field "OpComplete":Boolean
Field "OprSeq":Number
{JobMtl.JobComplete}
False
True
{JobMtl.JobNum}
2010
2011
{JobOper.Complete}
False
True
{JobOper.OprSeq}
10
20
30
40
Let's say I dragged all 4 fields into a report. It would look like this.
JobNum JobComplete OprSeq OpComplete
2010 False 10 True
2010 False 20 True
2010 False 30 False
2010 False 40 False
2011 False 10 True
2011 False 20 False
2011 False 30 False
I would it to read like this
JobNum JobComplete PrevOp CurrOp NextOp
2010 False 20 30 40
2011 False 10 20 30
**Note: {JobMtl.JobComplete} will be used so I am only reporting jobs that are "not complete". I guess it means nothing to you guys, but I put it here because I was not sure if this will be involved in a formula.
Thanks,
Anthony
My email is ls1z282002_at_yahoo.com (replace "_at_" => "@.") if you would a *.RPT with the data I've shown.Check your e-mail.|||Come on
We all want to see the solution|||Here's what I got from her.
There is a second report that somone from another forum helped me on. I actually need to combine both of these into one because I like the report from SvB_NY because she mad the operations a single String. So I need to do some combining of the two.
I have another question that I'm posting below this.
Anthony|||After showing this to everyone at work, they of course asked for more detail in the report :)
What we have is for each operation {JobOper.OprSeq} it may be an Outsourced (There is a boolean field {JobOper.OprSeq}) that states whether that Operation is outsourced. If it is outsourced they want me to list the PONum & POLn. I know this is simple and I thought so too! I even got it too work! So here's the snag...if the Job does not have any outsourced operations it gets skipped in my report! My reasoning is our ERP software doesn't actually make a index for PO's if that job does not have any PO's against it. Makes sense to me. So how do I handle this?
In the report I attached there should be 3 jobs
2010
2011 => Not shown because no outsourced operations
2012
Am I going to have to create 2 seperate reports, save all the information in arrays. Then match up the arrays with some code and print out a report?
Thanks,
Anthony|||SvB_NY, Thanks for all the help!
I got my report to work and I published it into our ERP software. I appreciate all the help and attached is the final product, if anyone cares to look.
I did never get the PO's to work right because I found out we have multiple PO's to a given operation and that created multiple records for the operation, so my logic for getting the "Prev,Curr,Next" operation didn't work. This is OK though because I was running out of room for the data and I had to have a big comment field. If it was dire for us I'm sure I could get it to work, or I actually I would have posted the question here :)
Anthony
Placing MSDE .mdf and .ldf files on seperate drives.
Hi,
I know that SQL Server (MSDE), databases run much faster when the data and log files are on seperate physical devices. I can do this explictly when I write a CREATE DATABASE SQL script, but would like to change the MODEL database so that all new databases use the sperate data/log file assignments by default. I haven't found a way to alter the sysfiles table in model - I can read the data, but it balks at trying to update it. (recordset is not updateable, or adhoc querries not allowed against a system database).
I know that SQL Server (MSDE), databases run much faster when the data and log files are on seperate physical devices. I can do this explictly when I write a CREATE DATABASE SQL script, but would like to change the MODEL database so that all new databases use the sperate data/log file assignments by default. I haven't found a way to alter the sysfiles table in model - I can read the data, but it balks at trying to update it. (recordset is not updateable, or adhoc querries not allowed against a system database).
I didn't see any options on the MSDE install that implements seperate data/log paths.
I don't have the SQL Server tools (like Enterprise Manager) available, but I do have VS.NET 03 (Standard). And Web Data Administrator.
Any suggestions? Thanks in advance.When you install MSDE you can add settings to the setup.ini file that specify the TARGETDIR (where the binaries and log files go) and DATADIR (where the data goes).
If you need to move the locations of those after the fact,Books Online contains a topic called "Attaching and Detaching a Database" which explains how.
Terri|||Excellent, thanks for the information! I'll check out the Books online link.
I think part of the problem of finding the information that I'm sure must be in the documentation, is knowing the exact terminology used. "Attaching and Detaching" never made it into my search input box. Thanks again..|||If you haven't yet downloaded and installed Books Online you definitely should! I myself refer to it dozens of times each week. It's a terrific free SQL Server 2000 reference and really helps with synax as well as larger concepts.
And yes, it can certainly be a struggle to figure out which terms to look for!
Terri|||Hi,
Thanks again for the BOOKS ONLINE reference, I've used it a number of times already. Curiously, it hasn't been much help with my original problem. I did learn about detaching and reattaching databases, and that it one way to get the physical locations set for my user DB .mdf and .ldf files. I still haven't worked out how to get the model database to refelct these new assignments (thought there are a few more thngs to try).
While searching the BOOKS ONLINE, I did find the customizing MSDE install section. Unfortunatelly, it looks like there are no paramenters to the setup that will allow an installation with seperate drive locations for the data and log files. You can specify the location of the system data files, but the setup automatically adds the log file in the same location. I had considered reinstalling MSDE, or adding a new instance, but that doesn't seem to offer a solution.
I've run into a number of roadblocks when trying to relocate the assignments of the data or log files with the system BDs. Understandably, the system is quite restrictive about manipulating system files. If someone has established an instance of MSDE with sperate physical drive locations for the data and log files, I'd be interested in knowing how they did that.
Other than getting the model DB set up with the data files on one drive and log files on another (so that new USER DBs would follow that pattern), I'm not sure that there would be an advantage to this scheme anyway. I wouldn't think that the master DB data and log files get written to often enough to make a perfomance difference. If an application is creating SQL Server objects dynamically all the time, maybe; but not under normal use. I'd guess the same holds true for other system DBs.
Where it stands now, I can get all my user DBs set up the way I'd like; though I can't get the model DB to do this automattically for new user DBs. I can't get the system DB dat and log files on sperate drives, but that may not be important. I just wonder is MS set it up that way as a performance governor for MSDE?|||Brain,
You won't find much in Books Online about MSDE. MSDE, for all intents and purposes is just SQL Server without a number of the tools (Enterprise manager, etc), and 'hobbled' to certain database sizes etc.
You've got a few options to move your database files around:
1) detach and reattach with the files in the desired locations. This takes your DB offline for a few minutes while you make the change. Check out Books Online for sp_attach_db and sp_detach_db
(this one's a bit tricky/ugly).
2) ALTER the database to specify the creation of NEW files. Look into ALTER DATABASE (add and remove file options). This allows you do to everything while the db is up and running.. but can present some issues where you'll need to tidy things up.
3) Do a backup of your database, then restore it over itself with the files MOVED to where you want them. This might be the best option. And will look something like:
-- backup the db
BACKUP DATABASE myDatabaseName TO DISK = N'C:oink.bak'
-- create a device to 'wrap' your backup and details:
EXEC sp_addumpdevice 'disk', 'mydevice','C:\oink.BAK'
-- Now... just execute this:
RESTORE FILELISTONLY FROM mydevice
this shows you all of your logical file names as well as the actual paths to those files.
map those into something like the following:
RESTORE DATABASE
[myDatabase]
FROM
[mydevice]
WITH
RECOVERY, -- make it work when it comes back online
REPLACE, -- overwrite the existing database if it's there
STATS = 25,
MOVE 'logicalfilename' TO 'G:\SQLData\ProductCatalog_Data.MDF'
,MOVE 'logicalfilename' TO 'F:\SQLData\ProductCatalog_Log.LDF'
EXEC sp_dropdevice mydevice --,'delfile'
the logicalfilenames above correspond to the logical name returned in the resultset you get when you do RESTOREFILELISTONLY
hth
Monday, March 12, 2012
Placing Log on another drive
We have a database that uses the same drive for logs and
data. After a new disk has been installed what is the
easiest way (and reliable) way to move the logs to the new
disk.
I'm thinking of dumping and restoring the database using
the log on clause of restore. Any other ideas ?
TIA,
Jack
Your two best options are probably
BACKUP and RESTORE (WITH MOVE)
or
sp_detach_db and sp_attach_db
More information on each of these can be found within Books Online =
(within your SQL Server program group).
--=20
Keith
"Jack A" <anonymous@.discussions.microsoft.com> wrote in message =
news:1b06e01c44fce$eadf5a60$a601280a@.phx.gbl...
>=20
> We have a database that uses the same drive for logs and=20
> data. After a new disk has been installed what is the=20
> easiest way (and reliable) way to move the logs to the new=20
> disk.
>=20
> I'm thinking of dumping and restoring the database using=20
> the log on clause of restore. Any other ideas ?
>=20
> TIA,
> Jack
|||Hi,
Steps to move LDF file to new drive
1. Execute detach database (SP_DETACH_DB <dbname>
2. Copy the LDF file to new drive
3. Attach the database (SP_DETACH_DB <dbname>,'MDF file with path','LDF file
with path'
Before doing the above steps , make the database single user
alter database <dbname> set single_user with rollback immediate
After the activity turn the databse to multi user by executing
alter database <dbname> set multi_user
Thanks
Hari
MCDBA
"Jack A" <anonymous@.discussions.microsoft.com> wrote in message
news:1b06e01c44fce$eadf5a60$a601280a@.phx.gbl...
> We have a database that uses the same drive for logs and
> data. After a new disk has been installed what is the
> easiest way (and reliable) way to move the logs to the new
> disk.
> I'm thinking of dumping and restoring the database using
> the log on clause of restore. Any other ideas ?
> TIA,
> Jack
|||Backup and then Restore WITH MOVE
Detach\Reattach requires downtime
Greg Jackson
PDX, Oregon
|||Just to be clear: both options require some downtime. =20
--=20
Keith
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message =
news:%234EOGi$TEHA.1260@.TK2MSFTNGP11.phx.gbl...
> Backup and then Restore WITH MOVE
>=20
> Detach\Reattach requires downtime
>=20
>=20
>=20
> Greg Jackson
> PDX, Oregon
>=20
>
|||In article <OZKelQgUEHA.1012@.TK2MSFTNGP09.phx.gbl>, "Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote:
>Just to be clear: both options require some downtime. =20
>--=20
>Keith
Huh?
Why wouldn't you just add a second logfile, move the data to the secondfile,
then remove the first logfile?
data. After a new disk has been installed what is the
easiest way (and reliable) way to move the logs to the new
disk.
I'm thinking of dumping and restoring the database using
the log on clause of restore. Any other ideas ?
TIA,
Jack
Your two best options are probably
BACKUP and RESTORE (WITH MOVE)
or
sp_detach_db and sp_attach_db
More information on each of these can be found within Books Online =
(within your SQL Server program group).
--=20
Keith
"Jack A" <anonymous@.discussions.microsoft.com> wrote in message =
news:1b06e01c44fce$eadf5a60$a601280a@.phx.gbl...
>=20
> We have a database that uses the same drive for logs and=20
> data. After a new disk has been installed what is the=20
> easiest way (and reliable) way to move the logs to the new=20
> disk.
>=20
> I'm thinking of dumping and restoring the database using=20
> the log on clause of restore. Any other ideas ?
>=20
> TIA,
> Jack
|||Hi,
Steps to move LDF file to new drive
1. Execute detach database (SP_DETACH_DB <dbname>
2. Copy the LDF file to new drive
3. Attach the database (SP_DETACH_DB <dbname>,'MDF file with path','LDF file
with path'
Before doing the above steps , make the database single user
alter database <dbname> set single_user with rollback immediate
After the activity turn the databse to multi user by executing
alter database <dbname> set multi_user
Thanks
Hari
MCDBA
"Jack A" <anonymous@.discussions.microsoft.com> wrote in message
news:1b06e01c44fce$eadf5a60$a601280a@.phx.gbl...
> We have a database that uses the same drive for logs and
> data. After a new disk has been installed what is the
> easiest way (and reliable) way to move the logs to the new
> disk.
> I'm thinking of dumping and restoring the database using
> the log on clause of restore. Any other ideas ?
> TIA,
> Jack
|||Backup and then Restore WITH MOVE
Detach\Reattach requires downtime
Greg Jackson
PDX, Oregon
|||Just to be clear: both options require some downtime. =20
--=20
Keith
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message =
news:%234EOGi$TEHA.1260@.TK2MSFTNGP11.phx.gbl...
> Backup and then Restore WITH MOVE
>=20
> Detach\Reattach requires downtime
>=20
>=20
>=20
> Greg Jackson
> PDX, Oregon
>=20
>
|||In article <OZKelQgUEHA.1012@.TK2MSFTNGP09.phx.gbl>, "Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote:
>Just to be clear: both options require some downtime. =20
>--=20
>Keith
Huh?
Why wouldn't you just add a second logfile, move the data to the secondfile,
then remove the first logfile?
Placing Log on another drive
We have a database that uses the same drive for logs and
data. After a new disk has been installed what is the
easiest way (and reliable) way to move the logs to the new
disk.
I'm thinking of dumping and restoring the database using
the log on clause of restore. Any other ideas ?
TIA,
JackYour two best options are probably
BACKUP and RESTORE (WITH MOVE)
or
sp_detach_db and sp_attach_db
More information on each of these can be found within Books Online =(within your SQL Server program group).
-- Keith
"Jack A" <anonymous@.discussions.microsoft.com> wrote in message =news:1b06e01c44fce$eadf5a60$a601280a@.phx.gbl...
> > We have a database that uses the same drive for logs and > data. After a new disk has been installed what is the > easiest way (and reliable) way to move the logs to the new > disk.
> > I'm thinking of dumping and restoring the database using > the log on clause of restore. Any other ideas ?
> > TIA,
> Jack|||Hi,
Steps to move LDF file to new drive
1. Execute detach database (SP_DETACH_DB <dbname>
2. Copy the LDF file to new drive
3. Attach the database (SP_DETACH_DB <dbname>,'MDF file with path','LDF file
with path'
Before doing the above steps , make the database single user
alter database <dbname> set single_user with rollback immediate
After the activity turn the databse to multi user by executing
alter database <dbname> set multi_user
Thanks
Hari
MCDBA
"Jack A" <anonymous@.discussions.microsoft.com> wrote in message
news:1b06e01c44fce$eadf5a60$a601280a@.phx.gbl...
> We have a database that uses the same drive for logs and
> data. After a new disk has been installed what is the
> easiest way (and reliable) way to move the logs to the new
> disk.
> I'm thinking of dumping and restoring the database using
> the log on clause of restore. Any other ideas ?
> TIA,
> Jack|||Backup and then Restore WITH MOVE
Detach\Reattach requires downtime
Greg Jackson
PDX, Oregon|||Just to be clear: both options require some downtime. -- Keith
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message =news:%234EOGi$TEHA.1260@.TK2MSFTNGP11.phx.gbl...
> Backup and then Restore WITH MOVE
> > Detach\Reattach requires downtime
> > > > Greg Jackson
> PDX, Oregon
> >|||In article <OZKelQgUEHA.1012@.TK2MSFTNGP09.phx.gbl>, "Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote:
>Just to be clear: both options require some downtime. >-- >Keith
Huh?
Why wouldn't you just add a second logfile, move the data to the secondfile,
then remove the first logfile?
data. After a new disk has been installed what is the
easiest way (and reliable) way to move the logs to the new
disk.
I'm thinking of dumping and restoring the database using
the log on clause of restore. Any other ideas ?
TIA,
JackYour two best options are probably
BACKUP and RESTORE (WITH MOVE)
or
sp_detach_db and sp_attach_db
More information on each of these can be found within Books Online =(within your SQL Server program group).
-- Keith
"Jack A" <anonymous@.discussions.microsoft.com> wrote in message =news:1b06e01c44fce$eadf5a60$a601280a@.phx.gbl...
> > We have a database that uses the same drive for logs and > data. After a new disk has been installed what is the > easiest way (and reliable) way to move the logs to the new > disk.
> > I'm thinking of dumping and restoring the database using > the log on clause of restore. Any other ideas ?
> > TIA,
> Jack|||Hi,
Steps to move LDF file to new drive
1. Execute detach database (SP_DETACH_DB <dbname>
2. Copy the LDF file to new drive
3. Attach the database (SP_DETACH_DB <dbname>,'MDF file with path','LDF file
with path'
Before doing the above steps , make the database single user
alter database <dbname> set single_user with rollback immediate
After the activity turn the databse to multi user by executing
alter database <dbname> set multi_user
Thanks
Hari
MCDBA
"Jack A" <anonymous@.discussions.microsoft.com> wrote in message
news:1b06e01c44fce$eadf5a60$a601280a@.phx.gbl...
> We have a database that uses the same drive for logs and
> data. After a new disk has been installed what is the
> easiest way (and reliable) way to move the logs to the new
> disk.
> I'm thinking of dumping and restoring the database using
> the log on clause of restore. Any other ideas ?
> TIA,
> Jack|||Backup and then Restore WITH MOVE
Detach\Reattach requires downtime
Greg Jackson
PDX, Oregon|||Just to be clear: both options require some downtime. -- Keith
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message =news:%234EOGi$TEHA.1260@.TK2MSFTNGP11.phx.gbl...
> Backup and then Restore WITH MOVE
> > Detach\Reattach requires downtime
> > > > Greg Jackson
> PDX, Oregon
> >|||In article <OZKelQgUEHA.1012@.TK2MSFTNGP09.phx.gbl>, "Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote:
>Just to be clear: both options require some downtime. >-- >Keith
Huh?
Why wouldn't you just add a second logfile, move the data to the secondfile,
then remove the first logfile?
Placing Log on another drive
We have a database that uses the same drive for logs and
data. After a new disk has been installed what is the
easiest way (and reliable) way to move the logs to the new
disk.
I'm thinking of dumping and restoring the database using
the log on clause of restore. Any other ideas ?
TIA,
JackYour two best options are probably
BACKUP and RESTORE (WITH MOVE)
or
sp_detach_db and sp_attach_db
More information on each of these can be found within Books Online =
(within your SQL Server program group).
--=20
Keith
"Jack A" <anonymous@.discussions.microsoft.com> wrote in message =
news:1b06e01c44fce$eadf5a60$a601280a@.phx
.gbl...
>=20
> We have a database that uses the same drive for logs and=20
> data. After a new disk has been installed what is the=20
> easiest way (and reliable) way to move the logs to the new=20
> disk.
>=20
> I'm thinking of dumping and restoring the database using=20
> the log on clause of restore. Any other ideas ?
>=20
> TIA,
> Jack|||Hi,
Steps to move LDF file to new drive
1. Execute detach database (SP_DETACH_DB <dbname>
2. Copy the LDF file to new drive
3. Attach the database (SP_DETACH_DB <dbname>,'MDF file with path','LDF file
with path'
Before doing the above steps , make the database single user
alter database <dbname> set single_user with rollback immediate
After the activity turn the databse to multi user by executing
alter database <dbname> set multi_user
Thanks
Hari
MCDBA
"Jack A" <anonymous@.discussions.microsoft.com> wrote in message
news:1b06e01c44fce$eadf5a60$a601280a@.phx
.gbl...
> We have a database that uses the same drive for logs and
> data. After a new disk has been installed what is the
> easiest way (and reliable) way to move the logs to the new
> disk.
> I'm thinking of dumping and restoring the database using
> the log on clause of restore. Any other ideas ?
> TIA,
> Jack|||Backup and then Restore WITH MOVE
Detach\Reattach requires downtime
Greg Jackson
PDX, Oregon|||Just to be clear: both options require some downtime. =20
--=20
Keith
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message =
news:%234EOGi$TEHA.1260@.TK2MSFTNGP11.phx.gbl...
> Backup and then Restore WITH MOVE
>=20
> Detach\Reattach requires downtime
>=20
>=20
>=20
> Greg Jackson
> PDX, Oregon
>=20
>|||In article <OZKelQgUEHA.1012@.TK2MSFTNGP09.phx.gbl>, "Keith Kratochvil" <sqlguy.back2u@.comcas
t.net> wrote:
>Just to be clear: both options require some downtime. =20
>--=20
>Keith
Huh?
Why wouldn't you just add a second logfile, move the data to the secondfile,
then remove the first logfile?
data. After a new disk has been installed what is the
easiest way (and reliable) way to move the logs to the new
disk.
I'm thinking of dumping and restoring the database using
the log on clause of restore. Any other ideas ?
TIA,
JackYour two best options are probably
BACKUP and RESTORE (WITH MOVE)
or
sp_detach_db and sp_attach_db
More information on each of these can be found within Books Online =
(within your SQL Server program group).
--=20
Keith
"Jack A" <anonymous@.discussions.microsoft.com> wrote in message =
news:1b06e01c44fce$eadf5a60$a601280a@.phx
.gbl...
>=20
> We have a database that uses the same drive for logs and=20
> data. After a new disk has been installed what is the=20
> easiest way (and reliable) way to move the logs to the new=20
> disk.
>=20
> I'm thinking of dumping and restoring the database using=20
> the log on clause of restore. Any other ideas ?
>=20
> TIA,
> Jack|||Hi,
Steps to move LDF file to new drive
1. Execute detach database (SP_DETACH_DB <dbname>
2. Copy the LDF file to new drive
3. Attach the database (SP_DETACH_DB <dbname>,'MDF file with path','LDF file
with path'
Before doing the above steps , make the database single user
alter database <dbname> set single_user with rollback immediate
After the activity turn the databse to multi user by executing
alter database <dbname> set multi_user
Thanks
Hari
MCDBA
"Jack A" <anonymous@.discussions.microsoft.com> wrote in message
news:1b06e01c44fce$eadf5a60$a601280a@.phx
.gbl...
> We have a database that uses the same drive for logs and
> data. After a new disk has been installed what is the
> easiest way (and reliable) way to move the logs to the new
> disk.
> I'm thinking of dumping and restoring the database using
> the log on clause of restore. Any other ideas ?
> TIA,
> Jack|||Backup and then Restore WITH MOVE
Detach\Reattach requires downtime
Greg Jackson
PDX, Oregon|||Just to be clear: both options require some downtime. =20
--=20
Keith
"Jaxon" <GregoryAJackson@.hotmail.com> wrote in message =
news:%234EOGi$TEHA.1260@.TK2MSFTNGP11.phx.gbl...
> Backup and then Restore WITH MOVE
>=20
> Detach\Reattach requires downtime
>=20
>=20
>=20
> Greg Jackson
> PDX, Oregon
>=20
>|||In article <OZKelQgUEHA.1012@.TK2MSFTNGP09.phx.gbl>, "Keith Kratochvil" <sqlguy.back2u@.comcas
t.net> wrote:
>Just to be clear: both options require some downtime. =20
>--=20
>Keith
Huh?
Why wouldn't you just add a second logfile, move the data to the secondfile,
then remove the first logfile?
Placing labels on line chart
Hi,
Some of my charts have many data series on them and the colours can be
difficult to differentiate. I would like to include a label on the
actual line chart as the legend is not helpful - the colours are too
similar. Is this easy to do?
For an example see here:
http://tinyurl.com/3kkou
Thanks.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.htmlFollowing the provided link shows an error on the server side. Not sure if
this is exactly what you are looking for, but you may be interested in the
thread "bar chart help" and the example attached at the bottom of the
posting.
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=7f5be65f-e515-4993-9526-e9e85480c67a&sloc=en-us
For the line charts you may however want to have the labels e.g. only on the
first datapoint of the line and would need to use the First() aggregate
function to determine it.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:eGPmtFZ7EHA.3336@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Some of my charts have many data series on them and the colours can be
> difficult to differentiate. I would like to include a label on the
> actual line chart as the legend is not helpful - the colours are too
> similar. Is this easy to do?
> For an example see here:
> http://tinyurl.com/3kkou
> Thanks.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
Some of my charts have many data series on them and the colours can be
difficult to differentiate. I would like to include a label on the
actual line chart as the legend is not helpful - the colours are too
similar. Is this easy to do?
For an example see here:
http://tinyurl.com/3kkou
Thanks.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.htmlFollowing the provided link shows an error on the server side. Not sure if
this is exactly what you are looking for, but you may be interested in the
thread "bar chart help" and the example attached at the bottom of the
posting.
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=7f5be65f-e515-4993-9526-e9e85480c67a&sloc=en-us
For the line charts you may however want to have the labels e.g. only on the
first datapoint of the line and would need to use the First() aggregate
function to determine it.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Mark Allison" <marka@.no.tinned.meat.mvps.org> wrote in message
news:eGPmtFZ7EHA.3336@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Some of my charts have many data series on them and the colours can be
> difficult to differentiate. I would like to include a label on the
> actual line chart as the legend is not helpful - the colours are too
> similar. Is this easy to do?
> For an example see here:
> http://tinyurl.com/3kkou
> Thanks.
> --
> Mark Allison, SQL Server MVP
> http://www.markallison.co.uk
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602m.html
Placing Filegroups on separate drives
Hi All,
I have adatabase which is over 150GB in size and an estimated size of 1 Terabyte. I am planning to split the database into various filegroups. I have already separated the indexes and data from the Database. My Tempdb is in separate physical drive.
I was thinking if it’s a good idea to place each filegroup into a separate drive. The drives are not physical drives but partitioned, however the drive I portioned is raid 1+0.
Would I get any great performance benefit of placing filegroups on their own drives ?
Placing the filegroup structure below.
No
Filegroup
Drive
1
PRIMARY
F
2
LOG
J
3
AD_Data
G
4
AD_Indexes
H
5
CV_Data
I
6
CV_Indexes
J
7
DIM_Data
K
8
DIM_Indexes
L
9
FACT_Data
M
10
FACT_Indexes
N
11
STAGING_Data
O
12
STAGING_Indexes
P
13
ORG_Data
Q
14
ORG_Indexes
R
15
TMP_Data
S
16
TMP_Indexes
T
Any help would be appreciated
Thx
It depends whether these drives have separate controllers or not. If so, then its possible that a query accessing data in separate files groups will use multiple contollers so the data can be access simultaneously giving better performance. If they all use the same controller then performance will be be helped. If one or more tend to grow more quickly they could be put on separate drives and auth growth paramters might be different but that really isn't peformance related unless auto growth occurs during an INSERT or UPDATE. It may be faster to autogrow one smaller filegroup than one huge database.
My 2 cents
placing existing table on a different filegroup
Hi ,
Is there any commands to place existing tables to a new file group besides
using the EM as below ?
How to place an existing table on a different filegroup (Enterprise Manager)
To place an existing table on a different filegroup
Expand a server group, and then expand a server.
Expand Databases, expand the database in which the table belongs, and then
click Tables.
In the details pane, right-click the table, and then click Design Table.
Right-click any column, and then click Properties.
On the Tables tab, in the Table Filegroup list, select the filegroup on whic
h
to place the table.
Optionally, in the Text Filegroup list, select a filegroup on which to place
any text, image, and ntext columns.
appreciate any advise
tks & rdgs
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200605/1You can (re)create a clustered index for the table; the data will always
move to the FG that the clustered index is built on
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"maxzsim via webservertalk.com" <u14644@.uwe> wrote in message
news:5ff96f8c4fdba@.uwe...
> Hi ,
> Is there any commands to place existing tables to a new file group besides
> using the EM as below ?
> How to place an existing table on a different filegroup (Enterprise
> Manager)
> To place an existing table on a different filegroup
> Expand a server group, and then expand a server.
> Expand Databases, expand the database in which the table belongs, and then
> click Tables.
> In the details pane, right-click the table, and then click Design Table.
> Right-click any column, and then click Properties.
> On the Tables tab, in the Table Filegroup list, select the filegroup on
> which
> to place the table.
> Optionally, in the Text Filegroup list, select a filegroup on which to
> place
> any text, image, and ntext columns.
> appreciate any advise
> tks & rdgs
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200605/1|||Hi ,
Another is for the Text FileGroup : i seem not able to change it to the new
filegroup it keeps turning back to the PRIMARY FileGroup even though i have
changed to a new filegorup and saved it
any possibly reason ?
tks & rdgs
maxzsim wrote:
>Hi ,
> Is there any commands to place existing tables to a new file group besides
>using the EM as below ?
>How to place an existing table on a different filegroup (Enterprise Manager
)
>To place an existing table on a different filegroup
>Expand a server group, and then expand a server.
>Expand Databases, expand the database in which the table belongs, and then
>click Tables.
>In the details pane, right-click the table, and then click Design Table.
>Right-click any column, and then click Properties.
>On the Tables tab, in the Table Filegroup list, select the filegroup on whi
ch
>to place the table.
>Optionally, in the Text Filegroup list, select a filegroup on which to plac
e
>any text, image, and ntext columns.
>appreciate any advise
>tks & rdgs
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200605/1|||It is interesting to see on Profiler what Enterprise Manager is doing when
you follow that procedure. Among other commands is doing
-- to move mytable to myfilegroup
create table tmp_mytable ( ... ) on myfilegroup
insert into tmp_mytable (select ... from mytable)
drop table mytable
sp_rename tmp_mytable mytable
Ben Nevarez, MCDBA, OCP
Database Administrator
"maxzsim via webservertalk.com" wrote:
> Hi ,
> Is there any commands to place existing tables to a new file group beside
s
> using the EM as below ?
> How to place an existing table on a different filegroup (Enterprise Manage
r)
> To place an existing table on a different filegroup
> Expand a server group, and then expand a server.
> Expand Databases, expand the database in which the table belongs, and then
> click Tables.
> In the details pane, right-click the table, and then click Design Table.
> Right-click any column, and then click Properties.
> On the Tables tab, in the Table Filegroup list, select the filegroup on wh
ich
> to place the table.
> Optionally, in the Text Filegroup list, select a filegroup on which to pla
ce
> any text, image, and ntext columns.
> appreciate any advise
> tks & rdgs
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200605/1
>|||tks
Kalen Delaney wrote:
>You can (re)create a clustered index for the table; the data will always
>move to the FG that the clustered index is built on
>
>[quoted text clipped - 20 lines]
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200605/1|||hi ,
this is what i got from the profiler using the standard i.e no inclsion of
columns or new processes or any filter:
use [GL53_03_Bkup]
set implicit_transactions on SET TEXTSIZE 2147483647
sp_MStablechecks N'dbo.GL030106'
IF @.@.TRANCOUNT > 0 COMMIT TRAN
set implicit_transactions off SET TEXTSIZE 64512
use [GL53_03_Bkup]
select s1 = o.name, s2 = user_name(o.uid),o.crdate,o.id, N'SystemObj' = (cas
e
when (OBJECTPROPERTY(o.id, N'IsMSShipped')=1) then 1 else OBJECTPROPERTY(o.i
d,
N'IsSystemTable') end), o.category, 0, ObjectProperty(o.id,
N'TableHasActiveFulltextIndex'), ObjectProperty(o.id,
N'TableFulltextCatalogId'), N'FakeTable' = (case when (OBJECTPROPERTY(o.id,
N'tableisfake')=1) then 1 else 0 end), (case when (OBJECTPROPERTY(o.i
d,
N'IsQuotedIdentOn')=1) then 1 else 0 end), (case when (OBJECTPROPERTY(o.id,
N'IsAnsiNullsOn')=1) then 1 else 0 end) from dbo.sysobjects o, dbo.
sysindexes i where OBJECTPROPERTY(o.id, N'IsTable') = 1 and i.id = o.id and
i.
indid < 2 and o.name not like N'#%' and o.id = object_id(N'[dbo].[GL030106]
') order by s1, s2
SELECT N'Testing Connection...'
what can i actually make out of this ? or what other events i should be
included to see such changes ?
The table : Gl030106's original "Text FileGroup" is "PRIMARY" when i tried t
o
change it to my own FG : TEST2 and saved it goes back to PRIMARY
tks & rdgs
Ben Nevarez wrote:
>It is interesting to see on Profiler what Enterprise Manager is doing when
>you follow that procedure. Among other commands is doing
>-- to move mytable to myfilegroup
>create table tmp_mytable ( ... ) on myfilegroup
>insert into tmp_mytable (select ... from mytable)
>drop table mytable
>sp_rename tmp_mytable mytable
>Ben Nevarez, MCDBA, OCP
>Database Administrator
>
>[quoted text clipped - 17 lines]
Message posted via http://www.webservertalk.com
Is there any commands to place existing tables to a new file group besides
using the EM as below ?
How to place an existing table on a different filegroup (Enterprise Manager)
To place an existing table on a different filegroup
Expand a server group, and then expand a server.
Expand Databases, expand the database in which the table belongs, and then
click Tables.
In the details pane, right-click the table, and then click Design Table.
Right-click any column, and then click Properties.
On the Tables tab, in the Table Filegroup list, select the filegroup on whic
h
to place the table.
Optionally, in the Text Filegroup list, select a filegroup on which to place
any text, image, and ntext columns.
appreciate any advise
tks & rdgs
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200605/1You can (re)create a clustered index for the table; the data will always
move to the FG that the clustered index is built on
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"maxzsim via webservertalk.com" <u14644@.uwe> wrote in message
news:5ff96f8c4fdba@.uwe...
> Hi ,
> Is there any commands to place existing tables to a new file group besides
> using the EM as below ?
> How to place an existing table on a different filegroup (Enterprise
> Manager)
> To place an existing table on a different filegroup
> Expand a server group, and then expand a server.
> Expand Databases, expand the database in which the table belongs, and then
> click Tables.
> In the details pane, right-click the table, and then click Design Table.
> Right-click any column, and then click Properties.
> On the Tables tab, in the Table Filegroup list, select the filegroup on
> which
> to place the table.
> Optionally, in the Text Filegroup list, select a filegroup on which to
> place
> any text, image, and ntext columns.
> appreciate any advise
> tks & rdgs
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200605/1|||Hi ,
Another is for the Text FileGroup : i seem not able to change it to the new
filegroup it keeps turning back to the PRIMARY FileGroup even though i have
changed to a new filegorup and saved it
any possibly reason ?
tks & rdgs
maxzsim wrote:
>Hi ,
> Is there any commands to place existing tables to a new file group besides
>using the EM as below ?
>How to place an existing table on a different filegroup (Enterprise Manager
)
>To place an existing table on a different filegroup
>Expand a server group, and then expand a server.
>Expand Databases, expand the database in which the table belongs, and then
>click Tables.
>In the details pane, right-click the table, and then click Design Table.
>Right-click any column, and then click Properties.
>On the Tables tab, in the Table Filegroup list, select the filegroup on whi
ch
>to place the table.
>Optionally, in the Text Filegroup list, select a filegroup on which to plac
e
>any text, image, and ntext columns.
>appreciate any advise
>tks & rdgs
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200605/1|||It is interesting to see on Profiler what Enterprise Manager is doing when
you follow that procedure. Among other commands is doing
-- to move mytable to myfilegroup
create table tmp_mytable ( ... ) on myfilegroup
insert into tmp_mytable (select ... from mytable)
drop table mytable
sp_rename tmp_mytable mytable
Ben Nevarez, MCDBA, OCP
Database Administrator
"maxzsim via webservertalk.com" wrote:
> Hi ,
> Is there any commands to place existing tables to a new file group beside
s
> using the EM as below ?
> How to place an existing table on a different filegroup (Enterprise Manage
r)
> To place an existing table on a different filegroup
> Expand a server group, and then expand a server.
> Expand Databases, expand the database in which the table belongs, and then
> click Tables.
> In the details pane, right-click the table, and then click Design Table.
> Right-click any column, and then click Properties.
> On the Tables tab, in the Table Filegroup list, select the filegroup on wh
ich
> to place the table.
> Optionally, in the Text Filegroup list, select a filegroup on which to pla
ce
> any text, image, and ntext columns.
> appreciate any advise
> tks & rdgs
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200605/1
>|||tks
Kalen Delaney wrote:
>You can (re)create a clustered index for the table; the data will always
>move to the FG that the clustered index is built on
>
>[quoted text clipped - 20 lines]
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200605/1|||hi ,
this is what i got from the profiler using the standard i.e no inclsion of
columns or new processes or any filter:
use [GL53_03_Bkup]
set implicit_transactions on SET TEXTSIZE 2147483647
sp_MStablechecks N'dbo.GL030106'
IF @.@.TRANCOUNT > 0 COMMIT TRAN
set implicit_transactions off SET TEXTSIZE 64512
use [GL53_03_Bkup]
select s1 = o.name, s2 = user_name(o.uid),o.crdate,o.id, N'SystemObj' = (cas
e
when (OBJECTPROPERTY(o.id, N'IsMSShipped')=1) then 1 else OBJECTPROPERTY(o.i
d,
N'IsSystemTable') end), o.category, 0, ObjectProperty(o.id,
N'TableHasActiveFulltextIndex'), ObjectProperty(o.id,
N'TableFulltextCatalogId'), N'FakeTable' = (case when (OBJECTPROPERTY(o.id,
N'tableisfake')=1) then 1 else 0 end), (case when (OBJECTPROPERTY(o.i
d,
N'IsQuotedIdentOn')=1) then 1 else 0 end), (case when (OBJECTPROPERTY(o.id,
N'IsAnsiNullsOn')=1) then 1 else 0 end) from dbo.sysobjects o, dbo.
sysindexes i where OBJECTPROPERTY(o.id, N'IsTable') = 1 and i.id = o.id and
i.
indid < 2 and o.name not like N'#%' and o.id = object_id(N'[dbo].[GL030106]
') order by s1, s2
SELECT N'Testing Connection...'
what can i actually make out of this ? or what other events i should be
included to see such changes ?
The table : Gl030106's original "Text FileGroup" is "PRIMARY" when i tried t
o
change it to my own FG : TEST2 and saved it goes back to PRIMARY
tks & rdgs
Ben Nevarez wrote:
>It is interesting to see on Profiler what Enterprise Manager is doing when
>you follow that procedure. Among other commands is doing
>-- to move mytable to myfilegroup
>create table tmp_mytable ( ... ) on myfilegroup
>insert into tmp_mytable (select ... from mytable)
>drop table mytable
>sp_rename tmp_mytable mytable
>Ben Nevarez, MCDBA, OCP
>Database Administrator
>
>[quoted text clipped - 17 lines]
Message posted via http://www.webservertalk.com
Subscribe to:
Posts (Atom)