database cleanup

is there any SQL statement that can clear all rows in each table in a databa
se?Hi,
There is no single command to clean all the tables. You need to rite an
iterative loop traversing all the tables in the database.
For better performance try using TRUNCATE TABLE instead of DELETE
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"nonno" wrote:
> is there any SQL statement that can clear all rows in each table in a database?[/c
olor]|||Here is how you can do it.
Create a cursor on table sysobjects so that you select all the tables.
Traversing each row, create a query to TRUNCATE dynamically an then execute
the query.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Chandra" wrote:
> Hi,
> There is no single command to clean all the tables. You need to rite an
> iterative loop traversing all the tables in the database.
> For better performance try using TRUNCATE TABLE instead of DELETE
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---
>
> "nonno" wrote:
>|||You can use the sp_msforeachtable stored procedure:
exec sp_msforeachtable 'truncate table ?'
Notice that sp_msforeachtable is a none documented stored procedure
(but it is very well known and you can find articles that describe it
with google search). Also if you have foreign keys in the DB, then
truncate can not be used for the parent tables.
Adi|||In addition , check out FKs created on the tables.
DECLARE @.TruncateStatement nvarchar(4000)
DECLARE TruncateStatements CURSOR LOCAL FAST_FORWARD
FOR
SELECT
N'TRUNCATE TABLE ' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = 'BASE TABLE' AND
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE
_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
OPEN TruncateStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM TruncateStatements INTO @.TruncateStatement
IF @.@.FETCH_STATUS <> 0 BREAK
RAISERROR (@.TruncateStatement, 0, 1) WITH NOWAIT
EXEC(@.TruncateStatement)
END
CLOSE TruncateStatements
DEALLOCATE TruncateStatements
"Adi" <adico@.clalit.org.il> wrote in message
news:1117006995.155155.232830@.z14g2000cwz.googlegroups.com...
> You can use the sp_msforeachtable stored procedure:
> exec sp_msforeachtable 'truncate table ?'
> Notice that sp_msforeachtable is a none documented stored procedure
> (but it is very well known and you can find articles that describe it
> with google search). Also if you have foreign keys in the DB, then
> truncate can not be used for the parent tables.
> Adi
>|||http://www.extremeexperts.com/SQL/S...eAllTables.aspx
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
"nonno" <nonno@.discussions.microsoft.com> wrote in message
news:BBED9F55-36ED-4D65-9FD0-95F031F4B42F@.microsoft.com...
> is there any SQL statement that can clear all rows in each table in a
database?|||Generate a script for all the tables in the database, and then run it. This
will drop / create all tables.
"nonno" <nonno@.discussions.microsoft.com> wrote in message
news:BBED9F55-36ED-4D65-9FD0-95F031F4B42F@.microsoft.com...
> is there any SQL statement that can clear all rows in each table in a
database?sql

Database Being Replicated

I need to detect whether or not a database is being replicated. I need to do
this in a script and use the results in an IF statement. Is the best way to
do this by checking the value of category in the sysdatabases table?
Something like this …
IF EXISTS (SELECT * FROM sysdatabases WHERE [name] = 'emobile_markp' AND
category = 0)
BEGIN
PRINT 'Not replicating'
END
ELSE
BEGIN
PRINT 'Replicating'
END
Thanks,
Mark
You could do this using databasepropertyex:
select name, databasepropertyex (name,'IsMergePublished') from
master..sysdatabases
select name, databasepropertyex (name,'IsPublished') from
master..sysdatabases
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||You could also try
sp_helpreplicationdboption and observe the values of transpublish and
mergepublish
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"mrprice" <mrprice@.discussions.microsoft.com> wrote in message
news:36A25480-B64D-4D67-914C-F85A6D0EFA8A@.microsoft.com...
>I need to detect whether or not a database is being replicated. I need to
>do
> this in a script and use the results in an IF statement. Is the best way
> to
> do this by checking the value of category in the sysdatabases table?
> Something like this .
> IF EXISTS (SELECT * FROM sysdatabases WHERE [name] = 'emobile_markp' AND
> category = 0)
> BEGIN
> PRINT 'Not replicating'
> END
> ELSE
> BEGIN
> PRINT 'Replicating'
> END
> Thanks,
> Mark
|||Select name,category from sysdatabases (For SQL Server 2000)
See if it is replicate by
looking at these values
1 = Published.
2 = Subscribed.
4 = Merge Published.
8 = Merge Subscribed.
Select * from sys.datbases
and look for is_published,is_subscribed etc
thankx
"mrprice" wrote:

> I need to detect whether or not a database is being replicated. I need to do
> this in a script and use the results in an IF statement. Is the best way to
> do this by checking the value of category in the sysdatabases table?
> Something like this …
> IF EXISTS (SELECT * FROM sysdatabases WHERE [name] = 'emobile_markp' AND
> category = 0)
> BEGIN
> PRINT 'Not replicating'
> END
> ELSE
> BEGIN
> PRINT 'Replicating'
> END
> Thanks,
> Mark
sql

DataAdapter.Update updates zero records

I am having trouble getting an Update call to actually update records. The select statement is a stored procedure which is uses inner joins to link to property tables. The update, insert, and delete commands were generated by Visual Studio and only affect the primary table. So, to provide a simple example, I have a customer table with UID, Name, and LanguageID and a seperate table with LanguageID and LanguageDescription. The stored procedure can be used to populate a datagrid with all results (this works). The stored procedure also populates an edit page with one UID (this works). After the edit is completed, I attempt to update the dataset, which only has one row at this time, which shows that it has been modified. The Update modifies 0 rows and raises no exceptions. Is this because the update, insert, and delete statements do not match up one-to-one with the dataset? If so, what are my choices?I found the source of the problem. It was a result of a conflict elsewhere. So, if anyone else is trying to use a combination of stored procedures and normal sql queries, it will work fine, even if the statements do not match up perfectly with the dataset. The queries simply need to meet any requirements of the database.

DataAdapter - SELECT Statement - items in last 30 days

I'm using DataList to return vales stored in an SQL database, of which one of the fields contains the date the record was added.

I am trying to fill the dataset with items only from the last 30 days.
I've tried a few different ways, but all the database rows are returned.

What is the WHERE clause I sholud use to do this??

ThanksTry with the following SQL statement, i belive it should work.

select * from <tablename> where datediff(day, <columnname>, getdate()) < 30

Hope it solves your issue.|||Thanks very much, it worked a treat

Data type error

I have created a Foreach Loop container which generates a string variable which is a Select statement that in turn used in my OLE DB source in my Data Flow task. I have a 3 variables that I am using to create the Select statement. Two of them work fine but the 3rd gives me an error "Cannot convert varchar to numeric" after about the 5th or 6th loop which is odd as the variable is the same for each pass.

The SQL Task linked to the Foreach Loop is a query as follows

SELECT DISTINCT

CAST(FYr as varchar(4)) as FYr,

CAST(Acct1 as varchar(13)) as Acct1,

CAST(Acct2 as varchar(13)) as Acct2

FROM GL, AcctTbl

The resulting dataset looks like this (there is only one record in AcctTbl)

FYr Acct1 Acct2

2000 400.00 307.00

2001 400.00 307.00

2002 400.00 307.00

etc, which is exactly what I want.

I've created package scope string variables for sFYr, sAcct1 and sAcct2 as well as another variable qrySQL

The value for qrySQL string variable is something like this

"Select " + @.User :: sFYr + " as FiscalYear, Account, " + @.User :: sAcct2 + "Amount FROM tblGL WHERE Account < " + @.User :: sAcct1

This is then used as my datasourse in my data flow task

When I run the package it goes through a half a dozen iterations ( there are about a dozen total rows to iterate) and successfully writes the results to my data destination table but then fails with the "cannot convert varchar to numeric" message.

It seems to be with the sAcct1 variable because if I use the same string for my qrySQL except I replace the sAcct1 variable with string (as shown below) the package completes successfully

"Select " + @.User :: sFYr + " as FiscalYear, Account, " + @.User :: sAcct2 + "Amount FROM tblGL WHERE Account < '400.00'

Does anyone have any ideas? Can I not use the < to compare string? The Account field that I'm comparing is a varchar(13) field. I've even tried casting the Account and sAcct1 variable as numeric in the qrySQL string and I'm getting the same failure after several iterations.

Any insight would really be appreciated. I've lost a bit of hair over this one.

Thanks in advance

You should be including single quotes around sAcct1 in the WHERE clause, such as "...WHERE Account < '" + @.User :: sAcct1 + "'"

I believe SQL Server is converting the value of your Account column to a numeric to match the datatype you are sending it. The error is occurring because you have data in that column that fails the conversion.
|||

wpwebster wrote:

I have created a Foreach Loop container which generates a string variable which is a Select statement that in turn used in my OLE DB source in my Data Flow task. I have a 3 variables that I am using to create the Select statement. Two of them work fine but the 3rd gives me an error "Cannot convert varchar to numeric" after about the 5th or 6th loop which is odd as the variable is the same for each pass.

The SQL Task linked to the Foreach Loop is a query as follows

SELECT DISTINCT

CAST(FYr as varchar(4)) as FYr,

CAST(Acct1 as varchar(13)) as Acct1,

CAST(Acct2 as varchar(13)) as Acct2

FROM GL, AcctTbl

The resulting dataset looks like this (there is only one record in AcctTbl)

FYr Acct1 Acct2

2000 400.00 307.00

2001 400.00 307.00

2002 400.00 307.00

etc, which is exactly what I want.

I've created package scope string variables for sFYr, sAcct1 and sAcct2 as well as another variable qrySQL

The value for qrySQL string variable is something like this

"Select " + @.User :: sFYr + " as FiscalYear, Account, " + @.User :: sAcct2 + "Amount FROM tblGL WHERE Account < " + @.User :: sAcct1

This is then used as my datasourse in my data flow task

When I run the package it goes through a half a dozen iterations ( there are about a dozen total rows to iterate) and successfully writes the results to my data destination table but then fails with the "cannot convert varchar to numeric" message.

It seems to be with the sAcct1 variable because if I use the same string for my qrySQL except I replace the sAcct1 variable with string (as shown below) the package completes successfully

"Select " + @.User :: sFYr + " as FiscalYear, Account, " + @.User :: sAcct2 + "Amount FROM tblGL WHERE Account < '400.00'

Does anyone have any ideas? Can I not use the < to compare string? The Account field that I'm comparing is a varchar(13) field. I've even tried casting the Account and sAcct1 variable as numeric in the qrySQL string and I'm getting the same failure after several iterations.

Any insight would really be appreciated. I've lost a bit of hair over this one.

Thanks in advance

Is it possible that Account column in tblGL table is numeric? if so, you make sure that you cast accordingly the values of acct1 from Acttbl table. Why are you casting it in the query as varchar and putting it in a string variable? would not be better to to use a data type that is consistent with tblGL.Account?

|||

Thanks for the input. It helped me get to the bottom of it. The Account field is a varchar(13) field although the accounts are in a format of something like 400.00 There was however several records I found where the Accocunt was NA, when I changed those through a derived column data flow control to a "0" it worked fine.

I still find it a bit puzzling that the WHERE clause worked when it was WHERE Account < '400.00' but wouldnt' work when it was WHERE Account < @.UserVariable

Making sure all Accounts looked like numbers did the trick though.

Thanks again for the input.

Regards

Bill