Event ID: 18456 Nightmare...
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
ADVERTISEMENT
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
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
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
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 24, 2008
I hate access, I wish we could get away from it. I cant view employee pictures anymore, and every time theres and upgrade to a different version that means different problems. We are going to upgrade from 2003 to 2007. Good grief SQL doesnt need access, its too good for it anyways. Theres gotta be some different software out there that I can change too. Its just that I'm not a programmer. I keep hearing about Visual Fox Pro, that its easy but I have no idea. Anyone got any suggestions??
View 9 Replies
View Related
Dec 18, 2007
Hi,
I have setup SQL Reporitng Services on Windows 2003 server. Environment is like this on server:
- Framework 2.0
- SQL Server 2005 Enterprise Edition (with SQL Reporting Services)
- IIS 6.0
- Internet Explorer 7
- Microsoft Ajax 1.0
- Active Direcotry
I have configured SRS and it was done successfully, I got two Virtual Directories (ReportServer and Reports).
My problems are like this:
- When I type http://localhost/Reports in browser, It display ReportBuilder page with "Home" hyperlink. Nothing else is displayed on this page!!! (I am able to execute http://localhost/ReportServer/ I have login as Administrator on my server.)
- I created SQL Report Project in Visual Studio 2005 and created a Shared Data Source and a Report, I am able to preview the report in IDE. Then I tried to build and deploy it on above server, For it I modified project properties and changed TargetServerURL to above server (I tried following combinations for this property without any success http://192.168.115.17/ReportServer / http://ss6/ReportServer/ / http://localhost/ReportServer).
Exact error message is:
A connection could not be made to the report server http://192.168.115.17/ReportServer/.
Additional Information:
The attempt to connect to the server failed. Check your connection information and that the report server is a compatible version. (Microsoft.ReportingServices.Designer)
What could be the problem and solution? SRS is already running.
thanks inadvance,
Regards,
ND
View 1 Replies
View Related
Oct 17, 2007
Hello everybody ,
i have a severe problem here regarding production DBs
the system heavily utilizes UDFs in queries in bad behaviour
being used in select and where clauses , the queries can take considerable time
i have managed to enhance issues regarding indexes and select clauses , but UDf in where clauses still a problem bcz they contain some logic i cann't make it inline
here is an example :
select col1,
col2,
col3,
dbo.fn_ConvertUnit(TotalArea,AreaUnitID,@SearchOtherAreaUnit) , dbo.fnCurrencyConvert(SaleCurrency,@SearchCurrencyUnit,0,furnishedSalePrice) ,from listing inner join ResidentialSeller on listing.id=ResidentialSeller.idwhere (@TotalAreaFrom is null or dbo.fn_ConvertUnit(TotalArea,AreaUnitID,@SearchOtherAreaUnit) >= @TotalAreaFrom ) and (@TotalAreaTo is null or dbo.fn_ConvertUnit(TotalArea,AreaUnitID,@SearchOtherAreaUnit) <= @TotalAreaTo ) and (@SalePriceFurnishedFrom=0 or (furnishedSalePrice>0 and dbo.fnCurrencyConvert(SaleCurrency,@SearchCurrencyUnit,0,furnishedSalePrice)>=@SalePriceFrom)) and (@SalePriceFurnishedTo=1000000000 or (furnishedSalePrice>0 and dbo.fnCurrencyConvert(SaleCurrency,@SearchCurrencyUnit,0,furnishedSalePrice)<=@SalePriceTo))
the two UDFs fn_ConvertUnit is used to convert from measure unit to another (like from Meter to feet)
and fnCurrencyConvert is from currency to another (like from $ to Euro) based on predefined rate
CREATE FUNCTION [dbo].[fn_ConvertUnit] (@Uvalue float,@CfromID tinyint,@CToID tinyint)RETURNS float AS BEGIN if ((@Uvalue is null) or (@CfromID is null) or (@CToID is null))begin return nullend declare @fac intdeclare @temp floatif (@CfromID=@CToID)or (@Uvalue=0)beginreturn @Uvalueendif exists(select * from LookupUnitConversion where [LookupUnitConversion_UnitFrom]=@CfromID and [LookupUnitConversion_UnitTo]=@CToID )begin select @fac=LookupUnitConversion_Factor from LookupERAUnitConversion where [LookupUnitConversion_UnitFrom]=@CfromID and [LookupUnitConversion_UnitTo]=@CToID if (@fac>1) begin set @temp=1.79E308 /@fac end else begin set @temp=1.79E308 *@fac-- set @temp=@fac end if (@temp<@Uvalue) begin-- return 1.79E10return 1000000000-- A Big number enough to be compared by any number user can type end else begin RETURN @Uvalue*@fac endendif exists(select * from LookupUnitConversion where [LookupUnitConversion_UnitFrom]=@CToID and [LookupUnitConversion_UnitTo]=@CfromID )begin select @fac=LookupUnitConversion_Factor from LookupERAUnitConversion where [LookupUnitConversion_UnitFrom]=@CToID and [LookupUnitConversion_UnitTo]=@CfromID if (@fac>1) begin set @temp=1.79E308 /@fac end else begin set @temp=1.79E308 *@fac end if (@temp<@Uvalue) begin return 0 end else begin RETURN 1/(@Uvalue*@fac) endendreturn -1END
-------------------------------------------------
-------------------------------------------------
CREATE FUNCTION fnCurrencyConvert (@FromCurrency int, @ToCurrency int, @Country int, -- This prameter is useless @AmountOfMoney float)RETURNS floatASBEGINif( (@FromCurrency is null ) or (@ToCurrency is null) or (@AmountOfMoney is null))begin return nullenddeclare @temp float -- will carry the max value without exceptionif @FromCurrency=@ToCurrency or @AmountOfMoney=0beginreturn @AmountOfMoneyenddeclare @fac floatif exists(select * from CurrencyConvertion where cFromID=@FromCurrency and cToID=@ToCurrency )begin select @fac=Factor from CurrencyConvertion where cFromID=@FromCurrency and cToID=@ToCurrency order by ConvertionDate desc if (@fac>1) begin set @temp=1.79E308 /@fac end else begin set @temp=1.79E308 *@fac end if (@temp<@AmountOfMoney) begin return 1.79E308 end else begin RETURN @AmountOfMoney*@fac endendif exists(select * from CurrencyConvertion where cFromID=@ToCurrency and cToID=@FromCurrency )begin select @fac=Factor from CurrencyConvertion where cFromID=@ToCurrency and cToID=@FromCurrency order by ConvertionDate desc if (@fac>1) begin set @temp=1.79E308 /@fac end else begin set @temp=1.79E308 *@fac end if (@temp<@AmountOfMoney) begin return 0 end else begin RETURN 1/(@AmountOfMoney*@fac) endendreturn -1END
i will appreciate any help regarding these two Udfs as they r the most problematic ones , other suggesions r highly appreciated
thanks for ur time
regards
View 5 Replies
View Related
May 13, 2004
Hi everyone. I'm new to all this, and having a nightmare accessing my database.
I'm using IIS 5.1 a desktop sql server (MSDE) and Visual Studio.NET Enterprise Architect 2003.
If I try to access a sql database, I get an error "Login failed for user (ServerName)ASPNET". If I try to use an Access database, it tells me that the file is already opened exclusively by another user or that I need permission to view it.
From looking through the forums, it appears to be a security thing (an account for ASPNET?) but how do I fix it. In very plain English...
View 2 Replies
View Related
Jan 10, 2006
Hi There,
I've created a couple of search pages which look at sql server. whenever words or values like "?@~:&£^" etc, or words like for, the and so forth, the page the nasrty error page:
Execution of a full-text operation failed. A clause of the query contained only ignored words
Exception Details: System.Data.SqlClient.SqlException: Execution of a full-text operation failed. A clause of the query contained only ignored words.
In short: is there a way I can stop it doing this. It looks rather horrible. I've looked at form validation but cant find anything that seems to fit. I would imagine there is a simple solution, but I haven't been able to find it so far.
Many thanks
Stuart
View 2 Replies
View Related
Dec 5, 2007
Dear All
I have three tables
tblStudent
-----------------------------------
|anStudentId|txtFullName|txtMobile|
-----------------------------------
tblClass
------------------------
|anClassID|txtClassCode|
------------------------
tblStudentClass
--------------------------------------------------------------------------------
|id(int)|lngStudent(int)|lngClass(int)|dtStartDate(datetime)|dtEndDate(datetime)|
--------------------------------------------------------------------------------
Table tblStudentClass stores all information about classes that student was in, and will be in.
So there are plenty of records with the same lndStudent field.
What I need is to pull out information about all students from table tblStudent, and the last classes
the were/are in, or will be in if they are future students.
So the needed output should in this form with no duplicates
------------------------------------------------
|anStudentId|txtFullName|txtMobile|txtClassCode|
------------------------------------------------
What I have so far is this query:
SELECT tblStudent.anStudentId,tblStudent.txtFullName,tblStudent.txtMobile,tblClass.txtClassCode
FROM
(tblStudent
INNER JOIN
(
SELECT tblStudentClass.lngStudent, MAX(tblStudentClass.lngClass) AS LastOflngClass
FROM tblStudentClass
GROUP BY tblStudentClass.lngStudent
) AS s3
ON tblStudent.anStudentID = s3.lngStudent)
INNER JOIN tblClass
ON s3.LastOflngClass = tblClass.anClassID
WHERE
tblStudent.txtMobile LIKE '447_________'
ORDER BY tblStudent.txtMobile;
Which is not exactly what I want because it aggregates by greatest classid, not
by latest date, and it is slow on very large dataset. Maybe there are ways to optimize
it (temporary tables,stored procs,views)?
Many thanks in advance
View 3 Replies
View Related
May 24, 2007
added subreport to main tbl (by putting in detail row). Linked to tbl by 3 params. customer wants subrpt to be side-by-side to main tbl.
Unwanted symptoms: subrpt adds extra space in main tbl if subrpt contains more than 1 row; also, subrpt repeats if main table has more than 1 row. Got all of that? How do I resolve? THANK YOU
View 13 Replies
View Related
Apr 22, 2008
Here is my nightmare!! : I have 3 database tables. ( START, STOP, TICK )START ( Start_ID, Start_Time )STOP ( Stop_ID, Stop_Time )TICK ( Tick_ID, Tick_Time ) I have 3 records in each tables with the time fields being in datetime format. What I want to accomplish: I want to compare Tick_Time to see if it is between Start_Time and Stop_Time. ie. IF Tick_Time > Start_Time AND Tick_Time < Stop_Time THEN that Tick_Time.colour = Green elseTick_Time.colour = Red Can datetime fields be easily compared like I have shown above? Else can anyone give me an idea of howwwww Thanks and any help is appreciated
View 6 Replies
View Related
Aug 1, 2004
Hi,
I installed Reporting Services and forgot to name the directories. So when the installation was complete I made the virtual directories myself. But now when I try to view them It says "You are not Authorized to see this page".
Whats the deal with that ?
View 1 Replies
View Related
Dec 12, 2004
I'm hoping that someone here can help me.
I've written an app in C# with ASP.NET in VS. I can connect from my development computer (laptop - XP) to MY server (Win 2003) running MS SQL just fine.
However, when I try to deploy it on the server that it is supposed to run on, I can't connect. I can connect through the network to the SQL server, but I just can't do it with the app. This tells me that my connection string must be wrong... Of course, suggestions would be great...
The SQL server (7.0) is on Windows NT. The app is on a windows 2000 server, and yes, .NET Framework is installed. The Windows NT server is the web server, and the 2000 has been set up as www2. I can see the app from the net, but when I try to log in (requiring connection to the SQL server to verify user and pword), no go.
These are my connection strings, please correct me if I'm wrong...
(username and password are declared)
protected string myConnectionString = "Data Source=192.168.1.98;Initial Catalog=PIM;User ID=" + userName + ";Password=" + password + ";trusted_connection=false;";
protected string myAdminConnectionString = "Data Source=192.168.1.98;Initial Catalog=PIM;User ID=actualUsernameHere;Password=actualPasswordHere;trusted_connection=false;";
I've also tried them without the trusted connection, and with the server name... Same result (or lack thereof).
There's two connection strings, because one is used for the select statements for the public, and the other has all permissions for the back-end. I know it's redundant, but it's just one more safety feature I built in for the paranoid 'client'.
I have also tried to run the SQL server off of the 2000 box, but there's an error with the MMC... Does anyone know how to fix that, if it's a better solution???
Thanks very much for any and all help...
rob
View 2 Replies
View Related
Jun 9, 2005
Greetings,I have a couple of stored procedures
that interact to deliver a resultset of data. The first one is
"spStoreList", it receives a few parameters and returns a listing of
stores in a given company or division. The source is below:CREATE PROCEDURE [DBO].[spStoreList](
@p_companyX varchar(20) = NULL,
@p_divisionX char(1) = NULL,
@p_storeX char(4) = NULL
)
AS
CREATE TABLE #Output(
Company char(3),
Division char(1),
Store char(4),
Nickname varchar(40)
)
IF @p_companyX IS NULL
BEGIN
insert into #Output
SELECT CompanyNo,Division,StoreNo,StoreNickname FROM [TBL Store]
ORDER BY CompanyNo,Division,StoreNo
END
IF @p_companyX IS NOT NULL AND @p_divisionX IS NULL
BEGIN
insert into #Output
SELECT CompanyNo,Division,StoreNo,StoreNickname FROM [TBL Store]
WHERE
CompanyNo IN (SELECT DISTINCT CompanyNo FROM [TBL Company] WHERE [TBL Company].[CompanyGroup]=@p_companyX)
ORDER BY CompanyNo,Division,StoreNo
END
IF @p_companyX IS NOT NULL AND @p_divisionX IS NOT NULL AND @p_storeX IS NULL
BEGIN
insert into #Output
SELECT CompanyNo,Division,StoreNo,StoreNickname FROM [TBL Store]
WHERE
CompanyNo IN (SELECT DISTINCT CompanyNo FROM [TBL Company] WHERE [TBL Company].[CompanyGroup]=@p_companyX) AND
Division IN (SELECT DISTINCT DivNo FROM [TBL Division] WHERE DivNo=@p_divisionX AND [tbl Division].[CompanyNo]=[tbl Store].[CompanyNo])
ORDER BY CompanyNo,Division,StoreNo
END
IF @p_companyX IS NOT NULL AND @p_divisionX IS NOT NULL AND @p_storeX IS NOT NULL
BEGIN
insert into #Output
SELECT CompanyNo,Division,StoreNo,StoreNickname FROM [TBL Store]
WHERE
StoreNo = @p_storeX
ORDER BY CompanyNo,Division,StoreNo
END
SELECT * FROM #Output ORDER BY Company,Division,Store
DROP TABLE #Output
GO
The second procedure, "spEmployeeService" uses this
procedure to return a list of how many years the employees have worked
for stores that get returned in the list. THe source is here:CREATE PROCEDURE [dbo].[spEmployeeService](
@p_company char(20) = NULL,
@p_division char(1) = NULL,
@p_store char(4) = NULL
) AS
DECLARE @target int
DECLARE @newLabel varchar(30)
set @target=0
CREATE TABLE #Output(
SortCode int,
Label varchar(30),
Years int
)
CREATE TABLE #Service(
Cnt int
)
CREATE TABLE #Dock(
SortCode int,
Label varchar(30),
Years int
)
create table #Stores(
Company char(3),
Division char(1),
Store char(4),
Nickname char(40)
)
insert into #Stores
exec spStoreList @p_companyX=@p_company,@p_divisionX=@p_division,@p_storeX=@p_store
insert into #Service
select
DateDiff(yyyy,HireDate,GETDATE()) AS YearsOfService
from [tblPyoNet]
where
terminatedate is null and
company <> '' and
[tblPyoNet].StoreNo IN (Select Store FROM #Stores)
insert into #Dock (SortCode,Label,Years)
select
CASE
WHEN Cnt <= 1 THEN 1
WHEN Cnt = 2 THEN 2
WHEN Cnt = 3 THEN 3
WHEN Cnt = 4 THEN 4
WHEN Cnt = 5 THEN 5
WHEN Cnt = 6 THEN 6
WHEN Cnt = 7 THEN 7
WHEN Cnt = 8 THEN 8
WHEN Cnt = 9 THEN 9
WHEN Cnt = 10 THEN 10
WHEN Cnt <= 15 AND Cnt >10 THEN 11
WHEN Cnt >15 THEN 12
END,
CASE
WHEN Cnt <= 1 THEN '1 Year'
WHEN Cnt = 2 THEN '2 Years'
WHEN Cnt = 3 THEN '3 Years'
WHEN Cnt = 4 THEN '4 Years'
WHEN Cnt = 5 THEN '5 Years'
WHEN Cnt = 6 THEN '6 Years'
WHEN Cnt = 7 THEN '7 Years'
WHEN Cnt = 8 THEN '8 Years'
WHEN Cnt = 9 THEN '9 Years'
WHEN Cnt = 10 THEN '10 Years'
WHEN Cnt <= 15 AND Cnt >10 THEN '11-15 Years'
WHEN Cnt >15 THEN '> 15 Years'
END,
1
FROM #Service
insert into #Output
select SortCode,Label,SUM(Years) FROM #Dock GROUP BY Label,SortCode
while @target<12
begin
set @target=@target+1
if (select count(*) from #Output where SortCode=@target) = 0
begin
if @target = 11
begin
set @newLabel = '11-15 Years'
end
if @target = 12
begin
set @newLabel = '> 15 Years'
end
else
begin
set @newLabel = STR(@target)+' Years'
end
insert into #Output (SortCode,Label,Years) VALUES(@target,@newLabel,0)
end
end
SELECT * FROM #Output ORDER BY SortCode
DROP TABLE #Stores
DROP TABLE #Dock
DROP TABLE #Service
DROP TABLE #Output
GO
I can get these two procedures to work in Query Analyzer,
but if I try to pull pack a datatable in ASP.NET I get the following
error:ERROR:Invalid column name 'Store'. Invalid column name
'Division'. Invalid column name 'Company'. Insert Error: Column name or
number of supplied values does not match table definition. Insert
Error: Column name or number of supplied values does not match table
definition. Insert Error: Column name or number of supplied values does
not match table definition. Insert Error: Column name or number of
supplied values does not match table definition.
Any ideas why this would work in Query
Analyzer and not in .NET. By the way, I send the following SQL to an
SQLOLEDB driver, "exec spEmployeeService 'EFS','B'" to be returned in a
datatable.Help is appreciated.
View 8 Replies
View Related
Dec 5, 2005
I converted an Access Database to SQL Express. The dates were converted to datetime
I'm using VWD 2005
Here is the source of my date and the query.
sqlDate = (DateTime.Now.AddDays(-7))
sqlTxt = "SELECT Service_Orders.SStore_Assigned_Number, Store_Info.Store_Other, Service_Orders.PO_Number, Service_Orders.SWorkType, Service_Orders.Service_Order_Number, Service_Orders.SDate_Entered, Service_Orders.SContact, Service_Orders.SClosed FROM Service_Orders INNER JOIN Store_Info ON Service_Orders.Store_ID = Store_Info.Store_ID WHERE (Service_Orders.SDate_Entered >= CONVERT(DATETIME, '" + sqlDate + "', 101)) ORDER BY Service_Orders.SDate_Entered DESC"
This retrurns 0 records.
sqlDate = 11/28/2005 12:23:27 AM from the function above.
The query will return records with :
sqlDate = "2005-11-01 21:56:20"
I tried changing the CONVERT(DATETIME, '" + sqlDate + "', 1XX from 100 to 120 with no luck
I know this must be an easy fix, but it is beyond me.
I need to know how to
1. convert my date to the dateformat from "11/28/2005 12:23:27 AM" to "2005-11-01 21:56:20"
or
2. find out how to use the CONVERT(DATETIME, '" + sqlDate + "', 1XX properly
Thanks for any help in advance!
Bill
View 2 Replies
View Related
Apr 9, 2006
In PHP, connecting to a (MySQL) database is quite a simple matter. There are PHP functions for connecting, database user and password, database name, etc.
Is there something equally simple for Visual Web Developer Express? Hell, I can't even find a way to configure any aspects of my SQL Server Express Edition anywhere!
The Wrox Visual Web Developer 2005 Express Edition Starter Kit seemed promising, but it's useless too if I can't get the database to work at all. I cannot wait until my organization finally uses PHP so I can be rid of this M$ BS. Too bad I can't get my 102 hours and 48 minutes (so far) back.
View 1 Replies
View Related
Aug 1, 2006
I bet every SQL-Server guy in this forums has fought pageiolatch issues from one time to another. Either bad indexing, overloaded disks / controllers etc. I know I have, with a great variety of systems and solutions.
Anyways.... I simply have to ask for any of your experiences, as I'm currently stuck with a Navision client, that ocassionally stalls on pageiolatch on even simple queries.
User selects a customer account, asks to see his / her details, scrolls up and down a bit, and whammo, stall, and pageiolatch appears on the server.
Something tells me that this might be an ODBC driver issue rather than actual sql-server issues. Fetching the initial data isn't the issue, but scroling back and forth in the recorsset it seems to cause the cursor to freeze or at least lose track or stall in the progress of paging through the records.
All of the above sounds very unclear, I know, I'm just trying tro track down or center on the problem, so that I might find a solution. So if you have any experiences with clients, cursors and odbc driver version for sql-server, that really make a lot of trouble, let me know.
Cheers, Trin
View 8 Replies
View Related
Apr 9, 2008
hi All
in short, what i'm trying to do is add another field called CompanyId ( uniqueidentifier) to the aspnet_Membership table and aspnet_Users table which comes in the classifieds starter kit,
Curently, when a usere registers, a UserId ( uniqueidentifier) is created and stored both the aspnet_Membership table and aspnet_Users table, i just want to duplicate this functionality and have CompanyID in the same tables in the same way.
Ive been messing around with the stored precedures for a while but i'm getting nowhere, if anyone could help, or even explain a little how the UserId is passed from one table to another, i'd be so greatful
I have added the two stored precedures bellow, first is called ALTER PROCEDURE dbo.aspnet_Membership_CreateUse
seccond is called ALTER PROCEDURE [dbo].aspnet_Users_CreateUser
thanks a lot
odxsigma
ALTER PROCEDURE dbo.aspnet_Membership_CreateUser
@ApplicationName nvarchar(256),
@UserName nvarchar(256),
@Password nvarchar(128),
@PasswordSalt nvarchar(128),
@Email nvarchar(256),
@PasswordQuestion nvarchar(256),
@PasswordAnswer nvarchar(128),
@IsApproved bit,
@CurrentTimeUtc datetime,
@CreateDate datetime = NULL,
@UniqueEmail int = 0,
@PasswordFormat int = 0,
@UserId uniqueidentifier OUTPUT
AS
BEGIN
DECLARE @ApplicationId uniqueidentifier
SELECT @ApplicationId = NULL
DECLARE @NewUserId uniqueidentifier
SELECT @NewUserId = NULL
DECLARE @IsLockedOut bit
SET @IsLockedOut = 0
DECLARE @LastLockoutDate datetime
SET @LastLockoutDate = CONVERT( datetime, '17540101', 112 )
DECLARE @FailedPasswordAttemptCount int
SET @FailedPasswordAttemptCount = 0
DECLARE @FailedPasswordAttemptWindowStart datetime
SET @FailedPasswordAttemptWindowStart = CONVERT( datetime, '17540101', 112 )
DECLARE @FailedPasswordAnswerAttemptCount int
SET @FailedPasswordAnswerAttemptCount = 0
DECLARE @FailedPasswordAnswerAttemptWindowStart datetime
SET @FailedPasswordAnswerAttemptWindowStart = CONVERT( datetime, '17540101', 112 )
DECLARE @NewUserCreated bit
DECLARE @ReturnValue int
SET @ReturnValue = 0
DECLARE @ErrorCode int
SET @ErrorCode = 0
DECLARE @TranStarted bit
SET @TranStarted = 0
IF( @@TRANCOUNT = 0 )
BEGIN
BEGIN TRANSACTION
SET @TranStarted = 1
END
ELSE
SET @TranStarted = 0
EXEC dbo.aspnet_Applications_CreateApplication @ApplicationName, @ApplicationId OUTPUT
IF( @@ERROR <> 0 )
BEGIN
SET @ErrorCode = -1
GOTO Cleanup
END
SET @CreateDate = @CurrentTimeUtc
SELECT @NewUserId = UserId FROM dbo.aspnet_Users WHERE LOWER(@UserName) = LoweredUserName AND @ApplicationId = ApplicationId
IF ( @NewUserId IS NULL )
BEGIN
SET @NewUserId = @UserId
EXEC @ReturnValue = dbo.aspnet_Users_CreateUser @ApplicationId, @UserName, 0, @CreateDate, @NewUserId OUTPUT
SET @NewUserCreated = 1
END
ELSE
BEGIN
SET @NewUserCreated = 0
IF( @NewUserId <> @UserId AND @UserId IS NOT NULL )
BEGIN
SET @ErrorCode = 6
GOTO Cleanup
END
END
IF( @@ERROR <> 0 )
BEGIN
SET @ErrorCode = -1
GOTO Cleanup
END
IF( @ReturnValue = -1 )
BEGIN
SET @ErrorCode = 10
GOTO Cleanup
END
IF ( EXISTS ( SELECT UserId
FROM dbo.aspnet_Membership
WHERE @NewUserId = UserId ) )
BEGIN
SET @ErrorCode = 6
GOTO Cleanup
END
SET @UserId = @NewUserId
IF (@UniqueEmail = 1)
BEGIN
IF (EXISTS (SELECT *
FROM dbo.aspnet_Membership m WITH ( UPDLOCK, HOLDLOCK )
WHERE ApplicationId = @ApplicationId AND LoweredEmail = LOWER(@Email)))
BEGIN
SET @ErrorCode = 7
GOTO Cleanup
END
END
IF (@NewUserCreated = 0)
BEGIN
UPDATE dbo.aspnet_Users
SET LastActivityDate = @CreateDate
WHERE @UserId = UserId
IF( @@ERROR <> 0 )
BEGIN
SET @ErrorCode = -1
GOTO Cleanup
END
END
INSERT INTO dbo.aspnet_Membership
( ApplicationId,
UserId,
Password,
PasswordSalt,
Email,
LoweredEmail,
PasswordQuestion,
PasswordAnswer,
PasswordFormat,
IsApproved,
IsLockedOut,
CreateDate,
LastLoginDate,
LastPasswordChangedDate,
LastLockoutDate,
FailedPasswordAttemptCount,
FailedPasswordAttemptWindowStart,
FailedPasswordAnswerAttemptCount,
FailedPasswordAnswerAttemptWindowStart )
VALUES ( @ApplicationId,
@UserId,
@Password,
@PasswordSalt,
@Email,
LOWER(@Email),
@PasswordQuestion,
@PasswordAnswer,
@PasswordFormat,
@IsApproved,
@IsLockedOut,
@CreateDate,
@CreateDate,
@CreateDate,
@LastLockoutDate,
@FailedPasswordAttemptCount,
@FailedPasswordAttemptWindowStart,
@FailedPasswordAnswerAttemptCount,
@FailedPasswordAnswerAttemptWindowStart )
IF( @@ERROR <> 0 )
BEGIN
SET @ErrorCode = -1
GOTO Cleanup
END
IF( @TranStarted = 1 )
BEGIN
SET @TranStarted = 0
COMMIT TRANSACTION
END
RETURN 0
Cleanup:
IF( @TranStarted = 1 )
BEGIN
SET @TranStarted = 0
ROLLBACK TRANSACTION
END
RETURN @ErrorCode
END
ALTER PROCEDURE [dbo].aspnet_Users_CreateUser
@ApplicationId uniqueidentifier,
@UserName nvarchar(256),
@IsUserAnonymous bit,
@LastActivityDate DATETIME,
@UserId uniqueidentifier OUTPUT,
@CompanyId int OUTPUT
AS
BEGIN
IF( @UserId IS NULL )
SELECT @UserId = NEWID()
ELSE
BEGIN
IF( EXISTS( SELECT UserId FROM dbo.aspnet_Users
WHERE @UserId = UserId ) )
RETURN -1
END
INSERT dbo.aspnet_Users (ApplicationId, UserId, UserName, LoweredUserName, IsAnonymous, LastActivityDate)
VALUES (@ApplicationId, @UserId, @UserName, LOWER(@UserName), @IsUserAnonymous, @LastActivityDate)
RETURN 0
END
thanks a lot
odxsigma
View 1 Replies
View Related
Jul 26, 2006
Hi, nice to meet you all ^^
I've been given this access database to look at which could eventually be upgraded to SQL, I am conducting a kind of initital research in to how we would go about it. The previous system has developed over time and is pretty messy. I'm not really a database developer, but as everyone is busy on other things, this side project has been given to me to look at as a warm up!
Basically I've managed to upsize all the tables and their contents, and have begun copying the forms over to a .adp - creating most of the queries as stored procedures.
I'm working through cronologically, and this is the 2nd query the system requires... It was full of IIf statements which I have commented out. The adp works, but is missing a lot of data - can't really work out where it was meant to be feeding from, I personally can't stand access - but it's what the original was created in and what they want to use.
ALTER PROC [qrySaleLotEntry] @saleid nvarchar(3)
AS
SELECT [Sale & Lot].SaleNum, [Sale Details].SaleDate, [Sale Details].SaleDesc, [Sale & Lot].LotNum,
[Sale & Lot].onsite, [Sale & Lot].StockNum, [Sale & Lot].ThisEntryFee, [Sale & Lot].EntryFeePaid,
Accounts.[4x4ENTRYFEE], Accounts.CARENTRYFEE, [Sale & Lot].Vendor, Accounts.VENDORDESC, Accounts.ACCOUNTNAME,
Accounts.[GROUP], Accounts.CONTACT, Accounts.PHONE, Accounts.MOBILPHONE, Accounts.VATNO, Accounts.[e-mail],
[Sale & Lot].RegYear, [Sale & Lot].RegLetter, [Sale & Lot].RegDate, [Sale & Lot].RegNum, [Sale & Lot].ManuYear,
[Sale & Lot].Manufacturer, [Sale & Lot].Model, [Sale & Lot].[Type], [Sale & Lot].CAPCode, [Sale & Lot].Colour,
[Sale & Lot].MileageNum, [Sale & Lot].Kilometres, [Sale & Lot].Warranted, [Sale & Lot].ServiceHistory,
[Sale & Lot].ServiceNum, [Sale & Lot].ServiceMileage, [Sale & Lot].ServiceDate, [Sale & Lot].MoTDay, [Sale & Lot].MoT,
[Sale & Lot].Tax, [Sale & Lot].ReservePrice, [Sale & Lot].VATCode, [Sale & Lot].CAPValueClean, [Sale & Lot].CAPValueAve,
[Sale & Lot].CAPValuePoor, [Sale & Lot].AgrNum, [Sale & Lot].Extra1, [Sale & Lot].Extra2, [Sale & Lot].Extra3,
[Sale & Lot].Extra4, [Sale & Lot].Extra5, [Sale & Lot].Extra6, [Sale & Lot].Extra7, [Sale & Lot].Extra8,
[Sale & Lot].Extra9, [Sale & Lot].Extra10, [Sale & Lot].ASSEEN, Accounts.ASSEEN, Accounts.UNWARRANTED,
[Sale & Lot].NoMMF, [Sale & Lot].UNROADWORTHY, [Sale & Lot].PRESALEHPI, [Sale & Lot].[COLLECTION],
Accounts.[CollectionRequired?], [Sale & Lot].TotalLoss, [Sale & Lot].Accident, [Sale & Lot].FinanceOwed,
[Sale & Lot].Taxi, [Sale & Lot].V5, [Sale & Lot].V5Part2, [Sale & Lot].PlateTfr, [Sale & Lot].PlateTfrCharge,
Accounts.PlateTfrCharge, [Sale & Lot].PlateTfrPaid, [Sale & Lot].Fuel, [Sale & Lot].FuelCharge, Accounts.FuelCharge,
[Sale & Lot].PreSalePrep, [Sale & Lot].PreSalePrepCharge, Accounts.PreSalePrepCharge, [Sale & Lot].Misc1,
[Sale & Lot].Misc1Desc, [Sale & Lot].Misc1Charge, [Sale & Lot].Misc2, [Sale & Lot].Misc2Desc, [Sale & Lot].Misc2Charge,
[Sale & Lot].DeliveryIn, [Sale & Lot].DeliveryInCharge, Accounts.DeliveryInCharge, [Sale & Lot].DeliveryOut,
[Sale & Lot].DeliveryOutCharge, Accounts.DeliveryOutCharge, [Sale & Lot].WashOff, [Sale & Lot].WashOffCharge,
Accounts.WashOffCharge, [Sale & Lot].Polish, [Sale & Lot].PolishCharge, Accounts.PolishCharge, [Sale & Lot].ValetA,
[Sale & Lot].ValetACharge, Accounts.ValetACharge, [Sale & Lot].ValetB, [Sale & Lot].ValetBCharge,
Accounts.ValetBCharge, [Sale & Lot].ValetC, [Sale & Lot].ValetCCharge, Accounts.ValetCCharge, [Sale & Lot].Delogo,
[Sale & Lot].DelogoCharge, Accounts.DelogoCharge, [Sale & Lot].ENGINEER, [Sale & Lot].EngineersCharge,
Accounts.EngineersCharge, [Sale & Lot].Repairs, [Sale & Lot].RepairsCharge, Accounts.RepairsCharge,
[Sale & Lot].Provisional, [Sale & Lot].Purchaser, Accounts_1.ACCOUNTNAME, Accounts_1.CONTACT, Accounts_1.PHONE,
Accounts_1.MOBILPHONE, Accounts_1.[e-mail], [Sale & Lot].SalePrice, [Sale & Lot].Nett, [Sale & Lot].VAT,
[Sale & Lot].VATDesc, [Sale & Lot].TransInbound, [Sale & Lot].TransInboundPaid, [Sale & Lot].TransReturn,
[Sale & Lot].TransReturnPaid, [Sale & Lot].[Transfer Complete], [Sale & Lot].datetostock, [Sale & Lot].Reentry,
[Sale & Lot].ChassisNum, [Sale & Lot].Comments, [Sale & Lot].PackNumber, [Sale & Lot].MissedDeadline,
[Sale & Lot].Created, [Sale & Lot].CreatedBy, [Sale & Lot].Modified, [Sale & Lot].ModifiedBy, Accounts.TradeStatus,
[Sale & Lot].VIN, [Sale & Lot].PrevVRM, [Sale & Lot].DateVRMChanged, [Sale & Lot].ExpModel, [Sale & Lot].EngineNum,
[Sale & Lot].OrigCol, [Sale & Lot].NumPrevCols, [Sale & Lot].DateLastColChange, [Sale & Lot].PrevCol,
[Sale & Lot].AgreementType, [Sale & Lot].AgreementTerm, [Sale & Lot].AgreementDate, [Sale & Lot].FinanceCo,
[Sale & Lot].FinanceTel, [Sale & Lot].FinanceAgrNum, [Sale & Lot].FinanceDesc, [Sale & Lot].PoliceForce,
[Sale & Lot].PoliceTel, [Sale & Lot].PoliceReportDate, [Sale & Lot].RiskCompany, [Sale & Lot].RiskTel,
[Sale & Lot].RiskRef, [Sale & Lot].RiskPeriod, [Sale & Lot].RiskInterestDate, [Sale & Lot].RiskType,
[Sale & Lot].RiskOther, [Sale & Lot].ConditionInsurer, [Sale & Lot].ConditionClaimNum, [Sale & Lot].ConditionTel,
[Sale & Lot].ConditionMIAFTRDate, [Sale & Lot].ConditionMake, [Sale & Lot].ConditionModel, [Sale & Lot].KeeperPrevNum,
[Sale & Lot].KeeperChangeDate, [Sale & Lot].KeeperDateAcquired, [Sale & Lot].KeeperDateDisposed, [Sale & Lot].Scrapped, [Sale & Lot].Exported
FROM (Accounts RIGHT JOIN ([Sale Details] INNER JOIN [Sale & Lot] ON [Sale Details].SaleNum = [Sale & Lot].SaleNum) ON Accounts.ACCOUNT = [Sale & Lot].Vendor) LEFT JOIN Accounts AS Accounts_1 ON [Sale & Lot].Purchaser = Accounts_1.ACCOUNT
WHERE dbo.[Sale & Lot].SaleNum = @saleid
ORDER BY [Sale & Lot].SaleNum, [Sale & Lot].LotNum;
/*
IIf([saledesc] Like "C*",[sale & lot.LotNum] & "C",[sale & lot.LotNum] & "F") AS SaleLetter,
IIf([Accounts.VENDORDESC] Not Like "","Direct from") AS DirectFrom, [Sale & Lot.Manufacturer] & " " & [Sale & Lot.Model] & " " & [Sale & Lot.Type] AS [Desc], StrConv([sale & lot.RegNum],1) AS Reg,
IIf([accounts.unwarranted]=-1,"Unwarranted - Company Policy",
IIf([sale & lot.warranted] Like "1",[sale & lot.MileageNum] & " " & IIf([sale & lot.kilometres]=-1,"kms ") & "Warranted",
IIf([sale & lot.warranted] Like "2","Unwarranted",IIf([sale & lot.warranted] Like "3","Incorrect")))) AS MileageStatus,
IIf([Sale & Lot.VATCode] Like 1,"Plus VAT",
IIf([Sale & Lot.VATCode] Like 2,"No VAT",
IIf([Sale & Lot.VATCode] Like 3,"Inc VAT"))) AS VATStatus,
IIf([sale & lot.v5]=-1,"V5 here",IIf([sale & lot.v5part2]=-1,"V5/2 here",IIf([sale & lot.v5]=0 And [sale & lot.v5part2]=0,"V5 not here"))) AS V5here, IIf([Sale & Lot.Tax] Is Not Null,"Tax " & [Sale & Lot.Tax],"No Tax") AS Taxhere, IIf([sale & lot.MoT] Is Not Null,"MoT " & [sale & lot.MoTDay] & " " & [sale & lot.MoT],"No MoT") AS MoThere, IIf([Sale & Lot.ServiceHistory]<>-1 And [Sale & Lot.ServiceNum] Is Null,"No Service History",IIf([Sale & Lot.ServiceHistory]=-1 And [Sale & Lot.ServiceNum] Is Null,"Service History",IIf([Sale & Lot.ServiceNum]=1,"1 Service",[Sale & Lot.ServiceNum] & " " & "Services"))) AS Services, IIf([Sale & Lot.ASSEEN]=-1,"As Seen",IIf([Sale & Lot.NoMMF]=-1,"No Major Mechanical Faults",IIf([Sale & Lot.UNROADWORTHY]=-1,"Unroadworthy"))) AS MechDesc, IIf([Sale & Lot.PrevLotNum]>0,[Sale & Lot.PrevSaleNum] & "/" & [Sale & Lot.PrevLotNum],"") AS Prev, IIf([sale & lot.MoT] Is Not Null,Trim([sale & lot.MoTDay] & " " & [sale & lot.MoT]),"No") AS PendragonMoT, IIf([Sale & Lot.ServiceHistory]<>-1 And [Sale & Lot.ServiceNum] Is Null,"No",IIf([Sale & Lot.ServiceHistory]=-1 And [Sale & Lot.ServiceNum] Is Null,"Yes",IIf([Sale & Lot.ServiceNum]=1,"1 Service",[Sale & Lot.ServiceNum] & " " & "Services"))) AS PendragonServiceHist, IIf([Sale & Lot.ServiceMileage] Is Null,"","(Last Serviced " & [Sale & Lot.ServiceMileage] & ", " & [Sale & Lot.ServiceDate] & ")") AS LastServiced, IIf([Sale & Lot.Tax] Is Not Null,[Sale & Lot.Tax],"No") AS PendragonTax, Trim([Sale & Lot]![Extra2] & [Sale & Lot]![Extra3] & [Sale & Lot]![Extra4] & [Sale & Lot]![Extra5] & [Sale & Lot]![Extra6] & [Sale & Lot]![Extra7] & [Sale & Lot]![Extra8] & [Sale & Lot]![Extra9] & [Sale & Lot]![Extra10] & IIf([Sale & Lot]![TotalLoss]=-1," Total Loss") & IIf([Sale & Lot]![Accident]="No",""," " & [Sale & Lot.Accident]) & IIf([Sale & Lot]![Taxi]="No",""," " & [Sale & Lot.Taxi])) AS PendragonExtras, IIf([sale & lot.v5]=-1,"YES","NO") AS LogBook, IIf([sale & lot.Extra2] Like " Service History,","YES","NO") AS ServiceHistCondRep
FROM (Accounts RIGHT JOIN ([Sale Details] INNER JOIN [Sale & Lot] ON [Sale Details].SaleNum = [Sale & Lot].SaleNum) ON Accounts.ACCOUNT = [Sale & Lot].Vendor) LEFT JOIN Accounts AS Accounts_1 ON [Sale & Lot].Purchaser = Accounts_1.ACCOUNT
ORDER BY [Sale & Lot].SaleNum, [Sale & Lot].LotNum;
*/
I've commented out the IIf statements. I began by trying to convert them in to CASE statements and such - but frankly can't get my head around that.
Getting back to basics... I suppose the question would be: I am upsizing from access to SQL, what the heck do I do with all these IIf statements?
View 2 Replies
View Related
Apr 16, 2007
I've built this prototype web app in asp.net 2.0
it uses a sql server express database.
It uses the database to populate a grid control.
It works when I run it from my pc when I
1). run it in debug mode.
2) right click a page in the vs2005 IDE and browse
3). browse a page from the IIS snap in
it doesn't work from other peoples PC on our intranet say if someone types:
http://xxx.xxx.xx.xx/mywebapp/default.aspx because I get this result:
Server Error in '/FocusII_Website' Application.
--------------------------------------------------------------------------------
User does not have permission to perform this action.
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: User does not have permission to perform this action.
Source Error:
Line 82:
Line 83: SqlDataAdapter da = new SqlDataAdapter(sql, ConfigurationManager.ConnectionStrings["sqlExpress"].ToString());
Line 84: da.Fill(ds);
Line 85: RadGrid1.DataSource = ds;
Line 86: RadGrid1.DataBind();
Source File: c:InetpubwwwrootFocusII_WebsiteUnitGridPrototype.aspx.cs Line: 84
Stack Trace:
[SqlException (0x80131904): User does not have permission to perform this action.]
System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +437
System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105
System.Data.SqlClient.SqlConnection.Open() +111
System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +121
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +137
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) +86
UnitGridPrototype.Page_Load(Object sender, EventArgs e) in c:InetpubwwwrootFocusII_WebsiteUnitGridPrototype.aspx.cs:84
System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +15
System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +34
System.Web.UI.Control.OnLoad(EventArgs e) +99
System.Web.UI.Control.LoadRecursive() +47
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1061
--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.42
Impersonation is set to TRUE
Anonymous access is checked.
Integrated Windows Authentication is checked..
aspnet, iusr accounts have full privs on the data directory,,,,,
I'm at a loss..
View 1 Replies
View Related
Jan 22, 2008
This morning one of our client's database appeared as Suspect. I checked the event log and saw the following error:
Could not redo log record (413:835:2), for transaction ID (0:156861), on page (1:1246), database 'CLP' (database ID 26). Page: LSN = (412:2642:4), type = 1. Log: OpCode = 4, context 2, PrevPageLSN: (412:12236:2). Restore from a backup of the database, or repair the database.
I then tried to run a DBCC CHECKDB on the database and got the following output:
Msg 7987, Level 16, State 1, Line 1
System table pre-checks: Object ID 13 has chain linkage mismatch. (1:339)->next = (1:771), but (1:771)->prev = (1:341). Check statement terminated due to unrepairable error.
DBCC results for 'CLP'.
CHECKDB found 0 allocation errors and 0 consistency errors in database 'CLP'.
After a bit of research I began to get very sad, for a number of reasons. First I can't find much information on this error and what there is implies that I'm totally up the creek. Secondly and worst of all, backups have not been working so we don't have a viable backup of the database.
I did go as far as to run repair_allow_data_loss and received the same Msg: 7897 error as above.
Is there any hope? Can this be repaired? Any help is greatly appreciated.
-Will
View 5 Replies
View Related
Sep 24, 2006
I've been working 4 days non stop on this project, lost a complete weekend on it and I totally had it.
Please have a look at this "simple" question:
I have a for each loop that checks for csv files in a folder. The path of the file(s) is stored in a variable varFileName.
So far so good. But then I start with a data flow task and inside that data flow task I need to access one of those csv files at the time whenever it loops.
So my best guess is use a flat file source because that's the only task I see in the list that fits my question.
But the thing is, you set up a connection to a....yes right, a flat source connection and there you have to select a flat file.
But no, I don't want to select ONE file, I need to access them all as the loop goes through all files.
I'm sure this is something easy but I don't see it anymore.
I'm off taking a nap, need sleep
Could someone please point me to a direction?
Many thanks!
Worf
View 5 Replies
View Related
Jul 4, 2007
We have a setup with a web server and multiple databases, and a live, stage, and dev environment. We use SQL Server standard 2005 and use the ASP.NET ReportView control. I have spent countless hours now trying to get this to work and am about to give this up and go back to Crystal.
First I wanted a report that would work from dev to stage to live without modification, so we set up shared data sources on each environment to point to the appropriate database. No problem, I can publish it to each environment and it works, though sometimes I have to go into Report Manager and fix the data source.
Next I wanted to be able to work with multiple databases, identical in structure. For this we did a hidden parameter with the database name and used a formula for the query string. This works pretty well.
Next I wanted to be able to run against multiple database servers from a single web server. This has been nearly impossible. I've read a million posts about this, and nothing seems to work well. I've tried a dynamic connection string, and passing the server in as a parameter, but this doesn't work, because I can't get the credentials set on the ReportViewer.ServerReport, so it doesn't work from dev to stage. You can't programatically change the shared data source - that would make it too easy. Linked servers are not an option.
I guess I need to either publish a copy of the report for each database server, or set up an instance of SQL on the web server for each database server.
Any other reasonable options out there. I just can't imagine my setup is all that unique.
View 2 Replies
View Related
Jan 25, 2005
My hosting company will give me a user with db_ddlAdmin how do I change my current database which has all the tables,store procedures and views owned by dbo.
I've used a few sql scripts to change the owner using
Exec sp_changeobjectowner 'object', 'user'
and I've used
Grant Execute on object to user
These have changed ownership but the site still doesn't work
Does anyone know what to do to achieve this.
Any help greatly appreciated
Many Thanks
Duncan
View 2 Replies
View Related
Aug 9, 2006
This is more of a basic SQL question rather than anything specific for T-SQL, but I assume someone here can help me...
I have a problem with properly combining a lot of AND and ORs in a SELECT statement in a stored procedure in order to get the desired results. The problem is that I want to have all results that fullfill all of the supplied conditions: InstitutionCode, CollectionCode, ScientificName, Locality (unless they are null, hence 'coalesce') and the Parentid, that can be in one of eight columns.
SELECT * FROM QueryView WHERE InstitutionCode = COALESCE(@museum, InstitutionCode) AND CollectionCode = COALESCE(@collection, CollectionCode) AND ScientificName LIKE '%' + @binomen + '%' AND Locality LIKE '%' + @locality + '%' AND ParentID1 = COALESCE(@taxparent, ParentID3) OR ParentID2 = COALESCE(@taxparent, ParentID2) OR ParentID3 = COALESCE(@taxparent, ParentID3) OR ParentID4 = COALESCE(@taxparent, ParentID4) OR ParentID5 = COALESCE(@taxparent, ParentID5) OR ParentID6 = COALESCE(@taxparent, ParentID6) OR ParentID7 = COALESCE(@taxparent, ParentID7) OR ParentID8 = COALESCE(@taxparent, ParentID8)
The current construction, however, gives me all results that fullfill either on of the four conditions, or the parentid in one of the columns. putting parentheses around parentid part gives me zero query results. I understand that the ORs should be restricted to the parentids and not the rest, but putting parentheses around parentid part gives me zero query results.
Has anyone got a good tip to help me resolve this puzzle?
View 6 Replies
View Related
May 15, 2008
I have been trying to uninstall SQL Server 2005 Express Edition for a day and a half to no avail. I have cleaned it out of the registry, and I have done everything possible to get rid of this, er, Microsoft virus software. I have erased every folder that has any SQL Server name after I stopped all SQL server services in the task list. All I want to do is install SQL Server 2005 Developer Edition so that I can use the BI Manager to create a DTS package. Does anyone have any utilities that might help?
View 3 Replies
View Related
