Resetting Email Queue

Hi
I have a the database mail system parameters set to retry sending failed
emails every ten minutes. I want to leave these settings as is.
How can I reset the queue ? that is delete all items in the queue - even if
they have failed! I need to refresh/delete them because we send out the
emails manually if they failed tooo many times - and then eventually sql 2005
alse sends them - i have to prevent this.
Answered my quesiton myself!
One can look at the default system stored procedure and modify it to accept
a subject for example as a parameter.
Then just tweak the rest of the stored procedure to make it select only mail
items with similar subjects and delete those!
done!
"I.W Coetzer" wrote:

> Hi
> I have a the database mail system parameters set to retry sending failed
> emails every ten minutes. I want to leave these settings as is.
> How can I reset the queue ? that is delete all items in the queue - even if
> they have failed! I need to refresh/delete them because we send out the
> emails manually if they failed tooo many times - and then eventually sql 2005
> alse sends them - i have to prevent this.
sql

Resetting Email Queue

Hi
I have a the database mail system parameters set to retry sending failed
emails every ten minutes. I want to leave these settings as is.
How can I reset the queue ? that is delete all items in the queue - even if
they have failed! I need to refresh/delete them because we send out the
emails manually if they failed tooo many times - and then eventually sql 200
5
alse sends them - i have to prevent this.Answered my quesiton myself!
One can look at the default system stored procedure and modify it to accept
a subject for example as a parameter.
Then just tweak the rest of the stored procedure to make it select only mail
items with similar subjects and delete those!
done!
"I.W Coetzer" wrote:

> Hi
> I have a the database mail system parameters set to retry sending failed
> emails every ten minutes. I want to leave these settings as is.
> How can I reset the queue ? that is delete all items in the queue - even i
f
> they have failed! I need to refresh/delete them because we send out the
> emails manually if they failed tooo many times - and then eventually sql 2
005
> alse sends them - i have to prevent this.

resetting database

hi!
i'm new to sql server and enterprise manager and i accidentally deleted all data in a table. how do i recover them? the table (CaptureManager) is still there but empty. i have the following information:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CaptureManager]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[CaptureManager]
GO

CREATE TABLE [dbo].[CaptureManager] (
[ID] [tinyint] IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Email] [varchar] (75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Ext] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Status] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

The easiet way is to restore the database from backup and do INSERT INTO the old table which means everything remains the same you just return the deleted data. The Backup and restore Wizard lets you restore the database with any name you like. Hope this helps.|||unfortunately, i dont have a backup.|||If you buy some expensive log viewing software you may still have the original inserts logged. But really the chance of getting your data back is pretty minimal. I guess it's called learning by your mistakes, sorry :(
|||Try this link to test drive some tools. Hope this helps.
http://www.sql-server-performance.com/greg_robidoux_interview.asp

Reset Report Parameters

Hi

I am creating a report which have 10 different parameter. User needs a button or any functionality using which he/she can reset all the parameter at once and view unfiltered report.

How can I achieve that?

Regards

You can add a textbox that has an action set to "Jump to Report" and set the report to itself.

This will reset the parameters back to their default values or you can specify specific parameters to reset by setting the parameter values that you want to persist to their current value in the Actions dialog.

HTH, Jason

|||

Thanks Jason.

That will do.

Regards

Reset Identity Field Without dropping the table

Hi
I need to reset an Identity field to 1 from time to time.
The table is uses as as job list, the completed job are removed from the
table, there will not be a conflict of numbers at any stage, as the number
of entries per period are far less than the current Identity number.
Currently I drop the table, triggers and index and then create it again,
this is not an elegant solution!
System Configuration
Sql2000 with sp3 Windows 2003 Server
Thanks in advance
Edward Potgieter
edwardp@.foskor.co.za
You can use TRUNCATE TABLE tablename if you want to lose the existing data.
(Though this has some limitations, e.g. if there are foreign keys pointing
to the table... also if your triggers are used for logging deletes etc, I
haven't tested that scenario with truncate.) TRUNCATE can be faster than a
delete because it is logged less (I believe just the page rather than
rows)...
Check out DBCC CHECKIDENT in Books Online also, though this will be useful
usually only if you want to change the seed and keep the data, not reset to
1 (which sounds like the table is empty).
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Edward Potgieter" <edwardp@.foskor.co.za> wrote in message
news:b-udnbB2Qc4yIzHdRVn-sA@.is.co.za...
> Hi
> I need to reset an Identity field to 1 from time to time.
> The table is uses as as job list, the completed job are removed from the
> table, there will not be a conflict of numbers at any stage, as the number
> of entries per period are far less than the current Identity number.
> Currently I drop the table, triggers and index and then create it again,
> this is not an elegant solution!
> System Configuration
> Sql2000 with sp3 Windows 2003 Server
> Thanks in advance
> Edward Potgieter
> edwardp@.foskor.co.za
>
>
|||Try this: http://vyaskn.tripod.com/administration_faq.htm#q2
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Edward Potgieter" <edwardp@.foskor.co.za> wrote in message
news:b-udnbB2Qc4yIzHdRVn-sA@.is.co.za...
Hi
I need to reset an Identity field to 1 from time to time.
The table is uses as as job list, the completed job are removed from the
table, there will not be a conflict of numbers at any stage, as the number
of entries per period are far less than the current Identity number.
Currently I drop the table, triggers and index and then create it again,
this is not an elegant solution!
System Configuration
Sql2000 with sp3 Windows 2003 Server
Thanks in advance
Edward Potgieter
edwardp@.foskor.co.za
|||Here is how I reseed tables:
declare @.i int
select @.I = max(YourIdentityColumn) from YourTable
if @.I is null DBCC CHECKIDENT (YourTable, RESEED, 0)
else DBCC CHECKIDENT (YourTable, RESEED, @.I)
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Edward Potgieter" <edwardp@.foskor.co.za> wrote in message
news:b-udnbB2Qc4yIzHdRVn-sA@.is.co.za...
> Hi
> I need to reset an Identity field to 1 from time to time.
> The table is uses as as job list, the completed job are removed from the
> table, there will not be a conflict of numbers at any stage, as the number
> of entries per period are far less than the current Identity number.
> Currently I drop the table, triggers and index and then create it again,
> this is not an elegant solution!
> System Configuration
> Sql2000 with sp3 Windows 2003 Server
> Thanks in advance
> Edward Potgieter
> edwardp@.foskor.co.za
>
>

Reset Identity Field Without dropping the table

Hi
I need to reset an Identity field to 1 from time to time.
The table is uses as as job list, the completed job are removed from the
table, there will not be a conflict of numbers at any stage, as the number
of entries per period are far less than the current Identity number.
Currently I drop the table, triggers and index and then create it again,
this is not an elegant solution!
System Configuration
Sql2000 with sp3 Windows 2003 Server
Thanks in advance
Edward Potgieter
edwardp@.foskor.co.zaYou can use TRUNCATE TABLE tablename if you want to lose the existing data.
(Though this has some limitations, e.g. if there are foreign keys pointing
to the table... also if your triggers are used for logging deletes etc, I
haven't tested that scenario with truncate.) TRUNCATE can be faster than a
delete because it is logged less (I believe just the page rather than
rows)...
Check out DBCC CHECKIDENT in Books Online also, though this will be useful
usually only if you want to change the seed and keep the data, not reset to
1 (which sounds like the table is empty).
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Edward Potgieter" <edwardp@.foskor.co.za> wrote in message
news:b-udnbB2Qc4yIzHdRVn-sA@.is.co.za...
> Hi
> I need to reset an Identity field to 1 from time to time.
> The table is uses as as job list, the completed job are removed from the
> table, there will not be a conflict of numbers at any stage, as the number
> of entries per period are far less than the current Identity number.
> Currently I drop the table, triggers and index and then create it again,
> this is not an elegant solution!
> System Configuration
> Sql2000 with sp3 Windows 2003 Server
> Thanks in advance
> Edward Potgieter
> edwardp@.foskor.co.za
>
>|||Try this: http://vyaskn.tripod.com/administration_faq.htm#q2
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Edward Potgieter" <edwardp@.foskor.co.za> wrote in message
news:b-udnbB2Qc4yIzHdRVn-sA@.is.co.za...
Hi
I need to reset an Identity field to 1 from time to time.
The table is uses as as job list, the completed job are removed from the
table, there will not be a conflict of numbers at any stage, as the number
of entries per period are far less than the current Identity number.
Currently I drop the table, triggers and index and then create it again,
this is not an elegant solution!
System Configuration
Sql2000 with sp3 Windows 2003 Server
Thanks in advance
Edward Potgieter
edwardp@.foskor.co.za|||Here is how I reseed tables:
declare @.i int
select @.I = max(YourIdentityColumn) from YourTable
if @.I is null DBCC CHECKIDENT (YourTable, RESEED, 0)
else DBCC CHECKIDENT (YourTable, RESEED, @.I)
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Edward Potgieter" <edwardp@.foskor.co.za> wrote in message
news:b-udnbB2Qc4yIzHdRVn-sA@.is.co.za...
> Hi
> I need to reset an Identity field to 1 from time to time.
> The table is uses as as job list, the completed job are removed from the
> table, there will not be a conflict of numbers at any stage, as the number
> of entries per period are far less than the current Identity number.
> Currently I drop the table, triggers and index and then create it again,
> this is not an elegant solution!
> System Configuration
> Sql2000 with sp3 Windows 2003 Server
> Thanks in advance
> Edward Potgieter
> edwardp@.foskor.co.za
>
>sql

Re-set identity field

Hi:

I created a small SQL Express database/ASP.net/C# application and in the learning process. Before I implement it I would like to re-set autonumber / identity field back to 1. Also, I need to start with the blank database. I am not sure how to approach that?

Can you assist?

Thanks

http://www.mssqlcity.com/FAQ/Devel/reset_identity_column.htm

Required Microsoft Sql Server 2005 Express Server Roles for JDBC Connection

Hi!

I have developed a database in MS SQL Server 2005 Express, to which I would require only bulkadmin server role from an external java application, because I only need to update rows, insert values or use select queries in the database.

The problem is that, using either the Microsoft JDBC Driver 1.1 or the Java JDBC ODBC Driver and the Windows XP Data Base (ODBC) configurations, I need a user with sysadmin server role inside Sql Server, otherwise JDBC won't connect to the database using the selected user. Even if I leave the sql login with setupadmin or any server role lower than sysadmin, the connection is refused.

Is there no way to connect using JDBC to MS Sql Server 2005 other than granting the connected user sysadmin rights? My code looks as follows:

Code Snippet

String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
String url = "jdbc:sqlserver://FIREBLADE\\SQLEXPRESS";
String user = "username";
String password = "password$$";
Connection conn;

Class.forName(driver);
conn = DriverManager.getConnection(url,user,password);
if (conn != null)
System.out.println("\nSQL Server Connection established ...\n");

I have heard that Java JDBC connections to Microsoft require high-level access.

Any informed answer is more than welcome. Thanks for reading my post!

Your connection string is a bit different than what's documented in the following article - you'd want to start there:

http://blogs.msdn.com/angelsb/archive/2005/08/01/446452.aspx

Connecting through JDBC does not require high level access or sysadmin rights.

-Sue

Required Day as string; Ex: Sunday

Hi

I have a string which contains the year and month, now i wanted to know what is the starting day of that month in that particular year.

For Ex: string STR = "Jan\2006" then by any chance i can know the starting day of this month as whether it is Sunday etc. I need the string "sunday" as output. Also, I need to know how many number of days that particular month contains. Any function or stored procedure which will give out these two outputs will be much appreciated.

Thanks!

Santhosh

SQL Server has some built in functions to accomodate this functionality. Look at DateName and DatePart in Books On Line.

Here's an example to get you started.

Declare @.Temp VarChar(20)

Set @.Temp = 'Jan\2006'

Select DateName(Weekday, Convert(DateTime, '01-' + Replace(@.Temp, '\', '-'))) As StartingWeekday,
Day(DateAdd(day, -1, DateAdd(Month, 1, Convert(DateTime, '01-' + Replace(@.Temp, '\', '-'))))) As DaysInMonth

|||Where are you getting such a string? Can't you modify the application to send the date value in a proper format? Yes, you can parse and get the weekday etc but it will just be clumsy piece of code and probably unnecessarily complicated. It is best to use a data format that is easy to manipulate and understand. For example, using the ISO unseparated date format (YYYYMMDD) for date values will help a lot. It also protects you from different language or dateformat settings on the server. The code posted by the other user will work only for certain language / dateformat settings. It will fail if the language of the session is Czech for instance. Lastly, you will be better off having a calendar table in your database that has this information. You can then answer these type of questions with a simple query, handle language settings easily, have flexibility to make changes etc.

Required Day as string; Ex: Sunday

Hi

I have a string which contains the year and month, now i wanted to know what is the starting day of that month in that particular year.

For Ex: string STR = "Jan\2006" then by any chance i can know the starting day of this month as whether it is Sunday etc. I need the string "sunday" as output. Also, I need to know how many number of days that particular month contains. Any function or stored procedure which will give out these two outputs will be much appreciated.

Thanks!

Santhosh

SQL Server has some built in functions to accomodate this functionality. Look at DateName and DatePart in Books On Line.

Here's an example to get you started.

Declare @.Temp VarChar(20)

Set @.Temp = 'Jan\2006'

Select DateName(Weekday, Convert(DateTime, '01-' + Replace(@.Temp, '\', '-'))) As StartingWeekday,
Day(DateAdd(day, -1, DateAdd(Month, 1, Convert(DateTime, '01-' + Replace(@.Temp, '\', '-'))))) As DaysInMonth

|||Where are you getting such a string? Can't you modify the application to send the date value in a proper format? Yes, you can parse and get the weekday etc but it will just be clumsy piece of code and probably unnecessarily complicated. It is best to use a data format that is easy to manipulate and understand. For example, using the ISO unseparated date format (YYYYMMDD) for date values will help a lot. It also protects you from different language or dateformat settings on the server. The code posted by the other user will work only for certain language / dateformat settings. It will fail if the language of the session is Czech for instance. Lastly, you will be better off having a calendar table in your database that has this information. You can then answer these type of questions with a simple query, handle language settings easily, have flexibility to make changes etc.

Request to IIS failed

hi

i having having an error request to IIs failed evertime.i have even uinstall my firewall but it is still not wking.here is my sql server diagnotics

SQL Server Mobile Server Agent Diagnostics

2006/07/15 15:57:03

General Information Item Value Server Name nisha URL /SQLMobile/sqlcesa30.dll Authentication Type Anonymous Server Port 80 HTTPS off Server Software Microsoft-IIS/5.1 Replication Allowed RDA Allowed Logging Level 1

Impersonation and Access Tests Action Status ErrorCode Impersonate User SUCCESS 0x0 ReadWriteDeleteMessageFile SUCCESS 0x0

SQL Server Mobile Modules Test Module Status ErrorCode Version SQLCERP30.DLL SUCCESS 0x0 3.0.5207.0 SQLCESA30.DLL SUCCESS 0x0 3.0.5207.0

Reconciler Test Reconciler Status ErrorCode 9.0 Database Reconciler SUCCESS 0x0 8.0 Database Reconciler FAILURE 0x80004005

SQL Server Module Versions Module Version sqloledb.dll 2000.85.1117.0 9.0 replrec.dll 2005.90.1399.0 9.0 replprov.dll 2005.90.1399.0 9.0 msgprox.dll 2005.90.1399.0

i dont kno how to solve the database reconciler.i am not able to synchronize.my coding is as follows:

Dim repl AsNew SqlCeReplication()

Dim strpath AsString

strpath = "\Program Files\Flight_PPC\SQLFlight.sdf"

repl.InternetUrl = "http://nisha/SQLMobile/sqlcesa30.dll"

repl.Publisher = "NISHA"

repl.PublisherDatabase = "flightDatabase"

repl.PublisherSecurityMode = SecurityType.DBAuthentication

repl.PublisherLogin = "sa"

repl.PublisherPassword = "pradha"

repl.Publication = "SQLMobile"

repl.Subscriber = "SQLMobile"

repl.SubscriberConnectionString = "Data Source=" + strpath

Try

repl.Synchronize()

Catch err As SqlCeException

MessageBox.Show(err.ToString)

EndTry

Try increasing your default timeout value on the IIS server and also the timeout from the client and see if that helps.|||Hi, I'm having the same problem...

The timeout of my IIS is set to 120 seconds, so I don't think that is the problem. I have the same exact display page as posted above. Any other idea?

Thanks
|||

Hi, I still have the same issue. Any other suggestions will be appreciated. Thanks.

|||

Can you post the error message? You can see the log within "Replication Monitor". Open up SQL Server Managment Studio. Right click on "Replication" and choose "Launch Replication Monitor".

Request to IIS failed

hi

i having having an error request to IIs failed evertime.i have even uinstall my firewall but it is still not wking.here is my sql server diagnotics

SQL Server Mobile Server Agent Diagnostics

2006/07/15 15:57:03

General Information

Item

Value

Server Name

nisha

URL

/SQLMobile/sqlcesa30.dll

Authentication Type

Anonymous

Server Port

80

HTTPS

off

Server Software

Microsoft-IIS/5.1

Replication

Allowed

RDA

Allowed

Logging Level

1

Impersonation and Access Tests

Action

Status

ErrorCode

Impersonate User

SUCCESS

0x0

ReadWriteDeleteMessageFile

SUCCESS

0x0

SQL Server Mobile Modules Test

Module

Status

ErrorCode

Version

SQLCERP30.DLL

SUCCESS

0x0

3.0.5207.0

SQLCESA30.DLL

SUCCESS

0x0

3.0.5207.0

Reconciler Test

Reconciler

Status

ErrorCode

9.0 Database Reconciler

SUCCESS

0x0

8.0 Database Reconciler

FAILURE

0x80004005

SQL Server Module Versions

Module

Version

sqloledb.dll

2000.85.1117.0

9.0 replrec.dll

2005.90.1399.0

9.0 replprov.dll

2005.90.1399.0

9.0 msgprox.dll

2005.90.1399.0

i dont kno how to solve the database reconciler.i am not able to synchronize.my coding is as follows:

Dim repl As New SqlCeReplication()

Dim strpath As String

strpath = "\Program Files\Flight_PPC\SQLFlight.sdf"

repl.InternetUrl = "http://nisha/SQLMobile/sqlcesa30.dll"

repl.Publisher = "NISHA"

repl.PublisherDatabase = "flightDatabase"

repl.PublisherSecurityMode = SecurityType.DBAuthentication

repl.PublisherLogin = "sa"

repl.PublisherPassword = "pradha"

repl.Publication = "SQLMobile"

repl.Subscriber = "SQLMobile"

repl.SubscriberConnectionString = "Data Source=" + strpath

Try

repl.Synchronize()

Catch err As SqlCeException

MessageBox.Show(err.ToString)

End Try

Try increasing your default timeout value on the IIS server and also the timeout from the client and see if that helps.|||Hi, I'm having the same problem...

The timeout of my IIS is set to 120 seconds, so I don't think that is the problem. I have the same exact display page as posted above. Any other idea?

Thanks|||

Hi, I still have the same issue. Any other suggestions will be appreciated. Thanks.

|||

Can you post the error message? You can see the log within "Replication Monitor". Open up SQL Server Managment Studio. Right click on "Replication" and choose "Launch Replication Monitor".

Republishing

Hi
I want to republish data in sql server. Scenario is like
this.
SERVER A SERVER B SERVER C
Publisher --> Subscriber/Publisher --> Subscriber
Server A publish data (Merge/push to SERVER B)
Server B publish data (Merge/push to SERVER C)
Can be done this type of publishing.?
Is there any way to doing this type of publishing.
Thanks
Dishan
Yes this can be done.
Books On Line should have more information on it.
If you have trouble setting up, please respond to this thread with the
problems.
I will try to help.
Hope that helps
--Mahesh
[ This posting is provided "as is" with no warranties and confers no
rights. ]
"DishanF" <d700693@.hotmail.com> wrote in message
news:550901c480e9$7fac2560$a501280a@.phx.gbl...
> Hi
> I want to republish data in sql server. Scenario is like
> this.
> SERVER A SERVER B SERVER C
> Publisher --> Subscriber/Publisher --> Subscriber
> Server A publish data (Merge/push to SERVER B)
> Server B publish data (Merge/push to SERVER C)
> Can be done this type of publishing.?
> Is there any way to doing this type of publishing.
> Thanks
> Dishan
|||Can you send me any web urls ?

Representing rows as columns

Hi
I have a table in SQL Server 2000 that has following data:

PunchTime PunchType
11:45:00 In
12:45:00 Out
1:45:00 In
3:15:00 Out

Is there a way in SQL to represent this in the following format:
In Out In Out
11:45:00 12:45:00 1:45:00 3:15:00

ThanksYou specification is unclear in several respects. What is the datatype of
the PunchTime column (DATETIME or CHAR maybe)? What is the primary key of
this table? Do we know whether the times are AM or PM? Why is 1:45 shown
after 12:45 in your required result?

The best answer will be to do it in your client application. What you want
is purely presentational and presentational functionality belongs
client-side.

--
David Portas
SQL Server MVP
--|||[posted and mailed, please reply in news]

Rajeev (navvyus@.yahoo.com) writes:
> I have a table in SQL Server 2000 that has following data:
> PunchTime PunchType
> 11:45:00 In
> 12:45:00 Out
> 1:45:00 In
> 3:15:00 Out
> Is there a way in SQL to represent this in the following format:
> In Out In Out
> 11:45:00 12:45:00 1:45:00 3:15:00

There is no built-in construct, but there are a couple of possibilities
to depending on your requirements.

For this particular case, you could to this, under the assumption that
you have at most four rows per day:

SELECT In = in1.PunchTime, Out = out1.PunchTime,
In = in2.PunchTime, Out = out2.PunchTime
FROM tbl in1
JOIN tbl out1 ON in1.PunchDate = out1.PunchDate
LEFT JOIN tbl in2 ON in1.Punchdate = in2.PunchDate
AND in1.PunchType = in2.PunchType
AND in1.PunchType < in2.PunchType
LEFT JOIN tbl out2 ON out1.Punchdate = out2.PunchDate
AND out1.PunchType = out2.PunchType
AND out1.PunchType < out2.PunchType
WHERE in1.PunchType = 'In'
AND out1.PunchType = 'Out'

Here I have assumed there is a date column in the table, since that
would make sense. I have also been lazy and assumed that there is
always one In and one Out each day.

In a more general columns where you want dynamic column names etc,
you have to build dynamic SQL. But before you do that, check out
the third-party tool RAC, http://www.rac4sql.net/ which aspires to
be the ultimate tool for crosstab queries.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Representing a graph in TSQL

Hi!
I need to represent a graph in TSQL.
The graph is directed. It is essentially a number of trees which all have various (non-root) nodes in common with each other.
Any ideas or pointers would be most welcome :)
Thanks,
AngelosWithout knowing what your instructor covered in class, we can't even give you a good guess at how to do this. All three of the classic methods for representing graphs are easy to support in SQL, and many of the more avante-guard representations are easy too.

Talk with the instructor. It is still quite early in the session (at least most places). They should be willing to help you work this out.

-PatP