Good Old Event ID: 18456 - Can't Figure This One Out!
May 25, 2007
Hi everyone,
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:
Event Type: Failure Audit
Event Source: MSSQLSERVER
Event Category: (4)
Event ID: 18456
Date: 5/25/2007
Time: 1:57:00 AM
User: TNGTNG-SQL_Service
Computer: TNG-MOSSDB01
Description:
Login failed for user 'TNGTNG-SQL_Service'. [CLIENT: <local machine>]
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?
Thank you,
Chris
View 10 Replies
ADVERTISEMENT
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!
View 1 Replies
View Related
Oct 22, 2007
We recently upgraded to SQL 2005 from SQL 2000. We have most of our issues ironed out however about every 1 minute there is a message in the Application Event log and the SQL log that states:
EVENT ID 18456 Login Failed for the users DOMAIN/ACCOUNT [CLIENT: <local machine>]
This is a state 16 message which I thought meant that the account does not have access to the default database. The account is actually the account that the SQL services run under.
Any ideas? We can't seem to figure this one out. We actually upgraded to 2005 from 2000 and had an error appear after every reboot that prevented the SQL Agent from running(This application has failed to start because GAPI32.dll was not found. Re-installing the application may fix this problem.) We did a full uninstall of SQL and reinstalled fresh and restored the databases from .bak files and that is when the EVENT ID 18546 started occuring every minute.
We don't have any SQL heavy hitters here so please be detailed with any possible solutions. That you very much for any help you can provide!
David
View 5 Replies
View Related
Mar 4, 2008
In the application log on my SQL server i get a lot of messages saying:
Login failed for user 'DomainComputername$' [Client: "ip adress"]
Type: Failure Aud.
User: DomainComputername$
Source: MSSQLSERVER
Event ID: 18456
View 4 Replies
View Related
Aug 31, 2007
I am not a DBA, but have dealt with a number of POS applications that use SQL Server 2005 as their back end.
That said, I'm no pro, but I can generally keep a DB running. Here is my problem scenario:
Everyday, at least three times a day, the server spontaneously starts rejecting all connections. The event log shows repeated messages of Event ID 18456 which is a logon issue. The server continues to reject all connections from that point until the machine is restarted. Restarting the SQL server without restarting the machine accomplishes nothing...
Looking at the SQL logs gets some further information:
Event ID: 18456, Severity: 14, State: 10
Apparently, state is of some significance though all of the links I have found that explain what the various states mean leave out number 10. (such as here (http://www.eventid.net/display.asp?eventid=18456&eventno=8175&source=MSSQLSERVER&phase=1))
This problem did not start happening until 4 days ago. The only event I can think of that also happened at this time was the installation of PHP (which doesn't even touch SQL Server...)
Any ideas?
Alex
View 3 Replies
View Related
Jul 27, 2007
We occasionally get the error Login Failure for 'Admin' (client: then IP address). The SQL 2005 server is set up to use windows authentication and no one uses an account called Admin. The people here use their windows logon to access the database. Any suggestions would be appreciated.
Mark Hartman
View 1 Replies
View Related
Mar 25, 2008
Hi All,
I need to send out email when error occurs in the package. Is it a good practice to put the send email task in the event handler? Then MaximumErrorCount is set to 1. But for some reason, some time I saw more than one email are sent out. Please advise. Thanks
View 6 Replies
View Related
May 29, 2008
Hello,
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 ?
Thanks: Peter
View 5 Replies
View Related
Feb 4, 2008
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?
Thank you for your help!
cdun2
View 5 Replies
View Related
Jun 2, 2015
Recently we migrated our environment to 2012.
We are planning to implement Xevents in all the servers in place of Trace files and everything is working fine.
Is it possible to configure Extended event to trigger a mail whenever any event (example dead lock) occurs.
I have gone through so many websites but i never find.
View 13 Replies
View Related
Oct 25, 2011
My SQL Server 2005 SP4 on Windows 2008 R2 is flooded with the below errors:-
Date  10/25/2011 10:55:46 AM
Log  SQL Server (Current - 10/25/2011 10:55:00 AM)
Source  spid
Message
Event Tracing for Windows failed to send an event. Send failures with the same error code may not be reported in the future. Error ID: 0, Event class ID: 54, Cause: (null).
Â
Is there a way I can trace it how it is coming? When I check input buffer for these ids, it looks like it is tracing everything. All the general application DMLs are coming in these spids.
View 2 Replies
View Related
Apr 8, 2008
I have been testing with the WMI Event Watcher Task, so that I can identify a change to a file.
The WQL is thus:
SELECT * FROM __InstanceModificationEvent within 30
WHERE targetinstance isa 'CIM_DataFile'
AND targetinstance.name = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\AdventureWorks.bak'
This polls every 30 secs and in the SSIS Event (ActionAtEvent in the WMI Task is set to fire the SSIS Event) I have a simple script task that runs a message box).
My understanding is that the event polls every 30 s and if there is a change on the AdventureWorks.bak file then the event is triggered and the script task will run producing the message.
However, when I run the package the message is occurring every 30s, meaning the event is continually firing even though there has been NO change to the AdventureWorks.bak file.
Am I correct in my understanding of how this should work and if so why is the event firing when it should not ?
View 2 Replies
View Related
May 31, 2007
Server 2003 SE SP1 5.2.3790 Sql Server 2000, SP 4, 8.00.2187 (latest hotfix rollup)
We fixed one issue, but it brought up another. the fix we applied stopped the ServicesActive access failure, but now we have a failure on MSSEARCH. The users this is affecting do NOT have admin rights on the machine, they are SQL developers.
We were having
Event Type: Failure Audit
Event Source: Security
Event Category: Object AccessEvent ID: 560
Date: 5/23/2007
Time: 6:27:15 AM
User: domainuser
Computer: MACHINENAME
Description:
Object Open:
Object Server: SC Manager
Object Type: SC_MANAGER OBJECT
Object Name: ServicesActive
Handle ID: -
Operation ID: {0,1623975729}
Process ID: 840
Image File Name: C:WINDOWSsystem32services.exe
Primary User Name: MACHINE$
Primary Domain: Domain
Primary Logon ID: (0x0,0x3E7)
Client User Name: User
Client Domain: Domain
Client Logon ID: (0x0,0x6097C608)
Accesses: READ_CONTROL
Connect to service controller
Enumerate services
Query service database lock state
Privileges: -
Restricted Sid Count: 0
Access Mask: 0x20015
Applied the following fix
http://support.microsoft.com/kb/907460/
Now we are getting
Event Type: Failure Audit
Event Source: Security
Event Category: Object Access
Event ID: 560
Date: 5/23/2007
Time: 10:51:23 AM
User: domainuser
Computer: MACHINE
Description:
Object Open:
Object Server: SC Manager
Object Type: SERVICE OBJECT
Object Name: MSSEARCH
Handle ID: -
Operation ID: {0,1627659603}
Process ID: 840
Image File Name: C:WINDOWSsystem32services.exe
Primary User Name: MACHINE$
Primary Domain: domain
Primary Logon ID: (0x0,0x3E7)
Client User Name: user
Client Domain: domain
Client Logon ID: (0x0,0x60D37C1A)
Accesses: READ_CONTROL
Query service configuration information
Query status of service
Enumerate dependencies of service
Query information from service
Privileges: - Restricted Sid Count: 0 Access Mask: 0x2008D
View 4 Replies
View Related
Nov 2, 2007
Hi all,
Can we get the event properties by using a query?
Are there any extended stored procuder to get the above?
Scenario:
>Desktop>Right Click on My Computer
>Go to Manage and click
>Expand System Tools
>Expand Event Viewer
>Application
click on one event.We can get the log info which is the manual procudure.
But now i want to get the event properties through the Query analyzer...
Any help would be great?
Thanks,
View 4 Replies
View Related
Jan 19, 2004
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.
View 5 Replies
View Related
May 1, 2008
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.
This is my code:
ALTER procedure [dbo].[rptMgmtRpt]
@RptMonthBegin datetime,
@RptMonthEnd datetime
as
SELECT Summons.ID, Convert(varchar, Venire.VenireDateTime,101)as VenireDateTime, Venire.VenireStatusID, Summons.SummonsStatusID, Summons.ExcusalCodeID,
Venire.ID AS VenireID, { fn WEEK(Venire.VenireDateTime) } AS wkNum, { fn DAYOFWEEK(Venire.VenireDateTime) } AS wkDay, Venire.JuryTypeID,
Venire.VenireLocationID, summons.compensationTypeID
,{fn IFNULL(AccountingTransactionRequest.PaymentAmount,0)} AS payAmt
,Convert(varchar,AccountingTransactionRequest.ServiceDate,101) as AcctServDate
,{fn count(summons.id)}
FROM Venire INNER JOIN
Summons ON Venire.ID = Summons.VenireID
LEFT outer JOIN
AccountingTransactionRequest ON Summons.ID = AccountingTransactionRequest.SummonsID
Where Venire.VenireLocationID <> 16 and (VenireDateTime BETWEEN @RptMonthBegin AND @RptMonthEnd)
ORDER BY wknum, wkday , Summons.ID
This is a sample of what I get for results:
640404 11/5/2007 128 144 2866 45 2 8 0 11/5/2007
640888 11/5/2007 128 32 2866 45 2 8 0 11/5/2007
640979 11/5/2007 32 2 2866 45 2 112 15 11/5/2007
640979 11/5/2007 32 2 2866 45 2 112 15 11/6/2007
640979 11/5/2007 32 2 2866 45 2 112 15 11/7/2007
641533 11/5/2007 128 32 2866 45 2 8 0 11/5/2007
641762 11/5/2007 128 144 2866 45 2 8 0 11/5/2007
641851 11/5/2007 1024 2 2866 45 2 8 0 11/5/2007
What I need are results that look like this:
640404 11/5/2007 128 144 2866 45 2 8 0 11/5/2007 1
640888 11/5/2007 128 32 2866 45 2 8 0 11/5/2007 1
640979 11/5/2007 32 2 2866 45 2 112 15 11/5/2007 1
640979 11/5/2007 32 2 2866 45 2 112 15 11/6/2007 2
640979 11/5/2007 32 2 2866 45 2 112 15 11/7/2007 3
641533 11/5/2007 128 32 2866 45 2 8 0 11/5/2007 1
641762 11/5/2007 128 144 2866 45 2 8 0 11/5/2007 1
641851 11/5/2007 1024 2 2866 45 2 8 0 11/5/2007 1
In the first column, 640979 appears three times.
How do I get that count in the last column of my results ?
Thanks so much for your help!!!
View 4 Replies
View Related
May 2, 2008
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.
Thanks in advance.
View 4 Replies
View Related
Mar 9, 2004
Hello All,
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.
Please Help,
James
View 3 Replies
View Related
Feb 21, 2007
I have a table with multiple records.
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.
I can not figure this out. :eek:
Any help would be GREATLY appreciated.
View 3 Replies
View Related
Mar 13, 2007
I have the following sp which is appending records into my table. However the values appended are being round up eg
SC_PrimaryPupilPrice is 1.5
but when it is inserted into the sql table it is 2
The field in the sql table is numeric.
CREATE PROCEDURE spSM_AddWeeksandMealPrices
@dteWeekEnding datetime
as
INSERT INTO tblSM_Meals
(ML_Id,
ML_WeekEnding,
ML_SchoolNumber,
ML_PupilMealPrice,
ML_AdultMealPrice,
ML_SpillagesMealPrice,
ML_AdultRechargeMealPrice,
ML_ReservedMealPrice)
select convert(varchar,@dteWeekEnding ,103) + '*' + cast(SC_SchoolNumber as varchar(10)) , convert(datetime,@dteWeekEnding ,106),
SC_SchoolNumber,
SC_PrimaryPupilPrice,
SC_PrimaryAdultPrice,
SC_PrimaryPupilPrice,
SC_PrimaryAdultPrice,
SC_PrimaryAdultPrice
from tblSM_Schools
GO
View 4 Replies
View Related
Jun 8, 2007
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
Else
Row.myCol1 = CStr(rowValues(0)).Replace("""", String.Empty)
Row.myCol2 = CStr(rowValues(1)).Replace("""", String.Empty)
Row.myCol3 = CStr(rowValues(2)).Replace("""", String.Empty)
Row.myCol4 = CStr(rowValues(3)).Replace("""", String.Empty)
Row.myCol5 = CStr(rowValues(4)).Replace("""", String.Empty)
Row.myCol6 = CDec(rowValues(5))
End If
End Sub
View 10 Replies
View Related
Mar 22, 2007
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.
View 1 Replies
View Related
Oct 16, 2007
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
View 3 Replies
View Related
Oct 17, 2007
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!
View 3 Replies
View Related
Feb 25, 2005
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.
:confused: Advice?
View 8 Replies
View Related
May 22, 2008
Hi -
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.
CREATE TABLE [SalesAcct]
(
[EmpName] [varchar] (100),
[Acct_Num] [varchar](15),
[Acct_Name] [varchar] (50)
)
CREATE TABLE [Perf]
(
[Acct_Num] [varchar](15),
[PerfDate] [smalldatetime],
[Channel] [varchar](25),
[Medium] [varchar](25),
[Clicks] [int],
[Impressions] [int]
)
CREATE TABLE [Revenue]
(
[Acct_Num] [varchar](15),
[RevenueDate] [smalldatetime],
[Channel] [varchar](25),
[Medium] [varchar](25),
[ServiceLevel] [varchar],
[TransactionType] [varchar],
[GrossRev] [decimal](18, 2)
)
INSERT INTO SalesAcct (EmpName, Acct_Num, Acct_Name)
VALUES ('BobSmith', '100XYZ', 'My Account')
INSERT INTO Perf (Acct_NUm, PerfDate, Channel, Medium, Clicks, Impressions)
VALUES ('100XYZ', '05-11-2008', 'US', 'Net', 240, 500)
INSERT INTO Revenue (Acct_Num, RevenueDate, Channel, Medium, GrossRev)
VALUES ('100XYZ', '05-11-2008', 'US', 'Net',500.50)
INSERT INTO Revenue (Acct_Num, RevenueDate, Channel, Medium, GrossRev)
VALUES ('100XYZ', '05-11-2008', 'US', 'Cable',23.75)
What I need to provide to mgmt is two reports...one is rolled up by EmpName and AcctNum and Date, and the other is details like:
Suumary_Acct_By_Day:
EMPName | Acct_Num | Acct_Name | Date Rev $ | Clicks | Impressions
BobSmith| X0000005 | My Account | 2008-05-11 |524.25| 240500
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.
Any help would be appreciated.
Thank you,
Will
View 4 Replies
View Related
Jan 3, 2006
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.
i.e:
X=1000,Y=AAA
X=3000,Y=AAA
X=8500,Y=AAA
X=1000,Y=BBB
X=3000,Y=BBB
X=8500,Y=BBB
X=1000,Y=CCC
X=3000,Y=CCC
X=9999,Y=CCC
When the query runs, I want to see the following records:
X=1000,Y=AAA
X=3000,Y=AAA
X=8500,Y=AAA
X=1000,Y=BBB
X=3000,Y=BBB
X=8500,Y=BBB
BUT NOT:
X=1000,Y=CCC
X=3000,Y=CCC
X=9999,Y=CCC
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.
Anyhelp would be appreciated...
View 5 Replies
View Related
Feb 27, 2008
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.
View 3 Replies
View Related
Mar 5, 2008
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.
View 10 Replies
View Related
Jan 13, 2006
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??? :)
View 2 Replies
View Related
Jul 20, 2005
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.
View 3 Replies
View Related
Mar 30, 2008
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>'.
thanks
View 7 Replies
View Related
Oct 11, 2007
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?
View 5 Replies
View Related
