Hello.
I'm researching about the ways I can create an installer of the SQL (2000)
objects for an application.
At the present time we're using a set of calls to osql utility from a VB
application. But the maintenance of the scripts is becoming cumbersome.
Does MS provides utilities for this? Where can I look up for further info?
Thanks.-
| Thread-Topic: Creating an installer for SQL-side components of an app
| thread-index: AcThIuI4MycuAx2CSYGbOG6c0kGPaQ==
| X-WBNR-Posting-Host: 200.44.173.82
| From: =?Utf-8?B?cnBhbGxhcmVz?= <rpallares@.discussions.microsoft.com>
| Subject: Creating an installer for SQL-side components of an app
| Date: Mon, 13 Dec 2004 06:49:01 -0800
| Lines: 12
| Message-ID: <CF1D14A9-81F6-4891-BCCD-4D8299366B7D@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.clients
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.1.29
| Path: cpmsftngxa10.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.clients:29256
| X-Tomcat-NG: microsoft.public.sqlserver.clients
|
| Hello.
|
| I'm researching about the ways I can create an installer of the SQL
(2000)
| objects for an application.
|
| At the present time we're using a set of calls to osql utility from a VB
| application. But the maintenance of the scripts is becoming cumbersome.
|
| Does MS provides utilities for this? Where can I look up for further info?
|
| Thanks.-
|
|
<><><><><><><><><><><><><><><><><><><><><><><><><> <><><>
Hi,
If you still need assistance and you're using MSDE then I believe you will
find this link useful:
http://msdn.microsoft.com/library/de...us/dnmsde/html
/msdedepl.asp
Regards,
Yasemin Gunduz
Support Engineer
This posting is provided "AS IS" with no warranties, and confers no rights.
|||What u mean with the SQL server Objects ?
"rpallares" <rpallares@.discussions.microsoft.com> wrote in message
news:CF1D14A9-81F6-4891-BCCD-4D8299366B7D@.microsoft.com...
> Hello.
> I'm researching about the ways I can create an installer of the SQL (2000)
> objects for an application.
> At the present time we're using a set of calls to osql utility from a VB
> application. But the maintenance of the scripts is becoming cumbersome.
> Does MS provides utilities for this? Where can I look up for further info?
> Thanks.-
>
Showing posts with label time. Show all posts
Showing posts with label time. Show all posts
Thursday, March 29, 2012
Tuesday, March 27, 2012
creating alerts
I want to create an alert that for each time a sql view is deleted from the server alerts an operator and runs a sql query to recreate
I have the code for recreating the view and this can be manually invoked and need some help in automating this other than create a job to run at scheduled intervals as this is only needs executed when treh views have been deleted
**views are deleted by a rogue application processWouldn't it be better to find the process that is deleteing the iew(s) and stop it?
I have the code for recreating the view and this can be manually invoked and need some help in automating this other than create a job to run at scheduled intervals as this is only needs executed when treh views have been deleted
**views are deleted by a rogue application processWouldn't it be better to find the process that is deleteing the iew(s) and stop it?
Sunday, March 25, 2012
creating a time dimension
this is my first cube using 2005 analysis services. i have been using 2000 Analysis Services for about five years.
i have a cube with about 5 small dimensions and am trying to add a time dimension to include some of the Year to Year and Month to Month calculations.
i have a table with Year in column 1 and Month in column 2 mapped to the fact table.
before i add the time dimension everyting processes fine. after i add the Year-Month Hierarchy, the processing starts and gets to the SQL query to populate the cube but seems to go into a loop and never completes.
i don't have a clue what is going on.
does anyone have a suggestion?
Just want to make sure I understand the scenario ....
You have a Time dimension table with a structure similar to this:
create table DIM.Time (
TimeID int not null identity(1,1),
Year int null,
Month int null
...
)
Your fact table has a structure similar to this:
create table FACT.MyFact (
DimID int not null,
OtherDimID int not null,
TimeID int not null,
Measure1 money null,
...
)
Is this correct?
If so, do you get the "loop" when you process the Time dimension or just when you process the cube?
Thanks,
Bryan
|||the dim time does not have a time id just year and month.
the fact table also has year and month associated with each fact.
the fact year and month are mapped to the dim year and month.
and the dim table is used for the dimension generation.
i also tried using the fact table year and month for the dimension generation with the same result.
the loop happens when i process the cube.
|||I'd recommend using a single key for all foreign key references. If you don't have a surogate key for time, at least have a single smart key of YYYYMM in the fact table that references a single key in the dimension. Worst case, you can combine the year and month values in the fact table and the time dimension tables through the DSV.
Not 100% certain that's the source of your exact problem, but its worth giving it a shot.
B.
That worked!
thanks.
i guess this version of Analysis Services is more traditionally Relational.
sqlcreating a time dimension
this is my first cube using 2005 analysis services. i have been using 2000 Analysis Services for about five years.
i have a cube with about 5 small dimensions and am trying to add a time dimension to include some of the Year to Year and Month to Month calculations.
i have a table with Year in column 1 and Month in column 2 mapped to the fact table.
before i add the time dimension everyting processes fine. after i add the Year-Month Hierarchy, the processing starts and gets to the SQL query to populate the cube but seems to go into a loop and never completes.
i don't have a clue what is going on.
does anyone have a suggestion?
Just want to make sure I understand the scenario ....
You have a Time dimension table with a structure similar to this:
create table DIM.Time (
TimeID int not null identity(1,1),
Year int null,
Month int null
...
)
Your fact table has a structure similar to this:
create table FACT.MyFact (
DimID int not null,
OtherDimID int not null,
TimeID int not null,
Measure1 money null,
...
)
Is this correct?
If so, do you get the "loop" when you process the Time dimension or just when you process the cube?
Thanks,
Bryan
|||the dim time does not have a time id just year and month.
the fact table also has year and month associated with each fact.
the fact year and month are mapped to the dim year and month.
and the dim table is used for the dimension generation.
i also tried using the fact table year and month for the dimension generation with the same result.
the loop happens when i process the cube.
|||I'd recommend using a single key for all foreign key references. If you don't have a surogate key for time, at least have a single smart key of YYYYMM in the fact table that references a single key in the dimension. Worst case, you can combine the year and month values in the fact table and the time dimension tables through the DSV.
Not 100% certain that's the source of your exact problem, but its worth giving it a shot.
B.
That worked!
thanks.
i guess this version of Analysis Services is more traditionally Relational.
Thursday, March 22, 2012
Creating a table script that includes the data in the table
Hello,
I'm having a bear of a time moving my data from one database to another. Unfortunately both these databases are at webhosting providers so I don't readily have access to backup images. I might be able to get the new provider to put the backup image somewhere where I can reach it but the old provider is playing dead so I can't get to any backup image I could generate.
I've tried using Enterprise Manager for SQL2000 to move data from one to another. This grinds away for a while and eventually errors out with permission problems. I will try this again tonight as the provider has attempted to give me the needed permissions. In the meantime I'd like to try to pull the data out of the database just in case it goes belly-up. I've tried to create a script for my tables but all I get is the table structure, no data.
I don't have access to a full Enterprise Manager right now, only SQL Server Management Studio Express (someone needs to shorten that name :).
I'm a big SQL Server fan but it sure is easy to create one big SQL script for a MySQL databsae that you pull out of one db and apply to another one. Poof! Cloned copy. How do I do this with MSSQL and freely available tools? Command line is fine. Surely this is a problem that people have all the time?
Thanks,
Sander
Hi,
for a TSQL solution this could do the trick for you:
http://vyaskn.tripod.com/code.htm#inserts
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
thanks for posing this solution! I succeeded in the meantime to copy the data using EM but I still want this tool to work so I'll give that a try as well.
Thanks,
Sander
Monday, March 19, 2012
Creating a report
Hi,
Im new to dot net and CR.
I would like to create a a report based on an SQL query at run time.
How do i do it.
Tnx
PapsHave you tried doing a search on this forum or on Google or Crystal Report's website? There's tons of information on the many different ways to do Crystal Reports, you just have to a bit of digging!
Crystal Reports:
http://support.businessobjects.com/search/advsearch.asp
Google:
http://www.google.com
Crystal Reports Forum:
http://support.businessobjects.com/forums/default.asp
Im new to dot net and CR.
I would like to create a a report based on an SQL query at run time.
How do i do it.
Tnx
PapsHave you tried doing a search on this forum or on Google or Crystal Report's website? There's tons of information on the many different ways to do Crystal Reports, you just have to a bit of digging!
Crystal Reports:
http://support.businessobjects.com/search/advsearch.asp
Google:
http://www.google.com
Crystal Reports Forum:
http://support.businessobjects.com/forums/default.asp
Sunday, March 11, 2012
Creating a Monthend Database
Before I moved all the Access Databases to SQL I was able to create a monthend database for each database we had. It wasn't too time consuming as it was a simple copy and paste each month. Now that everything is moved over to SQL I had told the Acctg De
pt that I would prefer not to continue with the monthend companies as it is more time consuming to administer. All they use the monthend copies for are for reference if needed throughout the month. When I questioned how much they actually refer back to
it, it is not very often. They were quite persistent and old habits are hard to break so I need to find an efficient way of creating the monthend databases and then copying them each month. Does anyone have any suggestions? Your input is great apprecia
ted!
Thanks
Deb
If your month-end database is simply a snapshot of your operational database
at a specific point in time, you can copy the database to different database
and file names using backup/restore.
Hope this helps.
Dan Guzman
SQL Server MVP
"Deb" <anonymous@.discussions.microsoft.com> wrote in message
news:982F6016-7AE2-499D-9634-9BE97BA4223B@.microsoft.com...
> Before I moved all the Access Databases to SQL I was able to create a
monthend database for each database we had. It wasn't too time consuming as
it was a simple copy and paste each month. Now that everything is moved
over to SQL I had told the Acctg Dept that I would prefer not to continue
with the monthend companies as it is more time consuming to administer. All
they use the monthend copies for are for reference if needed throughout the
month. When I questioned how much they actually refer back to it, it is not
very often. They were quite persistent and old habits are hard to break so
I need to find an efficient way of creating the monthend databases and then
copying them each month. Does anyone have any suggestions? Your input is
great appreciated!
> Thanks
> Deb
pt that I would prefer not to continue with the monthend companies as it is more time consuming to administer. All they use the monthend copies for are for reference if needed throughout the month. When I questioned how much they actually refer back to
it, it is not very often. They were quite persistent and old habits are hard to break so I need to find an efficient way of creating the monthend databases and then copying them each month. Does anyone have any suggestions? Your input is great apprecia
ted!
Thanks
Deb
If your month-end database is simply a snapshot of your operational database
at a specific point in time, you can copy the database to different database
and file names using backup/restore.
Hope this helps.
Dan Guzman
SQL Server MVP
"Deb" <anonymous@.discussions.microsoft.com> wrote in message
news:982F6016-7AE2-499D-9634-9BE97BA4223B@.microsoft.com...
> Before I moved all the Access Databases to SQL I was able to create a
monthend database for each database we had. It wasn't too time consuming as
it was a simple copy and paste each month. Now that everything is moved
over to SQL I had told the Acctg Dept that I would prefer not to continue
with the monthend companies as it is more time consuming to administer. All
they use the monthend copies for are for reference if needed throughout the
month. When I questioned how much they actually refer back to it, it is not
very often. They were quite persistent and old habits are hard to break so
I need to find an efficient way of creating the monthend databases and then
copying them each month. Does anyone have any suggestions? Your input is
great appreciated!
> Thanks
> Deb
Creating a Monthend Database
Before I moved all the Access Databases to SQL I was able to create a monthend database for each database we had. It wasn't too time consuming as it was a simple copy and paste each month. Now that everything is moved over to SQL I had told the Acctg Dept that I would prefer not to continue with the monthend companies as it is more time consuming to administer. All they use the monthend copies for are for reference if needed throughout the month. When I questioned how much they actually refer back to it, it is not very often. They were quite persistent and old habits are hard to break so I need to find an efficient way of creating the monthend databases and then copying them each month. Does anyone have any suggestions? Your input is great appreciated
Thank
DebI'm going to start with an assumption. What you are
talking about in a monthend database (it means different
things to different people) is a reporting thing that you
transfer to the accounting section.
If thats the case then you may want to consider something
like DTS. You can either run this yourself or create a
job in SQL Agent.
With DTS you can set it up so you do a database query
that exports into say excel, which means you do not have
to cut and paste.
If all else fails however you can go back to how you were
doing it until you get something more concrete.
J
>--Original Message--
>Before I moved all the Access Databases to SQL I was
able to create a monthend database for each database we
had. It wasn't too time consuming as it was a simple
copy and paste each month. Now that everything is moved
over to SQL I had told the Acctg Dept that I would prefer
not to continue with the monthend companies as it is more
time consuming to administer. All they use the monthend
copies for are for reference if needed throughout the
month. When I questioned how much they actually refer
back to it, it is not very often. They were quite
persistent and old habits are hard to break so I need to
find an efficient way of creating the monthend databases
and then copying them each month. Does anyone have any
suggestions? Your input is great appreciated!
>Thanks
>Deb
>.
>|||If your month-end database is simply a snapshot of your operational database
at a specific point in time, you can copy the database to different database
and file names using backup/restore.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Deb" <anonymous@.discussions.microsoft.com> wrote in message
news:982F6016-7AE2-499D-9634-9BE97BA4223B@.microsoft.com...
> Before I moved all the Access Databases to SQL I was able to create a
monthend database for each database we had. It wasn't too time consuming as
it was a simple copy and paste each month. Now that everything is moved
over to SQL I had told the Acctg Dept that I would prefer not to continue
with the monthend companies as it is more time consuming to administer. All
they use the monthend copies for are for reference if needed throughout the
month. When I questioned how much they actually refer back to it, it is not
very often. They were quite persistent and old habits are hard to break so
I need to find an efficient way of creating the monthend databases and then
copying them each month. Does anyone have any suggestions? Your input is
great appreciated!
> Thanks
> Deb
Thank
DebI'm going to start with an assumption. What you are
talking about in a monthend database (it means different
things to different people) is a reporting thing that you
transfer to the accounting section.
If thats the case then you may want to consider something
like DTS. You can either run this yourself or create a
job in SQL Agent.
With DTS you can set it up so you do a database query
that exports into say excel, which means you do not have
to cut and paste.
If all else fails however you can go back to how you were
doing it until you get something more concrete.
J
>--Original Message--
>Before I moved all the Access Databases to SQL I was
able to create a monthend database for each database we
had. It wasn't too time consuming as it was a simple
copy and paste each month. Now that everything is moved
over to SQL I had told the Acctg Dept that I would prefer
not to continue with the monthend companies as it is more
time consuming to administer. All they use the monthend
copies for are for reference if needed throughout the
month. When I questioned how much they actually refer
back to it, it is not very often. They were quite
persistent and old habits are hard to break so I need to
find an efficient way of creating the monthend databases
and then copying them each month. Does anyone have any
suggestions? Your input is great appreciated!
>Thanks
>Deb
>.
>|||If your month-end database is simply a snapshot of your operational database
at a specific point in time, you can copy the database to different database
and file names using backup/restore.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Deb" <anonymous@.discussions.microsoft.com> wrote in message
news:982F6016-7AE2-499D-9634-9BE97BA4223B@.microsoft.com...
> Before I moved all the Access Databases to SQL I was able to create a
monthend database for each database we had. It wasn't too time consuming as
it was a simple copy and paste each month. Now that everything is moved
over to SQL I had told the Acctg Dept that I would prefer not to continue
with the monthend companies as it is more time consuming to administer. All
they use the monthend copies for are for reference if needed throughout the
month. When I questioned how much they actually refer back to it, it is not
very often. They were quite persistent and old habits are hard to break so
I need to find an efficient way of creating the monthend databases and then
copying them each month. Does anyone have any suggestions? Your input is
great appreciated!
> Thanks
> Deb
Creating a Monthend Database
Before I moved all the Access Databases to SQL I was able to create a monthe
nd database for each database we had. It wasn't too time consuming as it wa
s a simple copy and paste each month. Now that everything is moved over to
SQL I had told the Acctg De
pt that I would prefer not to continue with the monthend companies as it is
more time consuming to administer. All they use the monthend copies for are
for reference if needed throughout the month. When I questioned how much t
hey actually refer back to
it, it is not very often. They were quite persistent and old habits are har
d to break so I need to find an efficient way of creating the monthend datab
ases and then copying them each month. Does anyone have any suggestions? Y
our input is great apprecia
ted!
Thanks
DebIf your month-end database is simply a snapshot of your operational database
at a specific point in time, you can copy the database to different database
and file names using backup/restore.
Hope this helps.
Dan Guzman
SQL Server MVP
"Deb" <anonymous@.discussions.microsoft.com> wrote in message
news:982F6016-7AE2-499D-9634-9BE97BA4223B@.microsoft.com...
> Before I moved all the Access Databases to SQL I was able to create a
monthend database for each database we had. It wasn't too time consuming as
it was a simple copy and paste each month. Now that everything is moved
over to SQL I had told the Acctg Dept that I would prefer not to continue
with the monthend companies as it is more time consuming to administer. All
they use the monthend copies for are for reference if needed throughout the
month. When I questioned how much they actually refer back to it, it is not
very often. They were quite persistent and old habits are hard to break so
I need to find an efficient way of creating the monthend databases and then
copying them each month. Does anyone have any suggestions? Your input is
great appreciated!
> Thanks
> Deb
nd database for each database we had. It wasn't too time consuming as it wa
s a simple copy and paste each month. Now that everything is moved over to
SQL I had told the Acctg De
pt that I would prefer not to continue with the monthend companies as it is
more time consuming to administer. All they use the monthend copies for are
for reference if needed throughout the month. When I questioned how much t
hey actually refer back to
it, it is not very often. They were quite persistent and old habits are har
d to break so I need to find an efficient way of creating the monthend datab
ases and then copying them each month. Does anyone have any suggestions? Y
our input is great apprecia
ted!
Thanks
DebIf your month-end database is simply a snapshot of your operational database
at a specific point in time, you can copy the database to different database
and file names using backup/restore.
Hope this helps.
Dan Guzman
SQL Server MVP
"Deb" <anonymous@.discussions.microsoft.com> wrote in message
news:982F6016-7AE2-499D-9634-9BE97BA4223B@.microsoft.com...
> Before I moved all the Access Databases to SQL I was able to create a
monthend database for each database we had. It wasn't too time consuming as
it was a simple copy and paste each month. Now that everything is moved
over to SQL I had told the Acctg Dept that I would prefer not to continue
with the monthend companies as it is more time consuming to administer. All
they use the monthend copies for are for reference if needed throughout the
month. When I questioned how much they actually refer back to it, it is not
very often. They were quite persistent and old habits are hard to break so
I need to find an efficient way of creating the monthend databases and then
copying them each month. Does anyone have any suggestions? Your input is
great appreciated!
> Thanks
> Deb
Thursday, March 8, 2012
Creating a local SQL Server Compact Edition DB
Hi All ...
I'm setting up replication for the 1st time following the steps in the Books Online to get it up and running:
ms-help://MS.SSCE.v31.EN/ssmmain3/html/5a82aa7a-41a3-4246-a01a-2b1e4b2fdfe9.htm.
Anyhow, I get down to the section labeled - Create a new SQL Server Compact Edition database and am having a problem. I open SQL Server Management Studio as instructed. I click Connect and then am supposed to select SQL Server Compact Edition - yet can't find that option.
And, yes, I've installed Compact Edition on my development laptop. So, how do I get this option so that I can create the local DB for Compact Edition and continue development?
UPDATE:
Ok, duh moment, I have SQL Server Management Studio Express loaded on my laptop, so did not have the option. I've attempted to uninstall SQL Server Management Studio Express and install the full version from the SQL Server CDs. However, when I go to install the full version it tells me that the management tools are already installed and won't let me install anything. So, how do I get around this so that I can install full version of SQL Server Management Studio and then get the option to connect to the SQL Server Compact Edition?
Any help will be appreciated.
UPDATE 2:
Ok, figured it out. You can't just uninstall SQL Server Management Studio express, you must go in to Add/Remove Programs, SQL Server 2005, Change. Then select to uninstall the Workstation components. Then you can go use the SQL Server install discs to install the full blown SQL Server Management Studio along with the other Workstation components.
Thanks ...
David L. Collison
Any day above ground is a good day!
As a script-kiddie wannabe, I will suggest that you can use the following vbscript as well:
<job id="SqlServerCompactEditionDatabaseCreator">
<script language="VBScript">
Option Explicit
Dim strConnectionString
Dim oCatalog
strConnectionString = "Provider=Microsoft.SQLSERVER.MOBILE.OLEDB.3.0;Data Source=" & WScript.Arguments(0) & ";"
Set oCatalog = WScript.CreateObject("ADOX.Catalog")
oCatalog.Create strConnectionString
</script>
</job>
Save the script to something like SqlServerCompactEditionDatabaseCreator.wsf, and you can create a SQL Server Compact Edition database with something like cscript SqlServerCompactEditionDatabaseCreator.wsf foo.sdf from the command prompt.
Then again, that is probably just my 2 cents
-Raymond
Creating a huge tempdb size
If i create tempdb with 50GB allocated and if I read correctly that tempdb
gets rebuilt every time the SQL Server service starts up,does it mean that
the tempdb database gets dropped and recreated everytime at startup and if
so, will it cause some time to start up bcos it has to allocate 50GB. Using
SQL 2000
ThanksTempdb is only cleared at startup so there isn't much difference in startup
time with large tempdb files. The only time there is a startup time penalty
is when the files need to be recreated, such as when tempdb files are moved
with ALTER DATABASE.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23hEDXy61DHA.2000@.TK2MSFTNGP11.phx.gbl...
> If i create tempdb with 50GB allocated and if I read correctly that tempdb
> gets rebuilt every time the SQL Server service starts up,does it mean that
> the tempdb database gets dropped and recreated everytime at startup and if
> so, will it cause some time to start up bcos it has to allocate 50GB.
Using
> SQL 2000
> Thanks
>
gets rebuilt every time the SQL Server service starts up,does it mean that
the tempdb database gets dropped and recreated everytime at startup and if
so, will it cause some time to start up bcos it has to allocate 50GB. Using
SQL 2000
ThanksTempdb is only cleared at startup so there isn't much difference in startup
time with large tempdb files. The only time there is a startup time penalty
is when the files need to be recreated, such as when tempdb files are moved
with ALTER DATABASE.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23hEDXy61DHA.2000@.TK2MSFTNGP11.phx.gbl...
> If i create tempdb with 50GB allocated and if I read correctly that tempdb
> gets rebuilt every time the SQL Server service starts up,does it mean that
> the tempdb database gets dropped and recreated everytime at startup and if
> so, will it cause some time to start up bcos it has to allocate 50GB.
Using
> SQL 2000
> Thanks
>
Creating a huge tempdb size
If i create tempdb with 50GB allocated and if I read correctly that tempdb
gets rebuilt every time the SQL Server service starts up,does it mean that
the tempdb database gets dropped and recreated everytime at startup and if
so, will it cause some time to start up bcos it has to allocate 50GB. Using
SQL 2000
ThanksTempdb is only cleared at startup so there isn't much difference in startup
time with large tempdb files. The only time there is a startup time penalty
is when the files need to be recreated, such as when tempdb files are moved
with ALTER DATABASE.
Hope this helps.
Dan Guzman
SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23hEDXy61DHA.2000@.TK2MSFTNGP11.phx.gbl...
Using
gets rebuilt every time the SQL Server service starts up,does it mean that
the tempdb database gets dropped and recreated everytime at startup and if
so, will it cause some time to start up bcos it has to allocate 50GB. Using
SQL 2000
ThanksTempdb is only cleared at startup so there isn't much difference in startup
time with large tempdb files. The only time there is a startup time penalty
is when the files need to be recreated, such as when tempdb files are moved
with ALTER DATABASE.
Hope this helps.
Dan Guzman
SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23hEDXy61DHA.2000@.TK2MSFTNGP11.phx.gbl...
quote:
> If i create tempdb with 50GB allocated and if I read correctly that tempdb
> gets rebuilt every time the SQL Server service starts up,does it mean that
> the tempdb database gets dropped and recreated everytime at startup and if
> so, will it cause some time to start up bcos it has to allocate 50GB.
Using
quote:
> SQL 2000
> Thanks
>
Wednesday, March 7, 2012
Creating a formula at run time
Is it possible to create a formula at runtime. I want to add many formulas at runtime without pre-defining them.
Thanks a lotWhy do you want to do this?|||hi Madhi,
Tks 4 ur concern.
Im building a Report Tool, it should facilitate any number of columns (it should be very flexible). So I plan to use formulas, using them I can pass parameters to say what to display on the report. If I can create new formulas at run time this is possible.
Regards,
Janitha|||I dont know whether this helps you.
Add the columns in a tablebase table add design that report using those fields|||Madhi,
I'm designing a report tool. it will be used to create many user defined customizable reports. I can use known number of formulas (say 10) as report fields and pass and DB field or calculation to the report. But my problem is if I use 10 (or even 100) formulas, I have limited the number of maximum fields the report can display. To avoid that limitation I want to add formulas at run time.
Anyway I dont understand what you mean by tablebase table. Please help me out.
Thanks
rgds
Janitha|||Do u think this is possible|||Janitha,
Does your Report have 10 fields as default?
Anyway I dont understand what you mean by tablebase table.
I mean database table|||hi Madhi!
Thanks again,
Im using 10 blank formulas. I pass queries at run time to those formulas to generate any report. But I dont want any limitations, well I can think of using 100 fields. Nobody never will user 100 fields in one report, isnt it. But Im looking for a more professional solutions.|||Janitha,
I think the only way is add as many fields as possible that CR allows and pass the values to them
If you use Crystal Report Viewer, then there is an option to add formulas at runtime
CrRpt.FormulaFields.Add "FormulaName", "Value"|||I need VB.NET code for Report Designer.|||can any one help me please!|||Do any one know a better way?
:wave:|||need VB.NET code for Report Designer
Janitha,
Do you need VB.NET code to call the Report?|||No no, I want to create formulas using VB.NET code at run time, thanks for ur earlier reply. But I must use report designer, it provide much flexible way to design the report, I only couldnt find this option.
Thanks|||It seems this is impossible @.##@.|||Yea!!! It seems this is impossible @.##@.|||It seems this is impossible @.##@.|||Janitha,
Search for your solution in this web site
http://support.businessobjects.com/|||Well Madhi! I tried "businessobjects" site as well. but I could not get their tech support cos I dont have a Licence. They don't give sulutions otherwise.
The only option is to use "Crystal Repository"
Thanks 4 ur concern Madhi
Thanks a lotWhy do you want to do this?|||hi Madhi,
Tks 4 ur concern.
Im building a Report Tool, it should facilitate any number of columns (it should be very flexible). So I plan to use formulas, using them I can pass parameters to say what to display on the report. If I can create new formulas at run time this is possible.
Regards,
Janitha|||I dont know whether this helps you.
Add the columns in a tablebase table add design that report using those fields|||Madhi,
I'm designing a report tool. it will be used to create many user defined customizable reports. I can use known number of formulas (say 10) as report fields and pass and DB field or calculation to the report. But my problem is if I use 10 (or even 100) formulas, I have limited the number of maximum fields the report can display. To avoid that limitation I want to add formulas at run time.
Anyway I dont understand what you mean by tablebase table. Please help me out.
Thanks
rgds
Janitha|||Do u think this is possible|||Janitha,
Does your Report have 10 fields as default?
Anyway I dont understand what you mean by tablebase table.
I mean database table|||hi Madhi!
Thanks again,
Im using 10 blank formulas. I pass queries at run time to those formulas to generate any report. But I dont want any limitations, well I can think of using 100 fields. Nobody never will user 100 fields in one report, isnt it. But Im looking for a more professional solutions.|||Janitha,
I think the only way is add as many fields as possible that CR allows and pass the values to them
If you use Crystal Report Viewer, then there is an option to add formulas at runtime
CrRpt.FormulaFields.Add "FormulaName", "Value"|||I need VB.NET code for Report Designer.|||can any one help me please!|||Do any one know a better way?
:wave:|||need VB.NET code for Report Designer
Janitha,
Do you need VB.NET code to call the Report?|||No no, I want to create formulas using VB.NET code at run time, thanks for ur earlier reply. But I must use report designer, it provide much flexible way to design the report, I only couldnt find this option.
Thanks|||It seems this is impossible @.##@.|||Yea!!! It seems this is impossible @.##@.|||It seems this is impossible @.##@.|||Janitha,
Search for your solution in this web site
http://support.businessobjects.com/|||Well Madhi! I tried "businessobjects" site as well. but I could not get their tech support cos I dont have a Licence. They don't give sulutions otherwise.
The only option is to use "Crystal Repository"
Thanks 4 ur concern Madhi
creating a dropdown list of Saturdays
Hello Everyone,
This is my first time posting here. I am sorry if this is a beginner
question, but... I am a beginner. I am trying to write this as a stored
procedure. I need to create a dropdown list of Saturdays starting with the
first date in the database.
Here are my questions:
(1)What I am unsure about is taking the date given to me, changing it to a
Saturday, and then creating a temp table filled with a list of Saturdays up
to last Saturday.
(2)Would it be better to use the t_date(varchar) to create the temp table
with the Sat. and then convert to datetime datatype?
Table - - transactions
field1 - - t_date
field2 - - t_a_date
Any help is greatly appreciated.
Thanks in advance!!
Butch
--
Message posted via http://www.sqlmonster.comWhy don't you try something like this:
select t_date
from transactions
where datepart(dw, t_date) = 7
and t_date < getdate()
This will give you all dates that are on Saturday up through last Saturday
This will not count TODAY if today IS saturday - if you want to count today
if it is a saturday, then change the t_date < getdate() to t_date <= getdate()
--
~lb
"cearnhart via SQLMonster.com" wrote:
> Hello Everyone,
> This is my first time posting here. I am sorry if this is a beginner
> question, but... I am a beginner. I am trying to write this as a stored
> procedure. I need to create a dropdown list of Saturdays starting with the
> first date in the database.
> Here are my questions:
> (1)What I am unsure about is taking the date given to me, changing it to a
> Saturday, and then creating a temp table filled with a list of Saturdays up
> to last Saturday.
> (2)Would it be better to use the t_date(varchar) to create the temp table
> with the Sat. and then convert to datetime datatype?
> Table - - transactions
> field1 - - t_date
> field2 - - t_a_date
> Any help is greatly appreciated.
> Thanks in advance!!
> Butch
> --
> Message posted via http://www.sqlmonster.com
>|||Thank you for your quick response.
I tried this and it did not produce any results. :-(
here is my code:
select trans_id, t_a_date
from transactions
where trans_id = 6
AND datepart(dw, t_a_date) = 7
AND t_s_date < getdate()
trans_id gives me the first occurance of a date in the table. The date I get
is not a Saturday. I had to use t_a_date because of the datetime datatype.
Again, thank you for your time and help!
lonnye wrote:
>Why don't you try something like this:
>select t_date
>from transactions
>where datepart(dw, t_date) = 7
>and t_date < getdate()
>
--
Message posted via http://www.sqlmonster.com|||Is trans_id unique and/or the primary key on the table?
If you run the following, what do you get?
select datepart(dw, getdate())
Today is Thur, March 13... You should get 5 as your result.
Please let me know. (this is fun for me)
--
~lb
"cearnhart via SQLMonster.com" wrote:
> Thank you for your quick response.
> I tried this and it did not produce any results. :-(
> here is my code:
> select trans_id, t_a_date
> from transactions
> where trans_id = 6
> AND datepart(dw, t_a_date) = 7
> AND t_s_date < getdate()
> trans_id gives me the first occurance of a date in the table. The date I get
> is not a Saturday. I had to use t_a_date because of the datetime datatype.
> Again, thank you for your time and help!
>
> lonnye wrote:
> >Why don't you try something like this:
> >
> >select t_date
> >from transactions
> >where datepart(dw, t_date) = 7
> >and t_date < getdate()
> >
> --
> Message posted via http://www.sqlmonster.com
>|||trans_id is unique and is the primary key. And I do get 5 as my result
Thanks,
lonnye wrote:
>Is trans_id unique and/or the primary key on the table?
>If you run the following, what do you get?
>select datepart(dw, getdate())
>Today is Thur, March 13... You should get 5 as your result.
>Please let me know. (this is fun for me)
>> Thank you for your quick response.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200803/1|||Then you wouldnt want to include the "where trans_id = 6" since that will
only return that one row - and wont return it if that row does not fall on a
saturday.
Let me know if you still have an issue.
--
~lb
"cearnhart via SQLMonster.com" wrote:
> trans_id is unique and is the primary key. And I do get 5 as my result
> Thanks,
> lonnye wrote:
> >Is trans_id unique and/or the primary key on the table?
> >If you run the following, what do you get?
> >
> >select datepart(dw, getdate())
> >Today is Thur, March 13... You should get 5 as your result.
> >Please let me know. (this is fun for me)
> >> Thank you for your quick response.
> >>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200803/1
>|||It is still not producing results. The first date in the database is
09/17/2006(Sun), I want the dropdown to start with 09/23/2006(Sat), which
that date is not in the database.
Do I need to create a datetime variable and set that to the first date, and
then go from there? Does that make sense?
lonnye wrote:
>Then you wouldnt want to include the "where trans_id = 6" since that will
>only return that one row - and wont return it if that row does not fall on a
>saturday.
>Let me know if you still have an issue.
>> trans_id is unique and is the primary key. And I do get 5 as my result
>[quoted text clipped - 7 lines]
>> >Please let me know. (this is fun for me)
>> >> Thank you for your quick response.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200803/1|||If that date does not exist in the database and you want it shown,then you
will need to pull from a variable that increments.
Personally I would rather it not show if I already know from the query that
there is no data for that date. This way the users would not be selecting a
date only to have nothing show up (but this is just personal preference).
--
~lb
"cearnhart via SQLMonster.com" wrote:
> It is still not producing results. The first date in the database is
> 09/17/2006(Sun), I want the dropdown to start with 09/23/2006(Sat), which
> that date is not in the database.
> Do I need to create a datetime variable and set that to the first date, and
> then go from there? Does that make sense?
>
> lonnye wrote:
> >Then you wouldnt want to include the "where trans_id = 6" since that will
> >only return that one row - and wont return it if that row does not fall on a
> >saturday.
> >Let me know if you still have an issue.
> >> trans_id is unique and is the primary key. And I do get 5 as my result
> >>
> >[quoted text clipped - 7 lines]
> >> >Please let me know. (this is fun for me)
> >> >> Thank you for your quick response.
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200803/1
>|||On Mar 13, 10:26=A0pm, lonnye <lon...@.discussions.microsoft.com> wrote:
> If that date does not exist in the database and you want it shown,then you=
> will need to pull from a variable that increments.
> Personally I would rather it not show if I already know from the query tha=t
> there is no data for that date. This way the users would not be selecting =a
> date only to have nothing show up (but this is just personal preference).
> --
> ~lb
>
> "cearnhart via SQLMonster.com" wrote:
> > It is still not producing results. =A0The first date in the database is
> > 09/17/2006(Sun), I want the dropdown to start with 09/23/2006(Sat), whic=h
> > that date is not in the database.
> > Do I need to create a datetime variable and set that to the first date, =and
> > then go from there? =A0Does that make sense? =A0
> > lonnye wrote:
> > >Then you wouldnt want to include the "where trans_id =3D 6" since that =will
> > >only return that one row - and wont return it if that row does not fall= on a
> > >saturday.
> > >Let me know if you still have an issue.
> > >> trans_id is unique and is the primary key. =A0And I do get 5 as my re=sult
> > >[quoted text clipped - 7 lines]
> > >> >Please let me know. (this is fun for me)
> > >> >> Thank you for your quick response.
> > --
> > Message posted via SQLMonster.com
> >http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200803/1- =Hide quoted text -
> - Show quoted text -
Solution is creating a function ( with single date input ) which will
give the complete date table from imput date till recent date and from
that reslut set , u can eaisly run below query to extact what u
need .. We worked out here & its running fine .
select DateID, datename(dw, DateID) as Dayname from [dbo].[fnname]
('2006-01-01 00:00:00')
where datepart(dw, DateID) =3D 7 and DateID <=3D getdate()
even with this result set, you can map with your own fact table to get
the date for the missing dates in your database.
--Ayyappa
This is my first time posting here. I am sorry if this is a beginner
question, but... I am a beginner. I am trying to write this as a stored
procedure. I need to create a dropdown list of Saturdays starting with the
first date in the database.
Here are my questions:
(1)What I am unsure about is taking the date given to me, changing it to a
Saturday, and then creating a temp table filled with a list of Saturdays up
to last Saturday.
(2)Would it be better to use the t_date(varchar) to create the temp table
with the Sat. and then convert to datetime datatype?
Table - - transactions
field1 - - t_date
field2 - - t_a_date
Any help is greatly appreciated.
Thanks in advance!!
Butch
--
Message posted via http://www.sqlmonster.comWhy don't you try something like this:
select t_date
from transactions
where datepart(dw, t_date) = 7
and t_date < getdate()
This will give you all dates that are on Saturday up through last Saturday
This will not count TODAY if today IS saturday - if you want to count today
if it is a saturday, then change the t_date < getdate() to t_date <= getdate()
--
~lb
"cearnhart via SQLMonster.com" wrote:
> Hello Everyone,
> This is my first time posting here. I am sorry if this is a beginner
> question, but... I am a beginner. I am trying to write this as a stored
> procedure. I need to create a dropdown list of Saturdays starting with the
> first date in the database.
> Here are my questions:
> (1)What I am unsure about is taking the date given to me, changing it to a
> Saturday, and then creating a temp table filled with a list of Saturdays up
> to last Saturday.
> (2)Would it be better to use the t_date(varchar) to create the temp table
> with the Sat. and then convert to datetime datatype?
> Table - - transactions
> field1 - - t_date
> field2 - - t_a_date
> Any help is greatly appreciated.
> Thanks in advance!!
> Butch
> --
> Message posted via http://www.sqlmonster.com
>|||Thank you for your quick response.
I tried this and it did not produce any results. :-(
here is my code:
select trans_id, t_a_date
from transactions
where trans_id = 6
AND datepart(dw, t_a_date) = 7
AND t_s_date < getdate()
trans_id gives me the first occurance of a date in the table. The date I get
is not a Saturday. I had to use t_a_date because of the datetime datatype.
Again, thank you for your time and help!
lonnye wrote:
>Why don't you try something like this:
>select t_date
>from transactions
>where datepart(dw, t_date) = 7
>and t_date < getdate()
>
--
Message posted via http://www.sqlmonster.com|||Is trans_id unique and/or the primary key on the table?
If you run the following, what do you get?
select datepart(dw, getdate())
Today is Thur, March 13... You should get 5 as your result.
Please let me know. (this is fun for me)
--
~lb
"cearnhart via SQLMonster.com" wrote:
> Thank you for your quick response.
> I tried this and it did not produce any results. :-(
> here is my code:
> select trans_id, t_a_date
> from transactions
> where trans_id = 6
> AND datepart(dw, t_a_date) = 7
> AND t_s_date < getdate()
> trans_id gives me the first occurance of a date in the table. The date I get
> is not a Saturday. I had to use t_a_date because of the datetime datatype.
> Again, thank you for your time and help!
>
> lonnye wrote:
> >Why don't you try something like this:
> >
> >select t_date
> >from transactions
> >where datepart(dw, t_date) = 7
> >and t_date < getdate()
> >
> --
> Message posted via http://www.sqlmonster.com
>|||trans_id is unique and is the primary key. And I do get 5 as my result
Thanks,
lonnye wrote:
>Is trans_id unique and/or the primary key on the table?
>If you run the following, what do you get?
>select datepart(dw, getdate())
>Today is Thur, March 13... You should get 5 as your result.
>Please let me know. (this is fun for me)
>> Thank you for your quick response.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200803/1|||Then you wouldnt want to include the "where trans_id = 6" since that will
only return that one row - and wont return it if that row does not fall on a
saturday.
Let me know if you still have an issue.
--
~lb
"cearnhart via SQLMonster.com" wrote:
> trans_id is unique and is the primary key. And I do get 5 as my result
> Thanks,
> lonnye wrote:
> >Is trans_id unique and/or the primary key on the table?
> >If you run the following, what do you get?
> >
> >select datepart(dw, getdate())
> >Today is Thur, March 13... You should get 5 as your result.
> >Please let me know. (this is fun for me)
> >> Thank you for your quick response.
> >>
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200803/1
>|||It is still not producing results. The first date in the database is
09/17/2006(Sun), I want the dropdown to start with 09/23/2006(Sat), which
that date is not in the database.
Do I need to create a datetime variable and set that to the first date, and
then go from there? Does that make sense?
lonnye wrote:
>Then you wouldnt want to include the "where trans_id = 6" since that will
>only return that one row - and wont return it if that row does not fall on a
>saturday.
>Let me know if you still have an issue.
>> trans_id is unique and is the primary key. And I do get 5 as my result
>[quoted text clipped - 7 lines]
>> >Please let me know. (this is fun for me)
>> >> Thank you for your quick response.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200803/1|||If that date does not exist in the database and you want it shown,then you
will need to pull from a variable that increments.
Personally I would rather it not show if I already know from the query that
there is no data for that date. This way the users would not be selecting a
date only to have nothing show up (but this is just personal preference).
--
~lb
"cearnhart via SQLMonster.com" wrote:
> It is still not producing results. The first date in the database is
> 09/17/2006(Sun), I want the dropdown to start with 09/23/2006(Sat), which
> that date is not in the database.
> Do I need to create a datetime variable and set that to the first date, and
> then go from there? Does that make sense?
>
> lonnye wrote:
> >Then you wouldnt want to include the "where trans_id = 6" since that will
> >only return that one row - and wont return it if that row does not fall on a
> >saturday.
> >Let me know if you still have an issue.
> >> trans_id is unique and is the primary key. And I do get 5 as my result
> >>
> >[quoted text clipped - 7 lines]
> >> >Please let me know. (this is fun for me)
> >> >> Thank you for your quick response.
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200803/1
>|||On Mar 13, 10:26=A0pm, lonnye <lon...@.discussions.microsoft.com> wrote:
> If that date does not exist in the database and you want it shown,then you=
> will need to pull from a variable that increments.
> Personally I would rather it not show if I already know from the query tha=t
> there is no data for that date. This way the users would not be selecting =a
> date only to have nothing show up (but this is just personal preference).
> --
> ~lb
>
> "cearnhart via SQLMonster.com" wrote:
> > It is still not producing results. =A0The first date in the database is
> > 09/17/2006(Sun), I want the dropdown to start with 09/23/2006(Sat), whic=h
> > that date is not in the database.
> > Do I need to create a datetime variable and set that to the first date, =and
> > then go from there? =A0Does that make sense? =A0
> > lonnye wrote:
> > >Then you wouldnt want to include the "where trans_id =3D 6" since that =will
> > >only return that one row - and wont return it if that row does not fall= on a
> > >saturday.
> > >Let me know if you still have an issue.
> > >> trans_id is unique and is the primary key. =A0And I do get 5 as my re=sult
> > >[quoted text clipped - 7 lines]
> > >> >Please let me know. (this is fun for me)
> > >> >> Thank you for your quick response.
> > --
> > Message posted via SQLMonster.com
> >http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200803/1- =Hide quoted text -
> - Show quoted text -
Solution is creating a function ( with single date input ) which will
give the complete date table from imput date till recent date and from
that reslut set , u can eaisly run below query to extact what u
need .. We worked out here & its running fine .
select DateID, datename(dw, DateID) as Dayname from [dbo].[fnname]
('2006-01-01 00:00:00')
where datepart(dw, DateID) =3D 7 and DateID <=3D getdate()
even with this result set, you can map with your own fact table to get
the date for the missing dates in your database.
--Ayyappa
Creating a Date - or Time - Only Column in SQL Server
I understand that in SQL Svr 2000, all date/time fields store both the date
and the time. Is there a way through a constraint or trigger to force a tabl
e
to store only the date portion or time portion of the entry? For example, a
"DateHeld" field would actually contain only 2/14/2007, rather than
"2/14/2007 12:00:00 AM".
Or is there a better solution? I would rather not have to keep writing
functions to convert these combined date/time values when I want to use them
as just a date or just a time. Thanks! George> and the time. Is there a way through a constraint or trigger to force a
> table
> to store only the date portion or time portion of the entry?
USE tempdb;
GO
CREATE TABLE dbo.foo
(
dt SMALLDATETIME CHECK (DATEADD(DAY, 0, DATEDIFF(DAY, 0, dt)) = dt),
tm DATETIME CHECK (DATEADD(DAY, 0, DATEDIFF(DAY, 0, tm)) = '19000101')
);
SET NOCOUNT ON;
INSERT dbo.foo(dt, tm) SELECT '20070101', '19:34';
GO
-- fails:
INSERT dbo.foo(dt, tm) SELECT '20070101 19:34', '19:34';
GO
-- fails:
INSERT dbo.foo(dt, tm) SELECT '20070101', '19000102 19:34';
GO
-- fails:
INSERT dbo.foo(dt, tm) SELECT '19:34', '20060505';
GO
SELECT * FROM dbo.foo;
GO
DROP TABLE dbo.foo;
GO
> Or is there a better solution? I would rather not have to keep writing
> functions to convert these combined date/time values when I want to use
> them
> as just a date or just a time.
Why don't you let the presentation side of things handle the formatting and
display of the date only or time only value?|||"Aaron Bertrand [SQL Server MVP]" wrote:
> Why don't you let the presentation side of things handle the formatting an
d
> display of the date only or time only value?
>
Well, doing it at the interface end means a lot of repetitious formatting in
different places. I'd rather fix it at the source one time. I just find it
kind of amazing that SQL Server does not support current_date or
current_time, for example.
I presume the original T-SQL stuff at the top is used to build a trigger.
Thanks for the help, Aaron.
and the time. Is there a way through a constraint or trigger to force a tabl
e
to store only the date portion or time portion of the entry? For example, a
"DateHeld" field would actually contain only 2/14/2007, rather than
"2/14/2007 12:00:00 AM".
Or is there a better solution? I would rather not have to keep writing
functions to convert these combined date/time values when I want to use them
as just a date or just a time. Thanks! George> and the time. Is there a way through a constraint or trigger to force a
> table
> to store only the date portion or time portion of the entry?
USE tempdb;
GO
CREATE TABLE dbo.foo
(
dt SMALLDATETIME CHECK (DATEADD(DAY, 0, DATEDIFF(DAY, 0, dt)) = dt),
tm DATETIME CHECK (DATEADD(DAY, 0, DATEDIFF(DAY, 0, tm)) = '19000101')
);
SET NOCOUNT ON;
INSERT dbo.foo(dt, tm) SELECT '20070101', '19:34';
GO
-- fails:
INSERT dbo.foo(dt, tm) SELECT '20070101 19:34', '19:34';
GO
-- fails:
INSERT dbo.foo(dt, tm) SELECT '20070101', '19000102 19:34';
GO
-- fails:
INSERT dbo.foo(dt, tm) SELECT '19:34', '20060505';
GO
SELECT * FROM dbo.foo;
GO
DROP TABLE dbo.foo;
GO
> Or is there a better solution? I would rather not have to keep writing
> functions to convert these combined date/time values when I want to use
> them
> as just a date or just a time.
Why don't you let the presentation side of things handle the formatting and
display of the date only or time only value?|||"Aaron Bertrand [SQL Server MVP]" wrote:
> Why don't you let the presentation side of things handle the formatting an
d
> display of the date only or time only value?
>
Well, doing it at the interface end means a lot of repetitious formatting in
different places. I'd rather fix it at the source one time. I just find it
kind of amazing that SQL Server does not support current_date or
current_time, for example.
I presume the original T-SQL stuff at the top is used to build a trigger.
Thanks for the help, Aaron.
Creating a Date - or Time - Only Column in SQL Server
I understand that in SQL Svr 2000, all date/time fields store both the date
and the time. Is there a way through a constraint or trigger to force a table
to store only the date portion or time portion of the entry? For example, a
"DateHeld" field would actually contain only 2/14/2007, rather than
"2/14/2007 12:00:00 AM".
Or is there a better solution? I would rather not have to keep writing
functions to convert these combined date/time values when I want to use them
as just a date or just a time. Thanks! George> and the time. Is there a way through a constraint or trigger to force a
> table
> to store only the date portion or time portion of the entry?
USE tempdb;
GO
CREATE TABLE dbo.foo
(
dt SMALLDATETIME CHECK (DATEADD(DAY, 0, DATEDIFF(DAY, 0, dt)) = dt),
tm DATETIME CHECK (DATEADD(DAY, 0, DATEDIFF(DAY, 0, tm)) = '19000101')
);
SET NOCOUNT ON;
INSERT dbo.foo(dt, tm) SELECT '20070101', '19:34';
GO
-- fails:
INSERT dbo.foo(dt, tm) SELECT '20070101 19:34', '19:34';
GO
-- fails:
INSERT dbo.foo(dt, tm) SELECT '20070101', '19000102 19:34';
GO
-- fails:
INSERT dbo.foo(dt, tm) SELECT '19:34', '20060505';
GO
SELECT * FROM dbo.foo;
GO
DROP TABLE dbo.foo;
GO
> Or is there a better solution? I would rather not have to keep writing
> functions to convert these combined date/time values when I want to use
> them
> as just a date or just a time.
Why don't you let the presentation side of things handle the formatting and
display of the date only or time only value?|||"Aaron Bertrand [SQL Server MVP]" wrote:
> Why don't you let the presentation side of things handle the formatting and
> display of the date only or time only value?
>
Well, doing it at the interface end means a lot of repetitious formatting in
different places. I'd rather fix it at the source one time. I just find it
kind of amazing that SQL Server does not support current_date or
current_time, for example.
I presume the original T-SQL stuff at the top is used to build a trigger.
Thanks for the help, Aaron.
and the time. Is there a way through a constraint or trigger to force a table
to store only the date portion or time portion of the entry? For example, a
"DateHeld" field would actually contain only 2/14/2007, rather than
"2/14/2007 12:00:00 AM".
Or is there a better solution? I would rather not have to keep writing
functions to convert these combined date/time values when I want to use them
as just a date or just a time. Thanks! George> and the time. Is there a way through a constraint or trigger to force a
> table
> to store only the date portion or time portion of the entry?
USE tempdb;
GO
CREATE TABLE dbo.foo
(
dt SMALLDATETIME CHECK (DATEADD(DAY, 0, DATEDIFF(DAY, 0, dt)) = dt),
tm DATETIME CHECK (DATEADD(DAY, 0, DATEDIFF(DAY, 0, tm)) = '19000101')
);
SET NOCOUNT ON;
INSERT dbo.foo(dt, tm) SELECT '20070101', '19:34';
GO
-- fails:
INSERT dbo.foo(dt, tm) SELECT '20070101 19:34', '19:34';
GO
-- fails:
INSERT dbo.foo(dt, tm) SELECT '20070101', '19000102 19:34';
GO
-- fails:
INSERT dbo.foo(dt, tm) SELECT '19:34', '20060505';
GO
SELECT * FROM dbo.foo;
GO
DROP TABLE dbo.foo;
GO
> Or is there a better solution? I would rather not have to keep writing
> functions to convert these combined date/time values when I want to use
> them
> as just a date or just a time.
Why don't you let the presentation side of things handle the formatting and
display of the date only or time only value?|||"Aaron Bertrand [SQL Server MVP]" wrote:
> Why don't you let the presentation side of things handle the formatting and
> display of the date only or time only value?
>
Well, doing it at the interface end means a lot of repetitious formatting in
different places. I'd rather fix it at the source one time. I just find it
kind of amazing that SQL Server does not support current_date or
current_time, for example.
I presume the original T-SQL stuff at the top is used to build a trigger.
Thanks for the help, Aaron.
Creating a Date - or Time - Only Column in SQL Server
I understand that in SQL Svr 2000, all date/time fields store both the date
and the time. Is there a way through a constraint or trigger to force a table
to store only the date portion or time portion of the entry? For example, a
"DateHeld" field would actually contain only 2/14/2007, rather than
"2/14/2007 12:00:00 AM".
Or is there a better solution? I would rather not have to keep writing
functions to convert these combined date/time values when I want to use them
as just a date or just a time. Thanks! George
> and the time. Is there a way through a constraint or trigger to force a
> table
> to store only the date portion or time portion of the entry?
USE tempdb;
GO
CREATE TABLE dbo.foo
(
dt SMALLDATETIME CHECK (DATEADD(DAY, 0, DATEDIFF(DAY, 0, dt)) = dt),
tm DATETIME CHECK (DATEADD(DAY, 0, DATEDIFF(DAY, 0, tm)) = '19000101')
);
SET NOCOUNT ON;
INSERT dbo.foo(dt, tm) SELECT '20070101', '19:34';
GO
-- fails:
INSERT dbo.foo(dt, tm) SELECT '20070101 19:34', '19:34';
GO
-- fails:
INSERT dbo.foo(dt, tm) SELECT '20070101', '19000102 19:34';
GO
-- fails:
INSERT dbo.foo(dt, tm) SELECT '19:34', '20060505';
GO
SELECT * FROM dbo.foo;
GO
DROP TABLE dbo.foo;
GO
> Or is there a better solution? I would rather not have to keep writing
> functions to convert these combined date/time values when I want to use
> them
> as just a date or just a time.
Why don't you let the presentation side of things handle the formatting and
display of the date only or time only value?
|||"Aaron Bertrand [SQL Server MVP]" wrote:
> Why don't you let the presentation side of things handle the formatting and
> display of the date only or time only value?
>
Well, doing it at the interface end means a lot of repetitious formatting in
different places. I'd rather fix it at the source one time. I just find it
kind of amazing that SQL Server does not support current_date or
current_time, for example.
I presume the original T-SQL stuff at the top is used to build a trigger.
Thanks for the help, Aaron.
and the time. Is there a way through a constraint or trigger to force a table
to store only the date portion or time portion of the entry? For example, a
"DateHeld" field would actually contain only 2/14/2007, rather than
"2/14/2007 12:00:00 AM".
Or is there a better solution? I would rather not have to keep writing
functions to convert these combined date/time values when I want to use them
as just a date or just a time. Thanks! George
> and the time. Is there a way through a constraint or trigger to force a
> table
> to store only the date portion or time portion of the entry?
USE tempdb;
GO
CREATE TABLE dbo.foo
(
dt SMALLDATETIME CHECK (DATEADD(DAY, 0, DATEDIFF(DAY, 0, dt)) = dt),
tm DATETIME CHECK (DATEADD(DAY, 0, DATEDIFF(DAY, 0, tm)) = '19000101')
);
SET NOCOUNT ON;
INSERT dbo.foo(dt, tm) SELECT '20070101', '19:34';
GO
-- fails:
INSERT dbo.foo(dt, tm) SELECT '20070101 19:34', '19:34';
GO
-- fails:
INSERT dbo.foo(dt, tm) SELECT '20070101', '19000102 19:34';
GO
-- fails:
INSERT dbo.foo(dt, tm) SELECT '19:34', '20060505';
GO
SELECT * FROM dbo.foo;
GO
DROP TABLE dbo.foo;
GO
> Or is there a better solution? I would rather not have to keep writing
> functions to convert these combined date/time values when I want to use
> them
> as just a date or just a time.
Why don't you let the presentation side of things handle the formatting and
display of the date only or time only value?
|||"Aaron Bertrand [SQL Server MVP]" wrote:
> Why don't you let the presentation side of things handle the formatting and
> display of the date only or time only value?
>
Well, doing it at the interface end means a lot of repetitious formatting in
different places. I'd rather fix it at the source one time. I just find it
kind of amazing that SQL Server does not support current_date or
current_time, for example.
I presume the original T-SQL stuff at the top is used to build a trigger.
Thanks for the help, Aaron.
Saturday, February 25, 2012
Creating a custom Delivery Extension (File Share)
I would like to create a custom delivery extension wherby I take the filename of my report and then append a date time stamp to it. While I have some knowledge of RS I have little practical programming expierience.
I have little fear of learning something new, but I like to take known good working model, understand why / how it works and then apply that to my situation.
Are there any "Dummies" type of tutorials out there to get me started down this road?
Thanks for reading
hi , you can use this code .
public bool Deliver(Notification notification)
{
string reportName = notification.Report.Name;
}
Creating a custom Delivery Extension (File Share)
I would like to create a custom delivery extension wherby I take the filename of my report and then append a date time stamp to it. While I have some knowledge of RS I have little practical programming expierience.
I have little fear of learning something new, but I like to take known good working model, understand why / how it works and then apply that to my situation.
Are there any "Dummies" type of tutorials out there to get me started down this road?
Thanks for reading
hi , you can use this code .
public bool Deliver(Notification notification)
{
string reportName = notification.Report.Name;
}
Friday, February 24, 2012
Creating a cached instance of a report for all variable values
I have a report that takes quite some time to render.
So I would like to create cached-instances of the report for each possible
variable value.
I suppose this is a rather common problem. Is there a solution (script,
program) available somwhere to do this?
(I've tried some things with the scripts but I can't get it to work, I keep
geeting "timed out' errors (although de report execution is set not to time
out) or security exeptions (althoug my user is a RS system user with all the
authoroty))
Thank youDid you ever figure out how to do this?
We thought that creating a data-driven subscription that dumped the report
to a file share when the report was setup to be cached would cache all
possible versions of the report, but we're finding that it's not caching
those executions and the report is being rendered on the first request.
Any thoughts?
Thx, Joel
"Antoon" <Antoon@.discussions.microsoft.com> wrote in message
news:CFFBE8A3-1C94-4FC7-8280-3F68E4ECD19F@.microsoft.com...
>I have a report that takes quite some time to render.
> So I would like to create cached-instances of the report for each possible
> variable value.
> I suppose this is a rather common problem. Is there a solution (script,
> program) available somwhere to do this?
> (I've tried some things with the scripts but I can't get it to work, I
> keep
> geeting "timed out' errors (although de report execution is set not to
> time
> out) or security exeptions (althoug my user is a RS system user with all
> the
> authoroty))
> Thank you|||I did, but it's a workaround. I've written a small programme in VB.net
that will take the name of the report and the parameters and that will
render the report in a web-window for each possible combination of the
parameters.
This does the trick, but it's not what you would call "elegant", I hope MS
will solve this in the next version.
"Joel Rumerman" wrote:
> Did you ever figure out how to do this?
> We thought that creating a data-driven subscription that dumped the report
> to a file share when the report was setup to be cached would cache all
> possible versions of the report, but we're finding that it's not caching
> those executions and the report is being rendered on the first request.
> Any thoughts?
> Thx, Joel
> "Antoon" <Antoon@.discussions.microsoft.com> wrote in message
> news:CFFBE8A3-1C94-4FC7-8280-3F68E4ECD19F@.microsoft.com...
> >I have a report that takes quite some time to render.
> > So I would like to create cached-instances of the report for each possible
> > variable value.
> > I suppose this is a rather common problem. Is there a solution (script,
> > program) available somwhere to do this?
> >
> > (I've tried some things with the scripts but I can't get it to work, I
> > keep
> > geeting "timed out' errors (although de report execution is set not to
> > time
> > out) or security exeptions (althoug my user is a RS system user with all
> > the
> > authoroty))
> >
> > Thank you
>
>
So I would like to create cached-instances of the report for each possible
variable value.
I suppose this is a rather common problem. Is there a solution (script,
program) available somwhere to do this?
(I've tried some things with the scripts but I can't get it to work, I keep
geeting "timed out' errors (although de report execution is set not to time
out) or security exeptions (althoug my user is a RS system user with all the
authoroty))
Thank youDid you ever figure out how to do this?
We thought that creating a data-driven subscription that dumped the report
to a file share when the report was setup to be cached would cache all
possible versions of the report, but we're finding that it's not caching
those executions and the report is being rendered on the first request.
Any thoughts?
Thx, Joel
"Antoon" <Antoon@.discussions.microsoft.com> wrote in message
news:CFFBE8A3-1C94-4FC7-8280-3F68E4ECD19F@.microsoft.com...
>I have a report that takes quite some time to render.
> So I would like to create cached-instances of the report for each possible
> variable value.
> I suppose this is a rather common problem. Is there a solution (script,
> program) available somwhere to do this?
> (I've tried some things with the scripts but I can't get it to work, I
> keep
> geeting "timed out' errors (although de report execution is set not to
> time
> out) or security exeptions (althoug my user is a RS system user with all
> the
> authoroty))
> Thank you|||I did, but it's a workaround. I've written a small programme in VB.net
that will take the name of the report and the parameters and that will
render the report in a web-window for each possible combination of the
parameters.
This does the trick, but it's not what you would call "elegant", I hope MS
will solve this in the next version.
"Joel Rumerman" wrote:
> Did you ever figure out how to do this?
> We thought that creating a data-driven subscription that dumped the report
> to a file share when the report was setup to be cached would cache all
> possible versions of the report, but we're finding that it's not caching
> those executions and the report is being rendered on the first request.
> Any thoughts?
> Thx, Joel
> "Antoon" <Antoon@.discussions.microsoft.com> wrote in message
> news:CFFBE8A3-1C94-4FC7-8280-3F68E4ECD19F@.microsoft.com...
> >I have a report that takes quite some time to render.
> > So I would like to create cached-instances of the report for each possible
> > variable value.
> > I suppose this is a rather common problem. Is there a solution (script,
> > program) available somwhere to do this?
> >
> > (I've tried some things with the scripts but I can't get it to work, I
> > keep
> > geeting "timed out' errors (although de report execution is set not to
> > time
> > out) or security exeptions (althoug my user is a RS system user with all
> > the
> > authoroty))
> >
> > Thank you
>
>
Subscribe to:
Posts (Atom)