hi, i would like to store just the time part in sqlserver 2000. time part with default date which is 1899 - 12- 30 in my sql server. but i read some articles which mentioned default date as 1900 - 01 - 01. i am not sure which one but if i enter just the time part in my sql server and do the query i get 1899 -12-30 and (time part). anyway, i am just wondering whether it is possible to set the trigger or constraints to strip off the date part of entered value and replacing it with default is ok.
the reason is i need to some queries on time part only which is giving some problem now.
I'm wondering what is the best way to store time-series data in an SQL database?? I've done a bit of investigating on the rrdtool (round robin database tool) that is used in a lot of nix based solutions for monitoring network equipment. I have a need to collect performance data from servers and routers and then produces some nice graphs from that data. I'm just not sure who i should store that data without the database growing to some huge size.
1. How do I store audio and video files in sql server 2000. 2. Is it possible to store and retrieve audio and video files using t-sql 3. Which is the most efficient way to store and retrieve audio and video files in sql server
Is there a way to store a time in the SQLServer database, without the date? (e.g. just simply "HH:MM" instead of "HH:MM DD/MM/YYYY" which it stores it in)
Hi,I have a table which I need to store times in. What is the best way of doing this? The only way I can think of so far is to save the times as a datetime such as '1/1/1900 03:00 PM' then format the date as "hh:mm tt" so that the day isn't displayed. Is this the best method?Thanks,Curt.
I am using Jrun 3.1 + MS SQL Server 2000 + JSPs + Java on W 2000. I am having a lot of trouble storing Unicode characters in MS SQL Server since my 1.2 JDBC driver seems to assume that my characters belong to the Windows basic-Latin character set. Does anyone know how to solve this problem?
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
Scenario: We want to stage all data coming into our database from various applications. We have a generic control where all data goes through to get to the database.
We want have the data staged in a 'staging database'. My question is, what would be the best way to store the staging database in the database.
I'm thinking storing it as XML in a SQL column. The reason for this is because the data could change from application to application. It would be impossible to create a relational table because maintenance would be a nightmare.
Has anyone ever done something like this. Would you save the node names in another table in SQL?
Hi folks,Is it possible to store Binary Files in MS SQL 2000 ??? Say I have a100K PDF or a 150K word document. Is it possible to store this in afield in MS SQL and pull it out somehow? We're using ColdFusion on theserver on Apache.Thanks,Ringo
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 --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'
This 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
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.
hi. 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!
Dear 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
Im having a lot of trouble inserting a small time value into a table cell. I gave the cell column the data type 'DateTime', i found i couldnt manually insert a time only value such as '12:30 PM' into a column with 'SmallDateTime'. Something about a "SmallDateTime Overflow Error". However if i enter a similar time value into a table column with the data type 'DateTime' it will happily accept it and leave it as entered.
The real problem seems to be when i try to send a time value to that column with my ASP.NET application. Because it inserts the time value and todays date. So that if i send:
12:30 PM
It will be stored as:
15/11/2003 12:30:00 PM
I only want to store the short time, not the date especially not the date that row was created on because thats useless for the purposes of what my application is trying to achieve and just creates problems down the track when selecting rows.
Hello I have and tested in 2 way using variable and constant. Query 1: This query I am using vaiable "@last" for the date value. this takes to run for 2 minute .
Query 2: Here I am using value '11/05/2002' instead of varialbe and it is running within a second.
Why these much difference to run this same query?. Can anybody give correct reason or solution for this. Thanks, Ravi
query 1: DECLARE @last smalldatetime SET @last ='11/05/2002'
select distinct(o.account_num),a.enterprise_num,a.site_nu m from oesd.dbo.order_header o WITH(NOLOCK) INNER JOIN oesd.dbo.account a WITH(NOLOCK) ON a.account_num = o.account_num and o.added_date > @last UNION select distinct(o.account_num),a.enterprise_num,a.site_nu m from oesd.dbo.order_header o WITH(NOLOCK) INNER JOIN oesd.dbo.account a WITH(NOLOCK) ON a.account_num = o.account_num INNER JOIN Leap.dbo.ENTERPRISE_STATE es WITH(NOLOCK) ON (a.enterprise_num = es.ENTERPRISE_NUM AND o.last_updated_date > @last)
Query 2:
select distinct(o.account_num),a.enterprise_num,a.site_nu m from oesd.dbo.order_header o WITH(NOLOCK) INNER JOIN oesd.dbo.account a WITH(NOLOCK) ON a.account_num = o.account_num and o.added_date > '11/05/2002' UNION select distinct(o.account_num),a.enterprise_num,a.site_nu m from oesd.dbo.order_header o WITH(NOLOCK) INNER JOIN oesd.dbo.account a WITH(NOLOCK) ON a.account_num = o.account_num INNER JOIN Leap.dbo.ENTERPRISE_STATE es WITH(NOLOCK) ON (a.enterprise_num = es.ENTERPRISE_NUM AND o.last_updated_date > '11/05/2002' )
Hi!We have SQL Server 2000 on virtual machine (slowly). We run a pair ofstored procedures from ADO.NET 2.0 (WebService):- first procedure: insert into table A a data row with correlationID- next procedure: search for inserted record in tab A bycorrelationID and insert value into tab B with constraint to tab A.The problem is when we try to do this in short time (few minutes)thousands times (5000/6000 pairs in 5/6 minutes) in 100 parallelthreads:query in second procedure cannot find inserted record in firstprocedure.Everything is dirty read. no transactions.Have you any ideas?edi
After DST change (US/Canada), when I run any of my reports, I get this error:
"An internal error occurred on the report server. See the error log for more details. (rsInternalError) Get Online Help Specified argument was out of the range of valid values. Parameter name: date "
Hello there,I just want to ask if storing data in dbase is much better than storing it in the file system? Because for one, i am currenlty developing my thesis which uploads a blob.doc file to a web server (currently i'm using the localhost of ASP.NET) then retrieves it from the local hostAlso i want to know if im right at this, the localhost of ASP.NET is the same as the one of a natural web server on the net? Because i'm just thinking of uploading and downloading the files from a web server. Although our thesis defense didn't require us to really upload it on the net, we were advised to use a localhost on our PC's. I'll be just using my local server Is it ok to just use a web server for storing files than a database?
Hi guys, When I thought everything is okay with this script, I got a new problem... I have a VBA's script from Excel 2003 that builds sql script and retrieves data from SQL SERVER 2000. in order to make the sql running, I need to use a multi - batch processing, to pass and execute every command line once a time.
Up to here, I am using a test case with Account number = '123456' and getting the desire results. The code below is running okay with the test case, but when changing the account number (mark as yellow in the code) to include all the accounts (or just one other account), I am getting the following ERROR: run - time error '-2147217871 (80040e31)' - [Microsoft] [ODBC SQL Server Driver] time out expired.
Now, if I take the same code, with the condition that generates the ERROR, and try it into SQL Server, I get the results without errors. Thanks in advance, Aldo.
Below the code:
Code Snippet Function QuerySalesAging() '-------------------------------------------------------------- 'MUST !!! References: Microsoft ActiveX Data Object 2.1 Library '--------------------------------------------------------------
Dim ConnString As New ADODB.Connection Dim RecordSet As New ADODB.RecordSet
'Report Criterias Criteria05 = " AND " & "Accounts.ACCOUNTKEY Between " & AccountKeyAsRange ' -- ==> With AccountKeyAsRange = '123456' AND '123456' it works okay. ' -- ==> With any other value, in example AccountKeyAsRange = '123456' AND '9999999999' it get's ERROR.
CmdLine01 = " USE " & CompanyName
' Check and drop temporary table TemporaryTableName = "CTE" ' The table is a regular one CmdLine02 = " if object_id('" & TemporaryTableName & "') is not null exec('DROP TABLE " & TemporaryTableName & "') "
CmdLine03 = " SELECT ..." CmdLine03 = CmdLine03 & " INTO " & TemporaryTableName CmdLine03 = CmdLine03 & " FROM ..." CmdLine03 = CmdLine03 & " WHERE " & "(" & Replace(Criteria05, "AND", "") & ")" CmdLine03 = CmdLine03 & " ORDER BY ..."
My forms are taking user input, then HtmlEncoding them prior to being stored in the SQL DB. For some reason, SQL is storing quotes as � and it is causing the HTML when decoded in the page to not be rendered properly.
Has anyone come across this issue before?
For example (without encoding for readability):
SQL should store the parsed string as: <a href="someurl" class="main">
but for some reason it's being stored as: <a href=�someurl� class=�main�>.
Is there a way to keep track in real time on how long a stored procedure is running for? So what I want to do is fire off a trace in a stored procedure if that stored procedure is running for over like 5 minutes.
We are using SQL Server 2008 as our database and use Access as a GUI. I am looking to create a form in Access where employees can access their time card and request changes from management. I want to use the format from the attached screen shot for the form. I pretty much know how to do it all, the only point of complication is trying to figure out the easiest way to get the transaction punch record data on employee_punch_record into a format where I can easily populate the form in the horizontal format you see in the screen shot.
I am not super strong in SQL, but figure I can do it using a formatting table of some sort. quick and easy way to move transaction records into a more horizontally oriented record?
Hello Everyonen and thanks for your help in advance. I am developing a conetnet management system to allow for the storage of articles within a SQL Server 2000 database. I am using FreeTextBox as the editor for users to enter articles. I ahve two questions. First, many of the articles are quite lengthy and including HTML formatting go well beyond 8000 characters. How should I go about storing these articles? Should I use a TEXT datatype, or perhaps split the data into more than one row. This leads to my second quuestion. Many sites that display article type data break the artilce into multiple pages with page numbers or next links to page back and forth. I am not sure hot to go about implmeneting this. Any help on this topic would be greatly appreciated. Thanks.
I have some C# code that iterates through the session state, serializes each object and stores the binary representation in an SQL table with an 'image' column. The problem is: it doesn't work. SQL server doesn't throw an error (at least ADO.NET doesnt propagate it); the table is just left unchanged. The SP works (I tested it with a few simple values); the MemoryStream and byte array are being populated correctly and bound to the parameter correctly.
What am I doing wrong? Anyone have a better approach? I know there is a builtin way of storing state in an SQL server, but I only need to do this once--namely, when a user is redirected from non-secure to secure pages--so I don't want to take that performance hit,