print list of queries, tables, views and sp

I just started a new job and 1st time on sql server, how can i print list of queries, tables, views, stored procedures and functions?What do you mean by print?
What version of SQL Server are you running? This will have an effect on the query you need to run. The below example was written for 2000

SELECT name
, id
, type
FROM sysobjects
WHERE type IN ('V', 'U', 'SP', 'FN')
-- v = view, u = table, sp = sproc, fn = user-defined function|||2005 Users Note:
BOL Says
Important: This Microsoft SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use catalog views instead.

Any general comments as to whether we should still be coding with sysobjects ?

:angel:

GW|||We are kind of caught on the edge of the sword on this issue. Because users rarely give us enough information to know what version of SQL they are using, we tend to give them the answers that work under the largest possible set of conditions.

You are correct, using the catalog views is preferable if you are running a version of SQL Server that supports the catalog views. On a "going forward" basis, you probably ought to only use the catalog views, but on a "forum answer" I tend to stick with what will work for the largest number of people.

-PatP|||Anybody have the catalog solution to hand?

I don't get to play on much 2K5, but I am going to be taking my MCTS in it in a couple of months, so I should really get brushed up on it :p|||Play around with the view sys.objects. You should have it in no time. I think id changed to object_id, but most of the rest is the same.|||SELECT ROUTINE_TYPE, ROUTINE_SCHEMA, ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES

SELECT TABLE_TYPE, TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES-PatP

Print Layout feature showing black

Today I received a Windows Update that included SQL 2005 SP2. Since
that time whenever I try to use the "Print Layout" feature in
Reporting Services, it shows nothing but black. Does anyone know of a
fix for this?Here you go: http://support.microsoft.com/kb/935356/en-us
This is the new cumulative update that should fix your issue. You'll need
to follow the steps on the page to request the fix.
This is one of the bugs fixed:
50001105 After you apply SQL Server 2005 SP2, the Print Preview feature in
Reporting Services may show a solid black page.
--
Chris Alton, Microsoft Corp.
SQL Server Developer Support Engineer
This posting is provided "AS IS" with no warranties, and confers no rights.
--
> From: cphite@.gmail.com
> Newsgroups: microsoft.public.sqlserver.reportingsvcs
> Subject: Print Layout feature showing black
> Date: Thu, 04 Oct 2007 13:17:24 -0700
> Today I received a Windows Update that included SQL 2005 SP2. Since
> that time whenever I try to use the "Print Layout" feature in
> Reporting Services, it shows nothing but black. Does anyone know of a
> fix for this?
>|||On Oct 4, 4:52 pm, cal...@.online.microsoft.com (Chris Alton [MSFT])
wrote:
> Here you go:http://support.microsoft.com/kb/935356/en-us
> This is the new cumulative update that should fix your issue. You'll need
> to follow the steps on the page to request the fix.
Thanks, Chris - that worked perfectly.|||Great. You wern't the only person I've seen that had that problem.
--
Chris Alton, Microsoft Corp.
SQL Server Developer Support Engineer
This posting is provided "AS IS" with no warranties, and confers no rights.
--
> From: cphite@.gmail.com
> Newsgroups: microsoft.public.sqlserver.reportingsvcs
> Subject: Re: Print Layout feature showing black
> Date: Fri, 05 Oct 2007 07:56:08 -0700
> Organization: http://groups.google.com
> On Oct 4, 4:52 pm, cal...@.online.microsoft.com (Chris Alton [MSFT])
> wrote:
> > Here you go:http://support.microsoft.com/kb/935356/en-us
> >
> > This is the new cumulative update that should fix your issue. You'll
need
> > to follow the steps on the page to request the fix.
> Thanks, Chris - that worked perfectly.
>

Print Error messge

Hi,

When iam trying to print the report from the application i.e winforms application, at the first time it is displaying the Security waring(pop up message)

Do you want to install the software?

Name:Microssoft Sqlserver

Publisher:Microsoft

with Install and don't install buttons.

Once we install , from the next time printing it is not displaying this error message.

Whether it is of IE version Problem.

Thanks in advance.

What you are seeing is not an error message, but a security warning asking whether you really want to install the print control software. The printing functionality is provided by an ActiveX control, and is not built into the browser. Once it is installed on a machine, it is not necessary to install the control again (unless there is an update to the control), which is why you do not see the message again.

Ian

Print Date and Time

I am currently using the global execution time in my reports, but I would
also like to show the user what time they view the report. I guess in
Crystal they distinguish between the two as "Data Date" which would be the
execution time and then "Print Date" which is the date and time the report
is viewed. Does any one know if there a way to show this is RS?
Thanks,
LisaPlease review .Net's DateTime functionality (Search on DateTime in MSDN).
In your case your could place, in a textbox, an expression like
=DateTime.Now.
You could also use the Now() function.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Lisa" <Lisa.Lambert@._nospam_etalk.com> wrote in message
news:OnOBSRDZEHA.3716@.TK2MSFTNGP11.phx.gbl...
> I am currently using the global execution time in my reports, but I would
> also like to show the user what time they view the report. I guess in
> Crystal they distinguish between the two as "Data Date" which would be the
> execution time and then "Print Date" which is the date and time the report
> is viewed. Does any one know if there a way to show this is RS?
> Thanks,
> Lisa
>|||Thank you!
"Bruce Johnson [MSFT]" <brucejoh@.online.microsoft.com> wrote in message
news:O%23oBowEZEHA.712@.TK2MSFTNGP11.phx.gbl...
> Please review .Net's DateTime functionality (Search on DateTime in MSDN).
> In your case your could place, in a textbox, an expression like
> =DateTime.Now.
> You could also use the Now() function.
> --
> Bruce Johnson [MSFT]
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "Lisa" <Lisa.Lambert@._nospam_etalk.com> wrote in message
> news:OnOBSRDZEHA.3716@.TK2MSFTNGP11.phx.gbl...
> > I am currently using the global execution time in my reports, but I
would
> > also like to show the user what time they view the report. I guess in
> > Crystal they distinguish between the two as "Data Date" which would be
the
> > execution time and then "Print Date" which is the date and time the
report
> > is viewed. Does any one know if there a way to show this is RS?
> >
> > Thanks,
> >
> > Lisa
> >
> >
>|||You can try =now().
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Lisa" <Lisa.Lambert@._nospam_etalk.com> wrote in message
news:OnOBSRDZEHA.3716@.TK2MSFTNGP11.phx.gbl...
> I am currently using the global execution time in my reports, but I would
> also like to show the user what time they view the report. I guess in
> Crystal they distinguish between the two as "Data Date" which would be the
> execution time and then "Print Date" which is the date and time the report
> is viewed. Does any one know if there a way to show this is RS?
> Thanks,
> Lisa
>

Print button doesn't work in ReportServer 2005

I can preview reports on the server fine. The first time I clicked the
print button, I got a message asking me to install SQL Server? I said
yes, then after a few minutes I got a print error.
Now, every time I click the print button on the RS toolbar, I get this
message:
Print Error
An error occured trying to get the current window.
Besides the fact that occurred is spelled wrong, why is this error
happening? I tried deleting all RSClient* files from \Windows\Downloaded
Program Files, but that doesn't help.
How do I fix this?
TIA,
Mike RodriguezCan you try deleting the ActiveX control via IE.
Go to Tools->Internet Options
Click the Settings button
Click the View Objects button.
Delete the RSClientPrint control
I would also clear out my internet cache to make sure you are getting the
latest cab file.
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Michael Rodriguez" <mike@.nospam.com> wrote in message
news:eb%23LX2B$FHA.3636@.TK2MSFTNGP10.phx.gbl...
>I can preview reports on the server fine. The first time I clicked the
>print button, I got a message asking me to install SQL Server? I said
>yes, then after a few minutes I got a print error.
> Now, every time I click the print button on the RS toolbar, I get this
> message:
> Print Error
> An error occured trying to get the current window.
> Besides the fact that occurred is spelled wrong, why is this error
> happening? I tried deleting all RSClient* files from \Windows\Downloaded
> Program Files, but that doesn't help.
> How do I fix this?
> TIA,
> Mike Rodriguez
>|||Hi Daniel,
Ok, I did that. Unfortunately, I still get the same error.
Any other ideas?
Mike
"Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
news:eO9RFNC$FHA.216@.TK2MSFTNGP15.phx.gbl...
> Can you try deleting the ActiveX control via IE.
> Go to Tools->Internet Options
> Click the Settings button
> Click the View Objects button.
> Delete the RSClientPrint control
> I would also clear out my internet cache to make sure you are getting the
> latest cab file.
>
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> "Michael Rodriguez" <mike@.nospam.com> wrote in message
> news:eb%23LX2B$FHA.3636@.TK2MSFTNGP10.phx.gbl...
>>I can preview reports on the server fine. The first time I clicked the
>>print button, I got a message asking me to install SQL Server? I said
>>yes, then after a few minutes I got a print error.
>> Now, every time I click the print button on the RS toolbar, I get this
>> message:
>> Print Error
>> An error occured trying to get the current window.
>> Besides the fact that occurred is spelled wrong, why is this error
>> happening? I tried deleting all RSClient* files from \Windows\Downloaded
>> Program Files, but that doesn't help.
>> How do I fix this?
>> TIA,
>> Mike Rodriguez
>>
>|||Are you viewing reports through a hosted IE control? That is is this a
winforms app or are your going to the ReportServer through IE?
If it is in a hosted win forms then this is expected and the print
functionality is not supported there.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Michael Rodriguez" <mike@.nospam.com> wrote in message
news:etvrvsC$FHA.2036@.TK2MSFTNGP14.phx.gbl...
> Hi Daniel,
> Ok, I did that. Unfortunately, I still get the same error.
> Any other ideas?
> Mike
>
> "Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
> news:eO9RFNC$FHA.216@.TK2MSFTNGP15.phx.gbl...
>> Can you try deleting the ActiveX control via IE.
>> Go to Tools->Internet Options
>> Click the Settings button
>> Click the View Objects button.
>> Delete the RSClientPrint control
>> I would also clear out my internet cache to make sure you are getting the
>> latest cab file.
>>
>> --
>> -Daniel
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Michael Rodriguez" <mike@.nospam.com> wrote in message
>> news:eb%23LX2B$FHA.3636@.TK2MSFTNGP10.phx.gbl...
>>I can preview reports on the server fine. The first time I clicked the
>>print button, I got a message asking me to install SQL Server? I said
>>yes, then after a few minutes I got a print error.
>> Now, every time I click the print button on the RS toolbar, I get this
>> message:
>> Print Error
>> An error occured trying to get the current window.
>> Besides the fact that occurred is spelled wrong, why is this error
>> happening? I tried deleting all RSClient* files from
>> \Windows\Downloaded Program Files, but that doesn't help.
>> How do I fix this?
>> TIA,
>> Mike Rodriguez
>>
>>
>|||Hi Daniel,
Actually, the print button works in my winforms app. It doesn't work in the
IE web application that ReportServer created for me.
What can I do?
Mike
"Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
news:OwcNYeE$FHA.2320@.TK2MSFTNGP11.phx.gbl...
> Are you viewing reports through a hosted IE control? That is is this a
> winforms app or are your going to the ReportServer through IE?
> If it is in a hosted win forms then this is expected and the print
> functionality is not supported there.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> "Michael Rodriguez" <mike@.nospam.com> wrote in message
> news:etvrvsC$FHA.2036@.TK2MSFTNGP14.phx.gbl...
>> Hi Daniel,
>> Ok, I did that. Unfortunately, I still get the same error.
>> Any other ideas?
>> Mike
>>
>> "Daniel Reib [MSFT]" <danreib@.online.microsoft.com> wrote in message
>> news:eO9RFNC$FHA.216@.TK2MSFTNGP15.phx.gbl...
>> Can you try deleting the ActiveX control via IE.
>> Go to Tools->Internet Options
>> Click the Settings button
>> Click the View Objects button.
>> Delete the RSClientPrint control
>> I would also clear out my internet cache to make sure you are getting
>> the latest cab file.
>>
>> --
>> -Daniel
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Michael Rodriguez" <mike@.nospam.com> wrote in message
>> news:eb%23LX2B$FHA.3636@.TK2MSFTNGP10.phx.gbl...
>>I can preview reports on the server fine. The first time I clicked the
>>print button, I got a message asking me to install SQL Server? I said
>>yes, then after a few minutes I got a print error.
>> Now, every time I click the print button on the RS toolbar, I get this
>> message:
>> Print Error
>> An error occured trying to get the current window.
>> Besides the fact that occurred is spelled wrong, why is this error
>> happening? I tried deleting all RSClient* files from
>> \Windows\Downloaded Program Files, but that doesn't help.
>> How do I fix this?
>> TIA,
>> Mike Rodriguez
>>
>>
>>
>

PRIMARY key versus UNIQUE index

Hi Group
Other than the fact that PRIMARY keys seem to be defined at table
creation time - what's the difference between a primary key and a
UNIQUE index? If there is a difference, what's the typical use of a
PRIMARY key?
TIA
MarkusPrimary keys do not allow null values ... that is the only difference betwee
n
Primary key and Unique Key.
Best Regards
Vadivel
http://vadivel.blogspot.com
"Markus Zingg" wrote:

> Hi Group
> Other than the fact that PRIMARY keys seem to be defined at table
> creation time - what's the difference between a primary key and a
> UNIQUE index? If there is a difference, what's the typical use of a
> PRIMARY key?
> TIA
> Markus
>|||and unique key allows just one null value.
"Vadivel" wrote:
> Primary keys do not allow null values ... that is the only difference betw
een
> Primary key and Unique Key.
> Best Regards
> Vadivel
> http://vadivel.blogspot.com
> "Markus Zingg" wrote:
>|||Hi
Not, the other difference is that when you create a primary key, SQL Server
creates a clustered index ,as opposite UNIQUE key it creates unique
nonclustered index
"Vadivel" <Vadivel@.discussions.microsoft.com> wrote in message
news:599BB230-67B1-4EB2-A1A2-BDA047C212FE@.microsoft.com...
> Primary keys do not allow null values ... that is the only difference
> between
> Primary key and Unique Key.
> Best Regards
> Vadivel
> http://vadivel.blogspot.com
> "Markus Zingg" wrote:
>|||From the database design point of view the PRIMARY KEY should reflect the
natural key of your data.
From the technical point of view, a PRIMARY KEY does not allow NULL's, where
as a UNIQUE Constraint / Index allows at least 1 NULL.
From my own stand point I use PRIMARY KEY constraints on the natural key and
a UNIQUE Constraint for the surrogate key eg...
CREATE TABLE currency (
id int not null identity constraint sk_currency unique clustered,
code char(3) not null constraint pk_currency primary key nonclustered
)
CREATE TABLE trade (
..
...
settlement_currency_id int not null references currency( id ),
trade_currency_id int not null references currency( id ),
)
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Markus Zingg" <m.zingg@.nct.ch> wrote in message
news:rgcu42dr098qbiebq725rgvsqjaij775bq@.
4ax.com...
> Hi Group
> Other than the fact that PRIMARY keys seem to be defined at table
> creation time - what's the difference between a primary key and a
> UNIQUE index? If there is a difference, what's the typical use of a
> PRIMARY key?
> TIA
> Markus
>|||Unless it's composite.
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:01F43F78-1A63-4AD9-B5C0-D4F2486524C1@.microsoft.com...
> and unique key allows just one null value.
> "Vadivel" wrote:
>|||Just for the sake of completion. We can create Primary Keyws without
clustered index also. Check out
http://vadivel.blogspot.com/2006/03...el.blogspot.com
"Uri Dimant" wrote:

> Hi
> Not, the other difference is that when you create a primary key, SQL Serve
r
> creates a clustered index ,as opposite UNIQUE key it creates unique
> nonclustered index
>
> "Vadivel" <Vadivel@.discussions.microsoft.com> wrote in message
> news:599BB230-67B1-4EB2-A1A2-BDA047C212FE@.microsoft.com...
>
>|||This is a default behavior for the creation of a primary key; you can
override it, and make your underlying index a nonclustered one.
CREATE TABLE foo (pkid int PRIMARY KEY NONCLUSTERED,
doe smalldatetime)
CREATE CLUSTERED INDEX clidx ON foo(doe)
I try to keep the concepts of indexes and constraints as seperate as
possible in my mind (and in my designs); one is a physical tool to
assist in database performance, the other is a logical tool to assist
in data integrity. Sometimes it's useful to have a clustered primary
key; sometimes it's not.
Stu|||Hi Tony!

> From my own stand point I use PRIMARY KEY constraints on the natural key a
nd a UNIQUE Constraint
> for the surrogate key eg...
Interesting... I have a feeling that many people does it the other way aroun
d (identity is PK, and
natural key(s) are UQ). Is it just by habit, or do you have any particular r
eason doing it that way?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:%23hMXihRaGHA.5108@.TK2MSFTNGP05.phx.gbl...
> From the database design point of view the PRIMARY KEY should reflect the
natural key of your
> data.
> From the technical point of view, a PRIMARY KEY does not allow NULL's, whe
re as a UNIQUE
> Constraint / Index allows at least 1 NULL.
> From my own stand point I use PRIMARY KEY constraints on the natural key a
nd a UNIQUE Constraint
> for the surrogate key eg...
> CREATE TABLE currency (
> id int not null identity constraint sk_currency unique clustered,
> code char(3) not null constraint pk_currency primary key nonclustered
> )
> CREATE TABLE trade (
> ...
> ...
> settlement_currency_id int not null references currency( id ),
> trade_currency_id int not null references currency( id ),
> )
> Tony.
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlserverfaq.com - free video tutorials
>
> "Markus Zingg" <m.zingg@.nct.ch> wrote in message
> news:rgcu42dr098qbiebq725rgvsqjaij775bq@.
4ax.com...
>|||Hi Tibor - long time no beer....
My feeling and approach is this, most tables will have a natural key that we
need to provide uniqueness for, I make the natural key the PRIMARY KEY (it
also flows naturally from the logical design), the surrogate key is there as
part of the implementation phase to improve schema performance and
scalability hence I make it a unique constraint to enforce uniqueness, and
yes - use IDENTITY to auto populate.
There are a number of occaisions where there is no practical usable natural
key and in that instance I will just make the column with the IDENTITY
property the PRIMARY KEY and not bother with the surrogate.
I never use the natural key anywhere else - there is just one copy of it and
thats in its base table, and foreign key references I use the surrogate key.
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OCpTmJSaGHA.4544@.TK2MSFTNGP02.phx.gbl...
> Hi Tony!
>
> Interesting... I have a feeling that many people does it the other way
> around (identity is PK, and natural key(s) are UQ). Is it just by habit,
> or do you have any particular reason doing it that way?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
> news:%23hMXihRaGHA.5108@.TK2MSFTNGP05.phx.gbl...
>

Primary Key problems

Hi there,

I just tried to apply a set of primary keys to a table and after a
very long time (like an hour or more) i got this error message ...

Unable to create index 'PK_master_data'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]CREATE
UNIQUE INDEX terminated because a duplicate key was found for index ID
1. Most significant primary key is '2050500'.
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not create
constraint. See previous errors.
[Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
terminated.

.... i am new to sqlserver ... is this saying to me that i have a
duplicate record? because when i query the recordset for duplicate
records i get none!

please help ASAP.

Thanks

Mogitcumming@.smorgonsteel.com.au (mogi) wrote in message news:<d1729549.0401272220.49ea5ee6@.posting.google.com>...
> Hi there,
> I just tried to apply a set of primary keys to a table and after a
> very long time (like an hour or more) i got this error message ...
> Unable to create index 'PK_master_data'.
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]CREATE
> UNIQUE INDEX terminated because a duplicate key was found for index ID
> 1. Most significant primary key is '2050500'.
> [Microsoft][ODBC SQL Server Driver][SQL Server]Could not create
> constraint. See previous errors.
> [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been
> terminated.
> ... i am new to sqlserver ... is this saying to me that i have a
> duplicate record? because when i query the recordset for duplicate
> records i get none!
> please help ASAP.
> Thanks
> Mogi

Yes, there are duplicates. How are you querying the recordset for
them? A query like this should find them:

select PrimaryKeyColumn, count(*)
from dbo.MyTable
group by PrimaryKeyColumn
having count(*) > 1

Simon

primary key in aggregated view

Been pulling my hair out with this one for some time now ... hope someone out there can help :)

I have a database view which is an aggregated view of a number of tables. Trouble is I need to create what would effectively be a primary key for the view.

Can anyone suggest a sound way of doing this other than moving to Oracle :)If you already have an alternative, - may god help you (it won't be my god though ;))

Why do you need a PK on a view? Using it instead of a table?|||A view (should) "inherit" all the contraints that are expressed in the uderlying query. Unfortuneately, because SQL allows duplicates, the result set of the view cannot be guareented to contain a key.

As a possible work around, look at Indexed views if you have SQL2K.. you may be able to add a unique index to the view if it conforms...|||Yeah, why do you need a primary key on your view?

Truth is, I hardly ever use views any more. I find that either table variables, temporary tables, or table functions are more effective.|||The view aggregates a number of child tables which contain amounts by currency etc

I also have a second view which is effectively a breakdown of the contents of the aggregation in the first view.

Because the second view is a child of the first, I needed a consistent primary key to link them together.

I solved it by creating a function that took the MIN primary key of the instances that would be in the breakdown view and passed this back to the first view.

It works a treat, just not sure how it will perform yet :)

Thanks for your help|||THAT's what I was waiting for!

Primary Key Data type in Time Dimension??

I have to create a Time dimension with day grain in a Datawarehouse system
and I don’t know what is the best data type for the primary key...
For example
1) I could put Number(8) datatype, then the dates will be: 20050114,
20050115, 20050116... Then in the fact tables I put the Number(8) datatype
in the date fields... But in my reporting tools I have to put the conversion
function to show the dates in the right format.
2) Or I could put Date datatype, then the dates will be: 01/14/2005,
01/15/2005, 01/16/2005... Then in the fact tables I put the Date datatype in
the date fields...
It’s the Date primary key a bad datatype? (Very slow)
What is the best Primary Key Data type in Time Dimension?
Thanks!
In my opinion the key should be as small as you can get it.
Depending on how many days you want to store you could have
Smallint (4 bytes) == over 170 years
Int (8 bytes) == a very long time
"Marcelo" <Marcelo@.discussions.microsoft.com> wrote in message
news:Marcelo@.discussions.microsoft.com:
> I have to create a Time dimension with day grain in a Datawarehouse system
> and I don't know what is the best data type for the primary key...
> For example
> 1) I could put Number(8) datatype, then the dates will be: 20050114,
> 20050115, 20050116... Then in the fact tables I put the Number(8)
> datatype
> in the date fields... But in my reporting tools I have to put the
> conversion
> function to show the dates in the right format.
> 2) Or I could put Date datatype, then the dates will be: 01/14/2005,
> 01/15/2005, 01/16/2005... Then in the fact tables I put the Date datatype
> in
> the date fields...
>
> It's the Date primary key a bad datatype? (Very slow)
> What is the best Primary Key Data type in Time Dimension?
> Thanks!
|||I have my date dimension using an INT primary key like 20050202.
This has been running for about 6 months now, and it is really
feeling like a good decision.
As for your reporting tool issue, you can just put additional
columns on your time dimension like
calendar_date DATETIME,
month_name VARCHAR(10),
day_name VARCHAR(10),
day_of_week VARCHAR(10),
...etc...
So you can have nice pre-formatted things to use in your
reporting tools.
It also makes for nice roll-up groupings if you include things
like
fiscal_period,
week_of_year,
quarter_of_year,
...etc...
Good luck,
Steve
"=?Utf-8?B?TWFyY2Vsbw==?=" <Marcelo@.discussions.microsoft.com>
wrote in
news:62A63A74-D1CE-4519-AA48-737FE514EB28@.microsoft.com:

> I have to create a Time dimension with day grain in a
> Datawarehouse system and I don’t know what is the best data
> type for the primary key...
> For example
> 1) I could put Number(8) datatype, then the dates will be:
> 20050114, 20050115, 20050116... Then in the fact tables I put
> the Number(8) datatype in the date fields... But in my
> reporting tools I have to put the conversion function to show
> the dates in the right format. 2) Or I could put Date
> datatype, then the dates will be: 01/14/2005, 01/15/2005,
> 01/16/2005... Then in the fact tables I put the Date datatype
> in the date fields...
>
> It’s the Date primary key a bad datatype? (Very slow)
> What is the best Primary Key Data type in Time Dimension?
> Thanks!
>

Primary Key Data type in Time Dimension??

I have to create a Time dimension with day grain in a Datawarehouse system
and I don’t know what is the best data type for the primary key...
For example
1) I could put Number(8) datatype, then the dates will be: 20050114,
20050115, 20050116... Then in the fact tables I put the Number(8) datatype
in the date fields... But in my reporting tools I have to put the conversion
function to show the dates in the right format.
2) Or I could put Date datatype, then the dates will be: 01/14/2005,
01/15/2005, 01/16/2005... Then in the fact tables I put the Date datatype i
n
the date fields...
It’s the Date primary key a bad datatype? (Very slow)
What is the best Primary Key Data type in Time Dimension?
Thanks!In my opinion the key should be as small as you can get it.
Depending on how many days you want to store you could have
Smallint (4 bytes) == over 170 years
Int (8 bytes) == a very long time
"Marcelo" <Marcelo@.discussions.microsoft.com> wrote in message
news:Marcelo@.discussions.microsoft.com:
> I have to create a Time dimension with day grain in a Datawarehouse system
> and I don't know what is the best data type for the primary key...
> For example
> 1) I could put Number(8) datatype, then the dates will be: 20050114,
> 20050115, 20050116... Then in the fact tables I put the Number(8)
> datatype
> in the date fields... But in my reporting tools I have to put the
> conversion
> function to show the dates in the right format.
> 2) Or I could put Date datatype, then the dates will be: 01/14/2005,
> 01/15/2005, 01/16/2005... Then in the fact tables I put the Date datatype
> in
> the date fields...
>
> It's the Date primary key a bad datatype? (Very slow)
> What is the best Primary Key Data type in Time Dimension?
> Thanks!|||I have my date dimension using an INT primary key like 20050202.
This has been running for about 6 months now, and it is really
feeling like a good decision.
As for your reporting tool issue, you can just put additional
columns on your time dimension like
calendar_date DATETIME,
month_name VARCHAR(10),
day_name VARCHAR(10),
day_of_week VARCHAR(10),
...etc...
So you can have nice pre-formatted things to use in your
reporting tools.
It also makes for nice roll-up groupings if you include things
like
fiscal_period,
week_of_year,
quarter_of_year,
...etc...
Good luck,
Steve
"examnotes" <Marcelo@.discussions.microsoft.com>
wrote in
news:62A63A74-D1CE-4519-AA48-737FE514EB28@.microsoft.com:

> I have to create a Time dimension with day grain in a
> Datawarehouse system and I don’t know what is the best data
> type for the primary key...
> For example
> 1) I could put Number(8) datatype, then the dates will be:
> 20050114, 20050115, 20050116... Then in the fact tables I put
> the Number(8) datatype in the date fields... But in my
> reporting tools I have to put the conversion function to show
> the dates in the right format. 2) Or I could put Date
> datatype, then the dates will be: 01/14/2005, 01/15/2005,
> 01/16/2005... Then in the fact tables I put the Date datatype
> in the date fields...
>
> It’s the Date primary key a bad datatype? (Very slow)
> What is the best Primary Key Data type in Time Dimension?
> Thanks!
>

Primary Key AutoIncrement

(This is the second time I have sent this. For some reason I never did
see my thread on the server. If it appeared for you I apologize for the
duplicate.)
Hi I am struggling with creating an autoincremental key in my table in
my SQL database.
In the insert stored proc I have here I am trying to tell it to raise up
the number for the value in the QUOTEACTIONID field. It is the primary
key.
I am actually using this stored procedure in a vb.net program using
ADO.NET but I am told the incrementing should be done on the SQL side.
In addition I created a table called COUNTER which I thought about
inserting a higher number for each record added which the insert proc
could then use to enter a value for QUOTEACTIONID. But more experienced
folks told me this was a bad idea.
Can anyone suggest something and thank you.
CREATE PROCEDURE databaseexample.QuoteActionInsertCommand
(
@.QUOTEACTIONID int,
@.QUOTEACTIONDATE datetime,
@.QUOTEACTIONLOGINNAME varchar(50),
@.QUOTEACTIONIP varchar(50),
@.QUOTEACTIONCOMPNAME varchar(50),
@.QUOTEACTIONCOMPUSER varchar(50),
@.QUOTEACTIONUSERNAME varchar(50),
@.QUOTEACTIONQUOTENO int,
@.QUOTEACTIONQUOTEREL int,
@.QUOTEACTIONITEMNO int,
@.QUOTEACTIONITEMDESC varchar(50),
@.QUOTEACTIONITEMSTATUS varchar(50),
@.QUOTEACTIONITEMCOLOR varchar(50),
@.QUOTEACTIONITEMPRICE float,
@.QUOTEACTIONORDER varchar(50),
@.QUOTEACTIONCOMMENT varchar(250)
)
AS
SET NOCOUNT OFF;
INSERT INTO dbo.QUOTEACTION(QUOTEACTIONID, QUOTEACTIONDATE,
QUOTEACTIONLOGINNAME, QUOTEACTIONIP, QUOTEACTIONCOMPNAME,
QUOTEACTIONCOMPUSER, QUOTEACTIONUSERNAME, QUOTEACTIONQUOTENO,
QUOTEACTIONQUOTEREL, QUOTEACTIONITEMNO, QUOTEACTIONITEMDESC,
QUOTEACTIONITEMSTATUS, QUOTEACTIONITEMCOLOR, QUOTEACTIONITEMPRICE,
QUOTEACTIONORDER, QUOTEACTIONCOMMENT) VALUES (@.QUOTEACTIONID,
@.QUOTEACTIONDATE, @.QUOTEACTIONLOGINNAME, @.QUOTEACTIONIP,
@.QUOTEACTIONCOMPNAME, @.QUOTEACTIONCOMPUSER, @.QUOTEACTIONUSERNAME,
@.QUOTEACTIONQUOTENO, @.QUOTEACTIONQUOTEREL, @.QUOTEACTIONITEMNO,
@.QUOTEACTIONITEMDESC, @.QUOTEACTIONITEMSTATUS, @.QUOTEACTIONITEMCOLOR,
@.QUOTEACTIONITEMPRICE, @.QUOTEACTIONORDER, @.QUOTEACTIONCOMMENT);
SELECT QUOTEACTIONID, QUOTEACTIONDATE, QUOTEACTIONLOGINNAME,
QUOTEACTIONIP, QUOTEACTIONCOMPNAME, QUOTEACTIONCOMPUSER,
QUOTEACTIONUSERNAME, QUOTEACTIONQUOTENO, QUOTEACTIONQUOTEREL,
QUOTEACTIONITEMNO, QUOTEACTIONITEMDESC, QUOTEACTIONITEMSTATUS,
QUOTEACTIONITEMCOLOR, QUOTEACTIONITEMPRICE, QUOTEACTIONORDER,
QUOTEACTIONCOMMENT FROM dbo.QUOTEACTION WHERE (QUOTEACTIONID =
@.QUOTEACTIONID) GOModify QuoteActionID column to have identity and modify the sql stmt as
follows
INSERT INTO dbo.QUOTEACTION(QUOTEACTIONDATE,
QUOTEACTIONLOGINNAME, QUOTEACTIONIP, QUOTEACTIONCOMPNAME,
QUOTEACTIONCOMPUSER, QUOTEACTIONUSERNAME, QUOTEACTIONQUOTENO,
QUOTEACTIONQUOTEREL, QUOTEACTIONITEMNO, QUOTEACTIONITEMDESC,
QUOTEACTIONITEMSTATUS, QUOTEACTIONITEMCOLOR, QUOTEACTIONITEMPRICE,
QUOTEACTIONORDER, QUOTEACTIONCOMMENT) VALUES (@.QUOTEACTIONID,
@.QUOTEACTIONDATE, @.QUOTEACTIONLOGINNAME, @.QUOTEACTIONIP,
@.QUOTEACTIONCOMPNAME, @.QUOTEACTIONCOMPUSER, @.QUOTEACTIONUSERNAME,
@.QUOTEACTIONQUOTENO, @.QUOTEACTIONQUOTEREL, @.QUOTEACTIONITEMNO,
@.QUOTEACTIONITEMDESC, @.QUOTEACTIONITEMSTATUS, @.QUOTEACTIONITEMCOLOR,
@.QUOTEACTIONITEMPRICE, @.QUOTEACTIONORDER, @.QUOTEACTIONCOMMENT);
SELECT QUOTEACTIONDATE, QUOTEACTIONLOGINNAME,
QUOTEACTIONIP, QUOTEACTIONCOMPNAME, QUOTEACTIONCOMPUSER,
QUOTEACTIONUSERNAME, QUOTEACTIONQUOTENO, QUOTEACTIONQUOTEREL,
QUOTEACTIONITEMNO, QUOTEACTIONITEMDESC, QUOTEACTIONITEMSTATUS,
QUOTEACTIONITEMCOLOR, QUOTEACTIONITEMPRICE, QUOTEACTIONORDER,
QUOTEACTIONCOMMENT FROM dbo.QUOTEACTION WHERE (QUOTEACTIONID =
@.QUOTEACTIONID)|||Try,
CREATE PROCEDURE databaseexample.QuoteActionInsertCommand
(
@.QUOTEACTIONDATE datetime,
@.QUOTEACTIONLOGINNAME varchar(50),
@.QUOTEACTIONIP varchar(50),
@.QUOTEACTIONCOMPNAME varchar(50),
@.QUOTEACTIONCOMPUSER varchar(50),
@.QUOTEACTIONUSERNAME varchar(50),
@.QUOTEACTIONQUOTENO int,
@.QUOTEACTIONQUOTEREL int,
@.QUOTEACTIONITEMNO int,
@.QUOTEACTIONITEMDESC varchar(50),
@.QUOTEACTIONITEMSTATUS varchar(50),
@.QUOTEACTIONITEMCOLOR varchar(50),
@.QUOTEACTIONITEMPRICE float,
@.QUOTEACTIONORDER varchar(50),
@.QUOTEACTIONCOMMENT varchar(250),
@.QUOTEACTIONID int output
)
AS
SET NOCOUNT OFF;
INSERT INTO dbo.QUOTEACTION(QUOTEACTIONID, QUOTEACTIONDATE,
QUOTEACTIONLOGINNAME, QUOTEACTIONIP, QUOTEACTIONCOMPNAME,
QUOTEACTIONCOMPUSER, QUOTEACTIONUSERNAME, QUOTEACTIONQUOTENO,
QUOTEACTIONQUOTEREL, QUOTEACTIONITEMNO, QUOTEACTIONITEMDESC,
QUOTEACTIONITEMSTATUS, QUOTEACTIONITEMCOLOR, QUOTEACTIONITEMPRICE,
QUOTEACTIONORDER, QUOTEACTIONCOMMENT) VALUES (@.QUOTEACTIONID,
@.QUOTEACTIONDATE, @.QUOTEACTIONLOGINNAME, @.QUOTEACTIONIP,
@.QUOTEACTIONCOMPNAME, @.QUOTEACTIONCOMPUSER, @.QUOTEACTIONUSERNAME,
@.QUOTEACTIONQUOTENO, @.QUOTEACTIONQUOTEREL, @.QUOTEACTIONITEMNO,
@.QUOTEACTIONITEMDESC, @.QUOTEACTIONITEMSTATUS, @.QUOTEACTIONITEMCOLOR,
@.QUOTEACTIONITEMPRICE, @.QUOTEACTIONORDER, @.QUOTEACTIONCOMMENT);
if @.@.error = 0
begin
set @.quoteactionid = scope_identity( )
SELECT
QUOTEACTIONID, QUOTEACTIONDATE, QUOTEACTIONLOGINNAME,
QUOTEACTIONIP, QUOTEACTIONCOMPNAME, QUOTEACTIONCOMPUSER,
QUOTEACTIONUSERNAME, QUOTEACTIONQUOTENO, QUOTEACTIONQUOTEREL,
QUOTEACTIONITEMNO, QUOTEACTIONITEMDESC, QUOTEACTIONITEMSTATUS,
QUOTEACTIONITEMCOLOR, QUOTEACTIONITEMPRICE, QUOTEACTIONORDER,
QUOTEACTIONCOMMENT
FROM dbo.QUOTEACTION
WHERE (QUOTEACTIONID = @.QUOTEACTIONID)
return @.@.error
end
else
return 1
GO
Let sql server to generate the identity value and add an output parameter to
the command parameters collection to get the value of the identity.
AMB
"scorpion53061" wrote:

> (This is the second time I have sent this. For some reason I never did
> see my thread on the server. If it appeared for you I apologize for the
> duplicate.)
> Hi I am struggling with creating an autoincremental key in my table in
> my SQL database.
> In the insert stored proc I have here I am trying to tell it to raise up
> the number for the value in the QUOTEACTIONID field. It is the primary
> key.
> I am actually using this stored procedure in a vb.net program using
> ADO.NET but I am told the incrementing should be done on the SQL side.
> In addition I created a table called COUNTER which I thought about
> inserting a higher number for each record added which the insert proc
> could then use to enter a value for QUOTEACTIONID. But more experienced
> folks told me this was a bad idea.
> Can anyone suggest something and thank you.
> CREATE PROCEDURE databaseexample.QuoteActionInsertCommand
> (
> @.QUOTEACTIONID int,
> @.QUOTEACTIONDATE datetime,
> @.QUOTEACTIONLOGINNAME varchar(50),
> @.QUOTEACTIONIP varchar(50),
> @.QUOTEACTIONCOMPNAME varchar(50),
> @.QUOTEACTIONCOMPUSER varchar(50),
> @.QUOTEACTIONUSERNAME varchar(50),
> @.QUOTEACTIONQUOTENO int,
> @.QUOTEACTIONQUOTEREL int,
> @.QUOTEACTIONITEMNO int,
> @.QUOTEACTIONITEMDESC varchar(50),
> @.QUOTEACTIONITEMSTATUS varchar(50),
> @.QUOTEACTIONITEMCOLOR varchar(50),
> @.QUOTEACTIONITEMPRICE float,
> @.QUOTEACTIONORDER varchar(50),
> @.QUOTEACTIONCOMMENT varchar(250)
> )
> AS
> SET NOCOUNT OFF;
> INSERT INTO dbo.QUOTEACTION(QUOTEACTIONID, QUOTEACTIONDATE,
> QUOTEACTIONLOGINNAME, QUOTEACTIONIP, QUOTEACTIONCOMPNAME,
> QUOTEACTIONCOMPUSER, QUOTEACTIONUSERNAME, QUOTEACTIONQUOTENO,
> QUOTEACTIONQUOTEREL, QUOTEACTIONITEMNO, QUOTEACTIONITEMDESC,
> QUOTEACTIONITEMSTATUS, QUOTEACTIONITEMCOLOR, QUOTEACTIONITEMPRICE,
> QUOTEACTIONORDER, QUOTEACTIONCOMMENT) VALUES (@.QUOTEACTIONID,
> @.QUOTEACTIONDATE, @.QUOTEACTIONLOGINNAME, @.QUOTEACTIONIP,
> @.QUOTEACTIONCOMPNAME, @.QUOTEACTIONCOMPUSER, @.QUOTEACTIONUSERNAME,
> @.QUOTEACTIONQUOTENO, @.QUOTEACTIONQUOTEREL, @.QUOTEACTIONITEMNO,
> @.QUOTEACTIONITEMDESC, @.QUOTEACTIONITEMSTATUS, @.QUOTEACTIONITEMCOLOR,
> @.QUOTEACTIONITEMPRICE, @.QUOTEACTIONORDER, @.QUOTEACTIONCOMMENT);
> SELECT QUOTEACTIONID, QUOTEACTIONDATE, QUOTEACTIONLOGINNAME,
> QUOTEACTIONIP, QUOTEACTIONCOMPNAME, QUOTEACTIONCOMPUSER,
> QUOTEACTIONUSERNAME, QUOTEACTIONQUOTENO, QUOTEACTIONQUOTEREL,
> QUOTEACTIONITEMNO, QUOTEACTIONITEMDESC, QUOTEACTIONITEMSTATUS,
> QUOTEACTIONITEMCOLOR, QUOTEACTIONITEMPRICE, QUOTEACTIONORDER,
> QUOTEACTIONCOMMENT FROM dbo.QUOTEACTION WHERE (QUOTEACTIONID =
> @.QUOTEACTIONID) GO
>|||Correction,
I forgot to take off column QUOTEACTIONID from the insert.
CREATE PROCEDURE databaseexample.QuoteActionInsertCommand
(
@.QUOTEACTIONDATE datetime,
@.QUOTEACTIONLOGINNAME varchar(50),
@.QUOTEACTIONIP varchar(50),
@.QUOTEACTIONCOMPNAME varchar(50),
@.QUOTEACTIONCOMPUSER varchar(50),
@.QUOTEACTIONUSERNAME varchar(50),
@.QUOTEACTIONQUOTENO int,
@.QUOTEACTIONQUOTEREL int,
@.QUOTEACTIONITEMNO int,
@.QUOTEACTIONITEMDESC varchar(50),
@.QUOTEACTIONITEMSTATUS varchar(50),
@.QUOTEACTIONITEMCOLOR varchar(50),
@.QUOTEACTIONITEMPRICE float,
@.QUOTEACTIONORDER varchar(50),
@.QUOTEACTIONCOMMENT varchar(250),
@.QUOTEACTIONID int output
)
AS
SET NOCOUNT OFF;
INSERT INTO dbo.QUOTEACTION(QUOTEACTIONDATE,
QUOTEACTIONLOGINNAME, QUOTEACTIONIP, QUOTEACTIONCOMPNAME,
QUOTEACTIONCOMPUSER, QUOTEACTIONUSERNAME, QUOTEACTIONQUOTENO,
QUOTEACTIONQUOTEREL, QUOTEACTIONITEMNO, QUOTEACTIONITEMDESC,
QUOTEACTIONITEMSTATUS, QUOTEACTIONITEMCOLOR, QUOTEACTIONITEMPRICE,
QUOTEACTIONORDER, QUOTEACTIONCOMMENT) VALUES (@.QUOTEACTIONDATE,
@.QUOTEACTIONLOGINNAME, @.QUOTEACTIONIP,
@.QUOTEACTIONCOMPNAME, @.QUOTEACTIONCOMPUSER, @.QUOTEACTIONUSERNAME,
@.QUOTEACTIONQUOTENO, @.QUOTEACTIONQUOTEREL, @.QUOTEACTIONITEMNO,
@.QUOTEACTIONITEMDESC, @.QUOTEACTIONITEMSTATUS, @.QUOTEACTIONITEMCOLOR,
@.QUOTEACTIONITEMPRICE, @.QUOTEACTIONORDER, @.QUOTEACTIONCOMMENT);
if @.@.error = 0
begin
set @.quoteactionid = scope_identity( )
SELECT
QUOTEACTIONID, QUOTEACTIONDATE, QUOTEACTIONLOGINNAME,
QUOTEACTIONIP, QUOTEACTIONCOMPNAME, QUOTEACTIONCOMPUSER,
QUOTEACTIONUSERNAME, QUOTEACTIONQUOTENO, QUOTEACTIONQUOTEREL,
QUOTEACTIONITEMNO, QUOTEACTIONITEMDESC, QUOTEACTIONITEMSTATUS,
QUOTEACTIONITEMCOLOR, QUOTEACTIONITEMPRICE, QUOTEACTIONORDER,
QUOTEACTIONCOMMENT
FROM dbo.QUOTEACTION
WHERE (QUOTEACTIONID = @.QUOTEACTIONID)
return @.@.error
end
else
return 1
GO
AMB
"Alejandro Mesa" wrote:
> Try,
> CREATE PROCEDURE databaseexample.QuoteActionInsertCommand
> (
> @.QUOTEACTIONDATE datetime,
> @.QUOTEACTIONLOGINNAME varchar(50),
> @.QUOTEACTIONIP varchar(50),
> @.QUOTEACTIONCOMPNAME varchar(50),
> @.QUOTEACTIONCOMPUSER varchar(50),
> @.QUOTEACTIONUSERNAME varchar(50),
> @.QUOTEACTIONQUOTENO int,
> @.QUOTEACTIONQUOTEREL int,
> @.QUOTEACTIONITEMNO int,
> @.QUOTEACTIONITEMDESC varchar(50),
> @.QUOTEACTIONITEMSTATUS varchar(50),
> @.QUOTEACTIONITEMCOLOR varchar(50),
> @.QUOTEACTIONITEMPRICE float,
> @.QUOTEACTIONORDER varchar(50),
> @.QUOTEACTIONCOMMENT varchar(250),
> @.QUOTEACTIONID int output
> )
> AS
> SET NOCOUNT OFF;
> INSERT INTO dbo.QUOTEACTION(QUOTEACTIONID, QUOTEACTIONDATE,
> QUOTEACTIONLOGINNAME, QUOTEACTIONIP, QUOTEACTIONCOMPNAME,
> QUOTEACTIONCOMPUSER, QUOTEACTIONUSERNAME, QUOTEACTIONQUOTENO,
> QUOTEACTIONQUOTEREL, QUOTEACTIONITEMNO, QUOTEACTIONITEMDESC,
> QUOTEACTIONITEMSTATUS, QUOTEACTIONITEMCOLOR, QUOTEACTIONITEMPRICE,
> QUOTEACTIONORDER, QUOTEACTIONCOMMENT) VALUES (@.QUOTEACTIONID,
> @.QUOTEACTIONDATE, @.QUOTEACTIONLOGINNAME, @.QUOTEACTIONIP,
> @.QUOTEACTIONCOMPNAME, @.QUOTEACTIONCOMPUSER, @.QUOTEACTIONUSERNAME,
> @.QUOTEACTIONQUOTENO, @.QUOTEACTIONQUOTEREL, @.QUOTEACTIONITEMNO,
> @.QUOTEACTIONITEMDESC, @.QUOTEACTIONITEMSTATUS, @.QUOTEACTIONITEMCOLOR,
> @.QUOTEACTIONITEMPRICE, @.QUOTEACTIONORDER, @.QUOTEACTIONCOMMENT);
> if @.@.error = 0
> begin
> set @.quoteactionid = scope_identity( )
> SELECT
> QUOTEACTIONID, QUOTEACTIONDATE, QUOTEACTIONLOGINNAME,
> QUOTEACTIONIP, QUOTEACTIONCOMPNAME, QUOTEACTIONCOMPUSER,
> QUOTEACTIONUSERNAME, QUOTEACTIONQUOTENO, QUOTEACTIONQUOTEREL,
> QUOTEACTIONITEMNO, QUOTEACTIONITEMDESC, QUOTEACTIONITEMSTATUS,
> QUOTEACTIONITEMCOLOR, QUOTEACTIONITEMPRICE, QUOTEACTIONORDER,
> QUOTEACTIONCOMMENT
> FROM dbo.QUOTEACTION
> WHERE (QUOTEACTIONID = @.QUOTEACTIONID)
> return @.@.error
> end
> else
> return 1
> GO
> Let sql server to generate the identity value and add an output parameter
to
> the command parameters collection to get the value of the identity.
>
> AMB
> "scorpion53061" wrote:
>|||Thank you Alejandro for your help.
I tried your proc and it looks like I put the cart before the horse at
least it seems so what I was reading.
I added a "identity column" with this statement and made it the primary
key...
alter table QUOTEACTION
add record_id numeric(5,0) identity not null
Then I adjusted the proc as follows. When I run this however with this
query analyzer statement it tells me that "Explicit value must be
specified for identity column in table 'QUOTEACTION' when
IDENTITY_INSERT is set to ON"
Here is the query analyzer for the insert followed by the stored proc
that was generated. By the way do you know by chance how .NET handles
this or even needs to handle this? It balked on the delete statement and
the update statement. I wonder if it has to do with the insert.
QuoteActionInsert '1/1/2000',
'loginname',
'ipaddress',
'computer name',
'computer user',
'login name',
1,
1,
1,
'ITEM DESCRIPTION',
'ITEM STATUS',
'ITEM COLOR',
'1.21',
'ORDER',
'COMMENT'
CREATE PROCEDURE kjmsolutions.QuoteActionInsert
(
@.QUOTEACTIONDATE datetime,
@.QUOTEACTIONLOGINNAME varchar(50),
@.QUOTEACTIONIP varchar(50),
@.QUOTEACTIONCOMPNAME varchar(50),
@.QUOTEACTIONCOMPUSER varchar(50),
@.QUOTEACTIONUSERNAME varchar(50),
@.QUOTEACTIONQUOTENO int,
@.QUOTEACTIONQUOTEREL int,
@.QUOTEACTIONITEMNO int,
@.QUOTEACTIONITEMDESC varchar(50),
@.QUOTEACTIONITEMSTATUS varchar(50),
@.QUOTEACTIONITEMCOLOR varchar(50),
@.QUOTEACTIONITEMPRICE float,
@.QUOTEACTIONORDER varchar(50),
@.QUOTEACTIONCOMMENT varchar(250)
)
AS
SET NOCOUNT OFF;
INSERT INTO dbo.QUOTEACTION(QUOTEACTIONDATE, QUOTEACTIONLOGINNAME,
QUOTEACTIONIP, QUOTEACTIONCOMPNAME, QUOTEACTIONCOMPUSER,
QUOTEACTIONUSERNAME, QUOTEACTIONQUOTENO, QUOTEACTIONQUOTEREL,
QUOTEACTIONITEMNO, QUOTEACTIONITEMDESC, QUOTEACTIONITEMSTATUS,
QUOTEACTIONITEMCOLOR, QUOTEACTIONITEMPRICE, QUOTEACTIONORDER,
QUOTEACTIONCOMMENT) VALUES (@.QUOTEACTIONDATE, @.QUOTEACTIONLOGINNAME,
@.QUOTEACTIONIP, @.QUOTEACTIONCOMPNAME, @.QUOTEACTIONCOMPUSER,
@.QUOTEACTIONUSERNAME, @.QUOTEACTIONQUOTENO, @.QUOTEACTIONQUOTEREL,
@.QUOTEACTIONITEMNO, @.QUOTEACTIONITEMDESC, @.QUOTEACTIONITEMSTATUS,
@.QUOTEACTIONITEMCOLOR, @.QUOTEACTIONITEMPRICE, @.QUOTEACTIONORDER,
@.QUOTEACTIONCOMMENT);
SELECT record_id, QUOTEACTIONDATE, QUOTEACTIONLOGINNAME, QUOTEACTIONIP,
QUOTEACTIONCOMPNAME, QUOTEACTIONCOMPUSER, QUOTEACTIONUSERNAME,
QUOTEACTIONQUOTENO, QUOTEACTIONQUOTEREL, QUOTEACTIONITEMNO,
QUOTEACTIONITEMDESC, QUOTEACTIONITEMSTATUS, QUOTEACTIONITEMCOLOR,
QUOTEACTIONITEMPRICE, QUOTEACTIONORDER, QUOTEACTIONCOMMENT FROM
dbo.QUOTEACTION WHERE (record_id = @.@.IDENTITY)
GO
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in
message news:AlejandroMesa@.discussions.microsoft.com:
> Correction,
> I forgot to take off column QUOTEACTIONID from the insert.
> CREATE PROCEDURE databaseexample.QuoteActionInsertCommand
> (
> @.QUOTEACTIONDATE datetime,
> @.QUOTEACTIONLOGINNAME varchar(50),
> @.QUOTEACTIONIP varchar(50),
> @.QUOTEACTIONCOMPNAME varchar(50),
> @.QUOTEACTIONCOMPUSER varchar(50),
> @.QUOTEACTIONUSERNAME varchar(50),
> @.QUOTEACTIONQUOTENO int,
> @.QUOTEACTIONQUOTEREL int,
> @.QUOTEACTIONITEMNO int,
> @.QUOTEACTIONITEMDESC varchar(50),
> @.QUOTEACTIONITEMSTATUS varchar(50),
> @.QUOTEACTIONITEMCOLOR varchar(50),
> @.QUOTEACTIONITEMPRICE float,
> @.QUOTEACTIONORDER varchar(50),
> @.QUOTEACTIONCOMMENT varchar(250),
> @.QUOTEACTIONID int output
> )
> AS
> SET NOCOUNT OFF;
> INSERT INTO dbo.QUOTEACTION(QUOTEACTIONDATE,
> QUOTEACTIONLOGINNAME, QUOTEACTIONIP, QUOTEACTIONCOMPNAME,
> QUOTEACTIONCOMPUSER, QUOTEACTIONUSERNAME, QUOTEACTIONQUOTENO,
> QUOTEACTIONQUOTEREL, QUOTEACTIONITEMNO, QUOTEACTIONITEMDESC,
> QUOTEACTIONITEMSTATUS, QUOTEACTIONITEMCOLOR, QUOTEACTIONITEMPRICE,
> QUOTEACTIONORDER, QUOTEACTIONCOMMENT) VALUES (@.QUOTEACTIONDATE,
> @.QUOTEACTIONLOGINNAME, @.QUOTEACTIONIP,
> @.QUOTEACTIONCOMPNAME, @.QUOTEACTIONCOMPUSER, @.QUOTEACTIONUSERNAME,
> @.QUOTEACTIONQUOTENO, @.QUOTEACTIONQUOTEREL, @.QUOTEACTIONITEMNO,
> @.QUOTEACTIONITEMDESC, @.QUOTEACTIONITEMSTATUS, @.QUOTEACTIONITEMCOLOR,
> @.QUOTEACTIONITEMPRICE, @.QUOTEACTIONORDER, @.QUOTEACTIONCOMMENT);
> if @.@.error = 0
> begin
> set @.quoteactionid = scope_identity( )
> SELECT
> QUOTEACTIONID, QUOTEACTIONDATE, QUOTEACTIONLOGINNAME,
> QUOTEACTIONIP, QUOTEACTIONCOMPNAME, QUOTEACTIONCOMPUSER,
> QUOTEACTIONUSERNAME, QUOTEACTIONQUOTENO, QUOTEACTIONQUOTEREL,
> QUOTEACTIONITEMNO, QUOTEACTIONITEMDESC, QUOTEACTIONITEMSTATUS,
> QUOTEACTIONITEMCOLOR, QUOTEACTIONITEMPRICE, QUOTEACTIONORDER,
> QUOTEACTIONCOMMENT
> FROM dbo.QUOTEACTION
> WHERE (QUOTEACTIONID = @.QUOTEACTIONID)
> return @.@.error
> end
> else
> return 1
> GO
>
> AMB
> "Alejandro Mesa" wrote:
>

'primary file was full' claimed by job when there is plenty space to grow

Hi,
Working on sql server 2000.
I have a job that pulls data from one server into this
server's database. It failed last time claiming the
primary file of the db was full while there were over 24
gig room to grow and the job will only pull in less than 2
gig data.
Then, I run it again serveral hours later, and everything
went through fine.
What happened? Ever encountered this wierd problem
yourself?
Many thanks.
JJCheck out below:
http://support.microsoft.com/default.aspx?scid=kb;en-us;305635
Tibor Karaszi
"JJ Wang" <anonymous@.discussions.microsoft.com> wrote in message
news:07ab01c3a258$9cf1a610$a301280a@.phx.gbl...
> Hi,
> Working on sql server 2000.
> I have a job that pulls data from one server into this
> server's database. It failed last time claiming the
> primary file of the db was full while there were over 24
> gig room to grow and the job will only pull in less than 2
> gig data.
> Then, I run it again serveral hours later, and everything
> went through fine.
> What happened? Ever encountered this wierd problem
> yourself?
> Many thanks.
> JJ

Price Aggregation Question

Hi all,

I have a cube that has time, product, and location hierarchies. I also have the retail price for every transaction at the leaf level. I am having trouble writing the mdx to get the aggregation the way I need them.

In the time dimension, at the non-leaf level, i want the last retail price of the children in the time. ie: if I am at the quarter level, i want to show the price of period 3.

In both the Product and Location dimensions, I want the retail price to be averaged at the non-leaf levels such as Product category and Zip code.

I don't really know what the right approach is to make this happen, any advice is greatly appreciated.

Thanks,

Wino...

Hi Wino,

Based on my understanding of your scenario, and assuming you have AS 2005 Enterprise Edition, here's an approach to try:

Create a cube measure like [RetailPrice] on the "Retail Price" fact table field, using LastNonEmpty aggregation function.

Prevmember versus Lag(1)

My brain hurts again...

I have a cube with Time Intelligence, and I'm trying to make a verysimple function: Percentage Change (current month vs. prior month). To determine this, I compare

([Time].[Month].Lag(1),[Measures].[ACTV QTY]) and

([Time].[Month].CurrentMember,[Measures].[ACTV QTY])

Seems pretty simple, right? Except that Lag(1) keeps giving me the NEXT month instead of the prior month! So, I tried Lag(-1), which is contrary to the documentation of course, but it still gave me the next month!! Really weird!

Then, for kicks, I tried replacing the Lag expression with the following:

([Time].[Month].Prevmember,[Measures].[ACTV QTY])

...and guess what? It works. : (

Interestingly enough, this ONLY seems to be a problem in Reporting Services, not in the SSAS cube browser!!!!?

If there are issues between SSAS and SSRS, does that also explain why my calculated fields' conditional formatting doesn't carry forward from SSAS to SSRS?

Can't seem to reproduce the Lag(1) issue, using Adventure Works - if you paste the MDX query generated by Reporting Services into Management Studio and execute it, do you get different results - and can you provide an Adventure Works repro?

Assuming that you're using the Analysis Services Provider, I think that you need to do a bit of manual "splicing" to carry over the conditional formatting. For example, [Scenario].[Budget Variance %] in Adventure Works has conditional background coloring. If you create an Adventure Works cube report, with [Measures].[Amount] on columns and [Scenario] on rows, you can set the background color expression of the [Measures].[Amount] value textbox to:

=Fields!Amount.BackgroundColor

|||

It's still not working correctly. In order to get the equivalent of [Time].[Month].Lag(6), I have to use:

[Time].[Month].Prevmember.Prevmember.Prevmember.Prevmember.Prevmember

Otherwise, [Time].[Month].Lag(6) gives me a value from 5 months forward instead of backward.

In other words, 5 prevmember commands. My Time dimension was created using the SQL Server functionality to define and populate the table, so I don't see any reason why things like "Natural order" and stuff like that would be giving me any problems.

The craziest part, as I said, is that in the cube browser in SSAS, it is perfect. The weird backwards lag problem occurs only once I get to SSRS! Could it be related to the MDX Compatibility setting, or something? It's definitely a compatibility issue/setting.

Thanks again...!

Prevmember versus Lag(1)

My brain hurts again...

I have a cube with Time Intelligence, and I'm trying to make a verysimple function: Percentage Change (current month vs. prior month). To determine this, I compare

([Time].[Month].Lag(1),[Measures].[ACTV QTY]) and

([Time].[Month].CurrentMember,[Measures].[ACTV QTY])

Seems pretty simple, right? Except that Lag(1) keeps giving me the NEXT month instead of the prior month! So, I tried Lag(-1), which is contrary to the documentation of course, but it still gave me the next month!! Really weird!

Then, for kicks, I tried replacing the Lag expression with the following:

([Time].[Month].Prevmember,[Measures].[ACTV QTY])

...and guess what? It works. : (

Interestingly enough, this ONLY seems to be a problem in Reporting Services, not in the SSAS cube browser!!!!?

If there are issues between SSAS and SSRS, does that also explain why my calculated fields' conditional formatting doesn't carry forward from SSAS to SSRS?

Can't seem to reproduce the Lag(1) issue, using Adventure Works - if you paste the MDX query generated by Reporting Services into Management Studio and execute it, do you get different results - and can you provide an Adventure Works repro?

Assuming that you're using the Analysis Services Provider, I think that you need to do a bit of manual "splicing" to carry over the conditional formatting. For example, [Scenario].[Budget Variance %] in Adventure Works has conditional background coloring. If you create an Adventure Works cube report, with [Measures].[Amount] on columns and [Scenario] on rows, you can set the background color expression of the [Measures].[Amount] value textbox to:

=Fields!Amount.BackgroundColor

|||

It's still not working correctly. In order to get the equivalent of [Time].[Month].Lag(6), I have to use:

[Time].[Month].Prevmember.Prevmember.Prevmember.Prevmember.Prevmember

Otherwise, [Time].[Month].Lag(6) gives me a value from 5 months forward instead of backward.

In other words, 5 prevmember commands. My Time dimension was created using the SQL Server functionality to define and populate the table, so I don't see any reason why things like "Natural order" and stuff like that would be giving me any problems.

The craziest part, as I said, is that in the cube browser in SSAS, it is perfect. The weird backwards lag problem occurs only once I get to SSRS! Could it be related to the MDX Compatibility setting, or something? It's definitely a compatibility issue/setting.

Thanks again...!

Previous day query

Hi There,

I am a newbie on SQL qieries and I am trying to run a query on a date/time filed to get yesterdays data.

Here is my test query ran from SQL analyzer:

select * from mvs_store_all_data_time_change where mvs_creation_date = {fn NOW() }

The error I am getting is:

Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.

I assume it is to do with the format of this field I am trying to query and may need to be converted?

Any help will be much appreciated.

Cheers,

Riaan.Riaan,
Try
select * from mvs_store_all_data_time_change
where mvs_creation_date > (getdate()-1)|||try this one

select * from <table> where DATEPART(dd, <date_column>)=DATEPART(dd, getdate()-1)

<date_column>-where your date is saved
Explanation-let it be 03/08/2006 today,then DATEPART(dd, getdate()-1)
returns 02.and u can get the data of the previous day.
Regards,
Lipsa|||Try this one

select * from <table> where DATEPART(dd, <date_column>)=DATEPART(dd, getdate()-1)

<date_column>-your column name in database where u insert dates
Explanation-suppose its 03/08/2006 today.so ,DATEPART(dd, getdate()-1)will return 02.and u can do the comarison and get the data of previous day. :)
Regards,
Lipsa :)|||Hi There.

Ok, I have tried your suggestion, but get the following error:

"Syntax error converting datetime from character string."

Any further ideas :). Cheers, Riaan.|||Hi Phil. Ok I treid your suggestion, but get the following error "Syntax error converting datetime from character string.". Any more ideas :).

Cheers, Riaan.|||

Quote:

Originally Posted by Riaan

Hi Phil. Ok I treid your suggestion, but get the following error "Syntax error converting datetime from character string.". Any more ideas :).

Cheers, Riaan.

Ho Riaan,

try this one...

select * from mvs_store_all_data_time_change
where convert(char,mvs_creation_date,102) = convert(char,getdate()-1,102)

regards,
Pankaj Bhatnagar|||

Quote:

Originally Posted by bhatnagarp

Ho Riaan,

try this one...

select * from mvs_store_all_data_time_change
where convert(char,mvs_creation_date,102) = convert(char,getdate()-1,102)

regards,
Pankaj Bhatnagar

Hi Pankaj

Ok, I tried your suggestion and now I am not getting the error message, but no data is returned? Is this not a case of converting this field (which is a integer, to a string and then cast it as a datetime format?). If so, can you maybe help with the syntax of such a query? ALso can I do it from a view, where I am running this from, as I am also not sure where to enter it in the view?. Hope you can help.

Cheers,

Riaan.|||Hi Pankaj

Ok, I tried your suggestion and now I am not getting the error message, but no data is returned? Is this not a case of converting this field (which is a integer, to a string and then cast it as a datetime format?). If so, can you maybe help with the syntax of such a query? ALso can I do it from a view, where I am running this from, as I am also not sure where to enter it in the view?. Hope you can help.

Cheers,

Riaan.

Preview Error

Hi all,

I am all the time receiving this arror message:

TITLE: Microsoft Report Designer

A connection cannot be made to the database.
Set and test the connection string.

ADDITIONAL INFORMATION:

Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection. (Microsoft SQL Server, Error: 18452)

What should i do?

Thanks

Hi,

you are trying to logon with your datasource object as a SQL Server login, but your SQL Server doesn′t accept mixed (this includes SQL Server as well as Windows) authentication. So either switch your server to mixed authentication or switch your datasource objects security context to Windows (Integrated) Authentication.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

preventing second instance of SP

Is it possible to prevent second instance of my SP running
at the same time on SQL Server?
I'll appreciate a SQL example how-to define if the SP
already running.
Thank youAbout the only way you can do that is by exclusively locking something so
the second instance waits...
--
Wayne Snyder MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
(Please respond only to the newsgroups.)
I support the Professional Association for SQL Server
(www.sqlpass.org)
"vitaliyk" <vitaliykrasner@.hotmail.com> wrote in message
news:075a01c3c315$d1d5b8c0$a101280a@.phx.gbl...
> Is it possible to prevent second instance of my SP running
> at the same time on SQL Server?
> I'll appreciate a SQL example how-to define if the SP
> already running.
> Thank you

Preventing overlapping data entry

Hello Everyone,

I have a web form that allows the user to select a time to reserve. I am trying to make it so that when a user selects a date to schedule something (which i have working) a drop down list will fill with times that have not been reserved.

The data is stored in two tables: tblCalendar and tblTime. tblTime contains pkTimeID and times (which are all possible times to select from in half hour intervals). tblCalendar contains a few fields but timeID and date (which is in the format M/d/yyyy) are what I need to test against. timeID is the foreign key of pkTimeID.

Basically when the user selects the date, a function gets called that will run a SELECT statement to get the times available. All of this works, I am able to fill the ddl with all times available no matter what the date is or what has already been reserved. I want to check if a time has been already selected based on the date selected by the user and only allow times not selected to be listed.

After acheiving this I would like to prevent the immediate time before and immediate time after from being displayed because each reserved time will last for one hour but the data is stored in half hour increments.

Any help/suggestions/links will be greatly appreciated. If I need to provide anything else please let me know.

Thanks in advance,

Brian

This query should give you the timeIDs that have not been scheduled:

SELECT t.pkTimeIDFROM tblTime tWHERE t.pkTimeIDNOT IN (SELECT c.timeIDFROM tblCalendar cWHERE c.date ='4/20/2007' )

Replace the date with the date the user has selected.|||

This query leaves out the prior and next time slots to avoid overlapping a one hour appointment.

SELECT t.pkTimeIDFROM tblTime tWHERE t.pkTimeIDNOT IN (SELECT unavailableTimes.timeIDFROM (SELECT c.timeID, c.dateFROM tblCalendar cLEFTJOIN tblTime tON t.pkTimeID = c.timeIDUNION SELECT t1.pkTimeID, c.dateFROM tblCalendar cLEFTJOIN tblTime tON t.pkTimeID = c.timeIDLEFTJOIN tblTime t1ON t1.time =SUBSTRING(CONVERT(VARCHAR(20),DATEADD(n, -30, c.date +' ' + t.time)), 14, 7)UNION SELECT t2.pkTimeID, c.dateFROM tblCalendar cLEFTJOIN tblTime tON t.pkTimeID = c.timeIDLEFTJOIN tblTime t2ON t2.time =SUBSTRING(CONVERT(VARCHAR(20),DATEADD(n, 30, c.date +' ' + t.time)), 14, 7) ) unavailableTimesWHERE unavailableTimes.date ='4/20/2007' )

This query is not optimal but you get the idea. Again, replace the date with the date the user has selected. You may also need to tweak the SUBSTRING params based on the format of your DATETIMEs.|||

Thank you so much for your help. I think I was a little to vague on my description but this solution helped me get a solution that worked for me. I needed to check for the previous and next pkTimeID value to eliminate not the actual datetime values. Just to keep this short my statement is as follows:

SELECT t.pkTimeID, t.timeFROM tblTime tWHERE t.pkTimeIDNOT IN (SELECT c.timeID - 1FROM tblCalendar cUNIONSELECT c1.timeIDFROM tblCalendar c1UNIONSELECT c2.timeID + 1FROM tblCalendar c2WHERE (c2.[date] ='4/20/2007'))

Thank you again for your help, and if you have any suggestions on how I might improve what I did come up with I would appreciate it. This is my first "real" project in the work place and I would like to learn the best way to approach a problem where ever I can, so that I will not have to learn these things later.

Brian

Preventing deadlocks

When we start multiple publications at the same time on the same publisher
and even against different published datatabases, at times, we run into
deadlocks and start all over again. This was in 2000.
Do we know, if in 2005, the chances of deadlocks are minimized if i start
multiple publications at the same time?
Its on the publisher. We want to run multiple publications at the same time
to save the time we are down.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:Oh2GCVD2HHA.1124@.TK2MSFTNGP06.phx.gbl...
> Hassan - never experienced this myself. Presumably the deadlocks are on
> the subscriber? One possibility is that having a remote distributor might
> help, although I'd like to know about the source of the deadlocks. Any
> ideas of the source - perhaps the cleanup agent running at the same time
> as the distribution agent if the problem is on the publisher. Staggering
> the executions in this case should be sufficient (if practical).
> HTH,
> Paul Ibison
>