I face following problem by last few day, please help me for the same
My Mssql server 2000 with service pack 3 use for my lan bas users, normally they can work fine without any problem, but some time user not able to retrieve information from server. I had debugged this problem and found one small table with 50/60 records not retrieving for so long at clients machine. I open enterprise manage and trough that try to open table, but server in try mode only not show a single row after long time and give message client time out.
I open query analyzer and try to select * from table_name, it is also not retrieve a single row after long time and nothing got as a message.
Shutdown the server and restart , I am able to retrieve that table from EM, Query analyzer and from application also.
[MS SQL Server 7]We load a table from a text file using Data Transformation Services. Thesource file is already sorted by primary key order.After the DTS load, the default retrieval order on the target table (select* from targettable) appears to be random. I know that theoretically theretrieval order from a SELECT statement isn't guaranteed, but this is thefirst time that I've actually had a default retrieval not follow theprimary key order in the table. I'm thinking that what we're seeing is thephysical storage order of the rows in the table, which have been somewhatscrambled by the way that DTS loads a text file (probably some I/Ooptimization).Is there any way that we can get DTS to load the table in the order thatthe rows appear in the text file (assuming that's what our problem is)?The Project Lead really doesn't like the fact that the default retrievalorder isn't following the primary key.Regards,Lyle H. Gray
Columns are obviously fixed, but not rows. I want to show this data using lables and SqlDataReader for report purpose like; Label1.text=dr("value16").toString( ) Label2.text=dr("value28").toString( ) Label3.text=dr("value31").toString( ) etc
Do you have any idea how i can do it or am I approaching it in the wrong way????
where including/excluding a single column in an empty staging table would influence a resultset returning from distributed query? Both servers are SQL Server 2012. Nothing special about the staging table. It contains 12 columns with a mixture of INT and NVARCHAR(256) columns. In one case I exclude the column and the query returns in 17 seconds. When I include it the query does not return. Excluding the INSERT INTO the staging table and query returns in 17 secs with and without the column.
If I join Table1 to Table2 with a WHERE condition, isit the same if I would join Table2 to Table1 consideringthat the size of the tables are different.Let's assume Table2 is much bigger than Table1.I've never used MERGE, HASH JOINs etc, do any ofthese help in this scenario?Thank you
Hello, could someone pls help me with this table design.
I have a project table and a code table. The code table has things like priorities (High, Medium, Low).
Now, I want all projects to be able to use these 'global' codes as well as define their own. So, they could define their own priority code 'Critical', that only their project can see.
Hello.I am administering a SQL Server (Enterprise Edition on Windows 2003)from some month and can't understand what is going on in the latestweek (when the db grow a lot).The DB is around 250G, and has one table with 1 billion rows. It isperforming in a decent way, but can't understand why a particolar tablehas strong performance problem.I have a stored procedure that read table from table A and insert them,after processing in table B, and then move them in other table (similarto a Star Schema) for reporting.Table B is, for how the SP is written, not more than 3000 lines. TableB is very simple, has 3 rows, and no index.What is very strange is that performance of table B is really slow. IfI do a select count (*) from table_b it takes between 30s & 2minutes toreturn it has 0 lines. When the stored procedure insert 1000 lines, ittakes 20/30 seconds and it takes 20/30 seconds to delete them.To me it doesn't look like a lock problem, because it is slow also whenthe only procedure that access that table are stopped. I did an updatestatistics with fullscan on this table with no improvement.The DB is on a Storage Area Network that should perform decently. TheLUN I use is configured to use a piece of 32 disk that are used also byother application. I don't have performance data of the SAN. Themachine is an HP DL580 with 4 CPU (hiperthreading disabled), 8G of RAM,AWE and PAE and 5G reserved for SQL Server.I don't know what to do to solve this situation. Could it be a"corruption problem" that slow this table so much? is it possible thefact the db grow a lot in the last week created problem also to thissmall and simple table?Do you have any idea or hint on how to manage this situation, orpointer to documentation that can help in analizing this situation?For the ones that arrived till here, thank you for your time andpatience reading my bad english...Best Regards,MamoPSI can't rewrite the stored procedure, because it is part of a closedsource product.
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.
In my database/MY SERVER (SQL7/Win2K), I run a simple query with a Table/10000 rows (without cluster index): SELECT * FROM TABLE it take over 30s. Why it's slow? How can I check for reason? How to configure my server to improve performance? Thanks in advance. TH ---------------------------------- SP_CONFIGURE's RESULT in MY SERVER ----------------------------------
Table 'spt_values'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0. name minimum maximum config_value run_value ----------------------------------- ----------- ----------- ------------ ----------- affinity mask 0 2147483647 0 0 allow updates 0 1 1 1 cost threshold for parallelism 0 32767 5 5 cursor threshold -1 2147483647 -1 -1 default language 0 9999 0 0 default sortorder id 0 255 52 52 extended memory size (MB) 0 2147483647 0 0 fill factor (%) 0 100 0 0 index create memory (KB) 704 1600000 0 0 language in cache 3 100 3 3 language neutral full-text 0 1 0 0 lightweight pooling 0 1 0 0 locks 5000 2147483647 0 0 max async IO 1 255 32 32 max degree of parallelism 0 32 0 0 max server memory (MB) 4 2147483647 2147483647 2147483647 max text repl size (B) 0 2147483647 65536 65536 max worker threads 10 1024 255 255 media retention 0 365 0 0 min memory per query (KB) 512 2147483647 1024 1024 min server memory (MB) 0 2147483647 0 0 nested triggers 0 1 1 1 network packet size (B) 512 65535 4096 4096 open objects 0 2147483647 0 0 priority boost 0 1 1 1 query governor cost limit 0 2147483647 0 0 query wait (s) -1 2147483647 -1 -1 recovery interval (min) 0 32767 0 0 remote access 0 1 1 1 remote login timeout (s) 0 2147483647 5 5 remote proc trans 0 1 0 0 remote query timeout (s) 0 2147483647 0 0 resource timeout (s) 5 2147483647 10 10 scan for startup procs 0 1 0 0 set working set size 0 1 0 0 show advanced options 0 1 1 1 spin counter 1 2147483647 10000 10000 time slice (ms) 50 1000 100 100 two digit year cutoff 1753 9999 2049 2049 Unicode comparison style 0 2147483647 196609 196609 Unicode locale id 0 2147483647 1033 1033 user connections 0 32767 0 0 user options 0 4095 0 0
I am trying to do a very small numbers table to compare A1c's against. However I am running into a issue when recursion hits the number 2.27 it starts to go out of my scope that I want with the next number being 2.27999999999999. Here is the code I'm using below. I need a Decimal(2,2) or Numeric (2,2) format with a range of 01.00 to 20.00. However every time I use Numeric or Decimal as the data type I get a error "Msg 240, Level 16, State 1, Line 5.Types don't match between the anchor and the recursive part in column "Number" of recursive query "NumberSequence"."
DECLARE @Start FLOAT , @End FLOAT ---DECIMAL(2,2) Numeric (2,2) SELECT @Start=01.00, @End=20.00 ;WITH NumberSequence( Number ) AS ( SELECT @start as Number UNION ALL SELECT Number + 00.01 FROM NumberSequence WHERE Number < @end
Please could you tell me how big sql tables are when people refer to them as small, medium and large? Preferably in terms of disk space or rows (each row in my table will contain a standard length job advert and 20 additional columns with an average of 8 characters)
I have a small tricky problem here...need help of all you experts.
Let me explain in detail. I have three tables
1. Emp Table: Columns-> EMPID and DeptID 2. Dept Table: Columns-> DeptName and DeptID 3. Team table : Columns -> Date, EmpID1, EmpID2, DeptNo.
There is a stored procedure which runs every day, and for "EVERY" deptID that exists in the dept table, selects two employee from emp table and puts them in the team table. Now assuming that there are several thousands of departments in the dept table, the amount of data entered in Team table is tremendous every day.
If I continue to run the stored proc for 1 month, the team table will have lots of rows in it and I have to retain all the records.
The real problem is when I want to retrive data for a employee(empid1 or empid2) from Team table and view the related details like date, deptno and empid1 or empid2 from emp table. HOw do we optimise the data retrieval and storage for the table Team. I cannot use partitions as I have SQL server 2005 standard edition.
Please help me to optimize the query and data retrieval time from Team table.
I have a very small SSAS database with around 35 Mb. I opened it on Excel 32 bits and started dragging fields to a pivot table and it started failing with memory errors. The behavior on the SSAS server was that memory started growing very fast until 8 GB (vm memory total) and then the error is reported in excel.
What might be the issue in such a small database? I would understand in a big database, but not on this one.
I have a table of vehicle usage records with fields including vehicle number, driver, date/time, starting odo, ending odo. I'm looking to compare the starting odo of a given record to the ending odo of the last time that vehicle was used. I also need to return other fields from that previous record, like the date/time and driver.My users basically run a report for any given day, and the vehicles used on that day need to be compared to their most recent usage (most recent relative to the record at hand). This is to ensure that the vehicle hasn't been used in the interim and not recorded (which may indicate theft).I've got a very convoluted process in place, but I'd like to see if it can be streamlined. The current process is done in Access and has a number of queries built on other queries. It is very slow. The data is in SQL Server. Any thoughts on the best ways to accomplish this?TIA
Hi there, Is there a way to retrieve the set of primary keys from an inserted record, especially when the primary keys are generated upon insertion (ex. identity, uniqueid)? Basically I need to get the ID of the record I just inserted so that I can use it as a reference to that record in other places. I'm using MS SQL and raw SQL commands (ie. no database interfacing objects). I'm hoping I can tag some sort of SELECT statement on the end of the INSERT command that will give me the results that I need. Thanks!
Hi all, I dont know if anyone here can help me...I am using ASP.NEt with VB.Net and SQL Sever...What I am trying to do is create a system whereby it allows users to upload images into sql server, and thereon later retireve this image.I used a book called The Ultimate VB.Net and ASP.Net Code Book, and it basically gave me the code below to upload and retrieve images. I seem to have managed to allow the uploading of images, with them being stored as <byte> in my database, thought I have no idea how to retrieve them...Does anyone have any ideas???Here is the code I followed.. :"Storing Uploaded Files in Your Database First, a few tips on storing files inside your SQL Server database.For convenience, you’ll really need to store at least three bits of information about your file to get it out in the same shape as you put it in. I’d suggest “data� (a field that will hold your actual file as a byte array, data type “image�), “type� (a field to hold details of the type of file it is, data type “varchar�), and “length� (a field to hold the length in bytes of your file, data type “int�). I’d also recommend “downloadName�, a field to hold the name that the file had when it was uploaded, data type “varchar�. This helps suggest a name should the file be downloaded again via the Web.The problem you have is translating the information from the File Field control into an acceptable format for your database. For a start, you need to get your file into a byte array to store it in an image field. You also need to extract the file type, length, and the download name. Once you have this, set your fields to these values using regular ADO.NET code.So, how do you get this information? It’s simple: just use the following ready-to-run code snippets, passing in your File Field control as an argument. Each function will return just the information you want to feed straight into your database, from a byte array for the image field to a string for the file type.Public Function GetByteArrayFromFileField( _ ByVal FileField As System.Web.UI.HtmlControls.HtmlInputFile) _ As Byte() ' Returns a byte array from the passed ' file field controls file Dim intFileLength As Integer, bytData() As Byte Dim objStream As System.IO.Stream If FileFieldSelected(FileField) Then intFileLength = FileField.PostedFile.ContentLength ReDim bytData(intFileLength) objStream = FileField.PostedFile.InputStream objStream.Read(bytData, 0, intFileLength) Return bytData End If End Function Public Function FileFieldType(ByVal FileField As _ System.Web.UI.HtmlControls.HtmlInputFile) As String ' Returns the type of the posted file If Not FileField.PostedFile Is Nothing Then _ Return FileField.PostedFile.ContentType End Function Public Function FileFieldLength(ByVal FileField As _ System.Web.UI.HtmlControls.HtmlInputFile) As Integer ' Returns the length of the posted file If Not FileField.PostedFile Is Nothing Then _ Return FileField.PostedFile.ContentLength End Function Public Function FileFieldFilename(ByVal FileField As _ System.Web.UI.HtmlControls.HtmlInputFile) As String ' Returns the core filename of the posted file If Not FileField.PostedFile Is Nothing Then _ Return Replace(FileField.PostedFile.FileName, _ StrReverse(Mid(StrReverse(FileField.PostedFile.FileName), _ InStr(1, StrReverse(FileField.PostedFile.FileName), ""))), "") End Function Sorted! One question remains, however. Once you’ve got a file inside a database, how do you serve it back up to a user? First, get the data back out of SQL Server using regular ADO.NET code. After that? Well, here’s a handy function that’ll do all the hard work for you. Simply pass the data from your table fields and hey presto:Public Sub DeliverFile(ByVal Page As System.Web.UI.Page, _ ByVal Data() As Byte, ByVal Type As String, _ ByVal Length As Integer, _ Optional ByVal DownloadFileName As String = "") ' Delivers a file, such as an image or PDF file, ' back through the Response object ' Sample usage from within an ASP.NET page: ' - DeliverFile(Me, bytFile(), strType, intLength, "MyImage.bmp") With Page.Response .Clear() .ContentType = Type If DownloadFileName <> "" Then Page.Response.AddHeader("content-disposition", _ "filename=" & DownloadFileName) End If .OutputStream.Write(Data, 0, Length) .End() End With End Sub Simply pass your byte array, file type, and length, and it’ll send it straight down to your surfer. If it’s an image, it’ll be displayed in the browser window. If it’s a regular file, you’ll be prompted for download.If it’s made available for download, this function also allows you to specify a suggested download file name, a technique that many ASP.NET developers spend weeks trying to figure out. Easy! Working with Uploaded Images Whether you’re building the simplest of photo album Web sites or a fully fledged content management system, the ability to work with uploaded images is a vital one, and with ASP.NET, it’s a real doddle.The following code snippet shows you how, by example. It takes a data stream from the File Field control and converts it into an image object, adding simple error handling should the uploaded file not actually be an image. The code then uses this image object to extract a few core details about the file, from its dimensions to file type:' Get data into image format Dim objStream As System.IO.Stream = _ MyFileField.PostedFile.InputStream Dim objImage As System.Drawing.Image Try ' Get the image stream objImage = System.Drawing.Image.FromStream(objStream) Catch ' This is not an image, exit the method (presuming code is in one!) Exit Sub End Try ' Filename Dim strOriginalFilename As String = MyFileField.PostedFile.FileName ' Type of image Dim strImageType If objImage.RawFormat.Equals(objImage.RawFormat.Gif) Then strImageType = "This is a GIF image" ElseIf objImage.RawFormat.Equals(objImage.RawFormat.Bmp) Then strImageType = "This is a Bitmap image" ElseIf objImage.RawFormat.Equals(objImage.RawFormat.Jpeg) Then strImageType = "This is a JPEG image" ElseIf objImage.RawFormat.Equals(objImage.RawFormat.Icon) Then strImageType = "This is an icon file" ElseIf objImage.RawFormat.Equals(objImage.RawFormat.Tiff) Then strImageType = "This is a TIFF file" Else strImageType = "Other" End If ' Dimensions Dim strDimensions As String strDimensions = "Width in pixels: " & objImage.Width & _ ", Height in pixels: " & objImage.Height ' Send raw output to browser Response.Clear() Response.Write(strOriginalFilename & "<p>" & strImageType & _ "<p>" & strDimensions) Response.End() "For some reason the retrieval code isnt working for me..Can anyone provide any help? even if it means using another method?
Hi,I am using MS Access as the front end and it's using ODBC link to connectto the backend tables residing at SQL SERVER.I have some queries that are doing some calculation and the time taken toretrieve the data has been quite slow.The no of data in that table is around 500K records. and the tables havealready got the PKs defined.How can i improve the performance of the query besides converting to SQLViews or SP(this is going to be diffcult as i am getting some filterparameters from the forms in MS Access) ?Or what further checks shld i be doing to find out the cause. I have donePerformance Monitor and the CPU processing% is around 20 - 55% while runningthose querieskindly advisetks & rdgs--Message posted via SQLMonster.comhttp://www.sqlmonster.com/Uwe/Forum...eneral/200511/1
What i want to know is if there is any better way of obtaining the 'id' and 'name' data values then just assuming that id is the first returned object and name is the second returned object in each reader record.
ie. Is there any way to retrieve a specific field from the reader in a Dictionary-type manner...?
Hi, I'm using ASP.NET 1.1, SQL Server 2000 Server: I followed the ASP.NET 1.1 Starter Kit's Commerce application and applied the same principles it had written the code to retrieve data to my web application I created. For example I've written this Function in a class to return a sqldatareader: Public Function GetAdvanceSearch(ByVal s As String, ByVal Ext As Integer, ByVal fdate As DateTime, ByVal tdate As DateTime) As SqlDataReader Dim oDrAdSearch As SqlDataReaderDim oCmdGetSearch As New SqlCommand("spAdvanceSearch", oComConn) With oCmdGetSearch .CommandType = CommandType.StoredProcedure .Parameters.Add(New SqlParameter("@DialNo", SqlDbType.VarChar)).Value = s .Parameters.Add(New SqlParameter("@FDate", SqlDbType.DateTime)).Value = fdate .Parameters.Add(New SqlParameter("@TDate", SqlDbType.DateTime)).Value = tdate .Parameters.Add(New SqlParameter("@Ext", SqlDbType.Int)).Value = ExtEnd With oComConn.Open()oDrAdSearch = oCmdGetSearch.ExecuteReader(CommandBehavior.CloseConnection) If oDrAdSearch.HasRows Then Return oDrAdSearchElse Return NothingEnd If End Function And When I'm calling this function I do write in this way (assuming that this function is in a class called "Calls"): Dim objCalls as New Calls DataGrid1.DataSource = objCalls.GetAdvanceSearch(<PARAMS.......>)DataGrid1.Databind My application is a Telephone Call Recording System and could expect vast amount of data. Averagely, a month may produce approximately 50,000 records or more. So while querying through my web application for a month, the application itself either gets stuck or the retrieval speed gets drastically slow. However I'm using Datareaders for every querying scenario. My Web application is hosted in a Windows 2000 Server and accessed via Local Network or IntraNet. What are the ways I could make this retrieval more speedier and efficient? I would like to hear from anyone who have come across this problem and anyone who could help me on this. Thanks in Advance. Looking forward for a reply from some one.
I need help(Tutorials or online links) regarding storing and retrieval of files in Sql server (BLOB) using ASP.net and C#. Secondly,Is it possible to search file in BLOB using SQL server Full text search service.
I'm stumped on how to solve this question so I figured i'd ask the community. As a warning i'm not sure best how to describe my situation so i'll try and give as much detail as I can.
First in table A, I have two columns that already have data in them that are numeric (Col1, Col2). Also, in table A I have two more columns that are going to derive their data based of a complicated data retrieveal routine (Col3, Col4). So my table structure looks something like this:
Code:
Table A Col1 Col2 Col3 Col4 20 20 NULL NULL
(Where Col3, and Col4 are going to be populated based off the routine)
The Data for Col3 and Col4 is in an excel spreadsheet that i'd like to convert into a table for MSSQL. However i'm not sure how to do this because in the spreadsheet there is a lookup routine (that i'm trying to copy to MSSQL code, i'll show that in a minute) that generates its values based off data in the X / Y columns, so something like this:
Code:
_|1__|2___|3__ 0|0 |0 |0 1|1 |15 |25
So when 2 is met, and 1 is met, they would equal '15'. No arithmetic involved, just simply matching up the X / Y and pulling the data.
My question is, how do I create tables out of this, so my lookup routines can get the values as a result of matching X / Y? (2, 1 = 15)???
i am getting a hard time in minimizing time for data retrieval, over SQL Server DataBase. My DataBase consist of 2 tables. One of the table has more than 10000 entries and other table with more than 10 million entries. I have used SQLNative Client for connecting to data base and my goal was to find a value from the 1st table and search it out in 2nd table. The result is more than hundered thousand rows. Now the problem is: the time it took for retrieving those rows is much slower approx. 12 minutes. Can this time be cut down. I have used SQLClient connection to make sure it is accessing SQL server on a direct access base.
Also, i am using
SqlClient::SqlDataReader
for reading, getting rows returned by the my query.
Is there any short way to make the server give out IDs of all clientprocesses connected to a specified DB? I guess this information mustbe stored somewhere in system databases...
I am working on an application to retrieve data to the windows ce 3.0 handheld from the Sql server database. I am able to retrieve the data from the server using RDA pull method and able to see the data on the local handheld database. To display the retrieved data in the dialog to the user, it requires to query the local databse and get the information and then display the information, which is taking some time.
My question is, to reduce the time and improve the performance , instead of pulling the data to the local table, is there any way to pull the data and have it in memory and display the details to the user?
To develop the above application, I used some of the code to pull the data from the server from the sample application C:Program FilesMicrosoft SQL Server CE 2.0SampleseVCeVCReplRdaHPC. I am developing the application for windows ce 3.0 device using eVC 3.0
