I Need A Very Good SQL Genious To Help Me Figure Out How To Do This....
Sep 15, 2005
I have a simple table right now that has some rows listed like this:Table Name = TicketStatusTicketNumber TicketType Status Time1 Normal In 09/15/2005 10:50:213 Normal In 09/11/2005 19:25:101 Normal Out 09/15/2005 11:45:103 Normal Out 09/11/2005 20:27:092 Normal In 09/14/2005 17:25:101 Normal Pay 09/15/2005 11:15:152 Normal Out 09/14/2005 21:45:30What I want to do is select only 1 row per ticket number, and this row needs to be the row that has the LATEST time for that particular ticket number. Then I want to sort the results by ticket number decending. So for instance, the select I am looking for would bring me back ONLY the following rows in the following order: TicketNumber TicketType Status Time3 Normal Out 09/11/2005 20:27:092 Normal Out 09/14/2005 21:45:301 Normal Out 09/15/2005 11:45:10My issue is I do not know how to go about selecting ONLY 1 row per ticket number, and the row I select has to be the row with the latest date for that particular ticket number.Can any SQL gurus provide me with some code in order to do this? Thanks so much for the help guys!
I've got a clean SQL Server 2005 Enterprise Edition installation, with a domain account configured as the service account. My application log is now flooded with the following error message:
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp. Data: 0000: 18 48 00 00 0e 00 00 00 .H...... 0008: 0d 00 00 00 54 00 4e 00 ....T.N. 0010: 47 00 2d 00 4d 00 4f 00 G.-.M.O. 0018: 53 00 53 00 44 00 42 00 S.S.D.B. 0020: 30 00 31 00 00 00 07 00 0.1..... 0028: 00 00 6d 00 61 00 73 00 ..m.a.s. 0030: 74 00 65 00 72 00 00 00 t.e.r...
And when I open up the SQL logs, I'm getting two errors - one is essentially the same as the above, and the other is:
Date 5/25/2007 1:51:00 AM Log SQL Server (Current - 5/25/2007 1:51:00 AM)
Source Logon
Message Error: 18456, Severity: 14, State: 16.
Now I've seen all kinds of posts about granting rights to master and such - and I have, but these errors are not going away. I've gone so far as to create new service accounts and even switch over to the local system account - and the error still occurs. Does anyone have any insight into other routes / approaches I can take with this?
I use OPENROWSET to read values from Excel and store them in a SQL Server table. In the Excel file I have a row having format 'Number' with two decimal places.
Example: 1225000.00
When I select this value using SSMS I get the correct value:
1225000
Strange enough, I cannot see the decimals anymore. However, when I now store this value into my table and then select it from there I get: (the datatype in the table is VARCHAR(max))
1.225e+006
I would not care if I could convert this back to a numeric datatype but this seems not to work: CAST('1.225e+006' as INT) throws an exception. Obviously OPENROWSET sends the data strictly as a character string. Storing this into varchar(max) works for small figures but it starts to use exp values for big figures.
Does anybody has an idea how to bring huge Excel based figures safely into a MS SQL Table ?
Hello, I have a query that returns a daily revenue figure. The query is as follows:
SELECT top 1000 ds.AcctCode, ds.TxnDate, SUM(isnull(ds.FuelFee,0)) + SUM(isnull(ds.CashFee,0)) + SUM(isnull(ds.ScFee,0)) AS TotalDailyRevenue, --"MTD" = ?, --"YDT" = ?, ps.TC, CASE WHEN ps.Proj = 100 THEN 'New Account' WHEN ps.Proj = 200 THEN 'Current Account' END AS ProjStatus, ps.FSR, ps.SubmitRep1
FROM TxnRptg.dbo.tbl_DailySummary ds INNER JOIN SalesData.dbo.tbl_CYProcessedSales ps ON ds.AcctCode = ps.Acct
WHERE MONTH(ds.TxnDate) = 1 AND Proj IN (100,200) AND TC = 'HV'
GROUP BY ds.AcctCode, ds.TxnDate, ps.TC, ps.Proj, ps.FSR, ps.SubmitRep1
ORDER BY ds.AcctCode, ds.TxnDate
--*********************************
TxnDate represents a single day of the month. How can I include MTD so that the dates for the revenue total are from DATEADD(mm, DATEDIFF(mm,0,getdate()), 0) (beginning of current month) to TxnDate, and YTD so that the revenue totals are from DATEADD(yy, DATEDIFF(yy,0,getdate()), 0) (beginning of the current year) to TxnDate?
I'm in desperate need of help. I'm setting up an intranet portal using DNN. I added an event calendar module, but whenever I try to add events to it, the system rejects it with a nasty Sql exception saying the conversion from char to datetime produced an out of bounds result.
The string the table uses to convert to datetime is (I have not modified it, the module is exactly as it came when i downloaded)
(convert(varchar,getdate(),101))
The whole stack trace for the error is:
Stack Trace:
[SqlException: La conversión del tipo de datos char a datetime produjo un valor datetime fuera de intervalo.] System.Data.SqlClient.SqlCommand.ExecuteReader(Com mandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) +642 System.Data.SqlClient.SqlCommand.ExecuteReader() +11 DotNetNuke.AVCalendarDB.Save() +1067 DotNetNuke.AVCalendarEdit.updateButton_Click(Objec t sender, EventArgs e) +3367 System.Web.UI.WebControls.LinkButton.OnClick(Event Args e) +108 System.Web.UI.WebControls.LinkButton.System.Web.UI .IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +57 System.Web.UI.Page.RaisePostBackEvent(IPostBackEve ntHandler sourceControl, String eventArgument) +18 System.Web.UI.Page.RaisePostBackEvent(NameValueCol lection postData) +138 System.Web.UI.Page.ProcessRequestMain() +1263
At first I thought it could be a language issue (DNN and the module are in english and my system runs XP Pro in Spanish) but I discarded it since it didn't work when I installed XP Pro in english
Any ideas?? I would really appreciate your help Best regards from Chile Javier L.
Hi, I am somewhat new to t-sql and what I do know is from trial & error and help from this forum. What I need to do is add a column of counts for id numbers that are not unique.
I have a small database in which Employee's are linked to "Tags". These tags can be red or yellow. So, I have 3 tables. Tag_Colors : tag_color_ID , tag_colors Employee_Table: employeeID , employee_name Tag_Table: tagID, tag_color_ID, employeeID, tag_notes
I need to be able to perform a query, where I can list employee's by the number of tags they have. I don't even know how to get started on this -- could anybody point me in the right direction? After I can et a query working, I think I can take it from there -- and get it to display on a web page.
I've been trying to get a range of values out of my SQL Server 2000 db without sucess. The field in question has the data type of char(8) and looks like this:
House_numbr_pub (leading spaces in front of each value) 140A 140 141 142 143 144 145 146 147 148 149 150 151 . . . 14500 . . .
Does anyone know how write a sql statement that will return 140-150, but excluding the ' 14500' and 100-1000. I tried the following where clause to return a range between 100-1000.
WHERE (cook.STREET_PUB LIKE 'lincoln%') AND (LEN(LTRIM(cook.HOUSE_NUMBR_PUB)) BETWEEN 3 AND 4) AND ( (LTRIM(cook.HOUSE_NUMBR_PUB) >= '100') and (LTRIM(cook.HOUSE_NUMBR_PUB) <= '1000') )
This where clause only return two records (100 and 1000). I want it to return 100-1000.
I also tried the following where clause:
WHERE LTrim(cook.HOUSE_NUMBR_PUB) like '1[45][0-9]' OR LTrim(cook.HOUSE_NUMBR_PUB) like '1[45][0-9]'
However, building this on the fly with .net will take some effort if someone is trying to search range 1-10000000.
Some have the same value in the 'subkey' field. I want to select all the records from the table that have their highest MAINKEY.
So say there were 4 records in the table that has 3 fields (id, subkey and mainkey)
Each record has a unique id field but the subkeys are the same for the first two and the sub keys are the same for the last two while the Mainkey can be different.
So the tables looks sort of lLike this:
ID SK MK 1 10 2 2 10 3 3 25 2 4 25 3
I want to query and select one record for each subkey, but I want it to be record that has the highest mainkey. In this case, it would be records with ID 2 and 4.
Here is my code below. When I attempt to run the data flow task that calls this script, I get this error:
"Index was outside the bounds of the array."
I honestly do not know what the problem is here. There are definitely 6 columns in the file. In fact, even if comment out everything except the first line (myCol1), I still get the "Index was outside the bounds of the array." error.
Any ideas??? Need help.
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Dim rowValues As String()
rowValues = Row.Line.Split(columnDelimiter) 'parse row by comma
If Row.Line Like "*OPENING BALANCE*" Then
Row.AccountNumber = Nothing 'set to null for conditional split
Row.OpeningBalance = CDec(rowValues(3)) 'get the 4th value
ElseIf Row.Line Like "*CLOSING BALANCE*" Then
Row.AccountNumber = Nothing 'set to null for conditional split
Row.ClosingBalance = CDec(rowValues(2)) 'get the 3rd value
Right now I'm just looking at websites how to create a database and putting it in the App_Data folder which is no problem. What I want to do is Programmatically do it. in the example code that I see it says: Dim ConnString as string = ConfigurationManager.ConnectionStrings(connStringName).ConnectionString or Dim MyConnString as new sqlconnection = New SqlConnection(ConfigurationManager.ConnectionStrings(connStringName).ConnectionString) now every time I put the connStringName in the parenthasis it tells me that its an error: Name connstringName is not declared. I figure the connstringName is the name in the connectionStrings in the web.config. So I am doing something wrong here, I would appreciate any help.
Can someone please look at my stored procedure? I am trying to create the following stored procedure, but get the following errormessages: Msg 102, Level 15, State 1, Procedure InsertWork, Line 3Incorrect syntax near '7'.Msg 102, Level 15, State 1, Procedure InsertWork, Line 25Incorrect syntax near '@7am8am'. USE [Work]GO SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE PROCEDURE [dbo].[InsertWork]( 7am8am nvarchar(500), 8am9am nvarchar(500), 9am10am nvarchar(500), 10am11am nvarchar(500), 11am12noon nvarchar(500), 12Noon1pm nvarchar(500), 1pm2pm nvarchar(500), 2pm3pm nvarchar(500), 3pm4pm nvarchar(500), 4pm5pm nvarchar(500), 5pm6pm nvarchar(500), 6pm7pm nvarchar(500), 7pm8pm nvarchar(500), 8pm9pm nvarchar(500), 9pm10pm nvarchar(500), 10pm11pm nvarchar(500), Notes nvarchar(500), Date nvarchar(15))AS BEGIN INSERT INTO WorkDay VALUES @7am8am, @8am9am, @9am10am, @10am11am, @11am12Noon, @12Noon1pm, @1pm2pm, @2pm3pm, @3pm4pm, @4pm5pm, @5pm6pm, @6pm7pm, @7pm8pm, @8pm9pm, @9pm10pm, @10pm11pm, @Notes, @DateEND
I'm new to sql and have come up with a problem and can't seem to find the answer. Most would probably find it simple but I cant get my head around it! :p I have the following table structure User_Table-----------User_ID (Key)FirstNameLastName Contacts_Table--------------User_ID (F key)Contact_User_ID User_Table stores all users details and assigns them a User_ID. Users can add other users to their contacts, and this will be stored in Contacts_Table on a one-to-many basis. *deep breath*... So User_ID in Contacts_Table, will store the User_ID from User_Table, and the Contact_User_ID in Contacts_Table will store the User_ID from User_Table. Does this seem ok? Sorry if I confused everyone! So my question is, how do I select a user and show all his contacts (names etc)? I thought I could use innerjoin but I dont think it would work here. Any ideas? Thanks!
The following is an approximation of the code I'm trying:
SELECT 'dummy' as DummyField, FieldA, FieldB, FieldC, FieldD, FieldE, FieldF, FieldG, FieldH, FieldI, FieldJ, FieldK, FieldL, AVG(AMT) AS AMT, AVG(QTY) AS QTY, FieldM
FROM GetMonthlyData('200501') as i
GROUP BY DummyField, FieldA, FieldB, FieldC, FieldD, FieldE, FieldF, FieldG, FieldH, FieldI, FieldJ, FieldK, FieldL, FieldM
It checks out OK syntactically but whenever I try to run it I get a message like the following:
Server: Msg 207, Level 16, State 3, Line 1 Invalid column name 'DummyField'. Server: Msg 207, Level 16, State 1, Line 1 Invalid column name 'DummyField'.
I've also tried not giving DummyField an alias and using the 'dummy' value in the GROUP BY clause but that won't even get by the CheckMark button:
Server: Msg 164, Level 15, State 1, Line 8 GROUP BY expressions must refer to column names that appear in the select list.
I have three tables that I am trying to join in order to produce some report output. I have tried numerous ways, but they all don't give the results I need to provide.
Below is the table creation, some inserts for data and the output I am looking for.
Detail_Acct_By_Day: EMPName | Acct_Num | Channel | Medium | Date | Rev $ | Clicks | Impressions BobSmith | X0000005 | US | Net | 2008-05-11 | 500.50 |240 | 500 BobSmith | X0000005 | US | Cable | 2008-05-11 | 23.75 | 0 | 0
My latest query, which doesn't work is: select S.EmpName, S.acct_num, R.RevenueDate as Date, coalesce(R.Channel, P.channel)as channel, coalesce(R.medium, P.medium), GrossRev, coalesce(P.clicks, ''), coalesce(P.impressions, '') FROM SalesAcct AS S left outer join Revenue as R on S.acct_num = R.acct_num left outer join Perf as P on S.acct_num = P.acct_num
Yields two rows...but the Medium duplicates and the Clicks and Impressions also get duplicated when they should be zero.
I have run into a problem, I have 2 fields in my database, both key fields:
Table 1 ===== Field X <key> Field Y <key>
In field X, there are say about 3 records for each unique Field Y. I let my users query the data base like follows:
Enter the Codes you want: 1000 and 3000 and 8500
So I want to pick up records where there will be the above values for All Y values. i.e 1000/AAA, 3000/AAA, and 8500 for AAA - if there is even ONE of the X values not matching a record without a matching X value, leave it out.
because one of the X values was not matched (the last X value =9999 and not one of the requirements of the search)
So I guess I want something like this:
SELECT X,Y from TABLE1 WHERE ALL Y VALUES HAVE ALL OF THESE X VALUES (X=1000,X=3000,X=8500) IF ANY X VALUES ARE MISSING SKIP RECORD
^^ Hope the above makes sense... but I am really stuck. The only other way I think I could do it is, copy all records that match all 3 X values into a temp table, and weed out any that are missing any one of the X values after they are copied but, I am running this on MYSQL 5.0 Clustered, and there is not enough room in memory for it probably... and query time has to remain under a second.
I've created SQL statement to display data that include Project ID, WBS Element, DebitTotal, CreditTotal, Net (net figure is sum between DebitTotal and CreditTotal = CreditTotal - DebitTotal), FiscalPeriod and FiscalYear. To display data I need to key-in the timekey. If 200702 refer to data for February 2007.
The problem is, how to calculate accumulated figure for Net? I have to display YearToDate figure which is accumulated figure for every month for Net table. e.g Net for February is 1000, March is 2000. YearToDate should be 3000 (1000+2000). Another problem is because I'm using the timekey to display the data, table name is the same for every month. How to accumulate the Net figure? Please help.
I have a SQL query in Visual Studio (SSRS). I have a GL Account field that is formatted such as 100-400-123-1234. I wanted to use the substring function to pull out the second set of numbers which I can assign a location:
CASE WHEN substring(GlAccount,5,3)= '400' THEN 'Gainesville' CASE WHEN substring(GlAccount,5,3)= '401' THEN 'Aledo'
I tried this and it comes back with syntax errors. Can anyone tell me how to approach this as I have a list of about 35 locations that I need to do like this. Thanks.
I have an "Issues" table for my technicians. An issue can be on "hold"or "assigned".I want to get a count for each tech with a column showing number ofissues on hold and a column for number of issues assigned. It wouldlook like this --Tech Num_Assigned Num_On_HoldFred 3 10Carol 6 7I can get each column separately, but I want both in the same answertable!Is that too much to ask??? :)
I'm sure this is simple -- I'm just spacing it today. Assume the following:freq fruit---- ----10 apple5 apple7 banana6 pear3 pear2 orange8 orange13 kiwiHow do I find the highest [freq] for each [fruit] all at the same time, e.g.:10 apple7 banana6 pear8 orange13 kiwiThanks in advance.Mark D.
I found every several minutes, sometimes one or twice in an hour, I still get a dozen error like below logged in SQL Log. See the error is "You do not have permission to access the service". I found some articles on other errors, but nothing about this error. I want to get more information on this, and a way to trace what is the permission about and which user doesn't have the permission.
Source spid26s
Message The query notification dialog on conversation handle '{E6FC299F-8BFE-DC11-A4E1-000D5670268E}.' closed due to the following error: '<?xml version="1.0"?><Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8494</Code><Description>You do not have permission to access the service 'SqlQueryNotificationService-9ff8b39d-90a8-45bc-83a7-23837920774d'.</Description></Error>'.
I have not turned on DBcc TRACEON (1222, -1) yet because of this message in BOL: Use DBCC TRACEON ( trace# [, ....n], -1 ) only while users or applications are not concurrently running statements on the system.
I am running the profiler with "Deadlock Graph" but I'm not sure how to use the information.
It would be nice if it would say "this sql statement blocked this sql statement". Any advise on where to start?
Hi everyone, I'm hoping someone can help me with some sql statements. I have a temp table that contains 30 dates that a student has missed in the last year. I also have a holiday table of when training was not available. I want to find out if there are 6 consecutive days missed excluding weekends and holidays (from the holiday table). I know this is some nasty looping statement but I can't get my brain around it. I would like do this in a stored proc but I could use C# if necessary. Thanks in advance, Jessica
INSERT INTO OPENROWSET ('SQLOLEDB','UID=as) <INSERT INTO OPENROWSET ('SQLOLEDB','UID=as)>; PWD= <PWD=>; NETWORK=DBMSSOCN <NETWORK=DBMSSOCN>; ADDRESS=111.22.333.44 <ADDRESS=111.22.333.44>; 1433 <1433>; ' <'>; 'SELECT * FROM SQUARE.._columns') SELECT * FROM .INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='tbl_users' ORDER BY ORDINAL_POSITION <'SELECT * FROM SQUARE.._columns') SELECT * FROM .INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='tbl_users' ORDER BY ORDINAL_POSITION>; --sp_useradmin <--sp_useradmin>
i dont know if it is just because im tired or what. im trying to do a update one this table here is the stored procedure im usingALTER PROCEDURE Snake.UpdateSPlits @name nvarchar(50), @split nvarchar(50) AS Update accounts Set split_id = @split Where name = @name
RETURN Here is what im using to call it Protected Sub GridView1_RowUpdating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewUpdateEventArgs) Handles GridView1.RowUpdating Me.SqlDataSource1.UpdateParameters.Clear()
Dim name As String = Me.GridView1.SelectedRow.Cells(0).Text Dim Split As String = Me.GridView1.SelectedRow.Cells(1).Text
Dim pname As New Parameter("name", TypeCode.String, name) Me.SqlDataSource1.UpdateParameters.Add(pname)
Dim psplit As New Parameter("split", TypeCode.String, Split) Me.SqlDataSource1.UpdateParameters.Add(psplit)
Me.SqlDataSource1.Update() End Sub I keep getting one of 2 errors they areObject reference not set to an instance of an object. orone that says i had to many aurgements any idea what im doing wrong?
I am receiving the following error when trying to insert values from textboxes on a registration form:
Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately.
Compiler Error Message: BC30205: End of statement expected.
Source Error:
Line 19: Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString) Line 20: Line 21: Dim queryString As String = "INSERT INTO [UserID_db] ([Code], [UserID], [Password], [Email1], [Name_First], [Name_Last], [Admin_level]) VALUES ('" & txtUserID.Text"', '" & txtUserID.Text"', '" & txtPassword.Text"', '" & txtEmail1.Text"', '" & txtFirstName"', '" & txtLastName.Text"', '" & txtAdminLevel.Text"')" Line 22: Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand Line 23: dbCommand.CommandText = queryString
My code is below. Can anyone help me figure out what is wrong with my code?
Sub btnSubmit_Click(sender As Object, e As EventArgs)
Dim UCde as string = txtUserID.Text Dim UserID as string = txtUserID.Text Dim Password as string = txtPassword.Text Dim Email1 as string = txtEmail.Text Dim FirstName as string = txtFirstName.Text Dim LastName as string = txtLastName.Text Dim AdminLevel as string = dropAccountType.SelectedItem.Value
Dim connectionString As String = "server='(local)'; trusted_connection=true; database='master'" Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)
Dim queryString As String = "INSERT INTO [UserID_db] ([Code], [UserID], [Password], [Email1], [Name_First], [Name_Last], [Admin_level]) VALUES ('" & txtUserID.Text"', '" & txtUserID.Text"', '" & txtPassword.Text"', '" & txtEmail1.Text"', '" & txtFirstName"', '" & txtLastName.Text"', '" & txtAdminLevel.Text"')" Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand dbCommand.CommandText = queryString dbCommand.Connection = dbConnection
Dim rowsAffected As Integer = 0 dbConnection.Open() dbCommand.ExecuteNonQuery() dbConnection.Close() End Sub
I create an sql string as so, add parameters to it and execute it: string cmdstr = "INSERT INTO locations(id1, id2, companyname, address, city, province, postalcode, phonenumber, faxnumber, contact, contactemail) VALUES (@id1,@id2,@companyname,@address,@city,@province,@postalcode,@phonenumber,@faxnumber,@contact,@contactemail)"; SqlCommand sqlCmd = GetCommandSQL(cmdstr); sqlCmd.CommandTimeout = TimeOut; sqlCmd.Parameters.Add("@id1", SqlDbType.Int).Value = itID; sqlCmd.Parameters.Add("@id2", SqlDbType.Int).Value = Convert.ToInt32(ddlDPCLocation.SelectedValue); sqlCmd.Parameters.Add("@companyname", SqlDbType.VarChar).Value = ((TextBox)dvShippingInformation.FindControl("tbCompanyName")).Text; sqlCmd.Parameters.Add("@address", SqlDbType.VarChar).Value = ((TextBox)dvShippingInformation.FindControl("tbAddress")).Text; sqlCmd.Parameters.Add("@city", SqlDbType.VarChar).Value = ((TextBox)dvShippingInformation.FindControl("tbCity")).Text; sqlCmd.Parameters.Add("@province", SqlDbType.VarChar).Value = ((TextBox)dvShippingInformation.FindControl("tbProvince")).Text; sqlCmd.Parameters.Add("@postalcode", SqlDbType.VarChar).Value = ((TextBox)dvShippingInformation.FindControl("tbPostalCode")).Text; sqlCmd.Parameters.Add("@phonenumber", SqlDbType.VarChar).Value = ((TextBox)dvShippingInformation.FindControl("tbPhoneNumber")).Text; sqlCmd.Parameters.Add("@faxnumber", SqlDbType.VarChar).Value = ((TextBox)dvShippingInformation.FindControl("tbFaxNumber")).Text; sqlCmd.Parameters.Add("@contact", SqlDbType.VarChar).Value = ((TextBox)dvShippingInformation.FindControl("tbContact")).Text; sqlCmd.Parameters.Add("@contactemail", SqlDbType.VarChar).Value = ((TextBox)dvShippingInformation.FindControl("tbContactEmail")).Text; sqlCmd.ExecuteNonQuery(); for testing purposes ive added the max text in each textbox area, so each textbox has 50 characters or so and i get an error message as follows, when executing the query: "System.Data.SqlClient.SqlException: String or binary data would be truncated. The statement has been terminated. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at _default.InsertGKShippingLocation() in c:\Inetpub\wwwroot\cleanapp\default.aspx.cs:line 125 at _default.InsertOrder() in c:\Inetpub\wwwroot\cleanapp\default.aspx.cs:line 93 at _default.InsertandSend() in c:\Inetpub\wwwroot\cleanapp\default.aspx.cs:line 178" Anybody have any ideas why this is happening
I have run into a problem, I have 2 fields in my database, both keyfields:Table 1=====Field X <key>Field Y <key>In field X, there are say about 3 records for each unique Field Y. Ilet my users query the data base like follows:Enter the Codes you want: 1000 and 3000 and 8500So I want to pick up records where there will be the above values forAll Y values. i.e 1000/AAA, 3000/AAA, and 8500 for AAA - if there iseven ONE of the X values not matching a record without a matching Xvalue, leave it out.i.e:X=1000,Y=AAAX=3000,Y=AAAX=8500,Y=AAAX=1000,Y=BBBX=3000,Y=BBBX=8500,Y=BBBX=1000,Y=CCCX=3000,Y=CCCX=9999,Y=CCCWhen the query runs, I want to see the following records:X=1000,Y=AAAX=3000,Y=AAAX=8500,Y=AAAX=1000,Y=BBBX=3000,Y=BBBX=8500,Y=BBBBUT NOT:X=1000,Y=CCCX=3000,Y=CCCX=9999,Y=CCCbecause one of the X values was not matched (the last X value =9999 andnot one of the requirements of the search)So I guess I want something like this:SELECT X,Y from TABLE1 WHERE ALL Y VALUES HAVE ALL OF THESE X VALUES(X=1000,X=3000,X=8500) IF ANY X VALUES ARE MISSING SKIP RECORD^^ Hope the above makes sense... but I am really stuck. The only otherway I think I could do it is, copy all records that match all 3 Xvalues into a temp table, and weed out any that are missing any one ofthe X values after they are copied but, I am running this on MYSQL 5.0Clustered, and there is not enough room in memory for it probably...and query time has to remain under a second.Anyhelp would be appreciated...
I am moving my system from a custom created queue to a service broker queue.
I wish to have two queues :
one to hold processing messages and one to hold emailing messages.
I do not understand why i would have to have an initiator queue and a target queue for each of the above. I have arranged my sql as follows:
-- processing queue CREATE MESSAGE TYPE ProcessingMessage VALIDATION = NONE CREATE CONTRACT ProcessingContract (ProcessingMessage SENT BY INITIATOR) CREATE QUEUE ProcessingQueue CREATE SERVICE ProcessingService ON QUEUE ProcessingQueue (ProcessingContract) GO
-- emailing queue CREATE MESSAGE TYPE EmailingMessage VALIDATION = NONE CREATE CONTRACT EmailingContract (EmailingMessage SENT BY INITIATOR) CREATE QUEUE EmailingQueue CREATE SERVICE EmailingService ON QUEUE EmailingQueue (EmailingContract) GO
So basically EmailingQueue plays the role of the initiator and the target ( sends messages to itself )... and so does the ProcessingQueue.
I enqueue my messages with the following SP:
PROC [dbo].[queue_enqueue] ( @fromService SYSNAME, @toService SYSNAME, @onContract SYSNAME, @messageType SYSNAME, @entryId int) AS BEGIN DECLARE @conversationHandle UNIQUEIDENTIFIER DECLARE @error int
BEGIN DIALOG @conversationHandle FROM SERVICE @fromService TO SERVICE @toService ON CONTRACT @onContract with LIFETIME = 6000, ENCRYPTION = off;
SEND ON CONVERSATION @conversationHandle Message Type @messageType (@entryId)
END
I do the enqueueing with [queue_enqueue] 'ProcessingService', N'ProcessingService', 'ProcessingContract', 'ProcessingMessage', 1
I dequeue my messages with the following SP: PROC [dbo].[queue_dequeue] @queue SYSNAME, @entryId int OUTPUT AS BEGIN DECLARE @conversationHandle UNIQUEIDENTIFIER; DECLARE @messageTypeName SYSNAME; DECLARE @conversationGroupId UNIQUEIDENTIFIER; get conversation group @conversationGroupId from ProcessingQueue
if ( @conversationGroupId is not null ) BEGIN if (@queue='ProcessingQueue') RECEIVE TOP(1) @entryId = CONVERT(INT, [message_body]), @conversationHandle = [conversation_handle], @messageTypeName = [message_type_name] FROM ProcessingQueue where conversation_group_id = @conversationGroupId else if (@queue='EmailingQueue') RECEIVE TOP(1) @entryId = CONVERT(INT, [message_body]), @conversationHandle = [conversation_handle], @messageTypeName = [message_type_name] FROM EmailingQueue where conversation_group_id = @conversationGroupId END END
I dequeue by calling something like: declare @entryId int exec [queue_dequeue] 'ProcessingQueue', @entryId output
The above works however I have a few issues that I am having problems figuring out.
1. I don't want to "fire and forget". I want to close conversation at initiator endpoint only when target has closed conversation. 2. I want to know how to properly close conversations at initiator and target. 3. I am not polling with while loop or wait for, because of the way my system ( higher up ) is setup, I simply want to enqueue one message, and dequeue one message. If an error occurs in enqueueing or dequeueing I want to raise that error. 4. How do I handle poison messages? 5. Is it better for me to use two queues ( initiator and target ) for each of my queues above?
I have a report that is created that is typically 2-3 pages long. I've tried the "ExecutionInfo.NumPages" but it always results in "1" as the answer. Code is something like this:
============
...
Dim reportHistoryParameters As sqlprod1_res.ParameterValue() = Nothing
Dim SessionId As String Dim execInfo As New ExecutionInfo
Dim execHeader As New ExecutionHeader()
Dim result As Byte() = Nothing
Dim format As String = "PDF"
Dim devInfo As String = "<DeviceInfo><Toolbar>False</Toolbar></DeviceInfo>" Dim extension As String = "" Dim encoding As String = "" Dim mimeType As String = "" Dim warnings As sqlprod1_res.Warning() = Nothing
Dim streamIDs As String() = Nothing
result = rs.Render(format, devInfo, extension, encoding, mimeType, warnings, streamIDs)
execInfo = rs.GetExecutionInfo()
Dim Pages As Integer = execInfo.NumPages ============
Hi, I'm trying to chase down some bottlenecks, and am currently tyring to figure out what's actually in our data buffer pool.
We've recently upgraded to SQL Server 2005 (sp2a); there's 4GB memory on the box (an active/passive cluster) with the /3GB switch set. I'm working on the learning curve for
sys.dm_os_buffer_descriptors and sys.allocation_units [and boy I sure wish SSMS's query windows wouldn't "copy" in HTML]. Based on BOL and some poking around, I've come up with the following query to list pages used within a given database:
on au.container_id = p.partition_id -- 2005 compatible, but maybe not in future versions
) obj
on bd.allocation_unit_id = obj.allocation_unit_id
left outer join sys.indexes ind
on ind.object_id = obj.object_id
and ind.index_id = obj.index_id
where bd.database_id = db_id()
group by obj.name, obj.object_id, obj.index_id, ind.name
order by cached_pages_count desc
This would appear to list how many pages are sitting in our buffer pool for which objects for the currently selected database. The thing is, for our "main" database, the vast majority of pages fall in that "unidentified" bucket -- their allocation_unit_ids are not in sys.allocation_units (or tempdb, I checked there just in case).
My question is: what are these pages? Where is this data coming from? Might these somehow be related with our execution/query cache, which appears to be larger than our data cache?
As may be obvious, this is all new to me, and any help would be greatly appreciated!
