How to remove unwanted characters in SQL?

Hi all,

I have a stored procedure that generates the following SQL WHERE clause

UserName LIKE 'Adi234%' AND Fname LIKE 'David%' AND LName LIKE 'Justin%' AND

It is good except that it i can not remove the last AND which is not neccessary at the end of the clause.

I want to remove the last AND that come up at the end, my code places AND after each data field(UserName, Fname, Lname) .

Thanks

a quick and dirty way: add

1=1

after the query (which always is good

|||

In your stored procedure, you can use a Substring to remove the end AND:

Assume @.strSQL is the SQL statement string you are returning now. Add this line:

SET @.strSQL=SUBSTRING(@.strSQL, 0,LEN(@.strSQL)-3)

|||

Thanks Limno, that works great

How to remove the commented notes in stored procedure?

Hello, everyone:
There are some commented notes in the stored procedure from clients. Is there any way to remove these notes automatically, I mean using scripts etc.
Thanks
ZYTWhat ever for? Are they rude comments? Are they misleading? Are they lies? Do they disparage members of your immediate family?

Comments are there for a purpose. They help future DBAs understand what the procedure is doing. They in no way hinder the performance of the code. Leave them be.|||We want to check some special words in stored procedures. Commented notes will make confusion.|||Then perhaps one solution would be to script your database, write a little MS Word (or other editor) macro to remove the comments, and then do your searching. Maybe not a 100 easy solution, but one that would preserve the comments in the actual DB's stored procedures.

Creating such a macro would be a fairly trivial task in most editors.|||Well, the problem is that you are going to need to identify both single-line comments (with '--' characters) and block comments (bracketed by '/*' and '*/'). Rotsa-Ruck getting it to work reliably, though.|||Problem? What problem? I have a UltraEdit script at home that does the /* */ ('C' programming anyone?) stuff...and would THINK that the -- stuff would be easy also (find "--", delete to EOL).

But as far as reliability...I would be concerned about that...for instance, now that I think about it, my UE macro might not work correctly for /* /* */ */ stuff...

And if it was me, I'd do the searching with the comments staying put. I would think considerably less hassle. You can weed out the comments from the output easier than pulling them from the procs, I would think.sql

how to remove reference of a table from all stored procs

Hello All
I am working on SQL SERVER 2000 DATABASE.
Is there any quick way to Drop the table say TABLE1 from the Database and
remove its reference from all stored procs(about 100 Stored Proc's)
Pls help me asap.
Regards,
EktaI'm afraid not. You need to work through your procedures and see which referenced the table and
adjust the proc code for each to your liking. You can get some help in finding which procedures
referencing your tables, for instance:
SELECT object_name(id) FROM syscomments WHERE text like '%procname%'
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"ekta" <enahar@.hotmail.com> wrote in message news:e02qRhY2HHA.5980@.TK2MSFTNGP04.phx.gbl...
> Hello All
> I am working on SQL SERVER 2000 DATABASE.
> Is there any quick way to Drop the table say TABLE1 from the Database and remove its reference
> from all stored procs(about 100 Stored Proc's)
> Pls help me asap.
> Regards,
> Ekta
>

how to remove reference of a table from all stored procs

Hello All
I am working on SQL SERVER 2000 DATABASE.
Is there any quick way to Drop the table say TABLE1 from the Database and
remove its reference from all stored procs(about 100 Stored Proc's)
Pls help me asap.
Regards,
EktaI'm afraid not. You need to work through your procedures and see which refer
enced the table and
adjust the proc code for each to your liking. You can get some help in findi
ng which procedures
referencing your tables, for instance:
SELECT object_name(id) FROM syscomments WHERE text like '%procname%'
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"ekta" <enahar@.hotmail.com> wrote in message news:e02qRhY2HHA.5980@.TK2MSFTNGP04.phx.gbl...[v
bcol=seagreen]
> Hello All
> I am working on SQL SERVER 2000 DATABASE.
> Is there any quick way to Drop the table say TABLE1 from the Database and
remove its reference
> from all stored procs(about 100 Stored Proc's)
> Pls help me asap.
> Regards,
> Ekta
>[/vbcol]

How to regenerate a stored proc (drop & create or alter procedure) ??

Hi all.
Cf my other news: How to change or alter a user defined data type?
I need to regenerate (not only "sp_recompile") a list of stored procedures.
With the "SQL Server Enterprise Manager" it's very easy: just open each
Stored proc and save it. But how to do that by transact SQL script '
Thanks.
Lilian.What purpose does "regenerating" these serve? There are 2 ways to do this
depending on what your after. One way is to use the DROP PROCEDURE xxx
command and then issue the CREATE PROCEDURE xxx ... command. The other is
to use the ALTER PROCEDURE xxx.... command. Drop will not keep any
existing permissions and ALTER will.
--
Andrew J. Kelly
SQL Server MVP
"Lilian Pigallio" <lpigallio@.nospam.com> wrote in message
news:u7Os%23CHQDHA.2228@.tk2msftngp13.phx.gbl...
> Hi all.
> Cf my other news: How to change or alter a user defined data type?
> I need to regenerate (not only "sp_recompile") a list of stored
procedures.
> With the "SQL Server Enterprise Manager" it's very easy: just open each
> Stored proc and save it. But how to do that by transact SQL script '
> Thanks.
>
> Lilian.
>sql

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 reference items from different database?

Hi all,

I have two databases DB_External and DB_Internal.

I am writing some stored procedures (in DB_Internal) that reads from
the tables in DB_External. I execute my stored procedures from
DB_Internal.

Everytime I want to read the tables in DB_External, I have to refer to
the table as DB_External.dbo.tableName.

Is there a better way of declaring the database DB_External up front
in the stored procedure so I don't have to pre-fix "DB_External.dbo."
in all the table names?

Thanks in advance,
June Moore."June Moore" <jungewum@.yahoo.com.au> wrote in message
news:e5dfaf21.0310100030.3e0067a5@.posting.google.c om...
> Hi all,
> I have two databases DB_External and DB_Internal.
> I am writing some stored procedures (in DB_Internal) that reads from
> the tables in DB_External. I execute my stored procedures from
> DB_Internal.
> Everytime I want to read the tables in DB_External, I have to refer to
> the table as DB_External.dbo.tableName.
> Is there a better way of declaring the database DB_External up front
> in the stored procedure so I don't have to pre-fix "DB_External.dbo."
> in all the table names?
> Thanks in advance,
> June Moore.

You always have to fully qualify the name - there's no way around that. And
it's good practice to include the dbo owner prefix, even in the 'local'
database.

If you're concerned about moving code between servers where the DB names may
be different, you could consider a pre-processor script to 'fill in' or
find/replace the DB names in a code template, then create the procedure.

Simonsql

How to refer from a stored procedure to a table in another databas

Hi,
I need to select data from a table in my stored procedure. The problem is
that the table I am interested in resides in another database. Hardcoding
fully qualified name is not an option. Database name where the table is -
must be a parameter of SP. Is there any other way than using dynamically
generated query string inside stored procedure? Generated query string is
difficult to maintain.
Any ideas?
Thanks in advance.Alexander Korol wrote:
> Hi,
> I need to select data from a table in my stored procedure. The
> problem is that the table I am interested in resides in another
> database. Hardcoding fully qualified name is not an option. Database
> name where the table is - must be a parameter of SP. Is there any
> other way than using dynamically generated query string inside stored
> procedure? Generated query string is difficult to maintain.
>
Two options that I can think of:
1.A series of IF statements, which I'm pretty sure would be the solution of
choice, given that you don't have more than 10 or so databases ...
2. Another option is to create a view that unions the tables including a
column for source database name (I think this is how partitioning is
implemented )
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

How to refer from a stored procedure to a table in another dat

I changed the view in my SP dynamically it helped. Thanks.Alexander Korol wrote:
> I changed the view in my SP dynamically it helped. Thanks.
Huh?
What view?
Oh! Are you saying you used dynamic sql?
Hopefully you are aware of and have taken precautions against sql injection
...
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Hello Bob,
I do not change or create databases dynamically I simply do not know
beforehand how many there is going to be databases, where they reside and ho
w
they are called. This is say maintanance stored procedure in a reporting
database (the one that collects data from many other databases) and YES I
care about SQL injection.
Thanks for your participation. Looks like you hate dynamically built sql. So
do I - but I am not the architect - just solving particular task :)
Moreover if you could advise any better architecture I will greately
appreciate. I have several production databases of similar structure and one
reporting database where data ready to be reported is kept. Data
preprocessing is so heavy that I can not query data directly from production
databases. So in reporting database SPs are to be run daily against differen
t
production databases and their number depends on a customer. That is why
neither IFs can be used neither fixed queried VIEWs. Customer wants to do as
less actions as possible.
Actually to avoid SQL injection i check that database name is a single word.
Could you advise any other solution?
Thanks again for your help.
"Bob Barrows [MVP]" wrote:

> Alexander Korol wrote:
> Huh?
> What view?
> Oh! Are you saying you used dynamic sql?
> Hopefully you are aware of and have taken precautions against sql injectio
n
> ...
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>

How to refer from a stored procedure to a table in another dat

"Bob Barrows [MVP]" wrote:

> Two options that I can think of:
> 1.A series of IF statements, which I'm pretty sure would be the solution o
f
> choice, given that you don't have more than 10 or so databases ...
> 2. Another option is to create a view that unions the tables including a
> column for source database name (I think this is how partitioning is
> implemented )
1. Could you describe in more details how first way will help me to pass a
database name as a parameter. Database name must not be hardcoded in any for
m
even not in several IFs.
2. I can not do anything beforehand. The procedure is guaranteed to be given
a database of certain structure. That is all.
Thanks for replyAlexander Korol wrote:
> "Bob Barrows [MVP]" wrote:
>
> 1. Could you describe in more details how first way will help me to
> pass a database name as a parameter. Database name must not be
> hardcoded in any form
Why not? You're not creating db's dynamically are you?

> even not in several IFs.
IF @.dbname = thisdb
select ... from thisdb..table
IF @.dbname = thatdb
select ... from thatdb..table
...
But if what you say is true, this method is ruled out.

> 2. I can not do anything beforehand. The procedure is guaranteed to
> be given a database of certain structure. That is all.
>
Then you are forced to use dynamic sql ... ugh!!
Somebody needs to rethink this architecture ...
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

How to recover transaction log from failed database ?.

Hi,
If data and log devices are stored on separate disks and
the disk with the data device crashes, is it then possible to backup (or
in some other was get) the transaction log from the log device so it can
be applied to the new database ?.
After the crash will database will be marked as "suspect" and it's not
possible to use the "backup log" command.
Let's say that one total backup was taken the previous night and the
last transaction log was taken 2 hours before the disk crash.
Best regards
Kjell Gunnarsson, Stockholm, Sweden.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
From BOL (Topic: How to Restore to the point of Failure)
How to restore to the point of failure (Transact-SQL)
To restore to the point of failure
1.. Execute the BACKUP LOG statement using the NO_TRUNCATE clause to back
up the currently active transaction log.
2.. Execute the RESTORE DATABASE statement using the NORECOVERY clause to
restore the database backup.
3.. Execute the RESTORE LOG statement using the NORECOVERY clause to apply
each transaction log backup.
4.. Execute the RESTORE LOG statement using the RECOVERY clause to apply
the transaction log backup created in Step 1.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Kjell Gunnarsson" <kjell.gunnarsson@.sungard.com> wrote in message
news:us4YvPcvEHA.3376@.TK2MSFTNGP12.phx.gbl...
> Hi,
> If data and log devices are stored on separate disks and
> the disk with the data device crashes, is it then possible to backup (or
> in some other was get) the transaction log from the log device so it can
> be applied to the new database ?.
> After the crash will database will be marked as "suspect" and it's not
> possible to use the "backup log" command.
> Let's say that one total backup was taken the previous night and the
> last transaction log was taken 2 hours before the disk crash.
> Best regards
> Kjell Gunnarsson, Stockholm, Sweden.
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||This scenario is what the NO_TRUNCATE option for the log backup command is for.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Kjell Gunnarsson" <kjell.gunnarsson@.sungard.com> wrote in message
news:us4YvPcvEHA.3376@.TK2MSFTNGP12.phx.gbl...
> Hi,
> If data and log devices are stored on separate disks and
> the disk with the data device crashes, is it then possible to backup (or
> in some other was get) the transaction log from the log device so it can
> be applied to the new database ?.
> After the crash will database will be marked as "suspect" and it's not
> possible to use the "backup log" command.
> Let's say that one total backup was taken the previous night and the
> last transaction log was taken 2 hours before the disk crash.
> Best regards
> Kjell Gunnarsson, Stockholm, Sweden.
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!

How to recover transaction log from failed database ?.

Hi,
If data and log devices are stored on separate disks and
the disk with the data device crashes, is it then possible to backup (or
in some other was get) the transaction log from the log device so it can
be applied to the new database ?.
After the crash will database will be marked as "suspect" and it's not
possible to use the "backup log" command.
Let's say that one total backup was taken the previous night and the
last transaction log was taken 2 hours before the disk crash.
Best regards
Kjell Gunnarsson, Stockholm, Sweden.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!From BOL (Topic: How to Restore to the point of Failure)
How to restore to the point of failure (Transact-SQL)
To restore to the point of failure
1.. Execute the BACKUP LOG statement using the NO_TRUNCATE clause to back
up the currently active transaction log.
2.. Execute the RESTORE DATABASE statement using the NORECOVERY clause to
restore the database backup.
3.. Execute the RESTORE LOG statement using the NORECOVERY clause to apply
each transaction log backup.
4.. Execute the RESTORE LOG statement using the RECOVERY clause to apply
the transaction log backup created in Step 1.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Kjell Gunnarsson" <kjell.gunnarsson@.sungard.com> wrote in message
news:us4YvPcvEHA.3376@.TK2MSFTNGP12.phx.gbl...
> Hi,
> If data and log devices are stored on separate disks and
> the disk with the data device crashes, is it then possible to backup (or
> in some other was get) the transaction log from the log device so it can
> be applied to the new database ?.
> After the crash will database will be marked as "suspect" and it's not
> possible to use the "backup log" command.
> Let's say that one total backup was taken the previous night and the
> last transaction log was taken 2 hours before the disk crash.
> Best regards
> Kjell Gunnarsson, Stockholm, Sweden.
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!|||This scenario is what the NO_TRUNCATE option for the log backup command is f
or.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Kjell Gunnarsson" <kjell.gunnarsson@.sungard.com> wrote in message
news:us4YvPcvEHA.3376@.TK2MSFTNGP12.phx.gbl...
> Hi,
> If data and log devices are stored on separate disks and
> the disk with the data device crashes, is it then possible to backup (or
> in some other was get) the transaction log from the log device so it can
> be applied to the new database ?.
> After the crash will database will be marked as "suspect" and it's not
> possible to use the "backup log" command.
> Let's say that one total backup was taken the previous night and the
> last transaction log was taken 2 hours before the disk crash.
> Best regards
> Kjell Gunnarsson, Stockholm, Sweden.
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!

How to recover transaction log from failed database ?.

Hi,
If data and log devices are stored on separate disks and
the disk with the data device crashes, is it then possible to backup (or
in some other was get) the transaction log from the log device so it can
be applied to the new database ?.
After the crash will database will be marked as "suspect" and it's not
possible to use the "backup log" command.
Let's say that one total backup was taken the previous night and the
last transaction log was taken 2 hours before the disk crash.
Best regards
Kjell Gunnarsson, Stockholm, Sweden.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!From BOL (Topic: How to Restore to the point of Failure)
How to restore to the point of failure (Transact-SQL)
To restore to the point of failure
1.. Execute the BACKUP LOG statement using the NO_TRUNCATE clause to back
up the currently active transaction log.
2.. Execute the RESTORE DATABASE statement using the NORECOVERY clause to
restore the database backup.
3.. Execute the RESTORE LOG statement using the NORECOVERY clause to apply
each transaction log backup.
4.. Execute the RESTORE LOG statement using the RECOVERY clause to apply
the transaction log backup created in Step 1.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Kjell Gunnarsson" <kjell.gunnarsson@.sungard.com> wrote in message
news:us4YvPcvEHA.3376@.TK2MSFTNGP12.phx.gbl...
> Hi,
> If data and log devices are stored on separate disks and
> the disk with the data device crashes, is it then possible to backup (or
> in some other was get) the transaction log from the log device so it can
> be applied to the new database ?.
> After the crash will database will be marked as "suspect" and it's not
> possible to use the "backup log" command.
> Let's say that one total backup was taken the previous night and the
> last transaction log was taken 2 hours before the disk crash.
> Best regards
> Kjell Gunnarsson, Stockholm, Sweden.
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||This scenario is what the NO_TRUNCATE option for the log backup command is for.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Kjell Gunnarsson" <kjell.gunnarsson@.sungard.com> wrote in message
news:us4YvPcvEHA.3376@.TK2MSFTNGP12.phx.gbl...
> Hi,
> If data and log devices are stored on separate disks and
> the disk with the data device crashes, is it then possible to backup (or
> in some other was get) the transaction log from the log device so it can
> be applied to the new database ?.
> After the crash will database will be marked as "suspect" and it's not
> possible to use the "backup log" command.
> Let's say that one total backup was taken the previous night and the
> last transaction log was taken 2 hours before the disk crash.
> Best regards
> Kjell Gunnarsson, Stockholm, Sweden.
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

How to recover Database from .MDF

Hi there,

I lost my .LDF file. Is there a possibility to recover Database from the MDF file with all data and Stored procedures?

Im using SQL Server 2000.

Please help....and sry if this is the wrong Forum for it...

Thx T. Gnewuch

Can you explain what you mean by losing the .ldf file?

Do you have your databases backed up regularly. If so, you can recover your database from the latest backup.

|||Try to use sp_attach_single_file_db, it can recreate the logfile if the database is not corrupt.

HTH, Jens Suessmeyer.

http://www.sqlserver205.de
|||thx , that solved my problem, everything is working fine now again ;)

How to read XML data from SQL data field?

Using RS2000

I have a field in a SQL DB which contains data stored as an XML string. Is there an easy way to parse this string into data without moving to SS2005?

I have builts a parser in SQL, but I would like to know if RS had a way to directly read this. If not, is there a method in RS2005 which handles this type of data?

The only way to really do this in RS2000 is to create your own custom data processing extension that encapsulates the code you have written to parse the Xml. There are quite a few examples online.

In RS2005, you can use the Xml Data Processing Extension, which is designed for retieving data from xml data sources. It retrieves the data from an xml document which is retireved from a web service, web page, or can use a document embedded in the Xml Query. This extension is very powerful, but can also be somewhat complex for advanced queries.

For more information: http://msdn2.microsoft.com/en-us/library/ms159741.aspx|||

you can write a function in report Code section that will take the xml string, load it into System.Xml.XmlDocument and parse it.

<?xml version="1.0" encoding="utf-8"?>

<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">

<DataSources>

<DataSource Name="DataSource1">

<ConnectionProperties>

<IntegratedSecurity>true</IntegratedSecurity>

<ConnectString>server=.</ConnectString>

<DataProvider>SQL</DataProvider>

</ConnectionProperties>

<rd:DataSourceID>6cfe005e-c5e0-4297-aa6b-f2ca0673ec05</rd:DataSourceID>

</DataSource>

</DataSources>

<BottomMargin>1in</BottomMargin>

<RightMargin>1in</RightMargin>

<rd:DrawGrid>true</rd:DrawGrid>

<InteractiveWidth>8.5in</InteractiveWidth>

<rd:SnapToGrid>true</rd:SnapToGrid>

<Body>

<ReportItems>

<List Name="list1">

<Left>1.25in</Left>

<ReportItems>

<Textbox Name="my_xml_1">

<Left>0.5in</Left>

<Top>0.5in</Top>

<rd:DefaultName>my_xml_1</rd:DefaultName>

<Width>3.375in</Width>

<Style>

<PaddingLeft>2pt</PaddingLeft>

<PaddingBottom>2pt</PaddingBottom>

<PaddingRight>2pt</PaddingRight>

<PaddingTop>2pt</PaddingTop>

</Style>

<CanGrow>true</CanGrow>

<Height>0.25in</Height>

<Value>=Code.ParseXml(Fields!my_xml.Value)</Value>

</Textbox>

</ReportItems>

<DataSetName>DataSet1</DataSetName>

<Top>0.375in</Top>

<Height>1in</Height>

</List>

</ReportItems>

<Height>2in</Height>

</Body>

<rd:ReportID>c9ad02b9-b7a2-4780-b954-3ebaa11e0afb</rd:ReportID>

<LeftMargin>1in</LeftMargin>

<DataSets>

<DataSet Name="DataSet1">

<Query>

<rd:UseGenericDesigner>true</rd:UseGenericDesigner>

<CommandText>select '&lt;root&gt;Hello world!&lt;/root&gt;' my_xml

union select '&lt;root&gt;Goodbye!&lt;/root&gt;'</CommandText>

<DataSourceName>DataSource1</DataSourceName>

</Query>

<Fields>

<Field Name="my_xml">

<rd:TypeName>System.String</rd:TypeName>

<DataField>my_xml</DataField>

</Field>

</Fields>

</DataSet>

</DataSets>

<CodeModules>

<CodeModule>System.Xml, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089</CodeModule>

</CodeModules>

<Code>public shared Function ParseXml(xml as string) as string

Dim doc as System.Xml.XmlDocument = new System.Xml.XmlDocument()

doc.LoadXml(xml)

ParseXml = doc.SelectSingleNode("root").InnerText

end function</Code>

<Width>6.5in</Width>

<InteractiveHeight>11in</InteractiveHeight>

<Language>en-US</Language>

<TopMargin>1in</TopMargin>

</Report>

How to read windows registry thru query ?

Hi
How can I access the windows registry entries from MSSQL Server query analyzer. I am looking for some stored procedures. Are there any system stored procedures ?
ThxExtended procedure xp_regread
USE Master
EXEC xp_regread 'HKEY_LOCAL_MACHINE',
'SOFTWARE\Microsoft\MSSQLServer\Setup',
'SQLPath'

How to read the return value of a stored procedure?

Stored procedure in SQL Server 2K is as follows..

CREATE PROCEDURE TestDataRead

@.TestString varchar(20) OUTPUT

AS

SET @.TestString = 'Mr.String'

GO

--

SSIS package details.

OLEDB connection manager; Connection works fine.

Execute SQL Task Editor properties are as follows.

ResultSetàNone

SQLStatementà exec TestDataRead

SQLSourceTypeirectInput

Parameter Mapping:

VariableName: selected the user variable from the list.

Direction: Output

DataType:varchar

Parameter Name: 0

Parameter Type: 20

When I run I am getting the following error.

SSIS package "TEST SSIS1.dtsx" starting.
Error: 0xC002F210 at Execute SQL Task, Execute SQL Task: Executing the query "exec TestDataRead" failed with the following error: "Procedure 'TestDataRead' expects parameter '@.TestString', which was not supplied.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. ...........................................
...................................................................

Please help.

Smith

John Smith001 wrote:

SQLStatementà exec TestDataRead ? OUTPUT

For output parameters also it needs to be passed when calling the stored procedure.

Thanks

|||

Thank you, Karunakarana!

how to read the query in store procedure

i wrote a stored procedure here is the code

CREATE Procedure dm_deleteBBPost
(
@.TopicIDnumeric,
@.ForumIDnumeric

)
As
declare @.netCount as int

BEGIN
Select @.netCount=count(*) from tablename where TopicID=@.TopicID

update tablename set TotalTopics=TotalTopics-1,TotalPosts=TotalPosts-@.netCount where ForumID=@.ForumID

Delete From tblBBPosts Where TopicID = @.TopicID
Delete From tblBBTopics Where TopicID =@.TopicID

end
declare @.pr_ForumsToday as varchar

////////// select Top 1 * from tablename order by column name desc
GO

IN that last line one row will be displayed. how can i read one of the field from the query (selct top1 etc)

Quote:

Originally Posted by madhu7sudan

i wrote a stored procedure here is the code

CREATE Procedure dm_deleteBBPost
(
@.TopicIDnumeric,
@.ForumIDnumeric

)
As
declare @.netCount as int

BEGIN
Select @.netCount=count(*) from tablename where TopicID=@.TopicID

update tablename set TotalTopics=TotalTopics-1,TotalPosts=TotalPosts-@.netCount where ForumID=@.ForumID

Delete From tblBBPosts Where TopicID = @.TopicID
Delete From tblBBTopics Where TopicID =@.TopicID

end
declare @.pr_ForumsToday as varchar

////////// select Top 1 * from tablename order by column name desc
GO

IN that last line one row will be displayed. how can i read one of the field from the query (selct top1 etc)

Try this...

SELECT TOP 1 COLUMNNAME FROM TABLENAME ORDER BY COLUMN NAME DESC

How to read the content of a flat file in Stored procedure

Hi there,
How to read the content of a flat file in Stored procedure into a
variable? I am also trying to find the size of a given flat file into a
variable in stored procedure.
Does anybody have any ideas on these?
Thanks in advance,
-- Sree
*** Sent via Developersdex http://www.examnotes.net ***Perhaps you can use the T-SQL command BULK INSERT. You will have to read
the data into a table.
as far as finding out the file attributes (file size) - perhaps you can use
xp_cmdshell to give you the "dos" level information.
Keith Kratochvil
"sasachi sachi sachi" <sasachi@.getmail.com> wrote in message
news:OYLTC%23UbGHA.404@.TK2MSFTNGP04.phx.gbl...
> Hi there,
> How to read the content of a flat file in Stored procedure into a
> variable? I am also trying to find the size of a given flat file into a
> variable in stored procedure.
> Does anybody have any ideas on these?
> Thanks in advance,
> -- Sree
> *** Sent via Developersdex http://www.examnotes.net ***|||to get the file size use this
declare @.FileName VARCHAR(149)
SELECT @.FileName ='C:\Program Files\Microsoft SQL
Server\MSSQL\readme.txt'
EXEC master..xp_getfiledetails @.FileName
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||EXEC master..xp_getfiledetails @.file
"Keith Kratochvil" wrote:

> Perhaps you can use the T-SQL command BULK INSERT. You will have to read
> the data into a table.
> as far as finding out the file attributes (file size) - perhaps you can us
e
> xp_cmdshell to give you the "dos" level information.
> --
> Keith Kratochvil
>
> "sasachi sachi sachi" <sasachi@.getmail.com> wrote in message
> news:OYLTC%23UbGHA.404@.TK2MSFTNGP04.phx.gbl...
>
>|||Also, yu can access that using xp_cmdshell.
SET @.cmd='DIR '+@.outputfolder
EXEC @.res=master..xp_cmdshell @.cmd
"sasachi sachi sachi"?? ??? ??:

> Hi there,
> How to read the content of a flat file in Stored procedure into a
> variable? I am also trying to find the size of a given flat file into a
> variable in stored procedure.
> Does anybody have any ideas on these?
> Thanks in advance,
> -- Sree
> *** Sent via Developersdex http://www.examnotes.net ***
>