Advice on dynamically generating RDL files

I would prefer to dynamically create my RDL files on the fly instead of
building them ahead of time. My question is this: the creation of these files
would occur on the server, but the reporting server may or may not be the
same box. Is the best way to upload the RDL file to use the web service
interface and import it, or are there other options here?
Thanks!
MichaelHi moflaherty,
You can create your RDL on the fly, the creation of the files can be
located at the client or server side, it does not matter as long you can
create the XML/RDL after that you can use the WS interface to upload them
into the server. In this example
http://www.rdlcomponents.com/ASPExamples/Default.aspx?sm=b1_a
You can see how the RDL creation process is isolated from the upload process.
Thanks
JErry
"moflaherty" wrote:
> I would prefer to dynamically create my RDL files on the fly instead of
> building them ahead of time. My question is this: the creation of these files
> would occur on the server, but the reporting server may or may not be the
> same box. Is the best way to upload the RDL file to use the web service
> interface and import it, or are there other options here?
> Thanks!
> Michael
>sql

Advice Needed : Nasty Problem PHP/MS SQL Server and Varchar fields > 255 in Length

I am currently working on a PHP based website that needs to be able to draw
from Oracle, MS SQL Server, MySQL and given time and demand other RDBMS. I
took a lot of time and care creating a flexible and solid wrapper and am
deep into coding. The only problem is a noticed VARCHAR fields being drawn
from SQL Server 2000 are being truncated to 255 characters.

I searched around php.net and found the following :

Note to Win32 Users: Due to a limitation in the underlying API used by PHP
(MS DbLib C API), the length of VARCHAR fields is limited to 255. If you
need to store more data, use a TEXT field instead.
(http://www.php.net/manual/en/functi...ield-length.php)

The only problem with this advice is Text fields seem to be limited to 16
characters in length, and I am having similar results in terms of truncation
with other character based fields that can store more than 255 characters.

I am using PHP 4.3.3 running on IIS using the php_mssql.dll extensions and
the functions referenced here http://www.php.net/manual/en/ref.mssql.php.
What are my options here? Has anybody worked around this or am I missing
something obvious?

JamesNo, text fields can handle text string up to 2-Gig. The 16 bytes refers to
the space used up by the string pointer inside the record. The problem with
text/ntext is that they're slow, and you can't use them in ORDER BY or GROUP
BY.

The superlame way of retrieving more than 255 chars from a MSSQL varchar is
to retrieve the field in multiple sections, using the SUBSTR() function,
then concatenate them in PHP:

SELECT SUBSTR(msg, 1, 255) AS msg_1, SUBSTR(msg, 256, 255) AS msg_2, ...

$msg = $row['msg_1'] . $row['msg_2'] . $row['msg_3'] ...

Uzytkownik "James" <jamesstarrittRemovethefollowingtoemailme@.hotmail.c om>
napisal w wiadomosci news:40285e29$1_1@.newspeer2.tds.net...
> I am currently working on a PHP based website that needs to be able to
draw
> from Oracle, MS SQL Server, MySQL and given time and demand other RDBMS.
I
> took a lot of time and care creating a flexible and solid wrapper and am
> deep into coding. The only problem is a noticed VARCHAR fields being
drawn
> from SQL Server 2000 are being truncated to 255 characters.
> I searched around php.net and found the following :
> Note to Win32 Users: Due to a limitation in the underlying API used by PHP
> (MS DbLib C API), the length of VARCHAR fields is limited to 255. If you
> need to store more data, use a TEXT field instead.
> (http://www.php.net/manual/en/functi...ield-length.php)
> The only problem with this advice is Text fields seem to be limited to 16
> characters in length, and I am having similar results in terms of
truncation
> with other character based fields that can store more than 255 characters.
> I am using PHP 4.3.3 running on IIS using the php_mssql.dll extensions and
> the functions referenced here http://www.php.net/manual/en/ref.mssql.php.
> What are my options here? Has anybody worked around this or am I missing
> something obvious?
> James|||James (jamesstarrittRemovethefollowingtoemailme@.hotmail. com) writes:
> The only problem is a noticed VARCHAR fields being drawn
> from SQL Server 2000 are being truncated to 255 characters.
> I searched around php.net and found the following :
> Note to Win32 Users: Due to a limitation in the underlying API used by PHP
> (MS DbLib C API), the length of VARCHAR fields is limited to 255. If you
> need to store more data, use a TEXT field instead.
> (http://www.php.net/manual/en/functi...ield-length.php)
> The only problem with this advice is Text fields seem to be limited to
> 16 characters in length, and I am having similar results in terms of
> truncation with other character based fields that can store more than
> 255 characters.

As pointed out by Chung Leong, there is room for 2GB of data in text.
The 16 bytes you see is just a pointer.

However, text is fairly cumbersome and not really easy to use. I don't
know anything about PHP, but it's apparent that PHP uses DB-Library to
access SQL Server. And while I think this is a very nice API, Microsoft
does not think so, and has not developed DB-Library since the release of
SQL 6.5, which was seven years ago. The next version of SQL Server, slated
for release this year, will accept connections from DB-Library, but will
not come with files needed for development. You may not even get the DB-
Library run-time DLL:s, but have to find them elsewhere.

Thus, there are all reasons to look into alternative means of connections
for PHP to MS SQL Server.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog" <sommar@.algonet.se> wrote in message
> > Note to Win32 Users: Due to a limitation in the underlying API used by
PHP
> > (MS DbLib C API), the length of VARCHAR fields is limited to 255. If you
> > need to store more data, use a TEXT field instead.
> > (http://www.php.net/manual/en/functi...ield-length.php)
> > The only problem with this advice is Text fields seem to be limited to
> > 16 characters in length, and I am having similar results in terms of
> > truncation with other character based fields that can store more than
> > 255 characters.
> As pointed out by Chung Leong, there is room for 2GB of data in text.
> The 16 bytes you see is just a pointer.
> Thus, there are all reasons to look into alternative means of connections
> for PHP to MS SQL Server.

I believe the Linux PHP builds use that alternate means and the Warning
(given only on one page and not the main PHP MS SQL Server driver page as it
should have been) -- I'll be testing that later on today.

Retrieving a field in chunks may not be such a big issue, it is rare that
this will happen frequently in the app however the possibility is there on
some 60% of the fields and I do need to be able to Group and Order By on the
majority of these fields. I may just have to not support the MS SQL Server
at this time -- the goal was an app that could run on Linux and Windows
webservers and connect to just about any DB alive - its a shame that one of
the staple db's is so poorly supported by the PHP project given that I am
tied to it. My work is with non-profits and we have to be able to utilize
the licensing they already have so demanding the use of particular software
will only drive the pricing up.

Strangley I had similar issues when working with ASP and SQL Server in the
past - fields would simply not show up sometimes if they where over 255
characters in length and it is a known and documented issue that affects
'certain databases' according to MS but I have only ever seen with the SQL
Server and MSDE - never Oracle, MySQL, Postgre or even Access -- its a shame
I like the SQL Server but it seems that everytime I get contracted to use it
with a web back end I run into problems that make it a royal pain in the ass
to work with.

Thanks for the feedback,

J|||James (jamesstarrittRemovethefollowingtoemailme@.hotmail. com) writes:
> Strangley I had similar issues when working with ASP and SQL Server in
> the past - fields would simply not show up sometimes if they where over
> 255 characters in length and it is a known and documented issue that
> affects 'certain databases' according to MS but I have only ever seen
> with the SQL Server and MSDE - never Oracle, MySQL, Postgre or even
> Access -- its a shame I like the SQL Server but it seems that everytime
> I get contracted to use it with a web back end I run into problems that
> make it a royal pain in the ass to work with.

I don't know more about ASP than I know about ASP, but I would expect
ASP today have any problems with longer varchar values.

Of course there was a time when SQL Server did not have anything better
than varchar(255) (and text). If you were accessing ASP from a machine
with a version of ODBC that did not support the new and improved types
in SQL7, then you would be in that boat. But that's long ago.

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

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

Adverse effects of too may "sleeping" connections

Hi,
I have an application which maintains around 25-30 connections to the
database.
The status for all those are "sleeping" most of the time.
The database is MSDE.
I would like to know what are the effects on performance, locking and any
other issuses due to this "sleeping" connections.
Any technical document or link would be of great help
hi,
LazyDBA wrote:
> Hi,
> I have an application which maintains around 25-30 connections to the
> database.
> The status for all those are "sleeping" most of the time.
> The database is MSDE.
> I would like to know what are the effects on performance, locking and
> any other issuses due to this "sleeping" connections.
> Any technical document or link would be of great help
no effects on on performance and/or locking... just memory footprint and
"wasted" resources...
each connection, live or sleeping, will eat about 24kb of memory, calculated
as 12 KB + 3 times the Network Packet Size (default setting that can be
partially customized via sp_configure system stored procedure modifying the
'user connections' setting), used to store the data structures holding the
connection 's context, as long as for buffer used to send and receive the
relative associated network streams (default to 4KB network packet setting),
that can be stolen from the buffer pool memory region and/or the MemToLeave
memory area... as these memory regions are not infinite (:D) you are wasting
some bytes.. :D
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.12.0 - DbaMgr ver 0.58.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Adventure Works.sln DEPLOY ERROR first time

After opening the Adventure Works.sln.

I build.

I deploy.

I get the following error ...

Build started: Project: Adventure Works DW, Configuration: Development

Started Building Analysis Services project: Incremental ....

Build complete -- 0 errors, 0 warnings

Deploy started: Project: Adventure Works DW, Configuration: Development

Performing an incremental deployment of the 'Adventure Works DW' database to the 'localhost' server.

Generating deployment script...

Add Database Adventure Works DW

Process Database Adventure Works DW

Done

Sending deployment script to the server...

Error -1056308996 : The attribute Destination Currency does not have any members.

Error -1056308996 : The attribute Parent Organization Key0 does not have any members.

Deploy complete -- 2 errors, 0 warnings

========== Build: 1 succeeded or up-to-date, 0 failed, 0 skipped ==========

========== Deploy: 0 succeeded, 1 failed, 0 skipped ==========

What am I doing wrong?

What do I do next?

AIM

The only thing I could think of is that maybe you are deploying the enterprise sample verison against a standard server....|||

I am using SQL Server Enterprise 2005 180-Eval (and I am choosing the Enterprise folder and not the Standard folder).

My (MSSQLSERVER) Analysis Services is started. Hmmm mm. Maybe its the Data connection? Maybe its not finding my AdventureWorksDW local database? Maybe I have to define this and not using the default? Maybe its the order. I did build. Next, I did deploy. Hmmm mm, maybe I a missing something?

Any ideas?

AIMDBA

|||

When I choose "Process" I get the following result. ...

<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Parallel>
<Process xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Object>
<DatabaseID>Adventure Works DW</DatabaseID>
</Object>
<Type>ProcessFull</Type>
<WriteBackTableCreation>UseExisting</WriteBackTableCreation>
</Process>
</Parallel>
</Batch>
Processing Database 'Adventure Works DW' completed successfully.
Start time: 4/18/2006 10:39:47 AM; End time: 4/18/2006 10:39:48 AM; Duration: 0:00:01
Processing Dimension 'Destination Currency' completed successfully.
Start time: 4/18/2006 10:39:47 AM; End time: 4/18/2006 10:39:48 AM; Duration: 0:00:01
Processing Dimension Attribute '(All)' completed successfully.
Start time: 4/18/2006 10:39:47 AM; End time: 4/18/2006 10:39:47 AM; Duration: 0:00:00
Processing Dimension Attribute 'Destination Currency' completed successfully. 1 rows have been read.
Start time: 4/18/2006 10:39:47 AM; End time: 4/18/2006 10:39:47 AM; Duration: 0:00:00
SQL queries 1
SELECT
DISTINCT
[DimDestinationCurrency].[CurrencyName] AS [DimDestinationCurrencyCurrencyName0_0]
FROM
(

SELECT CurrencyKey, CurrencyAlternateKey, CurrencyName
FROM DimCurrency
WHERE (CurrencyKey IN
(SELECT DISTINCT CurrencyKey
FROM FactCurrencyRate))
)
AS [DimDestinationCurrency]
Processing Dimension Attribute 'Destination Currency Code' completed successfully. 1 rows have been read.
Start time: 4/18/2006 10:39:47 AM; End time: 4/18/2006 10:39:47 AM; Duration: 0:00:00
SQL queries 1
SELECT
DISTINCT
[DimDestinationCurrency].[CurrencyKey] AS [DimDestinationCurrencyCurrencyKey0_0],[DimDestinationCurrency].[CurrencyAlternateKey] AS [DimDestinationCurrencyCurrencyAlternateKey0_1],[DimDestinationCurrency].[CurrencyName] AS [DimDestinationCurrencyCurrencyName0_2]
FROM
(

SELECT CurrencyKey, CurrencyAlternateKey, CurrencyName
FROM DimCurrency
WHERE (CurrencyKey IN
(SELECT DISTINCT CurrencyKey
FROM FactCurrencyRate))
)
AS [DimDestinationCurrency]
Processing Dimension 'Organization' completed successfully.
Start time: 4/18/2006 10:39:47 AM; End time: 4/18/2006 10:39:48 AM; Duration: 0:00:01
Processing Dimension Attribute '(All)' completed successfully.
Start time: 4/18/2006 10:39:47 AM; End time: 4/18/2006 10:39:47 AM; Duration: 0:00:00
Processing Dimension Attribute 'Currency Code' completed successfully. 1 rows have been read.
Start time: 4/18/2006 10:39:48 AM; End time: 4/18/2006 10:39:47 AM; Duration: 0:00:-01
SQL queries 1
SELECT
DISTINCT
[dbo_DimOrganization].[CurrencyKey] AS [dbo_DimOrganizationCurrencyKey0_0],[dbo_DimOrganization].[CurrencyAlternateKey] AS [dbo_DimOrganizationCurrencyAlternateKey0_1]
FROM
(

SELECT o.OrganizationKey, o.ParentOrganizationKey, o.PercentageOfOwnership, o.OrganizationName, o.CurrencyKey, c.CurrencyAlternateKey
FROM DimOrganization AS o INNER JOIN
DimCurrency AS c ON o.CurrencyKey = c.CurrencyKey
)
AS [dbo_DimOrganization]
Processing Dimension Attribute 'Organization' completed successfully. 1 rows have been read.
Start time: 4/18/2006 10:39:48 AM; End time: 4/18/2006 10:39:48 AM; Duration: 0:00:00
SQL queries 1
SELECT
DISTINCT
[dbo_DimOrganization].[OrganizationKey] AS [dbo_DimOrganizationOrganizationKey0_0],[dbo_DimOrganization].[OrganizationName] AS [dbo_DimOrganizationOrganizationName0_1],[dbo_DimOrganization].[PercentageOfOwnership] AS [dbo_DimOrganizationPercentageOfOwnership0_2],[dbo_DimOrganization].[CurrencyKey] AS [dbo_DimOrganizationCurrencyKey0_3],[dbo_DimOrganization].[ParentOrganizationKey] AS [dbo_DimOrganizationParentOrganizationKey0_4]
FROM
(

SELECT o.OrganizationKey, o.ParentOrganizationKey, o.PercentageOfOwnership, o.OrganizationName, o.CurrencyKey, c.CurrencyAlternateKey
FROM DimOrganization AS o INNER JOIN
DimCurrency AS c ON o.CurrencyKey = c.CurrencyKey
)
AS [dbo_DimOrganization]
Processing Hierarchy 'Organizations'.
Start time: 4/18/2006 10:39:48 AM; End time: 4/18/2006 10:39:48 AM; Duration: 0:00:00
Processing Dimension 'Sales Reason' completed successfully.
Start time: 4/18/2006 10:39:48 AM; End time: 4/18/2006 10:39:48 AM; Duration: 0:00:00
Errors and Warnings from Response
The attribute Destination Currency does not have any members.

Any ideas?

Thanks.

AIMDBA

Advanced date/time queries

I'm a new member here and love the amount of knowledge available.

1) I have a database schema and it stores repair information for phones, with columns such as ID, Phone manufacturer, model, fault, etc.What query would I have to write to get a query which returns the phone model, its manufacturer (this I know), but also a number of the amount of times that phone model has been raised in the repair table (which is every time a customer hands in a faulty phone). Do I use a count function for the phone model or is there a more efficient way?

2) Another table stores contract information with an expiry date of the contract. How can I write a query that will pick up the contracts due to expire exactly one month for today? I have looked all over the net for some resourced on writing advanced tsql queries, does anyone know of any good websites or even books on the topic?

Thanks

GSS1 wrote:

1) I have a database schema and it stores repair information for phones, with columns such as ID, Phone manufacturer, model, fault, etc.What query would I have to write to get a query which returns the phone model, its manufacturer (this I know), but also a number of the amount of times that phone model has been raised in the repair table (which is every time a customer hands in a faulty phone). Do I use a count function for the phone model or is there a more efficient way?

You could answer this using the query:

Code Snippet

select t.phone_model, t.phone_manuf, count(*) as #faults

from t

group by t.phone_model, t.phone_manuf

GSS1 wrote:

2) Another table stores contract information with an expiry date of the contract. How can I write a query that will pick up the contracts due to expire exactly one month for today? I have looked all over the net for some resourced on writing advanced tsql queries, does anyone know of any good websites or even books on the topic?

This can be done by using query like below:

Code Snippet

select *

from Contracts as c

where c.expiry_date >= dateadd(month, 1, convert(varchar, CURRENT_TIMESTAMP, 112));

The "Inside SQL Server 2005" books are good ones for learning advanced TSQL programming.

adomd.net cache server object

Hi All,

I have a bit of a dilemma regarding how to cache the server object so I don't have to reconnect all the time.

I have a web service which serves AS2005 data, and whenever the cube schema changes (or sometimes just after a process), the server object doesn't return the cubes collection, and I end up having to restart IIS or the app to clear the application obj and start again. Obviously this is not the best. Is there anyway I can detect for schema changes in the server object, and serve up a fresh obj if there has been?

I'm using the following code:

Code Snippet

Dim server As OlapServer.Server = Me.Application.Get(cubeServerStorageKey)
If (server Is Nothing) Then

Dim cubeServer As String = System.Configuration.ConfigurationManager.AppSettings.Get("Analysis Server")
If (String.IsNullOrEmpty(cubeServer)) Then _
cubeServer = "."

Dim cubeServerTimeoutSeconds As String = System.Configuration.ConfigurationManager.AppSettings.Get("Cube Timeout")

server = New OlapServer.Server(cubeServer, Me.RequestData.Database, Int32.Parse(cubeServerTimeoutSeconds))
server.Connect()

Me.Application.Set(cubeServerStorageKey, server)
End If

Return server

hello,

could you please clarify a bit. I believe adomd.net does not have an OlapServer class in it (nor does AMO). So, can you please provide relevant code (i guess of how the OlapServer object returns you the cubes collection, since from the problem description you mention that this is where the problem occurs), because without that code it is hard to tell what the problem might be or how to solve it.

thanks a lot,

|||

Well why would you want to cache such a connection? 100ms routine is hardly worthy of cache. Chaching the return dataset I can see, but connection? Sorry if I misunderstood.

Also, you can use .NET's cache methods to specify an exact timeout for a cached object.

ADODB to textBox

First time poster, I am using MS Access and I have used the following code to get some data. It is as follows:

Code Snippet

Private Sub FillGUI()
On Error Resume Next

Dim myRS2 As New ADODB.Recordset

myRS2.ActiveConnection = CurrentProject.Connection
myRS2.CursorType = adOpenDynamic
myRS2.LockType = adLockOptimisticd
myRS2.Open "SELECT E.SSN, E.LNAME, SUM(W.HOURS) FROM EMPLOYEE E, WORKS_ON W WHERE (E.SSN = W.ESSN)GROUP BY E.SSN, E.LNAME HAVING(SUM(HOURS)) < 40"

MsgBox (myRS2.GetString)
myRS2.MoveFirst

End Sub

and I have the following output:

http://www.angelfire.com/oh5/ohiostate120/untitled1.JPG

This is what I want. However I need this to be in a text box so I have the following code:

Code Snippet

Private Sub FillGUI()
On Error Resume Next

Dim myRS2 As New ADODB.Recordset

myRS2.ActiveConnection = CurrentProject.Connection
myRS2.CursorType = adOpenDynamic
myRS2.LockType = adLockOptimisticd
myRS2.Open "SELECT E.SSN, E.LNAME, SUM(W.HOURS) FROM EMPLOYEE E, WORKS_ON W WHERE (E.SSN = W.ESSN)GROUP BY E.SSN, E.LNAME HAVING(SUM(HOURS)) < 40"

Me.txtEmployee.SetFocus
Me.txtEmployee.Text = myRS2.GetString
myRS2.MoveFirst

End Sub

And i get this:

http://www.angelfire.com/oh5/ohiostate120/untitled2.JPG

How can I get the text box format to look like the msgbox format? Thanks.........

This is not an SSIS question.

You might try asking in the "Where is the forum for...?" forum to see if someone there can direct you to the correct place to ask your Access question.

Forum: http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=881&SiteID=1

Thanks,
Phil Brammer|||Yeah I thought I put it in the wrong place. Thanks

ADO Recordset time out during query..PLEASE HELP

PLEASE HELP!
I have a large query that takes about a minute to run from the SQL Query
Analyzer. Now when I run the query from VB6 it craps out 30 seconds into it.
Is there a way around this without manipulating my query. I'm attaching the
query maybe you'll see something I can adjust i'm not a SQL guy so my query
may come off as crude but it works.... sort of!
"SELECT Master.* , Projects.VolumeCode as Expr1 FROM Master
INNER JOIN projects ON Master.ProjectNumber = projects.ProjectNumber
WHERE (Master.TSRDate >= CONVERT(DATETIME, '2004-01-01 00:00:00', 102))AND
(Master.TSRDate < CONVERT(DATETIME, '" + NewDateString + "', 102))
AND (Master.TSRDate + Projects.HoldTime < CONVERT(DATETIME, '" +
NewDateString + "', 102)) AND (Master.ProjectNumber >10)
AND (Not (Master.ProjectNumber=5682))
AND (Not (Master.ProjectNumber=4520))
AND (Not (Master.ProjectNumber=5535))
AND (Not (Master.ProjectNumber=6042))
AND (Not (Master.ProjectNumber=5867))
AND (Master.VolumeID Is Null)
ORDER BY Master.recordnumber;"
Thanks
Rob
IT guy!
at very quick glance,
is there a nonclustered index on ProjectNumber column?
is there a clustered index on the recordnumber column?
you might need to check Index Tunning Wizard for suggestions of possible
indexes
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:C9D32D06-A9E8-450D-B53B-F45860A8D7C3@.microsoft.com...
> PLEASE HELP!
> I have a large query that takes about a minute to run from the SQL Query
> Analyzer. Now when I run the query from VB6 it craps out 30 seconds into
it.
> Is there a way around this without manipulating my query. I'm attaching
the
> query maybe you'll see something I can adjust i'm not a SQL guy so my
query
> may come off as crude but it works.... sort of!
> "SELECT Master.* , Projects.VolumeCode as Expr1 FROM Master
> INNER JOIN projects ON Master.ProjectNumber = projects.ProjectNumber
> WHERE (Master.TSRDate >= CONVERT(DATETIME, '2004-01-01 00:00:00', 102))AND
> (Master.TSRDate < CONVERT(DATETIME, '" + NewDateString + "', 102))
> AND (Master.TSRDate + Projects.HoldTime < CONVERT(DATETIME, '" +
> NewDateString + "', 102)) AND (Master.ProjectNumber >10)
> AND (Not (Master.ProjectNumber=5682))
> AND (Not (Master.ProjectNumber=4520))
> AND (Not (Master.ProjectNumber=5535))
> AND (Not (Master.ProjectNumber=6042))
> AND (Not (Master.ProjectNumber=5867))
> AND (Master.VolumeID Is Null)
> ORDER BY Master.recordnumber;"
> Thanks
> --
> Rob
> IT guy!
|||In addition to tuning the sql statement and making sure that you have
appropriate indexes in place, have you tried increasing the .CommandTimeout
(within your VB code)?
Keith
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:C9D32D06-A9E8-450D-B53B-F45860A8D7C3@.microsoft.com...
> PLEASE HELP!
> I have a large query that takes about a minute to run from the SQL Query
> Analyzer. Now when I run the query from VB6 it craps out 30 seconds into
it.
> Is there a way around this without manipulating my query. I'm attaching
the
> query maybe you'll see something I can adjust i'm not a SQL guy so my
query
> may come off as crude but it works.... sort of!
> "SELECT Master.* , Projects.VolumeCode as Expr1 FROM Master
> INNER JOIN projects ON Master.ProjectNumber = projects.ProjectNumber
> WHERE (Master.TSRDate >= CONVERT(DATETIME, '2004-01-01 00:00:00', 102))AND
> (Master.TSRDate < CONVERT(DATETIME, '" + NewDateString + "', 102))
> AND (Master.TSRDate + Projects.HoldTime < CONVERT(DATETIME, '" +
> NewDateString + "', 102)) AND (Master.ProjectNumber >10)
> AND (Not (Master.ProjectNumber=5682))
> AND (Not (Master.ProjectNumber=4520))
> AND (Not (Master.ProjectNumber=5535))
> AND (Not (Master.ProjectNumber=6042))
> AND (Not (Master.ProjectNumber=5867))
> AND (Master.VolumeID Is Null)
> ORDER BY Master.recordnumber;"
> Thanks
> --
> Rob
> IT guy!

ADO initialization fails on W2003

OVERVIEW:
ADO initialization fails on W2003 Server, Standard Edition.
This is a new system.
This is the first time this app has been compiled or run on this system.
The app runs fine on all other servers I've tried it on.
What should I check?
Is anyone else able to run ADO routines against OLE 5.2?
Thanks,
Brad.
DETAILS:
OLE32.dll v.5.2.3790.250
OLEAUT32.dll v.5.2.3790.0
Tracing into the code, I get into
_WStrAsg("", "'ISAUTOINCREMENT'");
which calls SysReAllocStringLen. This fails with an access violation.
SysReAllocStringLen is a call into OLEAUT32.dll.
All the OLE32 dll look to be updated. They are newer than anything
on any of my XP systems.
I have a new test project that uses ADODB and it *doesn't* fail.
Yes, it does go through the same code just as nice as you could want.
The same app is deployed an running on dozens of other systems.
None are as new as this one.
So my guess is that it has to be something about the way the new
system is set up.
My next step is to start tearing down a copy of my project. But I'm
not terribly hopeful.
Thanks,
Brad.Long shot but is the bad project compiled on the same library as the working
one. Also, have you updated to the latest mdac + fixes.
-oj
"Brad White" <bwhite at inebraska . com> wrote in message
news:OQLP18vDFHA.2540@.TK2MSFTNGP09.phx.gbl...
> OVERVIEW:
> ADO initialization fails on W2003 Server, Standard Edition.
> This is a new system.
> This is the first time this app has been compiled or run on this system.
> The app runs fine on all other servers I've tried it on.
> What should I check?
> Is anyone else able to run ADO routines against OLE 5.2?
> Thanks,
> Brad.
> DETAILS:
> OLE32.dll v.5.2.3790.250
> OLEAUT32.dll v.5.2.3790.0
> Tracing into the code, I get into
> _WStrAsg("", "'ISAUTOINCREMENT'");
> which calls SysReAllocStringLen. This fails with an access violation.
> SysReAllocStringLen is a call into OLEAUT32.dll.
> All the OLE32 dll look to be updated. They are newer than anything
> on any of my XP systems.
> I have a new test project that uses ADODB and it *doesn't* fail.
> Yes, it does go through the same code just as nice as you could want.
> The same app is deployed an running on dozens of other systems.
> None are as new as this one.
> So my guess is that it has to be something about the way the new
> system is set up.
> My next step is to start tearing down a copy of my project. But I'm
> not terribly hopeful.
> --
> Thanks,
> Brad.
>
>
>|||Thanks for the reply.
The first thing I checked was MDAC, and yes it is 2.8.
I thought last night that perhaps it is a 2003 thing,
but no, we have had installations for months on 2003 Server
with no problems.
Same libraries. Tried the debug versions of the libraries in both exe's
with same results.
"oj" <nospam_ojngo@.home.com> wrote in message
news:uMArKKwDFHA.2032@.tk2msftngp13.phx.gbl...
> Long shot but is the bad project compiled on the same library as the
working
> one. Also, have you updated to the latest mdac + fixes.
> --
> -oj
>
> "Brad White" <bwhite at inebraska . com> wrote in message
> news:OQLP18vDFHA.2540@.TK2MSFTNGP09.phx.gbl...
>|||Brad,
Perhaps, reinstall the package?
-oj
"Brad White" <bwhite at inebraska . com> wrote in message
news:%23LzC%23$3DFHA.3924@.TK2MSFTNGP09.phx.gbl...
> Thanks for the reply.
> The first thing I checked was MDAC, and yes it is 2.8.
> I thought last night that perhaps it is a 2003 thing,
> but no, we have had installations for months on 2003 Server
> with no problems.
> Same libraries. Tried the debug versions of the libraries in both exe's
> with same results.
> --
> "oj" <nospam_ojngo@.home.com> wrote in message
> news:uMArKKwDFHA.2032@.tk2msftngp13.phx.gbl...
> working
>|||"oj" <nospam_ojngo@.home.com> wrote in message
news:u34aoVAEFHA.3732@.TK2MSFTNGP14.phx.gbl...
> Brad,
> Perhaps, reinstall the package?
>
Thanks for the reply.
The way it was acting reminded me of memory corruption,
but I couldn't see how that could be since no custom code
had run yet.
Turns out it was an unrelated dll improperly installed.
Reinstalling that to solve another problem, solved this problem.
It wasn't an ADO thing or WideStr thing at all.
--
Brad White

Adjusting Time Zone and Daylight Saving in SQL

Hi,

I have this simple SQL query which SELECTs the fields according to the criteria in WHERE clause.

SELECT callingPartyNumber, originalCalledPartyNumber, finalCalledPartyNumber,dateadd(ss, (dateTimeConnect + (60 * 60 * -5))+3600 ,'01-01-1970 00:00:00')AS dateTimeConnect,dateadd(ss, (dateTimeDisconnect + (60 * 60 * -5))+3600,'01-01-1970 00:00:00')AS dateTimeDisconnect,CONVERT(char(8),DATEADD(second, duration,'0:00:00'), 108)AS durationFROM CallsWHERE (callingPartyNumber =ISNULL(@.callingPartyNumber, callingPartyNumber))AND (originalCalledPartyNumber =ISNULL(@.originalCalledPartyNumber, originalCalledPartyNumber))AND (finalCalledPartyNumber =ISNULL(@.finalCalledPartyNumber, finalCalledPartyNumber))AND (duration >= @.theDuration)AND ((datetimeConnect - 14400) >=ISNULL(convert(bigint,datediff(ss,'01-01-1970 00:00:00', @.dateTimeConnect)), datetimeConnect))AND ((dateTimeDisconnect - 14400) <=ISNULL(convert(bigint,datediff(ss,'01-01-1970 00:00:00', @.dateTimeDisconnect)), dateTimeDisconnect))

If you notice, in the SELECT, I add 3600 (1 hour) to adjust the time according to the current DayLight saving setting. Similarly, while comparing in WHERE clause, I subtract 14400 seconds (4 hours) to adjust the comparison according to EST (-5 GMT, but doing -4 GMT since daylight saving) and daylight saving.

Also, dateTimeConnect and dataTimeDisconnect fields save time in UTC seconds.

Now this application may be used by clients in different timezones. The application may be hosted on their server which would mean a different time zone. What would be the best way to make this time zone and daylight adjustment more dynamic instead of hardcoding the values.

I think the conversions need to be done at the client, not at the database.

Say you have a user in Moscow and another user in Paris, and they are using the same database: How will the database know how many hours to add/subtract? It will be different for each client.

The clients should get the time in UTC and convert it to their local zone for displaying.

The clients should convert their time to UTC before sending it to the database.

FYI: This stuff is really complicated: Did you know that Arizona has its own "time zone" because they do not observe DST like the rest of the country?

|||

Ya I know. They like to make things complicated. I agree with what you are saying, do you have any suggestions on how to do that? This is my applications scenario:

ASP.NET application (C#, doesn't matter though) and SQL Server database with several stored procedures. The SQL I mentioned is one of them. A databound gridview to diplay the results. I am already using formatting for the datetime columns, so I have 4 datetime columns (2 pull dateTimeConnect and the other 2 pull dateTimeDisconnect). I format these columns using DataFormatString to display date in one and time in other (I do this because I get date and time both in one field so I use formatting to split them).

Now where should I do the conversions? I checked the format strings for UTC but they don't adjust the timezone and/or DST.

|||

You can convert using DateTime functions:

 DateTime NowLocal; DateTime NowUTC; NowLocal = DateTime.Now; NowUTC = NowLocal.ToUniversalTime(); NowLocal = NowUTC.ToLocalTime();

Convert to UTC just before you update the database.

Convert to LocalTime before you format/display the date.

|||

SGWellens:

Convert to LocalTime before you format/display the date

I am stuck with this one. Since I am using gridview to display data (which comes from stored procedures), where will I do the conversion from UTC to local time. Gridview columns are boundfield columns.

|||

This is kind of 'klunky' but it works:

protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e) {// Convert UTC to local times (DateTime field is column 1)if (e.Row.RowType == DataControlRowType.DataRow) { DateTime TempDateTime;// convert text in GridView cell to DateTime TempDateTime = DateTime.Parse(e.Row.Cells[1].Text);// convert UTC to LocalTime TempDateTime = TempDateTime.ToLocalTime();// replace text in GridView cell e.Row.Cells[1].Text = TempDateTime.ToString(); } }

|||

Thanks Steve, I don't know why I always forget the RowDataBound event. This surely will work. I will work on this and post here if any problems.

Thanks again, I really appreciate it.

|||

Hmmm...I am getting a Format exception in DateTime.Parse method:

String was not recognized as a valid DateTime.

This is what I am trying to pass thru the Parse method:

1187033495 //this is datetime in UTC seconds.

I tried this:

DateTime tempDateTime =new DateTime(Convert.ToInt64(e.Row.Cells[4].Text));tempDateTime = tempDateTime.ToLocalTime();e.Row.Cells[4].Text = tempDateTime.ToString();

But the constructor takes the argument as number of ticks (number of 100 nanosecond intervals since midnight on 1 January of year 1) which obviously gives me all crap.

|||

You can do ticks like this (you should be able to convert seconds to ticks):

if (e.Row.RowType == DataControlRowType.DataRow) {long Ticks;// Cell has Ticks as text, convert to long Ticks =long.Parse(e.Row.Cells[0].Text);// now convert to DateTime DateTime TempDateTime =new DateTime(Ticks);// convert UTC to LocalTime TempDateTime = TempDateTime.ToLocalTime();// replace text in GridView cell e.Row.Cells[0].Text = TempDateTime.ToString(); }

|||

Hi Steve,

NowLocal = NowUTC.ToLocalTime();

I believe above statement always gives Server's Local Time not Client Machine's Local Time. How can I display Client's Local Time?

|||

venu_babu80:

I believe above statement always gives Server's Local Time not Client Machine's Local Time.

I believe you are correct.

Here is code to get the client's timezone offset. A problem is you can't get the client's timezone until the page loads at the client's at least once. Ideally, you would get the timezone offset and store it in a Session variable before the page with the GridView is loaded. But you may want to do that anyway for when you need to convert TO UTC time from client time.

<head runat="server"> <script> function GetClientsUTCOffset() { var DateNow = new Date(); // UTCOffset is in minutes document.forms[0]["UTCOffset"].value = DateNow.getTimezoneOffset(); } </script><body onload="GetClientsUTCOffset();"> <form id="form1" runat="server"> <input type="hidden" runat="server" id="UTCOffset" />

protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e) {long Ticks;// Cell has Ticks as text, convert to long Ticks = DateTime.Now.Ticks;// now convert to DateTime DateTime TempDateTime =new DateTime(Ticks);// convert UTC to LocalTime // note: when first databinding takes place, page hasn't loaded // at the client yet and UTCOffset will not be availableif (UTCOffset.Value !="") {int UTCOffsetMinutes =int.Parse(UTCOffset.Value); TempDateTime = TempDateTime.AddMinutes(UTCOffsetMinutes);// replace text in GridView cell e.Row.Cells[0].Text = TempDateTime.ToString(); } }

|||

Thanks so much Steve. I am working on this...

|||

Ok...I am getting there but I am still a few years behind. For example, here is the sample data:

UTC seconds = 1187092422

ticks = UTC seconds * 10000000 = 11870924220000000

The correct date and time should be: 08/14/2007 07:53:42 AM

What I am getting: 8/14/0038 7:53:42 AM

So you see, I am a few years behind.

Code used:

long ticks;ticks =long.Parse(e.Row.Cells[4].Text) * 10000000;DateTime tempDateTime =new DateTime(ticks);tempDateTime = tempDateTime.ToLocalTime(); e.Row.Cells[4].Text = tempDateTime.ToString();

Update:

I noticed that 0038 is 1969 years off from current year. Since I also know that ticks is from 01/01/01 and UTC is from 1970, I figured that adding 1969 years to the datetime value would give me the correct value. This is what I did:

DateTime tempDateTime =new DateTime(ticks).AddYears(1969);

I just want to confim if this is the right way to do it and if I will get a consistent value every time, every year.|||

You shouldn't have to add years.

protected void Button1_Click(object sender, EventArgs e) {const long TICKS_PER_SEC = 10000000;// 1 tick = 100 nanosecond DateTime From, To; From = DateTime.Parse("08/14/2007 07:53:42 AM");long Ticks = From.Ticks;long Seconds = Ticks / TICKS_PER_SEC;// Note: Seconds is 63322674822 To =new DateTime(Seconds * TICKS_PER_SEC); }

|||

SGWellens:

From = DateTime.Parse("08/14/2007 07:53:42 AM");

I have date and time in UTC seconds.

SGWellens:

To =new DateTime(Seconds * TICKS_PER_SEC);

This is what I am doing (though my seconds value is UTC seconds).

How will your last code take care of the UTC factor. Ticks and UTC are calculated from two different starting points.