Duplicate Record Problem

I am working on a web application that utilizes a sql server database. One of the tables is a large text file that is imported through a DTS package from a Unix server. For whatever reason, the Unix box dumps quite a few duplicate records in the nightly run and these are in turn pulled into the table. I need to get rid of these duplicates, but can't seem to get a workable solution. the query that is needed to get the records is:
SELECT tblAppointments.PatientID, tblPTDEMO2.MRNumber, tblAppointments.PatientFirstName, tblAppointments.PatientLastName,
tblAppointments.PatientDOB, tblAppointments.PatientSex, tblAppointments.NewPatient, tblAppointments.HomePhone,
tblAppointments.WorkPhone, tblAppointments.Insurance1, tblPTDEMO2.Ins1CertNmbr, tblAppointments.Insurance2,
tblPTDEMO2.Ins2CertNmbr, tblAppointments.Insurance3, tblPTDEMO2.Ins3CertNmbr, tblAppointments.ApptDate, tblAppointments.ApptTime
FROM tblAppointments CROSS JOIN
tblPTDEMO2
WHERE (tblAppointments.PatientID = tblPTDEMO2.MRNumber)
AND tblAppointments.Insurance1 = 'MED'
AND tblAppointments.ApptTypeID <> 'MTG'
AND tblAppointments.ApptTypeID <> 'PNV'
AND DateDiff("dd", ApptDate, GetDate()) = 0
Order By tblAppointments.ApptDate
My first thought was to try to get a Select DISTINCT to work, but couldn't figure out how to do this with the query. My next thought was to try to set up constraints on the table, but, since there are duplicates, the DTS package fails. I assume there is a way to set up the transformations in a way to get this to work, but I'm not enough of an expert with SQL Server to figure this out on my own. I guess the other way to do this is to write some small script or application to do this, but I suspect there must be an easier way for those who know what they are doing. Any help on this topic would be greatly appreciated. Thanks.In SQL Server duplicates are eliminated by using Unique constraint or Index on the index you can add IGNORE_DUP_KEY option and make it all inserts because that option will not affect update statements. And they are very slow but the UNION operator also eliminates duplicates by applying implict Distinct. Run a search for all of the above in SQl Server BOL(books online). Hope this helps.|||For an import approach, I'd suggest inserting the records into atemporary table first. Then select out the unique records and insertthem into your destination table.
For the above query, would something like this work?
SELECT DISTINCT
PatientID,
MRNumber,
PatientFirstName,
PatientLastName,
PatientDOB,
PatientSex,
NewPatient,
...etc, etc, etc...
FROM
(
SELECT tblAppointments.PatientID, tblPTDEMO2.MRNumber, tblAppointments.PatientFirstName, tblAppointments.PatientLastName,
tblAppointments.PatientDOB, tblAppointments.PatientSex, tblAppointments.NewPatient, tblAppointments.HomePhone,
tblAppointments.WorkPhone, tblAppointments.Insurance1, tblPTDEMO2.Ins1CertNmbr, tblAppointments.Insurance2,
tblPTDEMO2.Ins2CertNmbr,tblAppointments.Insurance3, tblPTDEMO2.Ins3CertNmbr,tblAppointments.ApptDate, tblAppointments.ApptTime
FROM tblAppointments CROSS JOIN
tblPTDEMO2
WHERE (tblAppointments.PatientID = tblPTDEMO2.MRNumber)
AND tblAppointments.Insurance1 = 'MED'
AND tblAppointments.ApptTypeID <> 'MTG'
AND tblAppointments.ApptTypeID <> 'PNV'
AND DateDiff("dd", ApptDate, GetDate()) = 0
Order By tblAppointments.ApptDate
) AS SQ|||

I have re-written this a bit to remove the CROSS JOIN syntax which I think is less efficient than the INNER JOIN that is implied in the WHERE clause:
SELECT A.PatientID, D.MRNumber, A.PatientFirstName, A.PatientLastName, A.PatientDOB, A.PatientSex,
A.NewPatient, A.HomePhone, A.WorkPhone, A.Insurance1, D.Ins1CertNmbr, A.Insurance2,
D.Ins2CertNmbr, A.Insurance3, D.Ins3CertNmbr, A.ApptDate, A.ApptTime
FROM tblAppointments A INNER JOIN tblPTDEMO2 D on A.PatientID = D.MRNumber
WHERE A.Insurance1 = 'MED'
AND A.ApptTypeID <> 'MTG'
AND A.ApptTypeID <> 'PNV'
AND DateDiff("dd", ApptDate, GetDate()) = 0
Order By A.ApptDate

Now, I assume that the duplicates are in thetblPTDEMO2table. Tell me more about these: are the IDs duped?

|||Thanks for your response. The query you wrote is conceptually what I am trying to get, but it throws a syntax error. I've played with a number of variations of this and I can't seem to get it to work.
The other option of using a temporary table during the DTS package is viable, but once again, I can't seem to get the query right. I think what I want is something like:
Insert Into tblAppointments AppointmentKey, PatientFirstName, PatientLastName, ...VALUES (Select DISTINCT AppointmentKey, PatientFirstName, PatientLastName, ...)
but once again, I can't seem to get the syntax correct. Any help would be greatly appreciated. Thanks.|||Thanks for your response. Actually, the tblAppointments table is the one with the duplicates, and yes, the IDs are duplicated as well.

Duplicate members in parent-child dim + writeback

Hello,

I have this problem: I need duplicate members in parent-child dim. I searched web and i find this solution http://www.sqlserveranalysisservices.com/OLAPPapers/DuplicateMembers.htm by Richard Tkachuk. I'm using dimension in cube. And I want to use writeback to this cube. And that is the problem.

For example:

dimension structure employee

Boss Fred

Michael

Sam Will

Rachel Sam1 Will1

For the second occurrence of Sam and Will I changed Unique Name and add calculation to get right value from fact table.

But when I use UPDATE CUBE ... and change value of Will, Will and Will1 will have different values. Will the old one and Will1 the new one.

Can anybody help me, how to use duplicate members with writeback ?

Thank's

Erik

(Please excuse me my English.)

From your description it sounds like what is happening is that the "Will" member is the one with the formula and that the "Will1" member is the original member and that when you are doing the write back it is overwriting the formula, not writing into the original member. So if you could check if the member has a "Source Member" before writing back and either block the write back or re-direct the write back to the "Source Member" you should be able to get this working.|||

I know about this solution, but i'm trying to find solution without checking property 'Source Member'. From my aplication it isn't problem, but I want to use this dimenion from other clients too.

Thank's

Erik

|||I am not aware of any other solution, the writeback facility is just not that sophisticated. You can't intercept writebacks and the writeback cannot allocate back through a formula (which would be nice, but you can imagine how complicated it could get)|||

Thank's anyway..

Erik

Dundas Web Chart Control

I'm having trouble understanding how to build my bar chart. I have a column of info called Issue and then I'm coutning the record.

I bascially need to alter my query into time periods. I need a series for quarter 1 (1/1/2006 - 3/1/2006), one for quarter 2, 3, 4 and the entire year as a whole.

Can this data all be organized in a single select query?

Here is my current query getting the year as a whole data only:

SELECT

Issue,COUNT(*)AS NumRecordsFROM CSP_Item CI

INNER

JOIN CSP_ProblemNotification CPON CP.ID= CI.CSPNum

WHERE

CP.PNDateBETWEEN'01/01/2007'AND'01/31/2007'

GROUP

BY IssueORDERBY NumRecordsASC

Maybe...

SelectDatePart(quarter, theDate) as Quarter.

group byDatePart(quarter, theDate)

Dump Excel Sheets

I need to make a gigantic collection of excel sheets searchable from a web interface.
I needentire rows fetched into a webpage depending on the users query.

Mind you I'm not searching the names (filenames) of these excel files, but
the contents inside the excel file. E.g. in a file test.xls, if I search for the word
"test1" from the web interface, the entire row in the excel file containing
the word test1 should be displayed.

One way out is to dump the contents of the excel sheets into a database, and
search the database via ASP.

For this, can anyone tell me how to efficiently dump the contents of an excel
sheet into SQL server?

Or if anyone can suggest an alternate strategy for searching this mammoth
collection of excel files' contents, if would be great.

Thanks a lot.

To dump excel data into database, you have serveral options: DTS (Import/Export Wizard), bcp utility, BULK INSERT, etc. Seach with these key words in SQL Books Online, you can find how to use them.

Dumb questions on attribute relationships and multiple hierachy relationships

Hi,

This is a couple of general questions about how to create robust attribute hierarchies. I'm sure the answera are somewhere on the MS web sites, but I can't find it.

1) How precisely does the inclusion of attribute relationships affect the design of aggregations and the resulting speed?

I'm led to believe that defining the attribute relationships will effectively force AS to use these hierarchies when creating aggregations, and so putting them in improves the speed. This seems to be the case - any known best practices?

2) What do all the properties such as "Fully Optimised" for attributes mean?

3) Imagine have, for example, a time dimension table which has Year, Month, Week, Date columns on it, where weeks do not roll into months, but weeks and months roll into years. I build two user defined hierarchies Year-Month-Date and Year-Week-Date, then when I try to put attribute relationships on both these tables, I can get odd results, for example all the weeks appearing in the first year only.

This may be finger trouble, or complete stupidity, but has anyone else seen this? The actual case was not time as above, but was a similar structure.

As ever, thanks for all the input.

Richard

1) If you defined relationship between say your State and your City. And if you are asking for the State data, you can get answer from aggregation that contains City, or from cache created as result of City request. Now if you've created relationships for all the attributes in your dimensions, you need few aggregations to cover big space of potential requests. And therefore you can achieve performance. It is almost imperative to create attribute relationships.

2) Fully Optimized tells the server whenever to build indexes for this attribute or not. Sometimes you might want your attribute to exist but to be hidden. In this case makes sense to tell the server not to create indexes for it.

3) Not sure what you mean here. If you have correct relationships in the relational database, you should get right results when you browse you cube. Please note, when you define your relationships Analysis Server will try and find a parent for every individual week member. For instance if you have "Week 1 Dec 2001" it will have Dec 2001 parent in Week-Month hierarchy , or 2001 in Week-Year hierarchy.

Edward Melomed (MSFT)

---

This posting is provided "AS IS" with no warranties, and confers no rights.

|||To add a comment on 3: This normally indicates a problem with a key. AS 2005 is very sensitive with keys. "1" may be a bad key for a week... "200601" is a good key... (the same applies to months)...|||

refering to question #3

It sounds like you have not defined the key correctly. It needs to be unique.

so in the case of your hierarchy of year>month>week>date you need to define the key

for month and week to also include the year. In AS2005 you cannot rely on the hierarchy

to define a unique attribute. If you do a search on time dimension you should get much

more details on how to correctly define it.

Dumb questions on attribute relationships and multiple hierachy relationships

Hi,

This is a couple of general questions about how to create robust attribute hierarchies. I'm sure the answera are somewhere on the MS web sites, but I can't find it.

1) How precisely does the inclusion of attribute relationships affect the design of aggregations and the resulting speed?

I'm led to believe that defining the attribute relationships will effectively force AS to use these hierarchies when creating aggregations, and so putting them in improves the speed. This seems to be the case - any known best practices?

2) What do all the properties such as "Fully Optimised" for attributes mean?

3) Imagine have, for example, a time dimension table which has Year, Month, Week, Date columns on it, where weeks do not roll into months, but weeks and months roll into years. I build two user defined hierarchies Year-Month-Date and Year-Week-Date, then when I try to put attribute relationships on both these tables, I can get odd results, for example all the weeks appearing in the first year only.

This may be finger trouble, or complete stupidity, but has anyone else seen this? The actual case was not time as above, but was a similar structure.

As ever, thanks for all the input.

Richard

1) If you defined relationship between say your State and your City. And if you are asking for the State data, you can get answer from aggregation that contains City, or from cache created as result of City request. Now if you've created relationships for all the attributes in your dimensions, you need few aggregations to cover big space of potential requests. And therefore you can achieve performance. It is almost imperative to create attribute relationships.

2) Fully Optimized tells the server whenever to build indexes for this attribute or not. Sometimes you might want your attribute to exist but to be hidden. In this case makes sense to tell the server not to create indexes for it.

3) Not sure what you mean here. If you have correct relationships in the relational database, you should get right results when you browse you cube. Please note, when you define your relationships Analysis Server will try and find a parent for every individual week member. For instance if you have "Week 1 Dec 2001" it will have Dec 2001 parent in Week-Month hierarchy , or 2001 in Week-Year hierarchy.

Edward Melomed (MSFT)

---

This posting is provided "AS IS" with no warranties, and confers no rights.

|||To add a comment on 3: This normally indicates a problem with a key. AS 2005 is very sensitive with keys. "1" may be a bad key for a week... "200601" is a good key... (the same applies to months)...|||

refering to question #3

It sounds like you have not defined the key correctly. It needs to be unique.

so in the case of your hierarchy of year>month>week>date you need to define the key

for month and week to also include the year. In AS2005 you cannot rely on the hierarchy

to define a unique attribute. If you do a search on time dimension you should get much

more details on how to correctly define it.

Dumb question about compatibility level

Hi everyone,

This is my first psot so I am hoping for success..

I had been asked to web enable a sql 2000 database. We upgraded to sql 2005 and I imported the data. All went well until a tried to run a CTE ( common table ) routine and it kept on coming back with an error. On searching the web it turns out that the compatibility level must be set to 2005.

When I tried to set the compatibility level the only two options are sql 7 and sql 2000.

I thought it might be due to the fact I had restored from a 2000 database so I tried to create a new database in SSMS but again only sql 7 and 2000 options were available from the drop down box..

What am I doing wrong ?

Am I not running in sql 2005 at all ?

Any help would be appreciated

PeterAnother note..

I tried using

sp_dbcmptlevel DairyManager , 90

and it kept on coming back with :-

'Valid values of database compatibility level are 60, 65, 70, or 80.'

if I tried

sp_dbcmptlevel DairyManager , 80
that worked fine..

Me thinks something is not quite correct here!

Please can someone help as I need to sort this out before any further development|||

Try running the following on your SQL Server instance:

SELECT @.@.VERSION

All being well then something like the following should be returned:

Microsoft SQL Server 2005 - 9.00.2153.00 (Intel X86) May 8 2006 22:41:28 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

If the version returned is 'Microsoft SQL Server 2000 etc...', and if you performed a side-by-side upgrade when you installed the 2005 instance, then you are probably inadvertedly using the old 2000 instance instead of the new 2005 instance - this would account for the problems you are experiencing.

Chris

|||

This is on SQL 2005 Server? That will be interesting

We restored our SQL2000 database files onto a newly installed SQL2005 Server, change compatibility modes in SSMS to 9.0 (from 8.0) no problem

Helps to list your environment, SQL version, SP1/SP2, blah blah...

|||

It really sounds like you are running SQL Server 2000. If you did an in place upgrade from 2000 to 2005, the default instance gets upgraded to 2005. If you installed a named instance of 2005 on the same machine where 2000 was running, maybe you are accidently connecting the the default instance that is still running SQL Server 2000.

Running @.@.VERSION will settle the question. Also the version number of the SQL instance you are connected to will show up in SSMS. If you are in a query window, it will say something like 9.0 SP1 down at the bottom, or in Object Explorer it will say SQL Server 9.0.3042 (for 2005 SP2)

duff page in web dev

my page calls a stored procedure......

but it does not work.....

<%@.PageLanguage="C#"MasterPageFile="~/Masterhowit.master"Title="Untitled Page" %>

<%@.ImportNamespace="System.Net.Mail" %>

<%@.ImportNamespace="System.Data.SqlClient" %>

<scriptrunat="server">

protectedvoid Button1_Click(object sender,EventArgs e)

{

//.....sql insert statement for stored procedure...make active?

string numberintextbox = TextBox1.Text;

string yippyaddress = Request.UserHostAddress.ToString();

string dateandtime = DateTime.Now.ToString();

SqlDataSource Datast1000 = new SqlDataSource();

Datast1000.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionStringtodatast1000"].ToString();

Datast1000.InsertCommandType = SqlDataSourceCommandType.StoredProcedure;

Datast1000.InsertCommand = "givemeinserting";

Datast1000.InsertParameters.Add("@.mobiles", numberintextbox);

Datast1000.InsertParameters.Add("@.ipaddress", yippyaddress);

The page collects mobile tele numbers......ipaddress.....and DateTimeStamp

well should.

The stored procedure works fine when executed from the database explorer and asks for the parameters all ok.

The connection string is fine and matches the web config......all compiles ok but at run time makes the berp noise

then my... if else.... throws exception to say no database entry made.

//--kickout---for no upload

int rowsAffected = 0;

try

{

rowsAffected = Datast1000.Insert();

}

catch (Exception ex)

{

// this error page says sorry error and sends message to admin about failure

Server.Transfer("erroratgiveme.aspx");

}

finally

{

Datast1000 =null;

}

if (rowsAffected != 1)

{

// this page says sorry there has been an error loading..try again

Server.Transfer("erroratgiveloading.aspx");

}

else

{

//happy days...go to giveme

Server.Transfer("giveme.aspx");

}

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

I feel the code is fine because I have played with it for hours.....made basic insert statements etc.

Now this all worked fine two months ago until I tried to build a second page with insert statements then

nada.

Left it whilst I learned how to use microsoft sql server management express. Now tried for days to find problem

with no luck......

Should anyone from up high be available i would be very greatfull

Does anyone know what this means below

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

The thread 0xb58 has exited with code 0 (0x0).

A first chance exception of type 'System.Data.SqlClient.SqlException' occurred in System.Web.dll

'WebDev.WebServer.EXE' (Managed): Loaded 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\st1000april7\c09871d0\14ed6cf9\App_Web_9nwdvyfr.dll', Symbols loaded.

A first chance exception of type 'System.Threading.ThreadAbortException' occurred in mscorlib.dll

An exception of type 'System.Threading.ThreadAbortException' occurred in mscorlib.dll but was not handled in user code

The program '[2652] WebDev.WebServer.EXE: Managed' has exited with code 0 (0x0).

Try to delete the Temporary files under the directory

'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files

and rebuild and try the application

|||

Try to delete the Temporary files under the directory

'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files

and rebuild and try the application

Delete all the folder under the specified path

|||

trying...trying..hold on|||

App_global.asax.compiled

App_global.asax.eo21_x31.0.cs

App_WebReferances.3-2oi4

App_Webreferances.compiled

hash.web

any of these?

Am i looking in the right place......did search for

TemporaryASP.NET Files

regards rich

|||

right found it and deleted all objects within the folder.....rerun application...still not doing

it...however I think you are close ......anything else to do?

|||

What error you are getting this time? Is it same please copy & paste the stack trace

|||

Well thats the problem.

there is no error.

asp thinks its done the job..... its just that no data is going into the db.

And there are no tipos......spent hours on that...

remember stored procedure works fine in database explorer.

Its as if my page data is all running fine its just not arriving at the database door.

I thought the page my have a corruption onit so made new page and deleted old one..still no good.

made an admin page and put onit a from view and gridview......all working fine at runtime and inserting data!

Also have form view on the fault page which is working fine.

its a no brainer

'WebDev.WebServer.EXE' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_32\mscorlib\2.0.0.0__b77a5c561934e089\mscorlib.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.

'WebDev.WebServer.EXE' (Managed): Loaded 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\WebDev.WebServer.EXE', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.

'WebDev.WebServer.EXE' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_32\WebDev.WebHost\8.0.0.0__b03f5f7f11d50a3a\WebDev.WebHost.dll', No symbols loaded.

'WebDev.WebServer.EXE' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\System.Windows.Forms\2.0.0.0__b77a5c561934e089\System.Windows.Forms.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.

'WebDev.WebServer.EXE' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\System\2.0.0.0__b77a5c561934e089\System.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.

'WebDev.WebServer.EXE' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\System.Drawing\2.0.0.0__b03f5f7f11d50a3a\System.Drawing.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.

Warning: Cannot debug script code. Script debugging is disabled for the application you are debugging. Please uncheck the 'Disable script debugging' option on the Internet Options dialog box (Advanced page) for Internet Explorer and restart the process.

'WebDev.WebServer.EXE' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_32\System.Web\2.0.0.0__b03f5f7f11d50a3a\System.Web.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.

'WebDev.WebServer.EXE' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\System.Configuration\2.0.0.0__b03f5f7f11d50a3a\System.Configuration.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.

'WebDev.WebServer.EXE' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\System.Xml\2.0.0.0__b77a5c561934e089\System.Xml.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.

'WebDev.WebServer.EXE' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\System.Web.RegularExpressions\2.0.0.0__b03f5f7f11d50a3a\System.Web.RegularExpressions.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.

'WebDev.WebServer.EXE' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_32\System.Data\2.0.0.0__b77a5c561934e089\System.Data.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.

'WebDev.WebServer.EXE' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_32\System.Transactions\2.0.0.0__b77a5c561934e089\System.Transactions.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.

'WebDev.WebServer.EXE' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_32\System.EnterpriseServices\2.0.0.0__b03f5f7f11d50a3a\System.EnterpriseServices.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.

'WebDev.WebServer.EXE' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\Microsoft.JScript\8.0.0.0__b03f5f7f11d50a3a\Microsoft.JScript.dll', No symbols loaded.

'WebDev.WebServer.EXE' (Managed): Loaded 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\st1000april7\c09871d0\14ed6cf9\App_WebReferences.r9lj_2qs.dll', Symbols loaded.

'WebDev.WebServer.EXE' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\System.Web.Services\2.0.0.0__b03f5f7f11d50a3a\System.Web.Services.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.

'WebDev.WebServer.EXE' (Managed): Loaded 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\st1000april7\c09871d0\14ed6cf9\App_global.asax.lyflx3jz.dll', Symbols loaded.

'WebDev.WebServer.EXE' (Managed): Loaded 'C:\WINDOWS\assembly\GAC_MSIL\System.Web.Mobile\2.0.0.0__b03f5f7f11d50a3a\System.Web.Mobile.dll', Skipped loading symbols. Module is optimized and the debugger option 'Just My Code' is enabled.

'WebDev.WebServer.EXE' (Managed): Loaded 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\st1000april7\c09871d0\14ed6cf9\App_Web_zbstgaax.dll', Symbols loaded.

'WebDev.WebServer.EXE' (Managed): Loaded 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\st1000april7\c09871d0\14ed6cf9\App_Web_masterhowit.master.cdcab7d2.nufeebfc.dll', Symbols loaded.

A first chance exception of type 'System.Data.SqlClient.SqlException' occurred in System.Web.dll

A first chance exception of type 'System.Threading.ThreadAbortException' occurred in mscorlib.dll

An exception of type 'System.Threading.ThreadAbortException' occurred in mscorlib.dll but was not handled in user code

The thread 0x534 has exited with code 0 (0x0).

A first chance exception of type 'System.Data.SqlClient.SqlException' occurred in System.Web.dll

A first chance exception of type 'System.Threading.ThreadAbortException' occurred in mscorlib.dll

An exception of type 'System.Threading.ThreadAbortException' occurred in mscorlib.dll but was not handled in user code

'WebDev.WebServer.EXE' (Managed): Loaded 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\st1000april7\c09871d0\14ed6cf9\App_Web_2itmrd10.dll', Symbols loaded.

The program '[780] WebDev.WebServer.EXE: Managed' has exited with code 0 (0x0).

|||

Its not looking good we can try one more thing Uninstall the

aspnet_regiis.exe -ua

and then reinstall using

aspnet_regiis.exe -i

Make sure you are in C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\ directory before u run the commands. Send me the Code Behind as Well

I appriciate you patience

|||

mmm dont seem to have that file aspnet_regiis.exe -ua

I have four aspnet_regiis.exe files.......one of which is the 2.0.50727

search did not find ...aspnet_regiis.exe -ua

......so what is worst case sinario...delete and reload web dev

this all worked with sql insert statement in feb but suddenly lost the plot

|||

No the aspnet_regiis.exe is a file and

aspnet_regiis.exe -ua is command line parameters you need to supply when u execute from command line

and after that again from the command prompt run aspne_regiis.exe -i

|||

derr

|||

? what was that

|||

need help to run that.....got a mate who can help

but cant see him till friday.

going to have to put this on hold I think till then....going to mark post as answer for you

thanks so much for help.....ill post back here when Ive done it.

again thanks so much

rich

|||

don't worry we need to resovle this issue...

Dual Xeon or Dual P4 for SQL Server?

I am planning to build a server to be used as a SQL Server and web server.
Right now I can only use a single box for both.

I have read some threads were dual processors are having problems with some
parallel queries and the suggestions of having sql server use a single CPU.

My budget is limited so I am debating whether to get 2.6G dual xeon 533FSB
or dual P4 800FSB (DRR@. ram) or stick with a speedy single cpu.
If I get a dual cpu motherboard, is it a good idea to have 1 cpu used for
sql server and the other for everything else?

John DalbergOn Sun, 21 Nov 2004 07:14:17 -0800, John Dalberg wrote:

> I am planning to build a server to be used as a SQL Server and web server.
> Right now I can only use a single box for both.
> I have read some threads were dual processors are having problems with some
> parallel queries and the suggestions of having sql server use a single CPU.
> My budget is limited so I am debating whether to get 2.6G dual xeon 533FSB
> or dual P4 800FSB (DRR@. ram) or stick with a speedy single cpu.
> If I get a dual cpu motherboard, is it a good idea to have 1 cpu used for
> sql server and the other for everything else?
> John Dalberg

I forgot to mention also what about AMD's Athlon since I can get faster
Athlons for the same amount of money for Intel CPUs? Are dual Athlon
motherboards a good choice for SQL Server?

John Dalberg|||In article <u1ekecr53aij.1ek79hzro64q4$.dlg@.40tude.net>,
john_dd@.hotmail.com says...
> I am planning to build a server to be used as a SQL Server and web server.
> Right now I can only use a single box for both.
> I have read some threads were dual processors are having problems with some
> parallel queries and the suggestions of having sql server use a single CPU.
> My budget is limited so I am debating whether to get 2.6G dual xeon 533FSB
> or dual P4 800FSB (DRR@. ram) or stick with a speedy single cpu.
> If I get a dual cpu motherboard, is it a good idea to have 1 cpu used for
> sql server and the other for everything else?

John, you can't use Dual P4's any more, Intel fixed that after the P3.

If you want to build a cheap but fast SQL Server, assuming SQL 2000 or
higher, tape a look at the ASUS PC-DL Deluxe motherboard. I have about
30 of these running the following:

ASUS PC-DL Deluxe
Dua Xeon 2.4G (or faster) (make sure they are same lot/part)
2GB or 4GB of RAM (does not require ECC RAM)
It has onboard SATA and IDE, you can use a total of 6 drives between the
connectors without a problem.

The 2GB systems with Dual 250GB SATA drives, video, case, CD-ROM, etc...
are under $1900 each.

If you want a big box, like this:

PC-DL Deluxe
Dual Xeon 2.8ghz
2 x 80GB IDE (OS = 12GB, LOGS = 60GB)
6 x 250GB IDE (for DATA files)
Promise SX6000 Hardware IDE RAID Controller (hot swap IDE drives)
cheap video
Chenbro Server case with Dual 550W PSU's
and Windows 2003 Standard Server 5cal

This box runs about $4000

I run SQL 2000 on Dual P3 and Dual/Quad Xeon without any problems.

If you get a P4, get one that does Hyper-Threading, and use Server 2003
so that you get the most benefit from it.

As for Athlon, faster is a matter of what task is being performed and
how stable you want the system to be. When it comes to business systems
I use Intel CPU's only, but that's a personal preference and has never
cost me a job or stability.

--
--
spamfree999@.rrohio.com
(Remove 999 to reply to me)|||John Dalberg (john_dd@.hotmail.com) writes:
> I am planning to build a server to be used as a SQL Server and web server.
> Right now I can only use a single box for both.
> I have read some threads were dual processors are having problems with
> some parallel queries and the suggestions of having sql server use a
> single CPU.
> My budget is limited so I am debating whether to get 2.6G dual xeon 533FSB
> or dual P4 800FSB (DRR@. ram) or stick with a speedy single cpu.
> If I get a dual cpu motherboard, is it a good idea to have 1 cpu used for
> sql server and the other for everything else?

Machine configurations is not my best game, but if you are going to have
both SQL Server and web server on the same box, I would definitely go for
two CPUs. (Or at least one CPU that is hyper-threaded.)

As for SQL Server and parallel queries, yes, there is a potential problem.
I have seen more than once, SQL Server being too optimistic about parallel
query plans, and taken a plan with poor performance. What we usually do,
when we run into these queries is to add the query hint OPTION (MAXDOP 1)
which turns of parallellism for that query.

A more definitive cure is to set the configuration option "Max degress of
parllellism" to 1. Note that this is not the same as confining SQL Server
to one CPU only, but that one single query can only take one processor. Two
users executing different queries can still get one CPU each.

Giving SQL Server only one CPU *may* be a good idea, if you expect the
web server and the rest to need substantial amount of resources. But you
could also get the effect that one processor is mainly idle, while the
other is working around the clock.

So my recommendation would be set "Max degrees of Parallelism" to 1 (Or
to 2, if you have two hyper-threaded CPUs), but let SQL Server use both
CPUs.

Finally, there may be a licensing issue. I vaguely recall that for some
editions that you pay a license per processor, but please check this with
Microsoft. Licensing is definitely not my best game.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||In article <Xns95A8BE037B8EAYazorman@.127.0.0.1>, esquel@.sommarskog.se
says...
> Finally, there may be a licensing issue. I vaguely recall that for some
> editions that you pay a license per processor, but please check this with
> Microsoft. Licensing is definitely not my best game.

I was told by the MS People in the Great Lakes MS Office that HT CPU's
still count as 1 CPU for licensing.

One other thing, unless he's using SBS, I was also told that a Web
Server accessing a MS SQL Database, requires a CPU License on the
Database server for each physical CPU the SQL server is permitted to
use.

I have always licensed our SQL Servers in CPU mode when being accessed
by a web server using anonymous connections to the web server.

--
--
spamfree999@.rrohio.com
(Remove 999 to reply to me)|||Erland Sommarskog wrote:
> John Dalberg (john_dd@.hotmail.com) writes:
> > I am planning to build a server to be used as a SQL Server and web
server.
> > Right now I can only use a single box for both.
> > bob.mckenna@.rbc.com
> > I have read some threads were dual processors are having problems
with
> > some parallel queries and the suggestions of having sql server use
a
> > single CPU.
> > My budget is limited so I am debating whether to get 2.6G dual xeon
533FSB
> > or dual P4 800FSB (DRR@. ram) or stick with a speedy single cpu.
> > If I get a dual cpu motherboard, is it a good idea to have 1 cpu
used for
> > sql server and the other for everything else?
> Machine configurations is not my best game, but if you are going to
have
> both SQL Server and web server on the same box, I would definitely go
for
> two CPUs. (Or at least one CPU that is hyper-threaded.)
> As for SQL Server and parallel queries, yes, there is a potential
problem.
> I have seen more than once, SQL Server being too optimistic about
parallel
> query plans, and taken a plan with poor performance. What we usually
do,
> when we run into these queries is to add the query hint OPTION
(MAXDOP 1)
> which turns of parallellism for that query.
> A more definitive cure is to set the configuration option "Max
degress of
> parllellism" to 1. Note that this is not the same as confining SQL
Server
> to one CPU only, but that one single query can only take one
processor. Two
> users executing different queries can still get one CPU each.
> Giving SQL Server only one CPU *may* be a good idea, if you expect
the
> web server and the rest to need substantial amount of resources. But
you
> could also get the effect that one processor is mainly idle, while
the
> other is working around the clock.
> So my recommendation would be set "Max degrees of Parallelism" to 1
(Or
> to 2, if you have two hyper-threaded CPUs), but let SQL Server use
both
> CPUs.
>
> Finally, there may be a licensing issue. I vaguely recall that for
some
> editions that you pay a license per processor, but please check this
with
> Microsoft. Licensing is definitely not my best game.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

Dual Authentication with web and rs farm using remote sql server

I am having a problem with users being prompted to authenticate twice within
an application. I know this is related to the current design but for
scalability and isolation I want to keep the infrastructure design as is.
Currently I have two IIS 6 2k3 servers in an NLB. I have a web application
installed on this which is a front end for running reports. I then have two
IIS 6 2k3 servers running sql 2000 RS in an NLB farm. Finally there is a
Clustered SQL 2000 server where the application database and RS databases
exist. The application requires Basic w/SSL at the website level. This is
the first authentication. When the users go to run a report the first report
goes to the RS Farm and the users are authenticated once again. Once they
have authenticated by the web farm and the RS farm they no longer have to
authenticate but I'm trying to get it to a SSO.
From my experience, if I try using Integrated authentication then the UN and
Pass doesn't get passed onto the sql server and they cannot authenticate to
access the app database. If I try using integrated ont he RS farm I have an
issue where the UN and pass doesn't get passed to sql there. Based on what I
am seeing the fact that it requires 2 logons, 1 per farm, actually makes
sense but I would think this would be the most scalable and isolated design
you could have so it seems to me that there should just be a way for the SSO.
I would like to be able to do this at an admin or infrastructure design
level as the application is from a third party vendor and I don't want to try
and get them to recode anything. Any help would be appreciated.The problem was with Windows 2003 SP1 and IIS6.
Had to disable loopback check and then change RS to using Integrated
authentication. After that I was able to get in locally to /reports
http://support.microsoft.com/default.aspx?scid=kb;en-us;896861
"Chris Fauver" wrote:
> I am having a problem with users being prompted to authenticate twice within
> an application. I know this is related to the current design but for
> scalability and isolation I want to keep the infrastructure design as is.
> Currently I have two IIS 6 2k3 servers in an NLB. I have a web application
> installed on this which is a front end for running reports. I then have two
> IIS 6 2k3 servers running sql 2000 RS in an NLB farm. Finally there is a
> Clustered SQL 2000 server where the application database and RS databases
> exist. The application requires Basic w/SSL at the website level. This is
> the first authentication. When the users go to run a report the first report
> goes to the RS Farm and the users are authenticated once again. Once they
> have authenticated by the web farm and the RS farm they no longer have to
> authenticate but I'm trying to get it to a SSO.
> From my experience, if I try using Integrated authentication then the UN and
> Pass doesn't get passed onto the sql server and they cannot authenticate to
> access the app database. If I try using integrated ont he RS farm I have an
> issue where the UN and pass doesn't get passed to sql there. Based on what I
> am seeing the fact that it requires 2 logons, 1 per farm, actually makes
> sense but I would think this would be the most scalable and isolated design
> you could have so it seems to me that there should just be a way for the SSO.
>
> I would like to be able to do this at an admin or infrastructure design
> level as the application is from a third party vendor and I don't want to try
> and get them to recode anything. Any help would be appreciated.

DTSX Package failure returning 0

I have a Web App that I'm trying to launch a DTSX pack with. I wrote
a Stored Procedure that executes a job that launches the package.
CODE:
Execute msdb.dbo.sp_start_Job
'Import_Contractor_Employee_Data_to_CCTS
'
When everything is correct, it runs good. I found out this moring
that there is one potential situation that will cause the package to
fail. The problem is, when the package fails...with the above code,
it returns the below if I run it as a single line:
Job 'Import_Contractor_Employee_Data_to_CCTS
' started successfully.
If I execute it through the complete stored procedure...:
ALTER PROCEDURE [dbo].& #91;ASP_CCTS_RunImportContractorEmployee
_Job]
AS
DECLARE @.intErrorCode int
BEGIN
SET NOCOUNT ON;
Execute @.intErrorCode = msdb.dbo.sp_start_Job
'Import_Contractor_Employee_Data_to_CCTS
'
Select @.intErrorCode Error_Code
END
It returns a 0 as though nothing is wrong. What am I missing here?
ThanksHi Dave
You status says that the job started ok, not that it completed ok. Look at
sysjobs and you can see if it is currently executing and wait until it
finishes before checking the status.
John
"Dave" wrote:

> I have a Web App that I'm trying to launch a DTSX pack with. I wrote
> a Stored Procedure that executes a job that launches the package.
> CODE:
> Execute msdb.dbo.sp_start_Job
> 'Import_Contractor_Employee_Data_to_CCTS
'
>
> When everything is correct, it runs good. I found out this moring
> that there is one potential situation that will cause the package to
> fail. The problem is, when the package fails...with the above code,
> it returns the below if I run it as a single line:
> Job 'Import_Contractor_Employee_Data_to_CCTS
' started successfully.
> If I execute it through the complete stored procedure...:
> ALTER PROCEDURE [dbo].& #91;ASP_CCTS_RunImportContractorEmployee
_Job]
> AS
> DECLARE @.intErrorCode int
> BEGIN
> SET NOCOUNT ON;
> Execute @.intErrorCode = msdb.dbo.sp_start_Job
> 'Import_Contractor_Employee_Data_to_CCTS
'
> Select @.intErrorCode Error_Code
> END
>
> It returns a 0 as though nothing is wrong. What am I missing here?
>
> Thanks
>

DTSX Package failure returning 0

I have a Web App that I'm trying to launch a DTSX pack with. I wrote
a Stored Procedure that executes a job that launches the package.
CODE:
Execute msdb.dbo.sp_start_Job
'Import_Contractor_Employee_Data_to_CCTS'
When everything is correct, it runs good. I found out this moring
that there is one potential situation that will cause the package to
fail. The problem is, when the package fails...with the above code,
it returns the below if I run it as a single line:
Job 'Import_Contractor_Employee_Data_to_CCTS' started successfully.
If I execute it through the complete stored procedure...:
ALTER PROCEDURE [dbo].[ASP_CCTS_RunImportContractorEmployee_Job]
AS
DECLARE @.intErrorCode int
BEGIN
SET NOCOUNT ON;
Execute @.intErrorCode = msdb.dbo.sp_start_Job
'Import_Contractor_Employee_Data_to_CCTS'
Select @.intErrorCode Error_Code
END
It returns a 0 as though nothing is wrong. What am I missing here?
ThanksHi Dave
You status says that the job started ok, not that it completed ok. Look at
sysjobs and you can see if it is currently executing and wait until it
finishes before checking the status.
John
"Dave" wrote:
> I have a Web App that I'm trying to launch a DTSX pack with. I wrote
> a Stored Procedure that executes a job that launches the package.
> CODE:
> Execute msdb.dbo.sp_start_Job
> 'Import_Contractor_Employee_Data_to_CCTS'
>
> When everything is correct, it runs good. I found out this moring
> that there is one potential situation that will cause the package to
> fail. The problem is, when the package fails...with the above code,
> it returns the below if I run it as a single line:
> Job 'Import_Contractor_Employee_Data_to_CCTS' started successfully.
> If I execute it through the complete stored procedure...:
> ALTER PROCEDURE [dbo].[ASP_CCTS_RunImportContractorEmployee_Job]
> AS
> DECLARE @.intErrorCode int
> BEGIN
> SET NOCOUNT ON;
> Execute @.intErrorCode = msdb.dbo.sp_start_Job
> 'Import_Contractor_Employee_Data_to_CCTS'
> Select @.intErrorCode Error_Code
> END
>
> It returns a 0 as though nothing is wrong. What am I missing here?
>
> Thanks
>

DTS, SMO, SSIS

Database Transfer Services, replaced now by SMO now as I know..

In SQL Server 2005 is SSIS (SQL Server Integration Services).From Microsoft web page:SQL Server Integration Services, or SSIS, is an engine for building data import and export solutions and performing transformations on data as it is transferred.

pls. explain SSIS than? what is latest technology?

SSIS is the new, and very much improved, DTS.

SMO, or SQL Managment Objects, are used to automate common SQL Server functions, I believe...

DTS with ASP.NET

Hi. I need to execute my DTS package from a web page. Can this be done? If yes, can i please see some code.

Thank you !This site seems promising
or
maybe this site.