BinToBit function

Anyone happen to have a function that does the following:

I have an integer for example 57.

This translates to binary: 111001

I'm looking for a function like this:

Code Snippet

CREATE FUNCTION [dbo].[BinToBit] (@.ValueCol int, @.Number TinyInt)

RETURNS bit AS

If I would call the function:

select dbo.BinToBit(57, 0) it should return 1

select dbo.BinToBit(57, 1) it should return 0

select dbo.BinToBit(57, 2) it should return 0

select dbo.BinToBit(57, 3) it should return 1

select dbo.BinToBit(57, 4) it should return 1

select dbo.BinToBit(57, 5) it should return 1

I've been looking on the net, because I'm convinced someone must have this kind of function, unfortunately haven't been able to find it.

CREATE FUNCTION [dbo].[BinToBit] (@.ValueCol int, @.Number TinyInt)

RETURNS bit AS

BEGIN

RETURN (@.ValueCol & POWER(2,@.Number))

END

|||Ha! Perfect thanks!

binding textbox with SqlDataSource

Hi,

I wants to bind textbox with sqldatasource in c#.net so I am using following code and has following error...

Exception Details:System.NullReferenceException: Object reference not set to an instance of an object.

Source Error:

Line 22:
Line 23: System.Data.DataView dv = (DataView) SqlDataSource1.Select(System.Web.UI.DataSourceSelectArguments.Empty);
Line 24: TextBox1.Text = dv[0]["Proid"].ToString();
Line 25:
Line 26: }

Please, anybody knows solution tell me

Show all your code here, please. Do you have the column "Proid" in your datasource and a TextBox1 in your ASPX page?

Binding a SqlDataSource, to a GridView at Runtime

Hello

I'm experiencing some problems, binding a SqlDataSource to a GridView.

The following code creates the SqlDataSource:
string strConn = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
string strProvider = ConfigurationManager.ConnectionStrings["ConnectionString"].ProviderName;

SqlDataSource ds = new SqlDataSource(strProvider, strConn);
ds.SelectCommand = "SELECT * FROM rammekategori";

Then i bind the SqlDataSource to a GridView:

GridView1.DataSource = ds;
GridView1.DataBind();

ErrorMessage:

Format of the initialization string does not conform to specification starting at index 0.

Exception Details: System.ArgumentException: Format of the initialization string does not conform to specification starting at index 0.

Line 24: GridView1.DataBind();

Am i totally off target here? Can it be something about, that you have to set the datasource of the gridview, before the Page_Load event?

Thanks - MartinHN

You are probably not going to be happy with the results if you do manage to fix your immediate error. May I suggest you actually put the SqlDataSource in the .aspx page? You can then bind it in code, but don't change the select statement in code, as that too will cause some issues.|||

oh. The code was copied from VS2005, i forgot one important detail.

I add the SqlDataSource to the page, by using Page.Controls.Add(ds);

Still, i get the error, even if i set the DataSource property of the gridview like this:

GridView1.DataSource = (SqlDataSource)Page.FindControl("ds");

--

MartinHN

|||

What if you try this:

SqlDataSource m_SqlDataSource = Page.FindControl("ds") as SqlDataSource;

if (m_SqlDataSource != null)
{
GridView1.DataSourceID = m_SqlDataSource.ID;
GridView1.DataBind();
}

HTH,
Ryan

|||

>>>>What if you try this:

SqlDataSource m_SqlDataSource = Page.FindControl("ds") as SqlDataSource;

if (m_SqlDataSource != null)
{
GridView1.DataSourceID = m_SqlDataSource.ID;
GridView1.DataBind();
}

Still no success... It is still the samme error message:Format of the initialization string does not conform to specification starting at index 0.

I was wondering, if i need to set a couple of more properties on my SqlDataSource.

One thing i just tried, was createing a new SqlDataSource directly on the aspx page, from the Designer in VS2005. The i set the datasource, to the FindControl method, with the name as the parameter. That worked. But i'm interessted in getting a SqlDataSource object, from lower tier, than the page it self. So that i have a complete SqlDataSource with select, update, delete and insert commands, to use along with the gridview...

|||

Ah. I totally missed the actual error. Please post your connection string, leaving out any login information, but keep the formatting as-is.

|||

ok...

The connectionstring i am using are as follows:

<connectionStrings>
<add name="ConnectionString" connectionString="DRIVER={MySQL ODBC 3.51 Driver};SERVER=myserverip;DATABASE=mydb;UID=myuid;PASSWORD=mypwd;" providerName="System.Data.Odbc" />
</connectionStrings>

Should there be any troubles using a mySQL DB?

|||

I think I see your problem. The overloads for the SqlDataSource on MSDN are as follows:

SqlDataSource()
SqlDataSource(string connectionString, string selectCommand)
SqlDataSource(string providerName, string connectionString, string selectCommand)

So, you should be doing something like this:

string strConn = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
string strProvider = ConfigurationManager.ConnectionStrings["ConnectionString"].ProviderName;
string selectCommand = "SELECT * FROM rammekategori";

SqlDataSource ds = new SqlDataSource(strProvider, strConn, selectCommand);

HTH,
Ryan

|||

Ryan - I really cannot find a way to show my appreciation... That was very nice to have sorted out, thanks for your help.

What about insert, delete and update commands, can i set them after I've instantiated the SqlDataSource object?

|||

martinhn wrote:

What about insert, delete and update commands, can i set them after I've instantiated the SqlDataSource object?

Yes.

SqlDataSource.DeleteCommand
SqlDataSource.InsertCommand
SqlDataSource.UpdateCommand

HTH,
Ryan

sql

Bind multi-table dataset to a datagrid

I have created a SQL procedure that returns a dataset with a varying number of tables like the following example:

RepID-- PhoneUPS
---- ----
3---- 3

RepID-- PhoneUPS
---- ----
4---- 0

RepID-- PhoneUPS
---- ----
5---- 2

No more results.
(9 row(s) returned)
@.RETURN_VALUE = 0

All of the tables have the same header row, but are a seperate table. From my experience, I am not able to bind more than one table to a datagrid. Does anyone have any suggestions on how I can go about displaying this full dataset on my aspx page? I've been going in circles on this for two days, so I'm open to any suggestions :)

Cheers,
AndrewCan you create a UNION query in the stored procedure, rather then seperate resultsets?

Failing that, you can manually add each row of each resultset to the Items collection of the Datagrid.|||Thanks for the tip. I've been working on my SQL procedure trying to incorporate the UNION in the SELECT statement. I am having problems with the logic of working it in, though. Here is the original SQL procedure I've created:

/*BEGIN Procedure */

ALTER PROCEDURE Rep_Status_Array

AS

/* Build array or ID numbers to cycle through */
DECLARE RepIDs_cursor CURSOR
FOR SELECT UserID
FROM TBL_Users
WHERE (TBL_Users.DepartmentID > 0)

OPEN RepIDs_cursor
DECLARE @.userID int
FETCH NEXT FROM RepIDs_cursor INTO @.userID

/* Begin WHILE loop to collect data for each ID */
WHILE (@.@.FETCH_STATUS <> -1)
BEGIN
SELECT
RepID=@.userID,

PhoneUPS=(SELECT Count(*) FROM TBL_UpEntry WHERE (TypeID = 11) AND (SalesmanID = @.userID)),

LOTUPS=(SELECT Count(*) FROM TBL_UpEntry WHERE (TypeID = 1) AND (SalesmanID = @.userID)),

CVR=(SELECT Count(*) FROM TBL_UpEntry WHERE (TypeID = 1) AND (SalesmanID = @.userID)),

FETCH NEXT FROM RepIDs_cursor INTO @.userID
END
/* END WHILE loop */

CLOSE RepIDs_cursor
DEALLOCATE RepIDs_cursor

/* END Procedure */

The problem I'm having with this is that each time through the WHILE loop creates a new table in the dataset that is returned by the procedure. Any suggestions?|||Is it even possible to use UNION or UNION ALL within a WHILE loop?|||To the best of my knowledge, it is not possible to use the UNION statement in conjunction with a WHILE loop.

I'm going to go with Douglas' second recommendation and manually create a table within my page, adding each row one at a time.|||Another alternative:

Create a temporary table, and then INSERT all selected rows into the temp table, and then at the end of the SP,

SELECT * FROM #temp

(with any required ORDER BY).|||Thanks for the help, Douglas, much appreciated!

Andrew

Binary files in DB : set name on retrieve

Hello,

I have a table that stores binary files. When I serve them up to the user, I call the following page (serveDocument.aspx?DocumentID=xxx) which holds only the following code and pass the document ID:

Protected Sub Page_Load(ByVal senderAs Object,ByVal eAs System.EventArgs)Handles Me.Load
Dim DocumentIDAs Integer = Convert.ToInt32(Request.QueryString("DocumentID"))
'Connect to the database and bring back the image contents & MIME type for the specified picture Using myConnectionAs New SqlConnection(ConfigurationManager.ConnectionStrings("kelly_lmConnectionString1").ConnectionString)
Const SQLAs String ="SELECT [Content_Type], [Document_Data] FROM [lm_Service_Detail_Documents] WHERE [Document_id] = @.Document_id"Dim myCommandAs New SqlCommand(SQL, myConnection)
 myCommand.Parameters.AddWithValue("@.Document_id", DocumentID)
 myConnection.Open()
Dim myReaderAs SqlDataReader = myCommand.ExecuteReader
If myReader.ReadThen Response.ContentType = myReader("Content_Type").ToString()
 Response.BinaryWrite(myReader("Document_Data"))
End If myReader.Close() myConnection.Close()End Using
End Sub

It works perfectly. But heres the thing: I want users to be able to right click on the link and chose 'Save Target As'. In IE 7, this works fine. In FireFox it saves the document as 'serveDocument.aspx'. Is there any way toinjectthe filename that I want to save the document as?

Thank you.

Add this Header also

context.Response.ContentType = "application/octet-stream";
context.Response.AddHeader("Content-Disposition", "attachment; filename=\"" + filename+".txt" + "\"");

------------------

Mark as Answer if you feel

|||

What does 'context' refer to?

And, are you saying I shouldn't set the ContentType to the actual type of file?

|||

REmove the context

it uses to html encode when you are using http handler

|||

Well, that partially works. When a user clicks the linkthen saves the file, the correct name is present. But, my issue isn't that... I want the user to be able to right click the link and 'Save Link As...' with the correct filename. When I do that, it still says 'documentServe.aspx'.

|||

Hi,

But heres the thing: I want users to be able to right click on the link and chose 'Save Target As'. In IE 7, this works fine. In FireFox it saves the document as 'serveDocument.aspx'.

From your description, it seems that when you right click on the link and choose the 'save target as' in IE7, you can got the whole url with parameters, but not in FireFox, right?

If so, I'm afraid that the behavior is related to the Explorer, and based on my knowledge, there's not any workaround to fix the problem, since working mechanism for IE and FireFox is just not the same.

Thanks.

sql

Binary Column always returns 0 for ISNULL

Why don't i ever get return value of 1 when the following binary column (profSignature) is null?

RETURN SELECT ISNULL

(profSignature, profSignature)FROMmpProfilesWHEREApplicantID =CAST(@.CIDAS INT)ANDProfileID =CAST(@.PIDAS INT)

There is no conversion between GUID and INT that is what I think you are trying to do, try the link below for SQL Server data type conversion chart. Hope this helps.

http://msdn2.microsoft.com/en-us/library/ms187928.aspx

|||I forgot to add that the column type is "Image" in SQL 2005|||

Dup post

|||How would I get it so a <NULL> column value makes the stored procedure return 1 or 0?|||

I have checked the conversion chart again that is not valid either so check the create table statement below from Microsoft AdventureWorks it pick the columns relevant to your situation and you can get the information you are looking for. Sorry about the duplicate post. Hope this helps.

CREATE TABLE [EmployeePhoto] (
[EmployeePhotoID] [int] IDENTITY (1, 1) NOT NULL ,
[ThumbNailPhoto] [image] NULL ,
[ThumbnailPhotoFileName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LargePhoto] [image] NULL ,
[LargePhotoFileName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_EmployeePhoto_ModifiedDate] DEFAULT (getdate()),
CONSTRAINT [PK_EmployeePhoto_EmployeePhotoID] PRIMARY KEY CLUSTERED
(
[EmployeePhotoID]
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

|||

If you need 1 on null try this:

RETURN SELECT ISNULL(profSignature,1)
FROMmpProfiles
WHEREApplicantID =CAST(@.CIDAS INT)
ANDProfileID =CAST(@.PIDAS INT)

or

RETURN (case
when exist(Select * FROMmpProfiles
WHEREApplicantID =CAST(@.CIDAS INT)
ANDProfileID =CAST(@.PIDAS INT)
and profSignature is null) then 1 -- for null

when exist(Select * FROMmpProfiles
WHEREApplicantID =CAST(@.CIDAS INT)
ANDProfileID =CAST(@.PIDAS INT)
and not profSignature is null) then 2 -- for not nulls

else

0 -- does not exists in database

end)

|||Thanks ... the case statement did the trick.sql

big problem with sql server 2000 msde sp 4 (stops working by accident)

Hi,
I get the following error-message from sqlserver 2000 (msde edition)
I have no idea what is going wrong.
I was only able to get back to working state by
DBCC CHECKDB ALLOW-DATA-LOSS
(or restoring a backup)
But i do not want the db to crash at my customers ...
In the knowledge-base I found:
"An assertion or Msg 7987 may occur when an operation is performed on an
instance of SQL Server"
without any reason why inconsitencies can occur
This bug is at least known since 5th april 2005 ... and still known for
sql-server 9 (2005 I think)
I hope I oversaw something ... or does SQLServer realy stops to work by
chance?
please help!
2006-07-26 03:43:04.00 spid51 ex_raise2: Exception raised, major=79,
minor=87, severity=22, attempting to create symptom dump
2006-07-26 03:43:04.28 spid51 Using 'dbghelp.dll' version '4.0.5'
*Dump thread - spid = 51, PSS = 0x414491a8, EC = 0x414494d8
Event Type: Error
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 17052
Date: 7/26/2006
Time: 5:32:26 AM
User: N/A
Computer: HLX02
Description:
Error: 7987, Severity: 22, State: 3
A possible database consistency problem has been detected on database
'faroer'. DBCC CHECKDB and DBCC CHECKCATALOG should be run on database
'faroer'.
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
Data:
0000: 33 1f 00 00 16 00 00 00 3......
0008: 06 00 00 00 48 00 4c 00 ...H.L.
0010: 58 00 30 00 32 00 00 00 X.0.2...
0018: 07 00 00 00 66 00 61 00 ...f.a.
0020: 72 00 6f 00 65 00 72 00 r.o.e.r.
0028: 00 00 ..Sascha Bohnenkamp wrote:
> Hi,
> I get the following error-message from sqlserver 2000 (msde edition)
> I have no idea what is going wrong.
> I was only able to get back to working state by
> DBCC CHECKDB ALLOW-DATA-LOSS
> (or restoring a backup)
> But i do not want the db to crash at my customers ...
> In the knowledge-base I found:
> "An assertion or Msg 7987 may occur when an operation is performed on an
> instance of SQL Server"
> without any reason why inconsitencies can occur
> This bug is at least known since 5th april 2005 ... and still known for
> sql-server 9 (2005 I think)
> I hope I oversaw something ... or does SQLServer realy stops to work by
> chance?
> please help!
> 2006-07-26 03:43:04.00 spid51 ex_raise2: Exception raised, major=79,
> minor=87, severity=22, attempting to create symptom dump
> 2006-07-26 03:43:04.28 spid51 Using 'dbghelp.dll' version '4.0.5'
> *Dump thread - spid = 51, PSS = 0x414491a8, EC = 0x414494d8
>
> Event Type: Error
> Event Source: MSSQLSERVER
> Event Category: (2)
> Event ID: 17052
> Date: 7/26/2006
> Time: 5:32:26 AM
> User: N/A
> Computer: HLX02
> Description:
> Error: 7987, Severity: 22, State: 3
> A possible database consistency problem has been detected on database
> 'faroer'. DBCC CHECKDB and DBCC CHECKCATALOG should be run on database
> 'faroer'.
> For more information, see Help and Support Center at
> http://go.microsoft.com/fwlink/events.asp.
> Data:
> 0000: 33 1f 00 00 16 00 00 00 3......
> 0008: 06 00 00 00 48 00 4c 00 ...H.L.
> 0010: 58 00 30 00 32 00 00 00 X.0.2...
> 0018: 07 00 00 00 66 00 61 00 ...f.a.
> 0020: 72 00 6f 00 65 00 72 00 r.o.e.r.
> 0028: 00 00 ..
No, SQL Server does not "stop working by accident". Most likely you
have flaky hardware that is causing corruption in your database file.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Tracy McKibben schrieb:
> No, SQL Server does not "stop working by accident". Most likely you
> have flaky hardware that is causing corruption in your database file.
well ... are there any chance to find someting to help find the problem
in the log files of the server (or in the dumps) ?
From teh stack dump I cannot see any i/o problems ...
* Short Stack Dump
* 009BA08C Module(sqlservr+005BA08C) (GetOSErrString+00004F68)
* 009BA9B5 Module(sqlservr+005BA9B5) (GetOSErrString+00005891)
* 006EE757 Module(sqlservr+002EE757) (SQLExit+00186C60)
* 005FA686 Module(sqlservr+001FA686) (SQLExit+00092B8F)
* 006BA785 Module(sqlservr+002BA785) (SQLExit+00152C8E)
* 004040DA Module(sqlservr+000040DA)
* 0042ADAE Module(sqlservr+0002ADAE)
* 0042A3D3 Module(sqlservr+0002A3D3)
* 0043638B Module(sqlservr+0003638B)
* 0043288B Module(sqlservr+0003288B)
* 00432542 Module(sqlservr+00032542)
* 00434980 Module(sqlservr+00034980)
* 00432542 Module(sqlservr+00032542)
* 00434980 Module(sqlservr+00034980)
* 00432542 Module(sqlservr+00032542)
* 00861732 Module(sqlservr+00461732) (GetIMallocForMsxml+0006CBB2)
* 0086116B Module(sqlservr+0046116B) (GetIMallocForMsxml+0006C5EB)
* 00434980 Module(sqlservr+00034980)
* 00432542 Module(sqlservr+00032542)
* 005B810A Module(sqlservr+001B810A) (SQLExit+00050613)
* 005B8266 Module(sqlservr+001B8266) (SQLExit+0005076F)
* 004398A5 Module(sqlservr+000398A5)
* 004398A5 Module(sqlservr+000398A5)
* 0041D396 Module(sqlservr+0001D396)
* 00861732 Module(sqlservr+00461732) (GetIMallocForMsxml+0006CBB2)
* 0086116B Module(sqlservr+0046116B) (GetIMallocForMsxml+0006C5EB)
* 005B810A Module(sqlservr+001B810A) (SQLExit+00050613)
* 005B8266 Module(sqlservr+001B8266) (SQLExit+0005076F)
* 00434980 Module(sqlservr+00034980)
* 00432542 Module(sqlservr+00032542)
* 004194B9 Module(sqlservr+000194B9)
* 004193E4 Module(sqlservr+000193E4)
* 00429EAA Module(sqlservr+00029EAA)
* 00415D04 Module(sqlservr+00015D04)
* 00416214 Module(sqlservr+00016214)
* 00415F28 Module(sqlservr+00015F28)
* 0076C7D0 Module(sqlservr+0036C7D0) (SQLExit+00204CD9)
* 007704EF Module(sqlservr+003704EF) (SQLExit+002089F8)
* 0077099C Module(sqlservr+0037099C) (SQLExit+00208EA5)
* 006332EC Module(sqlservr+002332EC) (SQLExit+000CB7F5)
* 0043D005 Module(sqlservr+0003D005)
* 0042598D Module(sqlservr+0002598D)
* 41075309 Module(ums+00005309) (UmsThreadScheduler::ExitUser+00000459)|||Sascha Bohnenkamp wrote:
> Tracy McKibben schrieb:
>> No, SQL Server does not "stop working by accident". Most likely you
>> have flaky hardware that is causing corruption in your database file.
> well ... are there any chance to find someting to help find the problem
> in the log files of the server (or in the dumps) ?
> From teh stack dump I cannot see any i/o problems ...
> * Short Stack Dump
> * 009BA08C Module(sqlservr+005BA08C) (GetOSErrString+00004F68)
> * 009BA9B5 Module(sqlservr+005BA9B5) (GetOSErrString+00005891)
> * 006EE757 Module(sqlservr+002EE757) (SQLExit+00186C60)
> * 005FA686 Module(sqlservr+001FA686) (SQLExit+00092B8F)
> * 006BA785 Module(sqlservr+002BA785) (SQLExit+00152C8E)
> * 004040DA Module(sqlservr+000040DA)
> * 0042ADAE Module(sqlservr+0002ADAE)
> * 0042A3D3 Module(sqlservr+0002A3D3)
> * 0043638B Module(sqlservr+0003638B)
> * 0043288B Module(sqlservr+0003288B)
> * 00432542 Module(sqlservr+00032542)
> * 00434980 Module(sqlservr+00034980)
> * 00432542 Module(sqlservr+00032542)
> * 00434980 Module(sqlservr+00034980)
> * 00432542 Module(sqlservr+00032542)
> * 00861732 Module(sqlservr+00461732) (GetIMallocForMsxml+0006CBB2)
> * 0086116B Module(sqlservr+0046116B) (GetIMallocForMsxml+0006C5EB)
> * 00434980 Module(sqlservr+00034980)
> * 00432542 Module(sqlservr+00032542)
> * 005B810A Module(sqlservr+001B810A) (SQLExit+00050613)
> * 005B8266 Module(sqlservr+001B8266) (SQLExit+0005076F)
> * 004398A5 Module(sqlservr+000398A5)
> * 004398A5 Module(sqlservr+000398A5)
> * 0041D396 Module(sqlservr+0001D396)
> * 00861732 Module(sqlservr+00461732) (GetIMallocForMsxml+0006CBB2)
> * 0086116B Module(sqlservr+0046116B) (GetIMallocForMsxml+0006C5EB)
> * 005B810A Module(sqlservr+001B810A) (SQLExit+00050613)
> * 005B8266 Module(sqlservr+001B8266) (SQLExit+0005076F)
> * 00434980 Module(sqlservr+00034980)
> * 00432542 Module(sqlservr+00032542)
> * 004194B9 Module(sqlservr+000194B9)
> * 004193E4 Module(sqlservr+000193E4)
> * 00429EAA Module(sqlservr+00029EAA)
> * 00415D04 Module(sqlservr+00015D04)
> * 00416214 Module(sqlservr+00016214)
> * 00415F28 Module(sqlservr+00015F28)
> * 0076C7D0 Module(sqlservr+0036C7D0) (SQLExit+00204CD9)
> * 007704EF Module(sqlservr+003704EF) (SQLExit+002089F8)
> * 0077099C Module(sqlservr+0037099C) (SQLExit+00208EA5)
> * 006332EC Module(sqlservr+002332EC) (SQLExit+000CB7F5)
> * 0043D005 Module(sqlservr+0003D005)
> * 0042598D Module(sqlservr+0002598D)
> * 41075309 Module(ums+00005309) (UmsThreadScheduler::ExitUser+00000459)
Doesn't have to be an I/O problem - it could be bad RAM, a bad CPU, any
number of things. You should run some hardware diagnostics on the
machine, perhaps even open an incident with Microsoft - they can help
you decipher the logs.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Big clustered index rebuild

I need to establish the storage requirements for a clustered index rebuild with SQL 05. The table is made up of the following columns

[ProductID] [int] NOT NULL,

[RegionID] [int] NOT NULL,

[TimeID] [int] NOT NULL,

[FactID] [int] NOT NULL,

[Value] [decimal](14, 4) NOT NULL

This is the clustered index :-

[RegionId] ASC,

[FactId] ASC,

[TimeId] ASC,

[ProductId] ASC

This is the result of a sp_spaceused on this particular table

name rows reserved data index_size unused Table 16910379278 868107368 KB 863579184 KB 3869848 KB 658336 KB

The database where this table is stored is in Simple recovery mode.

What i would really like to know is, what additional storage would i require to run the following rebuild index command.

Alter Index blah on table Rebuild

Thanks

There is a nice explanation here.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

Bidirectional transactional replication and remote distributors

I want to be able to setup the following configuration using bidirectional transactional replication on SQL 2005

instance A lives on machine 1

instance B lives on machine 2

Instance A publishes to a transactional subscription on Instance B

Instance B does the reverse and publishes to a transactional subscription on Instance A

Instance A pushes to a distribution database on machine 2

Instance B pushes to a distribution database on machine 1

Problems Implementing Configuration

I can setup each instance as a distributor and create separate distribution databases using

sp_adddistributor and sp_adddistributiondb

I can then enable each publisher to use the correct distribution database using sp_adddistpublisher

However, when I try and run sp_replicationdboption to setup a database for publication, I get the error:

Msg 20028, Level 16, State 1, Procedure sp_MSpublishdb, Line 55

The Distributor has not been installed correctly. Could not enable database for publishing.

If I try and configure a publication with the wizard, it says that instance A must be enabled as a publisher before you can create a publication - and then presents the dialog to add instance A to be handled by the local distribution database - which I don't want.

It appears that I need to run sp_adddistributor on the publisher as well as the distributor for the appropriate instance, but if I do this I get the error:

Msg 14099, Level 16, State 1, Procedure sp_adddistributor, Line 109

The server 'instance A' is already defined as a Distributor.

It seems that you can't select a remote distributor if you already have a local distributor configured.

Is there a way round this limitation?

Note that configuring the environment with a single distribution database works fine

Try calling sp_adddistpublisher on machine 1 to add machine 2 as publisher and do the same on machine 2 to add machine 1 as publisher.

Regards,

Gary

|||Check out the new Peer-Peer replication in 2005 which does exactly what you want.|||

Gary - I have tried what you suggested and got the following error as per my original post

Msg 14099, Level 16, State 1, Procedure sp_adddistributor, Line 109

The server 'instance A' is already defined as a Distributor.

=====================

Dinakar - thanks for the suggestion - I have been checking out peer to peer, but I am not sure if I can live with the restrictions

=====================

I will probably go with a single distribution database located on the publisher with the lowest load. This should be satisfactory for the requirements

Thanks

aero1

BI Accelerator 1.1 and Bulk Insert

Dear Anyone,

I had been using BI Accelerator 1.1 for 2 years now. In our current project, I encountered the following error during the importing phase of BI.

I traced the problem to the bulk insert statement that BI Accelerator is executing which is ...

BULK INSERT [Project88_Staging]..[S_Fact_BrdcastAlert] FROM 'C:\Program Files\Microsoft SQL Server Accelerator for BI\data\Project88\Import\S_Fact_BrdcastAlert.txt' WITH ( FORMATFILE = 'C:\Program Files\Microsoft SQL Server Accelerator for BI\data\Project88\DTS\Import\Format\S_Fact_Brdcast Alert.fmt' , DATAFILETYPE = 'widechar', BATCHSIZE = 100000 , MAXERRORS = 100 )

The error that it generates is as follows:

Bulk Insert: DataFileType was incorrectly specified as widechar. DataFileType will be assumed to be char because the data file does not have a Unicode signature.

We export data to text files that BI Accelerator picks up. All other files imports properly imported without problems except for one fact table text file. We use the same format file that BI Accelerator uses to export to file the data to make sure that there would be no incompatabilities. File size is about 2.3GB++, which is a standard size with our other fact table files that doesnt have any errors.

We also checked for data error which we found none. We also checked the txt file we generate, notepad confirms that it is a unicode file.

Can anyone please shed a light in what is happening. Is this a bug? As much as possible I dont want to place a workaround on this one since our entire framework for loading data is all the same and has already been set up. Any help would be apreciated.

Thank YouAny reply would be appreciated.

Joseph Ollero
jollero@.wizardsgroup.com

BETWEEN DATES (TIME) HELP

Hi,

I need to display the employees from a current work shift. These are the following work shift:

4 AM – 12 PM

12 PM – 8 PM

8 PM – 4 AM

I’m having problems in third one “8 PM – 4 AM”. The next code is the one that I’m working on. If somebody knows a better way to do this let me know or if somebody can help me with this go ahead.

begin

declare @.from_time varchar(25);

set @.from_time = '8:00:00.000 PM';

declare @.to_time varchar(25);

set @.to_time = '4:00:00.000 AM';

declare @.current_date VARCHAR(25);

set @.current_date = getdate();

--select convert(smalldatetime, substring(@.current_date, 0, 13) + @.from_time),

--convert(smalldatetime, substring(@.current_date, 0, 13) + @.to_time),

--getdate();

if getdate() BETWEEN convert(smalldatetime, substring(@.current_date, 0, 13) + @.from_time) AND

convert(smalldatetime, substring(@.current_date, 0, 13) + @.to_time)

print 'OK';

else

print 'NO';

end

That's because of midnight crossover - the dates are changed also. Try this, it's far more simple and reliable:

Code Snippet

declare @.From int, @.To int, @.DT int

select @.From = 20, @.To = 4, @.DT = datepart(hh, getdate())

if @.DT >= @.From or @.DT < @.To
print 'OK'
else
print 'No'

|||Yeah. Thanks.

Between date selection

Hi,

I have the following situation.

I want to create reports with a start date parameter and an end date parameter

In my query i use "where date between @.startdate and @.enddate"

i configure my parameters so i get a nice calendar when i generate the report.

the problem is when i select for example a date starting 1 april 2007 and enddate 10 april 2007,

the result in my report gives me only the data from 1 until 9 april and not until 10.

in my database the date is stored as a date time (10/04/2007 17:25:30).

Any suggestion how i can solve my problem?

Greetings

Vinnie

Hello Vinnie,

The problem is that your database stores the time with your date and when you select a date in the date picker, it doesn't have a tme, so anything after midnight gets excluded. Try this as your where clause:

where date >= @.startdate and date < dateadd(d, 1, @.enddate)

Hope this helps.

Jarret

|||

Hi Vinnie,

When I come across this I normally use a conversion in my query:

where convert(varchar(25),date,101) between @.startdate and @.enddate

The conversion eliminates the time from the database field and will pull back all rows with the date portion between the start and end dates.

Simone

|||

Do you have a date picker control on your reporting website, so that instead of the user having to manually input the date in the correct format, they are given a control to pick the date ranges?

(Like datetimepicker control in C#)

Please let me know as that is what I need to do.

|||

Yes I do. You need to change the data type of the parameter in the Report to datetime. Go to "Report" then "Report Parameters" Select the parameter from the list on the left and change the data type to DateTime. This should automatically give the date control.

Simone

Better way to solve my RS problem...

Hello all,
I have a sales table with the following columns:
Product
Date
Price
SalesAmount
Quantity
I need to write a report in Rs that has the following information:
Products sold in a month and during the year...
Currently I use a sub report with different date parameters ie date between
1/1/04 and 1/1/05 for The year sold data, and Date = @.Month for the month
sales.
However, sub-reports seem to slow the report down significantly. Is there a
better way to do this using only one report?
Thanks,
ClintTake a look at the [Employee Sales Summary.rdl] sample report that ships
with the product.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"AshVsAOD" <.> wrote in message news:en4Pm2NqEHA.340@.TK2MSFTNGP10.phx.gbl...
> Hello all,
> I have a sales table with the following columns:
> Product
> Date
> Price
> SalesAmount
> Quantity
> I need to write a report in Rs that has the following information:
> Products sold in a month and during the year...
> Currently I use a sub report with different date parameters ie date
between
> 1/1/04 and 1/1/05 for The year sold data, and Date = @.Month for the month
> sales.
> However, sub-reports seem to slow the report down significantly. Is there
a
> better way to do this using only one report?
> Thanks,
> Clint
>|||Thanks,
However, I need one table, this solution seems to require multiple
tables/charts.
Any other way?
"Ravi Mumulla (Microsoft)" <ravimu@.online.microsoft.com> wrote in message
news:eP2I7cWqEHA.3464@.tk2msftngp13.phx.gbl...
> Take a look at the [Employee Sales Summary.rdl] sample report that ships
> with the product.
> --
> Ravi Mumulla (Microsoft)
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "AshVsAOD" <.> wrote in message
news:en4Pm2NqEHA.340@.TK2MSFTNGP10.phx.gbl...
> > Hello all,
> >
> > I have a sales table with the following columns:
> > Product
> > Date
> > Price
> > SalesAmount
> > Quantity
> >
> > I need to write a report in Rs that has the following information:
> > Products sold in a month and during the year...
> >
> > Currently I use a sub report with different date parameters ie date
> between
> > 1/1/04 and 1/1/05 for The year sold data, and Date = @.Month for the
month
> > sales.
> > However, sub-reports seem to slow the report down significantly. Is
there
> a
> > better way to do this using only one report?
> >
> > Thanks,
> > Clint
> >
> >
>|||This looks to me to be a good candidate for drill down. Your query should
summarize by year,month. Then in Report Services you adding a grouping for
year and a grouping for month. You want the month grouping to be hidden and
based on the year. Then if they click on the year it expands and you get the
month. If the group header for the year you put in a sum expression for the
year total shows in the heading. Hope that makes sense you you.
Bruce L-C
MVP Reporting Services
"AshVsAOD" <.> wrote in message
news:eCvnWabqEHA.1300@.TK2MSFTNGP12.phx.gbl...
> Thanks,
> However, I need one table, this solution seems to require multiple
> tables/charts.
> Any other way?
> "Ravi Mumulla (Microsoft)" <ravimu@.online.microsoft.com> wrote in message
> news:eP2I7cWqEHA.3464@.tk2msftngp13.phx.gbl...
>> Take a look at the [Employee Sales Summary.rdl] sample report that ships
>> with the product.
>> --
>> Ravi Mumulla (Microsoft)
>> SQL Server Reporting Services
>> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>> "AshVsAOD" <.> wrote in message
> news:en4Pm2NqEHA.340@.TK2MSFTNGP10.phx.gbl...
>> > Hello all,
>> >
>> > I have a sales table with the following columns:
>> > Product
>> > Date
>> > Price
>> > SalesAmount
>> > Quantity
>> >
>> > I need to write a report in Rs that has the following information:
>> > Products sold in a month and during the year...
>> >
>> > Currently I use a sub report with different date parameters ie date
>> between
>> > 1/1/04 and 1/1/05 for The year sold data, and Date = @.Month for the
> month
>> > sales.
>> > However, sub-reports seem to slow the report down significantly. Is
> there
>> a
>> > better way to do this using only one report?
>> >
>> > Thanks,
>> > Clint
>> >
>> >
>>
>|||The Company Sales.rdl sample report, that shipped with SQL Server 2000
Reporting Services, is good example of using drilldown.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Bruce Loehle-Conger" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:Oj0OQIiqEHA.2764@.TK2MSFTNGP11.phx.gbl...
> This looks to me to be a good candidate for drill down. Your query should
> summarize by year,month. Then in Report Services you adding a grouping for
> year and a grouping for month. You want the month grouping to be hidden
> and based on the year. Then if they click on the year it expands and you
> get the month. If the group header for the year you put in a sum
> expression for the year total shows in the heading. Hope that makes sense
> you you.
> Bruce L-C
> MVP Reporting Services
> "AshVsAOD" <.> wrote in message
> news:eCvnWabqEHA.1300@.TK2MSFTNGP12.phx.gbl...
>> Thanks,
>> However, I need one table, this solution seems to require multiple
>> tables/charts.
>> Any other way?
>> "Ravi Mumulla (Microsoft)" <ravimu@.online.microsoft.com> wrote in message
>> news:eP2I7cWqEHA.3464@.tk2msftngp13.phx.gbl...
>> Take a look at the [Employee Sales Summary.rdl] sample report that ships
>> with the product.
>> --
>> Ravi Mumulla (Microsoft)
>> SQL Server Reporting Services
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "AshVsAOD" <.> wrote in message
>> news:en4Pm2NqEHA.340@.TK2MSFTNGP10.phx.gbl...
>> > Hello all,
>> >
>> > I have a sales table with the following columns:
>> > Product
>> > Date
>> > Price
>> > SalesAmount
>> > Quantity
>> >
>> > I need to write a report in Rs that has the following information:
>> > Products sold in a month and during the year...
>> >
>> > Currently I use a sub report with different date parameters ie date
>> between
>> > 1/1/04 and 1/1/05 for The year sold data, and Date = @.Month for the
>> month
>> > sales.
>> > However, sub-reports seem to slow the report down significantly. Is
>> there
>> a
>> > better way to do this using only one report?
>> >
>> > Thanks,
>> > Clint
>> >
>> >
>>
>>
>

Better Modeling Methods ?

Want to model the following two items: Can anyone think of better solutions
*****************
* Address
*****************
Here we have an Address which could relate to a Company or an Individual, but the same address will never relate to both company and indvidual. (this is theory to keep the proble simple). In this senerio, a Company and an Individual will have different addresses sometimes, and the same address sometimes, and two or more Individuals could also have the same address
Table
--
Compan
Individua
Addres
--
- Possible Solution A:
--
Make Company and Individual Primary Keys Globally Unique and map them both to the same foreign key, in one Address table
Compan
--
CompanyId (GUID) --
|
|
Individual
--
IndividualId (GUID) --
| Addres
| --
+--> EntityI
--
- Possible Solution B:
--
Make two address tables - store the exact same kind of data in two different locations - ;
Compan
--
CompanyId (GUID) --
| CompanyAddres
| --
+--> CompanyId
Individual
--
IndividualId (GUID) --
| IndividualAddres
| --
+--> IndividualI
********************
* Drug Allergys
********************
Here we will be modling drugs and their associated allergys, based upon both Ingredient and Route (method of drug administration i.e. Oral,IV,Suppository). In this case, I will have to check both Ingredient and Route OR Ingredient ONLY OR Route ONLY to see if these has been a drug Allergy (in the real world many many items and combinations there of)
Table
--
Dru
Ingredien
Rout
Allerg
--
- Possible Solution A:
--
Place all drug descriptors (Ingredient, Route etc) in the Allergy table as foreign keys
Dru
--
DrugId --
| IngredientMap
| -- Ingredien
| IngredientMapId --
| IngredientId <-- IngredientId --
+->DrugId Ingredient
| | Allerg
| RouteMap | --
| -- Route | AllergyI
| RouteMapId -- +-> IngredientI
| RouteId <-- RouteId --> RouteI
+->DrugId Rout
This problem differs from the Address problem, in that I can not create unique keys, because I need to look at all "drug descriptors" (Ingredient,Route etc.) at the same time, and therefore mappling all "drug descriptors to one foreign key does not seem to be convienient
In this senerio, I have to check all columns in Allergy (WHERE clause), ALL THE TIME, in order to get the correct results. For Example
SELECT AllergyI
FROM Allerg
WHERE IngredientId IN
SELECT IngredientI
FROM IngredientMa
WHERE DrugId = ) AN
RouteId IN
SELECT Rout
FROM RouteMa
WHERE DrugId =Hi
For the first scenario you can have a superset that contains individuals and
companies and allocates the keys. This will be the FK from the Address
table, although I would have throught that your relationships is the wrong
way round and address is a FK from companies or individual.
As far as drugs goes you may want to look and talk to snomed. I would be
surprised if you can be allergic to a route only (although understandable if
1KG suppository is one!). I think that you will need to directly link route
and ingredient if your ingredient includes a quantity. i.e. an
Drug-Ingredient-Route table which is then the FK in an interim
Allergy-Drug-Ingredient-Route. Anything you do come up with should be gone
over with a clinician to make sure all possible scenarios are covered.
John
"sorengi" <anonymous@.discussions.microsoft.com> wrote in message
news:C594FB72-0B24-4EED-9670-CF0316A39415@.microsoft.com...
> Want to model the following two items: Can anyone think of better
solutions ?
> ******************
> * Address *
> ******************
> Here we have an Address which could relate to a Company or an Individual,
but the same address will never relate to both company and indvidual. (this
is theory to keep the proble simple). In this senerio, a Company and an
Individual will have different addresses sometimes, and the same address
sometimes, and two or more Individuals could also have the same address.
> Tables
> --
> Company
> Individual
> Address
> --
> - Possible Solution A:-
> --
> Make Company and Individual Primary Keys Globally Unique and map them both
to the same foreign key, in one Address table.
>
> Company
> --
> CompanyId (GUID) --|
> |
> |
> |
> |
> Individual |
> -- |
> IndividualId (GUID) --|
> | Address
> | --
> +--> EntityId
> --
> - Possible Solution B:-
> --
> Make two address tables - store the exact same kind of data in two
different locations - ;(
> Company
> --
> CompanyId (GUID) --|
> |
> | CompanyAddress
> | --
> +--> CompanyId
> Individual |
> -- |
> IndividualId (GUID) --|
> | IndividualAddress
> | --
> +--> IndividualId
>
> *********************
> * Drug Allergys *
> *********************
> Here we will be modling drugs and their associated allergys, based upon
both Ingredient and Route (method of drug administration i.e.
Oral,IV,Suppository). In this case, I will have to check both Ingredient
and Route OR Ingredient ONLY OR Route ONLY to see if these has been a drug
Allergy (in the real world many many items and combinations there of).
> Tables
> --
> Drug
> Ingredient
> Route
> Allergy
>
> --
> - Possible Solution A:-
> --
> Place all drug descriptors (Ingredient, Route etc) in the Allergy table as
foreign keys.
> Drug
> --
> DrugId --+
> | IngredientMap
> | -- Ingredient
> | IngredientMapId --
> | IngredientId <-- IngredientId --+
> +->DrugId Ingredient |
> | | Allergy
> | RouteMap | --
> | -- Route | AllergyId
> | RouteMapId -- +-> IngredientId
> | RouteId <-- RouteId --> RouteId
> +->DrugId Route
>
> This problem differs from the Address problem, in that I can not create
unique keys, because I need to look at all "drug descriptors"
(Ingredient,Route etc.) at the same time, and therefore mappling all "drug
descriptors to one foreign key does not seem to be convienient.
>
> In this senerio, I have to check all columns in Allergy (WHERE clause),
ALL THE TIME, in order to get the correct results. For Example:
> SELECT AllergyId
> FROM Allergy
> WHERE IngredientId IN (
> SELECT IngredientId
> FROM IngredientMap
> WHERE DrugId = X
> ) AND
> RouteId IN (
> SELECT Route
> FROM RouteMap
> WHERE DrugId = X
> )
>|||Hi
What I am not clear about is if you have really need to have say someone is
allergic to 250mg of Drug A but someone is not allergic to 500mg of Drug A
(and vice-versa)!!!
I think in general your drug dictionary is going to be reasonably static
therefore you can build your complex relationships upon upload and index
them appropriately. Additional design thought would be needed when/if a
Pharmacist is allowed to produce their own incantation!
John
"Sorengi" <anonymous@.discussions.microsoft.com> wrote in message
news:3F34819A-ACF2-463A-8461-A50C15EFD2A2@.microsoft.com...
> Great idea for Company/Individual/Address - Thanks John.
> The problem that I am having with the Drug Interactions is that there can
be any combination of items which could result in an allergy. I will look
at what it takes to design per your suggestion - thanks for the ideas.
> Michael D.|||The patient specific items are located in client databases - (Hospitals). If a patient is or is not allergic to drug "X" comes form another system..
Thanks for your help, I was able to get the Super Set to work great for both issues
Michael D.

Better Modeling Methods ?

Want to model the following two items: Can anyone think of better solutions ?
******************
* Address *
******************
Here we have an Address which could relate to a Company or an Individual, but the same address will never relate to both company and indvidual. (this is theory to keep the proble simple). In this senerio, a Company and an Individual will have different ad
dresses sometimes, and the same address sometimes, and two or more Individuals could also have the same address.
Tables
Company
Individual
Address
- Possible Solution A:-
Make Company and Individual Primary Keys Globally Unique and map them both to the same foreign key, in one Address table.
Company
CompanyId (GUID) --|
|
|
|
|
Individual |
-- |
IndividualId (GUID) --|
| Address
| --
+--> EntityId
- Possible Solution B:-
Make two address tables - store the exact same kind of data in two different locations - ;(
Company
CompanyId (GUID) --|
|
| CompanyAddress
| --
+--> CompanyId
Individual |
-- |
IndividualId (GUID) --|
| IndividualAddress
| --
+--> IndividualId
*********************
* Drug Allergys *
*********************
Here we will be modling drugs and their associated allergys, based upon both Ingredient and Route (method of drug administration i.e. Oral,IV,Suppository). In this case, I will have to check both Ingredient and Route OR Ingredient ONLY OR Route ONLY to s
ee if these has been a drug Allergy (in the real world many many items and combinations there of).
Tables
Drug
Ingredient
Route
Allergy
- Possible Solution A:-
Place all drug descriptors (Ingredient, Route etc) in the Allergy table as foreign keys.
Drug
DrugId --+
| IngredientMap
| -- Ingredient
| IngredientMapId --
| IngredientId <-- IngredientId --+
+->DrugId Ingredient |
| | Allergy
| RouteMap | --
| -- Route | AllergyId
| RouteMapId -- +-> IngredientId
| RouteId <-- RouteId --> RouteId
+->DrugId Route
This problem differs from the Address problem, in that I can not create unique keys, because I need to look at all "drug descriptors" (Ingredient,Route etc.) at the same time, and therefore mappling all "drug descriptors to one foreign key does not seem t
o be convienient.
In this senerio, I have to check all columns in Allergy (WHERE clause), ALL THE TIME, in order to get the correct results. For Example:
SELECT AllergyId
FROM Allergy
WHERE IngredientId IN (
SELECT IngredientId
FROM IngredientMap
WHERE DrugId = X
) AND
RouteId IN (
SELECT Route
FROM RouteMap
WHERE DrugId = X
)
Hi
For the first scenario you can have a superset that contains individuals and
companies and allocates the keys. This will be the FK from the Address
table, although I would have throught that your relationships is the wrong
way round and address is a FK from companies or individual.
As far as drugs goes you may want to look and talk to snomed. I would be
surprised if you can be allergic to a route only (although understandable if
1KG suppository is one!). I think that you will need to directly link route
and ingredient if your ingredient includes a quantity. i.e. an
Drug-Ingredient-Route table which is then the FK in an interim
Allergy-Drug-Ingredient-Route. Anything you do come up with should be gone
over with a clinician to make sure all possible scenarios are covered.
John
"sorengi" <anonymous@.discussions.microsoft.com> wrote in message
news:C594FB72-0B24-4EED-9670-CF0316A39415@.microsoft.com...
> Want to model the following two items: Can anyone think of better
solutions ?
> ******************
> * Address *
> ******************
> Here we have an Address which could relate to a Company or an Individual,
but the same address will never relate to both company and indvidual. (this
is theory to keep the proble simple). In this senerio, a Company and an
Individual will have different addresses sometimes, and the same address
sometimes, and two or more Individuals could also have the same address.
> Tables
> --
> Company
> Individual
> Address
> --
> - Possible Solution A:-
> --
> Make Company and Individual Primary Keys Globally Unique and map them both
to the same foreign key, in one Address table.
>
> Company
> --
> CompanyId (GUID) --|
> |
> |
> |
> |
> Individual |
> -- |
> IndividualId (GUID) --|
> | Address
> | --
> +--> EntityId
> --
> - Possible Solution B:-
> --
> Make two address tables - store the exact same kind of data in two
different locations - ;(
> Company
> --
> CompanyId (GUID) --|
> |
> | CompanyAddress
> | --
> +--> CompanyId
> Individual |
> -- |
> IndividualId (GUID) --|
> | IndividualAddress
> | --
> +--> IndividualId
>
> *********************
> * Drug Allergys *
> *********************
> Here we will be modling drugs and their associated allergys, based upon
both Ingredient and Route (method of drug administration i.e.
Oral,IV,Suppository). In this case, I will have to check both Ingredient
and Route OR Ingredient ONLY OR Route ONLY to see if these has been a drug
Allergy (in the real world many many items and combinations there of).
> Tables
> --
> Drug
> Ingredient
> Route
> Allergy
>
> --
> - Possible Solution A:-
> --
> Place all drug descriptors (Ingredient, Route etc) in the Allergy table as
foreign keys.
> Drug
> --
> DrugId --+
> | IngredientMap
> | -- Ingredient
> | IngredientMapId --
> | IngredientId <-- IngredientId --+
> +->DrugId Ingredient |
> | | Allergy
> | RouteMap | --
> | -- Route | AllergyId
> | RouteMapId -- +-> IngredientId
> | RouteId <-- RouteId --> RouteId
> +->DrugId Route
>
> This problem differs from the Address problem, in that I can not create
unique keys, because I need to look at all "drug descriptors"
(Ingredient,Route etc.) at the same time, and therefore mappling all "drug
descriptors to one foreign key does not seem to be convienient.
>
> In this senerio, I have to check all columns in Allergy (WHERE clause),
ALL THE TIME, in order to get the correct results. For Example:
> SELECT AllergyId
> FROM Allergy
> WHERE IngredientId IN (
> SELECT IngredientId
> FROM IngredientMap
> WHERE DrugId = X
> ) AND
> RouteId IN (
> SELECT Route
> FROM RouteMap
> WHERE DrugId = X
> )
>
|||Great idea for Company/Individual/Address - Thanks John.
The problem that I am having with the Drug Interactions is that there can be any combination of items which could result in an allergy. I will look at what it takes to design per your suggestion - thanks for the ideas.
Michael D.
|||Hi
What I am not clear about is if you have really need to have say someone is
allergic to 250mg of Drug A but someone is not allergic to 500mg of Drug A
(and vice-versa)!!!
I think in general your drug dictionary is going to be reasonably static
therefore you can build your complex relationships upon upload and index
them appropriately. Additional design thought would be needed when/if a
Pharmacist is allowed to produce their own incantation!
John
"Sorengi" <anonymous@.discussions.microsoft.com> wrote in message
news:3F34819A-ACF2-463A-8461-A50C15EFD2A2@.microsoft.com...
> Great idea for Company/Individual/Address - Thanks John.
> The problem that I am having with the Drug Interactions is that there can
be any combination of items which could result in an allergy. I will look
at what it takes to design per your suggestion - thanks for the ideas.
> Michael D.
|||The patient specific items are located in client databases - (Hospitals). If a patient is or is not allergic to drug "X" comes form another system...
Thanks for your help, I was able to get the Super Set to work great for both issues.
Michael D.

Better Modeling Methods ?

Want to model the following two items: Can anyone think of better solutions
?
******************
* Address *
******************
Here we have an Address which could relate to a Company or an Individual, bu
t the same address will never relate to both company and indvidual. (this is
theory to keep the proble simple). In this senerio, a Company and an Indivi
dual will have different ad
dresses sometimes, and the same address sometimes, and two or more Individua
ls could also have the same address.
Tables
--
Company
Individual
Address
- Possible Solution A:-
--
Make Company and Individual Primary Keys Globally Unique and map them both t
o the same foreign key, in one Address table.
Company
--
CompanyId (GUID) --|
|
|
|
|
Individual |
-- |
IndividualId (GUID) --|
| Address
| --
+--> EntityId
- Possible Solution B:-
--
Make two address tables - store the exact same kind of data in two different
locations - ;(
Company
--
CompanyId (GUID) --|
|
| CompanyAddress
| --
+--> CompanyId
Individual |
-- |
IndividualId (GUID) --|
| IndividualAddress
| --
+--> IndividualId
*********************
* Drug Allergys *
*********************
Here we will be modling drugs and their associated allergys, based upon both
Ingredient and Route (method of drug administration i.e. Oral,IV,Suppositor
y). In this case, I will have to check both Ingredient and Route OR Ingredi
ent ONLY OR Route ONLY to s
ee if these has been a drug Allergy (in the real world many many items and c
ombinations there of).
Tables
--
Drug
Ingredient
Route
Allergy
- Possible Solution A:-
--
Place all drug descriptors (Ingredient, Route etc) in the Allergy table as f
oreign keys.
Drug
--
DrugId --+
| IngredientMap
| -- Ingredient
| IngredientMapId --
| IngredientId <-- IngredientId --+
+->DrugId Ingredient |
| | Allergy
| RouteMap | --
| -- Route | AllergyId
| RouteMapId -- +-> IngredientId
| RouteId <-- RouteId --> RouteId
+->DrugId Route
This problem differs from the Address problem, in that I can not create uniq
ue keys, because I need to look at all "drug descriptors" (Ingredient,Route
etc.) at the same time, and therefore mappling all "drug descriptors to one
foreign key does not seem t
o be convienient.
In this senerio, I have to check all columns in Allergy (WHERE clause), ALL
THE TIME, in order to get the correct results. For Example:
SELECT AllergyId
FROM Allergy
WHERE IngredientId IN (
SELECT IngredientId
FROM IngredientMap
WHERE DrugId = X
) AND
RouteId IN (
SELECT Route
FROM RouteMap
WHERE DrugId = X
)Hi
For the first scenario you can have a superset that contains individuals and
companies and allocates the keys. This will be the FK from the Address
table, although I would have throught that your relationships is the wrong
way round and address is a FK from companies or individual.
As far as drugs goes you may want to look and talk to snomed. I would be
surprised if you can be allergic to a route only (although understandable if
1KG suppository is one!). I think that you will need to directly link route
and ingredient if your ingredient includes a quantity. i.e. an
Drug-Ingredient-Route table which is then the FK in an interim
Allergy-Drug-Ingredient-Route. Anything you do come up with should be gone
over with a clinician to make sure all possible scenarios are covered.
John
"sorengi" <anonymous@.discussions.microsoft.com> wrote in message
news:C594FB72-0B24-4EED-9670-CF0316A39415@.microsoft.com...
> Want to model the following two items: Can anyone think of better
solutions ?
> ******************
> * Address *
> ******************
> Here we have an Address which could relate to a Company or an Individual,
but the same address will never relate to both company and indvidual. (this
is theory to keep the proble simple). In this senerio, a Company and an
Individual will have different addresses sometimes, and the same address
sometimes, and two or more Individuals could also have the same address.
> Tables
> --
> Company
> Individual
> Address
> --
> - Possible Solution A:-
> --
> Make Company and Individual Primary Keys Globally Unique and map them both
to the same foreign key, in one Address table.
>
> Company
> --
> CompanyId (GUID) --|
> |
> |
> |
> |
> Individual |
> -- |
> IndividualId (GUID) --|
> | Address
> | --
> +--> EntityId
> --
> - Possible Solution B:-
> --
> Make two address tables - store the exact same kind of data in two
different locations - ;(
> Company
> --
> CompanyId (GUID) --|
> |
> | CompanyAddress
> | --
> +--> CompanyId
> Individual |
> -- |
> IndividualId (GUID) --|
> | IndividualAddress
> | --
> +--> IndividualId
>
> *********************
> * Drug Allergys *
> *********************
> Here we will be modling drugs and their associated allergys, based upon
both Ingredient and Route (method of drug administration i.e.
Oral,IV,Suppository). In this case, I will have to check both Ingredient
and Route OR Ingredient ONLY OR Route ONLY to see if these has been a drug
Allergy (in the real world many many items and combinations there of).
> Tables
> --
> Drug
> Ingredient
> Route
> Allergy
>
> --
> - Possible Solution A:-
> --
> Place all drug descriptors (Ingredient, Route etc) in the Allergy table as
foreign keys.
> Drug
> --
> DrugId --+
> | IngredientMap
> | -- Ingredient
> | IngredientMapId --
> | IngredientId <-- IngredientId --+
> +->DrugId Ingredient |
> | | Allergy
> | RouteMap | --
> | -- Route | AllergyId
> | RouteMapId -- +-> IngredientId
> | RouteId <-- RouteId --> RouteId
> +->DrugId Route
>
> This problem differs from the Address problem, in that I can not create
unique keys, because I need to look at all "drug descriptors"
(Ingredient,Route etc.) at the same time, and therefore mappling all "drug
descriptors to one foreign key does not seem to be convienient.
>
> In this senerio, I have to check all columns in Allergy (WHERE clause),
ALL THE TIME, in order to get the correct results. For Example:
> SELECT AllergyId
> FROM Allergy
> WHERE IngredientId IN (
> SELECT IngredientId
> FROM IngredientMap
> WHERE DrugId = X
> ) AND
> RouteId IN (
> SELECT Route
> FROM RouteMap
> WHERE DrugId = X
> )
>|||Great idea for Company/Individual/Address - Thanks John.
The problem that I am having with the Drug Interactions is that there can be
any combination of items which could result in an allergy. I will look at
what it takes to design per your suggestion - thanks for the ideas.
Michael D.|||Hi
What I am not clear about is if you have really need to have say someone is
allergic to 250mg of Drug A but someone is not allergic to 500mg of Drug A
(and vice-versa)!!!
I think in general your drug dictionary is going to be reasonably static
therefore you can build your complex relationships upon upload and index
them appropriately. Additional design thought would be needed when/if a
Pharmacist is allowed to produce their own incantation!
John
"Sorengi" <anonymous@.discussions.microsoft.com> wrote in message
news:3F34819A-ACF2-463A-8461-A50C15EFD2A2@.microsoft.com...
> Great idea for Company/Individual/Address - Thanks John.
> The problem that I am having with the Drug Interactions is that there can
be any combination of items which could result in an allergy. I will look
at what it takes to design per your suggestion - thanks for the ideas.
> Michael D.|||The patient specific items are located in client databases - (Hospitals). I
f a patient is or is not allergic to drug "X" comes form another system...
Thanks for your help, I was able to get the Super Set to work great for both
issues.
Michael D.

best way to write to DB for ASP 2.0 project?

Following is a stored procedure I'm thinking of using in my ASP 2.0 project and I need opinions so I can evaluate if this is the optimum way to access and write to my database. I will be writing to an extra table (in addition to the standard aspnet_ tables). If you can please let me know your opinion, I'd appreciate it.

@.UserNamenvarchar(128),
@.Emailnvarchar(50),
@.FirstNamenvarchar(25),
@.LastNamenvarchar(50),
@.Teachernvarchar(25),
@.GradYrint

DECLARE@.UserIDuniqueidentifier
SELECT@.UserID =NULL
SELECT @.UserID = UserIdFROMdbo.aspnet_UsersWHERE LOWER(@.UserName) = LoweredUserName
INSERT INTO[table name]
(UserID,UserName,Email,FirstName,LastName,Teacher,GradYr)
VALUES(@.UserID,@.UserName,@.Email,@.FirstName,@.LastName,@.Teacher,@.GradYr)

Also, add some error handling in the stored procedure after the insert. Something like as follows.

SET @.returnstatus = @.@.error

IF @.returnstatus <> 0
BEGIN
RETURN @.returnstatus
END

|||

From what you show, it seems like you are making a "custom" way to just write Profile data (email, First, last, Teacher, GradYr). If you enable the Profile provider and supply these fields, you can use the built-in provider to do this kind of stuff - - no need to write your own.

|||

pbromberg:

From what you show, it seems like you are making a "custom" way to just write Profile data (email, First, last, Teacher, GradYr). If you enable the Profile provider and supply these fields, you can use the built-in provider to do this kind of stuff - - no need to write your own.

OK, I give. I just read a bunch of articles on the subject of profile provider and none of them really helped me--seemed like they were talking about creating instead of enabling profiles. Where's some good, simple information on activating the built-in provider. The closest I've come is implementing _CreatingUser on the CreateNewUser wizard, and listing the field names from my new table in web.config. If you can educate we where to go from here in order to enable the profile provider, I'd appreciate it.

|||

I just read up on profiles in Walther'sUnleashed book, and came up with the stuff below for web.config. I get Intellisense in the code-behind, which is a good sign, but when I run the page, I can't get it right for "type = " and the book doesn't elaborate on it. Not having the correct entry for the type criteria or omitting it produces an error. If someone can help me on this, I think I'll be in good shape.

One other thing: Should I use a separate table (shown as tblAlumni below) or use one of the standard aspnet tables? I'd prefer the latter to keep things the most simple, but Walther shows an "outside" table. Thanks in advance for any help.

<profile defaultProvider="DNProfileProvider"> <properties> <add name="FirstName" /> <add name="LastName" /> <add name="GradYr" type="integer"/> <add name="Address1" /> <add name="Address2" /> <add name="City" /> <add name="State" /> <add name="Zip" /> <add name="SpouseName" /> <add name="Gender" /> <add name="MaidenName" /> <add name="Phone" /> </properties> <providers> <add name="DNProfileProvider" type="??" connectionStringName="sqlConnection" profileTableName="tblAlumni"/> </providers> </profile>

|||

It depends on how the Profile class is written. The default profile provider has its own table and it can hold any type and number of fields that you define for each profile. However, this data is "opaque" in the database - you cannot easily search on it. If you want to use a custom profile provide as it seems from the snippet you posted, then it may use it's own table. There are some samples for "Table Profile Provider" and "Stored Procedure Profile Provider" that you can use as a model if you want a custom table.

Here is an article with some examples:

http://www.eggheadcafe.com/articles/20060731.asp

|||

muybn:

Following is a stored procedure I'm thinking of using in my ASP 2.0 project and I need opinions so I can evaluate if this is the optimum way to access and write to my database. I will be writing to an extra table (in addition to the standard aspnet_ tables). If you can please let me know your opinion, I'd appreciate it.

@.UserNamenvarchar(128),
@.Emailnvarchar(50),
@.FirstNamenvarchar(25),
@.LastNamenvarchar(50),
@.Teachernvarchar(25),
@.GradYrint

DECLARE@.UserIDuniqueidentifier
SELECT@.UserID =NULL
SELECT @.UserID = UserIdFROMdbo.aspnet_UsersWHERE LOWER(@.UserName) = LoweredUserName
INSERT INTO[table name]
(UserID,UserName,Email,FirstName,LastName,Teacher,GradYr)
VALUES(@.UserID,@.UserName,@.Email,@.FirstName,@.LastName,@.Teacher,@.GradYr)

A new problem has arisen, having to do with database, that I need to resolve first. I make an entry to my form, save it to the database; then the next entry I make throws the error that the same UID can't be written to the database. I believe that, Mr. DB, but since when am I writing the same UID to you? I don't know where to start tracing this, except to show the stored proc again (above), and to describe my process and some of my code that might generate it.

 
Protected Sub cuwCreateUserWizard1_CreatingUser(ByVal senderAs Object,ByVal eAs System.EventArgs)Handles cuwCreateUserWizard1.CreatedUser strEmail =CType(cuwCreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Email"), TextBox).Text strUserName =CType(cuwCreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("UserName"), TextBox).Text.ToLower strFirstName =CType(cuwCreateUserWizard1.CreateUserStep.CustomNavigationTemplateContainer.FindControl("txtFirstName"), TextBox).Text strLastName =CType(cuwCreateUserWizard1.CreateUserStep.CustomNavigationTemplateContainer.FindControl("txtLastName"), TextBox).Text lngGradYr =CType(cuwCreateUserWizard1.CreateUserStep.CustomNavigationTemplateContainer.FindControl("txtGradYr"), TextBox).Text strTeacher =CType(cuwCreateUserWizard1.CreateUserStep.CustomNavigationTemplateContainer.FindControl("txtTeacher"), TextBox).TextEnd Sub Protected Sub cuwCreateUserWizard1_CreatedUser(ByVal senderAs Object,ByVal eAs System.EventArgs)Handles cuwCreateUserWizard1.CreatedUserDim cmdAs New SqlCommand("sp_CreateUser", con) cmd.CommandType = Data.CommandType.StoredProcedure cmd.Parameters.AddWithValue("@.UserName", strUserName) cmd.Parameters.AddWithValue("@.Email", strEmail) cmd.Parameters.AddWithValue("@.FirstName", strFirstName) cmd.Parameters.AddWithValue("@.LastName", strLastName) cmd.Parameters.AddWithValue("@.Teacher", strTeacher) cmd.Parameters.AddWithValue("@.GradYr", lngGradYr) Using con con.Open() cmd.ExecuteScalar() con.Close()End Using cmd.Equals(Nothing)End Sub
  

Please let me know any other info you'd need to help me determine what's wrong.

|||

Can you please start a new POST / Thread. By this way we can concentrate your new issue. Since a post is marked as answered, every one will think that your issue is resolved. Hope you understand.

Best way to migrate SQL version 7 databases to 2000

We are moving all databases from a SQL version 7 server to a version 2000
server. Which of the following is the preferred method?

1) Detach the DB on the version 7 server, move it to the new server via
Windows Explorer and reattach it there.

2) Use SQL Enterprise Mananger with the DTS Export Wizard to "copy objects &
data between SQL server databases" from the version 7 server to the 2000
server.

With the changing of the version, I thought maybe option 2 is best. Thoughts?

--
Mike Czizek
Iowa Network Services, Inc.
mikec@.netins.comConsider using backup-restore method. It works rather well.

--
Dean Savovic
www.teched.hr

"Mike Czizek" <Mikec@.netins.net> wrote in message news:bnts03$5b8$1@.ins22.netins.net...
> We are moving all databases from a SQL version 7 server to a version 2000
> server. Which of the following is the preferred method?
> 1) Detach the DB on the version 7 server, move it to the new server via
> Windows Explorer and reattach it there.
> 2) Use SQL Enterprise Mananger with the DTS Export Wizard to "copy objects &
> data between SQL server databases" from the version 7 server to the 2000
> server.
> With the changing of the version, I thought maybe option 2 is best. Thoughts?
> --
> Mike Czizek
> Iowa Network Services, Inc.
> mikec@.netins.com

Best way to do a calculation

I'm kinda stuck on how to accomplish the following. Help appreciated.

I have a fact table like so:

TimeByDay ProjectID TaskID %Complete ActualHours Planned Hours

4/4/07 001 002 .25 5 8

4/5/07 001 002 .30 6 8

4/6/07 001 002 .40 8 8

4/7/07 001 002 .45 8 8

I want to add a calculated measure that is the product of %Complete and the total planned hours. In my example, the total planned hours for task 002 is 32 hours (sum of all days for a given taskID and ProjectID)

I was thinking of adding the total to the fact table - which would yield alot of repetitive data.

However, If it wont kill performance - I'd prefer to have a calculated expression that computes the total hours then performs my %Complete * TotalHours calculation.

your help is very much appreciated.

Hello again JohnBGood.

If you create a calculated measure, in the cube, like MyCalculatedMeasure = [Measure].[%Complete]*[Measure].[Planned Hours]

,a client like Excel2007 would take care of creating the totals for you with this calculated measure.

You can also create a named calculation on you fact table, in the data source view, with TSQL like [%Complete]*[Planned Hours] and aggregate that, because it is hours that are possible to aggregate by sum().

HTH

Thomas Ivarsson

|||

A good way to test the impact this would have on SSAS performance is to first define the measure in a query:

Code Snippet

with member [Measures].[Estimated Hours]

as

[Measures].[Percent Complete] * [Measures].[Planned Hours]

select

[Measures].[Estimated Hours] on 0,

[Project].[Project Name].Members on 1

from [My Project Cube]

Try this with various restrictions, etc. to see how it holds up. If it behaves well, you can use the formula in a calculated member that will make this query available to all your users in their various applications.

If you run into performance problems, try using the [Measures].[Planned Hours] measure on Axis 0 (Columns) to see if the performance problem is still there. If it is, the calculation isn't the source of the performance issue.

A couple other things .... be sure to consider the format of the value you want to return to your users. You can lookup the syntax of the "WITH MEMBER" clause in Books Online and it will show you how to define formats here. Also, consider your NonEmpty Behavior. With the NonEmpty Behavior, you identify when to return an Empty value (and skip the calculation). In this scenario, you might want to skip the calculation when [Measures].[Planned Hours] is empty because a calculated value would be meaningless.

Good luck,
Bryan Smith

Best use of Indexing and Clustering

I am afraid I am here again with a problem (I seem to create more help
requests than answers these days). I have the following table (which is not
my design!).
[Messagetpye] [varchar] (50) NULL ,
[Sourcetype] [varchar] (50) NULL ,
[CallID] [varchar] (50) NULL ,
[Devicetype] [varchar] (50) NULL ,
[DeviceID] [varchar] (50) NULL ,
[Starttimestamp] [datetime] NOT NULL ,
[Direction] [varchar] (50) NULL ,
[AdvisorID] [varchar] (50) NULL ,
[CustomerID] [varchar] (50) NULL ,
[ActivityID] [varchar] (50) NULL ,
[AuditTrail] [varchar] (8000) NULL
I have set Indexing on
StartTimeStamp as Clustered
AdvisorID
CustomerID
ActivityID
If I do a simple SQL Statement like.
select top 100 * from tbl_audittrail where advisorid like '%LH%'
I get a 10 second response and 97% cost on the index scanning 390,610
however if I do..
select top 50 * from tbl_audittrail where advisorid like '%LH%' and
starttimestamp >'03 august 2007'
I get a 120 second response and 97% cost on the index scanning 4,325,334
Is there any way to configure this in a better way to get a quicker response
on the 2nd Query as the web front end I have build always has to use the
starttimestamp along with one of the indexed fields in its searches.
Any help is greatly appreciated.
Stumpy
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200709/1Hi
First of all what is the version are you using? It is important because MS
has added in SQL Server 2005 INCLUDE operator for indexes, see BOL
Secondly, what kind of indexes you are created? Is it Clustered or
NonClusterd?
> select top 100 * from tbl_audittrail where advisorid like '%LH%'
Don't use TOP clause without specify ORDER BY clause as you may geg
unordered result. Moreover , with the above query SQL Server will not use an
index as you provide '%%' .Do you really need to return all columns? What if
you run
SELECT AdvisorID,CustomerID FROM tbl WHERE advisorid like 'LH%' ,does it
run faster?
> select top 50 * from tbl_audittrail where advisorid like '%LH%' and
> starttimestamp >'03 august 2007'
Again , do you have an index on starttimestamp ? If you do try run this
query as
select top 50 * from tbl_audittrail where advisorid like '%LH%' and
starttimestamp >='20070803' AND starttimestamp < DATEADD(d,1,getdate())
ORDER BY .....
"stumpy_uk via SQLMonster.com" <u15773@.uwe> wrote in message
news:77c0b1470e597@.uwe...
>I am afraid I am here again with a problem (I seem to create more help
> requests than answers these days). I have the following table (which is
> not
> my design!).
> [Messagetpye] [varchar] (50) NULL ,
> [Sourcetype] [varchar] (50) NULL ,
> [CallID] [varchar] (50) NULL ,
> [Devicetype] [varchar] (50) NULL ,
> [DeviceID] [varchar] (50) NULL ,
> [Starttimestamp] [datetime] NOT NULL ,
> [Direction] [varchar] (50) NULL ,
> [AdvisorID] [varchar] (50) NULL ,
> [CustomerID] [varchar] (50) NULL ,
> [ActivityID] [varchar] (50) NULL ,
> [AuditTrail] [varchar] (8000) NULL
> I have set Indexing on
> StartTimeStamp as Clustered
> AdvisorID
> CustomerID
> ActivityID
> If I do a simple SQL Statement like.
> select top 100 * from tbl_audittrail where advisorid like '%LH%'
> I get a 10 second response and 97% cost on the index scanning 390,610
> however if I do..
> select top 50 * from tbl_audittrail where advisorid like '%LH%' and
> starttimestamp >'03 august 2007'
> I get a 120 second response and 97% cost on the index scanning 4,325,334
> Is there any way to configure this in a better way to get a quicker
> response
> on the 2nd Query as the web front end I have build always has to use the
> starttimestamp along with one of the indexed fields in its searches.
> Any help is greatly appreciated.
> Stumpy
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200709/1
>|||Uri Dimant wrote:
>Hi
>First of all what is the version are you using? It is important because MS
>has added in SQL Server 2005 INCLUDE operator for indexes, see BOL
>Secondly, what kind of indexes you are created? Is it Clustered or
>NonClusterd?
>> select top 100 * from tbl_audittrail where advisorid like '%LH%'
>Don't use TOP clause without specify ORDER BY clause as you may geg
>unordered result. Moreover , with the above query SQL Server will not use an
>index as you provide '%%' .Do you really need to return all columns? What if
>you run
>SELECT AdvisorID,CustomerID FROM tbl WHERE advisorid like 'LH%' ,does it
>run faster?
>> select top 50 * from tbl_audittrail where advisorid like '%LH%' and
>> starttimestamp >'03 august 2007'
>Again , do you have an index on starttimestamp ? If you do try run this
>query as
> select top 50 * from tbl_audittrail where advisorid like '%LH%' and
> starttimestamp >='20070803' AND starttimestamp < DATEADD(d,1,getdate())
>ORDER BY .....
>>I am afraid I am here again with a problem (I seem to create more help
>> requests than answers these days). I have the following table (which is
>[quoted text clipped - 39 lines]
>> Stumpy
Uri,
Its SQL 2000 and the datetime was a Clustered Index.
However you have answered my question as soon as I took the * Away and added
the order by its 0 - 5 seconds for each of the queries...thanks again for all
your help
Cheers
Lee
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200709/1