DataTime as parameters: Help Needed.

Hello all,
I need to generate a report based on dates(from and to). Is there
anyway I can include a dropdown of datetime in the parameters of the RS
interface.
for example like from mm/dd/yyyy to mm/dd/yyyy.
I have RS 2003 EE with SP1 and have no plans of installing SP2.
Any help will be appreciated.
Thanks a lot
RaviRavi,
You need to create a list of dates as a DS, and then add two params to the
report (well, just add them to the DS for the report itself) and configure
the Params to be query selections from the Dates DS.
Using Adventurewroks DB, the following suffices for selecting particular
records of SalesOrderID, ModifiedDate columns from the SalesOrderDetail
table, by date.
DataSets:
DSDates:
SELECT ModifiedDate, CONVERT(char(20), ModifiedDate, 101) AS dateselect
FROM dbo.SalesOrderDetail
GROUP BY ModifiedDate
ORDER BY ModifiedDate
DS1:
SELECT SalesOrderID, ModifiedDate
FROM dbo.SalesOrderDetail
WHERE (ModifiedDate > @.STARTDATE AND ModifiedDate < @.ENDDATE)
Form:
Table with DS1 as it's source.
Remember to edit the params in Report-Report Parameters, for both @.STARTDATE
AND @.ENDDATE, to be;
From Query; Dataset:DSDates;ValueField:ModifiedDate;LabelField:dateselect.
If you leave out the dateselect column from the first DS then the dropdown
will default to 00:00:00 time, and looks awful.
Hope this helps,
Tony
"Ravi R" wrote:
> Hello all,
>
> I need to generate a report based on dates(from and to). Is there
> anyway I can include a dropdown of datetime in the parameters of the RS
> interface.
> for example like from mm/dd/yyyy to mm/dd/yyyy.
> I have RS 2003 EE with SP1 and have no plans of installing SP2.
> Any help will be appreciated.
> Thanks a lot
> Ravi
>|||Ravi,
In addiditon to my last reply, if you want to make 'intelligent' parameters,
then your end date should be greater than your start date.
To achieve this, complete tasks as per my previous reply and add the
following;
Create a further DS called DSDates2.
SELECT ModifiedDate, CONVERT(char(20), ModifiedDate, 101) AS dateselect
FROM dbo.SalesOrderDetail
GROUP BY ModifiedDate
HAVING (ModifiedDate > @.STARTDATE)
ORDER BY ModifiedDate
and change the source for the @.ENDDATE parameter to point to DSDates2.
You will then find the End Date drop down is disabled until the Start Date
is selected.
If you wish, you can set a default for the Start Date as being the first
date found in the table (Use Top 1 selected from DSDates) for extra
useability.
Hope this assists further,
Tony
"Ravi R" wrote:
> Hello all,
>
> I need to generate a report based on dates(from and to). Is there
> anyway I can include a dropdown of datetime in the parameters of the RS
> interface.
> for example like from mm/dd/yyyy to mm/dd/yyyy.
> I have RS 2003 EE with SP1 and have no plans of installing SP2.
> Any help will be appreciated.
> Thanks a lot
> Ravi
>|||thanks logicalman,
will try that first thing on monday.
Ravi

DataSet to SQL script?

Hi,Does anyone have a way to generate a SQL 2000 database generation script from a DataSet? I'm looking to load an XML file into a DataSet and use it to create the db design.Matt.

You can load elements/attributes into fields of database tables, which contains information used to create a database. Then you can query out the information, and use it in dynamic sql statement. For example if we want to create such a database using DataFileName, LogFileName, DatabaseName, Size, FileGrowth frommyTable:

DECLARE @.DFN sysname,@.LFN sysname, @.DBN sysname
DECLARE @.Size varchar(4),@.FG varchar(4)
SELECT @.DFN=DataFileName, @.LFN=LogFileName, @.DBN=DatabaseName,
@.Size=Size, @.FG=FileGrowth
FROMmyTable

--select @.DFN='c:\saledat.mdf',@.LFN='c:\salelog.ldf',@.DBN='Sales',
--@.Size='10MB',@.FG='5MB'
EXEC('CREATE DATABASE'+@.DBN+'
ON
( NAME = Sales_dat,
FILENAME ='''+@.DFN+''',
SIZE ='+@.Size+',
MAXSIZE = 50,
FILEGROWTH ='+@.FG+' )
LOG ON
( NAME = Sales_log,
FILENAME ='''+@.LFN+''',
SIZE ='+@.Size+',
MAXSIZE = 25,
FILEGROWTH ='+@.FG+' )')

Note: the aboving create database statement recieves @.Size and @.FG as varchar datatype.

|||Thanks, but I was under the impression that the BulkInsert3 method (SQLXMLBULKLOADLib.SQLXMLBulkLoad3Class.Execute() found in xblkld3.dll) would take an xml schema and xml file and actually create the tables, within a specified database, if they were not already present.Do you know if this is actually possible?Matt.|||Actually, to clarify, I would like an automated version of the article mentioned previously (http://msdn.microsoft.com/msdnmag/issues/03/05/MetaDataServices/) in order to generate a db (well, the tables) script.This comes from seeing Visual Studio open an XML file, click on XML->Create Schema and View Designer. This shows what I would derive a db table design from.However, currently I am loading the XML into a DataSet and generating the xml schema from that. I may have to simply generate the database tables from looking at the DataSet and then use the bulk insert library.Obviously, my intention here is to do as much of the work with automation as possible (as any good/lazy coder would) but it just looks like some crowbarring is needed.Unless anyone knows how to generate a databases tables from the XML/XML schema or DataSet directly?Matt.

Database wont start in cluster - cannot generate SSPI context

Hi,
I have a MSSQL database running in a Microsoft cluster. I was working on troubleshooting for another SAP problem when they suggested I change the setting using hte SQL Server Client Network Utility to 'named pipes' from 'tcp/ip'. Every since, despite changing it back I'm unable to start the database in the cluster.

I'm kind of a newbie at this so I'd really appreciate any help in getting this database up and running.

Here is the error message from the event log:
sqsrvres] checkODBCConnectError: sqlstate = HY000; native error = 0; message = [Microsoft][ODBC SQL Server Driver]Cannot generate SSPI contexthttp://www.sqlservercentral.com/columnists/cmiller/cannotgeneratesspicontext.asp
http://support.microsoft.com/?id=811889
and
http://blogs.msdn.com/sql_protocols/archive/2005/10/19/482782.aspx|||Hi,
Thanks for your help. The solution was to change the /etc/hosts 127.0.0.1 to the SQL Server instance name. Everything is up and running now.
The line in my /etc/hosts used to read:
127.0.0.1 localhost
Now it reads:
127.0.0.1 <instance name>
#127.0.0.1 localhost
Regards,
Jeff

Database Version

Hello

if we generate a database file on a particular version of ms sql express and in time to come if there are a few more versions of ms sql express out there with the users - if we continue to package with our app a database file from an older version of sql express will that be a problem with users running a newer version of sql express ?

Also what about the converse scenario - where we ship a database file from a newer version of sql while the customer is still running on an older version.

Thanks,

Avinash

Hi,

normally you speak out only the support where you are sure that it works. It you are a userfriendly vendorf :-) you would check the database version and give a message box that the software can′t be installed or will be installed at their own risks because this version isn′t supported by you. Othwise, if you have a fire-and-forget attitude (like some software vendors) you only write the prerequisites on your box or readme.txt stating that the users *should* read that carefully or otherwise won′t get any support.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Many thanks for your response.

What I wanted to actually know is; since being able to do an auto attach on a database file and because applications in general and smart clients in particular have the ability to bring with them their own database file; surely in time to come there may be several versions of sql express out there with the users.

From your earlier email, I undertand that you are suggesting we perform some kind of a defensive check before we use the database file

But I was curious to hear of any features - upgrade/migration features that express has within it. For example there should be no problem opening a word 97 document in word 2000 but the otherway round may not be possible. But it may not be fully dysfunctional aswell. For example - may be the limitation is that (hypothetically) x, y and z features may not be rendered/rendered corectly. But the document itself should be rendered minus support for the newer features.

Although it may quite a while before the next version of express comes along, I wanted to get an understanding of what potential situations we will need to prepare for with our customers and also what measures we can take now to hopefully ease support to our customers later.

I'm not sure if I've been able to explain what I was unsure about or require clarifications on. Pl let me know if I need to clarify any of my questions.

Thanks and Regards,

Avinash

PS: It will be great if you can point me to some documentation that talks about the issues that I'm not certain about.

|||

Hi,

don′t get me wrong, in th most cases you are sure right. There is an automatical upgrade path with attaching databases, even if their service pack level differs, but I wouldn′t rely on that. Although this is a nice cool feature, we would have to go the like the one I mentiond above in Germany due to law restrictions / warranty.

As the version of SQL Server Express and 2000 / 7.0 are upgrade compatible , there sure will be an automated way to upgrade the SQL Server Express version among each other. But due to database changes on the main successor version of SQl Server they are not (AFAIK and my experience is) downgrade compatible.

What can you do ? -Testing testing testing prior to sending out your database version. Thats a way to document that you did your best to eliminate errors. What of a customers calls you and tells "Hey, I did upgrade my database from Version 9.0.2043 to your new version and this didn′t work and my database is crashed."

What about you telling then that you don′t know if that particular version is not compatible for upgrading ? So you would have to go the test afterwards, inform all customers to whom you shipped the upgrade product and extinguish the burning house at the customer site.

But on the other side, what about telling the customers that all version were tested for upgrading and had gone though a quality test at your center and this is a customer specific problem ?

I guess the latter one is more relaxing for the vendors, becasue they can proof that they took care to this scenarios.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Many thanks Jens Suessmeyer. I think I understand what you are saying.

Thanks and Regards,

Avinash