SOLVED: TDSSNIClient Initialization Failed With Error 0x7e
Nov 13, 2005solution found here:
View 1 Replies
ADVERTISEMENT
SQL 2005 TDSSNIClient Initialization Failed With Error 0x32, Status Code 0x1c (Clustered Environment)
Jan 31, 2007I've seen other posts on this topic, but not with error 0x32.
I was attempting to change a CLUSTERED instance of SQL 2005 from dynamic port to static port and in the process have somehow messed it up to the point it will no longer start. Version is 9.00.2047.00
The ERRORLOG has the following
2007-01-31 15:02:05.77 spid9s Starting up database 'model'.
2007-01-31 15:02:05.77 Server Error: 17182, Severity: 16, State: 1.
2007-01-31 15:02:05.77 Server TDSSNIClient initialization failed with error 0x32, status code 0x1c.
2007-01-31 15:02:05.77 Server Error: 17182, Severity: 16, State: 1.
2007-01-31 15:02:05.77 Server TDSSNIClient initialization failed with error 0x32, status code 0x1.
2007-01-31 15:02:05.77 Server Error: 17826, Severity: 18, State: 3.
2007-01-31 15:02:05.77 Server Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.
2007-01-31 15:02:05.77 Server Error: 17120, Severity: 16, State: 1.
2007-01-31 15:02:05.77 Server SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.
The values have only been changed using SQL Server Configuration Manager (no direct registry changes have been made).
Thanks for you help.
Fallback Certificate Initialization Failed. Error: 1
Aug 15, 2007When opening Visual Studio 2005 and creating a new project my computer sometimes crashes (blue screen).
The System Error with source MSSQL$SQLEXPRESS and description: Fallback certificate initialization failed. Error: 1 (Event-ID: 17190) is found in the programlog.
Do anyone know the source of this error/what is causing it? How can I fix it?
Regards,
Lotta
SQL Server 2005 Startup Error - TDSSNIClient... Error 0x5... Code 0x51
Jul 13, 2006Hi,My SQL Server 2005 won't start - can anyone provide some help? Thisseems to be the most significant error:- TDSSNIClient initialization failed with error 0x5, status code 0x51.There was some info here, but the reg key described looks okay:- http://blogs.msdn.com/sql_protocols/I am running SQL Server 2005 64-bit Standard Edition. The server inquestion is the first node of an Active/Active Cluster. This firststarted happening while I was trying to add a share as a clusteredresource of the other node (not to this node's resources).I have not yet reinstalled SQL Server 2005 because I am hoping for moreexplanation of the problem and a possible fix.Thanks,Tom
View 1 Replies View RelatedCLR Initialization Failed With Hresult 0x80131022
Jul 23, 2007I guess this may not strictly be a CLR integration question, because what I'm actually doing is using sp_OACreate in a T-SQL batch to call a COM object written in C#. Not as nice as using full CLR integration, but unfortunately the T-SQL must run without error on a SQL 2000 server before it is upgraded to 2005, and I preferred this option to using reams of dynamic SQL.
The first call to sp_OACreate fails with hRes 0x80131022. MSSQLServer also logs the following error to the app event log:
"Failed to initialize the Common Language Runtime (CLR) v2.0.50727 with HRESULT 0x80131022. You may fix the problem and try again later."
Please can anyone suggest why this is happening? I've tried repairing the .NET framework (v2) installation, and reinstalled SQL Server Express. I've double checked both CLR integration and OLE Automation are allowed in the Surface Area config tool for SQL Express. I've tried running SQL Express using both LocalSystem and the administrator account on the machine. I've even checked there is plenty of free memory (including virtual memory). Nothing has helped though.
Thanks in advance for any help or suggestions you can provide,
Rob
Trouble Installing MSDE, Failed Server Initialization!
Feb 8, 2005Hi,
I am working on the guided tour of the ASP.NET WebMatrix tool, and I wanted to perform the section of connecting to a SQL server.
I downloaded SQL2kdesksp3 from the Microsoft website, extracted it, and followed the instructions for installing it:
setup SAPWD=(some password) SecurityMode=SQL
It installs all the way to the end, and I get an error message :
"Setup failed to initialize the server. Please see the setup error logs for more information"
I tried to reinstall it, and even tried downloading it again, and then running it, I get the same message.
Could someone tell me what is going on, or where to find the error logs?
Thanks in advance.
Error In Attaching .mdf Files.. How Can This Be Solved?
May 11, 2006We've tried attaching the mdf files given to us by some customers using the script they've provided, too.
I created the database with the corresponding names, and tried to run their script. However, I get the message "The media family on device 'D:DatabaseDVVAD01.mdf' is incorrectly formed. SQL Server cannot process this media family."
What could possibly have gone wrong? Is it also possible that the file was not properly detached, or what?
Error On Replication Initialization
Jul 24, 2007Hi,
We had a healthy 2-way merge replication set up with a SQL 2005 publisher/distributor and a SQL 2000 subscriber which had been running for about a week. I re-created the publication so I could add automatic identity range management to an article. I sucessfully re-created a push subscription to a SQL 2005 subscriber but when I tried to create one to the same SQL 2000 machine that had been quite happy a few hours earlier, I kept getting this error during initialization: "bulk data stream was incorrectly specified as sorted". Nothing much has changed as far as I can see. Can anyone give me some pointers?
Thanks,
Pete
Data Access :: Initialization Of Data Source Failed
Sep 23, 2015I'm new to sql server. Last week I made my first cube. Everything went fine. But when I want to explore the cube (from the explore panel in Visual Studio 2012) in Excel then I'm getting the error "Initialization of the data source failed".
Check your database server or contact your database administrator. Make sure the external database is available, and then try the operation again. If you see this message again, create a new datasource to connect to the database.
I'm using Sql Server 2012. Visual studio 2012. Excel 2013 on Windows 10
Format Of The Initialization String Error
Apr 14, 2005Hi all,
Complete newbie to ASP.NET, but you have to learn some time. Anyway, I am attempting to fill a datagrid from the Northwind database installed on my SQL server at work and I receive the following error when I attempt to compile to check the connection.
Format of the initialization string does not conform to specification starting at index 67.
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.ArgumentException: Format of the initialization string does not conform to specification starting at index 67.Source Error:
Line 31:
Line 32: Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Line 33: Dim cnn As New SqlConnection( _
Line 34: "Data source=xxxxx;Initial Catalog=Northwind;User ID=sa;Password=;xxxxxx")
Line 35: End SubSource File: C:InetpubwwwrootApp_02default.aspx.vb Line: 33 Stack Trace:
[ArgumentException: Format of the initialization string does not conform to specification starting at index 67.]
System.Data.Common.DbConnectionOptions.GetKeyValuePair(String connectionString, Int32 currentPosition, StringBuilder buffer, String& keyname, String& keyvalue) +1203
System.Data.Common.DbConnectionOptions.ParseInternal(Hashtable parsetable, String connectionString, Boolean buildChain, Hashtable synonyms, Boolean firstKey) +127
System.Data.Common.DbConnectionOptions..ctor(String connectionString, Hashtable synonyms, Boolean useFirstKeyValuePair) +103
System.Data.SqlClient.SqlConnectionString..ctor(String connectionString) +32
System.Data.SqlClient.SqlConnectionFactory.CreateConnectionOptions(String connectionString, DbConnectionOptions previous) +25
System.Data.ProviderBase.DbConnectionFactory.GetConnectionPoolGroup(String connectionString, DbConnectionPoolOptions poolOptions, DbConnectionOptions& userConnectionOptions) +119
System.Data.ProviderBase.DbConnectionBase.set_ConnectionString(String value) +53
System.Data.SqlClient.SqlConnection.set_ConnectionString(String value) +7
System.Data.SqlClient.SqlConnection..ctor(String connectionString) +23
App_02.WebForm1.Page_Load(Object sender, EventArgs e) in C:InetpubwwwrootApp_02default.aspx.vb:33
System.Web.UI.Control.OnLoad(EventArgs e) +102
System.Web.UI.Control.LoadRecursive() +45
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +952
I have been tearing my hair out trying to work out this error. Can anyone help????
Using VisualStudio.NET 2003 on an XP box and and SQL Server 2000 on a Windows2003 Server box.
Thanks,
Gerald
Error In Initialization Of The Report Service
Feb 23, 2006I installed the evaluation version of SQL Server 2005 to my Windows XP machine. Everything has worked well. Now I have problem. I installed the reporting service to test the web reporting.
In the Report service configuration manager I get three errors:
The Web Services Identity indicator is red and pushing Apply button gives the error:ReportServicesConfigUI.WMIProvider.WMIProviderException: An unknown error has occurred in the WMI Provider. Error Code 8007056B
Pushing Initialization button gives the message: There was an error while switching panels. The most likely cause is an error retriewing WMI properties. the exeception details are: An error occured when attempting to connect to the report server remote procedure call (RPC) end point. Verify that the Report Server Windows service is running and then try the operation. Execption 800706B3.
Server Status Indicator is also red. Pushing the Start Button gives the following message: System.ServiceProcess.TimeoutException: Time out has expired and the operation has not been completed. at System.ServiceProcess. ServiceController. WaitForStatus(ServiceControllerStatus desiredStatus, TimeSpan timeout) at ReportServicesConfigUI.Panels.ServerInformationPanel.StartStopServiceTask(Boolean start)
Has anyone an idea what I should do ??
thankfully
Matti
OLE DB Error: Format Of The Initialization String Does Not Conform To The OLE DB Specification.
Feb 11, 2008It is SQL Server 2005. I am trying to create an SSIS package for OUR database engine with OUR OLE DB provider.
In Server Explorer I am trying to add a data connection for OUR database using OUR own ole db data provider. Procedure below-
1. Right click "Data Connections"
2. Click "Add Connection...", "Change Data Source" dialog is displayed.
3. Select "other" and ".NET Framework Data Provider for OLE DB". Click OK. "Add Connection" dialog is displayed.
4. Select "OLE DB Provider". I selected OUR provider.
5. Edit necessary properties. Click OK.
6. An error dialog is displayed saying "Format of the initialization string does not conform to the OLE DB specification."
I did some tracing. My observations are-
Connection string is built internally as
"Provider=MYOLEDB;Password=password;Data Source=myserver;User ID=admin;Initial Catalog=SYSTEM;Window Handle=984398;Prompt=CompleteRequired"
The last property causes this issue. i.e. presence of 'Prompt=CompleteRequired' is the problem.
I tried this with simple c# test program to connect. If I pass the whole connection string as above to OleDbConnection() it gives the error--
"Format of the initialization string does not conform to the OLE DB specification."
It works fine if I pass connection string removing last property, i.e.
"Provider=MYOLEDB;Password=password;Data Source=myserver;User ID=admin;Initial Catalog=SYSTEM;Window Handle=984398"
So the problem is that the connection string built contains 'Prompt=CompleteRequired' at the end. Our provider returns DBPROMPT_NOPROMPT for DBPROP_INIT_PROMPT property. Still why the connection string is built as 'Prompt=CompleteRequired'?
Probably this is because of missing something in properties/implementation. What is it?
Thanks,
Vivek.
RDA Push Error Another User Has Opened The Database With Different Instance-level Initialization Properties. [ 32 ]
Mar 3, 2008
Hi all,
I'm using VS2005 + SQL Compact 3.1 and Enterprise Library 3.1 May 2007
I can successfully create a db using pull method, when I try to push back data I got the following SQLCE error:
"Another user has opened the database with different instance-level initialization properties. [ 32 ]"
Before calling the push method I did
SqlCeDatabase eCE = Helper.GetDB() as SqlCeDatabase;
eCE.CloseSharedConnection();
SqlCeConnectionPool.CloseSharedConnections();
SqlCeRemoteDataAccess rda = new SqlCeRemoteDataAccess();
rda.InternetUrl = webSynchronizationUrl;
rda.InternetLogin = webuser;
rda.InternetPassword = webpass;
rda.LocalConnectionString = eCE.ConnectionStringWithoutCredentials;
but after the assignment the LocalConnectionString is different from ConnectionStringWithoutCredentials, there are much more ssce parameters.
What I don't understand is:
why I'm getting this error even after closing the connection ?
Since the error report [32] the ssce conflicting parameters shoud be AUTO_SHRINK_THRESHOLD, FLUSH_INTERVAL and MAX_DATABASE_SIZE
I tried to cut & paste the same values from LocalConnectionString into app.config connection string, at first It seemed to work, but now I got the same error.
Any help will be greatly appreciated
Thanks
TDSSNIClient Init Failure
Feb 21, 2007I started getting this error when I tried to start my local SQL Server yesterday and nothing I do seems to help:
Error: 17182, Severity: 16, State: 1.
TDSSNIClient initialization failed with error 0x2, status code 0x35.
Error: 17182, Severity: 16, State: 1.
TDSSNIClient initialization failed with error 0x2, status code 0x1.
Error: 17826, Severity: 18, State: 3.
Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.
Error: 17120, Severity: 16, State: 1.
SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.
The only thing I did recently was move the My Documents folder from my local machine to a folder on a network share (to make sure my docs are backed up) via a right click on the My Documents folder in the Start menu, then pick the new location. I've since moved it back, but the above error persists.
Also, when I try to configure the server's protocols, I'm not able to enable any of the protocols. I can right click the protocol and click enable, then I get the error that says it'll take effect when i restart the service, but then in the display, it still says disabled. Refreshing doesn't change the status. The client protocols are enabled though.
Any help would be greatly appreciated (since the service never successfully starts)...
Sqlmaint.exe Failed. [SQLSTATE 42000] (Error 22029). The Step Failed.
Jan 3, 2001I get the following error when I try to run my scheduled job.
sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.
Any ideas Thanks in advance
Reddy
Package Failed : Failed To Decrypt Protected XML Node DTS:Password With Error
Apr 21, 2008Dear all,
I've built SSIS package and made a job to execute it automatically but it always returns an error. The job returns OK but when we looked at the Log File viewer, it conatins this error log :
Key not valid for use in specified state
Failed to decrypt protected XML node "DTS Password" with error
What's wrong with the package ?
Thanks in advance.
Best regards,
Hery
Log Shipping Failure Sqlmaint.exe Failed. [SQLSTATE 42000] (Error 22029). The Step Failed.
Sep 13, 2001I have log shipping set up between 2 SQL 2000 SP1 Servers on Win 2000. The db is small 10 meg, and when the restore job on the backup server fails I am getting "sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed." as the message, the maint paln on the primary server show no error. Anyone seen this before? The restore has worked 3 out of 5 times
View 2 Replies View RelatedLogin Failed For User - Token Based Server Access Validation Failed With Infrastructure Error
Sep 9, 2015Many a times see the below error in SQL Error log.
Login failed for user 'NT AUTHORITYANONYMOUS LOGON'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: ]
Is this something to do here?
Note: If I run the below statement I know that the SQL Error log entry will go off, but wanted to know the real significance of this error?
CREATE LOGIN [NT AUTHORITYANONYMOUS LOGON] FROM WINDOWS
Executed As User: CONNECTSRVmyadmin. Sqlmaint.exe Failed. [SQLSTATE 42000] (Error 22029). The Step Failed.
Feb 29, 2008Hi all help needed from your End.
I have a re-indexing stored procedure,yester day night it got failed
"Executed as user: CONNECTSRVmyadmin. sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed."
what may be the reason..
Nm Solved
Mar 26, 2007nm solved
View 1 Replies View RelatedSolved
Sep 20, 2006Hi,
Now it´s working fine!!!!
On the Firewall of the Server, I´ve added the default port 1433 on the exceptions.
thanx!!!!
Problem Solved
Feb 21, 2008
The problem is solved but still I would like to know what is going on behind the scenes. I was always thinking that the native client was involved and not oledb.
Using the Export/Import wizard you still end up going via SQL Server Agent that's where the Credential/Proxy items show up.
My Problem Also Not Solved Yet
Jun 24, 2006Upper case sentece in normal case sentence
View 6 Replies View RelatedSimple Problem But Cannot Solved
Feb 25, 2003I'm just beginning to use DTS. I have a test table which has only two column, char (10) and decimal (5,0). I want to do a simple transformation by adding the second column by 100 with the following statement, but I receive a type mismatch error!
Function Main()
DTSDestination("a")=DTSSource("a")
DTSDestination("b")=DTSSource("b").Value + 100
Main = DTSTransformStat_OK
End Function
or
Function Main()
dim x
DTSDestination("a")=DTSSource("a")
x = DTSSource("b").Value + 100
DTSDestination("b").Value = x
Main = DTSTransformStat_OK
End Function
An Importanat Problem To Be Solved
Dec 8, 1999Hello friends I need a very ergent help!!!!!!!!!!!!!
Problem: my sql server on production had everything grayed out, when I want to administer this server as a SA. eg: I can't add users, I can't backup and so on.
but the database is still running and users are accessing it.
This SQL database is accessed by an application(it can also create users on
the backend). I don't know how it happend ( i cant even get to errorlog).
I beleive somehow sa user rights were taken away from him. Can anyone tell me how to restore sa with all rights.
Please help me on this matter, because I have to do lots of important tasks on this server.
I appreciate all of your help.
Thanks
Ragul
Compare Counts [SOLVED]
Mar 12, 2008Hi, hope someone can help. I have two tables
Test1 that lists all the training courses that i can count to find out the total as below.
select count (distinct Training_Course) as total
from Test1
Then Test2 lists all our customers and courses they have attended. I count the courses attended and then group by their ID.
select Cust_id, count (Attended) as TotalAttend
from Test2
Group by Cust_id
What i am now trying to do, without any luck, is find out which Customers have attended all training sessions by comparing the two queries and only bringing back the cust_id where it matches the total count from the Test1 query.
Make any sense? Any help/suggestions gratefully recieved.
SOLVED: Need Help With This Count() Query
Mar 23, 2008Hello,uery - I'm Stuck
I'm very rusty with my SQL, and could use a little assistance on building this query. Thanks for taking the time to help me. It should be a relatively simple Count() query but I'm not getting the right results for some reason and I'm hoping somebody can point out my error to me.
I'm attempting to count the number of times each m_id is returned after running this query:
SELECT m_id
FROM taglink
WHERE m_id <> '25'
AND t_id
IN (
SELECT t_id
FROM taglink
WHERE m_id = '25'
)
which returns:
m_id
33
34
34
35
35
35
36
I want to count the number of times that m_id is returned so that the results of my query will be:
m_id | count
33 | 1
34 | 2
35 | 3
36 | 1
In my attempt to do this, I run the following query:
SELECT m_id, count( m_id ) AS "count"
FROM taglink
WHERE m_id
IN (
SELECT m_id
FROM taglink
WHERE m_id <> '25'
AND t_id
IN (
SELECT t_id
FROM taglink
WHERE m_id = '25'
)
)
GROUP BY m_id
The problem I'm having is that the query returns:
m_id | count
33 | 3
34 | 2
35 | 3
36 | 3
It seems to return the count of "3" for fields that should count "1," but count correctly if the fields count "2" or "3."
Perhaps somebody can spot my error... I can't seem to wrap my brain around this one. Thanks so much for your time!
Object Must Implement IConvertible Is A BUG Non Solved?
Aug 9, 2006
i try to do a simple insert using SQLDataSource and a Stored Procedure
I try all night and always have this error
Object Must Implement IConvertible
I can’t understand where I wrong..
Looking around I understand is maybe a typecasting problema but I not find nothing more understeable..
Could someone tell me why Microsoft release this SQLdatasorce making more difficult a simple insert?
If someone could help me to fix this problem please
Here is the code
------------------------------------------------------------------------------------------------------------
Protected Sub btnSave_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSave.Click
Try
SqlDataSource1.Insert()
Catch ex As Exception
Response.Write(ex.ToString)
Finally
SqlDataSource1.Dispose()
End Try
End Sub
------------------------------------------------------------------------------------------------------------
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:CommerceTemplate %>"
InsertCommandType="StoredProcedure" InsertCommand="dbo.StoredProcedure1">
<InsertParameters>
<asp:ControlParameter ControlID="DropDownListCategory" Name="@CategoryID" PropertyName="SelectedValue"
Type="Int32" />
<asp:ControlParameter ControlID="DropDownListCategory" Name="@ModelNumber" PropertyName="SelectedValue"
Type="String" />
<asp:ControlParameter ControlID="txtModelName" Name="@ModelName" PropertyName="Text"
Type="String" />
<asp:ControlParameter ControlID="txtProductImage" Name="@ProductImage" PropertyName="Text"
Type="String" />
<asp:ControlParameter ControlID="txtUnitCost" Name="@UnitCost" PropertyName="Text"
Type="Decimal" />
<asp:ControlParameter ControlID="txtDescription" Name="@Description" PropertyName="Text"
Type="String" />
<asp:ControlParameter ControlID="txtWeight" Name="@Weight" PropertyName="Text" Type="Decimal" />
<asp:ControlParameter ControlID="txtDiscount" Name="@Discount" PropertyName="Text"
Type="Decimal" />
<asp:ControlParameter ControlID="chkIsActive" Name="@isActive" PropertyName="Checked"
Type="Boolean" />
<asp:ControlParameter ControlID="DropDownListAuthors" Name="@IDAuthor" PropertyName="SelectedItem"
Type="Int32" />
</InsertParameters>
</asp:SqlDataSource>
------------------------------------------------------------------------------------------------------------
ALTER PROCEDURE dbo.StoredProcedure1
@CategoryID int,
@ModelNumber nvarchar(50),
@ModelName nvarchar(50),
@ProductImage nvarchar(50),
@UnitCost money,
@Description varchar(500),
@weight money,
@IsActive bit,
@DiscountPerCent int,
@IDAuthor int
AS
INSERT INTO dbo.CMRC_Products (
CategoryID,
ModelNumber,
ModelName,
ProductImage,
UnitCost,
Description,
weight,
IsActive,
DiscountPerCent,
IDAuthor
)
VALUES (
@CategoryID,
@ModelNumber,
@ModelName,
@ProductImage,
@UnitCost,
@Description,
@weight,
@IsActive,
@DiscountPerCent,
@IDAuthor
)
SELECT @@IDENTITY as [NewID]
------------
Urgent , Execute Permission Not Solved
Aug 9, 1999i have a table "employee" , i have given grant all rights to a user
in sql 6.5 server . still whenever he tries to access the table from VB he gets an error "execute permission denied on table employee, owner dbo"
. if i log in the server with this user name and his password i am able to interact with the table . please help.
Run-Time Running Total *(SOLVED)
May 14, 2008I am re-writing a old query that update's it's value based on values at run time. Is there anyway to accomplish the following query where column s1.AI referances the values that were updated during run-time.
I want to avoid a Loop (i.e. cursor or a CTE).
Please help!
Declare @Stage Table(StartDate datetime,BenefitInterestID INT PRIMARY KEY, Amount MONEY, InterestAmount MONEY, Interest DECIMAL(10, 4), ai DECIMAL(10, 4))
Insert Into @Stage
Select
convert(datetime,'2006-12-01 00:00:00.000',101) as StartDate,1 as BenefitInterestID,1701.00 as amount,79.605 as InterestAmount ,0.1000 as Interest,0.0000 as ai
Union all
select '2007-12-01 00:00:00.000',2,172.80,7.92,0.0500,0
Union all
select '2008-12-01 00:00:00.000',4,0.00,0.00,0.0700,0
UPDATEs1
SETs1.ai = s1.Interest * coalesce((SELECT SUM(coalesce(s2.Amount,0) + coalesce(s2.InterestAmount,0)+coalesce(s2.ai,0)) FROM @Stage AS s2 WHERE s2.StartDate < s1.StartDate ),0)
FROM@Stage AS s1
select * from @Stage
My desired Results are
2006-12-01 00:00:00.00011701.0079.6050.10000.0000
2007-12-01 00:00:00.0002172.807.920.050089.0303
2008-12-01 00:00:00.00040.000.000.0700143.5300
But I am geting
2006-12-01 00:00:00.00011701.0079.6050.10000.0000
2007-12-01 00:00:00.0002172.807.920.050089.0303
2008-12-01 00:00:00.00040.000.000.0700137.2928
Due to as you can see on row 3 that the amount does not factor in the previous Years ai column when I sum, due to the update takes place at runtime.
Please help!
Simplish Query Problem *SOLVED*
May 28, 2008First, I'm not sure what format to post the SQL query in, but I'm using it in the Pawn programming language, so it's all on one line. Sorry if it's hard to read :(
Ok, I am supplying you guys with my database that I am having problems with. Basically, I'm telling it to ORDER BY ASC but it's not ordering it correctly. It's almost correct, but not perfect. I'm not sure if SQLite is much different than SQL standard, that's why I differentiate.
Here is the SQLite database:
*deleted*
The problem comes in when you execute this query:
SELECT p.alias, min(s.fin_time), s.cps, s.gcs, s.boosts, s.wpns, s.score, s.score_id FROM climb_scores s JOIN climb_players p ON s.user_id = p.user_id JOIN (SELECT user_id, fin_time, score_id FROM climb_scores WHERE map_name="j2s_4floors" AND cps = 0) g ON g.score_id=s.score_id GROUP BY s.user_id ORDER BY s.fin_time ASC LIMIT 20
The end goal is to have fin_time sorted in ascending order, but it's not working right. Can someone clue me in to the problem?
thanks!
SQL Query Problem For Reporting [SOLVED]
May 30, 2008hi guys, im having a sql problem for reporting here. here is the table
CREATE TABLE `tblasset` (
`AssetID` int(11) NOT NULL,
`AssetName` varchar(50) NOT NULL,
PRIMARY KEY (`AssetID`)
);
INSERT INTO `tblasset` (`AssetID`, `AssetName`) VALUES
(1, 'IPOD'),
(2, 'Laptop'),
(3, 'Sony PS3'),
(4, 'Wooden Cupboard'),
(5, 'Fridge');
CREATE TABLE `tblassetplacement` (
`PlacementID` int(11) NOT NULL,
`LocationID` int(11) NOT NULL,
`AssetID` int(11) NOT NULL,
`PlacementDate` bigint(20) NOT NULL,
`OfficerInChargeID` varchar(50) DEFAULT NULL,
PRIMARY KEY (`PlacementID`)
);
INSERT INTO `tblassetplacement` (`PlacementID`, `LocationID`, `AssetID`, `PlacementDate`, `OfficerInChargeID`) VALUES
(1, 3, 1, 1209628156, 'John'),
(2, 6, 1, 1209800956, 'Susan'),
(3, 3, 3, 1209714556, 'Erik'),
(4, 4, 3, 1210405756, 'Albert'),
(5, 5, 3, 1211096956, 'Fred');
CREATE TABLE `tbllocation` (
`LocationID` int(11) NOT NULL,
`LocationName` varchar(50) NOT NULL,
`ParentID` int(11) DEFAULT NULL,
PRIMARY KEY (`LocationID`),
KEY `ParentID` (`ParentID`)
);
INSERT INTO `tbllocation` (`LocationID`, `LocationName`, `ParentID`) VALUES
(1, 'Building A', NULL),
(2, 'Building B', NULL),
(3, 'Room 1', 1),
(4, 'Room 2', 1),
(5, 'Partition 1', 4),
(6, 'Room 1', 2);
basically the location looks like this
Location Tree View
---
.___ Building A
. .___ Room 1
. .___ Room 2
. .___ Partition 1
.
.___ Building B
. .___ Room 1
on tblAssetPlacement, the column PlacementDate is a unix timestamp stored as bigint.
the higher of its value means it is the latest placement.
so i had created a sql to pull the data like this:
SELECT
tblasset.AssetName,
tblassetplacement.OfficerInChargeID,
tblassetplacement.Locationid,
tbllocation.LocationName
FROM
tblasset,tblassetplacement, tbllocation,
(select tblassetplacement.AssetID,tblassetplacement.LocationID,MAX(tblassetplacement.PlacementDate) AS LatestDate from tblassetplacement group by tblassetplacement.AssetID) temptbl
where
tblasset.AssetID = tblassetplacement.AssetID
and tblassetplacement.PlacementDate = temptbl.LatestDate
and tblassetplacement.AssetID = temptbl.AssetID
and tbllocation.LocationID=tblassetplacement.LocationID
;
this will output:
---
AssetNameOfficerInChargeIDLocationIDLocationName
IPODSusan6Room 1
Sony PS3Fred5Partition 1
i would need to have a column ParentLocationName that directly shows the name of the parent's location as below:
---
AssetNameOfficerInChargeIDLocationIDLocationNameParentLocationName
IPODSusan6Room 1Building B
Sony PS3Fred5Partition 1Building A
is there a way to pull the parent's location name in one sql syntax?
Suppress Output For One Column...SOLVED
Sep 14, 2006Is there a way to supress output on one column in a SP, using data from the same row?
Like This:
SELECT Last, First, DOP, dbo.fnDueDate(DOP, 3, GETDATE()) AS NextQDue, dbo.fnDueDate(DOP, 6, GETDATE()) AS NextNSPDue, DATEADD(m, 1, DOP)AS InitialNSPDue, DATEADD(m, 1, DOP) AS InitialAssessDue, DOT, DisReason, DATEADD(m, 1, DOT) AS DisSummDue, Facility, Active
FROM dbo.tblResidents
But which returns null for some of the columns if DOT is not null?
DOT is the Termination Date, so the only columns that have any meaning once there is data in the DOT column are DisReason and DisSummDue. Also, if DOT *is* null, then the above columns also have no meaning.
I tried several variations of the following, but I can't figure it out
CREATE PROCEDURE [dbo].[spTesting] AS
BEGIN
SELECT Last, First, DOP, dbo.fnDueDate(DOP, 3, GETDATE()) AS NextQDue, dbo.fnDueDate(DOP, 6, GETDATE()) AS NextNSPDue, DATEADD(m, 1, DOP) AS InitialNSPDue, DATEADD(m, 1, DOP) AS InitialAssessDue, Facility
FROM dbo.tblResidents A
WHERE DOT IS NULL
UNION
SELECT Last, First, DOP, DOT, DisReason, DATEADD(m, 1, DOT) AS DisSummDue, Facility
FROM dbo.tblResidents I
END
GO
----------------
-Stephen