Build 9.0.3050

I downloaded SP2 on March 10, 2007, and applied it. The system to which I applied it reports 9.0.3050 as the version number, not 9.0.3042. Is this due to the post-SP2 fix?My system, which was updated with the bugged SP2, and then the subsequent hotfix, also reports 9.0.3050. So that seems like a reasonable assumption.|||

refer this link for latest fixes in SQL Server 2005...

http://support.microsoft.com/kb/933508

SP2 Release version was --9.00.3042.00

Latest version with this fix(Mar 05) is -- 9.00.3050.00

Madhu

BUG: Height property reverts to inches

Just thought I'd report a little bug I'd found.
My system is set to UK regional settings which includes metric measurements.
When I add section to a report, i.e Header or Footer the height property is in inches.
If I change the height, say to 2 it correctly treats it as centimetres. A small oversight me thinks!
Regards
Chris McGuiganYou are correct. Thanks for catching this one.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Chris McGuigan" <ChrisMcGuigan@.discussions.microsoft.com> wrote in message
news:A45D03E9-4B69-4FB9-B056-8B9A8683E23F@.microsoft.com...
> Just thought I'd report a little bug I'd found.
> My system is set to UK regional settings which includes metric
measurements.
> When I add section to a report, i.e Header or Footer the height property
is in inches.
> If I change the height, say to 2 it correctly treats it as centimetres. A
small oversight me thinks!
> Regards
> Chris McGuigan

Bug with Renaming Connection

I used the wizard to create a package that loads data from Access into SQL Server 2005. I then saved the package to the File System and added to my project.

I had two connections, one for SQL and one for Access. When I rename either one, the source or destination tasks in the Data Flow task reset to the other connection and/or I get an error that the old connection ID cannot be found.

I solved by opening the dtsx in Notepad and doing a global find/replace for the connection ID's. This is a real hassle.

Are you right-clicking on the connection manager and selecting rename? Or how are you renaming them?|||

That was how I tried to rename, by right-clicking. Also tried within the Properties and in the Package Explorer. Same thing.

|||

Do you want to know another bug. In the Import Data wizard that creates the package, I checked the Source box in the upper left which selected all the tables. I then went into Edit Mappings and checked to Delete Rows in Destination Tables.

However, when I execute the package, it doesn't delete the rows and instead appends the data. It might be because I am importing a lot of tables, because when I tried one table only, it worked. I believe it calls sp_executesql to execute the SQL statement and maybe the sql statement string is too long. Unfortunately, it doesn't warn that anything is incorrect, so one doesn't discover this until after testing. Don't mind bugs, but a bug like this without any kind of warning message is kind of dangerous.

|||

When building a package from the designer, renaming connections is fine, because actually the task stores the connection ID not the name.

I did a quick test with a Wizard built package and it varies, for example a snippet of the Exec SQL Task <SQLTask:SqlTaskData SQLTask:Connection="{5545ACCD-6E14-45EF-9112-2AC292C30609}" shows the use of the ID, so renames should keep working just fine, except that other stuff is doing it wrong.

For example an OLE-DB destination shows <connection id="48" name="OleDbConnection" description="The OLE DB runtime connection used to access the database." connectionManagerID="DestinationConnectionOLEDB"/></connections> which uses the name.

If I then open the package, and open the UI and close it again for the OLE-DB component, the XMl is changed to this <connection id="48" name="OleDbConnection" description="The OLE DB runtime connection used to access the database." connectionManagerID="{5545ACCD-6E14-45EF-9112-2AC292C30609}"/></connections> which clearly will survive a rename.

So why not log it at http://connect.microsoft.com

By the way, stop using notepad if you have added the package to a project. Just right click the package in the Solution Explore and select View Code. You get a much nicer text editor with better find and replace, and it keeps it all nicely liked to the designer window for the package as well. Much easier for when you do want to hack package XML directly.

Bug or Feature?

When upgrading a database from MSDE to Visual Studio 2005 Express, I've noticed that the system tables in the MSDB database don't get upgraded. For example, the SYSSCHEDULES table doesn't get added. Is this by design or a bug?

-Doug

Belatedly moving this thread to the Express forum for an answer.|||

Given that there is no SQL Agent support in Express this may well be by design

|||

Hi Doug,

This has been addressed in SP1, which you can get from http://msdn.microsoft.com/vstudio/express/sql/download.

Regards,

Mike Wachal
SQL Express team

Mark the best posts as Answers!

|||I did find a fix to this problem. If I run the INSTMSDB.SQL script from the install directory on the updgraded database, it will install all of the new MSDB system tables.

Bug in NS 2005?

We have rolled out a solution on SQL 2005 and NS for 2005. As we have been looking into reporting notifications from our system I came across that a column that is created out of the box does not make sense.

In the generated table NS<NotificationClassName>Notifications there is a column called SubscriptionID, it is set as nvarchar(255), but in the NS<SubscriptionClassName>Subscriptions the SubscriptionID is BIGINT. Why would then be different when the refer to the same thing?

What we have done for our solution is not read from the NS table directly, since we do have vacuuming enabled, but we trigger all notifications to another table so we can build reports off the data.

It would be more efficient in our queries to have the data as BIGINT, but I guess it was not an intention to use the tables for reporting inside of NS. We can change our table we are dumping into, but we just did not catch this prior to going live.

I believe this is a bug and thought I would throw it out there.

Hi Andy,

I know this doesn't answer your question, but I would suggest that using triggers or any mechanism that depends on the proprietary implementation schema of Notification Services is unwise and dangerous.

Instead, perhaps you could write a custom delivery protocol to do your reporting. This approach would eliminate any dependence on Microsoft's implentation detail that is subject to change without notice.

Marcus

|||I agree that is a solution, but we plan to test everything before upgrading in the future. We are doing it there so we can collect information about the default protocols, SMTP. Our custom protocol would be easy to populate the table, but then we would have to also make a change for SMTP to use our own process instead of what comes out of the box.

Our app was stood up very quickly, no excuse, but we did not have any past experience with the product to know what issues we might have.|||

I believe you can use the Microsoft SMTP delivery protocol alongside a custom protocol. Admittedly, this would be more of a hassle, but it might free you from your MS schema dependency.

|||

In NS 2005, we provide a set of views you should use rather than using tables directly.

For the inventory Tracker sample, the views are:

NS_InventoryTrackerApplication.InventoryTrackerEvents

NS_InventoryTrackerApplication.InventoryTrackerSubscriptions

NS_InventoryTrackerApplication.InventoryTrackerNotifications

These have the correct data types.

Hope that helps,

-Lukasz

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

The problem is, we have developed a history of notifications database, so we can go back later to see what kind of volume we have sent and who we sent it to.

Since we assume the vacuuming process will delete out old notifications, we need to move those off to somewhere. Our solution was to add a trigger on the table itself, we felt more comfortable with that than altering the view you created, since we were not sure how an UPDATE to the ADF would affect your trigger. We can test that too I guess, but our table that we buit schema from was based on the notification table, it still seems like a bug that your are changing datatypes between them, or was that by design?

Did we meet when I was out in Redmond in October? Your last name seems familiar.

|||What about SSIS'ing the notifications periodically - before vacuuming?

HTH...

--
Joe Webb
SQL Server MVP
http://www.sqlns.com|||

Yes, I think we did meet - if I recall Shyam covered debugging/diagnosing NS applications on the whiteboard.

What do we mean by ‘bug’? Typically, it’s something that impairs intended functionality. Right now, since we don't expect users to work directly against the tables (they are not documented), and since no actual functionality seems to be impaired by this, it seems like an implementation decision. Whether it was the right decision is unknown - there might have been a compelling reason for making this choice. On the face of it, it seems wrong to have done the conversion, but I don’t know the history of this implementation. Now whether we'd change the data type in future – two views on that: 1) if it ain't broken... 2) it would probably be cleaned up if we were to do some work on code that touches this area.

Hope that helps,

-Lukasz

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hi Andy,

Double-check the notification schema specification in your application definition file. Notification Services does not automatically generate a "SubscriptionID" field in the notifications table, so it sounds like a custom field has been defined in your application.

For a list of fields that are generated by NS (some optionally) in the notifications table, see:

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

-Colin

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

My appologies. I did have it defined in my <NotificationClassName> schema fields. It was a slip on our part. We want to track where received notifications and missed the data type error when we created it.

Thank you for the reminder of what to look at. This was not a NS problem, but our own.

|||

Our solution is using MS Speech server as well and we had to build an engine to handle retries to speech if the phone was busy for instance. This engine is logging all call attempts back to the original notification, which we are not relying on NS table, but rather our copy of the data via the trigger.

Just thought I would give you a little more background.

Thanks for the post though.

Bug in NS 2005?

We have rolled out a solution on SQL 2005 and NS for 2005. As we have been looking into reporting notifications from our system I came across that a column that is created out of the box does not make sense.

In the generated table NS<NotificationClassName>Notifications there is a column called SubscriptionID, it is set as nvarchar(255), but in the NS<SubscriptionClassName>Subscriptions the SubscriptionID is BIGINT. Why would then be different when the refer to the same thing?

What we have done for our solution is not read from the NS table directly, since we do have vacuuming enabled, but we trigger all notifications to another table so we can build reports off the data.

It would be more efficient in our queries to have the data as BIGINT, but I guess it was not an intention to use the tables for reporting inside of NS. We can change our table we are dumping into, but we just did not catch this prior to going live.

I believe this is a bug and thought I would throw it out there.

Hi Andy,

I know this doesn't answer your question, but I would suggest that using triggers or any mechanism that depends on the proprietary implementation schema of Notification Services is unwise and dangerous.

Instead, perhaps you could write a custom delivery protocol to do your reporting. This approach would eliminate any dependence on Microsoft's implentation detail that is subject to change without notice.

Marcus

|||I agree that is a solution, but we plan to test everything before upgrading in the future. We are doing it there so we can collect information about the default protocols, SMTP. Our custom protocol would be easy to populate the table, but then we would have to also make a change for SMTP to use our own process instead of what comes out of the box.

Our app was stood up very quickly, no excuse, but we did not have any past experience with the product to know what issues we might have.|||

I believe you can use the Microsoft SMTP delivery protocol alongside a custom protocol. Admittedly, this would be more of a hassle, but it might free you from your MS schema dependency.

|||

In NS 2005, we provide a set of views you should use rather than using tables directly.

For the inventory Tracker sample, the views are:

NS_InventoryTrackerApplication.InventoryTrackerEvents

NS_InventoryTrackerApplication.InventoryTrackerSubscriptions

NS_InventoryTrackerApplication.InventoryTrackerNotifications

These have the correct data types.

Hope that helps,

-Lukasz

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

The problem is, we have developed a history of notifications database, so we can go back later to see what kind of volume we have sent and who we sent it to.

Since we assume the vacuuming process will delete out old notifications, we need to move those off to somewhere. Our solution was to add a trigger on the table itself, we felt more comfortable with that than altering the view you created, since we were not sure how an UPDATE to the ADF would affect your trigger. We can test that too I guess, but our table that we buit schema from was based on the notification table, it still seems like a bug that your are changing datatypes between them, or was that by design?

Did we meet when I was out in Redmond in October? Your last name seems familiar.

|||What about SSIS'ing the notifications periodically - before vacuuming?

HTH...

--
Joe Webb
SQL Server MVP
http://www.sqlns.com|||

Yes, I think we did meet - if I recall Shyam covered debugging/diagnosing NS applications on the whiteboard.

What do we mean by ‘bug’? Typically, it’s something that impairs intended functionality. Right now, since we don't expect users to work directly against the tables (they are not documented), and since no actual functionality seems to be impaired by this, it seems like an implementation decision. Whether it was the right decision is unknown - there might have been a compelling reason for making this choice. On the face of it, it seems wrong to have done the conversion, but I don’t know the history of this implementation. Now whether we'd change the data type in future – two views on that: 1) if it ain't broken... 2) it would probably be cleaned up if we were to do some work on code that touches this area.

Hope that helps,

-Lukasz

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hi Andy,

Double-check the notification schema specification in your application definition file. Notification Services does not automatically generate a "SubscriptionID" field in the notifications table, so it sounds like a custom field has been defined in your application.

For a list of fields that are generated by NS (some optionally) in the notifications table, see:

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

-Colin

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

My appologies. I did have it defined in my <NotificationClassName> schema fields. It was a slip on our part. We want to track where received notifications and missed the data type error when we created it.

Thank you for the reminder of what to look at. This was not a NS problem, but our own.

|||

Our solution is using MS Speech server as well and we had to build an engine to handle retries to speech if the phone was busy for instance. This engine is logging all call attempts back to the original notification, which we are not relying on NS table, but rather our copy of the data via the trigger.

Just thought I would give you a little more background.

Thanks for the post though.

Bug in NS 2005?

We have rolled out a solution on SQL 2005 and NS for 2005. As we have been looking into reporting notifications from our system I came across that a column that is created out of the box does not make sense.

In the generated table NS<NotificationClassName>Notifications there is a column called SubscriptionID, it is set as nvarchar(255), but in the NS<SubscriptionClassName>Subscriptions the SubscriptionID is BIGINT. Why would then be different when the refer to the same thing?

What we have done for our solution is not read from the NS table directly, since we do have vacuuming enabled, but we trigger all notifications to another table so we can build reports off the data.

It would be more efficient in our queries to have the data as BIGINT, but I guess it was not an intention to use the tables for reporting inside of NS. We can change our table we are dumping into, but we just did not catch this prior to going live.

I believe this is a bug and thought I would throw it out there.

Hi Andy,

I know this doesn't answer your question, but I would suggest that using triggers or any mechanism that depends on the proprietary implementation schema of Notification Services is unwise and dangerous.

Instead, perhaps you could write a custom delivery protocol to do your reporting. This approach would eliminate any dependence on Microsoft's implentation detail that is subject to change without notice.

Marcus

|||I agree that is a solution, but we plan to test everything before upgrading in the future. We are doing it there so we can collect information about the default protocols, SMTP. Our custom protocol would be easy to populate the table, but then we would have to also make a change for SMTP to use our own process instead of what comes out of the box.

Our app was stood up very quickly, no excuse, but we did not have any past experience with the product to know what issues we might have.|||

I believe you can use the Microsoft SMTP delivery protocol alongside a custom protocol. Admittedly, this would be more of a hassle, but it might free you from your MS schema dependency.

|||

In NS 2005, we provide a set of views you should use rather than using tables directly.

For the inventory Tracker sample, the views are:

NS_InventoryTrackerApplication.InventoryTrackerEvents

NS_InventoryTrackerApplication.InventoryTrackerSubscriptions

NS_InventoryTrackerApplication.InventoryTrackerNotifications

These have the correct data types.

Hope that helps,

-Lukasz

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

The problem is, we have developed a history of notifications database, so we can go back later to see what kind of volume we have sent and who we sent it to.

Since we assume the vacuuming process will delete out old notifications, we need to move those off to somewhere. Our solution was to add a trigger on the table itself, we felt more comfortable with that than altering the view you created, since we were not sure how an UPDATE to the ADF would affect your trigger. We can test that too I guess, but our table that we buit schema from was based on the notification table, it still seems like a bug that your are changing datatypes between them, or was that by design?

Did we meet when I was out in Redmond in October? Your last name seems familiar.

|||What about SSIS'ing the notifications periodically - before vacuuming?

HTH...

--
Joe Webb
SQL Server MVP
http://www.sqlns.com|||

Yes, I think we did meet - if I recall Shyam covered debugging/diagnosing NS applications on the whiteboard.

What do we mean by ‘bug’? Typically, it’s something that impairs intended functionality. Right now, since we don't expect users to work directly against the tables (they are not documented), and since no actual functionality seems to be impaired by this, it seems like an implementation decision. Whether it was the right decision is unknown - there might have been a compelling reason for making this choice. On the face of it, it seems wrong to have done the conversion, but I don’t know the history of this implementation. Now whether we'd change the data type in future – two views on that: 1) if it ain't broken... 2) it would probably be cleaned up if we were to do some work on code that touches this area.

Hope that helps,

-Lukasz

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hi Andy,

Double-check the notification schema specification in your application definition file. Notification Services does not automatically generate a "SubscriptionID" field in the notifications table, so it sounds like a custom field has been defined in your application.

For a list of fields that are generated by NS (some optionally) in the notifications table, see:

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

-Colin

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

My appologies. I did have it defined in my <NotificationClassName> schema fields. It was a slip on our part. We want to track where received notifications and missed the data type error when we created it.

Thank you for the reminder of what to look at. This was not a NS problem, but our own.

|||

Our solution is using MS Speech server as well and we had to build an engine to handle retries to speech if the phone was busy for instance. This engine is logging all call attempts back to the original notification, which we are not relying on NS table, but rather our copy of the data via the trigger.

Just thought I would give you a little more background.

Thanks for the post though.

Bug in a Stored Procedure [sys].[sp_dbmmonitorupdate]

Hi guys,

I found an annoying bug in the system sproc [sys].[sp_dbmmonitorupdate]. There in line
308 : set @.database_name = db_name( @.database_id )
it should basically state
set @.database_name = QUOTENAME(db_name( @.database_id ))
because if a database has a space or point in the name this causes the sproc to fail with the error:
"Incorrect syntax near '.'."

has someone any idea how to change this sys sproc?
or an idea for a workaround?

thanks...
darkook, let me ask the other way around:

i cannot change the sys sproc, can I? in that case I need a sproc which is deployed in the msdb (there is a check in the sp_dbmmonitorupdate which says it must be executed in the context of the msdb). how ever, I understand that no sprocs should be deployed in msdb. any ideas how to solve this problem?

thx!

Brute Force Attack

Hello,
I notice a brute force attack to discover the sa password on a public
SQL Server.
Do anybody know an intrusion detection system that notify when there are
N attempts in N minutes ?
Thanks
Vittorio Pavesi
--
http://www.vittorio.tkVittorio Pavesi ha scritto:
> Hello,
> I notice a brute force attack to discover the sa password on a public
> SQL Server.
> Do anybody know an intrusion detection system that notify when there are
> N attempts in N minutes ?
> Thanks
>
> Vittorio Pavesi
> --
> http://www.vittorio.tk
Ok, I'll develop it by myself...
Vittorio Pavesi
--
http://www.vittorio.tk|||Vittorio Pavesi ha scritto:
> Hello,
> I notice a brute force attack to discover the sa password on a public
> SQL Server.
> Do anybody know an intrusion detection system that notify when there are
> N attempts in N minutes ?
> Thanks
>
> Vittorio Pavesi
> --
> http://www.vittorio.tk
Ok, I'll develop it by myself...
Vittorio Pavesi
--
http://www.vittorio.tk|||Vittorio,
If you use SQL 2005 on a Win2003 server, then the OS security policy can
be configured to allow you to only allow N attempts, then block that IP for
X minutes.
No such luck with SQL 2000 - Microsoft is never going to fix it - the
whole mess started when they released a product with an sa account that
couldn't be deleted or changed with a blank as the default password. This
started a whole industry of nerds breaking into SQL servers. They knew the
account name, and many had blank passwords. Those that didn't, they went
after with a brute force pw attack.
Anyway - what works for me.
Write a 20 line custom program (I use VB .NET but you could use anything)
that is run by the Windows task scheduler every 5 minutes (or whatever). Run
under an admin account. The program does the following
1. Runs netstat -n and dumps the result to a file.
2. Read the file - look for port 1433 - make a list of the ones that are
timing out. Collect the IP numbers of the bad guys.
3. Then run netsh from the custom program to block the IP by adding it to
an existing IP filter list. ie - the command would be (this is for a Win
2003 server)
cmd /c netsh ipsec static add filter filterlist="Block IP Numbers"
srcaddr=CurIP dstaddr=me protocol=ANY mirrored=no
where "Block IP Numbers" is the name of the filter list and CurIP is the IP
you are blocking.
4. Then send an email to yourself from the program
*************************
This is kind of a convuluted way of doing it but it works fine. Why
Microsoft couldn't come up with something more elegant after all these years
is hard to understand. Have fun. - AND - make sure you are using an
impossible sa password - they will never guess it but unless you stop them
they can sure use up a lot of bandwidth trying.
Russ Stevens|||Thanks Russel for your very complete post, I totally agree with you !!
I developed it and released with source code for free.
It's a plugin for HealthMonitor (http://healthmonitor.sourceforge.net)
so you can use its notification and scheduling feature.
The plugin name is CheckSQLSec, to download go to:
http://sourceforge.net/project/show...
se_id=434781.
I hope this will be useful for everybody.
Vittorio Pavesi
--
http://www.vittorio.tk

browsing for server

I have placed a Windows 2003 server, running SQL Server 2000 on a DMZ that i
s
on my internal network. I understand that as a Windows system starts, it
advertises its services through Service Advertising Protocol, which allows
client computers to browse and select servers of a particular type (in my
case a SQL Server). Is this just a broadcast, or is it registering
somewhere, like with active directory or a master browser? What firewall
rule needs to exist (what port or ports do I need to allow) to enable that
server to advertise that it has SQL Server to clients on another network
connected to the firewall?
Thanks,
JHi Badazizi,
1. SQL Server is using port 1433 as default. If it is purposely changed then
you can check which port it is using through Server Network Utility under SQ
L
Program Groups
2. If you want to publish this SQL instance in Active Directory the you have
to do the same through enterprise manage then properties and then active
directory add.
If u need further clarification let me know
Regards,
Rajesh Kumar
"badazizi" wrote:

> I have placed a Windows 2003 server, running SQL Server 2000 on a DMZ that
is
> on my internal network. I understand that as a Windows system starts, it
> advertises its services through Service Advertising Protocol, which allows
> client computers to browse and select servers of a particular type (in my
> case a SQL Server). Is this just a broadcast, or is it registering
> somewhere, like with active directory or a master browser? What firewall
> rule needs to exist (what port or ports do I need to allow) to enable tha
t
> server to advertise that it has SQL Server to clients on another network
> connected to the firewall?
> Thanks,
> J

Browsetable SQL Server 2000

I have a tough question (well tough for me). We're noticing that for 2
of our users out of the 75 that use the system, the tracer shows that
the Set No BrowseTable On command is being passed for these two and
it's causing a host of issues with their account in our application.
They aren't able to retrieve correct Primary keys for a row they just
updated or inserted.
I've read this has something to do with client side cursors in ASP, but
I dont know a work around.
The authentication is NT Authentication if that matters.
ThanksCheck out ODBC SQLSetStmtOptions. The SET NOBROWSETABLE and FOR BROWSE
options are for Meta Data handling. They can cause additional work and
network traffic; however, some usage is required in order to do parameter
validation and statement parsing.
You should be able to set these options through ADO, most likely at the
Recordset object level.
Check out this link for ODBC:
http://msdn.microsoft.com/library/e...asp?frame=true
Sincerely,
Anthony Thomas
"belacyrf" <bela@.webnet-x.com> wrote in message
news:1104859698.178707.185530@.c13g2000cwb.googlegroups.com...
I have a tough question (well tough for me). We're noticing that for 2
of our users out of the 75 that use the system, the tracer shows that
the Set No BrowseTable On command is being passed for these two and
it's causing a host of issues with their account in our application.
They aren't able to retrieve correct Primary keys for a row they just
updated or inserted.
I've read this has something to do with client side cursors in ASP, but
I dont know a work around.
The authentication is NT Authentication if that matters.
Thanks

Browsetable SQL Server 2000

I have a tough question (well tough for me). We're noticing that for 2
of our users out of the 75 that use the system, the tracer shows that
the Set No BrowseTable On command is being passed for these two and
it's causing a host of issues with their account in our application.
They aren't able to retrieve correct Primary keys for a row they just
updated or inserted.
I've read this has something to do with client side cursors in ASP, but
I dont know a work around.
The authentication is NT Authentication if that matters.
ThanksCheck out ODBC SQLSetStmtOptions. The SET NOBROWSETABLE and FOR BROWSE
options are for Meta Data handling. They can cause additional work and
network traffic; however, some usage is required in order to do parameter
validation and statement parsing.
You should be able to set these options through ADO, most likely at the
Recordset object level.
Check out this link for ODBC:
http://msdn.microsoft.com/library/en-us/dnodbc/html/odbcsql.asp?frame=true
Sincerely,
Anthony Thomas
"belacyrf" <bela@.webnet-x.com> wrote in message
news:1104859698.178707.185530@.c13g2000cwb.googlegroups.com...
I have a tough question (well tough for me). We're noticing that for 2
of our users out of the 75 that use the system, the tracer shows that
the Set No BrowseTable On command is being passed for these two and
it's causing a host of issues with their account in our application.
They aren't able to retrieve correct Primary keys for a row they just
updated or inserted.
I've read this has something to do with client side cursors in ASP, but
I dont know a work around.
The authentication is NT Authentication if that matters.
Thanks

Browsetable SQL Server 2000

I have a tough question (well tough for me). We're noticing that for 2
of our users out of the 75 that use the system, the tracer shows that
the Set No BrowseTable On command is being passed for these two and
it's causing a host of issues with their account in our application.
They aren't able to retrieve correct Primary keys for a row they just
updated or inserted.
I've read this has something to do with client side cursors in ASP, but
I dont know a work around.
The authentication is NT Authentication if that matters.
Thanks
Check out ODBC SQLSetStmtOptions. The SET NOBROWSETABLE and FOR BROWSE
options are for Meta Data handling. They can cause additional work and
network traffic; however, some usage is required in order to do parameter
validation and statement parsing.
You should be able to set these options through ADO, most likely at the
Recordset object level.
Check out this link for ODBC:
http://msdn.microsoft.com/library/en...asp?frame=true
Sincerely,
Anthony Thomas

"belacyrf" <bela@.webnet-x.com> wrote in message
news:1104859698.178707.185530@.c13g2000cwb.googlegr oups.com...
I have a tough question (well tough for me). We're noticing that for 2
of our users out of the 75 that use the system, the tracer shows that
the Set No BrowseTable On command is being passed for these two and
it's causing a host of issues with their account in our application.
They aren't able to retrieve correct Primary keys for a row they just
updated or inserted.
I've read this has something to do with client side cursors in ASP, but
I dont know a work around.
The authentication is NT Authentication if that matters.
Thanks

Bringing text into a database

I have a report that is generated out of another system, that is 180
characters wide. It resembles the layout of an old COBOL type of report.
What I need to do to is to bring it into a database. I've set the field
to be 180 characters (have tried char, varchar, nvarchar, text) but when
I go to import the data, I notice that it gets truncated around position
150 or so, no matter what data type I choose.
What I want to do is to bring this report in, as a single field, and be
able to move it out as a single line.
What's the limit on varchar, char, text and so on? Why is the wizard
truncating the firstline around position 150?
Thanks,
BCThe limit is 8000. Perhaps the wizard is the issue. Can you create the DTS
package manually? It may be that the input row contains a character that's
supposed to be used as a delimiter.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Blasting Cap" <goober@.christian.net> wrote in message
news:%237EjS3KPGHA.2668@.tk2msftngp13.phx.gbl...
I have a report that is generated out of another system, that is 180
characters wide. It resembles the layout of an old COBOL type of report.
What I need to do to is to bring it into a database. I've set the field
to be 180 characters (have tried char, varchar, nvarchar, text) but when
I go to import the data, I notice that it gets truncated around position
150 or so, no matter what data type I choose.
What I want to do is to bring this report in, as a single field, and be
able to move it out as a single line.
What's the limit on varchar, char, text and so on? Why is the wizard
truncating the firstline around position 150?
Thanks,
BC|||Tom Moreau wrote:
> The limit is 8000. Perhaps the wizard is the issue. Can you create the D
TS
> package manually? It may be that the input row contains a character that'
s
> supposed to be used as a delimiter.
>
The wizard in DTS is the same wizard. Best I can figure is that a CRLF
is at the end of each line, and it varies as to what position it's in.
It appears that the first line of the report dictates where the "hard"
column end appears thoughout the rest of the report.
BC|||CRLF is the standard row terminator. You may need a hex editor to track
down exactly where it is.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Blasting Cap" <goober@.christian.net> wrote in message
news:%23RijHHLPGHA.1760@.TK2MSFTNGP10.phx.gbl...
Tom Moreau wrote:
> The limit is 8000. Perhaps the wizard is the issue. Can you create the
> DTS
> package manually? It may be that the input row contains a character
> that's
> supposed to be used as a delimiter.
>
The wizard in DTS is the same wizard. Best I can figure is that a CRLF
is at the end of each line, and it varies as to what position it's in.
It appears that the first line of the report dictates where the "hard"
column end appears thoughout the rest of the report.
BC

Breaking down Total Hours worked into Day and Evening hours

I have data coming from a telephony system that keeps track of when an
employee makes a phone call to conduct a survey and which project number
is being billed for the time the employee spends on that phone call in a
MS SQL Server 2000 database (which I don't own).

The data is being returned to me in a view (see DDL for w_HR_Call_Log
below). I link to this view in MS access through ODBC to create a
linked table. I have my own view in Access that converts the integer
numbers for start and end date to Date/Time and inserts some other
information i need.

This data is eventually going to be compared with data from some
electronic timesheets for purposes of comparing entered hours vs hours
actually spent on the telephone, and the people that will be viewing the
data need the total time on the telephone as wall as that total broken
down by day/evening and weekend. Getting weekend durations is easy
enough (see SQL for qryTelephonyData below), but I was wondering if
anyone knew of efficient set-based methods for doing a day/evening
breakdown of some duration given a start date and end date (with the
day/evening boundary being 17:59:59)? My impression is that to do this
correctly (i.e., handle employees working in different time zones,
adjusting for DST, and figuring out what the boundary is for switching
from evening back to day) will require procedural code (probably in
Visual Basic or VBA).

However, if there are set-based algorithms that can accomplish it in
SQL, I'd like to explore those, as well. Can anyone give any pointers?
Thanks.

--
DDL for view in MS SQL 2000 database:

CREATE VIEW dbo.w_HR_Call_Log
AS
SELECT TOP 100 PERCENT dbo.TRCUsers.WinsID, dbo.users.username AS
Initials, dbo.billing.startdate, dbo.billing.startdate +
dbo.billing.duration AS EndDate,
dbo.billing.duration, dbo.projects.name AS
PrjName, dbo.w_GetCallTrackProject6ID(dbo.projects.descript ion) AS ProjID6,

dbo.w_GetCallTrackProject10ID(dbo.projects.descrip tion) AS ProjID10,
dbo.billing.interactionid
FROM dbo.projects INNER JOIN
dbo.projectsphone INNER JOIN
dbo.users INNER JOIN
dbo.TRCUsers ON dbo.users.userid =
dbo.TRCUsers.UserID INNER JOIN
dbo.billing ON dbo.users.userid =
dbo.billing.userid ON dbo.projectsphone.projectid =
dbo.billing.projectid ON
dbo.projects.projectid = dbo.projectsphone.projectid
WHERE (dbo.billing.userid 0)
ORDER BY dbo.billing.startdate

I don't have acess to the tables, but the fields in the view come
through as the following data types:
WinsID - varchar(10)
Initials - varchar(30)
startdate - long integer (seconds since 1970-01-01 00:00:00)
enddate - long integer (seconds since 1970-01-01 00:00:00)
duration - long integer (enddate - startdate)
ProjID10 - varchar(15)
interactionid - varchar(255) (the identifier for this phone call)

MS Access SQL statement for qryTelephonyData (based on the view,
w_HR_Call_Log):
SELECT dbo_w_HR_Call_Log.WinsID, dbo_w_HR_Call_Log.ProjID10,
FORMAT(CDATE(DATEADD('s',startdate-(5*60*60),'01-01-1970
00:00:00')),"yyyy-mm-dd") AS HoursDate,
CDATE(DATEADD('s',startdate-(5*60*60),'01-01-1970 00:00:00')) AS
StartDT,
CDATE(DATEADD('s',enddate-(5*60*60),'01-01-1970 00:00:00')) AS EndDT,
DatePart('w',[StartDT]) AS StartDTDayOfWeek, Duration,
IIf(StartDTDayOfWeek=1 Or StartDTDayOfWeek=7,Duration,0) AS
WeekendSeconds,
FROM dbo_w_HR_Call_Log
WHERE WinsID<>'0'Beowulf (beowulf_is_not_here@.hotmail.com) writes:

Quote:

Originally Posted by

This data is eventually going to be compared with data from some
electronic timesheets for purposes of comparing entered hours vs hours
actually spent on the telephone, and the people that will be viewing the
data need the total time on the telephone as wall as that total broken
down by day/evening and weekend. Getting weekend durations is easy
enough (see SQL for qryTelephonyData below), but I was wondering if
anyone knew of efficient set-based methods for doing a day/evening
breakdown of some duration given a start date and end date (with the
day/evening boundary being 17:59:59)? My impression is that to do this
correctly (i.e., handle employees working in different time zones,
adjusting for DST, and figuring out what the boundary is for switching
from evening back to day) will require procedural code (probably in
Visual Basic or VBA).
>
However, if there are set-based algorithms that can accomplish it in
SQL, I'd like to explore those, as well. Can anyone give any pointers?

It sounds perfectly possible to do that set-based, provided there is
enough data. Mapping the hour to day/night may be best be done
through a table, so you can enter the table with the hour and get
back what part of the day it is. With a calendar table, you can also
use this for days, so that you can catch non-working days in the middle
of the week.

The time zone is a little more complicated, but provided that there is
a time zone available somewhere this should not be any problem. Assuming
that all times are stored in UTC (or some other time zone), just add the
time-zone offset to get the local time.

Quote:

Originally Posted by

CREATE VIEW dbo.w_HR_Call_Log
AS
SELECT TOP 100 PERCENT dbo.TRCUsers.WinsID, dbo.users.username AS
>...
ORDER BY dbo.billing.startdate

I would recommend that you take out that TOP 100 PERCENT and ORDER BY,
as it fills no purpose, but just results in extra query overhead.

If you want the data to be sorted that way, you need to apply an
ORDER BY clause when you retrieve it. In SQL 2000 it may seen that
when you say "SELECT ... FROM view" that you get the order anyway,
but that is mere chance, and on SQL 2005 that does typically not happen.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog wrote:

Quote:

Originally Posted by

Beowulf (beowulf_is_not_here@.hotmail.com) writes:

Quote:

Originally Posted by

>This data is eventually going to be compared with data from some
>electronic timesheets for purposes of comparing entered hours vs hours
>actually spent on the telephone, and the people that will be viewing the
>data need the total time on the telephone as wall as that total broken
>down by day/evening and weekend. Getting weekend durations is easy
>enough (see SQL for qryTelephonyData below), but I was wondering if
>anyone knew of efficient set-based methods for doing a day/evening
>breakdown of some duration given a start date and end date (with the
>day/evening boundary being 17:59:59)? My impression is that to do this
>correctly (i.e., handle employees working in different time zones,
>adjusting for DST, and figuring out what the boundary is for switching
>from evening back to day) will require procedural code (probably in
>Visual Basic or VBA).
>>
>However, if there are set-based algorithms that can accomplish it in
>SQL, I'd like to explore those, as well. Can anyone give any pointers?

>
It sounds perfectly possible to do that set-based, provided there is
enough data. Mapping the hour to day/night may be best be done
through a table, so you can enter the table with the hour and get
back what part of the day it is. With a calendar table, you can also
use this for days, so that you can catch non-working days in the middle
of the week.

Thanks for taking the time to reply. I always appreciate your advice
here. I'm a little confused by your suggestion. What I have is a
duration (start datetime and end datetime). Would an "hour" to "part of
day" table still work with this data or would I have to convert the
start and end date into something else first? Do you have any pointers
to good tutorials on calendar tables (or is google my friend)? It's a
concept I haven't heard of before.

Quote:

Originally Posted by

The time zone is a little more complicated, but provided that there is
a time zone available somewhere this should not be any problem. Assuming
that all times are stored in UTC (or some other time zone), just add the
time-zone offset to get the local time.

As returned by the view, the startdate and enddate are integers (number
of seconds since 1970-01-01 00:00:00) so it's fairly simple to convert
to UTC.

Quote:

Originally Posted by

Quote:

Originally Posted by

>CREATE VIEW dbo.w_HR_Call_Log
>AS
>SELECT TOP 100 PERCENT dbo.TRCUsers.WinsID, dbo.users.username AS
>...
>ORDER BY dbo.billing.startdate

>
I would recommend that you take out that TOP 100 PERCENT and ORDER BY,
as it fills no purpose, but just results in extra query overhead.
>
If you want the data to be sorted that way, you need to apply an
ORDER BY clause when you retrieve it. In SQL 2000 it may seen that
when you say "SELECT ... FROM view" that you get the order anyway,
but that is mere chance, and on SQL 2005 that does typically not happen.

Thank you for the advice. I learned that fact a little while ago in
this very newsgroup. I don't own that particular view, though.|||Beowulf (beowulf_is_not_here@.hotmail.com) writes:

Quote:

Originally Posted by

Thanks for taking the time to reply. I always appreciate your advice
here. I'm a little confused by your suggestion. What I have is a
duration (start datetime and end datetime). Would an "hour" to "part of
day" table still work with this data or would I have to convert the
start and end date into something else first?

I don't know. That is, I don't know what your business requirements are,
so I cannot answer. I made the simple assumption that only the start time
applied. If you want to split a call that started at 17:23 and ended at
18:14 into day and evening, I don't know in which way you want to split it.

<Standard rant>

Please post:

o CREATE TABLE(s) statements for your tables.
o INSERT statements with sample data.
o The desired result given the sample.

That makes it possible to easily copy and paste to develop a tested
solution.

</Standard rant>

(It's not likely that it will be me this time though, as I'm
off for vacation tomorrow.)

Quote:

Originally Posted by

Do you have any pointers to good tutorials on calendar tables (or is
google my friend)? It's a concept I haven't heard of before.

http://www.aspfaq.com, search for calendar. Aaron has several entries
on them.

Essentially a calendar is a table with one row for each day, and then
you associate attributes to the days that are appropriate for your
business like IsWorkingDay.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Brainstorming: Ideas for categories database structure please

Hi

Thanks in advance for any ideas.

What I'm trying to do, is have a category system like Yahoo or these
newsgroups where you have a parent "COMP" and multiple children "LANG"
or "DATABASES". However, these can also have children. So for
"DATABASES", you have "MS-SQLSERVER" "ACCESS" "ORACLE8" etc

My question is, what is the best way to store these in a database and
also allow me to retrieve the data

I'm not sure how to procede

Thanks

SamWhat you want to do is store a tree structure. Celko has written
extensively on Nested Sets - you can also use a modified adjacency list
(something I prefer), storing the full path to each node with each record.
Anyway, if you search on Nested Sets or Adjacency List, you will find all
the information you need.

"Samuel Hon" <noreply@.samuelhon.co.uk> wrote in message
news:c8672b7d.0309231103.21d812fb@.posting.google.c om...
> Hi
> Thanks in advance for any ideas.
> What I'm trying to do, is have a category system like Yahoo or these
> newsgroups where you have a parent "COMP" and multiple children "LANG"
> or "DATABASES". However, these can also have children. So for
> "DATABASES", you have "MS-SQLSERVER" "ACCESS" "ORACLE8" etc
> My question is, what is the best way to store these in a database and
> also allow me to retrieve the data
> I'm not sure how to procede
> Thanks
> Sam|||Thanks

"Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in message news:<bkq745$9fp$1$8300dec7@.news.demon.co.uk>...
> What you want to do is store a tree structure. Celko has written
> extensively on Nested Sets - you can also use a modified adjacency list
> (something I prefer), storing the full path to each node with each record.
> Anyway, if you search on Nested Sets or Adjacency List, you will find all
> the information you need.
> "Samuel Hon" <noreply@.samuelhon.co.uk> wrote in message
> news:c8672b7d.0309231103.21d812fb@.posting.google.c om...
> > Hi
> > Thanks in advance for any ideas.
> > What I'm trying to do, is have a category system like Yahoo or these
> > newsgroups where you have a parent "COMP" and multiple children "LANG"
> > or "DATABASES". However, these can also have children. So for
> > "DATABASES", you have "MS-SQLSERVER" "ACCESS" "ORACLE8" etc
> > My question is, what is the best way to store these in a database and
> > also allow me to retrieve the data
> > I'm not sure how to procede
> > Thanks
> > Sam|||You can do it in one table.

Use a few fields like child-id (unique index), name, parent_id

so your table might look something like

CHILD_ID NAME PARENT_ID
1 COMP 0
2 LANG 1
3 DATABASES 1
4 MS_SQLSERVER 3
5 ACCESS 3
6 ORACLE 3
7 VB 5 2
8 COBOL 2
9 ORACLE FORMS 2
10 ASP 2
11 HTML 2
12 JSCRIPT 2

So you can immediately , by knowing the parent ID, which would be a hard
coded static reference code (so languages will always have a parent code of
2), find all child codes and therefore types

???????

"Samuel Hon" <noreply@.samuelhon.co.uk> wrote in message
news:c8672b7d.0309240054.14d3f324@.posting.google.c om...
> Thanks
> "Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in
message news:<bkq745$9fp$1$8300dec7@.news.demon.co.uk>...
> > What you want to do is store a tree structure. Celko has written
> > extensively on Nested Sets - you can also use a modified adjacency list
> > (something I prefer), storing the full path to each node with each
record.
> > Anyway, if you search on Nested Sets or Adjacency List, you will find
all
> > the information you need.
> > "Samuel Hon" <noreply@.samuelhon.co.uk> wrote in message
> > news:c8672b7d.0309231103.21d812fb@.posting.google.c om...
> > > Hi
> > > > Thanks in advance for any ideas.
> > > > What I'm trying to do, is have a category system like Yahoo or these
> > > newsgroups where you have a parent "COMP" and multiple children "LANG"
> > > or "DATABASES". However, these can also have children. So for
> > > "DATABASES", you have "MS-SQLSERVER" "ACCESS" "ORACLE8" etc
> > > > My question is, what is the best way to store these in a database and
> > > also allow me to retrieve the data
> > > > I'm not sure how to procede
> > > > Thanks
> > > > Sam|||You have here an adjacency list. Pretty standard way of doing things BUT
very difficult to write queries about subtrees and relations that aren't
immediate parent/child. You might also store the full path with each
record, as I do (which is usually efficient enough) to allow easy subtree
queries using "LIKE" operator.

"cor_blimey" <no_spam@.no_spam.com> wrote in message
news:1Jdcb.523$_N1.384673@.newsfep1-win.server.ntli.net...
> You can do it in one table.
> Use a few fields like child-id (unique index), name, parent_id
> so your table might look something like
> CHILD_ID NAME PARENT_ID
> 1 COMP 0
> 2 LANG 1
> 3 DATABASES 1
> 4 MS_SQLSERVER 3
> 5 ACCESS 3
> 6 ORACLE 3
> 7 VB 5 2
> 8 COBOL 2
> 9 ORACLE FORMS 2
> 10 ASP 2
> 11 HTML 2
> 12 JSCRIPT 2
>
> So you can immediately , by knowing the parent ID, which would be a hard
> coded static reference code (so languages will always have a parent code
of
> 2), find all child codes and therefore types
> ???????
>
> "Samuel Hon" <noreply@.samuelhon.co.uk> wrote in message
> news:c8672b7d.0309240054.14d3f324@.posting.google.c om...
> > Thanks
> > "Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in
> message news:<bkq745$9fp$1$8300dec7@.news.demon.co.uk>...
> > > What you want to do is store a tree structure. Celko has written
> > > extensively on Nested Sets - you can also use a modified adjacency
list
> > > (something I prefer), storing the full path to each node with each
> record.
> > > Anyway, if you search on Nested Sets or Adjacency List, you will find
> all
> > > the information you need.
> > > > "Samuel Hon" <noreply@.samuelhon.co.uk> wrote in message
> > > news:c8672b7d.0309231103.21d812fb@.posting.google.c om...
> > > > Hi
> > > > > > Thanks in advance for any ideas.
> > > > > > What I'm trying to do, is have a category system like Yahoo or these
> > > > newsgroups where you have a parent "COMP" and multiple children
"LANG"
> > > > or "DATABASES". However, these can also have children. So for
> > > > "DATABASES", you have "MS-SQLSERVER" "ACCESS" "ORACLE8" etc
> > > > > > My question is, what is the best way to store these in a database
and
> > > > also allow me to retrieve the data
> > > > > > I'm not sure how to procede
> > > > > > Thanks
> > > > > > Sam