Detecting whether table contents have changed

If I start with table1 and take a copy of it, call it table2.
How can I later determine whether there have been any changes have been made to table2 since the copy (so I can determine whether I need to copy back the changes to table1).

I'd like to use a 'last-updated-date' on the table, but I can't seem to find anything like that in the system tables.

I'm considering using:
exec sp_table_validation
to get checksums of table1 and table2 (I have no image, text or ntext fields) so this should work fine, though might be slow (since I'd have to select the full count(*) option).
I'd prefer to use timestamps if they're available.

And no, I don't want to use triggers.

Thanks,

- Andy AbelOther than using to trigger to *log* changes, you would have to do a full table *scan* to find the differences. This sure would take a long time to step through a large table.

detecting whether all items are selected in a multi-value paramete

Is there an easy way to detect whether the user has selected all possible
items in a multi-value parameter? If they've selected everything I want to
put "All Regions" as a label in the header of the report. If they've selected
only some, I want to list the regions they've selected [accomplished by doing
Join(Parameters!Region.Label,", ")]. Since I'd like this to appear in the
header, I can't just compare Parameters!Region.Label.Count to
Count(Fields!Region.Value,"RegionsDataset") because you can't use fields in
the header. (The workaround of adding a hidden field to the body of the
report then referencing it by ReportItems!HiddenField.Value is just too
kludgy for me... plus the hidden field's gotta repeat on every page.)
If anyone has any good suggestions, I'd be interested in hearing them.
The solution I decided upon was builiding a hidden parameter which is
dependent upon the multi-value parameter... it's query figures out if they've
selected all possible options. But it seems silly to have to fire off another
query to the DB to accomplish this.If you know the count of parameters you can use that:
This is something I do in a textbox where I show the parameters selected:
="Containers= " & iif(Parameters!Container.Count > 8," more than 8
Selected",Join(Parameters!Container.Value, ", "))
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"FurmanGG" <FurmanGG@.discussions.microsoft.com> wrote in message
news:709E330A-6E97-4F37-9F2B-50A417CFF15C@.microsoft.com...
> Is there an easy way to detect whether the user has selected all possible
> items in a multi-value parameter? If they've selected everything I want to
> put "All Regions" as a label in the header of the report. If they've
> selected
> only some, I want to list the regions they've selected [accomplished by
> doing
> Join(Parameters!Region.Label,", ")]. Since I'd like this to appear in the
> header, I can't just compare Parameters!Region.Label.Count to
> Count(Fields!Region.Value,"RegionsDataset") because you can't use fields
> in
> the header. (The workaround of adding a hidden field to the body of the
> report then referencing it by ReportItems!HiddenField.Value is just too
> kludgy for me... plus the hidden field's gotta repeat on every page.)
> If anyone has any good suggestions, I'd be interested in hearing them.
> The solution I decided upon was builiding a hidden parameter which is
> dependent upon the multi-value parameter... it's query figures out if
> they've
> selected all possible options. But it seems silly to have to fire off
> another
> query to the DB to accomplish this.|||Bruce-
That would work if the list of items in the multi-value parameter was a
constant set. But the list can change over time, so I can't hard code it to a
specific count. Any other suggestions? Thanks for the reply, though.
"Bruce L-C [MVP]" wrote:
> If you know the count of parameters you can use that:
> This is something I do in a textbox where I show the parameters selected:
> ="Containers= " & iif(Parameters!Container.Count > 8," more than 8
> Selected",Join(Parameters!Container.Value, ", "))
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "FurmanGG" <FurmanGG@.discussions.microsoft.com> wrote in message
> news:709E330A-6E97-4F37-9F2B-50A417CFF15C@.microsoft.com...
> > Is there an easy way to detect whether the user has selected all possible
> > items in a multi-value parameter? If they've selected everything I want to
> > put "All Regions" as a label in the header of the report. If they've
> > selected
> > only some, I want to list the regions they've selected [accomplished by
> > doing
> > Join(Parameters!Region.Label,", ")]. Since I'd like this to appear in the
> > header, I can't just compare Parameters!Region.Label.Count to
> > Count(Fields!Region.Value,"RegionsDataset") because you can't use fields
> > in
> > the header. (The workaround of adding a hidden field to the body of the
> > report then referencing it by ReportItems!HiddenField.Value is just too
> > kludgy for me... plus the hidden field's gotta repeat on every page.)
> >
> > If anyone has any good suggestions, I'd be interested in hearing them.
> >
> > The solution I decided upon was builiding a hidden parameter which is
> > dependent upon the multi-value parameter... it's query figures out if
> > they've
> > selected all possible options. But it seems silly to have to fire off
> > another
> > query to the DB to accomplish this.
>
>|||If your list is based on a query you could have a query that returns a count
and then you can use that count to compare to.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"FurmanGG" <FurmanGG@.discussions.microsoft.com> wrote in message
news:EAFC9B4C-3371-4159-8917-4CAA8595FC7E@.microsoft.com...
> Bruce-
> That would work if the list of items in the multi-value parameter was a
> constant set. But the list can change over time, so I can't hard code it
> to a
> specific count. Any other suggestions? Thanks for the reply, though.
> "Bruce L-C [MVP]" wrote:
>> If you know the count of parameters you can use that:
>> This is something I do in a textbox where I show the parameters selected:
>> ="Containers= " & iif(Parameters!Container.Count > 8," more than 8
>> Selected",Join(Parameters!Container.Value, ", "))
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "FurmanGG" <FurmanGG@.discussions.microsoft.com> wrote in message
>> news:709E330A-6E97-4F37-9F2B-50A417CFF15C@.microsoft.com...
>> > Is there an easy way to detect whether the user has selected all
>> > possible
>> > items in a multi-value parameter? If they've selected everything I want
>> > to
>> > put "All Regions" as a label in the header of the report. If they've
>> > selected
>> > only some, I want to list the regions they've selected [accomplished by
>> > doing
>> > Join(Parameters!Region.Label,", ")]. Since I'd like this to appear in
>> > the
>> > header, I can't just compare Parameters!Region.Label.Count to
>> > Count(Fields!Region.Value,"RegionsDataset") because you can't use
>> > fields
>> > in
>> > the header. (The workaround of adding a hidden field to the body of the
>> > report then referencing it by ReportItems!HiddenField.Value is just too
>> > kludgy for me... plus the hidden field's gotta repeat on every page.)
>> >
>> > If anyone has any good suggestions, I'd be interested in hearing them.
>> >
>> > The solution I decided upon was builiding a hidden parameter which is
>> > dependent upon the multi-value parameter... it's query figures out if
>> > they've
>> > selected all possible options. But it seems silly to have to fire off
>> > another
>> > query to the DB to accomplish this.
>>

Detecting whether a trace is running

We have had some problems with getting a trace restarted after capturing the
trace output to a table. Is there a query or API call that can determine i
f a server trace is running?Randy,
I believe that fn_trace_getinfo (0) will give you what you need.
Russell Fields
"Randy Hersom" <anonymous@.discussions.microsoft.com> wrote in message
news:3FAA134B-9472-4AC7-B619-128FD7B5B6B0@.microsoft.com...
> We have had some problems with getting a trace restarted after capturing
the trace output to a table. Is there a query or API call that can
determine if a server trace is running?
>sql

Detecting when an user connects to a database

Greetings,
Is there any way to detect when a user connects to my database?
I mean, I want to know when a user connects to my database and when he/she
disconnects, and do something when this happens (a trigger, I guess).
Any ideas'
Thanks!Hi
If the user is only connecting to your database through a bespoke
application, then you will have control of this. If you don't have this
option you may want to check out the C2 auditing abilities and processing
the audit file, using SQL profiler or poling the sysprocesses table.
John
"Arnau Font" <afont@.nospam-ipssoft.com> wrote in message
news:uATVAOA5DHA.2576@.TK2MSFTNGP11.phx.gbl...

quote:

---

> Greetings,
> Is there any way to detect when a user connects to my database?
> I mean, I want to know when a user connects to my database and when he/she
> disconnects, and do something when this happens (a trigger, I guess).
> Any ideas'
> Thanks!
>

---

|||HI,
Simple option is enable Audit level (ALL)
How to enable audit,
1. In Enterprise manager , select the server name and right click and select
properties
2. Select the security tab and select Audit level as "ALL"
3. Restart SQL server Service
From now all the successful Login and Failure attempts will stored in SQL
server errorlog, but we can not set a trigger for this.
I belive to set a trigger we may need write our own custom trigger in
sysprocesses table.
Thanks
Hari
MCDBA
"Arnau Font" <afont@.nospam-ipssoft.com> wrote in message
news:uATVAOA5DHA.2576@.TK2MSFTNGP11.phx.gbl...

quote:

---

> Greetings,
> Is there any way to detect when a user connects to my database?
> I mean, I want to know when a user connects to my database and when he/she
> disconnects, and do something when this happens (a trigger, I guess).
> Any ideas'
> Thanks!
>

---

detecting Update Conflict

When a user try to update the record I use timestamp field (upsize_ts) to
figure out if data was modified by other user.
However I found out @.@.DBTS may cause problem in Multi user senario and may
not return correct Tiemstamp value as it is not the timestamp for the curren
t
operation but for the database is there any inexpensive solutin to resolve
this.
Thanks
Tanweer
CREATE PROCEDURE [dbo].[oan_300_PEOPLE_PHONE_Update]
@.rowversion timestamp OUTPUT,
@.people_phone_id int,
@.offsysid varchar(5),
@.people_id int=Null,
@.sequence_id smallint=Null,
@.code_type varchar(5)=Null,
@.code_value varchar(50)=Null,
@.modify_operator varchar(15)=Null,
@.upsize_ts timestamp=Null
AS
SET NOCOUNT OFF
DECLARE @.errormsg varchar(1000)
DECLARE @.errcode int
DECLARE @.rowcount int
DECLARE @.modify_date datetime
SET @.modify_date = GetDate()
UPDATE [dbo].[PEOPLE_PHONE] SET
[people_id] = case when @.people_id is null then people_id else @.people_id
end,
[sequence_id] = case when @.sequence_id is null then sequence_id else
@.sequence_id end,
[code_type] = case when @.code_type is null then code_type else @.code_type
end,
[code_value] = case when @.code_value is null then code_value else
@.code_value end,
[modify_date] = case when @.modify_date is null then modify_date else
@.modify_date end,
[modify_operator] = case when @.modify_operator is null then modify_operator
else @.modify_operator end
WHERE
[people_phone_id] = @.people_phone_id
AND [offsysid] = @.offsysid
AND [upsize_ts] = @.rowversion
SELECT @.errcode = @.@.ERROR, @.RowCount = @.@.ROWCOUNT ,@.rowversion=@.@.DBTS
--Use it to check the value of Timestamp column
--print 'Timestamp' + convert(varchar(50),@.rowversion)
select @.rowversion
IF(@.errcode = 0)
BEGIN
IF(@.RowCount = 0)
BEGIN
IF EXISTS(SELECT * FROM [dbo].[People_PHONE] WHERE [people_phone_id] =
@.people_phone_id AND [offsysid] = @.offsysid)
BEGIN
RAISERROR('Row not updated because it was changed', 15, 1)
RETURN 50003
END
ELSE
BEGIN
RAISERROR('Row is gone', 15, 1)
RETURN 50004
END
END
ELSE
PRINT 'Row updated'
END
ELSE
PRINT 'Error'
RETURN @.errcode
GOeasist of all..
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN
ALL OPERATIONS
COMMIT TRAN
"Tanweer" wrote:

> When a user try to update the record I use timestamp field (upsize_ts) to
> figure out if data was modified by other user.
> However I found out @.@.DBTS may cause problem in Multi user senario and may
> not return correct Tiemstamp value as it is not the timestamp for the curr
ent
> operation but for the database is there any inexpensive solutin to resolve
> this.
> Thanks
> Tanweer
>
>
> CREATE PROCEDURE [dbo].[oan_300_PEOPLE_PHONE_Update]
> @.rowversion timestamp OUTPUT,
> @.people_phone_id int,
> @.offsysid varchar(5),
> @.people_id int=Null,
> @.sequence_id smallint=Null,
> @.code_type varchar(5)=Null,
> @.code_value varchar(50)=Null,
> @.modify_operator varchar(15)=Null,
> @.upsize_ts timestamp=Null
> AS
> SET NOCOUNT OFF
> DECLARE @.errormsg varchar(1000)
> DECLARE @.errcode int
> DECLARE @.rowcount int
> DECLARE @.modify_date datetime
> SET @.modify_date = GetDate()
>
> UPDATE [dbo].[PEOPLE_PHONE] SET
> [people_id] = case when @.people_id is null then people_id else @.people_id
> end,
> [sequence_id] = case when @.sequence_id is null then sequence_id else
> @.sequence_id end,
> [code_type] = case when @.code_type is null then code_type else @.code_type
> end,
> [code_value] = case when @.code_value is null then code_value else
> @.code_value end,
> [modify_date] = case when @.modify_date is null then modify_date else
> @.modify_date end,
> [modify_operator] = case when @.modify_operator is null then modify_operat
or
> else @.modify_operator end
> WHERE
> [people_phone_id] = @.people_phone_id
> AND [offsysid] = @.offsysid
> AND [upsize_ts] = @.rowversion
> SELECT @.errcode = @.@.ERROR, @.RowCount = @.@.ROWCOUNT ,@.rowversion=@.@.DBTS
> --Use it to check the value of Timestamp column
> --print 'Timestamp' + convert(varchar(50),@.rowversion)
> select @.rowversion
> IF(@.errcode = 0)
> BEGIN
> IF(@.RowCount = 0)
> BEGIN
> IF EXISTS(SELECT * FROM [dbo].[People_PHONE] WHERE [people_phone_id] =
> @.people_phone_id AND [offsysid] = @.offsysid)
> BEGIN
> RAISERROR('Row not updated because it was changed', 15, 1)
> RETURN 50003
> END
> ELSE
> BEGIN
> RAISERROR('Row is gone', 15, 1)
> RETURN 50004
> END
> END
> ELSE
> PRINT 'Row updated'
> END
> ELSE
> PRINT 'Error'
>
> RETURN @.errcode
> GO
>|||A doubt, why do you bother in this scnario. There is only one update and no
select and no transaction. You will never get an erronous update in this cas
e.
"Tanweer" wrote:

> When a user try to update the record I use timestamp field (upsize_ts) to
> figure out if data was modified by other user.
> However I found out @.@.DBTS may cause problem in Multi user senario and may
> not return correct Tiemstamp value as it is not the timestamp for the curr
ent
> operation but for the database is there any inexpensive solutin to resolve
> this.
> Thanks
> Tanweer
>
>
> CREATE PROCEDURE [dbo].[oan_300_PEOPLE_PHONE_Update]
> @.rowversion timestamp OUTPUT,
> @.people_phone_id int,
> @.offsysid varchar(5),
> @.people_id int=Null,
> @.sequence_id smallint=Null,
> @.code_type varchar(5)=Null,
> @.code_value varchar(50)=Null,
> @.modify_operator varchar(15)=Null,
> @.upsize_ts timestamp=Null
> AS
> SET NOCOUNT OFF
> DECLARE @.errormsg varchar(1000)
> DECLARE @.errcode int
> DECLARE @.rowcount int
> DECLARE @.modify_date datetime
> SET @.modify_date = GetDate()
>
> UPDATE [dbo].[PEOPLE_PHONE] SET
> [people_id] = case when @.people_id is null then people_id else @.people_id
> end,
> [sequence_id] = case when @.sequence_id is null then sequence_id else
> @.sequence_id end,
> [code_type] = case when @.code_type is null then code_type else @.code_type
> end,
> [code_value] = case when @.code_value is null then code_value else
> @.code_value end,
> [modify_date] = case when @.modify_date is null then modify_date else
> @.modify_date end,
> [modify_operator] = case when @.modify_operator is null then modify_operat
or
> else @.modify_operator end
> WHERE
> [people_phone_id] = @.people_phone_id
> AND [offsysid] = @.offsysid
> AND [upsize_ts] = @.rowversion
> SELECT @.errcode = @.@.ERROR, @.RowCount = @.@.ROWCOUNT ,@.rowversion=@.@.DBTS
> --Use it to check the value of Timestamp column
> --print 'Timestamp' + convert(varchar(50),@.rowversion)
> select @.rowversion
> IF(@.errcode = 0)
> BEGIN
> IF(@.RowCount = 0)
> BEGIN
> IF EXISTS(SELECT * FROM [dbo].[People_PHONE] WHERE [people_phone_id] =
> @.people_phone_id AND [offsysid] = @.offsysid)
> BEGIN
> RAISERROR('Row not updated because it was changed', 15, 1)
> RETURN 50003
> END
> ELSE
> BEGIN
> RAISERROR('Row is gone', 15, 1)
> RETURN 50004
> END
> END
> ELSE
> PRINT 'Row updated'
> END
> ELSE
> PRINT 'Error'
>
> RETURN @.errcode
> GO
>|||Don't use @.@.dbts, but rather retrieve the actual value for the updated row.
If more than one row is updated at a time, then you'd have to store the
values in a temporary table (e.g. a table variable) or use the OUTPUT clause
in SQL 2005.
ML
http://milambda.blogspot.com/|||I guess I have to go with ML syggestion there is no easy way out.
May be a wish for SQL 2005 Service Pack2
Thanks
Tanweer
"ML" wrote:

> Don't use @.@.dbts, but rather retrieve the actual value for the updated row
.
> If more than one row is updated at a time, then you'd have to store the
> values in a temporary table (e.g. a table variable) or use the OUTPUT clau
se
> in SQL 2005.
>
> ML
> --
> http://milambda.blogspot.com/|||are you using sql server 2005'
--
"Tanweer" wrote:
> I guess I have to go with ML syggestion there is no easy way out.
> May be a wish for SQL 2005 Service Pack2
> Thanks
> Tanweer
> "ML" wrote:
>

Detecting the render method using an expression

Is there a way of detecting (in an expression in the report) the render
method that is being used to render the report?
Scenario
I have created a custom assembly that sits in the footer of a report and
writes the total number of pages to a database table. However, the total
number of pages varies depending on the render method used, and I need to
capture this render method along with the total pages.
I can't pass the render method into the report as a parameter because, even
when rendering with a single render format such as PDF the report seems to
render twice:
string format = "PDF";
results = viewer.ServerReport.Render(format, deviceInfo, out mimeType, out
encoding, out fileNameExtension, out streamIDs, out warnings);
The above code inserts two records into the database table (once as either
XML or HTML and then as PDF). The first record reflects the total pages
displayed in the viewer, and the second record reflects totalPages as if
exported into PDF.
If we could detect which format was being used at render time then we could
get around this double rendering problem.Hello Stu,
I undertstand that you want to pass the render type in the expression.
Well you could not pass the render type in the expression.
Could you please let me know how your custom code to insert the total page
information?
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Wei Lu,
The expression in the report is:
--
="Page "&Globals!PageNumber & " of "
&LogAttributes.CommonFunctions.LogReportDetails(Globals!ReportName,
Globals!TotalPages, Globals!PageNumber, Parameters!SnapshotID.Value,
Parameters!SchemaName.Value,
Parameters!Database.Value,Parameters!UserID.Value,Parameters!Password.Value,
Parameters!Server.Value)
--
And the code in the custom assembly is:
--
public static int LogReportDetails(string reportName, int
totalNumberOfPages, int currentPage, int snapShotID, string schema,
string database, string userID, string password, string server)
{
bool testRun = false;
if (currentPage == totalNumberOfPages) { testRun = true; } else
{ testRun = false; }
if (testRun)
{
int totalNumPages;
bool ok = true;
TableOfContents toc = new TableOfContents();
toc.Description = reportName;
toc.TotalNumberOfPages = totalNumberOfPages;
toc.CurrentPage = currentPage;
toc.SnapshotID = snapShotID;
toc.SchemaName = schema;
toc.Database = database;
toc.UserID = userID;
toc.Password = password;
toc.Server = server;
SqlTOCDalc dalc = new SqlTOCDalc();
totalNumPages = dalc.InsertTocRow(toc, ok);
return totalNumPages;
}
else
{
return totalNumberOfPages;
}
}
--
Thanks
Stu
"Wei Lu [MSFT]" wrote:
> Hello Stu,
> I undertstand that you want to pass the render type in the expression.
> Well you could not pass the render type in the expression.
> Could you please let me know how your custom code to insert the total page
> information?
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hello Stu,
Well, unfortunately, you could not refer the render method in the custom
code. And the only workaround I thought is that you may need to add the
custom application to call the report instead of the access the report via
the web browser.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Detecting the network UserID

Hey guys,
I would like to determine the network id of the current user so that I can
pass that value to a stored procedure that the report references.
I tried add the following code in the Code tab of the Report Properties tab:
Function GetUserID() AS String
Return System.Environment.UserName
End Function
I then referenced this function as the default value for the @.vcUserId
argument that the stored procedure wants by assigning =Code.GetUserID() as
the Non-queried default value for the report parameter.
This approach works well when I preview the report in Visual Studio.
Unfortunately, it fails when I deploy the report to the server.
Can anyone offer some suggestions on how to approach this issue?
Thanks in advance,
-JimI'd guess that the reason this doesn't work is because when you run it
in VS.net the application is running as you or some other acceptable
local user
(http://msdn2.microsoft.com/en-us/library/system.environment.username.aspx).
When it runs online it probably can't execute or it returns the
ASP.NET worker process's user or something similar and not useful. I
know for ASP.NET you can call HttpContext.User.Idenity.Name (or
something similar, I'm working from memory mostly) to see who the
logged in user is, this might work better in report services. Or there
might be a separate way to get the current user from the report manager
that I'm not aware of.|||Use the global variable User!UserID.value
You can get to this with the expression builder. It returns domain\username,
if you don't want the domain then you will need to strip it off.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Terence Tirella" <ttirella@.literate.com> wrote in message
news:1141851303.988200.237700@.u72g2000cwu.googlegroups.com...
> I'd guess that the reason this doesn't work is because when you run it
> in VS.net the application is running as you or some other acceptable
> local user
> (http://msdn2.microsoft.com/en-us/library/system.environment.username.aspx).
> When it runs online it probably can't execute or it returns the
> ASP.NET worker process's user or something similar and not useful. I
> know for ASP.NET you can call HttpContext.User.Idenity.Name (or
> something similar, I'm working from memory mostly) to see who the
> logged in user is, this might work better in report services. Or there
> might be a separate way to get the current user from the report manager
> that I'm not aware of.
>sql

Detecting the INSERT format for DATE database fields?

Assume I got a database resp. table defintion with a DATE field. How do I fi
nd out in which format the
field should be filled in an INSERT SQL statement?
Sometimes when I enter e.g.
INSERT INTO ... VALUES (...., '2005-01-25',....);
or
INSERT INTO ... VALUES (...., '2005-01-25-13.14.15.00',......);
I got an error like:
ORA-01861: literal does not match format string
before I realized that
INSERT INTO ... VALUES (...., '25-01-2005',...);
is the correct format.
TomThomas Jerkins (tomjerk@.hotmail.com) wrote:
: Assume I got a database resp. table defintion with a DATE field. How do I
find out in which format the
: field should be filled in an INSERT SQL statement?
: Sometimes when I enter e.g.
: INSERT INTO ... VALUES (...., '2005-01-25',....);
: or
: INSERT INTO ... VALUES (...., '2005-01-25-13.14.15.00',......);
: I got an error like:
: ORA-01861: literal does not match format string
: before I realized that
: INSERT INTO ... VALUES (...., '25-01-2005',...);
: is the correct format.
You asked in an oracle group, so I'll give the oracle answer.
INSERT INTO ...
VALUES (...., to_date('25-01-2005','DD-MM-YYYY') ,...);
look up to_date for all the options.
This space not for rent.|||Thomas Jerkins wrote:
> Assume I got a database resp. table defintion with a DATE field. How
> do I find out in which format the field should be filled in an INSERT
> SQL statement?
> Sometimes when I enter e.g.
> INSERT INTO ... VALUES (...., '2005-01-25',....);
> or
> INSERT INTO ... VALUES (...., '2005-01-25-13.14.15.00',......);
> I got an error like:
> ORA-01861: literal does not match format string
> before I realized that
> INSERT INTO ... VALUES (...., '25-01-2005',...);
> is the correct format.
> Tom
The only guaranteed formats inserting a date are the following:
select convert(datetime, 'YYYY-MM-DDThh:mm:ss.mmm')
select convert(datetime, 'YYYYMMDD hh:mm:ss.mmm')
All others are subject the locale and risk throwing exceptions or
inserting the incorrect date.
David Gugick
Imceda Software
www.imceda.com|||"Thomas Jerkins" <tomjerk@.hotmail.com> a crit dans le message de
news:cst3bg$osr$05$1@.news.t-online.com...
| Assume I got a database resp. table defintion with a DATE field. How do I
find out in which format
the
| field should be filled in an INSERT SQL statement?
|
| Sometimes when I enter e.g.
|
| INSERT INTO ... VALUES (...., '2005-01-25',....);
| or
| INSERT INTO ... VALUES (...., '2005-01-25-13.14.15.00',......);
|
| I got an error like:
|
| ORA-01861: literal does not match format string
|
| before I realized that
|
| INSERT INTO ... VALUES (...., '25-01-2005',...);
|
| is the correct format.
|
| Tom
|
Never rely on implicit conversion.
Use to_date to explicitly convert your external format to date datatype:
insert into ... values (..., to_date('2005-01-25','YYYY-MM-DD'),...);
or
insert into ... values (..., to_date('2005-01-25-13.14.15.00','YYYY-MM-DD-HH
24.MI.SS.FF2'),...);
or whatever is your external format.
This is the correct way.
Regards
Michel Cadot|||On Sat, 22 Jan 2005 09:37:04 +0100, tomjerk@.hotmail.com (Thomas
Jerkins) wrote:

>Assume I got a database resp. table defintion with a DATE field. How do I f
ind out in which format the
>field should be filled in an INSERT SQL statement?
>Sometimes when I enter e.g.
>INSERT INTO ... VALUES (...., '2005-01-25',....);
>or
>INSERT INTO ... VALUES (...., '2005-01-25-13.14.15.00',......);
>I got an error like:
>ORA-01861: literal does not match format string
>before I realized that
>INSERT INTO ... VALUES (...., '25-01-2005',...);
>is the correct format.
>Tom
You should never rely on the default date format, and always apply the
to_date function to any date literal submitted.
The default date format, btw, is in the docs (no one ever reads them)
and can be queried from nls_session_parameters.
But you should stop developing bad habits and not rely on it. It can
be changed on client level very easily, in which case your application
won't work anymore.
Sybrand Bakker, Senior Oracle DBA|||"Thomas Jerkins" <tomjerk@.hotmail.com> wrote in message
news:cst3bg$osr$05$1@.news.t-online.com...
> Assume I got a database resp. table defintion with a DATE field. How do I
find out in which format the
> field should be filled in an INSERT SQL statement?
> Sometimes when I enter e.g.
> INSERT INTO ... VALUES (...., '2005-01-25',....);
> or
> INSERT INTO ... VALUES (...., '2005-01-25-13.14.15.00',......);
> I got an error like:
> ORA-01861: literal does not match format string
> before I realized that
> INSERT INTO ... VALUES (...., '25-01-2005',...);
> is the correct format.
> Tom
>
Please don't cross post Oracle problems in other newsgroups. But if you were
using DB2, it would take the date in either order.|||"Thomas Jerkins" <tomjerk@.hotmail.com> wrote in message
news:cst3bg$osr$05$1@.news.t-online.com...
> Assume I got a database resp. table defintion with a DATE field. How do I
> find out in which format the
> field should be filled in an INSERT SQL statement?
> Sometimes when I enter e.g.
> INSERT INTO ... VALUES (...., '2005-01-25',....);
> or
> INSERT INTO ... VALUES (...., '2005-01-25-13.14.15.00',......);
> I got an error like:
> ORA-01861: literal does not match format string
> before I realized that
> INSERT INTO ... VALUES (...., '25-01-2005',...);
> is the correct format.
> Tom
>
you can check the setting of NLS_DATE_FORMAT in the V$NLS_PARAMETERS view
however, you should not rely on the default format because it can be changed
by the dba or by other code that you run
1) if using 10g, use the DATE keyword to specify an ansi date literal, ie
DATE '2005-02-22' (no time element)
2) for full oracle portability and reliability either use TO_CHAR with a
specific date format, ie, TO_CHAR('022205 15:22', 'RRMMDD HH24:MI')
3) or, if you could explicitly set the format for your session with ALTER
SESSION SET NLS_DATE_FORMAT = 'the format of your choice'
check the SQL manual at tahiti.oracle.com for addtional date formats
and _please_ ignore any examples that you ever see, including in Oracle docs
and courseware and OCP stuff, that assume a specific date format without
using one of these three techniques|||What is Oracle?|||On 22 Jan 2005 10:03:57 -0800, Max wrote:

>What is Oracle?
http://en.wikipedia.org/wiki/Oracle
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||On Sat, 22 Jan 2005 09:37:04 +0100, Thomas Jerkins wrote:

>Assume I got a database resp. table defintion with a DATE field. How do I f
ind out in which format the
>field should be filled in an INSERT SQL statement?
Hi Tom,
Most of the answers you got relate to Oracle. The error code indicates
that that's what you want. But since you posted in a SQL Server newsgroup,
the correct answer for SQL Server is:
a) Use one of the unambiguous formats:
- yyyymmdd for date only
- yyyy-mm-ddThh:mm:ss for date plus time
- yyyy-mm-ddThh:mm:ss.ttt for date plus time including milliseconds
b) Use explicit conversion using a style parameter (look up CONVERT and
CAST in Books Online for a list of all supported styles)
c) Use any format that matches your regional settings and pray that nobody
changes the regional setting and that you enver have to export your code
to other countries.
Actually, I recommend using only one of the first two options. Option a
takes the least number of keystrokes.

>before I realized that
>INSERT INTO ... VALUES (...., '25-01-2005',...);
>is the correct format.
Not for SQL Server, it isn't. Even though it won't fail for all settings,
it will fail for some.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Detecting Tables with no recent activity

Is there an efficent way to determine which tables in a SQL Server database
have had no activity reccently. This includes inserts, updates, deleted
and/or selects.
Are there any tools or techniques to find these tables?
All help appreciated!
Tim O wrote:
> Is there an efficent way to determine which tables in a SQL Server
> database have had no activity reccently. This includes inserts,
> updates, deleted and/or selects.
> Are there any tools or techniques to find these tables?
> All help appreciated!
Not inherently, as far as I know. The system tables do not track changes
to underlying objects. You can easily implement some very simple
triggers on the tables you are trying to track using something like the
following. This implementation tracks changes to a table at most once
each day. Using the Ignore_dup_key option on the clustered index tells
SQL Server to ignore duplicates, preventing you from having to manually
check the table each time. You could, of course, improve on this concept
and add a user column and add the suser_sname() value to it and the
index.
CREATE TABLE [testing] (
[col1] [int] NULL)
go
create table TableAccess(TblNm nvarchar(128) NOT NULL, AccessDate
char(8) NOT NULL)
go
create unique clustered index TableAccess_idx on TableAccess (TblNm,
AccessDate) with ignore_dup_key
go
create trigger testing_change on testing
for insert, update, delete
as
Begin
insert into TableAccess (TblNm, AccessDate) Values ('testing',
CONVERT(char(8), getdate(), 112))
End
go
insert into testing values (5)
insert into testing values (6)
insert into testing values (7)
David Gugick
Imceda Software
www.imceda.com
|||"Tim O" wrote:

> Is there an efficent way to determine which tables in a SQL Server database
> have had no activity reccently. This includes inserts, updates, deleted
> and/or selects.
> Are there any tools or techniques to find these tables?
> All help appreciated!
The use of TRIGGERS will not detect SELECTS that are executed on a TABLE as
far as I know!
I need an EFFICINIENT WAY to detect table that are neither read for written
to.
I am also concerned with the overheads that may be placed on the system.
There are a large number of tables and a high degree of daily activity!
Cheers Tim O
|||Tim O wrote:
> The use of TRIGGERS will not detect SELECTS that are executed on a
> TABLE as far as I know!
> I need an EFFICINIENT WAY to detect table that are neither read for
> written to.
> I am also concerned with the overheads that may be placed on the
> system. There are a large number of tables and a high degree of daily
> activity!
> Cheers Tim O
If you are using stored procedures, you can log access from there, even
on SELECTs. The overhead is minimal on insert activity. You can place
the table on a secondary filegroup or in another database, if necessary.
David Gugick
Imceda Software
www.imceda.com
|||Tim,
There is a column in sysindexes for the object that keeps a count of
modifications to the table. The column is rowmodctr. SQL uses this to keep
track of when to run Update Stats when "Auto Update Stats" is on. If after
running update stats for that table, the value rises above 0, then there are
modifications to it. For more info please see:
http://support.microsoft.com/default...en-us%3B195565
As far as Selects, you can run profiler (trace) to see if it is called using
the Filter "TextData". You can also see if it is embedded in any stored
procs, triggers via sp_depends <table_name>. This won't indicate any
references from outside the current DB, tho.
Hope some of this helps.
Steve
"Tim O" <TimO@.discussions.microsoft.com> wrote in message
news:31F8C451-62F7-480F-9362-736E07698FFF@.microsoft.com...[vbcol=seagreen]
>
> "Tim O" wrote:
database
> The use of TRIGGERS will not detect SELECTS that are executed on a TABLE
as
> far as I know!
> I need an EFFICINIENT WAY to detect table that are neither read for
written
> to.
> I am also concerned with the overheads that may be placed on the system.
> There are a large number of tables and a high degree of daily activity!
> Cheers Tim O

Detecting status of snapshot agent...

Is there an easy way to determine the status/progress of the snapshot agent
for a particular merge publication?
Troy - much the same as the question for your merge agent:
sp_help_job will do it on an ad-hoc basis, but if you need to do something
as a result of the job status, you could modify this script to do it:
http://www.replicationanswers.com/Downloads/KillRunningJobs.txt
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Detecting SQL Servers on a network

I need to show a list of detected SQL Server 2000 instances installed on a L
AN. I would like to know if anyone knows how to list the servers, similar t
o what Enterprise Manager does with its Server Registration Wizard.
ThanksHi,
From command prompt execute,
ISQL -L
Thanks
Hari
MCDBA
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:983A2E30-10E0-4E3A-BF35-AD0DA6FC7E7A@.microsoft.com...
> I need to show a list of detected SQL Server 2000 instances installed on a
LAN. I would like to know if anyone knows how to list the servers, similar
to what Enterprise Manager does with its Server Registration Wizard.
> Thanks|||Thanks,
Do you have a method that can be done programmatically, rather than from the
command prompt? I need to build this capability into an application for our
developers.
Mike|||SQLDMO
>--Original Message--
>Thanks,
>Do you have a method that can be done programmatically,
rather than from the command prompt? I need to build this
capability into an application for our developers.
>Mike
>.
>|||I believe Enterprise Manager uses the SQLBrowseConnect ODBC function to
enumerate the SQL Servers on a network.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||Hi Mike
It would help a great deal if you can supply me with the programming
language that you want this done in.
Regards
Wimpie
"Mike" <anonymous@.discussions.microsoft.com> wrote in message
news:983A2E30-10E0-4E3A-BF35-AD0DA6FC7E7A@.microsoft.com...
> I need to show a list of detected SQL Server 2000 instances installed on a
LAN. I would like to know if anyone knows how to list the servers, similar
to what Enterprise Manager does with its Server Registration Wizard.
> Thanks|||Me again...
I include the source of one of my VB .Net projects that does this. The is
no need to include any SQL objects in the project since the software
emulates the SQLDMO object in broadcasting a request for identification on
the network.
Hope it helps
Wimpie
----
---
Public Structure ServerInfo
Friend ServerName As String
Friend InstanceName As String
Friend IsClustered As Boolean
Friend Version As String
Friend TCPPort As Long
Friend IPAddress As String
End Structure
Public Sub Start()
Dim objSQLServers() As ServerInfo = EnumSQLServer()
End Sub
Friend Function EnumSQLServer() As ServerInfo()
Dim objUDPClient As New System.Net.Sockets.UdpClient
Dim objRemoteIP As System.Net.IPEndPoint
Dim objAllServers() As ServerInfo
Dim bytData() As Byte = {2, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
0, 0, 0, 0}
Dim bytReceived() As Byte
Dim strInfo() As String
Dim strHostHeaders() As String
Dim booDone As Boolean = False
Dim i As Long
Dim x As Int16
ReDim strHostHeaders(0)
objUDPClient.Send(bytData, 18, "255.255.255.255", "1434")
While booDone = False
Try
bytReceived = objUDPClient.Receive(objRemoteIP)
Catch
booDone = True
Exit While
End Try
If strHostHeaders(strHostHeaders.Length - 1) <> "" Then
ReDim Preserve strHostHeaders(strHostHeaders.Length)
End If
strHostHeaders(strHostHeaders.Length - 1) =
Mid(System.Text.ASCIIEncoding.ASCII.GetString(bytReceived), 4)
strHostHeaders(strHostHeaders.Length - 1) &= ";IPADDRESS;" &
objRemoteIP.Serialize.Item(4).ToString & "." &
objRemoteIP.Serialize.Item(5).ToString & "." &
objRemoteIP.Serialize.Item(6).ToString & "." &
objRemoteIP.Serialize.Item(7).ToString
If strHostHeaders.Length > 1 Then
If strHostHeaders(strHostHeaders.Length - 1) =
strHostHeaders(strHostHeaders.Length - 2) Then ReDim Preserve
strHostHeaders(strHostHeaders.Length - 2)
End If
End While
objUDPClient.Close()
objUDPClient = Nothing
ReDim objAllServers(strHostHeaders.Length - 1)
For i = 0 To strHostHeaders.Length - 1
strInfo = Split(strHostHeaders(i), ";")
For x = 0 To strInfo.Length - 1
Select Case LCase(strInfo(x))
Case "servername"
objAllServers(i).ServerName = strInfo(x + 1)
Case "instancename"
objAllServers(i).InstanceName = strInfo(x + 1)
Case "isclustered"
objAllServers(i).IsClustered = IIf(LCase(strInfo(x +
1)) = "no", False, True)
Case "version"
objAllServers(i).Version = strInfo(x + 1)
Case "tcp"
objAllServers(i).TCPPort = CLng(strInfo(x + 1))
Case "ipaddress"
objAllServers(i).IPAddress = strInfo(x + 1)
End Select
Next
Next
Return objAllServers
End Function

Detecting SQL Server 2000 vs 2005 instance

I have the following two questions:
1) I can use the ListAvailableSQLServers methods in SQL-DMO to get a list of
all the available sql servers (in the form of Namelist) but how can I detect
which ones are SQL Server 2000/MSDE 2000 instances and which ones are 2005
instances programmatically either using VB6 or C#?
2) If I have a few machines in my network and one of them has an instance
called
"mycomputer/myinstance", the method "ListAvailableSQLServers" does not list
it. Could that be related to the windows firewall being turned on?
--
ANeelimaYou'd want to use smo instead of dmo to interact with both sql2k5 and sql2k.
Take a look at Server.PingSqlServerVersion() method under
Microsoft.SqlServer.Management.Smo namespace.
--
-oj
"ANeelima" <neelima@.newsgroups.nospam> wrote in message
news:B178E81E-C9DA-46FC-B0FB-504537B030D3@.microsoft.com...
>I have the following two questions:
> 1) I can use the ListAvailableSQLServers methods in SQL-DMO to get a list
> of
> all the available sql servers (in the form of Namelist) but how can I
> detect
> which ones are SQL Server 2000/MSDE 2000 instances and which ones are 2005
> instances programmatically either using VB6 or C#?
> 2) If I have a few machines in my network and one of them has an instance
> called
> "mycomputer/myinstance", the method "ListAvailableSQLServers" does not
> list
> it. Could that be related to the windows firewall being turned on?
> --
> ANeelima|||Hi,
Thanks for your post!
From your description, I understand that:
Your 1st question was that you wanted to detect each SQL Server version
corresponding to each SQL Server instances.
Your 2nd question was that you found you couldn't get the list of all SQL
Server instances in your network via ListAvailableSQLServers.
If I have misunderstood, please let me know.
For the first question, I recommend you:
1) Create a SQLDMO.SQLServer object.
2) Connect to the SQL Server by server name.
3) Get the version information by the property SQLServer.VersionString or
SQLServer.VersionMajor.
For the second question, I recommend you check your network settings and
ensure your application machine can access any SQL Server instance.
I write a sample and get the named instances that my machine can access in
network:
SQLDMO.Application app = new SQLDMO.ApplicationClass();
SQLDMO.NameList nl = app.ListAvailableSQLServers();
for (int i = 0; i < nl.Count; ++i)
{
string s = nl.Item(i);
this.label1.Text += s + "\n";
}
If you have any other concerns, please feel free to let me know. It's my
pleasure to be of assistance.
+++++++++++++++++++++++++++
Charles Wang
Microsoft Online Partner Support
+++++++++++++++++++++++++++
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/technicalsupport/supportoverview/40010469
Others:
https://partner.microsoft.com/US/technicalsupport/supportoverview/
If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/default.aspx?scid=%2finternational.aspx.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks.
My application is in VB6.0. How can I use SMO from VB6.0? I can't
seem to find it in the list of references in VB6.0. What is the reference
name?
ANeelima
"oj" wrote:
> You'd want to use smo instead of dmo to interact with both sql2k5 and sql2k.
> Take a look at Server.PingSqlServerVersion() method under
> Microsoft.SqlServer.Management.Smo namespace.
> --
> -oj
>
> "ANeelima" <neelima@.newsgroups.nospam> wrote in message
> news:B178E81E-C9DA-46FC-B0FB-504537B030D3@.microsoft.com...
> >I have the following two questions:
> >
> > 1) I can use the ListAvailableSQLServers methods in SQL-DMO to get a list
> > of
> > all the available sql servers (in the form of Namelist) but how can I
> > detect
> > which ones are SQL Server 2000/MSDE 2000 instances and which ones are 2005
> > instances programmatically either using VB6 or C#?
> >
> > 2) If I have a few machines in my network and one of them has an instance
> > called
> > "mycomputer/myinstance", the method "ListAvailableSQLServers" does not
> > list
> > it. Could that be related to the windows firewall being turned on?
> >
> > --
> > ANeelima
>
>|||Well, I was hoping that I don't have to connect to the server to get the
version name.
I wanted to simply use ListAvailableSQLServers, get the namelist, loop
through the servers and find the version without connecting.
It appears like I can do that with SMO using something like
----
DataTable dataTable = SmoApplication.EnumAvailableSqlServers();
foreach (DataRow dataRow in dataTable.Rows )
{
row.Append(
"Server: " + dataRow["Name"] + " Version: " + dataRow["Version"]);
}
Console.WriteLine(row.ToString());
----
But I can't do that with SQLDMO.
My application is in VB6.0 and if I need to use SMO in VB6.0 how can I do
that?
--
ANeelima
"Charles Wang[MSFT]" wrote:
> Hi,
> Thanks for your post!
> From your description, I understand that:
> Your 1st question was that you wanted to detect each SQL Server version
> corresponding to each SQL Server instances.
> Your 2nd question was that you found you couldn't get the list of all SQL
> Server instances in your network via ListAvailableSQLServers.
> If I have misunderstood, please let me know.
> For the first question, I recommend you:
> 1) Create a SQLDMO.SQLServer object.
> 2) Connect to the SQL Server by server name.
> 3) Get the version information by the property SQLServer.VersionString or
> SQLServer.VersionMajor.
> For the second question, I recommend you check your network settings and
> ensure your application machine can access any SQL Server instance.
> I write a sample and get the named instances that my machine can access in
> network:
> SQLDMO.Application app = new SQLDMO.ApplicationClass();
> SQLDMO.NameList nl = app.ListAvailableSQLServers();
> for (int i = 0; i < nl.Count; ++i)
> {
> string s = nl.Item(i);
> this.label1.Text += s + "\n";
> }
> If you have any other concerns, please feel free to let me know. It's my
> pleasure to be of assistance.
> +++++++++++++++++++++++++++
> Charles Wang
> Microsoft Online Partner Support
> +++++++++++++++++++++++++++
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> Business-Critical Phone Support (BCPS) provides you with technical phone
> support at no charge during critical LAN outages or "business down"
> situations. This benefit is available 24 hours a day, 7 days a week to all
> Microsoft technology partners in the United States and Canada.
> This and other support options are available here:
> BCPS:
> https://partner.microsoft.com/US/technicalsupport/supportoverview/40010469
> Others:
> https://partner.microsoft.com/US/technicalsupport/supportoverview/
> If you are outside the United States, please visit our International
> Support page:
> http://support.microsoft.com/default.aspx?scid=%2finternational.aspx.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||As long as SQL Browser service is online (and it should), you don't have to
connect to the SQL instances to check their versions. You can send a packet
to UDP 1434 and get the version info in the reply. If you don't want to
program sockets firectly, you can leverage the output of SQLPing.exe and
parse the result text for the instance names and their versions. You can find
SQLPing.exe via Google.
Linchi
"ANeelima" wrote:
> I have the following two questions:
> 1) I can use the ListAvailableSQLServers methods in SQL-DMO to get a list of
> all the available sql servers (in the form of Namelist) but how can I detect
> which ones are SQL Server 2000/MSDE 2000 instances and which ones are 2005
> instances programmatically either using VB6 or C#?
> 2) If I have a few machines in my network and one of them has an instance
> called
> "mycomputer/myinstance", the method "ListAvailableSQLServers" does not list
> it. Could that be related to the windows firewall being turned on?
> --
> ANeelima|||Unfortunately, SMO doesn't support COM. You might want to take a look at the
updated DMO.
"Microsoft SQL Server 2005 Backward Compatibility Components
The SQL Server Backward Compatibility package includes the latest versions
of Data Transformation Services 2000 runtime (DTS), SQL Distributed
Management Objects (SQL-DMO), Decision Support Objects (DSO), and SQL
Virtual Device Interface (SQLVDI). These versions have been updated for
compatibility with SQL Server 2005 and include all fixes shipped through SQL
Server 2000 SP4."
http://www.microsoft.com/downloads/details.aspx?familyid=D09C1D60-A13C-4479-9B91-9E8B9D835CDC&displaylang=en
--
-oj
"ANeelima" <neelima@.newsgroups.nospam> wrote in message
news:E9A07FEA-8F2F-40C5-BADE-599D41B88FB4@.microsoft.com...
> Thanks.
> My application is in VB6.0. How can I use SMO from VB6.0? I can't
> seem to find it in the list of references in VB6.0. What is the reference
> name?
>
> --
> ANeelima
>
> "oj" wrote:
>> You'd want to use smo instead of dmo to interact with both sql2k5 and
>> sql2k.
>> Take a look at Server.PingSqlServerVersion() method under
>> Microsoft.SqlServer.Management.Smo namespace.
>> --
>> -oj
>>
>> "ANeelima" <neelima@.newsgroups.nospam> wrote in message
>> news:B178E81E-C9DA-46FC-B0FB-504537B030D3@.microsoft.com...
>> >I have the following two questions:
>> >
>> > 1) I can use the ListAvailableSQLServers methods in SQL-DMO to get a
>> > list
>> > of
>> > all the available sql servers (in the form of Namelist) but how can I
>> > detect
>> > which ones are SQL Server 2000/MSDE 2000 instances and which ones are
>> > 2005
>> > instances programmatically either using VB6 or C#?
>> >
>> > 2) If I have a few machines in my network and one of them has an
>> > instance
>> > called
>> > "mycomputer/myinstance", the method "ListAvailableSQLServers" does not
>> > list
>> > it. Could that be related to the windows firewall being turned on?
>> >
>> > --
>> > ANeelima
>>|||Hi,
Thanks for your response.
SMO is included in SQL Server 2005. You can find the assemblies in:
"C:\Program Files\Microsoft SQL
Server\90\SDK\Assemblies\Microsoft.SqlServer.ConnectionInfo.dll",
"C:\Program Files\Microsoft SQL
Server\90\SDK\Assemblies\Microsoft.SqlServer.Smo.dll",
"C:\Program Files\Microsoft SQL
Server\90\SDK\Assemblies\Microsoft.SqlServer.SmoEnum.dll",
"C:\Program Files\Microsoft SQL
Server\90\SDK\Assemblies\Microsoft.SqlServer.SqlEnum.dll"
You can directly add the references in VS.NET 2003/2005.
The assemblies names are:
Microsoft.SqlServer.ConnectionInfo
Microsoft.SqlServer.Smo
Microsoft.SqlServer.SmoEnum
Microsoft.SqlServer.SqlEnum
If you want to use SMO in VB6, I recommend you use VS.Net 2003/2005 to wrap
the assembly into a COM library.
It's easy to realize this wrap in VS.Net 2003/2005.
You can refer to this article:
Can I Interest You in 5000 Classes?
Using the Full .NET Framework from Visual Basic 6
http://msdn.microsoft.com/vbrun/vbfusion/default.aspx?pull=/library/en-us/dv
_vstechart/html/VB5000Cl.asp
If you have any other concerns, please feel free to let me know. It's my
pleasure to be of assistance.
+++++++++++++++++++++++++++
Charles Wang
Microsoft Online Partner Support
+++++++++++++++++++++++++++
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/technicalsupport/supportoverview/40010469
Others:
https://partner.microsoft.com/US/technicalsupport/supportoverview/
If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/default.aspx?scid=%2finternational.aspx.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi
How do you use the assemblies SMO if you don't install sql2005?
I have a web application in vs net 2003, and i have use de object
SQLServer for print a list of databases, tables, etc of other server
with sql2005
but in my server i don't have SQL2005, exist any client for that?
thanks
Charles Wang[MSFT] wrote:
> Hi,
> Thanks for your response.
> SMO is included in SQL Server 2005. You can find the assemblies in:
> "C:\Program Files\Microsoft SQL
> Server\90\SDK\Assemblies\Microsoft.SqlServer.ConnectionInfo.dll",
> "C:\Program Files\Microsoft SQL
> Server\90\SDK\Assemblies\Microsoft.SqlServer.Smo.dll",
> "C:\Program Files\Microsoft SQL
> Server\90\SDK\Assemblies\Microsoft.SqlServer.SmoEnum.dll",
> "C:\Program Files\Microsoft SQL
> Server\90\SDK\Assemblies\Microsoft.SqlServer.SqlEnum.dll"
> You can directly add the references in VS.NET 2003/2005.
> The assemblies names are:
> Microsoft.SqlServer.ConnectionInfo
> Microsoft.SqlServer.Smo
> Microsoft.SqlServer.SmoEnum
> Microsoft.SqlServer.SqlEnum
> If you want to use SMO in VB6, I recommend you use VS.Net 2003/2005 to wrap
> the assembly into a COM library.
> It's easy to realize this wrap in VS.Net 2003/2005.
> You can refer to this article:
> Can I Interest You in 5000 Classes?
> Using the Full .NET Framework from Visual Basic 6
> http://msdn.microsoft.com/vbrun/vbfusion/default.aspx?pull=/library/en-us/dv
> _vstechart/html/VB5000Cl.asp
> If you have any other concerns, please feel free to let me know. It's my
> pleasure to be of assistance.
> +++++++++++++++++++++++++++
> Charles Wang
> Microsoft Online Partner Support
> +++++++++++++++++++++++++++
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> Business-Critical Phone Support (BCPS) provides you with technical phone
> support at no charge during critical LAN outages or "business down"
> situations. This benefit is available 24 hours a day, 7 days a week to all
> Microsoft technology partners in the United States and Canada.
> This and other support options are available here:
> BCPS:
> https://partner.microsoft.com/US/technicalsupport/supportoverview/40010469
> Others:
> https://partner.microsoft.com/US/technicalsupport/supportoverview/
> If you are outside the United States, please visit our International
> Support page:
> http://support.microsoft.com/default.aspx?scid=%2finternational.aspx.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi
How do you use the assemblies SMO if you don't install sql2005?
I have a web application in vs net 2003, and i have use de object
SQLServer for print a list of databases, tables, etc of other server
with sql2005
but in my server i don't have SQL2005, exist any client for that?
thanks
Charles Wang[MSFT] wrote:
> Hi,
> Thanks for your response.
> SMO is included in SQL Server 2005. You can find the assemblies in:
> "C:\Program Files\Microsoft SQL
> Server\90\SDK\Assemblies\Microsoft.SqlServer.ConnectionInfo.dll",
> "C:\Program Files\Microsoft SQL
> Server\90\SDK\Assemblies\Microsoft.SqlServer.Smo.dll",
> "C:\Program Files\Microsoft SQL
> Server\90\SDK\Assemblies\Microsoft.SqlServer.SmoEnum.dll",
> "C:\Program Files\Microsoft SQL
> Server\90\SDK\Assemblies\Microsoft.SqlServer.SqlEnum.dll"
> You can directly add the references in VS.NET 2003/2005.
> The assemblies names are:
> Microsoft.SqlServer.ConnectionInfo
> Microsoft.SqlServer.Smo
> Microsoft.SqlServer.SmoEnum
> Microsoft.SqlServer.SqlEnum
> If you want to use SMO in VB6, I recommend you use VS.Net 2003/2005 to wrap
> the assembly into a COM library.
> It's easy to realize this wrap in VS.Net 2003/2005.
> You can refer to this article:
> Can I Interest You in 5000 Classes?
> Using the Full .NET Framework from Visual Basic 6
> http://msdn.microsoft.com/vbrun/vbfusion/default.aspx?pull=/library/en-us/dv
> _vstechart/html/VB5000Cl.asp
> If you have any other concerns, please feel free to let me know. It's my
> pleasure to be of assistance.
> +++++++++++++++++++++++++++
> Charles Wang
> Microsoft Online Partner Support
> +++++++++++++++++++++++++++
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> Business-Critical Phone Support (BCPS) provides you with technical phone
> support at no charge during critical LAN outages or "business down"
> situations. This benefit is available 24 hours a day, 7 days a week to all
> Microsoft technology partners in the United States and Canada.
> This and other support options are available here:
> BCPS:
> https://partner.microsoft.com/US/technicalsupport/supportoverview/40010469
> Others:
> https://partner.microsoft.com/US/technicalsupport/supportoverview/
> If you are outside the United States, please visit our International
> Support page:
> http://support.microsoft.com/default.aspx?scid=%2finternational.aspx.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi, xahaymar,
Thanks for your participation on this issue.
I need to appologize firstly I didn't perform a test that those SMO
assemblies are .net 2.0 assemblies and can't be applied in .net 1.1.
If you want to use the SMO assemblies you need to install SQL Server 2005.
Otherwise, you should use SQL-DMO.
However, you can install SQL Server 2005 Express on that machine. Those
assemblies are included in SQL 2005 Express.
SQL Server 2005 Express is free and you can directly download it from:
http://msdn.microsoft.com/vstudio/express/sql/download/
Enjoy your day!
+++++++++++++++++++++++++++
Charles Wang
Microsoft Online Partner Support
+++++++++++++++++++++++++++
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/technicalsupport/supportoverview/40010469
Others:
https://partner.microsoft.com/US/technicalsupport/supportoverview/
If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/default.aspx?scid=%2finternational.aspx.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Neelima,
I am interested in this issue. Would you mind letting me know the result of
the suggestions? If you need further assistance, feel free to let me know.
I will be more than happy to be of assistance.
Have a great day!
+++++++++++++++++++++++++++
Charles Wang
Microsoft Online Partner Support
+++++++++++++++++++++++++++
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.sql

Detecting SQL Server 2000 vs 2005 instance

I have the following two questions:
1) I can use the ListAvailableSQLServers methods in SQL-DMO to get a list of
all the available sql servers (in the form of Namelist) but how can I detect
which ones are SQL Server 2000/MSDE 2000 instances and which ones are 2005
instances programmatically either using VB6 or C#?
2) If I have a few machines in my network and one of them has an instance
called
"mycomputer/myinstance", the method "ListAvailableSQLServers" does not list
it. Could that be related to the windows firewall being turned on?
ANeelimaYou'd want to use smo instead of dmo to interact with both sql2k5 and sql2k.
Take a look at Server.PingSqlServerVersion() method under
Microsoft.SqlServer.Management.Smo namespace.
-oj
"ANeelima" <neelima@.newsgroups.nospam> wrote in message
news:B178E81E-C9DA-46FC-B0FB-504537B030D3@.microsoft.com...
>I have the following two questions:
> 1) I can use the ListAvailableSQLServers methods in SQL-DMO to get a list
> of
> all the available sql servers (in the form of Namelist) but how can I
> detect
> which ones are SQL Server 2000/MSDE 2000 instances and which ones are 2005
> instances programmatically either using VB6 or C#?
> 2) If I have a few machines in my network and one of them has an instance
> called
> "mycomputer/myinstance", the method "ListAvailableSQLServers" does not
> list
> it. Could that be related to the windows firewall being turned on?
> --
> ANeelima|||Hi,
Thanks for your post!
From your description, I understand that:
Your 1st question was that you wanted to detect each SQL Server version
corresponding to each SQL Server instances.
Your 2nd question was that you found you couldn't get the list of all SQL
Server instances in your network via ListAvailableSQLServers.
If I have misunderstood, please let me know.
For the first question, I recommend you:
1) Create a SQLDMO.SQLServer object.
2) Connect to the SQL Server by server name.
3) Get the version information by the property SQLServer.VersionString or
SQLServer.VersionMajor.
For the second question, I recommend you check your network settings and
ensure your application machine can access any SQL Server instance.
I write a sample and get the named instances that my machine can access in
network:
SQLDMO.Application app = new SQLDMO.ApplicationClass();
SQLDMO.NameList nl = app.ListAvailableSQLServers();
for (int i = 0; i < nl.Count; ++i)
{
string s = nl.Item(i);
this.label1.Text += s + "\n";
}
If you have any other concerns, please feel free to let me know. It's my
pleasure to be of assistance.
+++++++++++++++++++++++++++
Charles Wang
Microsoft Online Partner Support
+++++++++++++++++++++++++++
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/te...erview/40010469
Others:
https://partner.microsoft.com/US/te...upportoverview/
If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/defaul...rnational.aspx.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks.
My application is in VB6.0. How can I use SMO from VB6.0? I can't
seem to find it in the list of references in VB6.0. What is the reference
name?
ANeelima
"oj" wrote:

> You'd want to use smo instead of dmo to interact with both sql2k5 and sql2
k.
> Take a look at Server.PingSqlServerVersion() method under
> Microsoft.SqlServer.Management.Smo namespace.
> --
> -oj
>
> "ANeelima" <neelima@.newsgroups.nospam> wrote in message
> news:B178E81E-C9DA-46FC-B0FB-504537B030D3@.microsoft.com...
>
>|||Well, I was hoping that I don't have to connect to the server to get the
version name.
I wanted to simply use ListAvailableSQLServers, get the namelist, loop
through the servers and find the version without connecting.
It appears like I can do that with SMO using something like
----
DataTable dataTable = SmoApplication.EnumAvailableSqlServers();
foreach (DataRow dataRow in dataTable.Rows )
{
row.Append(
"Server: " + dataRow["Name"] + " Version: " + dataRow["Version"]);
}
Console.WriteLine(row.ToString());
----
--
But I can't do that with SQLDMO.
My application is in VB6.0 and if I need to use SMO in VB6.0 how can I do
that?
ANeelima
"Charles Wang[MSFT]" wrote:

> Hi,
> Thanks for your post!
> From your description, I understand that:
> Your 1st question was that you wanted to detect each SQL Server version
> corresponding to each SQL Server instances.
> Your 2nd question was that you found you couldn't get the list of all SQL
> Server instances in your network via ListAvailableSQLServers.
> If I have misunderstood, please let me know.
> For the first question, I recommend you:
> 1) Create a SQLDMO.SQLServer object.
> 2) Connect to the SQL Server by server name.
> 3) Get the version information by the property SQLServer.VersionString or
> SQLServer.VersionMajor.
> For the second question, I recommend you check your network settings and
> ensure your application machine can access any SQL Server instance.
> I write a sample and get the named instances that my machine can access in
> network:
> SQLDMO.Application app = new SQLDMO.ApplicationClass();
> SQLDMO.NameList nl = app.ListAvailableSQLServers();
> for (int i = 0; i < nl.Count; ++i)
> {
> string s = nl.Item(i);
> this.label1.Text += s + "\n";
> }
> If you have any other concerns, please feel free to let me know. It's my
> pleasure to be of assistance.
> +++++++++++++++++++++++++++
> Charles Wang
> Microsoft Online Partner Support
> +++++++++++++++++++++++++++
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
> Business-Critical Phone Support (BCPS) provides you with technical phone
> support at no charge during critical LAN outages or "business down"
> situations. This benefit is available 24 hours a day, 7 days a week to all
> Microsoft technology partners in the United States and Canada.
> This and other support options are available here:
> BCPS:
> https://partner.microsoft.com/US/te...erview/40010469
> Others:
> https://partner.microsoft.com/US/te...upportoverview/
> If you are outside the United States, please visit our International
> Support page:
> http://support.microsoft.com/defaul...rnational.aspx.
> ========================================
=============
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>|||As long as SQL Browser service is online (and it should), you don't have to
connect to the SQL instances to check their versions. You can send a packet
to UDP 1434 and get the version info in the reply. If you don't want to
program sockets firectly, you can leverage the output of SQLPing.exe and
parse the result text for the instance names and their versions. You can fin
d
SQLPing.exe via Google.
Linchi
"ANeelima" wrote:

> I have the following two questions:
> 1) I can use the ListAvailableSQLServers methods in SQL-DMO to get a list
of
> all the available sql servers (in the form of Namelist) but how can I dete
ct
> which ones are SQL Server 2000/MSDE 2000 instances and which ones are 2005
> instances programmatically either using VB6 or C#?
> 2) If I have a few machines in my network and one of them has an instance
> called
> "mycomputer/myinstance", the method "ListAvailableSQLServers" does not lis
t
> it. Could that be related to the windows firewall being turned on?
> --
> ANeelima|||Unfortunately, SMO doesn't support COM. You might want to take a look at the
updated DMO.
"Microsoft SQL Server 2005 Backward Compatibility Components
The SQL Server Backward Compatibility package includes the latest versions
of Data Transformation Services 2000 runtime (DTS), SQL Distributed
Management Objects (SQL-DMO), Decision Support Objects (DSO), and SQL
Virtual Device Interface (SQLVDI). These versions have been updated for
compatibility with SQL Server 2005 and include all fixes shipped through SQL
Server 2000 SP4."
http://www.microsoft.com/downloads/...&displaylang=en
-oj
"ANeelima" <neelima@.newsgroups.nospam> wrote in message
news:E9A07FEA-8F2F-40C5-BADE-599D41B88FB4@.microsoft.com...[vbcol=seagreen]
> Thanks.
> My application is in VB6.0. How can I use SMO from VB6.0? I can't
> seem to find it in the list of references in VB6.0. What is the reference
> name?
>
> --
> ANeelima
>
> "oj" wrote:
>|||Hi,
Thanks for your response.
SMO is included in SQL Server 2005. You can find the assemblies in:
"C:\Program Files\Microsoft SQL
Server\90\SDK\Assemblies\Microsoft.SqlServer.ConnectionInfo.dll",
"C:\Program Files\Microsoft SQL
Server\90\SDK\Assemblies\Microsoft.SqlServer.Smo.dll",
"C:\Program Files\Microsoft SQL
Server\90\SDK\Assemblies\Microsoft.SqlServer.SmoEnum.dll",
"C:\Program Files\Microsoft SQL
Server\90\SDK\Assemblies\Microsoft.SqlServer.SqlEnum.dll"
You can directly add the references in VS.NET 2003/2005.
The assemblies names are:
Microsoft.SqlServer.ConnectionInfo
Microsoft.SqlServer.Smo
Microsoft.SqlServer.SmoEnum
Microsoft.SqlServer.SqlEnum
If you want to use SMO in VB6, I recommend you use VS.Net 2003/2005 to wrap
the assembly into a COM library.
It's easy to realize this wrap in VS.Net 2003/2005.
You can refer to this article:
Can I Interest You in 5000 Classes?
Using the Full .NET Framework from Visual Basic 6
http://msdn.microsoft.com/vbrun/vbf...ibrary/en-us/dv
_vstechart/html/VB5000Cl.asp
If you have any other concerns, please feel free to let me know. It's my
pleasure to be of assistance.
+++++++++++++++++++++++++++
Charles Wang
Microsoft Online Partner Support
+++++++++++++++++++++++++++
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/te...erview/40010469
Others:
https://partner.microsoft.com/US/te...upportoverview/
If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/defaul...rnational.aspx.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi
How do you use the assemblies SMO if you don't install sql2005?
I have a web application in vs net 2003, and i have use de object
SQLServer for print a list of databases, tables, etc of other server
with sql2005
but in my server i don't have SQL2005, exist any client for that?
thanks
Charles Wang[MSFT] wrote:[vbcol=seagreen]
> Hi,
> Thanks for your response.
> SMO is included in SQL Server 2005. You can find the assemblies in:
> "C:\Program Files\Microsoft SQL
> Server\90\SDK\Assemblies\Microsoft.SqlServer.ConnectionInfo.dll",
> "C:\Program Files\Microsoft SQL
> Server\90\SDK\Assemblies\Microsoft.SqlServer.Smo.dll",
> "C:\Program Files\Microsoft SQL
> Server\90\SDK\Assemblies\Microsoft.SqlServer.SmoEnum.dll",
> "C:\Program Files\Microsoft SQL
> Server\90\SDK\Assemblies\Microsoft.SqlServer.SqlEnum.dll"
> You can directly add the references in VS.NET 2003/2005.
> The assemblies names are:
> Microsoft.SqlServer.ConnectionInfo
> Microsoft.SqlServer.Smo
> Microsoft.SqlServer.SmoEnum
> Microsoft.SqlServer.SqlEnum
> If you want to use SMO in VB6, I recommend you use VS.Net 2003/2005 to wra
p
> the assembly into a COM library.
> It's easy to realize this wrap in VS.Net 2003/2005.
> You can refer to this article:
> Can I Interest You in 5000 Classes?
> Using the Full .NET Framework from Visual Basic 6
> [url]http://msdn.microsoft.com/vbrun/vbfusion/default.aspx?pull=/library/en-us/dv[/ur
l]
> _vstechart/html/VB5000Cl.asp
> If you have any other concerns, please feel free to let me know. It's my
> pleasure to be of assistance.
> +++++++++++++++++++++++++++
> Charles Wang
> Microsoft Online Partner Support
> +++++++++++++++++++++++++++
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
> Business-Critical Phone Support (BCPS) provides you with technical phone
> support at no charge during critical LAN outages or "business down"
> situations. This benefit is available 24 hours a day, 7 days a week to all
> Microsoft technology partners in the United States and Canada.
> This and other support options are available here:
> BCPS:
> https://partner.microsoft.com/US/te...erview/40010469
> Others:
> https://partner.microsoft.com/US/te...upportoverview/
> If you are outside the United States, please visit our International
> Support page:
> http://support.microsoft.com/defaul...rnational.aspx.
> ========================================
=============
> This posting is provided "AS IS" with no warranties, and confers no rights.[/vbcol
]|||Hi
How do you use the assemblies SMO if you don't install sql2005?
I have a web application in vs net 2003, and i have use de object
SQLServer for print a list of databases, tables, etc of other server
with sql2005
but in my server i don't have SQL2005, exist any client for that?
thanks
Charles Wang[MSFT] wrote:[vbcol=seagreen]
> Hi,
> Thanks for your response.
> SMO is included in SQL Server 2005. You can find the assemblies in:
> "C:\Program Files\Microsoft SQL
> Server\90\SDK\Assemblies\Microsoft.SqlServer.ConnectionInfo.dll",
> "C:\Program Files\Microsoft SQL
> Server\90\SDK\Assemblies\Microsoft.SqlServer.Smo.dll",
> "C:\Program Files\Microsoft SQL
> Server\90\SDK\Assemblies\Microsoft.SqlServer.SmoEnum.dll",
> "C:\Program Files\Microsoft SQL
> Server\90\SDK\Assemblies\Microsoft.SqlServer.SqlEnum.dll"
> You can directly add the references in VS.NET 2003/2005.
> The assemblies names are:
> Microsoft.SqlServer.ConnectionInfo
> Microsoft.SqlServer.Smo
> Microsoft.SqlServer.SmoEnum
> Microsoft.SqlServer.SqlEnum
> If you want to use SMO in VB6, I recommend you use VS.Net 2003/2005 to wra
p
> the assembly into a COM library.
> It's easy to realize this wrap in VS.Net 2003/2005.
> You can refer to this article:
> Can I Interest You in 5000 Classes?
> Using the Full .NET Framework from Visual Basic 6
> [url]http://msdn.microsoft.com/vbrun/vbfusion/default.aspx?pull=/library/en-us/dv[/ur
l]
> _vstechart/html/VB5000Cl.asp
> If you have any other concerns, please feel free to let me know. It's my
> pleasure to be of assistance.
> +++++++++++++++++++++++++++
> Charles Wang
> Microsoft Online Partner Support
> +++++++++++++++++++++++++++
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
> Business-Critical Phone Support (BCPS) provides you with technical phone
> support at no charge during critical LAN outages or "business down"
> situations. This benefit is available 24 hours a day, 7 days a week to all
> Microsoft technology partners in the United States and Canada.
> This and other support options are available here:
> BCPS:
> https://partner.microsoft.com/US/te...erview/40010469
> Others:
> https://partner.microsoft.com/US/te...upportoverview/
> If you are outside the United States, please visit our International
> Support page:
> http://support.microsoft.com/defaul...rnational.aspx.
> ========================================
=============
> This posting is provided "AS IS" with no warranties, and confers no rights.[/vbcol
]

detecting renderer .

basically i only want a document map when rendering to PDF
however if there are any labels, then the HTML will show the
documentmap, SO
i want to write expressions for hte grouping labels that show nothing
unless it is rendering to PDF, but how do you detect the renderer from
expression code?
KarlIt is not possible to detect the renderer from an expression inside the
report.
Just consider the cases where a report is rendered from an execution or a
history snapshot. In both cases the report does not get reprocessed, but
just rendered from the snapshot. Snapshots contain the processed report
items in an output format independent format. Hence, during executing
expressions the renderer can't be determined.
There are some ways to achieve what you want, but they all involve
additional work (e.g. write your own application which requests different
reports depending on output format)
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Karl Prosser" <klumsy@.xtra.co.nz> wrote in message
news:7156880c.0503031437.2d9032ab@.posting.google.com...
> basically i only want a document map when rendering to PDF
> however if there are any labels, then the HTML will show the
> documentmap, SO
> i want to write expressions for hte grouping labels that show nothing
> unless it is rendering to PDF, but how do you detect the renderer from
> expression code?
> Karl

Detecting processing in progress

Is there an easy way through AMO to detect whether a cube (or database, or dimension...) is currently being processed?

I've tried registering for trace events, but that doesn't seem to be a very reliable way to detect an in-progress process - the gap between events are unknown, so I don't know how long I would need to listen for an event to be able to say definitively that something is being processed. Any other ideas?

I'm not aware of anyway of doing this. You can use performance counters to see if *something* is processing, but you can't see which object. You can use AMO to see when something was last processed, or if it is unprocessed and needs re-processing. But I can't see anything that would tell you if anything is currently being processed. Tracing seems to be one of the only ways of doing this, but you would almost need to write a service that could watch for the start, stop or cancelling of processing jobs in order to be able to figure this out reliably.

To look at this from another angle - why do you need to know? If something is already being processed, I would expect you should get a meaningful error stating this if you tried to process it again. And in most cases, end users should not care that something is being processed, they will just see the new data once the processing transaction has been committed.

|||Darren,
Knowing that *something* is processing is actually good enough for our needs right now. Perfmon counters are a great suggestion - I didn't think to look there. Processing Pool Busy threads seems accurate enough for our needs. I wish there were a counter for "Active Transaction", which would be even more useful for us, but it doesn't look like there is.

Our needs are somewhat unusual - we've actually built a product on SSAS, and our QA department needs to know when processing is in progress to avoid stepping on each other's toes while testing. I think you've given me what I need for now. Thanks!
|||

Well if you use a certain userid (ESP, SQLSERVER, or make a unique one) for processing, you could use the XMLA Discover command for DISCOVER_SESSIONS. If the userid is returned, something is processing.

We do something similiar. All jobs that process any cube use a specific userid.

|||

Yeah, DISCOVER_SESSIONS would be useful, there is also DISCOVER_TRANSACTIONS that might be worth a look.

<Discover xmlns="urnchemas-microsoft-com:xml-analysis">

<RequestType>DISCOVER_TRANSACTIONS</RequestType>

<Restrictions>

<RestrictionList></RestrictionList>

</Restrictions>

<Properties>

<PropertyList></PropertyList>

</Properties>

</Discover>

|||Thanks fellas. I think between DISCOVER_TRANSACTIONS and DISCOVER_SESSIONS I've got the info I need.

Detecting or monitoring for long-running queries.

I can't seem to find a step-by-step guide to how to detect or
continuously monitor for long-running queries that is suitable
for a comparative SQL Server novice. I know that it is possible
to monitor for such with other database products - can anyone
enlighten me as to how this is done with SQL Server? Ideally,
I'd like to snapshot running queries (preferably with query
text) at a particular instant via a script, although any help
to show what queries are running at an instant will be much
appreciated. Any ideas?

__________________________________________________ ___________
Are you Catholic ?
http://www.CatholicEmail.com

100s of FREE email addresses -->
http://www.UltimateEmail.com

Send an Online Greeting Card http://www.UltimateEcards.comsqlserver yeahbaby (Use-Author-Address-Header@.[127.1]) writes:
> I can't seem to find a step-by-step guide to how to detect or
> continuously monitor for long-running queries that is suitable
> for a comparative SQL Server novice. I know that it is possible
> to monitor for such with other database products - can anyone
> enlighten me as to how this is done with SQL Server? Ideally,
> I'd like to snapshot running queries (preferably with query
> text) at a particular instant via a script, although any help
> to show what queries are running at an instant will be much
> appreciated. Any ideas?

On my web site there is a stored procedure, aba_lockinfo, that is not
a perfect match for your request, but which does give the information
you are asking for - and maybe information which you do not care
about. You could look at
http://www.algonet.se/~sommar/sqlutil/aba_lockinfo.html, and see if
it anything for you.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

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

Detecting of record does not work in if query

Edit: Newer mind. I tested this query more after writing this, and now it seems to work!

I hope it continues to work.

In following query, else is never executed.

CREATE PROCEDURE Put_into_basket
( @.Product_code varchar(20))
AS
BEGIN
SET NOCOUNT ON;
IF NOT EXISTS(SELECT * FROM dbo.t_shopping_basket WHERE Product_code=@.Product_code)
BEGIN
INSERT dbo.t_shopping_basket (Product_code, Name,Price)
SELECT Product_code, Name,Price
FROM dbo.t_product
WHERE Product_code= @.Product_code
END
ELSE --this part is never executed
BEGIN
UPDATE dbo.t_shopping_basket
SET Quantity=Quantity+1
WHERE Product_code=@.Product_code
END
END
GO

The query should test if there is a record or row with Product_code=@.Product_code.

If there is not, that is the first part, one such row is inserted. Quantity has a default value of 1. Insertion works, one row is inserted. At least sort of.

If there is already record, That's later part, Quantity is increased by 1. That too works, if ran separately.

But when I test query, it never runs the quantity+1 part.

This is the code that I am using to do the same function. I use your method in other queries but also could not get it working for the cart. In my mind it was like the if exist can only take one other arguement either the update or the insert into but not both. So I changed mine around to this I also have to track another variable as there are different files they can access.

1SELECT2 @.CountItems = Count(ProductID)3FROM4 ShoppingCart5WHERE6 ProductID = @.ProductID7 AND8 CartID = @.CartID9 AND10Filenumber = @.FileNumber1112IF @.CountItems > 01314 UPDATE15 ShoppingCart16 SET17 Quantity = (@.Quantity + ShoppingCart.Quantity)18 WHERE19 ProductID = @.ProductID20 AND21 CartID = @.CartID22 AND23FileNumber = @.FileNumber2425ELSE26 INSERT INTO ShoppingCart27 (28 CartID,29 Quantity,30 ProductID,31FileNumber32 )33 VALUES34 (35 @.CartID,36 @.Quantity,37 @.ProductID,38@.FileNumber39 )

|||

Thanks, I'll keep this in mind. Expesially because my version didn't work at first. Hopefully it was my error and not something which may happen again.

I'm quite new at SQL, your code has some interesting features. Count(ProductID) for instance. You have all carts in same table?

Out of curiosity, where do need FileNumber field. That is, is specific to your solution?

Regards

Leif

|||

Yea it is specitic to my cart. They can buy the same report but covering different info. Not wanting to put a couple thousand individual prices in and having to update every time they added to the site, I have one price for each different report and then tell which report they bought. So the cart could show report1 several times and each one would have a different file number. If they buy something that is not related to a filenumber like a subscription then I just put a 0 in that field.

I use one table for the cart and if they buy, then I move the contents over to the order tables (2 tables) and delete the items from the cart. So that table should stay fairly small.

sql

Detecting maximum database size

I need a way of programmatically detecting the maximum size of a database
when being used under MSDE. The stored procedure sp_helpdb reports the
maxsize value as being 'unlimited', which in the case of MSDE is untrue.
I could look at the product name and infer the max size *but* this would be
my last alternative since I would like it also to work for say the upcoming
2005 version, that I understand has had its max db size increased to 4gigs.
Regards
Lee
There is no function for that, if you want to know the limit for the MSDE
you have to use that via the Selection of the prduct name.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Lee Alexander" <lee@.NoSpamPlease_Digita.com> wrote in message
news:eXwJQSigFHA.1416@.TK2MSFTNGP09.phx.gbl...
>I need a way of programmatically detecting the maximum size of a database
> when being used under MSDE. The stored procedure sp_helpdb reports the
> maxsize value as being 'unlimited', which in the case of MSDE is untrue.
> I could look at the product name and infer the max size *but* this would
> be
> my last alternative since I would like it also to work for say the
> upcoming
> 2005 version, that I understand has had its max db size increased to
> 4gigs.
> Regards
> Lee
>
|||Thanks for the response, i thought that might be the case.
Regards
Lee
"Jens Smeyer" <Jens@.remove_this_for_contacting_sqlserver2005.de> wrote in
message news:%23F68UpigFHA.1612@.TK2MSFTNGP12.phx.gbl...
> There is no function for that, if you want to know the limit for the MSDE
> you have to use that via the Selection of the prduct name.
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Lee Alexander" <lee@.NoSpamPlease_Digita.com> wrote in message
> news:eXwJQSigFHA.1416@.TK2MSFTNGP09.phx.gbl...
>