Hello all. I have a nvarchar field in the format dd:hh:mm:ss I need to get a sum of these fields in the same format. So for example if the values are
01:23:00:00 and 00:01:00:00 then the sum of these fields should be 02:00:00:00. Can someone help me? Thanks.
Good morning...
I begin with SQL, I would like to add a field that will be date like 21/01/2000.
Actually i find just "datetime" format but give me the format 21/01/2000 01:01:20.
How to do for having date and time in two different field.
Sorry for my english....
Cordially
A newbie
Hi,I have an orders database and I need to be able to write a querythat groups sales by:1. Date2. Time of day (both am and pm)I currently have a date/time field named "Submitted" that storesjust the date.However, how do I store the time?I know I can store the date/time in the "Submitted" field Icreated. However, because of the time being stored in that field, Ican't execute an easy query such as:select * from database where submitted = '2/10/05'(reason being, the time portion makes each "Submitted" fieldrecord unique, so just the date won't be recognized)I realize there probably is a much better way to do this in theSQL query language that I am not aware of.Two questions:1. Based on what I'm trying to do -- query based on date and groupby number of sales for each hour of the day (am and pm) -- what is thebest approach to store the time?2. What does the query look like that querys by date and groupsorders by time of day (am and pm)?Thanks for your help.J
View 1 Replies View Relatedi want to create fulltext search for that i have started Microsoft Search component but when i am tring to open SQL server Enterprise manager, i am getting sql server group but it showing "no items" not any database or any other data. what to do.
please help me out.
If I have a datetime field, RequestDate, and a time field, DecisionTime, how do I add just the date portion of RequestDate to DecisionTime?
View 6 Replies View RelatedSample Table
USE [Testing]
GO
/****** Object: Table [dbo].[Testing] Script Date: 4/25/2014 11:08:18 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
[Code] ....
It seems to work fine with one million records.
Each primary key is unique, but the begindate is non-unique, and i guess even if i use datetime2 and add nanoseconds, from what i have read, there is a chance that i could have a duplicate datetime since the date is imported via XML from multiple sources.
I hope to update a DateTime column value with a Time input parameter. Poor attempt below but it looks like the @ApptTime param is coming in as 10:45:00.0000000 and I might have an existing @SendOnDate as: 2015-10-05 07:00:00.000...I hope to end up with 2015-10-05 10:45:00.000
ALTER PROCEDURE [dbo].[SendEditUPDATE]
@QuePoolID int=null
,@ApptTime time(7)
,@SendOnDate datetime
[code]...
Hello All,
I am not sure what is going on, but ever since I installed my ASP.NET shopping cart I keep getting the following error message in my Event Viewer under Application.
If anyone out there has any idea why this is happening please let me know because it seems to be crashing my other sites on my web server.
I have a Web Server which connects to a separate SQL 2000 Server. This error message is on my Web Server.
Thank you all in advance for your help!
Error:--------------------------------------------------------------------------------------------------------------------------
Event code: 3005 Event message: An unhandled exception has occurred. Event time: 1/6/2007 1:07:17 PM Event time (UTC): 1/6/2007 6:07:17 PM Event ID: 518a9db199214e3e807dd676e85f3d5a Event sequence: 103 Event occurrence: 6 Event detail code: 0 Application information: Application domain: /LM/W3SVC/1309570491/Root-1-128125656181152736 Trust level: Full Application Virtual Path: / Application Path: C:InetpubStoreV7 Machine name: CDMLWEB Process information: Process ID: 2376 Process name: w3wp.exe Account name: NT AUTHORITYNETWORK SERVICE Exception information: Exception type: SqlException Exception message: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. Request information: Request URL: http://www.bestpricetags.com/default.aspx Request path: /default.aspx User host address: 207.38.200.139 User: c2e1ff27-e0ea-4c7f-ac61-5b1716651d3b Is authenticated: True Authentication Type: Forms Thread account name: NT AUTHORITYNETWORK SERVICE Thread information: Thread ID: 10 Thread account name: NT AUTHORITYNETWORK SERVICE Is impersonating: False Stack trace: at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error) at System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult asyncResult, TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParserStateObject.ReadPacket(Int32 bytesExpected) at System.Data.SqlClient.TdsParserStateObject.ReadBuffer() at System.Data.SqlClient.TdsParserStateObject.ReadByte() at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.SqlClient.SqlConnection.Open() at AspDotNetStorefrontCommon.DB.GetRS(String Sql) at AspDotNetStorefrontCommon.DB.GetSqlN(String Sql) at AspDotNetStorefrontCommon.AppLogic.IPIsRestricted(String IPAddress) at AspDotNetStorefront.Global.Application_AuthenticateRequest(Object sender, EventArgs e) in C:InetpubStoreV7App_CodeGlobal.asax.vb:line 103 at System.Web.HttpApplication.SyncEventExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) Custom event details:
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Hi Experts,
I have an urgent situation expecting your advice. I have a .net front end application running against SQL 2000 DB in a XP box, it has been working fine for two years, recently when the .Net app starts, it works still ok getting data from sql database on the first form (dislpay data retreived from a table and view in a master-client grid), while I often get timeout error when I proceed with other processes that need sql database and I noticed that the HD light on SQL server machine (XP) kept flashing when timeout error occured, seems it kept reading/writing disk. I could not even close enterprise manager from SQL Server box when the HD was busy, and it also took a considerable long time to reboot the machine. sometimes I just shut down the machine (power off) and turn on the power again, then the sql server came back to life and could work stable for a couple of days.
Do I need reindex? reinstall sql server? or might it be relating to hardware issue?
Thanks so much!
Eric
Hi,
We have few branches with individual dbs are sitting in the branches itself. Periodically all the data need to be updated to a single HQ database. At the moment, all the tables's primary key are unique and each branch will have branch ID's in the transaction tables.
e.g. Branch1 data need to be updated to HQ db. However, HQ db records which are related to Branch 1 data only need to be updated back to Branch 1. other branch records should not goto Branch1.
Similaryly other branch records need to be updated/replicated back to HQ database. And only records related to the particular branch need to be updated from HQ (if any changes made)..
At the moment, i can do simple replication from one db to another db only... Can anyone guide/give suggestions on how to go about doing this?? If possible, please give steps involved...
TX
I have passed createdDate from UI to Stored procedure.createdDate field declared with DateTime.it is having value 2014-07-01.I need to fetch records from the database based upon the created field.but Create_TM in database having value Date with timestamp.so how would i change the createdfield in stored procedure.
ALTER PROCEDURE [dbo].[ByDateRange]
@Feed VARCHAR(50),
@CreatedDate DATETIME
select * from Date_table where Create_TM = @CreatedDate
I am using VS .net 2005 to develop a web application with sql server 2000, but the SQL servrer 2000 is setup on other computer,so, my web server is setup on A machine and SQL servr is setup on B machine.I got a problem when i try to connect to SQL server through the Web form as following error."An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)"
I had try to setup the MDAC 2.8 on my Web Server, but i got a setup error sth like "MDAC 2.8 cannot install on your machine because it is not support your windows version.........."
Why i can't connect to SQL 2000 through the Web server???
It is a urgent case, Please help thx a lot.
Hi,
I am currently working on a project which requires reports that need multi-pass queries. I am currently using Crystal Report 8.5 and am not sure how to use multi-pass queries.
Here's some explanation about what I need :
A report has 10 different columns (counters). However, to get the values in all the columns, the filters (WHERE clause) are different for all the columns.
The only way I can think of doing this in Crystal Reports 8.5 is using Sub-reports (that will kill my performance) or then write stored procedures to populate an intermediate table on which the report will be based on.
Is there any reporting tool in the market that will help me pass multiple queries (with different WHERE clauses) in the same report.
I would appreciate your help on the above the earliest.
Thank you.
-- Parag
Hi every one
I am new meber of this community with an urgent new problem with SQL server DTS process:) pls HELP!
PROCESS DETAILS:
My data base(db) is on main frame(flat file with hierarchical db format) I used vb script to get that db and split it in to three files so processing is easy after this I use dts PROCESS WITH EXECUTE SQL TASK in combination of some bulk insert scripts to pick these files from server directory and import it in to sql server tables. I know I could have use bulk insert task in dts but my formate file was giving me a problem while I try to develop it through that process so I ended up using tsql bulk insert statment with formate file in the EXECUTE SQL TASK .
PROBLEM :
My problem is I have to manually activate VB script where as I want this to be part of my DTS packet so it can be automated.
I know there is Active x scripting task in DTS but have not used it can some one please tell me if this script can be put in to Active X scripting task with or with out any modifications and also what are the modifications I need to do so it can run before other DTS task (which picks the file and insert it in three different tables after format file application.
this is the VB SCRIPT:Const
ForReading = 1,ForWriting = 2
Dim fso, SourceFile, TargetFile, retstring,Level1No, Level2No, Level3No, Level1Str, Level2Str, Level3Str
Level1No = 0
Level2No = 0
Level3No = 0
Set fso = WScript.CreateObject("Scripting.FileSystemObject")
'Set SourceFile = fso.OpenTextFile("C:SourceWGEE.NONX.GMM220D.UNLOAD.G00178888V00", ForReading)
Set SourceFile = fso.OpenTextFile("C:SourceDataNotepad.txt", ForReading)
set l1 = fso.OpenTextFile("C:SourceLevel1.txt", ForWriting,True)
set l2 = fso.OpenTextFile("C:SourceLevel2.txt", ForWriting,True)
set l3 = fso.OpenTextFile("C:SourceLevel3.txt", ForWriting,True)
Do While SourceFile.AtEndOfStream <> True
retstring = SourceFile.ReadLine
if Instr(1,retstring,"PACM",0) > 0 then
Level1No = Level1No + 1
Level1Str = Rpad(Level1No," ",10)
l1.WriteLine(Level1Str & retstring)
End if
if Instr(1,retstring,"PACPRS",0) > 0 then
Level2No = Level2No + 1
Level2Str = Rpad(Level2No," ",10)
l2.WriteLine(Level1Str & Level2Str & retstring)
End if
if Instr(1,retstring,"PACACC",0) > 0 then
Level3No = Level3No + 1
Level3Str = Rpad(Level3No," ",10)
l3.WriteLine(Level1Str & Level2Str & Level3Str & retstring)
End if
Loop
Function Rpad (MyValue, MyPadChar, MyPaddedLength)
Rpad = MyValue & string(MyPaddedLength - Len(MyValue), MyPadChar)
End Function
THANKS ALL
I've a database in 2000 which needs to be accessed from 2005 via linkedserver. I've tried the GUI options and it is failing. One thins is that ourboth servers have hyphen('-' not underscore, could that be a problem) in thenames, like 2k-srv and 2k5-srv as hosts, but the instances are default.Can anyone give me the steps please.TIANasir
View 4 Replies View RelatedHi,
Can you help me how to find out sessions in SQL Server 2000.
Also is there any way to see the outstanding sessions for a database?
Thanks in advance,
Vasu.
I was using detach & attach method to move few 2000 databases to the instance 2005 on the same server...After moving a couple of databases which worked fine...Now i am not able to log into 2000 database...i guess the default database which i was connected had been moved at so its not letting me in to 2000 server..
TITLE: Connect to Server
------------------------------
Cannot connect to SQL-CENTRAL-DEV.
------------------------------
ADDITIONAL INFORMATION:
Cannot open user default database. Login failed.
Login failed for user 'test est'. (Microsoft SQL Server, Error: 4064)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=4064&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
how do i fix the problem
thanks
Hi
if i try to delete data via MFC from Microsofts SQL Server 2000
i get following message: Data can be read only.. Weird i can write and read already. do i have to setup anything in Micrsofts SQL Server 2000 ??
please help its urgent.
Greets
Indian
Hi,
I have the following data in a table called "Products" :
Product Qty LHinging RHinging
-------- ----- -------- --------
Panel_1 1 R
Panel_1 1 L
Panel_1 1 R
Panel_1 1 R
Panel_2 1
Panel_2 1 L
I need to group this data like this :
Product Qty LHinging RHinging
-------- ----- -------- --------
Panel_1 3 L R
Panel_2 1
Panel_2 1 L
How can I do that ???
Thanks !
Hi there,
I got a shared web hosting server with sql server db which is 2k, and now ive got dedicated with godaddy where i installed sql 2005 express edition, now my shared hosting ppl gave me db bkup which i cant restore , so i copied all data from asp.net web based enterprise manager into an excel sheet and am trying to copy that data into sql2005 management studio express, however it doesnt let me do that.
I am trying to use openrowset, but my knowledge in sql is minimal so cant run that either.
can you please please please tell me how to do this asap, its very urgent as my contract runs out with shared hosting on 31 and ive to switch this site live by tomorrow.
please let me know asap wat to do, i can pay also for this job?
amit
amitsharma1977@gmail.com
I want to update the time in a datetime field with the current time.
Fields current value is:
2013-11-11 00:00:00.000
I want to insert this into another table and when I do I want to grab the current time and update that field.
field name: picked_dt
Table: oeordlin
or is there a way through sql to update the time when the picked_dt is updated?
Hi All,
Iam experiencing lot of errors for users who are accessing from application to the database .
ERROR:
"timeout occurred while waiting for resources to execute the query"
I wpuld appreciate if you could tell me work around for this problem.
Thanks,
Reddy
I am trying to do a unattended upgrade of an MSDE 2000 named instance, say MyInstance, to a SQl Server Express Advanced edition. The set up is Mixed Mode Authentication. ( I can't use Windows Authentication for business reasons).
The error I get is
SQL Server Setup Cannot Upgrade the specified instance by using SQL Server
Creditial. You must use Windows Authentication creditial for the upgrade.
Details are as follows:
OS: Windows XP Prof (SP2).
Original install: MSDE 2000
Instance name: MyInstance
Authentication: SQL
Registry Entry: HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL ServerMyInstanceMSSQLServer
Key LoginMode has a value of 2 ( corresponding to Mixed Mode Auth)
Upgrade attempted to: SQL Server Express Adv.
Command Line: start /wait setup.exe /qn LOGNAME=C:setup.cab /settings E:Sql2005SqlTemplate.ini
SqlTemplate.ini Contents:
[OPTIONS]
INSTANCENAME=MyInstance
SECURITYMODE=SQL
UPGRADE=SQL_Engine
UPGRADEUSER=sa
UPGRADEPWD=My$Password
The error I get in Summary.txt:
Machine : xxxxxXP
Product : SQL Server Database Services
Error : SQL Server Setup cannot upgrade the specified instance by using SQL Server credentials. You must use Windows Authentication credentials for the upgrade.
Can't I use SQL Authentication? I could not find a definitive answer on this one. Here's one thread that seem to indicate the same issue
http://groups.google.com/group/microsoft.public.sqlserver.msde/browse_thread/thread/90fb4b7182a55d54/f3128b447585f29d%23f3128b447585f29d
My questions:
- Can I use SQL Authentication?
- Where am I going wrong?
- I know the error message says to use Windows Authentication but I don't understand why I am I forced into using that.
Any insightsopinions will be highly appreciated.
Best
LW.
I've successfully carried out pull subscription using Wizard in SQL server 2000.
But NOW its the time to deploy the project & I've to do it programatically........
i tried to use the default stored procedures like "sp_addmergepullsubscription ", "sp_addmergepullsubscription_agent ", "sp_reinitmergepullsubscription ", with respective parameters.
Then using replmerge.exe i tried to execute the batch file But could not succeed......
Can anyone guide me from the Basics ??? (i knw the Replication Architecture its heirarchy....but have NO idea of performing it Programatically......!!!!!!!!)
by BASICS, i mean......how should i execute the resp. stored procedures with what parametes & the order i should follow in order to successfully pull a merge subscription in SQL server 2000.
if a Sample code is mentioned as example, would be better for me to understand....
Thanking all of you in anticipation...........
Mandar
Is there a better way of doing this?
DECLARE @PriorDayDate as varchar(10)
DECLARE @CurrentDayDate as varchar(10)
DECLARE @StartHourTime as varchar(10)
DECLARE @EndHourTime as varchar(10)
DECLARE @PriorDayDateANDStartHourTime as varchar(20)
DECLARE @PriorDayDateANDEndHourTime as varchar(20)
DECLARE @CurrentDayDateANDStartHourTime as varchar(20)
DECLARE @CurrentDayDateANDEndHourTime as varchar(20)
SELECT @PriorDayDate = convert(varchar,(GETDATE()-1),126)
SELECT @CurrentDayDate = convert(varchar,(GETDATE()),126)
SELECT @StartHourTime = convert(varchar,(dateadd(hh, -1, getdate())),108)
SELECT @EndHourTime = convert(varchar,getdate(),108)
SELECT @StartHourTime = left(@StartHourTime, 2) + ':00:0000'
SELECT @EndHourTime = left(@EndHourTime, 2) + ':00:0000'
SELECT @PriorDayDateANDStartHourTime = @PriorDayDate + ' ' + @StartHourTime
SELECT @PriorDayDateANDEndHourTime = @PriorDayDate + ' ' + @EndHourTime
SELECT @CurrentDayDateANDStartHourTime = @CurrentDayDate + ' ' + @StartHourTime
SELECT @CurrentDayDateANDEndHourTime = @CurrentDayDate + ' ' + @EndHourTime
--select @PriorDayDate
--select @CurrentDayDate
--select @StartHourTime
--select @EndHourTime
--select @PriorDayDateANDStartHourTime
--select @PriorDayDateANDEndHourTime
select cast(@PriorDayDateANDStartHourTime as datetime) as 'PD Start Time'
select cast(@PriorDayDateANDEndHourTime as datetime) as 'PD End Time'
select cast(@CurrentDayDateANDStartHourTime as datetime) as 'CD Start Time'
select cast(@CurrentDayDateANDEndHourTime as datetime) as 'CD End Time'
HI there,I currently store the date using the getdate() functionbut how can I store just the time or seperate the time off from adatetime datatype?M3ckon*** Sent via Devdex http://www.devdex.com ***Don't just participate in USENET...get rewarded for it!
View 4 Replies View RelatedThis is a solution for a very specific problem, and it's one that you'll hardly ever use, but it's important to know about that one scenario where it can save your neck. Ordinarily, stored procedures are only recompiled if they're no longer in the procedure cache. But if a stored procedure's execution plan is still in the cache, then SQL Server reuses the compiled storedprocedure and its existing execution plan. This is almost always the best course of action. Almost always, but not always.Sometimes, however, reusing an existing plan doesn't offer the most efficient performance. Imagine, for example, that your stored procedure accepts a parameter that determines the natureof a JOIN operation. The results can vary in a big way, so you wouldn't want your procedure to be locked into an execution plan that might be completely inappropriate for that JOIN. In a highlyspecialized case like this, you might want to force SQL Server to recompile the procedure every time the procedure runs. Doing so comes at a performance cost, but this might be offset by thesavings you gain in not executing the procedure with an awful compiled execution plan. Consider carefully whether to use this approach (or whether to re-engineer the over-design of yourapplication to avoid this situation in the first place). Should you need to instruct SQL Server to recompile each time, add the WITH RECOMPILE directive to the procedure, like this: CREATE PROCEDURE ProcName @Param int /* ... other parameters */ WITH RECOMPILE AS /* ... procedure code follows */
If we omit "WITH RECOMPILE", what will be the consequence? Thanks
Hi,
I have a SSN field that I need to encrypt. Only persons who wants to get information about a user should be able to decrypt that field. What is the best way to encrypt and decrypt the ssn field? Thanks
Does anyone know how to use Formula Field in SQL Server 2000. I have googled a lot, but haven't been able to find out how to use it..
Thx
I am a little embarrased to be asking this question because I know that the answer must be simple but I am still a nube at SQL. I am stuck with a simple query that copies one field of data into another. Below is the script that I am trying to execute.
UPDATE table
SET field2 = field1
I get an error from SQL Server that says that the String or binary data would be truncated. I am assuming that the SQL server is trying to concatenate all the value of field1 into field2. All I am trying to do is copy the values of one field into another for each and every row in the table. Thanks in advance.
I have a table that has a int field that contains unique nubers.if I need to insert 4000 or so new records into this table using a SQLcommand how would I be able to fill this field with the next availablenumber?This will be a manual procedure done maybe once a year so the nextnumber will be known ahaed of time. but the insert command need toincrement this number by one everytime it puts a new row in.
View 2 Replies View Relatedhi. i am managing a sql server 2000 database. for some reason, a table got dropped from the database--why i don't know; i'll worry about that later. is there a way to "rollback" the database to a point in time...say like 5 minutes ago? I have taken the database offline for the moment so no more changes can take place. i have never done this before and i would really appreciate some advice/online tutorials/help on this matter. i do have a full backup of the database because of nightly backups. any help will be greatly appreciated. thanks!
View 1 Replies View RelatedDear All,Do anyone know where I can find some useful documentation or anydocumentationabout restoring database to the point in time (using logs).SQL Server 2000 on Win 2000Thanks for all,M&M
View 1 Replies View Related