float datatype

I have several fields in a table with a float datatype. When the user
enters 1.1 into the database, it returns 1.1000000000000001 -- I would think
it should be 1.1000000000000000.
Won't this eventually cause mathematically errors and what do I do about
this.FLOAT is an approximate numeric type. If you require accurate,
fixed-precision results from calculations then use an exact type such as
NUMERIC.
--
David Portas
SQL Server MVP
--

Flipping back to simple mode

Hi,
I have three user databases on my production server and I noticed a strange
behavior whenever the server gets rebooted. on two of the databases the
recovery model flips back from Full to simple when the server is rebooted
any time. On the third database the recover model stays as Full even after
the reboot. Even after I switch the recovery model back to full on the two
databases what I see is, as soon as the server gets rebooted, they both flip
back to simple.
I am confused as to why it is happening only with two databases and not the
third. Is there something that is set up wrongly on these two databases?.
Any insight is highly helpful.
Thanks
MeherCheck under Management | jobs in Enterprise Manager, something this is
running sp_dboption '<database','select into','true' and sp_dboption
'<database>','trunc','true'
****************************************
***************************
Andy S.
MCSE NT/2000, MCDBA SQL 7/2000
andymcdba1@.NOMORESPAM.yahoo.com
Please remove NOMORESPAM before replying.
Always keep your antivirus and Microsoft software
up to date with the latest definitions and product updates.
Be suspicious of every email attachment, I will never send
or post anything other than the text of a http:// link nor
post the link directly to a file for downloading.
This posting is provided "as is" with no warranties
and confers no rights.
****************************************
***************************
"Meher Malakapalli" <mmalakapalli@.cohesioninc.com> wrote in message
news:OiCIFAW$DHA.2808@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have three user databases on my production server and I noticed a
strange
> behavior whenever the server gets rebooted. on two of the databases the
> recovery model flips back from Full to simple when the server is rebooted
> any time. On the third database the recover model stays as Full even after
> the reboot. Even after I switch the recovery model back to full on the two
> databases what I see is, as soon as the server gets rebooted, they both
flip
> back to simple.
> I am confused as to why it is happening only with two databases and not
the
> third. Is there something that is set up wrongly on these two databases?.
> Any insight is highly helpful.
> Thanks
> Meher
>|||I checked them. No job is running with those options. I only have the
following options set to true on both the databases.
torn page detection
auto create statistics
auto update statistics
BTW I am running SQL Server 2000 with SP3 Installed.
Thanks for the suggestion Andy.
Meher
"Andy Svendsen" <andymcdba1@.NOMORESPAM.yahoo.com> wrote in message
news:ubIupIW$DHA.212@.TK2MSFTNGP12.phx.gbl...
> Check under Management | jobs in Enterprise Manager, something this is
> running sp_dboption '<database','select into','true' and sp_dboption
> '<database>','trunc','true'
> --
> ****************************************
***************************
> Andy S.
> MCSE NT/2000, MCDBA SQL 7/2000
> andymcdba1@.NOMORESPAM.yahoo.com
> Please remove NOMORESPAM before replying.
> Always keep your antivirus and Microsoft software
> up to date with the latest definitions and product updates.
> Be suspicious of every email attachment, I will never send
> or post anything other than the text of a http:// link nor
> post the link directly to a file for downloading.
> This posting is provided "as is" with no warranties
> and confers no rights.
> ****************************************
***************************
> "Meher Malakapalli" <mmalakapalli@.cohesioninc.com> wrote in message
> news:OiCIFAW$DHA.2808@.TK2MSFTNGP10.phx.gbl...
> strange
rebooted
after
two
> flip
> the
databases?.
>

Flipping back to simple mode

Hi,
I have three user databases on my production server and I noticed a strange
behavior whenever the server gets rebooted. on two of the databases the
recovery model flips back from Full to simple when the server is rebooted
any time. On the third database the recover model stays as Full even after
the reboot. Even after I switch the recovery model back to full on the two
databases what I see is, as soon as the server gets rebooted, they both flip
back to simple.
I am confused as to why it is happening only with two databases and not the
third. Is there something that is set up wrongly on these two databases?.
Any insight is highly helpful.
Thanks
MeherCheck under Management | jobs in Enterprise Manager, something this is
running sp_dboption '<database','select into','true' and sp_dboption
'<database>','trunc','true'
--
*******************************************************************
Andy S.
MCSE NT/2000, MCDBA SQL 7/2000
andymcdba1@.NOMORESPAM.yahoo.com
Please remove NOMORESPAM before replying.
Always keep your antivirus and Microsoft software
up to date with the latest definitions and product updates.
Be suspicious of every email attachment, I will never send
or post anything other than the text of a http:// link nor
post the link directly to a file for downloading.
This posting is provided "as is" with no warranties
and confers no rights.
*******************************************************************
"Meher Malakapalli" <mmalakapalli@.cohesioninc.com> wrote in message
news:OiCIFAW$DHA.2808@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have three user databases on my production server and I noticed a
strange
> behavior whenever the server gets rebooted. on two of the databases the
> recovery model flips back from Full to simple when the server is rebooted
> any time. On the third database the recover model stays as Full even after
> the reboot. Even after I switch the recovery model back to full on the two
> databases what I see is, as soon as the server gets rebooted, they both
flip
> back to simple.
> I am confused as to why it is happening only with two databases and not
the
> third. Is there something that is set up wrongly on these two databases?.
> Any insight is highly helpful.
> Thanks
> Meher
>|||I checked them. No job is running with those options. I only have the
following options set to true on both the databases.
torn page detection
auto create statistics
auto update statistics
BTW I am running SQL Server 2000 with SP3 Installed.
Thanks for the suggestion Andy.
Meher
"Andy Svendsen" <andymcdba1@.NOMORESPAM.yahoo.com> wrote in message
news:ubIupIW$DHA.212@.TK2MSFTNGP12.phx.gbl...
> Check under Management | jobs in Enterprise Manager, something this is
> running sp_dboption '<database','select into','true' and sp_dboption
> '<database>','trunc','true'
> --
> *******************************************************************
> Andy S.
> MCSE NT/2000, MCDBA SQL 7/2000
> andymcdba1@.NOMORESPAM.yahoo.com
> Please remove NOMORESPAM before replying.
> Always keep your antivirus and Microsoft software
> up to date with the latest definitions and product updates.
> Be suspicious of every email attachment, I will never send
> or post anything other than the text of a http:// link nor
> post the link directly to a file for downloading.
> This posting is provided "as is" with no warranties
> and confers no rights.
> *******************************************************************
> "Meher Malakapalli" <mmalakapalli@.cohesioninc.com> wrote in message
> news:OiCIFAW$DHA.2808@.TK2MSFTNGP10.phx.gbl...
> > Hi,
> >
> > I have three user databases on my production server and I noticed a
> strange
> > behavior whenever the server gets rebooted. on two of the databases the
> > recovery model flips back from Full to simple when the server is
rebooted
> > any time. On the third database the recover model stays as Full even
after
> > the reboot. Even after I switch the recovery model back to full on the
two
> > databases what I see is, as soon as the server gets rebooted, they both
> flip
> > back to simple.
> >
> > I am confused as to why it is happening only with two databases and not
> the
> > third. Is there something that is set up wrongly on these two
databases?.
> > Any insight is highly helpful.
> >
> > Thanks
> >
> > Meher
> >
> >
>

FK Constraint question

I have two tables
GroupUsers and Alias

GroupUsers
-GroupId
-AliasId
-UserId

Alias
-AliasId
-UserId

When a user joins a Group he selects a default Alias for his group, so I have put a foreign key constraint from the groupusers table to the alias table.

Now, if an alias is delete, is there any way through a foreign key constraint to set the value to null because all I've seen is Cascade and No Action

A better solution I'm looking for would be a way to run a script that would use the value in the UserId column from the GroupUsers table to select the top alias from the Alias table with a corresponding UserId (if exists) and set it to that. I'm unsure as to if this solution is even possible.
SQL Server doesn't support the SQL standard to set a FK to NULL, you would need to implement this, or your other solution, using a DELETE trigger on the Alias table.|||We support the SET DEFAULT and SET NULL options in SQL Server 2005. So for now, you will have to implement the logic using SPs or trigger code.

Fixed

I fixed the problem, what I was doing was working. I was doing something stupid. I was not refreshing the user properties in the GUI.Great to hear. Thanks for reporting back.

FIX TO: Reporting server 2005 prompting for user name /ID and domain when accessing http:localho

If you get the error message: The attempt to connect to the report server failed. Check your connection information and that the report server is a compatible version.

If you then scan the recent logfiles in C:\Program Files\Microsoft SQL Server\MSSQL.1\Reporting Services\LogFiles and find the following exception: Microsoft.SqlServer.ReportingServices2005.RSConnection+MissingEndpointException: The attempt to connect to the report server failed. Check your connection information and that the report server is a compatible version. > System.Net.WebException: The request failed with HTTP status 404: .

it means that you have configured the /reportserver/ app to not be reachable via http://localhost/... - there are two options from here:

1) add the localhost hostheader to the web-site which hosts the /reportserver/ and make sure the app is then reachable via that path

2) edit the file C:\Program Files\Microsoft SQL Server\MSSQL.1\Reporting Services\ReportManager\RSWebApplication.config and add the full url to the ReportServer into the ReportServerUrl tag (no ending / nessesary) AND remove the value from ReportServerVirtualDirectory (otherwise you get nasty, unprecise exceptions). THEN you must recycle the app-pool used by the Report-Manager so that config file is being reloaded.

THANK YOU VERY MUCH FOR YOUR HELP.

|||

Thank You for the FIX TO: message - just what I needed to implement Reporting services on a secondary web site (with different host header)

Added the complete url and removed the entry from the ReportServerVirtualDirectory and everything worked - glad I found this - wish I would have found it before I reinstalled RS 2005 a couple times..

thanks tilfried for your contribution

John F

|||

Glad I could help :)

When one messes around a couple of hours trying to fix a problem, I think it's worth the extra 5 mins to post the solution!

|||Many thanks for your post, Tilfried Weissenberger. thanks|||

Hello all,

Could some one please explain the security piece of reporting services? What type of IDs do I need, do I need to use SPN accounts if yes how and where would I put/specify this SPN account. Basically I am trying to understand how to setup security when I install reporting services.

Thanks

MA

|||You would have to edit the rsreportserver.config file with the full fqdn of the report server as well.|||

Tilfried:

Thank you! Thank you! If you are ever in southwest Florida, look me up, and I'll buy you a beer! I mean it. And there aren't many Vangors in the phone book down here, so I'll be easy to find. Or google me.

This was the final piece of the puzzle that I needed to get SSRS 2005 working on my Win2K Server with SQL Server 2000.

In my case, I made the following changes to the RSWebApplication.config file:

From: <ReportServerUrl></ReportServerUrl>
To: <ReportServerUrl>http://www.mydomain.com/ReportServer</ReportServerUrl>

From: <ReportServerVirtualDirectory>ReportServer</ReportServerVirtualDirectory>
To: <ReportServerVirtualDirectory></ReportServerVirtualDirectory>

Thanks, again!

Van

Fiscal year search

Hi

I am trying to perform a search that will return records based on a
fiscal year search of the bill_Date. The user gives the year then I
want to search based on the fiscal year (July 1 - June 30) for the year
given. The table looks like this

Bill Table
id_Num bill_date bill_amount
23 7/1/2005 500.00
33 12/2/2005 600.00
44 3/3/2006 700.00

I have tried

Select Bill.id_num, Bill.bill_date, Bill.bill_amount
from Bill
where Bill.bill_date BETWEEN 7/1/ + @.year and 6/30/ + (@.year +1)

Plus a variety of other fruitless concoctions...but nothing seems to
work. Any help would be appreciated.Discovered answer on my own. Thanks anyway.

Twobridge wrote:

Quote:

Originally Posted by

Hi
>
I am trying to perform a search that will return records based on a
fiscal year search of the bill_Date. The user gives the year then I
want to search based on the fiscal year (July 1 - June 30) for the year
given. The table looks like this
>
Bill Table
id_Num bill_date bill_amount
23 7/1/2005 500.00
33 12/2/2005 600.00
44 3/3/2006 700.00
>
I have tried
>
Select Bill.id_num, Bill.bill_date, Bill.bill_amount
from Bill
where Bill.bill_date BETWEEN 7/1/ + @.year and 6/30/ + (@.year +1)
>
Plus a variety of other fruitless concoctions...but nothing seems to
work. Any help would be appreciated.

|||I've got a simlar problem - can you post your solution?
Dan

On Nov 29, 1:50 am, "Twobridge" <Twobri...@.gmail.comwrote:

Quote:

Originally Posted by

Discovered answer on my own. Thanks anyway.
>
>
>
Twobridge wrote:

Quote:

Originally Posted by

Hi

>

Quote:

Originally Posted by

I am trying to perform a search that will return records based on a
fiscal year search of the bill_Date. The user gives the year then I
want to search based on the fiscal year (July 1 - June 30) for the year
given. The table looks like this

>

Quote:

Originally Posted by

Bill Table
id_Num bill_date bill_amount
23 7/1/2005 500.00
33 12/2/2005 600.00
44 3/3/2006 700.00

>

Quote:

Originally Posted by

I have tried

>

Quote:

Originally Posted by

Select Bill.id_num, Bill.bill_date, Bill.bill_amount
from Bill
where Bill.bill_date BETWEEN 7/1/ + @.year and 6/30/ + (@.year +1)

>

Quote:

Originally Posted by

Plus a variety of other fruitless concoctions...but nothing seems to
work. Any help would be appreciated.- Hide quoted text -- Show quoted text -

|||On 29 Nov 2006 01:28:49 -0800, Dan wrote:

Quote:

Originally Posted by

>I've got a simlar problem - can you post your solution?
>Dan

Hi Dan,

The best way to solve this is to have a calendar table (see
http://sqlserver2000.databases.aspf...dar-table.html),
with FiscalYear as one of it's columns.

Second best is to build a date in string format, using a format that is
guaranteed to be unabiguous WRT the order of day and month: yyyymmdd.
For isntance, for a fiscal year that starts on july first:

DECLARE @.FiscalYear int
SET @.FiscalYear = 2006
SELECT something
FROM sometable
WHERE TheDate >= CAST(@.Year AS varchar) + '0701'
AND TheDate < CAST(@.Year + 1 AS varchar) + '0701'

You might want to read this as well:
http://www.karaszi.com/SQLServer/info_datetime.asp
--
Hugo Kornelis, SQL Server MVP|||Hi Hugo

Thanks - i'll take a look

Dan
On Nov 30, 9:13 pm, Hugo Kornelis
<h...@.perFact.REMOVETHIS.info.INVALIDwrote:

Quote:

Originally Posted by

On 29 Nov 2006 01:28:49 -0800, Dan wrote:
>

Quote:

Originally Posted by

I've got a simlar problem - can you post your solution?
DanHi Dan,

>
The best way to solve this is to have a calendar table (seehttp://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using...),
with FiscalYear as one of it's columns.
>
Second best is to build a date in string format, using a format that is
guaranteed to be unabiguous WRT the order of day and month: yyyymmdd.
For isntance, for a fiscal year that starts on july first:
>
DECLARE @.FiscalYear int
SET @.FiscalYear = 2006
SELECT something
FROM sometable
WHERE TheDate >= CAST(@.Year AS varchar) + '0701'
AND TheDate < CAST(@.Year + 1 AS varchar) + '0701'
>
You might want to read this as well:http://www.karaszi.com/SQLServer/info_datetime.asp
>
--
Hugo Kornelis, SQL Server MVP

first user liberates

When an user opens any form to alter some given, if or user to open the same
screen and to try to consult the same registration everything is joined
until that the first user liberates. However, the times an user is in the
office and the other in the branch, the second is without to know that is
happening and it ends up giving Crt+Alt_Dell and closes in the the
application horns.
I am discussing with the company that developed the system telling them that
that could not happen and that if two users try to open the same
registration the second it should receive at least a message informing that
has somebody using or you simply let him to open only reading.
They say that doesn't have way and that have to guide the users they not to
leave her the open screens without use. I found this answer a tremendous
lack of good sense. I am trying to look for a more coherent answer for the
problem, because I don't believe that a group of data relational works in
that way. I am convinced that the programming technique used by them and
that is wrong. Somebody could talk about the subject<< I am trying to look for a more coherent answer for the problem >>
And we are trying to look for a more coherent description of the problem...
"Frank Dulk" <fdulk@.bol.com.br> wrote in message
news:Onx0PaAtFHA.912@.TK2MSFTNGP11.phx.gbl...
> When an user opens any form to alter some given, if or user to open the
> same
> screen and to try to consult the same registration everything is joined
> until that the first user liberates. However, the times an user is in the
> office and the other in the branch, the second is without to know that is
> happening and it ends up giving Crt+Alt_Dell and closes in the the
> application horns.
> I am discussing with the company that developed the system telling them
> that
> that could not happen and that if two users try to open the same
> registration the second it should receive at least a message informing
> that
> has somebody using or you simply let him to open only reading.
> They say that doesn't have way and that have to guide the users they not
> to
> leave her the open screens without use. I found this answer a tremendous
> lack of good sense. I am trying to look for a more coherent answer for the
> problem, because I don't believe that a group of data relational works in
> that way. I am convinced that the programming technique used by them and
> that is wrong. Somebody could talk about the subject
>|||From what I understand you're saying that you'd like to implement some sort
of entity-locking in your database. There are many solutions to this problem
,
but since this is the 21st century after all, you might look into what
experts say about the 'smart client'. Google for that or look it up on MSDN.
Entity-locking involves a special functionality in a database that helps
prevent concurrency conflicts. Do not confuse it with the locking options
provided by the SQL server. Usually you can achieve entity-locking by keepin
g
record of all entities that are being processed at any given time by a user.
Example: in a document management system - if a document is checked out by a
n
editor, all the rows in all the tables in that system become "off-limits"
(read-only) to any other user. This feature is not natively supported by MS
SQL Server, but it can be added. Usually it's called something like
'check-out managmenet' or the like.
ML|||It appears that the company that developed the system used pessimistic
concurrency instead of optimistic concurrency. While pessimistic
concurrency is useful in some circumstances, it is generally not appropriate
for data entry screens. With pessimistic concurrency, the rows are locked
as they are read out, and the locks are held until the transaction commits.
With optimistic concurrency, the rows are read out, but locks are not held.
Instead, the contents of the rows are checked against the original contents,
and if they're different, then a collision occurred--meaning that another
transaction changed a row.
"Frank Dulk" <fdulk@.bol.com.br> wrote in message
news:Onx0PaAtFHA.912@.TK2MSFTNGP11.phx.gbl...
> When an user opens any form to alter some given, if or user to open the
same
> screen and to try to consult the same registration everything is joined
> until that the first user liberates. However, the times an user is in the
> office and the other in the branch, the second is without to know that is
> happening and it ends up giving Crt+Alt_Dell and closes in the the
> application horns.
> I am discussing with the company that developed the system telling them
that
> that could not happen and that if two users try to open the same
> registration the second it should receive at least a message informing
that
> has somebody using or you simply let him to open only reading.
> They say that doesn't have way and that have to guide the users they not
to
> leave her the open screens without use. I found this answer a tremendous
> lack of good sense. I am trying to look for a more coherent answer for the
> problem, because I don't believe that a group of data relational works in
> that way. I am convinced that the programming technique used by them and
> that is wrong. Somebody could talk about the subject
>|||Rather than utilizing record locking, you can query the current datetime
when the user opens a form. When the form is submitted, it can confirm if
form datetime > the row's LastUpdated datetime. If so, then update the row
and LastUpdated. In the even that the row has been updated by another user
while the form was open (form datetime < LastUpdated), then let the end
user's decide (from a high level workflow perspective) what should take
place. Whatever, they decide is valid; it's their system. As a DBA, your
responsibility is to insure that referential integrity of the database is
intact, but this specific type of situation should not concern that.
"Frank Dulk" <fdulk@.bol.com.br> wrote in message
news:Onx0PaAtFHA.912@.TK2MSFTNGP11.phx.gbl...
> When an user opens any form to alter some given, if or user to open the
> same
> screen and to try to consult the same registration everything is joined
> until that the first user liberates. However, the times an user is in the
> office and the other in the branch, the second is without to know that is
> happening and it ends up giving Crt+Alt_Dell and closes in the the
> application horns.
> I am discussing with the company that developed the system telling them
> that
> that could not happen and that if two users try to open the same
> registration the second it should receive at least a message informing
> that
> has somebody using or you simply let him to open only reading.
> They say that doesn't have way and that have to guide the users they not
> to
> leave her the open screens without use. I found this answer a tremendous
> lack of good sense. I am trying to look for a more coherent answer for the
> problem, because I don't believe that a group of data relational works in
> that way. I am convinced that the programming technique used by them and
> that is wrong. Somebody could talk about the subject
>

first user liberates

When an user opens any form to alter some given, if or user to open the same
screen and to try to consult the same registration everything is joined
until that the first user liberates. However, the times an user is in the
office and the other in the branch, the second is without to know that is
happening and it ends up giving Crt+Alt_Dell and closes in the the
application horns.
I am discussing with the company that developed the system telling them that
that could not happen and that if two users try to open the same
registration the second it should receive at least a message informing that
has somebody using or you simply let him to open only reading.
They say that doesn't have way and that have to guide the users they not to
leave her the open screens without use. I found this answer a tremendous
lack of good sense. I am trying to look for a more coherent answer for the
problem, because I don't believe that a group of data relational works in
that way. I am convinced that the programming technique used by them and
that is wrong. Somebody could talk about the subject<< I am trying to look for a more coherent answer for the problem >>
And we are trying to look for a more coherent description of the problem...
"Frank Dulk" <fdulk@.bol.com.br> wrote in message
news:Onx0PaAtFHA.912@.TK2MSFTNGP11.phx.gbl...
> When an user opens any form to alter some given, if or user to open the
> same
> screen and to try to consult the same registration everything is joined
> until that the first user liberates. However, the times an user is in the
> office and the other in the branch, the second is without to know that is
> happening and it ends up giving Crt+Alt_Dell and closes in the the
> application horns.
> I am discussing with the company that developed the system telling them
> that
> that could not happen and that if two users try to open the same
> registration the second it should receive at least a message informing
> that
> has somebody using or you simply let him to open only reading.
> They say that doesn't have way and that have to guide the users they not
> to
> leave her the open screens without use. I found this answer a tremendous
> lack of good sense. I am trying to look for a more coherent answer for the
> problem, because I don't believe that a group of data relational works in
> that way. I am convinced that the programming technique used by them and
> that is wrong. Somebody could talk about the subject
>|||From what I understand you're saying that you'd like to implement some sort
of entity-locking in your database. There are many solutions to this problem
,
but since this is the 21st century after all, you might look into what
experts say about the 'smart client'. Google for that or look it up on MSDN.
Entity-locking involves a special functionality in a database that helps
prevent concurrency conflicts. Do not confuse it with the locking options
provided by the SQL server. Usually you can achieve entity-locking by keepin
g
record of all entities that are being processed at any given time by a user.
Example: in a document management system - if a document is checked out by a
n
editor, all the rows in all the tables in that system become "off-limits"
(read-only) to any other user. This feature is not natively supported by MS
SQL Server, but it can be added. Usually it's called something like
'check-out managmenet' or the like.
ML|||It appears that the company that developed the system used pessimistic
concurrency instead of optimistic concurrency. While pessimistic
concurrency is useful in some circumstances, it is generally not appropriate
for data entry screens. With pessimistic concurrency, the rows are locked
as they are read out, and the locks are held until the transaction commits.
With optimistic concurrency, the rows are read out, but locks are not held.
Instead, the contents of the rows are checked against the original contents,
and if they're different, then a collision occurred--meaning that another
transaction changed a row.
"Frank Dulk" <fdulk@.bol.com.br> wrote in message
news:Onx0PaAtFHA.912@.TK2MSFTNGP11.phx.gbl...
> When an user opens any form to alter some given, if or user to open the
same
> screen and to try to consult the same registration everything is joined
> until that the first user liberates. However, the times an user is in the
> office and the other in the branch, the second is without to know that is
> happening and it ends up giving Crt+Alt_Dell and closes in the the
> application horns.
> I am discussing with the company that developed the system telling them
that
> that could not happen and that if two users try to open the same
> registration the second it should receive at least a message informing
that
> has somebody using or you simply let him to open only reading.
> They say that doesn't have way and that have to guide the users they not
to
> leave her the open screens without use. I found this answer a tremendous
> lack of good sense. I am trying to look for a more coherent answer for the
> problem, because I don't believe that a group of data relational works in
> that way. I am convinced that the programming technique used by them and
> that is wrong. Somebody could talk about the subject
>|||Rather than utilizing record locking, you can query the current datetime
when the user opens a form. When the form is submitted, it can confirm if
form datetime > the row's LastUpdated datetime. If so, then update the row
and LastUpdated. In the even that the row has been updated by another user
while the form was open (form datetime < LastUpdated), then let the end
user's decide (from a high level workflow perspective) what should take
place. Whatever, they decide is valid; it's their system. As a DBA, your
responsibility is to insure that referential integrity of the database is
intact, but this specific type of situation should not concern that.
"Frank Dulk" <fdulk@.bol.com.br> wrote in message
news:Onx0PaAtFHA.912@.TK2MSFTNGP11.phx.gbl...
> When an user opens any form to alter some given, if or user to open the
> same
> screen and to try to consult the same registration everything is joined
> until that the first user liberates. However, the times an user is in the
> office and the other in the branch, the second is without to know that is
> happening and it ends up giving Crt+Alt_Dell and closes in the the
> application horns.
> I am discussing with the company that developed the system telling them
> that
> that could not happen and that if two users try to open the same
> registration the second it should receive at least a message informing
> that
> has somebody using or you simply let him to open only reading.
> They say that doesn't have way and that have to guide the users they not
> to
> leave her the open screens without use. I found this answer a tremendous
> lack of good sense. I am trying to look for a more coherent answer for the
> problem, because I don't believe that a group of data relational works in
> that way. I am convinced that the programming technique used by them and
> that is wrong. Somebody could talk about the subject
>

first user liberates

When an user opens any form to alter some given, if or user to open the same
screen and to try to consult the same registration everything is joined
until that the first user liberates. However, the times an user is in the
office and the other in the branch, the second is without to know that is
happening and it ends up giving Crt+Alt_Dell and closes in the the
application horns.
I am discussing with the company that developed the system telling them that
that could not happen and that if two users try to open the same
registration the second it should receive at least a message informing that
has somebody using or you simply let him to open only reading.
They say that doesn't have way and that have to guide the users they not to
leave her the open screens without use. I found this answer a tremendous
lack of good sense. I am trying to look for a more coherent answer for the
problem, because I don't believe that a group of data relational works in
that way. I am convinced that the programming technique used by them and
that is wrong. Somebody could talk about the subject
<< I am trying to look for a more coherent answer for the problem >>
And we are trying to look for a more coherent description of the problem...
"Frank Dulk" <fdulk@.bol.com.br> wrote in message
news:Onx0PaAtFHA.912@.TK2MSFTNGP11.phx.gbl...
> When an user opens any form to alter some given, if or user to open the
> same
> screen and to try to consult the same registration everything is joined
> until that the first user liberates. However, the times an user is in the
> office and the other in the branch, the second is without to know that is
> happening and it ends up giving Crt+Alt_Dell and closes in the the
> application horns.
> I am discussing with the company that developed the system telling them
> that
> that could not happen and that if two users try to open the same
> registration the second it should receive at least a message informing
> that
> has somebody using or you simply let him to open only reading.
> They say that doesn't have way and that have to guide the users they not
> to
> leave her the open screens without use. I found this answer a tremendous
> lack of good sense. I am trying to look for a more coherent answer for the
> problem, because I don't believe that a group of data relational works in
> that way. I am convinced that the programming technique used by them and
> that is wrong. Somebody could talk about the subject
>
|||From what I understand you're saying that you'd like to implement some sort
of entity-locking in your database. There are many solutions to this problem,
but since this is the 21st century after all, you might look into what
experts say about the 'smart client'. Google for that or look it up on MSDN.
Entity-locking involves a special functionality in a database that helps
prevent concurrency conflicts. Do not confuse it with the locking options
provided by the SQL server. Usually you can achieve entity-locking by keeping
record of all entities that are being processed at any given time by a user.
Example: in a document management system - if a document is checked out by an
editor, all the rows in all the tables in that system become "off-limits"
(read-only) to any other user. This feature is not natively supported by MS
SQL Server, but it can be added. Usually it's called something like
'check-out managmenet' or the like.
ML
|||It appears that the company that developed the system used pessimistic
concurrency instead of optimistic concurrency. While pessimistic
concurrency is useful in some circumstances, it is generally not appropriate
for data entry screens. With pessimistic concurrency, the rows are locked
as they are read out, and the locks are held until the transaction commits.
With optimistic concurrency, the rows are read out, but locks are not held.
Instead, the contents of the rows are checked against the original contents,
and if they're different, then a collision occurred--meaning that another
transaction changed a row.
"Frank Dulk" <fdulk@.bol.com.br> wrote in message
news:Onx0PaAtFHA.912@.TK2MSFTNGP11.phx.gbl...
> When an user opens any form to alter some given, if or user to open the
same
> screen and to try to consult the same registration everything is joined
> until that the first user liberates. However, the times an user is in the
> office and the other in the branch, the second is without to know that is
> happening and it ends up giving Crt+Alt_Dell and closes in the the
> application horns.
> I am discussing with the company that developed the system telling them
that
> that could not happen and that if two users try to open the same
> registration the second it should receive at least a message informing
that
> has somebody using or you simply let him to open only reading.
> They say that doesn't have way and that have to guide the users they not
to
> leave her the open screens without use. I found this answer a tremendous
> lack of good sense. I am trying to look for a more coherent answer for the
> problem, because I don't believe that a group of data relational works in
> that way. I am convinced that the programming technique used by them and
> that is wrong. Somebody could talk about the subject
>
|||Rather than utilizing record locking, you can query the current datetime
when the user opens a form. When the form is submitted, it can confirm if
form datetime > the row's LastUpdated datetime. If so, then update the row
and LastUpdated. In the even that the row has been updated by another user
while the form was open (form datetime < LastUpdated), then let the end
user's decide (from a high level workflow perspective) what should take
place. Whatever, they decide is valid; it's their system. As a DBA, your
responsibility is to insure that referential integrity of the database is
intact, but this specific type of situation should not concern that.
"Frank Dulk" <fdulk@.bol.com.br> wrote in message
news:Onx0PaAtFHA.912@.TK2MSFTNGP11.phx.gbl...
> When an user opens any form to alter some given, if or user to open the
> same
> screen and to try to consult the same registration everything is joined
> until that the first user liberates. However, the times an user is in the
> office and the other in the branch, the second is without to know that is
> happening and it ends up giving Crt+Alt_Dell and closes in the the
> application horns.
> I am discussing with the company that developed the system telling them
> that
> that could not happen and that if two users try to open the same
> registration the second it should receive at least a message informing
> that
> has somebody using or you simply let him to open only reading.
> They say that doesn't have way and that have to guide the users they not
> to
> leave her the open screens without use. I found this answer a tremendous
> lack of good sense. I am trying to look for a more coherent answer for the
> problem, because I don't believe that a group of data relational works in
> that way. I am convinced that the programming technique used by them and
> that is wrong. Somebody could talk about the subject
>

first user liberates

When an user opens any form to alter some given, if or user to open the same
screen and to try to consult the same registration everything is joined
until that the first user liberates. However, the times an user is in the
office and the other in the branch, the second is without to know that is
happening and it ends up giving Crt+Alt_Dell and closes in the the
application horns.
I am discussing with the company that developed the system telling them that
that could not happen and that if two users try to open the same
registration the second it should receive at least a message informing that
has somebody using or you simply let him to open only reading.
They say that doesn't have way and that have to guide the users they not to
leave her the open screens without use. I found this answer a tremendous
lack of good sense. I am trying to look for a more coherent answer for the
problem, because I don't believe that a group of data relational works in
that way. I am convinced that the programming technique used by them and
that is wrong. Somebody could talk about the subject<< I am trying to look for a more coherent answer for the problem >>
And we are trying to look for a more coherent description of the problem...
"Frank Dulk" <fdulk@.bol.com.br> wrote in message
news:Onx0PaAtFHA.912@.TK2MSFTNGP11.phx.gbl...
> When an user opens any form to alter some given, if or user to open the
> same
> screen and to try to consult the same registration everything is joined
> until that the first user liberates. However, the times an user is in the
> office and the other in the branch, the second is without to know that is
> happening and it ends up giving Crt+Alt_Dell and closes in the the
> application horns.
> I am discussing with the company that developed the system telling them
> that
> that could not happen and that if two users try to open the same
> registration the second it should receive at least a message informing
> that
> has somebody using or you simply let him to open only reading.
> They say that doesn't have way and that have to guide the users they not
> to
> leave her the open screens without use. I found this answer a tremendous
> lack of good sense. I am trying to look for a more coherent answer for the
> problem, because I don't believe that a group of data relational works in
> that way. I am convinced that the programming technique used by them and
> that is wrong. Somebody could talk about the subject
>|||From what I understand you're saying that you'd like to implement some sort
of entity-locking in your database. There are many solutions to this problem,
but since this is the 21st century after all, you might look into what
experts say about the 'smart client'. Google for that or look it up on MSDN.
Entity-locking involves a special functionality in a database that helps
prevent concurrency conflicts. Do not confuse it with the locking options
provided by the SQL server. Usually you can achieve entity-locking by keeping
record of all entities that are being processed at any given time by a user.
Example: in a document management system - if a document is checked out by an
editor, all the rows in all the tables in that system become "off-limits"
(read-only) to any other user. This feature is not natively supported by MS
SQL Server, but it can be added. Usually it's called something like
'check-out managmenet' or the like.
ML|||It appears that the company that developed the system used pessimistic
concurrency instead of optimistic concurrency. While pessimistic
concurrency is useful in some circumstances, it is generally not appropriate
for data entry screens. With pessimistic concurrency, the rows are locked
as they are read out, and the locks are held until the transaction commits.
With optimistic concurrency, the rows are read out, but locks are not held.
Instead, the contents of the rows are checked against the original contents,
and if they're different, then a collision occurred--meaning that another
transaction changed a row.
"Frank Dulk" <fdulk@.bol.com.br> wrote in message
news:Onx0PaAtFHA.912@.TK2MSFTNGP11.phx.gbl...
> When an user opens any form to alter some given, if or user to open the
same
> screen and to try to consult the same registration everything is joined
> until that the first user liberates. However, the times an user is in the
> office and the other in the branch, the second is without to know that is
> happening and it ends up giving Crt+Alt_Dell and closes in the the
> application horns.
> I am discussing with the company that developed the system telling them
that
> that could not happen and that if two users try to open the same
> registration the second it should receive at least a message informing
that
> has somebody using or you simply let him to open only reading.
> They say that doesn't have way and that have to guide the users they not
to
> leave her the open screens without use. I found this answer a tremendous
> lack of good sense. I am trying to look for a more coherent answer for the
> problem, because I don't believe that a group of data relational works in
> that way. I am convinced that the programming technique used by them and
> that is wrong. Somebody could talk about the subject
>|||Rather than utilizing record locking, you can query the current datetime
when the user opens a form. When the form is submitted, it can confirm if
form datetime > the row's LastUpdated datetime. If so, then update the row
and LastUpdated. In the even that the row has been updated by another user
while the form was open (form datetime < LastUpdated), then let the end
user's decide (from a high level workflow perspective) what should take
place. Whatever, they decide is valid; it's their system. As a DBA, your
responsibility is to insure that referential integrity of the database is
intact, but this specific type of situation should not concern that.
"Frank Dulk" <fdulk@.bol.com.br> wrote in message
news:Onx0PaAtFHA.912@.TK2MSFTNGP11.phx.gbl...
> When an user opens any form to alter some given, if or user to open the
> same
> screen and to try to consult the same registration everything is joined
> until that the first user liberates. However, the times an user is in the
> office and the other in the branch, the second is without to know that is
> happening and it ends up giving Crt+Alt_Dell and closes in the the
> application horns.
> I am discussing with the company that developed the system telling them
> that
> that could not happen and that if two users try to open the same
> registration the second it should receive at least a message informing
> that
> has somebody using or you simply let him to open only reading.
> They say that doesn't have way and that have to guide the users they not
> to
> leave her the open screens without use. I found this answer a tremendous
> lack of good sense. I am trying to look for a more coherent answer for the
> problem, because I don't believe that a group of data relational works in
> that way. I am convinced that the programming technique used by them and
> that is wrong. Somebody could talk about the subject
>

First time user that can't find answer. Need online website.

HI! Thank you for even looking at this question.

I need to get a website up and running for a small group of people. I've had SQL Server for a while, but didn't have any use for it. It came with Visual Studio 2005, which gave me a little know how on scripting. Now, I have Visual Web Developer and what I need to make a web page. I just need to know how to configure all my settings to allow my website to be viewed online by anyone with an internet connection. I'm a person that understands detailed or simple directions, but I can not seem to find directions online. Now, I know what your thinking, but I've never attemped anything like this before. So, please, HELP! I need and want to learn as much as possible.

Thank you, tecfreak213

P.S. also, how do I change or get a web address? Thanks.

Hi,

Welcome to Forum.

You will require a Web Server (Internet Information Server ), A dedicated Internet connection, SQL Server, IP Address to bind to web server, Domain Name

Setup SQL Server with Database

Setup IIS Server (with Internet Connection and dedicated IP address bind to that, refer below article on how to setup IIS)

http://www.no-ip.com/support/guides/web_servers/setting_up_iis.html

http://www.webwizguide.com/asp/tutorials/installing_iis_winXP_pro.asp

http://www.webmasterstop.com/122.html

Create Directory on the IIS Server and deploy you web application in it

Bind you Directory to IIS

HTH

Hemantgiri S. Goswami

First time user

I was told that Reporting Services is included in SQL server 2000 but
I have not been able to locate it if it is. Do I have to load it
separately when I load SQL server?SQL Server Reporting Services is licensed with SQL Server 2000, but it is
separate software. If you have an MSDN subscription it is on the September
2004 DVD (Disc 2432.6, it's a red disc). Otherwise, you will need to contact
the vendor from whom you purchased SQL Server 2000 to obtain the media. You
will also want to download and install Service Pack 1 which is available at:
http://www.microsoft.com/sql/reporting/downloads/default.asp
"George" wrote:
> I was told that Reporting Services is included in SQL server 2000 but
> I have not been able to locate it if it is. Do I have to load it
> separately when I load SQL server?
>|||On 15 Nov 2004 11:41:48 -0800, george.hunter@.conagrafoods.com (George)
wrote:
>I was told that Reporting Services is included in SQL server 2000 but
>I have not been able to locate it if it is. Do I have to load it
>separately when I load SQL server?
George,
You can download a trial version from
http://www.microsoft.com/sql/reporting/downloads/default.asp. (Scroll
down a little)
As far I am aware, the full version is available only on CD or on MSDN
(not sure what levels).
Andrew Watt
MVP - InfoPath|||Dear George and NG,
I am in the same boat you are in. In addition, I could not read this
NewsGroup using Outlook Express and netnews.attbi.com. Perhaps Comcast has
put it somewhere else. I am using IE and the MSDN subscription to reach this
message. The real purpose of this post is to see if this approach to
newsgroups works. I am going to try to set up RS this morning from my MSDN
DVDs. In order to use RS I am going to have to transfer my application
development from MSDE to full blown SQL Server 2000.
Thanks for your question and the replies,
Bob
--
Robert Schuldenfrei
S. I. Inc.
bob@.s-i-inc.com
"George" wrote:
> I was told that Reporting Services is included in SQL server 2000 but
> I have not been able to locate it if it is. Do I have to load it
> separately when I load SQL server?
>

First sp after re-start fails because of User-defined type

Hi,
I'm having a problem with SQL Server 7. If I restart the
server, and the first stroed proc I run contains a
reference to a user defined data type then the query fails
because the data type is not recognised. (In this case I'm
creating a temporary table in the stored proc, where one
column in the table uses the user defined data type)
A workaround is to run another stored proc first, and then
everything is fine.
Has anyone got a more elegant solution or reason why this
would happen?
Thanks,
Neil
Seems to be some issue with a user defined datatype not existing in tempdb. Hard to ell without a
repro. Perhaps it is as easy as creating that dt in the model database so you know it will exist in
tempdb?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Neil" <anonymous@.discussions.microsoft.com> wrote in message
news:49e801c4a0b1$d37e6cb0$a301280a@.phx.gbl...
> Hi,
> I'm having a problem with SQL Server 7. If I restart the
> server, and the first stroed proc I run contains a
> reference to a user defined data type then the query fails
> because the data type is not recognised. (In this case I'm
> creating a temporary table in the stored proc, where one
> column in the table uses the user defined data type)
> A workaround is to run another stored proc first, and then
> everything is fine.
> Has anyone got a more elegant solution or reason why this
> would happen?
> Thanks,
> Neil
|||I don't think so - In the SP,before I create the temporary
table, I copy all the user data types to the tempdb, but
this isn't being run. In fact, I put some logging calls in
the SP and ran it from Query analyzer, and nothing is run.
It seems that SQL Server is doing some sort of
compilation/validation on the SP and failing it before any
attempt is made to run it. Running some other query seems
to make it 'aware' of the user defined data types, and
then it is happy with the original SP.
Thanks anyway,
Neil

>--Original Message--
>Seems to be some issue with a user defined datatype not
existing in tempdb. Hard to ell without a
>repro. Perhaps it is as easy as creating that dt in the
model database so you know it will exist in
>tempdb?
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Neil" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:49e801c4a0b1$d37e6cb0$a301280a@.phx.gbl...
fails[vbcol=seagreen]
I'm[vbcol=seagreen]
then[vbcol=seagreen]
this
>
>.
>
|||It seems I spoke too soon! The following code reproduces
the problem:
CREATE PROCEDURE MyUDTTest AS
BEGIN
declare @.stmt as nvarchar(255)
set @.stmt = 'USE tempdb' + CHAR(13) + 'exec
sp_addtype ''udtBuySell'' , ''char(1)'''
exec sp_executesql @.stmt
CREATE TABLE #MyTable (MyColumn udtBuySell)
END
trying to run this SP after a stop/start gives the
following error:
Server: Msg 2715, Level 16, State 7, Procedure MyUDTTest,
Line 10
Column or parameter #1: Cannot find data type udtBuySell.
in tempdb create the udtBuySell data type. Then drop it.
Now the sp will run.
Any thoughts?
Neil

>--Original Message--
>Seems to be some issue with a user defined datatype not
existing in tempdb. Hard to ell without a
>repro. Perhaps it is as easy as creating that dt in the
model database so you know it will exist in
>tempdb?
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Neil" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:49e801c4a0b1$d37e6cb0$a301280a@.phx.gbl...
fails[vbcol=seagreen]
I'm[vbcol=seagreen]
then[vbcol=seagreen]
this
>
>.
>
|||This is a scoping problem. Since the UDT is created in dynamic SQL, the datatype doesn't exist when
SQL Server parses the code in the procedure. Create the datatype in model instead.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Neil" <anonymous@.discussions.microsoft.com> wrote in message
news:244901c4a176$b0fdea60$a601280a@.phx.gbl...[vbcol=seagreen]
> It seems I spoke too soon! The following code reproduces
> the problem:
> CREATE PROCEDURE MyUDTTest AS
> BEGIN
> declare @.stmt as nvarchar(255)
> set @.stmt = 'USE tempdb' + CHAR(13) + 'exec
> sp_addtype ''udtBuySell'' , ''char(1)'''
> exec sp_executesql @.stmt
> CREATE TABLE #MyTable (MyColumn udtBuySell)
> END
> trying to run this SP after a stop/start gives the
> following error:
> Server: Msg 2715, Level 16, State 7, Procedure MyUDTTest,
> Line 10
> Column or parameter #1: Cannot find data type udtBuySell.
> in tempdb create the udtBuySell data type. Then drop it.
> Now the sp will run.
> Any thoughts?
> Neil
>
> existing in tempdb. Hard to ell without a
> model database so you know it will exist in
> message
> fails
> I'm
> then
> this
|||Tibor,
Thanks very much for your help. Creating the data type in
model has been ruled out from above (!) as it would mean
remembering to change it in two places (in the working db
and in model).
I'm curious as to why the procedure should work after the
datatype has been created then dropped in tempdb. I can
see what you are saying about scoping, but why would it be
OK after creating/dropping the type in tempdb? It still
doesn't exist in tempdb after all...
Our current plan is to run a job when SQLAgent starts to
copy the user types from our working db to tempdb (so now
we have to remember to restart the server after a change
to the user types - where will it all end!)
Thanks again for your interest in this problem,
Neil

>--Original Message--
>This is a scoping problem. Since the UDT is created in
dynamic SQL, the datatype doesn't exist when
>SQL Server parses the code in the procedure. Create the
datatype in model instead.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Neil" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:244901c4a176$b0fdea60$a601280a@.phx.gbl...
MyUDTTest,[vbcol=seagreen]
udtBuySell.[vbcol=seagreen]
the[vbcol=seagreen]
case[vbcol=seagreen]
one
>
>.
>

First sp after re-start fails because of User-defined type

Hi,
I'm having a problem with SQL Server 7. If I restart the
server, and the first stroed proc I run contains a
reference to a user defined data type then the query fails
because the data type is not recognised. (In this case I'm
creating a temporary table in the stored proc, where one
column in the table uses the user defined data type)
A workaround is to run another stored proc first, and then
everything is fine.
Has anyone got a more elegant solution or reason why this
would happen?
Thanks,
NeilSeems to be some issue with a user defined datatype not existing in tempdb. Hard to ell without a
repro. Perhaps it is as easy as creating that dt in the model database so you know it will exist in
tempdb?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Neil" <anonymous@.discussions.microsoft.com> wrote in message
news:49e801c4a0b1$d37e6cb0$a301280a@.phx.gbl...
> Hi,
> I'm having a problem with SQL Server 7. If I restart the
> server, and the first stroed proc I run contains a
> reference to a user defined data type then the query fails
> because the data type is not recognised. (In this case I'm
> creating a temporary table in the stored proc, where one
> column in the table uses the user defined data type)
> A workaround is to run another stored proc first, and then
> everything is fine.
> Has anyone got a more elegant solution or reason why this
> would happen?
> Thanks,
> Neil|||I don't think so - In the SP,before I create the temporary
table, I copy all the user data types to the tempdb, but
this isn't being run. In fact, I put some logging calls in
the SP and ran it from Query analyzer, and nothing is run.
It seems that SQL Server is doing some sort of
compilation/validation on the SP and failing it before any
attempt is made to run it. Running some other query seems
to make it 'aware' of the user defined data types, and
then it is happy with the original SP.
Thanks anyway,
Neil
>--Original Message--
>Seems to be some issue with a user defined datatype not
existing in tempdb. Hard to ell without a
>repro. Perhaps it is as easy as creating that dt in the
model database so you know it will exist in
>tempdb?
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Neil" <anonymous@.discussions.microsoft.com> wrote in
message
>news:49e801c4a0b1$d37e6cb0$a301280a@.phx.gbl...
>> Hi,
>> I'm having a problem with SQL Server 7. If I restart the
>> server, and the first stroed proc I run contains a
>> reference to a user defined data type then the query
fails
>> because the data type is not recognised. (In this case
I'm
>> creating a temporary table in the stored proc, where one
>> column in the table uses the user defined data type)
>> A workaround is to run another stored proc first, and
then
>> everything is fine.
>> Has anyone got a more elegant solution or reason why
this
>> would happen?
>> Thanks,
>> Neil
>
>.
>|||This is a scoping problem. Since the UDT is created in dynamic SQL, the datatype doesn't exist when
SQL Server parses the code in the procedure. Create the datatype in model instead.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Neil" <anonymous@.discussions.microsoft.com> wrote in message
news:244901c4a176$b0fdea60$a601280a@.phx.gbl...
> It seems I spoke too soon! The following code reproduces
> the problem:
> CREATE PROCEDURE MyUDTTest AS
> BEGIN
> declare @.stmt as nvarchar(255)
> set @.stmt = 'USE tempdb' + CHAR(13) + 'exec
> sp_addtype ''udtBuySell'' , ''char(1)'''
> exec sp_executesql @.stmt
> CREATE TABLE #MyTable (MyColumn udtBuySell)
> END
> trying to run this SP after a stop/start gives the
> following error:
> Server: Msg 2715, Level 16, State 7, Procedure MyUDTTest,
> Line 10
> Column or parameter #1: Cannot find data type udtBuySell.
> in tempdb create the udtBuySell data type. Then drop it.
> Now the sp will run.
> Any thoughts?
> Neil
>
> >--Original Message--
> >Seems to be some issue with a user defined datatype not
> existing in tempdb. Hard to ell without a
> >repro. Perhaps it is as easy as creating that dt in the
> model database so you know it will exist in
> >tempdb?
> >
> >--
> >Tibor Karaszi, SQL Server MVP
> >http://www.karaszi.com/sqlserver/default.asp
> >http://www.solidqualitylearning.com/
> >
> >
> >"Neil" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:49e801c4a0b1$d37e6cb0$a301280a@.phx.gbl...
> >> Hi,
> >>
> >> I'm having a problem with SQL Server 7. If I restart the
> >> server, and the first stroed proc I run contains a
> >> reference to a user defined data type then the query
> fails
> >> because the data type is not recognised. (In this case
> I'm
> >> creating a temporary table in the stored proc, where one
> >> column in the table uses the user defined data type)
> >>
> >> A workaround is to run another stored proc first, and
> then
> >> everything is fine.
> >>
> >> Has anyone got a more elegant solution or reason why
> this
> >> would happen?
> >>
> >> Thanks,
> >>
> >> Neil
> >
> >
> >.
> >|||Tibor,
Thanks very much for your help. Creating the data type in
model has been ruled out from above (!) as it would mean
remembering to change it in two places (in the working db
and in model).
I'm curious as to why the procedure should work after the
datatype has been created then dropped in tempdb. I can
see what you are saying about scoping, but why would it be
OK after creating/dropping the type in tempdb? It still
doesn't exist in tempdb after all...
Our current plan is to run a job when SQLAgent starts to
copy the user types from our working db to tempdb (so now
we have to remember to restart the server after a change
to the user types - where will it all end!)
Thanks again for your interest in this problem,
Neil
>--Original Message--
>This is a scoping problem. Since the UDT is created in
dynamic SQL, the datatype doesn't exist when
>SQL Server parses the code in the procedure. Create the
datatype in model instead.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Neil" <anonymous@.discussions.microsoft.com> wrote in
message
>news:244901c4a176$b0fdea60$a601280a@.phx.gbl...
>> It seems I spoke too soon! The following code reproduces
>> the problem:
>> CREATE PROCEDURE MyUDTTest AS
>> BEGIN
>> declare @.stmt as nvarchar(255)
>> set @.stmt = 'USE tempdb' + CHAR(13) + 'exec
>> sp_addtype ''udtBuySell'' , ''char(1)'''
>> exec sp_executesql @.stmt
>> CREATE TABLE #MyTable (MyColumn udtBuySell)
>> END
>> trying to run this SP after a stop/start gives the
>> following error:
>> Server: Msg 2715, Level 16, State 7, Procedure
MyUDTTest,
>> Line 10
>> Column or parameter #1: Cannot find data type
udtBuySell.
>> in tempdb create the udtBuySell data type. Then drop it.
>> Now the sp will run.
>> Any thoughts?
>> Neil
>>
>> >--Original Message--
>> >Seems to be some issue with a user defined datatype not
>> existing in tempdb. Hard to ell without a
>> >repro. Perhaps it is as easy as creating that dt in the
>> model database so you know it will exist in
>> >tempdb?
>> >
>> >--
>> >Tibor Karaszi, SQL Server MVP
>> >http://www.karaszi.com/sqlserver/default.asp
>> >http://www.solidqualitylearning.com/
>> >
>> >
>> >"Neil" <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:49e801c4a0b1$d37e6cb0$a301280a@.phx.gbl...
>> >> Hi,
>> >>
>> >> I'm having a problem with SQL Server 7. If I restart
the
>> >> server, and the first stroed proc I run contains a
>> >> reference to a user defined data type then the query
>> fails
>> >> because the data type is not recognised. (In this
case
>> I'm
>> >> creating a temporary table in the stored proc, where
one
>> >> column in the table uses the user defined data type)
>> >>
>> >> A workaround is to run another stored proc first, and
>> then
>> >> everything is fine.
>> >>
>> >> Has anyone got a more elegant solution or reason why
>> this
>> >> would happen?
>> >>
>> >> Thanks,
>> >>
>> >> Neil
>> >
>> >
>> >.
>> >
>
>.
>

First Hit to SQL Reporting Services - Slow Response Time

The first time each day that a user accesses SQL Reporting Services 2005, it
takes up to 3 minutes to load the SQL Reporting web interface. After the
first hit, it loads much faster. Is there a way to speed up the intial load?This is an IIS issue. After a certain time it unloads RS. Note that as far
as IIS is concerned RS is just another asp.net application.
Go to IIS Manager, under Applocation Pools node, right click
"DefaultAppPool" in which the Reporting Server work process is running,
select properties. On "Performace" tag, you will see, by default, the app
pool will shut down if > being idle for 20 min. You can extend this time to
8x60min 480min, so that the app pool will not shut down for a regular
working day. However, the first report reader of the day, will hit the
delay. You may schedule a dummy report at beginning of a work day for this.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Chili86@.discussions.microsoft.com"
<Chili86@.discussions.microsoft.com@.discussions.microsoft.com> wrote in
message news:26DF80C2-7E46-4A7F-AEA1-57E36697D155@.microsoft.com...
> The first time each day that a user accesses SQL Reporting Services 2005,
> it
> takes up to 3 minutes to load the SQL Reporting web interface. After the
> first hit, it loads much faster. Is there a way to speed up the intial
> load?

First and Last day of week

Hi All
I have a report that brings back data based on the week a user selects.
The user will select a week number (e.g. week 1) then the report will automatically generate the first date (Monday) and last date of that week (Sunday), based on a whole week for the current year.
So, user selects week 3 for this year. The report generates the first date of the week: 16/01/2006 and last date of the week: 22/01/2006.

Note: I’m in the UK, so I am using the UK date format.

Try this:

Code/

Declare @.DOW As Char(10), @.weekdate Datetime, @.Monday DateTime, @.Sunday DateTime,

@.Working As Int, @.WeekNo Int

-- Find first day of year

Set @.WeekDate = (select DATEADD(yy, DATEDIFF(yy,0,getdate()), 0))

--Find a date in this week - any date

Set @.Working = @.WeekNo * 7

Select @.WeekDate = DateAdd(dd,@.Working,@.WeekDate)

Get day of week and subtract number of days to get the Monday

Set @.DOW = DateName(dw,@.Weekdate)

Set @.Monday = @.Weekdate

Set @.Monday = Case @.DOW

When 'Sunday' Then DateAdd(dd,-6,@.WeekDate)

When 'Saturday' Then DateAdd(dd,-5,@.WeekDate)

When 'Friday' Then DateAdd(dd,-4,@.WeekDate)

When 'Thursday' Then DateAdd(dd,-3,@.WeekDate)

When 'Wednesday' Then DateAdd(dd,-2,@.WeekDate)

When 'Tuesday' Then DateAdd(dd,-1,@.WeekDate)

Else @.Monday

End

Set @.Sunday = DateAdd(dd,6,@.Monday)

Select @.Monday, @.Sunday

/Code

You feed in @.WeekNo

Firing User defined Function from Select, without using function name, is this possib

Hi all, Can anyone tell me if it is possible to fire a user defined
function in SQL Server directly from an ordinary select function.
Example:
I have a function fx_Str_Title_Case(varchar). (change string to title
case, caps first letter of each word in sentence).
At present I call this as follows:
SELECT fx_Str_Title_Case(aColumn) AS Result
FROM aTable
I wont to know if I can call this like this:
SELECT aColumn AS Result
FROM aTable
to get the same result?
ThanksPut your function in a view and query the view.
David Portas
SQL Server MVP
--|||Thanks, I though of that, but was wondering if there is any way to
trigger the function based on constraints on the table or something
like.|||You can use a function in a constraint but constraints are referenced
only for updates, not for a SELECT.
David Portas
SQL Server MVP
--|||You can create a computed column with the UDF call.
CREATE TABLE Test(name VARCHAR(15), ProperName as (dbo.Proper(name)))
INSERT INTO Test Values('abc xyz')
INSERT INTO Test Values('abc XYZ aVC')
SELECT * FROM Test
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
<philip.mckee@.pramerica.ie> wrote in message
news:1123672753.295824.65610@.g44g2000cwa.googlegroups.com...
> Thanks, I though of that, but was wondering if there is any way to
> trigger the function based on constraints on the table or something
> like.
>|||On 10 Aug 2005 04:15:50 -0700, David Portas wrote:

>Put your function in a view and query the view.
Or in a computed column in the table.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||What is wrong with specifying the function, and how would you know what
function is beging "fired"?
<philip.mckee@.pramerica.ie> wrote in message
news:1123672116.950885.206380@.g49g2000cwa.googlegroups.com...
> Hi all, Can anyone tell me if it is possible to fire a user defined
> function in SQL Server directly from an ordinary select function.
> Example:
> I have a function fx_Str_Title_Case(varchar). (change string to title
> case, caps first letter of each word in sentence).
> At present I call this as follows:
> SELECT fx_Str_Title_Case(aColumn) AS Result
> FROM aTable
> I wont to know if I can call this like this:
> SELECT aColumn AS Result
> FROM aTable
> to get the same result?
> Thanks
>