More on ANSI Padding

The column attribute trimtrailingblanks is changed,
without notice, from yes to no when the length of the
column is altered via the Query Analyzer. Is there a way
to change the trimtrailingblanks property of a column on
the fly?Seems like ALTER TABLE doesn't honor the ANSI_PADDINGS setting and always set this to "no". See
script at end.
AFAIK, there's no way to change this with less that re-create the table.
I don't know if the current behavior is considered a bug or even reported, you might want to check
with MS.
SET ANSI_PADDING ON
GO
create table t(c1 varchar(1) NULL)
GO
EXEC sp_help t
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE t ALTER COLUMN c1 varchar(1) NOT NULL
GO
EXEC sp_help t
GO
DROP TABLE t
GO
SET ANSI_PADDING OFF --Doesn't help
GO
create table t(c1 varchar(1) NULL)
GO
EXEC sp_help t
GO
--SET ANSI_PADDING ON --Doesn't matter
GO
ALTER TABLE t ALTER COLUMN c1 varchar(1) NOT NULL
GO
EXEC sp_help t
GO
DROP TABLE t
GO
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Cordelia goh" <cordelia.goh@.gvrd.bc.ca> wrote in message
news:046901c3933d$71b19f20$a101280a@.phx.gbl...
> The column attribute trimtrailingblanks is changed,
> without notice, from yes to no when the length of the
> column is altered via the Query Analyzer. Is there a way
> to change the trimtrailingblanks property of a column on
> the fly?
>sql

More About SQL Mail

Dear Surajits,
Thanks for teach me the method of sending query result via SQL mail. But I
want to ask that if I want today date on the Subject, how can I perform this
?
i.e. @.Subject = 'Summary for the date of ' + GetDate() <= Can I write like
this ?
Thanks for concern.
You can't call the GETDATE() function in the proc execution. Bus you can declare a variable and
construct the subject into that variable (including today's date) and then pass that variable as a
parameter for subject in the xp_sendmail call.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Devil Garfield" <DevilGarfield@.discussions.microsoft.com> wrote in message
news:6F8F593F-C69B-467E-9BFF-276096853050@.microsoft.com...
> Dear Surajits,
> Thanks for teach me the method of sending query result via SQL mail. But I
> want to ask that if I want today date on the Subject, how can I perform this
> ?
> i.e. @.Subject = 'Summary for the date of ' + GetDate() <= Can I write like
> this ?
> Thanks for concern.
sql

More About SQL Mail

Dear Surajits,
Thanks for teach me the method of sending query result via SQL mail. But I
want to ask that if I want today date on the Subject, how can I perform this
?
i.e. @.Subject = 'Summary for the date of ' + GetDate() <= Can I write like
this '
Thanks for concern.You can't call the GETDATE() function in the proc execution. Bus you can dec
lare a variable and
construct the subject into that variable (including today's date) and then p
XXX that variable as a
parameter for subject in the xp_sendmail call.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Devil Garfield" <DevilGarfield@.discussions.microsoft.com> wrote in message
news:6F8F593F-C69B-467E-9BFF-276096853050@.microsoft.com...
> Dear Surajits,
> Thanks for teach me the method of sending query result via SQL mail. But I
> want to ask that if I want today date on the Subject, how can I perform th
is
> ?
> i.e. @.Subject = 'Summary for the date of ' + GetDate() <= Can I write like
> this '
> Thanks for concern.

More About SQL Mail

Dear Surajits,
Thanks for teach me the method of sending query result via SQL mail. But I
want to ask that if I want today date on the Subject, how can I perform this
?
i.e. @.Subject = 'Summary for the date of ' + GetDate() <= Can I write like
this '
Thanks for concern.You can't call the GETDATE() function in the proc execution. Bus you can declare a variable and
construct the subject into that variable (including today's date) and then pass that variable as a
parameter for subject in the xp_sendmail call.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Devil Garfield" <DevilGarfield@.discussions.microsoft.com> wrote in message
news:6F8F593F-C69B-467E-9BFF-276096853050@.microsoft.com...
> Dear Surajits,
> Thanks for teach me the method of sending query result via SQL mail. But I
> want to ask that if I want today date on the Subject, how can I perform this
> ?
> i.e. @.Subject = 'Summary for the date of ' + GetDate() <= Can I write like
> this '
> Thanks for concern.

Month and date wrong way around

Hi,

I am querying a report that was written with reporting services, via a webpage in vs2005. However when I input a date field to query from i.e 14/01/2007, this produces an error because the report is seeing this as 01/14/2007, even though in the database the record shows 14/01/2007? Please can someone offer any advice what to check?

thanks,

Harry.

Is that report runs client side?

|||

It seems to happen when report is tested in 'preview mode', and when the report is deployed it happens on the client. An error results because it can't handle the DD/MM the wrong way around?

There must be something I'm missing here...?

Many thanks,

Harry

|||

Hi, Harry:

You should try to format your date before you using them.

You can check out this article about how to format the date in SSRS.

http://msdn2.microsoft.com/en-us/library/ms157328.aspx

I hope the above information will be helpful. If you have any issues or concerns, please let me know. It's my pleasure to be of assistance

|||

Hi,

I'm a little confused how I can use the conversions,

I'm passing StartDate AND EndDate from 2 txtboxes,, my data is SELECT par1, par2, par2 from tbldatabase1 WHERE par1 >@.StartDate and par1 <@.EndDate.

But the parameters from the date boxes are taken in the wrong wat around. Do you know how I can implement some code to change this?. My main visual studio pages are written in c#?.

Or would you do this in the query itself?

Any help would be greatly appreciated.

Thanks Harry.

|||

HI,camper :

If your referring toReport Manager displaying the DateTime in theparameter input box, then no, you cannotformat this. You can onlyformat the date within thereport itself, or like the example I posted as following, give the user a dropdownparameter list of dates.

Apparently you can set theparameter to a string and then it won't enter the time, though you'll have to convert it into a date before it runs against your dataset. This way though,report users can enter a non-date as aparameter.

For some of myreports I create aparameter dataset based on the table holding the dates.

SELECT DATENAME(day, MyDateField) + ' ' + DATENAME(month, MyDateField) + ' ' + DATENAME(year, MyDateField) AS Label, MyDateField AS Value
FROM MyTable
GROUP BY DATENAME(day, MyDateField) + ' ' + DATENAME(month, MyDateField) + ' ' + DATENAME(year, MyDateField), MyDateField
ORDER BY MyDateField

This creates 2 fields, 'Label' which is what the user selects from and 'Value' which the dataset uses to query on.
Create aparameter (@.MyParameterDate) and reference it against your main dataset e.g.

SELECT * FROM MyTable WHERE MyDateField = @.MyParameterDate

In yourReportparameters set the values to be from a query and select yourparameter dataset. Set the Value and Label datafields to the ones created above and ensure theparameter datatype is DateTime. You can change the aboveformat if you don't want dates displaying as '7 November 2005'

I hope the above information will be helpful. If you have any issues or concerns, please let

me know. It's my pleasure to be of assistance

|||

Hi,

I know it's a few months since the last entry but I was having the same problems and fixed it by setting the Report properties: Language Property to my locale (English (United Kingdom) in this case). You can get at these properties from the report designer (in local mode) and clicking on the grey area outside the design grid. This stopped the report from swapping the months and days around :-)

Good luck.

|||

Change this line in your model's smdl file

<Culture>de-DE</Culture>

In this case German yyyy.mm.dd

|||

I am having the same problem. I saw your solution and tried that. However, it appears that those settings do not get passed to the report server. The report now correctly allows entry of the date in the Visual Studio designer, and when selected shows the date correctly, however, when the report is delpyed, the server, does not change behavior. It still thinks it is using mm/dd/yyy format.

Any ideas how to change the server side format to allow dd/mm/yy parameter format entry?

|||I also had to change regional settings to YYYY/MM/DD format on server. In layout view click properties on righthand side and select report from available fields, Click on language and choose English South Africa for YYYY/MM/DD format.sql

Monitoring Trans Log

Does anyone know of a system stored procedure that I can use to record
how much space is left on my trans logs?
Thanks for any help.
*** Sent via Developersdex http://www.codecomments.com ***
Take a look at:
DBCC SQLPERF(LogSpace)
-Sue
On Mon, 23 Jan 2006 12:12:20 -0800, Gerald Hopkins
<geraldh@.sl-tech.net> wrote:

>Does anyone know of a system stored procedure that I can use to record
>how much space is left on my trans logs?
>Thanks for any help.
>*** Sent via Developersdex http://www.codecomments.com ***
|||Thanks, Sue. This looks like what I need!
*** Sent via Developersdex http://www.codecomments.com ***

Monitoring Trans Log

Does anyone know of a system stored procedure that I can use to record
how much space is left on my trans logs?
Thanks for any help.
*** Sent via Developersdex http://www.developersdex.com ***Take a look at:
DBCC SQLPERF(LogSpace)
-Sue
On Mon, 23 Jan 2006 12:12:20 -0800, Gerald Hopkins
<geraldh@.sl-tech.net> wrote:
>Does anyone know of a system stored procedure that I can use to record
>how much space is left on my trans logs?
>Thanks for any help.
>*** Sent via Developersdex http://www.developersdex.com ***

Monitoring Trans Log

Does anyone know of a system stored procedure that I can use to record
how much space is left on my trans logs?
Thanks for any help.
*** Sent via Developersdex http://www.codecomments.com ***Take a look at:
DBCC SQLPERF(LogSpace)
-Sue
On Mon, 23 Jan 2006 12:12:20 -0800, Gerald Hopkins
<geraldh@.sl-tech.net> wrote:

>Does anyone know of a system stored procedure that I can use to record
>how much space is left on my trans logs?
>Thanks for any help.
>*** Sent via Developersdex http://www.codecomments.com ***|||Thanks, Sue. This looks like what I need!
*** Sent via Developersdex http://www.codecomments.com ***

monitoring tool

Which real-time monitoring toll you use for SQLServer ?
thanks
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200605/1What do you want to monitor? Perfmon (Performance Monitor) is
extremely useful for looking at hardware stats and certain critical SQL
metrics. The sysprocesses table is full of good info about what's
running in your SQL environment.|||We use NetIQ for monitoring critical SQL Server conditions such as SQL Serve
r
severe errors, stopped SQL instance, SQL instance restart, database that
can't be used, port binding failure, SQL instance that has changed its
cluster owner node, and so on.
Linchi
"jkostic via droptable.com" wrote:

> Which real-time monitoring toll you use for SQLServer ?
> thanks
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200605/1
>|||We use a sweet of tools from Compuware.

monitoring tool

Which real-time monitoring toll you use for SQLServer ?
thanks
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1What do you want to monitor? Perfmon (Performance Monitor) is
extremely useful for looking at hardware stats and certain critical SQL
metrics. The sysprocesses table is full of good info about what's
running in your SQL environment.|||We use NetIQ for monitoring critical SQL Server conditions such as SQL Server
severe errors, stopped SQL instance, SQL instance restart, database that
can't be used, port binding failure, SQL instance that has changed its
cluster owner node, and so on.
Linchi
"jkostic via SQLMonster.com" wrote:
> Which real-time monitoring toll you use for SQLServer ?
> thanks
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1
>|||We use a sweet of tools from Compuware.sql

Monitoring table size

We have a table whose size we wish to keep below 200 records. My thought is
to run a query every minute via SQL agent to get the record count. What I am
wondering is if it is possible to log the result of the query as a perfmon
counter so that we can both graph it realtime in perfmon and also alert off
it in Microsoft Operations Manager.
Thanks,
Mark
"Mark Murphy" <viosk@.newsgroup.nospam> schrieb im Newsbeitrag
news:7AE5DEC0-1F6C-4D60-AC99-FB50A13EFAD9@.microsoft.com...
> We have a table whose size we wish to keep below 200 records. My
thought is
> to run a query every minute via SQL agent to get the record count. What
I am
> wondering is if it is possible to log the result of the query as a
perfmon
> counter so that we can both graph it realtime in perfmon and also alert
off
> it in Microsoft Operations Manager.
And how will you react on this? If there's some action that lends itself
to automation, then a trigger is probably the most appropriate means.
That way you reduce load on the db and do the checks only when they are
necessary (i.e. on insertion).
Kind regards
robert
|||Hi Mark
In SQL Server 2000, there are user defined counters that can log any value
you send as a parameter. For example, if you had this statement: EXEC
sp_user_counter1 10, you could watch SQL Server User Counter 1 in a perfmon
graph, and see the value 10.
But you have to explicitly call the procedure to set the value, and you
could do that in a trigger every time the data in the table changes. The
trigger would do a select count(*), save the result into a variable, and
pass the variable to the sp_user_counter procedure. The trigger could also
check for the value being over your limit, and take the alerting action,
rather than using SQL Server's alert mechanism.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Mark Murphy" <viosk@.newsgroup.nospam> wrote in message
news:7AE5DEC0-1F6C-4D60-AC99-FB50A13EFAD9@.microsoft.com...
> We have a table whose size we wish to keep below 200 records. My thought
> is
> to run a query every minute via SQL agent to get the record count. What I
> am
> wondering is if it is possible to log the result of the query as a perfmon
> counter so that we can both graph it realtime in perfmon and also alert
> off
> it in Microsoft Operations Manager.
> Thanks,
> Mark
|||Thanks Kalen and Robert,
Exactly the advice I was looking for. We have a sproc that needs to poll
the table for new records periodically (We know it's less efficient, but it's
beyond our control). I'll set the counter there.
-Mark

Monitoring replication status via SNMP

I'm trying to find an SNMP MIB that will let me monitor the replication status of a Microsoft SQL2K database. I know you can send Email alerts from MSSQL, as long as the DB is running under a domain account, but in case that is not an option. Any help w
ould be GREATLY appreciated.
You can configure alerts to execute jobs. Use these jobs to raise SNMP alerts.
AFAIK there are no replication specific MIBs but there is a SQL Server specific one.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Derek Small" wrote:

> I'm trying to find an SNMP MIB that will let me monitor the replication status of a Microsoft SQL2K database. I know you can send Email alerts from MSSQL, as long as the DB is running under a domain account, but in case that is not an option. Any help
would be GREATLY appreciated.
>

Monitoring blocking...

Is there a way to be notified automatically when blocking
occurs (either via net send or e-mail and maybe even have
that written to the NT event log)?
ThanksHi Rob
Blocking happens all the time, but it is usually for so short a period of
time that if you had a notification every time you thing there was another
spam virus. There is no automatic way to e notified about blocking, but here
are some avenues you might consider:
Set up a script to run a loop, that captures sysprocesses output and saves
it in a table. Compare the waittime column from one run to the next, if the
blocked column is >0. If the waittime is above some threshhold, send a
notification.
Set lock timeout to 0 for each connection, so that any time a block occurs,
the process will get an error 1222 and stop. You will have to add code to
retry if needed. You can then set up an alert to notify you when error 1222
occurs, or you could change the definition of error 1222 to include writing
it to the event log. Getting every session to set the lock timeout value may
require changing a lot of application code.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Rob" <rhchin@.hotmail.com> wrote in message
news:02ba01c38f5a$285b5a50$a101280a@.phx.gbl...
> Is there a way to be notified automatically when blocking
> occurs (either via net send or e-mail and maybe even have
> that written to the NT event log)?
> Thanks|||Oops, I also meant to suggest you take a look at these KB articles:
http://support.microsoft.com/default.aspx?scid=kb;en-us;271509&Product=sql2k
INF: How to Monitor SQL Server 2000 Blocking
http://support.microsoft.com/default.aspx?scid=kb;en-us;283725&Product=sql2k
INF: How to View SQL Server 2000 Blocking Data
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:uOe6jK2jDHA.2964@.tk2msftngp13.phx.gbl...
> Hi Rob
> Blocking happens all the time, but it is usually for so short a period of
> time that if you had a notification every time you thing there was another
> spam virus. There is no automatic way to e notified about blocking, but
here
> are some avenues you might consider:
> Set up a script to run a loop, that captures sysprocesses output and saves
> it in a table. Compare the waittime column from one run to the next, if
the
> blocked column is >0. If the waittime is above some threshhold, send a
> notification.
> Set lock timeout to 0 for each connection, so that any time a block
occurs,
> the process will get an error 1222 and stop. You will have to add code to
> retry if needed. You can then set up an alert to notify you when error
1222
> occurs, or you could change the definition of error 1222 to include
writing
> it to the event log. Getting every session to set the lock timeout value
may
> require changing a lot of application code.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Rob" <rhchin@.hotmail.com> wrote in message
> news:02ba01c38f5a$285b5a50$a101280a@.phx.gbl...
> > Is there a way to be notified automatically when blocking
> > occurs (either via net send or e-mail and maybe even have
> > that written to the NT event log)?
> >
> > Thanks
>

Monitor transactional replication.

Hi,
I want via email to monitor transactional replication status such as still
running, have some problem during retry, or re-initialize, etc. instead of
login to Microsoft SQL Server Management Studio in SQL Server 2005 (SP1). I
think those information should get from msdb, does anyone done that before?
Regards,
Chen
Hi Paul,
Thanks a lot for reply.
Can you take about details how to do those alerts?
Regards,
Chen
"Paul Ibison" wrote:

> Shouldn't have to do anything unusual here - you can use the replication
> alerts for this:
> a.. Replication: agent success
> a.. Replication: agent failure
> a.. Replication: agent retry
> and have them send emails.
> HTH,
> Paul Ibison
>
>