How to remove not existed SQL Link Server

Hi all
In my SQL Server 2000, i've created linked server to
MachineA. I didn't remove the link before I reinstall
MachineA to become MachineB. Now, I'm failed to delete
MachineA link server from the Enterprise Manager. How can
I fix it? Thanks.You should be able to run the below in Query Analyzer
exec sp_dropserver 'MachineA','droplogins'
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Jon" <klliu98@.yahoo.com> wrote in message
news:075201c3d5aa$7df68390$a401280a@.phx.gbl...
> Hi all
> In my SQL Server 2000, i've created linked server to
> MachineA. I didn't remove the link before I reinstall
> MachineA to become MachineB. Now, I'm failed to delete
> MachineA link server from the Enterprise Manager. How can
> I fix it? Thanks.
>|||You should be able to use the sp_dropserver procedure to do this. It is
documented in Books on Line.
Rand
This posting is provided "as is" with no warranties and confers no rights.sql

How to remove DB

How to remove a DB ?
CREATED DB using .NET IDE. But not able to remove..
can you help on this regard..
thanks
DROP DATABASE databasename
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Barath" <anonymous@.discussions.microsoft.com> wrote in message
news:5E12F386-E253-40BC-8D03-D4AB47320F1D@.microsoft.com...
> How to remove a DB ?
> CREATED DB using .NET IDE. But not able to remove..
> can you help on this regard..
> thanks

How to refresh Full Text Indexes?

Hi All,

i am using full text search in stored procedure for my web application search-engine.

how do i refresh my fulltext indexes which i created using unique indexes?

as i can see two options tart full population and Start Incremental population in full-text index on right click of table.

on click of anyone its showing successful.

can anyone please let me know what is the standard way to refresh full text indexes periodically in terms of best performance? i found, when i run query first time with contains keyword , it takes much time.

what enterprise settings required to increase performance?

Thanks in advance.

When you start a population of a fulltext catalog, it reports successful but that only means its started successfully. The population can take a while depending on size of columns, number of rows etc.

Depending on the size of the index and how long it takes to do a full population, you may want to do run regular incremental populations which will only update the records that have been added/changed since the last full population. Population is quite a resource intensive process so be aware of what effect it has on your application. Also remember you'll need to add a timestamp field to any tables which you want to do incremental populations on.

If you need to realtime updates to happen, look into enabling change tracking/background update which will keep your index in sync with the data. Otherwise, new/changed rows won't be reflected in your fulltext index until you do another population.

As with any index, as data gets added and removed the index becomes fragmented so i'd look to schedule a rebuild of the catalog periodically (how frequent is up to you).

The first query may have been slow as the population may still have been in progress. Look at FULLTEXTCATALOGPROPERTY to check the size of the index and the current population status.

HTH!

|||

Hello,

thanks a lot for the reply.

can you please verify the way how i created Index?

create fulltext catalog MyFullTextCatalog

create unique index ui_AssetID on EMAM_Assets(ID)

create fulltext index on EMAM_Assets(title,asset_desc) KEY INDEX ui_AssetID on MyFullTextCatalog
with CHANGE_TRACKING AUTO

i created full text index as mentioned above. i want to perform search on title and asset_desc.

I created one schedule (daily) on table -> full text properties to populate full text index. I didnt get your point about timestamp. where do i need to add and how to use it? where will it be helpful?

second, i click on Full text Catalog property, its showing error: Property FullTextIndexSize is not available for FulltextCatalog. This property may not exist or may not be retrieved. once i click on OK on this error alert, its opening Full Text Catalog Property. Is it something wrong?

Please guide me in set up Administration configuration for Full Text Index. I am working on Search Engine and Query performance is very much important. i having 5 tables on which i need to perform search.

|||

You need to add a new column to the table you are running incremental populations on with a datatype of TIMESTAMP, the value of which is controlled by SQL Server to keep track of changes to records in the table.

With the commands you have used, SQL Server should populate the catalog as soon as the index is created. Rather than using the GUI to check the property, use FULLTEXTCATALOGPROPERTY in a query window (see Books Online for more details). The error you get may be related to the GUI rather than your full text catalog.

Have a look at the following link which has some great tips on Full Text Searching:

http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/ftslesld.mspx

HTH!

|||

Hi,

thanks for valuable input.

I created full text index with Change Track Automatically.

As i understand, in that case i need not to worry about full text population. is that right?

Full text index refresh will be done automatically. right?

let me know if it is required in this case also.

i already removed schedule for full text index population.

now i am facing on big problem is that: Query taking around 1 min while executing first time.

after that if u will be sit ideal for around 20 min then once again query will take more time.

instant execution followed by first one will result very fast.

I will check for cache memory and I/O for that. i think it could be reason.

Please let me know if you have any idea.

Thanks.

|||

You're right- once you've enabled change tracking a Full Population should happen automatically and the index will be maintained by sql server.

Make sure that the initial full population of the catalog has completed before running a query as it will be quite a resource intensive process and may be the cause of your slow query,

Good luck!

How to refresh Full Text Indexes?

Hi All,

i am using full text search in stored procedure for my web application search-engine.

how do i refresh my fulltext indexes which i created using unique indexes?

as i can see two options tart full population and Start Incremental population in full-text index on right click of table.

on click of anyone its showing successful.

can anyone please let me know what is the standard way to refresh full text indexes periodically in terms of best performance? i found, when i run query first time with contains keyword , it takes much time.

what enterprise settings required to increase performance?

Thanks in advance.

When you start a population of a fulltext catalog, it reports successful but that only means its started successfully. The population can take a while depending on size of columns, number of rows etc.

Depending on the size of the index and how long it takes to do a full population, you may want to do run regular incremental populations which will only update the records that have been added/changed since the last full population. Population is quite a resource intensive process so be aware of what effect it has on your application. Also remember you'll need to add a timestamp field to any tables which you want to do incremental populations on.

If you need to realtime updates to happen, look into enabling change tracking/background update which will keep your index in sync with the data. Otherwise, new/changed rows won't be reflected in your fulltext index until you do another population.

As with any index, as data gets added and removed the index becomes fragmented so i'd look to schedule a rebuild of the catalog periodically (how frequent is up to you).

The first query may have been slow as the population may still have been in progress. Look at FULLTEXTCATALOGPROPERTY to check the size of the index and the current population status.

HTH!

|||

Hello,

thanks a lot for the reply.

can you please verify the way how i created Index?

create fulltext catalog MyFullTextCatalog

create unique index ui_AssetID on EMAM_Assets(ID)

create fulltext index on EMAM_Assets(title,asset_desc) KEY INDEX ui_AssetID on MyFullTextCatalog
with CHANGE_TRACKING AUTO

i created full text index as mentioned above. i want to perform search on title and asset_desc.

I created one schedule (daily) on table -> full text properties to populate full text index. I didnt get your point about timestamp. where do i need to add and how to use it? where will it be helpful?

second, i click on Full text Catalog property, its showing error: Property FullTextIndexSize is not available for FulltextCatalog. This property may not exist or may not be retrieved. once i click on OK on this error alert, its opening Full Text Catalog Property. Is it something wrong?

Please guide me in set up Administration configuration for Full Text Index. I am working on Search Engine and Query performance is very much important. i having 5 tables on which i need to perform search.

|||

You need to add a new column to the table you are running incremental populations on with a datatype of TIMESTAMP, the value of which is controlled by SQL Server to keep track of changes to records in the table.

With the commands you have used, SQL Server should populate the catalog as soon as the index is created. Rather than using the GUI to check the property, use FULLTEXTCATALOGPROPERTY in a query window (see Books Online for more details). The error you get may be related to the GUI rather than your full text catalog.

Have a look at the following link which has some great tips on Full Text Searching:

http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/ftslesld.mspx

HTH!

|||

Hi,

thanks for valuable input.

I created full text index with Change Track Automatically.

As i understand, in that case i need not to worry about full text population. is that right?

Full text index refresh will be done automatically. right?

let me know if it is required in this case also.

i already removed schedule for full text index population.

now i am facing on big problem is that: Query taking around 1 min while executing first time.

after that if u will be sit ideal for around 20 min then once again query will take more time.

instant execution followed by first one will result very fast.

I will check for cache memory and I/O for that. i think it could be reason.

Please let me know if you have any idea.

Thanks.

|||

You're right- once you've enabled change tracking a Full Population should happen automatically and the index will be maintained by sql server.

Make sure that the initial full population of the catalog has completed before running a query as it will be quite a resource intensive process and may be the cause of your slow query,

Good luck!

How to refresh Access Table from SSIS package?

Hi all,

I have created an SSIS package to export rows of data from SQL to Access using SSIS package. The package is executed from asp.net web application. Below is what i want to achieve:

-User enters a date range

-SSIS package will export data between the date range from SQL to Access database.

-When user enter another date range, I want to clear the contents of the Access database. (Im using Execute Sql Task-- Delete tablename)

The problem is that when I look at the table after the second user request, the fields will show #deleted. Only after i click refresh will the new data appear. How can I make the data appear without manually refreshing the Access table.

Thks alot.

Can't you just drop off the access table and re-create it upon every user request ?

|||

Hi,

dropping the table means that I will need to create a the table again at the OLE DB destination. However I am unable to do that. I tried to use SQL command data access mode to create table. However, I received an error "CREATE TABLE SQL is not supported".

|||

Hi,

if it is not possible to refresh the Access table, can any expert here advise me on how to create a table when executing the package? The only way to go now is to drop the table and create a new table to solve my problem, but i am facing this problem of creating table.

Thks

how to reference/add a custom assembly in a report?

I created a custom assembly in VB2003 -- testClassLib. I gave it a namespace
of testNameSpace, class name is testLib with one public function that just
adds 10 to a number argument
NameSpace testNameSpace
Public Class testLib
Public addNum(n As Integer) As Integer
Return n + 10
End Class
End NameSpace
I tested this assembly in a VB2003 app, and it works fine.
In RS2003 I go to Report Properties to References and browse for my custom
assembly which would be testClassLib.dll. I select that in the Assembly name
box which will now display:
testClassLib, Version=1.0.2511.22986, Culture=neutral, PublicKeyToken=null
and then select testClassLib.testNameSpace.testLib and then use an
instance name of test1. This does not work. So I modified the classname to
testNameSpace.testLib -- still no go when I try to rebuild the report. When
I remove the reference and rebuild the report - it rebuilds fine without any
errors.
What do I need to do to add this custom assembly to my report? How to do
this?
Thanks,
RichIf you didn't already do so, you need to move the .dll to the following
directory for VS to recognize it:
Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies
For sql server to recognize it (once you deploy the report) you need to
add it to the following directory on the report server:
Program Files\Microsoft SQL Server\MSSQL.3\Reporting
Services\ReportServer\bin
Hope that helps.
On Nov 16, 4:10 pm, Rich <R...@.discussions.microsoft.com> wrote:
> I created a custom assembly in VB2003 -- testClassLib. I gave it a namespace
> of testNameSpace, class name is testLib with one public function that just
> adds 10 to a number argument
> NameSpace testNameSpace
> Public Class testLib
> Public addNum(n As Integer) As Integer
> Return n + 10
> End Class
> End NameSpace
> I tested this assembly in a VB2003 app, and it works fine.
> In RS2003 I go to Report Properties to References and browse for my custom
> assembly which would be testClassLib.dll. I select that in the Assembly name
> box which will now display:
> testClassLib, Version=1.0.2511.22986, Culture=neutral, PublicKeyToken=null
> and then select testClassLib.testNameSpace.testLib and then use an
> instance name of test1. This does not work. So I modified the classname to
> testNameSpace.testLib -- still no go when I try to rebuild the report. When
> I remove the reference and rebuild the report - it rebuilds fine without any
> errors.
> What do I need to do to add this custom assembly to my report? How to do
> this?
> Thanks,
> Rich|||Thank you very much. Yes, I pulled out my RS book, and it said the same
thing. But on the dev workstation I actually copied the dll to
C:\Program Files\Microsoft Sql Server\80\Tools\Report Designer
That is where I dropped the dll and where I reference it from the RS
designer, and now the desinger can see it and use it.
But the book was consistent with you on where you place the dll on the
server.
Thanks again for your reply.
Rich
"chris.teter@.gmail.com" wrote:
> If you didn't already do so, you need to move the .dll to the following
> directory for VS to recognize it:
> Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies
> For sql server to recognize it (once you deploy the report) you need to
> add it to the following directory on the report server:
> Program Files\Microsoft SQL Server\MSSQL.3\Reporting
> Services\ReportServer\bin
> Hope that helps.
> On Nov 16, 4:10 pm, Rich <R...@.discussions.microsoft.com> wrote:
> > I created a custom assembly in VB2003 -- testClassLib. I gave it a namespace
> > of testNameSpace, class name is testLib with one public function that just
> > adds 10 to a number argument
> >
> > NameSpace testNameSpace
> > Public Class testLib
> > Public addNum(n As Integer) As Integer
> > Return n + 10
> > End Class
> > End NameSpace
> >
> > I tested this assembly in a VB2003 app, and it works fine.
> >
> > In RS2003 I go to Report Properties to References and browse for my custom
> > assembly which would be testClassLib.dll. I select that in the Assembly name
> > box which will now display:
> >
> > testClassLib, Version=1.0.2511.22986, Culture=neutral, PublicKeyToken=null
> >
> > and then select testClassLib.testNameSpace.testLib and then use an
> > instance name of test1. This does not work. So I modified the classname to
> > testNameSpace.testLib -- still no go when I try to rebuild the report. When
> > I remove the reference and rebuild the report - it rebuilds fine without any
> > errors.
> >
> > What do I need to do to add this custom assembly to my report? How to do
> > this?
> >
> > Thanks,
> > Rich
>

How to reference a dataset passed to script

Hi, I have a dataset created via the Execute SQL Statement control and wish to pass it into a script control that within the vb.net script I wish to perform different functions to it.

The main goal is to take the dataset loop through it and record values into an array then take that array and loop through that so I can send an email containing the information within the array.

Can anyone help?

If inside a script component the common method is to use row

So you can do row.xxx where xxx is the field name... then using row.next row etc you can loop and such.

|||Ok, how do we reference the dataset? I appreciate your answer Dave but its very vague.

So I have the execute SQL statement create a dataset named "dataset1" using an ado.net object type.

Then within the script component how to we initialize, and work with "dataset1"? I wish to loop through "dataset1" and perform various functions on it. Sure the row access sounds great for the looping part but what of the other parts?|||Use a foreach loop to open that ADO recordset and loop through it. There are plenty of examples in this forum.|||I know that method and that won't work for what i'm trying to do, or at least i don't think so.

All I want to know is how to reference a dataset from an execute sql statement within a script component. I wish to reference this dataset within the script component and loop through and create an array with specific information. Then I want to use the array as loop through that to create a body of an email. So PLEASE answer the question of ->>>>>

How do I reference a dataset after it being populated via an Execute SQL Statement?

<<<<<--
I tried
dim results as dataset
dim reader as datatablereader
reader = results.tables(0).createdatareaders
do while reader.read
execute statements
loop

I get the error that reader is referenced to no table.|||

I misunderstood your initial question, I thought you needed basic access. I was thinking you used the execute sql command component to create the data set. So if you execute the sql, and then have the output of that component connect to a script component you can just use the Row call I mentioned, because it automatically ties to the "input" dataset from the sql execute component.

|||Here is my code. I have OBJ_NULL_SET as the result set object from the execute sql components output.

Public Sub Main()
'Assume success

Try
Dim results As DataSet

results = CType(ReadVariable("OBJ_NULL_SET"), DataSet)
For Each tbl As DataTable In results.Tables
System.Windows.Forms.MessageBox.Show(tbl.TableName)
Next

Catch ex As Exception
Dts.Events.FireError(-1, "N/A", ex.ToString(), "", 0)
End Try
Dts.TaskResult = Dts.Results.Success

End Sub

Private Function ReadVariable(ByVal varName As String) As Object
Dim result As Object

Try
Dim vars As Variables
Dts.VariableDispenser.LockForRead(varName)
Dts.VariableDispenser.GetVariables(vars)
Try
result = vars(varName).Value
Catch ex As Exception
Throw ex
Finally
vars.Unlock()
End Try
Catch ex As Exception
Throw ex
End Try

Return result
End Function

When the msgbox pops up it says Table with nothing else. It does not display the name of the dataset so i'm sure that i've done something wrong where the dataset is not being referenced properly.|||

You are already referencing the dataset by iterating through the DataTables. The first table's name so happens to be "Table", and that's what the message box call shows.

So, for the only DataTable of the DataSet, iterate through the rows using standard ADO.NET. For example, call this function right after you're first MsgBox() call, and it will display the column names and stringify the data. You're refencing the data from the Execute SQL task at this point, and what is done beyond that is up to your imagination.

Private Sub DisplayTable(ByVal tbl As DataTable)

Dim dr As DataRow, dc As DataColumn

Dim sb As System.Text.StringBuilder = New System.Text.StringBuilder()

For colNum As Integer = 0 To tbl.Columns.Count - 1

dc = tbl.Columns(colNum)

sb.Append(dc.ColumnName).Append(",")

Next

Dts.Events.FireInformation(1, "Columns", sb.ToString(), "", 0, True)

For rowNum As Integer = 0 To tbl.Rows.Count - 1

sb.Remove(0, sb.Length)

dr = tbl.Rows(rowNum)

For itemOffset As Integer = 0 To dr.ItemArray.Length - 1

sb.Append(dr.ItemArray(itemOffset)).Append(",")

Next

Dts.Events.FireInformation(1, "Data", sb.ToString(), "", 0, True)

Next

End Sub

sql

How to reduce the transaction log file

Hi all,

I know this topic has been discussed in the past, but I still don't quite get it. So please be patient with me

database size created with automatically growth of 10% with unrestricted file growth.

database size = 5 Gb used 4.5 GB (taskpad)
transaction log=8GB used 54MB (taskpad) 7.5 GB free
database run in FULL mode

full backup nightly, transaction log backup every 30 min

What should I do to free up the space that are not used in the transaction log.

Thanks for your help.DBCC SHRINKFILE
( { file_name | file_id }
{ [ , target_size ]
| [ , { EMPTYFILE | NOTRUNCATE | TRUNCATEONLY } ]
}
)|||How big is your hard drive?

If it's like 90GB...don't worry about it...

I'd be more worried though about unrestricted growth...

but you maint plan seems pretty good, and 7 gb for a tranny I would doubt would blow out...|||Doesn't your half hourly transaction log backup clear the space automatically?|||Originally posted by suresh_m_kumar
Doesn't your half hourly transaction log backup clear the space automatically?

No. that is why I don't understand|||The tranny backup every 30 min will truncate the log, but transaction log file (*ldf) is not automatically shrunk witht he truncation - see the post above with the syntax for DBCC shrink file - once you do this, the 30 min backups should keep the log to a more manageable size.|||Originally posted by joejcheng
DBCC SHRINKFILE
( { file_name | file_id }
{ [ , target_size ]
| [ , { EMPTYFILE | NOTRUNCATE | TRUNCATEONLY } ]
}
)

What option should I take : Truncateonly ??
[ , { EMPTYFILE | NOTRUNCATE | TRUNCATEONLY }|||You will want to use truncateonly I belive, so the freed space will be allocated back to the OS. Read this for an explanation of the options, or use the BOL:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_dbcc_8b51.asp

How to reduce database file size.

We have a database which was created with an initial file size of 10 gig. Currently it is only using 2 gigs.

We have developers that want to have a copy of the database on their desktops, but do not have 10 gigs free space.

What is the best way to get them a copy of the database while reducing the footprint?I'm assuming you don't want to do DBCC SHRINKDATABASE and DBCC SHRINFILE?|||I would go for generating a script of all the objects, creating a realistic sized DB on the laptop, then using DTS or some such to bring the data over.

It is a bit of a pain when you are dealing with a lot of tables. Especially since Enterprise Manager tends to do things alphabetically, instead of the way you have your foreign keys set up.|||Yeah, we could create a new database on the developers desktop and then DTS everything over. But the general question, I guess, is whether it is possible to shrink a database file below its initial size.

I believe SHRINKDATABASE and SHRINKFILE will only reduce down to the initial size of the file when it was created.|||That's true of SHRINKDATABASE; however, SHRINKFILE will let you shrink lower than the original size. You just need to specify the exact size. For example:

DBCC SHRINKFILE('Northwind_Data',300)|||One caution, though is that dbcc shrinkfile will take a bunch of time if it needs to move a lot of data around. This will also cause the transaction log to grow (which is kind of ironic, eh?). Start off with dbcc shrinkfile (1, truncateonly). This will chop off any of the file that is above the "high water mark" of the data, and is relatively quick.

EDIT: If memory serves, the only limit to the shrinkage is the size of the model database.|||How many objects are we talking about btw?sql

how to reduce a file (less then initial size)

Hi,
I have created a database with a 20gb initial size.
but I want to reduce this file
how to do this?
because the shrink command doesn't works.
or maybe I have missed an option?
thanks.
jerome.
DBCC SHRINKIFLE(<file id>, <target size>)
... Target size, of course, won't go beyond the size of the data in the file
(although you can set it as low as you want -- it won't give you an error
for going too low).
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
"Jj" <willgart@.AAAhotmailBBB.com> wrote in message
news:uxxcdeToFHA.572@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I have created a database with a 20gb initial size.
> but I want to reduce this file
> how to do this?
> because the shrink command doesn't works.
> or maybe I have missed an option?
>
> thanks.
> jerome.
|||I think I have typed to wrong command because now this works fine !!!!
I don't understand why before the shrink has never worked...
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uEiNdyToFHA.2904@.tk2msftngp13.phx.gbl...
> DBCC SHRINKIFLE(<file id>, <target size>)
> ... Target size, of course, won't go beyond the size of the data in the
> file
> (although you can set it as low as you want -- it won't give you an error
> for going too low).
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "Jj" <willgart@.AAAhotmailBBB.com> wrote in message
> news:uxxcdeToFHA.572@.TK2MSFTNGP15.phx.gbl...
>

how to reduce a file (less then initial size)

Hi,
I have created a database with a 20gb initial size.
but I want to reduce this file
how to do this?
because the shrink command doesn't works.
or maybe I have missed an option?
thanks.
jerome.DBCC SHRINKIFLE(<file id>, <target size> )
... Target size, of course, won't go beyond the size of the data in the fil
e
(although you can set it as low as you want -- it won't give you an error
for going too low).
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Jj" <willgart@.AAAhotmailBBB.com> wrote in message
news:uxxcdeToFHA.572@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I have created a database with a 20gb initial size.
> but I want to reduce this file
> how to do this?
> because the shrink command doesn't works.
> or maybe I have missed an option?
>
> thanks.
> jerome.|||I think I have typed to wrong command because now this works fine !!!!
I don't understand why before the shrink has never worked...
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uEiNdyToFHA.2904@.tk2msftngp13.phx.gbl...
> DBCC SHRINKIFLE(<file id>, <target size> )
> ... Target size, of course, won't go beyond the size of the data in the
> file
> (although you can set it as low as you want -- it won't give you an error
> for going too low).
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "Jj" <willgart@.AAAhotmailBBB.com> wrote in message
> news:uxxcdeToFHA.572@.TK2MSFTNGP15.phx.gbl...
>

how to reduce a file (less then initial size)

Hi,
I have created a database with a 20gb initial size.
but I want to reduce this file
how to do this?
because the shrink command doesn't works.
or maybe I have missed an option?
thanks.
jerome.DBCC SHRINKIFLE(<file id>, <target size>)
... Target size, of course, won't go beyond the size of the data in the file
(although you can set it as low as you want -- it won't give you an error
for going too low).
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Jéjé" <willgart@.AAAhotmailBBB.com> wrote in message
news:uxxcdeToFHA.572@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I have created a database with a 20gb initial size.
> but I want to reduce this file
> how to do this?
> because the shrink command doesn't works.
> or maybe I have missed an option?
>
> thanks.
> jerome.|||I think I have typed to wrong command because now this works fine !!!!
I don't understand why before the shrink has never worked...
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uEiNdyToFHA.2904@.tk2msftngp13.phx.gbl...
> DBCC SHRINKIFLE(<file id>, <target size>)
> ... Target size, of course, won't go beyond the size of the data in the
> file
> (although you can set it as low as you want -- it won't give you an error
> for going too low).
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "Jéjé" <willgart@.AAAhotmailBBB.com> wrote in message
> news:uxxcdeToFHA.572@.TK2MSFTNGP15.phx.gbl...
>> Hi,
>> I have created a database with a 20gb initial size.
>> but I want to reduce this file
>> how to do this?
>> because the shrink command doesn't works.
>> or maybe I have missed an option?
>>
>> thanks.
>> jerome.
>

How to redeploy reports?

Hello everyone,

I have created a selection of reports in VS and deployed them to my test machine. I would now like to deploy the project to the live machine, I cant install visual studio on this machine and the test machine cannot connect to live.

What is the best way to move my reports into the live environment?

Any help appreciated.

Kind regards
Taz

you need to change the reportserver path and deploy from your dev environment. (right click on ur report project - properties in VS)

probably you would have given http://localhost/reportserver

u need to change it to http://livemachine/reportserver.

and you can use the same method to deploy the reports. you can deploy to any reportserver after changing the name in that place.

|||

Hi,

thanks for the reply. is it possible todo this without my dev environment? The reports are part of a software package which I would like to deply through some installer if possible.

Is this possible?

Kind regards
Taz

|||

Hi Taz,

Russel Christopher shows a solution here:
http://blogs.msdn.com/bimusings/archive/2006/03/01/541599.aspx

cheers
Markus

|||

Fabulous,

exactly what I was looking for. You are a legend, thank you.

Taz

|||

I know there have been a couple of replies to your question. I would like to reccommend RSScripter. It works very well for deploying our sites. You can find it at http://www.sqldbatips.com/showarticle.asp?ID=62.

R

|||

Hi Ron,

i think the tool you linked to is much more like what I was after. I appreciate the link.

Kind regards
Taz

|||

My pleasure. You might want to look at the scripts it generates and clean them up. They have quite a bit of overhead. I hand-crafted ours (one login to SSRS, etc.) and they run much, much faster.

R

HOW TO RECOVER A DELETED DB

I NEED SOME SERIOUS HELP
I deleted a production database accidentally, because the person who created
named it test, so I figure that it was ok to deleted.
Well we don't have any type of backups for this Database.
I need to know if there a tool out there can retrieve these files from a
RAID 5.
Where do the files go when you right click in enterprise manager and delete
the DB?Where did the file go -it went 'poof' into the ether...
IF there has been no other writing activity to the RAID, you may find some
low level file recovery tools to help. Check with the RAID provider, and try
SysInternals.
There is nothing that SQL Server can do to help you recover the file.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Peter" <Vik> wrote in message
news:%233JoBiuxGHA.1300@.TK2MSFTNGP05.phx.gbl...
>I NEED SOME SERIOUS HELP
> I deleted a production database accidentally, because the person who
> created named it test, so I figure that it was ok to deleted.
> Well we don't have any type of backups for this Database.
> I need to know if there a tool out there can retrieve these files from a
> RAID 5.
>
> Where do the files go when you right click in enterprise manager and
> delete the DB?
>

How to record if a Linked Server is offline?

I created the below script to track my linked servers. I set it up that it
would record a record for each server along with all its corresponding
databases. Unfortunately, I have not been able to successfully track a
server which is offline.
Currently, I am using cmdshell to ping linked servers and return any that
have a 'timed out' string. I feel that there must be a better way to see if
a linked server is offline via TSQL.
Thanks in Advance.
Drake
DECLARE @.DatabaseName VARCHAR(50)
,@.DatabaseNameString VARCHAR(200)
,@.DBString VARCHAR(200)
,@.Server VARCHAR(100)
,@.SQLcmd NVARCHAR(500)
,@.Error int
,@.Errormsg varchar(100)
--BEGIN TRAN
--SET NOCOUNT ON
DECLARE SERVERCRSOR CURSOR FOR
select srvname from sysservers order by srvname
OPEN SERVERCRSOR
FETCH NEXT FROM SERVERCRSOR INTO @.Server
WHILE @.@.FETCH_STATUS = 0
BEGIN
BEGIN
set @.SQLcmd = 'IF EXISTS(select name from [' + @.Server +
'].master.dbo.sysdatabases)
DECLARE STATUSCRSOR CURSOR FOR SELECT Name FROM [' + @.Server +
'].master.dbo.sysdatabases'
EXEC sp_executesql @.SQLcmd ,N'@.Server varchar(255)' ,@.Server
select @.Error = @.@.Error
if @.Error <> 0
begin
set @.Errormsg = 'Linked SQL Server does not exist or access denied.'
Insert into SQLMONITOR.dbo.tblSQLServerStatus (szLinkedServer_nm,
szDatabases_nm, dtStatusdate_dt)
Select @.Server, '(' + @.Errormsg + ')', getdate() DateStatus
raiserror ('test',1,1)
end
OPEN STATUSCRSOR
FETCH NEXT FROM STATUSCRSOR INTO @.DatabaseName
Select @.DBString = @.DatabaseName
WHILE @.@.FETCH_STATUS = 0
BEGIN
Select @.DBString = @.DBString + ',' + @.DatabaseName
FETCH NEXT FROM STATUSCRSOR INTO @.DatabaseName
Select @.DatabaseNameString = @.DBString
END
Insert into SQLMONITOR.dbo.tblSQLServerStatus (szLinkedServer_nm,
szDatabases_nm, dtStatusdate_dt)
Select @.Server, '(' + @.DatabaseNameString + ')', getdate() DateStatus
END
FETCH NEXT FROM SERVERCRSOR INTO @.Server
CLOSE STATUSCRSOR
DEALLOCATE STATUSCRSOR
END
CLOSE SERVERCRSOR
DEALLOCATE SERVERCRSOR
--COMMIT
GOYou could try to do a select count(*) from <<linked
server>>.pubs.dbo.authors and see if anything comes back.|||I need to clarify my above response - I have a job that contains 3
steps. Step #1 contains the SELECT statement against the linked
server. Step #1, on success, goes to step #2 which handles some
processing on the remote server. Step #1, on failture, goes to step
#3 which informs me that the linked server is not available.
Make sure that step #2 skips step #3.

How to record if a Linked Server is offline?

I created the below script to track my linked servers. I set it up that it
would record a record for each server along with all its corresponding
databases. Unfortunately, I have not been able to successfully track a
server which is offline.
Currently, I am using cmdshell to ping linked servers and return any that
have a 'timed out' string. I feel that there must be a better way to see if
a linked server is offline via TSQL.
Thanks in Advance.
Drake

DECLARE @.DatabaseName VARCHAR(50)
,@.DatabaseNameString VARCHAR(200)
,@.DBString VARCHAR(200)
,@.Server VARCHAR(100)
,@.SQLcmd NVARCHAR(500)
,@.Error int
,@.Errormsg varchar(100)
--BEGIN TRAN
--SET NOCOUNT ON
DECLARE SERVERCRSOR CURSOR FOR
select srvname from sysservers order by srvname
OPEN SERVERCRSOR
FETCH NEXT FROM SERVERCRSOR INTO @.Server
WHILE @.@.FETCH_STATUS = 0
BEGIN
BEGIN
set @.SQLcmd = 'IF EXISTS(select name from [' + @.Server +
'].master.dbo.sysdatabases)
DECLARE STATUSCRSOR CURSOR FOR SELECT Name FROM [' + @.Server +
'].master.dbo.sysdatabases'
EXEC sp_executesql @.SQLcmd ,N'@.Server varchar(255)' ,@.Server
select @.Error = @.@.Error
if @.Error <> 0
begin
set @.Errormsg = 'Linked SQL Server does not exist or access denied.'
Insert into SQLMONITOR.dbo.tblSQLServerStatus (szLinkedServer_nm,
szDatabases_nm, dtStatusdate_dt)
Select @.Server, '(' + @.Errormsg + ')', getdate() DateStatus
raiserror ('test',1,1)
end
OPEN STATUSCRSOR
FETCH NEXT FROM STATUSCRSOR INTO @.DatabaseName
Select @.DBString = @.DatabaseName
WHILE @.@.FETCH_STATUS = 0
BEGIN
Select @.DBString = @.DBString + ',' + @.DatabaseName
FETCH NEXT FROM STATUSCRSOR INTO @.DatabaseName
Select @.DatabaseNameString = @.DBString
END
Insert into SQLMONITOR.dbo.tblSQLServerStatus (szLinkedServer_nm,
szDatabases_nm, dtStatusdate_dt)
Select @.Server, '(' + @.DatabaseNameString + ')', getdate() DateStatus
END
FETCH NEXT FROM SERVERCRSOR INTO @.Server
CLOSE STATUSCRSOR
DEALLOCATE STATUSCRSOR
END
CLOSE SERVERCRSOR
DEALLOCATE SERVERCRSOR
--COMMIT
GO

You could try to do a select count(*) from <<linked
server>>.pubs.dbo.authors and see if anything comes back.
|||I need to clarify my above response - I have a job that contains 3
steps. Step #1 contains the SELECT statement against the linked
server. Step #1, on success, goes to step #2 which handles some
processing on the remote server. Step #1, on failture, goes to step
#3 which informs me that the linked server is not available.
Make sure that step #2 skips step #3.

How to read block of rows from database tables

have created a Database Application in Java and display all the records in tabular format of one Table. This table have Millions of Rows, If I run Select * from Table, then my Machine not responding, so Now I wants to add paging of 1000 rows at one time.

Is there are any option/query to read block of rows at one time and then query again for next page ?

i.e In MYSQL have LIMIT clause with Select Statement

Please let me know..

Database : SQL Server 2000/2005,

Thanks in Advance
Laxmilal

Whenever you use Select statement you must use WHERE condition to limit the rows.

Eg.

Select col1,col2.... From Tablename WHERE someid between 1 and 1000

Madhu

|||

You 'Machine not responding' is most likely due to waiting for millions of rows to come back from the server.

You really need to limit the quantity of data that you are requesting from SQL Server. Without WHERE clause criteria that enforces limits, you are unnecessarily wasting bandwidth (and time) as more data than you need for the operation at hand is being transported to the client application.

With SQL 2005, you may wish to explore using the new ROW_NUMBER() function to assist in retrieving specific 'blocks' of rows.

Referring to Books Online, Topic: ROW_NUMBER()

Example:

USE AdventureWorks; GO

WITH OrderedOrders AS ( SELECT SalesOrderID, OrderDate, ROW_NUMBER() OVER (ORDER BY OrderDate) AS 'RowNumber' FROM Sales.SalesOrderHeader ) SELECT * FROM OrderedOrders WHERE RowNumber BETWEEN 50 AND 60;(You should be able to adapt this concept for your needs.)