We are running two NT 2000 servers with two synchronized databases (DB1 as publisher, DB2 as subscriber), MS SQL 7, and IIS (ASP). Synchronization is done by immediately transactional replication. We have a radware switch ( www.radware.com ) between these two servers for load balancing and redundancy issues. The problem is folowing - when I open two windows (connections to database DB1 - publisher) and run the same SQL select command I get two different recordsets (differency is 50% or more in count record rows).
Any ideas how to solve this problem?
I think probably is this problem somehow tied with second:
error message for stored procedures running from ASP:
-2147467259, [Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user 'sa' '. SQL : exec business_update @BusPrimId='1230138980', @business_name='Software-test', @Contact_Name='Brian'
Appointments - There are 3 appointments in this table, which includes a 'date' field Notes - There are 2 notes in this table, which includes an 'appointment' field to relate the notes to their respective appointments and a 'new' field which shows that they have been read (or not).
The draft SQL below is designed to return all PAST appointments that have either unread notes (where new=1) or no notes at all.
SELECT appt.ref FROM (SELECT DISTINCT ref FROM Appointments WHERE DATEDIFF(day, date, '15 JUN 2004')>0) appt, Notes note WHERE (note.appointment=appt.ref AND note.new=1) OR NOT EXISTS(SELECT 1 FROM Notes note2 WHERE note2.appointment=17)
There are NO NOTES with '17' in the appointment field and NO NOTES with new equal to 1, so the SQL should return 3 results. I've used 17 just to test it.
For some reason, the SQL returns 6 results, even though there are only 3 appointments in the database.
I have a package that when I open it up, gives me this error:
Error 1 Error loading myPackage.dtsx: The connection "{AEDA784A-4076-4661-BC99-B4D819E0C21B}" is not found. This error is thrown by Connections collection when the specific connection element is not found.
However, I don't know what this error is possibly referrring to. All of my tasks, where needed, have the appropriate ole db connection and are NOT pointing to this non-existent connection. I deleted all connections except the one I use.
I addition, none of my tasks are showing ANY errors whatsover and package executes perfectly.
How do you avoid phantom inserts? Can you recommend a strategy (or other posts, articles) to avoid "phantom inserts" with the default SQL Server isolation level ("Read Committed").
I am seeing something strange. I have a data file that has 6 rows that looks like:
ABC, "OPENING BALANCE", 1234, etc
ABC, "CLOSING BALANCE", 1235, etc
ABC, garbage data, etc
XYZ, "OPENING BALANCE", 1234, etc
XYZ, "CLOSING BALANCE", 1235, etc
[][] -- weird box things that shows up in my flat file conn mgr
I have a script transformation that reads the incoming rows as a single line, then checks for the value of the row.line, whether it's "OPENING BALANCE", or "CLOSING BALANCE". It ignores all other lines.
I even added a message box that pops up when it finds "OPENING BALANCE" or "CLOSING BALANCE". It only pops up 4 times, like it should.
However, when I check the database, it has 6 rows! The 4 good rows are there, and 2 garbage rows with a bunch of NULLS in them.
I really don't understand how this is happening. Please, any ideas.
A fellow developer of mine has created a ASP.Net application that executes some fairly complex stored procedures. This application is for all intensive purposes a queue and 3 or 4 people work on processing items in a FIFO type environment. There is logic in the Stored procedures to make sure each worker get a unique entry using a series of query hints.
Well what we are seeing is if these works are moving at a rapid pace and we execute an sp_who2 on the sql server there are entries that that seems to be hanging there and REMAINING there even after a browser is closed or the disposed method has been called on the connection object. Has anyone else experienced something similar to this with an ASP.Net application used by mutiple people?
My inclination is to blame the design of the application, but before I do that and step on my co-workers toes I thought I would throw this out to the group.
Hi, Im getting this error when attempting to retrieve data from an sql database.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Cannot open database requested in login 'projectAllocations'. Login fails. Login failed for user 'sa'.
Source Error:
Line 13: objConn = New SqlConnection( "Server=LAB303-066NETSDK; Database=projectAllocations; User ID=sa;Password=mypassword") Line 14: objCmd = New SqlCommand("SELECT * FROM project_descriptions", objConn) Line 15: objConn.Open() Line 16: objRdr = objCmd.ExecuteReader() Line 17: While objRdr.Read()
Been looking through the forums for a solution to this problem.I already tried granting access through statements such as:exec sp_grantloginaccess N1'machineNameASPNET'But they don't seem to work.. i vaguely remember seeing somewhere a DOS command line statement that grants access to the ASPNET_WP and that fixed my problem before on another computer.. but this is a new computer and i forgot to write down the command.Can anyone help explain and propose a solution to my problem. Many thanxs.
I am using the MSDE to connect to my ASP.NET application. I get this error after clicking the login button of my login page. Anyone know why this would happen?
Thanks for any help,
Cannot open database requested in login 'DataSQL'. Login fails. Login failed for user 'serverASPNET'.
I have a sp that when executed inserts data into two tables(shown below). The sp works fine when the correct information is inserted into the tables but when you try and insert data that breaks the constraints in the table it obviously errors, but it seems to inert a new row in to the tmptimesheet table(error message shown below). When you open the tmptimesheet table there isn’t the row of a data there but if you run this (SELECT IDENT_CURRENT('tmptimmesheets') after the error it will come back with a id one higher than what’s in the table. Then the next time you run the sp a record will be inserted into the tmptimesheet table and not in to the tmptimsheethours table!? I’m at a total lost at what to do can someone please help!?
CREATE TABLE [dbo].[tmptimesheets]( [TimesheetID] [int] IDENTITY(1,1) NOT NULL, [Placementid] [int] NOT NULL, [Periodstarting] [datetime] NOT NULL, [createdon] [datetime] NOT NULL, [createduserid] [int] NOT NULL, [Issued] [nchar](1) COLLATE Latin1_General_CI_AS NOT NULL, [ReadyForBilling] [nchar](1) COLLATE Latin1_General_CI_AS NOT NULL, [Reject] [nchar](1) COLLATE Latin1_General_CI_AS NULL, [Comments] [text] COLLATE Latin1_General_CI_AS NULL, [Rate] [nchar](1) COLLATE Latin1_General_CI_AS NOT NULL, CONSTRAINT [PK_tmptimesheets] PRIMARY KEY CLUSTERED ( [TimesheetID] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY], CONSTRAINT [IX_tmptimesheets_1] UNIQUE NONCLUSTERED ( [Placementid] ASC, [Periodstarting] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
CREATE TABLE [dbo].[tmptimesheethours]( [TmpTimesheethourid] [int] IDENTITY(1,1) NOT NULL, [Timesheetid] [int] NOT NULL, [applicantid] [int] NOT NULL, [Workedon] [datetime] NOT NULL, [Hoursworked] [numeric](10, 2) NOT NULL, [performancevalueid] [int] NOT NULL, [Reject] [ntext] COLLATE Latin1_General_CI_AS NULL, [Breaks] [numeric](10, 2) NOT NULL, CONSTRAINT [PK_tmptimesheethours] PRIMARY KEY CLUSTERED ( [TmpTimesheethourid] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO USE [Pronet_TS] GO ALTER TABLE [dbo].[tmptimesheethours] WITH NOCHECK ADD CONSTRAINT [FK_TimesheetHours_Timesheets] FOREIGN KEY([Timesheetid]) REFERENCES [dbo].[tmptimesheets] ([TimesheetID]) GO ALTER TABLE [dbo].[tmptimesheethours] CHECK CONSTRAINT [FK_TimesheetHours_Timesheets]
Error Message Msg 2627, Level 14, State 1, Procedure sp_tmptimesheet_insert_day, Line 40 Violation of UNIQUE KEY constraint 'IX_tmptimesheets_1'. Cannot insert duplicate key in object 'dbo.tmptimesheets'. The statement has been terminated. Msg 547, Level 16, State 0, Procedure sp_tmptimesheet_insert_day, Line 65 The INSERT statement conflicted with the FOREIGN KEY constraint "FK_TimesheetHours_Timesheets". The conflict occurred in database "Pronet_TS", table "dbo.tmptimesheets", column 'TimesheetID'. The statement has been terminated. Msg 547, Level 16, State 0, Procedure sp_tmptimesheet_insert_day, Line 86 The INSERT statement conflicted with the FOREIGN KEY constraint "FK_TimesheetHours_Timesheets". The conflict occurred in database "Pronet_TS", table "dbo.tmptimesheets", column 'TimesheetID'. The statement has been terminated. Msg 547, Level 16, State 0, Procedure sp_tmptimesheet_insert_day, Line 108 The INSERT statement conflicted with the FOREIGN KEY constraint "FK_TimesheetHours_Timesheets". The conflict occurred in database "Pronet_TS", table "dbo.tmptimesheets", column 'TimesheetID'. The statement has been terminated. Msg 547, Level 16, State 0, Procedure sp_tmptimesheet_insert_day, Line 130 The INSERT statement conflicted with the FOREIGN KEY constraint "FK_TimesheetHours_Timesheets". The conflict occurred in database "Pronet_TS", table "dbo.tmptimesheets", column 'TimesheetID'. The statement has been terminated. Msg 547, Level 16, State 0, Procedure sp_tmptimesheet_insert_day, Line 153 The INSERT statement conflicted with the FOREIGN KEY constraint "FK_TimesheetHours_Timesheets". The conflict occurred in database "Pronet_TS", table "dbo.tmptimesheets", column 'TimesheetID'. The statement has been terminated. Msg 547, Level 16, State 0, Procedure sp_tmptimesheet_insert_day, Line 178 The INSERT statement conflicted with the FOREIGN KEY constraint "FK_TimesheetHours_Timesheets". The conflict occurred in database "Pronet_TS", table "dbo.tmptimesheets", column 'TimesheetID'. The statement has been terminated. Msg 547, Level 16, State 0, Procedure sp_tmptimesheet_insert_day, Line 200 The INSERT statement conflicted with the FOREIGN KEY constraint "FK_TimesheetHours_Timesheets". The conflict occurred in database "Pronet_TS", table "dbo.tmptimesheets", column 'TimesheetID'. The statement has been terminated. Msg 3902, Level 16, State 1, Procedure sp_tmptimesheet_insert_day, Line 223 The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.
Hi All,Have come across something weird and am after some help.Say i run this query where rec_id is a column of table arlhrl,select * from arlhrl where rec_id >= 14260This returns to me 2 records with rec_id's of 14260 and 14261Then I run this queryselect * from arlhrl where rec_id >= 14263This returns 7 records with rec_ids of 14263 up.How come the first query doesn't return the records returned by the2nd query also?If I select for 14262 no records are returned. It is like this is aphantom record or has an end of file character in it.I tried re-creating the indexes but to no avail. If anyone has anyideas about what could be causing it or how to fix it it would be muchappreciated.Thanks in advance,Andrew
hi, so i have a new box and I'm trying to get my websites and SQL Server 2005 Standard Edition working on it, but the pages give me the following error when I try to load them: "Cannot Open Database "XXXX" requested by login. The login failed. Login failed for user 'xxx'" Everything seems exactly the same settings and user-wise from my old box to my new one, but nevertheless everything I've tried gives me the same error. I've tried creating new users in SQL Server and giving them appropriate permissions to my database. I've even tried just using the built in 'sa' account. Nothing seems to change the error, except when I give it the incorrect password then it just says 'login failed' This leads me to believe that i'm successfully logging into the SQL Server, but it doesn't want to give me access to the database I'm requesting access too. But "apparently" the account i'm using should have access to the database. If nothing else the 'sa' account should, but that didn't work either. I'm stumped. Any ideas?
I typically use DataAdapters and Datasets from the toolbox when connecting to a SQL database. Since switching to VS 2005, I am not able to find (or add) these items to the tool box for web site development. Any ideas on how I can get them back? Or is there a better alternative to retrieving the data so I can process it? Any help or suggestions would be appreciated.
Can anyone advise me on how to view the contents of a dataset in VS dotnet 2003..
I had filled a dataset from a query...But i don know what happens after filling the qry results.It might be helpful if someone tells me to view the contents of the dataset
I have become a big fan of the datasets in Visual Studio 2005. I usually create the SQL for each method in the table adapter; however, I am wondering if there is any 'built-in' functions in the C files for sql injection prevention? I have read that using stored procedures is a good method for prevention. Should I be using SP rather than SQL within my methods in the data table?
Is there any possibility of combining data from two datasets to a single report item. If there is any thing that we can do, can any one please tell me what to do, so that my problem will be solved.
I have a table which has over 450,000 records in it, I have now split this into 4 so each table has around 100,000 records in it but I'm still having the problem of the data being returned really slowly.
What I need to do to this data is group it by a code and show the total for each code for every month of the year (this is currently based on one column and selecting the data accordingly). I have created views and put some indexes onto my table but the results are still being returned slowly. Does anyone have any suggestions of how I can speed this up?
I have a report with four different datasets that slice certain record by different days of the week. I can do the individual summing for each dataset by putting a , "datasetname" at in the expression. I also have 4 matrixes in the report that are grouped on certain transactions within each timeslice. My problem is that if I use the dataset name in the fields, the groups get ignored. Do I have to put the dataset name in each group expression? for example, here is my group expression...
Hello all, I have a report with a table and a chart. It uses dataset1 as the data source. All works fine. I create a new dataset called dataset2. The queries are exactly the same. The only differences between the 2 datasets is the database server and the fact that one of the columns is a smallint (in dataset2) and an int(in Dataset1) I change the datasetName property of both the table and the chart to use dataset2. When I run the report I get a conversion error stating that there was an overflow of int2 while using dataset1. I have verified the report is not using dataset1 anywhere. If I delete dataset1 and run the report the error goes away. If I add it back, I get the error again. Why is the report looking at dataset1 if it is not referenced at all in the report? Does SQL RS cache the datasets and verify each when it compiles?
If I have a dataset that has ID and CODE, and another dataset that has CODE and DESCRIPTION, is it possible to relate these two datasets to one another so I put the ID and Code in columns 1 and 2 and then put the related DESCRIPTION in column 3 on the report table layout? In other words, can you "JOIN" two datasets, or must all that be done in the query and create a unified dataset for the report contents?
Each column right now is a subreport so I can use the object as a parameter to the subreports dataset. Each column needs to have the object as a parameter.
Is there a way I can pass a field from the first dataset as a parameter for the next dataset without using subreports?
I have got two datasets. Some 'normal' rows with state values and an addtional dataset with the translation from state value to plain text (value / text pair).
A table object is attached to the first dataset, but I would like to show the plain text from the second dataset in a group header. So I'd like to do some kind of 'look up in another dataset'.
I wasn't able to find a function that could help me solve this problem. Does anyone has any ideas on this?
i have a report in which i use two datasets the problem is that when i was using only one, there wasn't any scope argument you know, to tell in which dataset i am , because there was only one and all fields and cells in my table returned me the correct values
but as soon as i add the second data set and i drag and drop the datasets fields in my table
Can we use 2 datasets in one report (tabular or chart, either one)?
my report get its data-fed from OLAP database (Cubes) and Oracle tables.
Let say the cube contains the actual sale (all aggregated and query using MDX) and the Oracle table contains the budget value for specific period.
And I am trying to construct a tabular like this:
date item type Revenue Budget Actual (Sale - Budget) Jan calls regular 2000 1800 200 Jan calls super 3000 2500 500 Feb calls new 1000 900 100 Mar calls super 4500 4000 500 ..... and so on
The data for date, item, type and Revenue are available in the OLAP (cubes) and the Budget value is available in Oracle table and we can't afford to transfer the Budget value to the cube.
Is this possible in SSRS? Please let me know if more information needed.
I'm creating a report with SSRS (2005). In that report I have a table, and in that table I need to put fields from two different datasets. But I don't know how to link those both datasets (because if I don't I get a cartezien product)...
is this possible? i need to make a report that accesses two different tables, one for user activity and the other for the number of reports that user has done. the report information is in a different database however so i have to use different datasets. is there a way to use both databases in the same table? all i need is the one field from the reporting one and they can be linked by the user id...
1. A mulitvalued parameter State with WA, SA, QLD, TAS etc
2. A single value parameter of Top n - 10, 20, 40
The user will select the state and the top number to get the top n sellers off that state or a combination off states.
I have a dataset with the following cutdown version off SQL
Declare @TopNo Int
Set @TopNo = @Param_TopNo
Set Rowcount @TopNo
SELECT CreateDate,SaleDate,sales.Consultant, State, Sum(Value) As Winbacks
FROM dbo.tbl_kpi_sales
GROUP BY CreateDate, Consultant, State, SaleDate, Winbacks
ORDER BY Winbacks Desc;
Set RowCount 0
When I execute it and select 'Select all' from the mulitvalued state parameter with a Top N value off 100 the query returns all the top 100, which is correct, however when i select just a single state ie WA it only returns me the records which were in the TOP 100 off all states when it should be filtering it by only the state I choose and returning the 100 records...For some reason it when a single state is selected or a combination off states the dataset is already narrowed down and doesnt reexecute the query for just that state...Can someone suggest how to overcome this, maybe a table filter ?
I am relatively new to MS Reporting and I can€™t figure out how to do something that seems rather simple.
I need to somehow combine two different SQL statements into one dataset. The two different SQL statements are the same, except for the Select list and the Where.
Example,
1st Dataset:
SELECT dbo.claimtable.grpnum_plancode, dbo.plancodetable.plandescription, SUM(dbo.claimtable.charges) AS Charges, SUM(dbo.claimtable.benefitpaid) AS BenefitPaid FROM dbo.claimtable INNER JOIN dbo.plancodetable ON bo.claimtable.grpnum_plancode = dbo.plancodetable.grpnum_plancode WHERE (dbo.plancodetable.plantype = 'Med') AND (dbo.claimtable.paiddate BETWEEN @BeginDate AND @EndDate) AND (dbo.claimtable.paidflag = 'Y') AND (dbo.claimtable.grpnum IN (@GroupNumber)) GROUP BY dbo.claimtable.grpnum_plancode, dbo.plancodetable.plandescription
2nd Dataset:
SELECT dbo.claimtable.grpnum_plancode, dbo.plancodetable.plandescription, COUNT(distinct dbo.claimtable.claimid) AS ClaimCount FROM dbo.claimtable INNER JOIN dbo.plancodetable ON dbo.claimtable.grpnum_plancode = dbo.plancodetable.grpnum_plancode WHERE (dbo.plancodetable.plantype = 'Med') AND (dbo.claimtable.paiddate BETWEEN @BeginDate AND @EndDate) AND (dbo.claimtable.paidflag = 'Y') AND (dbo.claimtable.backoutflag = 'N') AND (dbo.claimtable.grpnum IN (@GroupNumber)) GROUP BY dbo.claimtable.grpnum_plancode, dbo.plancodetable.plandescription
I tried just referring to the different dataset within the report table, but it only provides it at the aggregate level and I need the claimcount at each individual claimtable.grpnum_plancode level.
I tried a union statement, but get a variety of errors.
Hi, I am pretty new to harcore ASP.NET and .NET in general but I know the basics of the language and stuff like that. Basically I used to be a hardcore object pascal Delphi developer doing Windows Applications but have now moved to .NET world. The reason I posted this question here is I couldn't find any other specific place to ask so here it goes. To get me started with good programming source and practices I downloaded the ASP.NET TimeTracker Starter Kit and after some modificaion in connection string (using SQL 2005 Developer edition not the Express one) I have managed to make it work. Things done in there are pretty interesting way by using a delegate to retrieve data and present it using databound controls. As much simple as it sounds I am more of a fan of not using too many databound controls except in dropdown box, list box and stuff like that. So basically I need to develop DataAccessLayer (DAL) in such a way that it's useful for both Windows and Web application. So with my research I found that I should be using DataSets in the DAL because they are serializable and thatz what Web Applications & Services love over using SqlDataReader. Other advantage of using DataSet is to use ForEach syntex over While Loop in SqlDataReaders. So below is some code I extracted from the TimeTracker Starter Kit to get me started. I also noticed that the return result is a list array of certain object, in this case Category. So does it mean that it can be used in frontend using ForEach syntex? How can I convert the code below to use DataSets over SqlDataReader? Private Delegate Sub TGenerateListFromReader(Of T)(ByVal returnData As SqlDataReader, ByRef tempList As List(Of T))
Public Overrides Function GetAllCategories() As List(Of Category) Dim sqlCmd As SqlCommand = New SqlCommand() SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_CATEGORY_GETALLCATEGORIES)
Dim categoryList As New List(Of Category)() TExecuteReaderCmd(Of Category)(sqlCmd, AddressOf TGenerateCategoryListFromReader(Of Category), categoryList)
Return categoryList End Function
Public Overrides Function GetCategoryByCategoryId(ByVal Id As Integer) As Category If Id <= DefaultValues.GetCategoryIdMinValue() Then Throw New ArgumentOutOfRangeException("Id") End If
Dim sqlCmd As SqlCommand = New SqlCommand() AddParamToSQLCmd(sqlCmd, "@CategoryId", SqlDbType.Int, 0, ParameterDirection.Input, Id) SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_CATEGORY_GETCATEGORYBYID)
Dim categoryList As New List(Of Category)() TExecuteReaderCmd(Of Category)(sqlCmd, AddressOf TGenerateCategoryListFromReader(Of Category), categoryList)
If categoryList.Count > 0 Then Return categoryList(0) Else Return Nothing End If End Function
Private Sub TGenerateCategoryListFromReader(Of T)(ByVal returnData As SqlDataReader, ByRef categoryList As List(Of Category)) Do While returnData.Read()
Dim actualDuration As Decimal = 0 If Not returnData("CategoryActualDuration") Is DBNull.Value Then actualDuration = Convert.ToDecimal(returnData("CategoryActualDuration")) End If
Dim category As Category = New Category(CStr(returnData("CategoryAbbreviation")), actualDuration, _ CInt(returnData("CategoryId")), CDec(returnData("CategoryEstimateDuration")), CStr(returnData("CategoryName")), _ CInt(returnData("ProjectId"))) categoryList.Add(category) Loop End Sub Hope this makes sense. Sorry if I have posted this in a wrong section..! Cheers,Nirav
I was watching the "To Do List" AJAX video created by Joe Stagner and while watching the video I noticed (in designer) Joe use a Dataset to populate a Gridview and this got me thinking. I have a few gridviews in my app at work that just use SqlDataSources, and they seem to work fine, but is it better to use Datasets ?
