SOLVED: TDSSNIClient Initialization Failed With Error 0x7e
Nov 13, 2005
solution found here:
Similar Messages:
ADVERTISEMENT
Jan 31, 2007
I'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.00The ERRORLOG has the following 15:02:05.77 spid9s
Starting up database 'model'. 15:02:05.77 Server
Error: 17182, Severity: 16, State: 1. 15:02:05.77 Server
TDSSNIClient initialization failed with error 0x32, status code 0x1c. 15:02:05.77 Server
Error: 17182, Severity: 16, State: 1. 15:02:05.77 Server
TDSSNIClient initialization failed with error 0x32, status code 0x1. 15:02:05.77 Server
Error: 17826, Severity: 18, State: 3. 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. 15:02:05.77 Server
Error: 17120, Severity: 16, State: 1. 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.
Aug 15, 2007
When 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
Jul 13, 2006
Hi,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:- /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
Jul 23, 2007
I 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 0x.
MSSQLServer also logs the following error to the app event log:&Failed to initialize the Common Language Runtime (CLR) v2.0.50727 with HRESULT 0x. 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
Feb 8, 2005
Hi,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=SQLIt 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.
May 11, 2006
We'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?
Jul 24, 2007
Hi,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
Sep 23, 2015
I'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
Apr 14, 2005
Hi 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.LoadLine 33:
Dim cnn As New SqlConnection( _Line 34:
"Data source=Initial Catalog=NUser ID=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.]
mon.DbConnectionOptions.GetKeyValuePair(String connectionString, Int32 currentPosition, StringBuilder buffer, String& keyname, String& keyvalue) +1203
mon.DbConnectionOptions.ParseInternal(Hashtable parsetable, String connectionString, Boolean buildChain, Hashtable synonyms, Boolean firstKey) +127
mon.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
Feb 23, 2006
I 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 8007056BPushing 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 .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 ??thankfullyMatti
Feb 11, 2008
It 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=Data Source=User ID=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=Data Source=User ID=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.
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 SqlCeD
eCE.CloseSharedConnection();
SqlCeConnectionPool.CloseSharedConnections();
SqlCeRemoteDataAccess rda = new SqlCeRemoteDataAccess();
rda.InternetUrl = webSynchronizationU
rda.InternetLogin =
rda.InternetPassword =
rda.LocalConnectionString = eCE.ConnectionStringWithoutC 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
Feb 21, 2007
I 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)...
Jan 3, 2001
I 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 advanceReddy
Apr 21, 2008
Dear 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 stateFailed to decrypt protected XML node &DTS Password& with errorWhat's wrong with the package ?Thanks in advance.Best regards,Hery
Sep 13, 2001
I 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
Sep 9, 2015
Many 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
Feb 29, 2008
Hi 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..
Mar 26, 2007
Sep 20, 2006
Hi,Now it磗 working fine!!!!On the Firewall of the Server, I磛e added the default port 1433 on the exceptions.thanx!!!!
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.
Jun 24, 2006
Upper case sentece in normal case sentence
Feb 25, 2003
I'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 + 100Main = DTSTransformStat_OKEnd FunctionorFunction Main() dim x DTSDestination(&a&)=DTSSource(&a&)x = DTSSource(&b&).Value + 100 DTSDestination(&b&).Value = xMain = DTSTransformStat_OKEnd Function
Dec 8, 1999
Hello 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 onthe 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
Mar 12, 2008
Hi, hope someone can help. I have two tablesTest1 that lists all the training courses that i can count to find out the total as below.select count (distinct Training_Course) as totalfrom Test1Then 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 TotalAttendfrom Test2Group by Cust_idWhat 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.
Mar 23, 2008
Hello,uery - I'm StuckI'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_idFROM taglinkWHERE m_id && '25'AND t_idIN (SELECT t_idFROM taglinkWHERE m_id = '25')which returns:m_id
33 34 34 35 35 35 36I want to count the number of times that m_id is returned so that the results of my query will be:m_id | count33
| 1In my attempt to do this, I run the following query:SELECT m_id, count( m_id ) AS "count"FROM taglinkWHERE m_idIN (SELECT m_idFROM taglinkWHERE m_id && '25'AND t_idIN (SELECT t_idFROM taglinkWHERE m_id = '25'))GROUP BY m_id The problem I'm having is that the query returns:m_id | count
| 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!
Aug 9, 2006
&&i try to do a simple insert using SQLDataSource and a Stored ProcedureI try all night and always have this error&Object Must Implement IConvertible &I can鈥檛 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]&------------
Aug 9, 1999
i have a table "employee" , i have given grant all rights to a userin 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.
May 14, 2008
I 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 @StageSelectconvert(datetime,' 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 aiUnion allselect ' 00:00:00.000',2,172.80,7.92,0.0500,0Union allselect ' 00:00:00.000',4,0.00,0.00,0.0700,0UPDATEs1SETs1.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 s1select * from @StageMy desired Results are 00:00:00.9..0000 00:00:00..920.3 00:00:00.0.0But I am geting 00:00:00.9..0000 00:00:00..920.3 00:00:00.0.8Due 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!
May 28, 2008
First, 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 20The 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!
May 30, 2008
hi guys, im having a sql problem for reporting here. here is the tableCREATE 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, , 'John'),(2, 6, 1, , 'Susan'),(3, 3, 3, , 'Erik'),(4, 4, 3, , 'Albert'),(5, 5, 3, , '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 thisLocation Tree View---.___ Building A.
.___ Room 1.
.___ Room 2.
.___ Partition 1..___ Building B.
.___ Room 1on 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.LocationNameFROMtblasset,tblassetplacement, tbllocation,(select tblassetplacement.AssetID,tblassetplacement.LocationID,MAX(tblassetplacement.PlacementDate) AS LatestDate from tblassetplacement group by tblassetplacement.AssetID) temptblwhere tblasset.AssetID = tblassetplacement.AssetID and tblassetplacement.PlacementDate = temptbl.LatestDate and tblassetplacement.AssetID = temptbl.AssetID and tbllocation.LocationID=tblassetplacement.LocationID;this will output:---AssetNameOfficerInChargeIDLocationIDLocationNameIPODSusan6Room 1Sony PS3Fred5Partition 1i would need to have a column ParentLocationName that directly shows the name of the parent's location as below:---AssetNameOfficerInChargeIDLocationIDLocationNameParentLocationNameIPODSusan6Room 1Building BSony PS3Fred5Partition 1Building Ais there a way to pull the parent's location name in one sql syntax?
Sep 14, 2006
Is 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, ActiveFROM
dbo.tblResidentsBut 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 outCREATE PROCEDURE [dbo].[spTesting] ASBEGINSELECT
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, FacilityFROM
dbo.tblResidents AWHERE DOT IS
NULLUNIONSELECT Last, First, DOP,
DOT, DisReason, DATEADD(m, 1, DOT) AS DisSummDue, FacilityFROM
dbo.tblResidents IENDGO-----------------Stephen
Copyrights 2005-15 , All rights reserved}