How do I merge NDF files into the MDF ?

Hi
I have a database with a whole lot of secondary data files
(*.ndf's). What do I run to merge all of those into the
Primary data file (*.mdf)?
Thanks
HHi,
To remove a NDF file, you must first moved the data off from the NDF file
onto
the other members in the data set. To do this, use the EMPTY FILE
parameter in DBCC SHRINKFILE command.
This will empty the file and mark it as unavailable. From there, you should
be able to use the REMOVE FILE parameter in ALTER DATABASE command.
Steps:-
1. Do a Full database backup
2. Execute below to move the data of the NDF file to other files
DBCC SHRINKFILE('logical_ndf_name',EMPTYFILE)
3. Now you execute the command to remove the RDF file
ALTER database <dbname> REMOVE FILE 'logical_ndf_name'
Do the same steps for all available NDF files in the database.
Thanks
Hari
MCDBA
"H" <anonymous@.discussions.microsoft.com> wrote in message
news:2329c01c45e5e$176c5950$a301280a@.phx
.gbl...
> Hi
> I have a database with a whole lot of secondary data files
> (*.ndf's). What do I run to merge all of those into the
> Primary data file (*.mdf)?
> Thanks
> H|||Thanks Hari
I'll give it a go!
Regards
H|||Hari
I see that each *.ndf is also in it's own filegroup, so it
won't let me run a dbcc shrinkfile...it keeps saying that
the filegroup is full (it isn't physically and expand
dynamically).
Thanks
H|||Then you need to get the tables and indexes in that file group onto some oth
er filegroup. Recreate
the indexes. If you have a table without a clustered index, move that by cre
ate a clustered index
(on another filegroup) and then possibly dropping that clustered index.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"H" <anonymous@.discussions.microsoft.com> wrote in message
news:22e0101c45e6a$13448b20$a601280a@.phx
.gbl...
> Hari
> I see that each *.ndf is also in it's own filegroup, so it
> won't let me run a dbcc shrinkfile...it keeps saying that
> the filegroup is full (it isn't physically and expand
> dynamically).
> Thanks
> H|||Look ,H
CREATE DATABASE mywind
GO
ALTER DATABASE mywind ADD FILEGROUP new_customers
GO
ALTER DATABASE mywind ADD FILE
(NAME='mywind_data_1',
FILENAME='d:\mw.dat1')
TO FILEGROUP new_customers
GO
CREATE TABLE mywind..t1 (id int) ON new_customers
GO
INSERT INTO mywind..t1 (id ) VALUES (1)
GO
ALTER DATABASE mywind REMOVE FILE mywind_data_1
--Server: Msg 5042, Level 16, State 1, Line 1
--The file 'mywind_data_1' cannot be removed because it is not empty.
USE mywind
DBCC SHRINKFILE (mywind_data_1, EMPTYFILE)
--
I went to EM and change the filegroup for t1 to PRIMARY FILEGROUP
--
GO
ALTER DATABASE mywind REMOVE FILE mywind_data_1
ALTER DATABASE mywind REMOVE FILEGROUP new_customers
GO
DROP DATABASE mywind
"H" <anonymous@.discussions.microsoft.com> wrote in message
news:22e0101c45e6a$13448b20$a601280a@.phx
.gbl...
> Hari
> I see that each *.ndf is also in it's own filegroup, so it
> won't let me run a dbcc shrinkfile...it keeps saying that
> the filegroup is full (it isn't physically and expand
> dynamically).
> Thanks
> H|||I use the method to remove a .ndf file from the primary group. But after I r
an the DBCC, the .ndf is still not empty and there are 0.6 MB for data in th
at file, which I couldn't clear. and I couldn't remove the file from the gro
up as well since the file i
s not empty. and suggestion?
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine sup
ports Post Alerts, Ratings, and Searching.|||I use the method to remove a .ndf file from the primary group. But after I r
an the DBCC, the .ndf is still not empty and there are 0.6 MB for data in th
at file, which I couldn't clear. and I couldn't remove the file from the gro
up as well since the file i
s not empty. and suggestion?
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine sup
ports Post Alerts, Ratings, and Searching.|||I use the method to remove a .ndf file from the primary group. But after I r
an the DBCC, the .ndf is still not empty and there are 0.6 MB for data in th
at file, which I couldn't clear. and I couldn't remove the file from the gro
up as well since the file i
s not empty. and suggestion?
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine sup
ports Post Alerts, Ratings, and Searching.|||Try SHRINKFILE with EMPTYFILE option again. I've heard of cases where you ne
ed to do it twice before it is
completely empty...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SqlJunkies User" <User@.-NOSPAM-SqlJunkies.com> wrote in message
news:%231Ab8wDjEHA.3524@.TK2MSFTNGP10.phx.gbl...
> I use the method to remove a .ndf file from the primary group. But after I ran the
DBCC, the .ndf is still
not empty and there are 0.6 MB for data in that file, which I couldn't clear
. and I couldn't remove the file
from the group as well since the file is not empty. and suggestion?
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports
Post Alerts, Ratings, and
Searching.

How do I merge NDF files into the MDF ?

Hi
I have a database with a whole lot of secondary data files
(*.ndf's). What do I run to merge all of those into the
Primary data file (*.mdf)?
Thanks
H
Hi,
To remove a NDF file, you must first moved the data off from the NDF file
onto
the other members in the data set. To do this, use the EMPTY FILE
parameter in DBCC SHRINKFILE command.
This will empty the file and mark it as unavailable. From there, you should
be able to use the REMOVE FILE parameter in ALTER DATABASE command.
Steps:-
1. Do a Full database backup
2. Execute below to move the data of the NDF file to other files
DBCC SHRINKFILE('logical_ndf_name',EMPTYFILE)
3. Now you execute the command to remove the RDF file
ALTER database <dbname> REMOVE FILE 'logical_ndf_name'
Do the same steps for all available NDF files in the database.
Thanks
Hari
MCDBA
"H" <anonymous@.discussions.microsoft.com> wrote in message
news:2329c01c45e5e$176c5950$a301280a@.phx.gbl...
> Hi
> I have a database with a whole lot of secondary data files
> (*.ndf's). What do I run to merge all of those into the
> Primary data file (*.mdf)?
> Thanks
> H
|||Thanks Hari
I'll give it a go!
Regards
H
|||Hari
I see that each *.ndf is also in it's own filegroup, so it
won't let me run a dbcc shrinkfile...it keeps saying that
the filegroup is full (it isn't physically and expand
dynamically).
Thanks
H
|||Then you need to get the tables and indexes in that file group onto some other filegroup. Recreate
the indexes. If you have a table without a clustered index, move that by create a clustered index
(on another filegroup) and then possibly dropping that clustered index.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"H" <anonymous@.discussions.microsoft.com> wrote in message
news:22e0101c45e6a$13448b20$a601280a@.phx.gbl...
> Hari
> I see that each *.ndf is also in it's own filegroup, so it
> won't let me run a dbcc shrinkfile...it keeps saying that
> the filegroup is full (it isn't physically and expand
> dynamically).
> Thanks
> H
|||Look ,H
CREATE DATABASE mywind
GO
ALTER DATABASE mywind ADD FILEGROUP new_customers
GO
ALTER DATABASE mywind ADD FILE
(NAME='mywind_data_1',
FILENAME='d:\mw.dat1')
TO FILEGROUP new_customers
GO
CREATE TABLE mywind..t1 (id int) ON new_customers
GO
INSERT INTO mywind..t1 (id ) VALUES (1)
GO
ALTER DATABASE mywind REMOVE FILE mywind_data_1
--Server: Msg 5042, Level 16, State 1, Line 1
--The file 'mywind_data_1' cannot be removed because it is not empty.
USE mywind
DBCC SHRINKFILE (mywind_data_1, EMPTYFILE)
I went to EM and change the filegroup for t1 to PRIMARY FILEGROUP
GO
ALTER DATABASE mywind REMOVE FILE mywind_data_1
ALTER DATABASE mywind REMOVE FILEGROUP new_customers
GO
DROP DATABASE mywind
"H" <anonymous@.discussions.microsoft.com> wrote in message
news:22e0101c45e6a$13448b20$a601280a@.phx.gbl...
> Hari
> I see that each *.ndf is also in it's own filegroup, so it
> won't let me run a dbcc shrinkfile...it keeps saying that
> the filegroup is full (it isn't physically and expand
> dynamically).
> Thanks
> H
|||I use the method to remove a .ndf file from the primary group. But after I ran the DBCC, the .ndf is still not empty and there are 0.6 MB for data in that file, which I couldn't clear. and I couldn't remove the file from the group as well since the file i
s not empty. and suggestion?
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.
|||I use the method to remove a .ndf file from the primary group. But after I ran the DBCC, the .ndf is still not empty and there are 0.6 MB for data in that file, which I couldn't clear. and I couldn't remove the file from the group as well since the file i
s not empty. and suggestion?
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.
|||I use the method to remove a .ndf file from the primary group. But after I ran the DBCC, the .ndf is still not empty and there are 0.6 MB for data in that file, which I couldn't clear. and I couldn't remove the file from the group as well since the file i
s not empty. and suggestion?
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.
|||Try SHRINKFILE with EMPTYFILE option again. I've heard of cases where you need to do it twice before it is
completely empty...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SqlJunkies User" <User@.-NOSPAM-SqlJunkies.com> wrote in message
news:%231Ab8wDjEHA.3524@.TK2MSFTNGP10.phx.gbl...
> I use the method to remove a .ndf file from the primary group. But after I ran the DBCC, the .ndf is still
not empty and there are 0.6 MB for data in that file, which I couldn't clear. and I couldn't remove the file
from the group as well since the file is not empty. and suggestion?
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and
Searching.

How do I know if it is working?

Hi
I think I have installed MSDE sucessfully. The new servie is running, but how can I test ifit is working? Can I place my files anywhere on the system?
All help appreciatedAs the service is running try to access it using "osql" utility.

If you are talking about data files, yes you can declare the path while creating your database...

check create database syntax for thissql

How do I get the next or previous rows in a database

Hi
I will be greatful for help with the expression.
Example:
'I currently requested 50 rows from a database and now I want the next 50
rows.
I know that the combination of field A, B, C and D makes the row unique.
What should my where clause be?
Is there some other way of doing this?'
select top 50 * from some_table
where <what should this be?>
order by A desc, B desc, C desc, D desc
Regards
Kjell Arne Johansen
Hi
http://databases.aspfaq.com/database/how-do-i-page-through-a-recordset.html
"Kjell Arne Johansen" <KjellArneJohansen@.discussions.microsoft.com> wrote in
message news:BCDE65CA-FB23-43FC-B6AD-B188073DBD81@.microsoft.com...
> Hi
> I will be greatful for help with the expression.
> Example:
> 'I currently requested 50 rows from a database and now I want the next 50
> rows.
> I know that the combination of field A, B, C and D makes the row unique.
> What should my where clause be?
> Is there some other way of doing this?'
> select top 50 * from some_table
> where <what should this be?>
> order by A desc, B desc, C desc, D desc
> Regards
> Kjell Arne Johansen
|||> I know that the combination of field A, B, C and D makes the row unique.
> What should my where clause be?
Below is an example that should work as long as the columns do not allow
nulls.
SELECT TOP 50 *
FROM dbo.some_table
WHERE
A < @.LastA
OR (A = @.LastA AND B < @.LastB)
OR (A = @.LastA AND B = @.LastB AND C < @.LastC)
OR (A = @.LastA AND B = @.LastB AND C = @.LastC AND D < @.LastD)
ORDER BY
A DESC,
B DESC,
C DESC,
D DESC

> Is there some other way of doing this?'
Uri provided some other common techniques. In my experience, the key based
pagination method is the fastest and ought to scale linearly regardless of
table size with the appropriate indexes (e.g. conposite primary key or
unique constraint). The downside with key pagination is that indexing can
quickly become complex with user-defined sorting and/or selection criteria.
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"Kjell Arne Johansen" <KjellArneJohansen@.discussions.microsoft.com> wrote in
message news:BCDE65CA-FB23-43FC-B6AD-B188073DBD81@.microsoft.com...
> Hi
> I will be greatful for help with the expression.
> Example:
> 'I currently requested 50 rows from a database and now I want the next 50
> rows.
> I know that the combination of field A, B, C and D makes the row unique.
> What should my where clause be?
> Is there some other way of doing this?'
> select top 50 * from some_table
> where <what should this be?>
> order by A desc, B desc, C desc, D desc
> Regards
> Kjell Arne Johansen
|||Thank you very much.
It looks to me that this I can use both on MS Access and SQL Server.
If I want to 'page up' then I only change from less than '<' to higher than
'>' I assume.
Thanks.
Kjell Arne Johansen
software engineer at Kongsberg Maritime as
"Dan Guzman" wrote:

> Below is an example that should work as long as the columns do not allow
> nulls.
> SELECT TOP 50 *
> FROM dbo.some_table
> WHERE
> A < @.LastA
> OR (A = @.LastA AND B < @.LastB)
> OR (A = @.LastA AND B = @.LastB AND C < @.LastC)
> OR (A = @.LastA AND B = @.LastB AND C = @.LastC AND D < @.LastD)
> ORDER BY
> A DESC,
> B DESC,
> C DESC,
> D DESC
>
> Uri provided some other common techniques. In my experience, the key based
> pagination method is the fastest and ought to scale linearly regardless of
> table size with the appropriate indexes (e.g. conposite primary key or
> unique constraint). The downside with key pagination is that indexing can
> quickly become complex with user-defined sorting and/or selection criteria.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> http://weblogs.sqlteam.com/dang/
> "Kjell Arne Johansen" <KjellArneJohansen@.discussions.microsoft.com> wrote in
> message news:BCDE65CA-FB23-43FC-B6AD-B188073DBD81@.microsoft.com...
>
|||Thank you very much.
A lot of good examples here.
I forgot to say that I would very much like the solution to work on MS
Access also, so it must probably be some kind of sql expression. -or I have
to make two different solutions for MS Access and SQL Server.
Kjell Arne Johansen
software engineer at Kongsberg Maritime as
"Uri Dimant" wrote:

> Hi
> http://databases.aspfaq.com/database/how-do-i-page-through-a-recordset.html
>
> "Kjell Arne Johansen" <KjellArneJohansen@.discussions.microsoft.com> wrote in
> message news:BCDE65CA-FB23-43FC-B6AD-B188073DBD81@.microsoft.com...
>
>
|||> If I want to 'page up' then I only change from less than '<' to higher
> than
> '>' I assume.
In addition to reversing the operators, you'll need to reverse the ORDER BY
so that TOP returns the previous page rows. You will also need wrap this
query in a derived table so that you can order the previous page data in the
original sequence (DESC):
SELECT *
FROM (
SELECT TOP 50 *
FROM dbo.some_table
WHERE
A > @.LastA
OR (A = @.LastA AND B > @.LastB)
OR (A = @.LastA AND B = @.LastB AND C > @.LastC)
OR (A = @.LastA AND B = @.LastB AND C = @.LastC AND D > @.LastD)
ORDER BY
A ASC,
B ASC,
C ASC,
D ASC
) AS previous_page
ORDER BY
A DESC,
B DESC,
C DESC,
D DESC
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"Kjell Arne Johansen" <KjellArneJohansen@.discussions.microsoft.com> wrote in
message news:D2F2F4A6-523F-4333-8DFE-FD4B67E08551@.microsoft.com...[vbcol=seagreen]
> Thank you very much.
> It looks to me that this I can use both on MS Access and SQL Server.
> If I want to 'page up' then I only change from less than '<' to higher
> than
> '>' I assume.
> Thanks.
> Kjell Arne Johansen
> software engineer at Kongsberg Maritime as
> "Dan Guzman" wrote:

How do I get SQL Server version (numbers only) programatically

Hi
I want to get SQL Server version. I know this way: Select @.@.version
But this gives me a very long string, i.e.
Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 18 2006
00:57:48
Copyright (c) 1988-2000 Microsoft Corporation Personal Edition on
Windows
NT 5.0 (Build 2195: Service Pack 4)
i want this value in a c++ application, and depending upon that i have
to fire query.
I just want the the major version number 6.0 ,7.0 , 8.0 or whatever
maybe.
How do I get these numbers only?
Regards,
Ravi ShankarFor the more recent versions of SQL Server, you can use SELECT
SERVERPROPERTY('ProductVersion'). Otherwise, you'll need to parse the
@.@.VERSION string. Transact-SQL example:
DECLARE @.Version nvarchar(125)
IF SERVERPROPERTY('ProductVersion') IS NOT NULL
BEGIN
SELECT SERVERPROPERTY('ProductVersion')
END
ELSE
BEGIN
SET @.Version =
SUBSTRING(@.@.VERSION, CHARINDEX('- ',
@.@.VERSION) + 2, 13)
SET @.Version = LEFT(@.Version, CHARINDEX(' ',
@.Version))
SELECT @.Version
END
Hope this helps.
Dan Guzman
SQL Server MVP
<ravidhari@.gmail.com> wrote in message
news:1156937494.514331.282750@.m73g2000cwd.googlegroups.com...
> Hi
> I want to get SQL Server version. I know this way: Select @.@.version
>
> But this gives me a very long string, i.e.
>
> Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 18 2006
> 00:57:48
> Copyright (c) 1988-2000 Microsoft Corporation Personal Edition on
> Windows
> NT 5.0 (Build 2195: Service Pack 4)
> i want this value in a c++ application, and depending upon that i have
> to fire query.
> I just want the the major version number 6.0 ,7.0 , 8.0 or whatever
> maybe.
> How do I get these numbers only?
>
> Regards,
> Ravi Shankar
>|||SELECT SERVERPROPERTY('ProductVersion')
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||Ravi,
You can try using "exec master..xp_msver".
It will return a recordset containing lots of info about your SQL Server
installation. One of the records will contain information about the
"ProductVersion".
Cheers!
SQLCatz

How do I force a job to fail ?

Hi
I think this is quite simple, but how do I force a job to fail using a TSQL
command?
I have a job that copies a backup to another server and then it starts
another job that restore the backup. What I'd like to do, is to put a step
in the first job, that e.g. runs RESTORE HEADERONLY FROM DISK=...... and if
this doesn't return the correct value the job should quit with a failure.
Regards
SteenDo a RAISERROR with severity > 10.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
news:uVV$UWxnFHA.3256@.tk2msftngp13.phx.gbl...
> Hi
> I think this is quite simple, but how do I force a job to fail using a TSQ
L command?
> I have a job that copies a backup to another server and then it starts ano
ther job that restore
> the backup. What I'd like to do, is to put a step in the first job, that e
.g. runs RESTORE
> HEADERONLY FROM DISK=...... and if this doesn't return the correct value
the job should quit with
> a failure.
> Regards
> Steen
>|||why dont you use continue, break in procedure that is getting executed
instead of stopping job on condition.
"Steen Persson (DK)" wrote:

> Hi
> I think this is quite simple, but how do I force a job to fail using a TSQ
L
> command?
> I have a job that copies a backup to another server and then it starts
> another job that restore the backup. What I'd like to do, is to put a step
> in the first job, that e.g. runs RESTORE HEADERONLY FROM DISK=...... and
if
> this doesn't return the correct value the job should quit with a failure.
> Regards
> Steen
>
>|||R.D wrote:
> why dont you use continue, break in procedure that is getting
> executed instead of stopping job on condition.
>
well...good question. It was just the first idea that came to my mind. If I
"force" the job to fail, I'd get a notification just like if the job was
failing in the "normal" way, so I saw it as the easiest way of doing it.
My next issue (which I thought I knew how to do...) is how I fetch the
result set I get from running "RESTORE HEADERONLY..." so I can evaluate on
e.g. the BackupName field?
Regards
Steen|||> My next issue (which I thought I knew how to do...) is how I fetch the result set I get f
rom
> running "RESTORE HEADERONLY..." so I can evaluate on e.g. the BackupName field?[/
color]
CREATE TABLE r (...)
INSERT INTO r(...)
EXEC('RESTORE HEADERONLY ...')
You can find the structure of the table you need to create, and the overall
technique at
http://www.karaszi.com/SQLServer/ut...ll_in_file.asp.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Steen Persson (DK)" <spe@.REMOVEdatea.dk> wrote in message
news:%23ky1olxnFHA.3316@.tk2msftngp13.phx.gbl...
> R.D wrote:
> well...good question. It was just the first idea that came to my mind. If
I "force" the job to
> fail, I'd get a notification just like if the job was failing in the "norm
al" way, so I saw it as
> the easiest way of doing it.
> My next issue (which I thought I knew how to do...) is how I fetch the res
ult set I get from
> running "RESTORE HEADERONLY..." so I can evaluate on e.g. the BackupName
field?
> Regards
> Steen
>

How do I fire a sql statement from being seen by a trace session

Hi
I am trying to do an OPENROWSET call to an ODBC driver which needs a key
to work but i need to keep the KEY hidden from being seen/ traced.
Is there a way to execute an sql statement and disable a tracing session or
another way stop a OPENROWSET argument from being traced and duplicated.
Thank you in advance for your time..
EdwinEdwin,
You can't hide anything away from the Profiler. It's not guaranteed that
the Profiler will get every statement but you can't hide statements
kind regards
Greg O
Need to document your databases. Use the firs and still the best AGS SQL
Scribe
http://www.ag-software.com
"Exonet Developer" <Exonet Developer@.discussions.microsoft.com> wrote in
message news:C5F9A585-7362-41AC-8C2D-6D7574FFF60E@.microsoft.com...
> Hi
> I am trying to do an OPENROWSET call to an ODBC driver which needs a key
> to work but i need to keep the KEY hidden from being seen/ traced.
> Is there a way to execute an sql statement and disable a tracing session
> or
> another way stop a OPENROWSET argument from being traced and duplicated.
> Thank you in advance for your time..
> Edwin|||you can add sensitive text like "sp_addlogin" as comment in your sql
statements and sql profiler will hide this statement
Aleksandar Grbic
MCDBA, Senior Database Administrator
"Exonet Developer" wrote:

> Hi
> I am trying to do an OPENROWSET call to an ODBC driver which needs a key
> to work but i need to keep the KEY hidden from being seen/ traced.
> Is there a way to execute an sql statement and disable a tracing session o
r
> another way stop a OPENROWSET argument from being traced and duplicated.
> Thank you in advance for your time..
> Edwin|||Hi Aleksandar,
Can you give me an example of this. I don't really understand what you mean
kind regards
Greg O
"Aleksandar Grbic" <AleksandarGrbic@.discussions.microsoft.com> wrote in
message news:FD863E94-89C2-45D3-B682-F7AA03E0C07C@.microsoft.com...
> you can add sensitive text like "sp_addlogin" as comment in your sql
> statements and sql profiler will hide this statement
> --
> Aleksandar Grbic
> MCDBA, Senior Database Administrator
>
> "Exonet Developer" wrote:
>

How do I find the length of a pattern?

Hi
I want to get the substring of a pattern match but I'm not sure how to
reliably get the length of the pattern as well as the start position. I'm
looking for html encoding strings, i.e. '%&%;%'. Please see below.
declare @.str varchar(100)
declare @.len int, @.start int
Set @.str = 'bob&burt'
set @.start = patindex('%&%;%',@.str)
set @.len = patindex('%;%',@.str)
print SUBSTRING ( @.str , @.start , @.len-@.start+1 )
-- works
Set @.str = ';bob&burt'
set @.start = patindex('%&%;%',@.str)
set @.len = patindex('%;%',@.str)
print SUBSTRING ( @.str , @.start , @.len-@.start+1 )
-- causes an error
Many thanks
AndrewI'm not sure if it will help you as-is, but here is a function I wrote that
solves a very similar problem: finding the end of a pattern. In order to
get the length of a match, I assume you need the end of the match...
http://www.sqljunkies.com/WebLog/am...plitString.aspx
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Andrew Jocelyn" <andrew.jocelyn@.REMOVETHIS.empetus.co.uk> wrote in message
news:u8w129MoFHA.2904@.TK2MSFTNGP14.phx.gbl...
> Hi
> I want to get the substring of a pattern match but I'm not sure how to
> reliably get the length of the pattern as well as the start position. I'm
> looking for html encoding strings, i.e. '%&%;%'. Please see below.
> declare @.str varchar(100)
> declare @.len int, @.start int
> Set @.str = 'bob&burt'
> set @.start = patindex('%&%;%',@.str)
> set @.len = patindex('%;%',@.str)
> print SUBSTRING ( @.str , @.start , @.len-@.start+1 )
> -- works
> Set @.str = ';bob&burt'
> set @.start = patindex('%&%;%',@.str)
> set @.len = patindex('%;%',@.str)
> print SUBSTRING ( @.str , @.start , @.len-@.start+1 )
> -- causes an error
> Many thanks
> Andrew
>

How do I find the db name from a stored procedure in the db

Hi
I need to know the name of the database from a stored procedure in that
database.
How can I do that?
Regards
Kjell Arne Johansen
Kjell Arne Johansen wrote:
> Hi
> I need to know the name of the database from a stored procedure in that
> database.
> How can I do that?
> Regards
> Kjell Arne Johansen
DB_NAME()
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||Hi
Thank You, thats what i needed!
kaj
"Tracy McKibben" wrote:

> Kjell Arne Johansen wrote:
> DB_NAME()
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>

How do I find the db name from a stored procedure in the db

Hi
I need to know the name of the database from a stored procedure in that
database.
How can I do that?
Regards
Kjell Arne JohansenKjell Arne Johansen wrote:
> Hi
> I need to know the name of the database from a stored procedure in that
> database.
> How can I do that?
> Regards
> Kjell Arne Johansen
DB_NAME()
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hi
Thank You, thats what i needed!
kaj
"Tracy McKibben" wrote:

> Kjell Arne Johansen wrote:
> DB_NAME()
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>

How do I find out what prevents my log file from being truncated

Hi
I think I need a little help on Log file architecture.
I have a database where I temporarily disabled my log backup schedules. We
did some updates on the database, and I didn't wanted the backup jobs to
kick in in the middle of the update.
Now I've enabled the log backups again, but of course my logfiles has now
grown to a size that it will never reach again in normal production. I'd
therefore like to shrink the logfile to a more feasible size, but I don't
seem to have much luck with that.
I've backed up the logfile, but when I then issue a DBCC SHRINKFILE command,
I get the message -
'Cannot shrink log file 2 (xxxxxx) because all logical log files are in
use.'
If I then run DBCC SQLPERF (Logspace) it tells me that log file space used
is 0.68127996 %. I've then tried to run a DBCC OPENTRAN to see if I've any
open transaction but that reports no active transactions.
My question is now, how do I find out that it is that prevents me from
shrinking the file. I assume that I'll have to chekc the Virtual Log Files,
but how do I do this? I've looked in BOL but I can't find any description of
how I can "see" the logfile "structure"? I know that this has been covered
in this newsgroup before, but I can't find any threads that helps me right
now.
Regards
Steen
Steen
What target size have you specified in DBCC SHRINKFILE command? Try to
increase it.
DECLARE @.db INT
SELECT @.db=db_id()
DBCC LOGINFO (@.db)
Pay attention on status column . If you see that value=2 is the last row
that means you have 'opened' ',in process' transaction
Perfom dummy inserst on order to move this value=2 at the top and then try
to shrink file
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:%23FNHyKkSFHA.204@.TK2MSFTNGP15.phx.gbl...
> Hi
> I think I need a little help on Log file architecture.
> I have a database where I temporarily disabled my log backup schedules. We
> did some updates on the database, and I didn't wanted the backup jobs to
> kick in in the middle of the update.
> Now I've enabled the log backups again, but of course my logfiles has now
> grown to a size that it will never reach again in normal production. I'd
> therefore like to shrink the logfile to a more feasible size, but I don't
> seem to have much luck with that.
> I've backed up the logfile, but when I then issue a DBCC SHRINKFILE
command,
> I get the message -
> 'Cannot shrink log file 2 (xxxxxx) because all logical log files are in
> use.'
> If I then run DBCC SQLPERF (Logspace) it tells me that log file space used
> is 0.68127996 %. I've then tried to run a DBCC OPENTRAN to see if I've any
> open transaction but that reports no active transactions.
> My question is now, how do I find out that it is that prevents me from
> shrinking the file. I assume that I'll have to chekc the Virtual Log
Files,
> but how do I do this? I've looked in BOL but I can't find any description
of
> how I can "see" the logfile "structure"? I know that this has been covered
> in this newsgroup before, but I can't find any threads that helps me right
> now.
> Regards
> Steen
>
>
|||Steen
Have a look at these articles.
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/default...b;en-us;256650
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/default...b;en-us;272318
http://www.mssqlserver.com/faq/logs-shrinklog.asp
Regards
John
"Steen Persson" wrote:

> Hi
> I think I need a little help on Log file architecture.
> I have a database where I temporarily disabled my log backup schedules. We
> did some updates on the database, and I didn't wanted the backup jobs to
> kick in in the middle of the update.
> Now I've enabled the log backups again, but of course my logfiles has now
> grown to a size that it will never reach again in normal production. I'd
> therefore like to shrink the logfile to a more feasible size, but I don't
> seem to have much luck with that.
> I've backed up the logfile, but when I then issue a DBCC SHRINKFILE command,
> I get the message -
> 'Cannot shrink log file 2 (xxxxxx) because all logical log files are in
> use.'
> If I then run DBCC SQLPERF (Logspace) it tells me that log file space used
> is 0.68127996 %. I've then tried to run a DBCC OPENTRAN to see if I've any
> open transaction but that reports no active transactions.
> My question is now, how do I find out that it is that prevents me from
> shrinking the file. I assume that I'll have to chekc the Virtual Log Files,
> but how do I do this? I've looked in BOL but I can't find any description of
> how I can "see" the logfile "structure"? I know that this has been covered
> in this newsgroup before, but I can't find any threads that helps me right
> now.
> Regards
> Steen
>
>
>
|||Hi Uri
Thanks for your reply.
I have actually tried to increase the target size, but that still doesn't
seems to do the trick. Also if I look at the physical file, it is approx. 21
GB. When running DBCC SQLPERF(LogSpace) it tells me that space in use is 11
% i.e. the file should in theory only be taking up approx. 2 GB. Also if I
run DBCC LOGINFO it gives me 1641 records, and only the last approx 200
records has the status 2. I can't find any describtion in BOL of the LOGINFO
command, so I'm not quite sure how to read it, but does the status 0 means
that it's "old" transactions that can be removed?
It might very well be that I still have some active transcations that
prevents my logfile from shrinking, but it puzzles me that I don't seems to
be able to "see" these transcations anywhere.
Regards
Steen
Uri Dimant wrote:[vbcol=seagreen]
> Steen
> What target size have you specified in DBCC SHRINKFILE command? Try to
> increase it.
> DECLARE @.db INT
> SELECT @.db=db_id()
> DBCC LOGINFO (@.db)
> Pay attention on status column . If you see that value=2 is the last
> row that means you have 'opened' ',in process' transaction
> Perfom dummy inserst on order to move this value=2 at the top and
> then try to shrink file
>
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:%23FNHyKkSFHA.204@.TK2MSFTNGP15.phx.gbl...
|||The file can only be shrunk from the end towards the beginning. You need to get 0 at the end. backup
the log to see if that makes you have 0 at the end. If not, do some dummy modifications so the head
of the log moves toward the beginning (in 2000, the shrink command should do this for you). Then
backup again. When you have 0 at the end, you can shrink it.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message news:ulusXfkSFHA.3296@.TK2MSFTNGP15.phx.gbl...
> Hi Uri
> Thanks for your reply.
> I have actually tried to increase the target size, but that still doesn't
> seems to do the trick. Also if I look at the physical file, it is approx. 21
> GB. When running DBCC SQLPERF(LogSpace) it tells me that space in use is 11
> % i.e. the file should in theory only be taking up approx. 2 GB. Also if I
> run DBCC LOGINFO it gives me 1641 records, and only the last approx 200
> records has the status 2. I can't find any describtion in BOL of the LOGINFO
> command, so I'm not quite sure how to read it, but does the status 0 means
> that it's "old" transactions that can be removed?
> It might very well be that I still have some active transcations that
> prevents my logfile from shrinking, but it puzzles me that I don't seems to
> be able to "see" these transcations anywhere.
> Regards
> Steen
>
>
> Uri Dimant wrote:
>
|||Hi Tibor
I've also read that SQL 2000 fills in the "dummy" records when I do the
SHRINKFILE, but I think I've missed something. You say that the dummys are
being inserted by the SHRINKFILE command and then I have to backup again and
then I can run the SHRINKFILE once more. I haven't got that little "twist"
until now, so it sounds like it's the 2nd. backup I'm missing. When I think
about it I should have thought about that, but from BOL I had got the
impression that SQL2000 did it "behind the scenes".
After I've done the backup once more, it works like a charm....:-).
Thanks for your inputs everybody...
Regards
Steen
Tibor Karaszi wrote:[vbcol=seagreen]
> The file can only be shrunk from the end towards the beginning. You
> need to get 0 at the end. backup the log to see if that makes you
> have 0 at the end. If not, do some dummy modifications so the head of
> the log moves toward the beginning (in 2000, the shrink command
> should do this for you). Then backup again. When you have 0 at the
> end, you can shrink it.
>
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:ulusXfkSFHA.3296@.TK2MSFTNGP15.phx.gbl...

How do I find out what prevents my log file from being truncated

Hi
I think I need a little help on Log file architecture.
I have a database where I temporarily disabled my log backup schedules. We
did some updates on the database, and I didn't wanted the backup jobs to
kick in in the middle of the update.
Now I've enabled the log backups again, but of course my logfiles has now
grown to a size that it will never reach again in normal production. I'd
therefore like to shrink the logfile to a more feasible size, but I don't
seem to have much luck with that.
I've backed up the logfile, but when I then issue a DBCC SHRINKFILE command,
I get the message -
'Cannot shrink log file 2 (xxxxxx) because all logical log files are in
use.'
If I then run DBCC SQLPERF (Logspace) it tells me that log file space used
is 0.68127996 %. I've then tried to run a DBCC OPENTRAN to see if I've any
open transaction but that reports no active transactions.
My question is now, how do I find out that it is that prevents me from
shrinking the file. I assume that I'll have to chekc the Virtual Log Files,
but how do I do this? I've looked in BOL but I can't find any description of
how I can "see" the logfile "structure"? I know that this has been covered
in this newsgroup before, but I can't find any threads that helps me right
now.
Regards
SteenSteen
What target size have you specified in DBCC SHRINKFILE command? Try to
increase it.
DECLARE @.db INT
SELECT @.db=db_id()
DBCC LOGINFO (@.db)
Pay attention on status column . If you see that value=2 is the last row
that means you have 'opened' ',in process' transaction
Perfom dummy inserst on order to move this value=2 at the top and then try
to shrink file
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:%23FNHyKkSFHA.204@.TK2MSFTNGP15.phx.gbl...
> Hi
> I think I need a little help on Log file architecture.
> I have a database where I temporarily disabled my log backup schedules. We
> did some updates on the database, and I didn't wanted the backup jobs to
> kick in in the middle of the update.
> Now I've enabled the log backups again, but of course my logfiles has now
> grown to a size that it will never reach again in normal production. I'd
> therefore like to shrink the logfile to a more feasible size, but I don't
> seem to have much luck with that.
> I've backed up the logfile, but when I then issue a DBCC SHRINKFILE
command,
> I get the message -
> 'Cannot shrink log file 2 (xxxxxx) because all logical log files are in
> use.'
> If I then run DBCC SQLPERF (Logspace) it tells me that log file space used
> is 0.68127996 %. I've then tried to run a DBCC OPENTRAN to see if I've any
> open transaction but that reports no active transactions.
> My question is now, how do I find out that it is that prevents me from
> shrinking the file. I assume that I'll have to chekc the Virtual Log
Files,
> but how do I do this? I've looked in BOL but I can't find any description
of
> how I can "see" the logfile "structure"? I know that this has been covered
> in this newsgroup before, but I can't find any threads that helps me right
> now.
> Regards
> Steen
>
>|||Steen
Have a look at these articles.
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/defaul...kb;en-us;256650
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/defaul...kb;en-us;272318
http://www.mssqlserver.com/faq/logs-shrinklog.asp
Regards
John
"Steen Persson" wrote:

> Hi
> I think I need a little help on Log file architecture.
> I have a database where I temporarily disabled my log backup schedules. We
> did some updates on the database, and I didn't wanted the backup jobs to
> kick in in the middle of the update.
> Now I've enabled the log backups again, but of course my logfiles has now
> grown to a size that it will never reach again in normal production. I'd
> therefore like to shrink the logfile to a more feasible size, but I don't
> seem to have much luck with that.
> I've backed up the logfile, but when I then issue a DBCC SHRINKFILE comman
d,
> I get the message -
> 'Cannot shrink log file 2 (xxxxxx) because all logical log files are in
> use.'
> If I then run DBCC SQLPERF (Logspace) it tells me that log file space used
> is 0.68127996 %. I've then tried to run a DBCC OPENTRAN to see if I've any
> open transaction but that reports no active transactions.
> My question is now, how do I find out that it is that prevents me from
> shrinking the file. I assume that I'll have to chekc the Virtual Log Files
,
> but how do I do this? I've looked in BOL but I can't find any description
of
> how I can "see" the logfile "structure"? I know that this has been covered
> in this newsgroup before, but I can't find any threads that helps me right
> now.
> Regards
> Steen
>
>
>|||Hi Uri
Thanks for your reply.
I have actually tried to increase the target size, but that still doesn't
seems to do the trick. Also if I look at the physical file, it is approx. 21
GB. When running DBCC SQLPERF(LogSpace) it tells me that space in use is 11
% i.e. the file should in theory only be taking up approx. 2 GB. Also if I
run DBCC LOGINFO it gives me 1641 records, and only the last approx 200
records has the status 2. I can't find any describtion in BOL of the LOGINFO
command, so I'm not quite sure how to read it, but does the status 0 means
that it's "old" transactions that can be removed?
It might very well be that I still have some active transcations that
prevents my logfile from shrinking, but it puzzles me that I don't seems to
be able to "see" these transcations anywhere.
Regards
Steen
Uri Dimant wrote:[vbcol=seagreen]
> Steen
> What target size have you specified in DBCC SHRINKFILE command? Try to
> increase it.
> DECLARE @.db INT
> SELECT @.db=db_id()
> DBCC LOGINFO (@.db)
> Pay attention on status column . If you see that value=2 is the last
> row that means you have 'opened' ',in process' transaction
> Perfom dummy inserst on order to move this value=2 at the top and
> then try to shrink file
>
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:%23FNHyKkSFHA.204@.TK2MSFTNGP15.phx.gbl...|||The file can only be shrunk from the end towards the beginning. You need to
get 0 at the end. backup
the log to see if that makes you have 0 at the end. If not, do some dummy mo
difications so the head
of the log moves toward the beginning (in 2000, the shrink command should do
this for you). Then
backup again. When you have 0 at the end, you can shrink it.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message news:ulusXfkSFHA.3296@.TK2MSFTNGP15.phx
.gbl...
> Hi Uri
> Thanks for your reply.
> I have actually tried to increase the target size, but that still doesn't
> seems to do the trick. Also if I look at the physical file, it is approx.
21
> GB. When running DBCC SQLPERF(LogSpace) it tells me that space in use is 1
1
> % i.e. the file should in theory only be taking up approx. 2 GB. Also if I
> run DBCC LOGINFO it gives me 1641 records, and only the last approx 200
> records has the status 2. I can't find any describtion in BOL of the LOGIN
FO
> command, so I'm not quite sure how to read it, but does the status 0 means
> that it's "old" transactions that can be removed?
> It might very well be that I still have some active transcations that
> prevents my logfile from shrinking, but it puzzles me that I don't seems t
o
> be able to "see" these transcations anywhere.
> Regards
> Steen
>
>
> Uri Dimant wrote:
>|||Hi Tibor
I've also read that SQL 2000 fills in the "dummy" records when I do the
SHRINKFILE, but I think I've missed something. You say that the dummys are
being inserted by the SHRINKFILE command and then I have to backup again and
then I can run the SHRINKFILE once more. I haven't got that little "twist"
until now, so it sounds like it's the 2nd. backup I'm missing. When I think
about it I should have thought about that, but from BOL I had got the
impression that SQL2000 did it "behind the scenes".
After I've done the backup once more, it works like a charm....:-).
Thanks for your inputs everybody...
Regards
Steen
Tibor Karaszi wrote:[vbcol=seagreen]
> The file can only be shrunk from the end towards the beginning. You
> need to get 0 at the end. backup the log to see if that makes you
> have 0 at the end. If not, do some dummy modifications so the head of
> the log moves toward the beginning (in 2000, the shrink command
> should do this for you). Then backup again. When you have 0 at the
> end, you can shrink it.
>
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:ulusXfkSFHA.3296@.TK2MSFTNGP15.phx.gbl...

How do I export my database from SQL Server Management Studio Express

Hi

I am using SQL Server Management Studio Express to make my databases but I noticed if I make my database in Visual studios and go new Item and make a new database I see it in app_data folder and server express tab and if I make it in SQL Server Management Studio Express I only see it in the server explorer.

So if I have to move my files to another computer how do I move my database easly with SQL Server Management Studio Express? Since when you make it with the visual studio the file gets stored with all the other files of your project so if you move it all to another computer you prob won't run into a problem.

So how do I make it that so I can do everything in SQL Server Management Studio Express(since I like working in it) then export it into a file that I can then go into my app_data folder and add it is an exist item?

Thanks

Hi chobo2,

Generally speaking, when you create a new database through Management Studio, the database file will be stored at: %yoursqlinstallationpath%MSSQL.1\MSSQL\Data. If you want to access it through your Visual Studio you must first connect to it(server exploer--> new connection).

And if you create a new database through Visual Studio, the database file will be stored in you application App_Data folder.You can access to it directly through both Visual Studio and Management Studio.

As to export a database file to new location(or migrate databae), as far as i know, you have 2 options:

1: Detach and Attach:

Detach the database from original database Server and copy the physical database files(*.mdf and *.ldf) to the new location.At the new server side, you put the physical database files to a certain place(generally speaking it's the default sql server database folder), and attach to the database again;

2:Use Backup and Restore:

In your original database server you can make a backup of your database file and store it to a certain place(*.bak).Take the *.bak file to the new server and in the new database server, restore that database.

You can implement the above 2 methods in sql2000, sql express, or sql 2005. However, detailed operations could be different if you use different verstions of sql server.

Hope my suggestion helps

How do I exclude carriage returns as part of a IS NULL exclusion clause...

Hi

I have stupid users... who doesn't?! They have entered carriage returns as a whole value in some fields, that is, the field contains nothing more than a carriage return.

I really need to treat these cases as nulls and have successfully removed whole fields of nothing but spaces by using the LTRIM(RTRIM()) construct. Unfortunately, this doesn't deal with carraige returns. Even CASTing and CONVERTing to varchar and then using LTRIM(RTRIM()) doesn't work.

Does anyone know how I can elegantly get around this problem other than my best guess below:

Best guess pseudo code:
IF count of field is greater than 1 THEN probably a full sentence so ignore ELSE SUBSTRING first character and if CHAR(10, etc) then treat as NULL.

Here's some code that reconstructs the problem:
select datalength(char(13)) CarriageReturnVisible
, datalength(ltrim(rtrim(cast(char(13) as varchar)))) [This Don't Work]

Cheers - AndyThis is a really slippery slope, but if you are dealing with 8 bit ASCII, you could use:LIKE '%[!-~]%' to see if there are any printable characters (this expression ignores whitespace).

-PatP|||Very interesting and very cool (speaking purely as a geek!).

I got it to work by doing this:

and field like '%[a-z0-9]%'

This expression only shows fields that contain letters and/or numbers and excludes stupid entries like periods, comma's, carraige returns...

Thanks very much for your help!

Andy|||i think that an enter is not just an carriage return but is also a line feed
so isnt is char(10) or Char(13) at the same time

i seem to remember something in 3g programming that looks like vbCRLF etc...

just askin'|||i think that an enter is not just an carriage return but is also a line feed
so isnt is char(10) or Char(13) at the same time

i seem to remember something in 3g programming that looks like vbCRLF etc...

just askin'You are quite correct, systems that are derived from MS-DOS see 0x0d0a as a line end. The code that I proposed dodges that bullet altogether though, along with several others, and seems to have solved the problem better than I'd expected!

-PatP