Default Retrieval From Table Loaded By DTS
Jul 20, 2005
[MS SQL Server 7]
We load a table from a text file using Data Transformation Services. The
source 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 the
retrieval order from a SELECT statement isn't guaranteed, but this is the
first time that I've actually had a default retrieval not follow the
primary key order in the table. I'm thinking that what we're seeing is the
physical storage order of the rows in the table, which have been somewhat
scrambled by the way that DTS loads a text file (probably some I/O
optimization).
Is there any way that we can get DTS to load the table in the order that
the 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 retrieval
order isn't following the primary key.
Regards,
Lyle H. Gray
View 1 Replies
ADVERTISEMENT
Jan 18, 2005
Dear Participant,
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.
I don’t understand what is the problem.
Thanks
R.Mall
View 6 Replies
View Related
Jul 18, 2007
Hi,
I am getting message while modifying one table in SSMS like "Table '?' could not be loaded. Column '?' does not exist" . Howevery particular column exist in table and even i am able to modify it with T-SQL.
Any Idea?
View 2 Replies
View Related
Feb 22, 2007
Hi,
I have a summary table like this
Field1
Field2
Field3
Field4
Field5
AAA11
value1
value2
value3
value4
value5
AAB23
value6
value7
value8
value9
value10
BCD14
value11
value12
value13
value14
value15
GFD12
value16
value17
value18
value19
value20
SDL25
value21
value22
value23
value24
value25
AUD56
value26
value27
value28
value29
value30
BER11
value31
value32
value33
value34
value35
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????
Thanks.
Michelle
View 1 Replies
View Related
Jun 1, 2006
I live in Brazil, and use SQL Server 2000 SP4 with Visual Basic 5.0 SP3, with connectivity to ODBC through RDO/ADO.
I have an example table with the following structure:
Table Name: Autioneer's (translated from Portuguese)
'Field 1/3
Name: Code
DataType: int
Description: Auctioneer's Code
'Field 2/3
Name: Name
DataType: nvarchar(50)
Description: Auctioneer's Name
'Field 3/3
Name: RegNum
DataType: nvarchar(20)
Description: Auctioneer's Registration Number
I need a way to programatically extract the Fields Description Property from the table
example sintaxe:
SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME ='AUCTIONEERS'
Thanks for any help that can be offered here
Neil Ramkeerat.
Replies can be sent to neilramkeerat@hotmail.com
View 3 Replies
View Related
Jul 17, 2015
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.
View 3 Replies
View Related
Oct 16, 2015
In my SSIS package I have flat files as a source, I have to load numbers of flat files into SQL target table. I am using For each loop container for that. I am doing it correctly. My aim is to validate the source data from all angle before writing it into target sql table. I am using below points to validate the source data , if I found any bad data I am redirecting those data to error output.
To Checking
1. To check whether data type of column.
2. To check whether buisinesskey column null.
Is there any thing which I am missing to validate source data.
Screen shot for reference
View 10 Replies
View Related
Apr 10, 2008
Hello Everybody,
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.
Thanks,
Ganesh
View 4 Replies
View Related
Nov 21, 2006
For reasons that are not relevant (though I explain them below *), Iwant, for all my users whatever privelige level, an SP which createsand inserts into a temporary table and then another SP which reads anddrops the same temporary table.My users are not able to create dbo tables (eg dbo.tblTest), but arepermitted to create tables under their own user (eg MyUser.tblTest). Ihave found that I can achieve my aim by using code like this . . .SET @SQL = 'CREATE TABLE ' + @MyUserName + '.' + 'tblTest(tstIDDATETIME)'EXEC (@SQL)SET @SQL = 'INSERT INTO ' + @MyUserName + '.' + 'tblTest(tstID) VALUES(GETDATE())'EXEC (@SQL)This becomes exceptionally cumbersome for the complex INSERT & SELECTcode. I'm looking for a simpler way.Simplified down, I am looking for something like this . . .CREATE PROCEDURE dbo.TestInsert ASCREATE TABLE tblTest(tstID DATETIME)INSERT INTO tblTest(tstID) VALUES(GETDATE())GOCREATE PROCEDURE dbo.TestSelect ASSELECT * FROM tblTestDROP TABLE tblTestIn the above example, if the SPs are owned by dbo (as above), CREATETABLE & DROP TABLE use MyUser.tblTest while INSERT & SELECT usedbo.tblTest.If the SPs are owned by the user (eg MyUser.TestInsert), it workscorrectly (MyUser.tblTest is used throughout) but I would have to havea pair of SPs for each user.* I have MS Access ADP front end linked to a SQL Server database. Forreports with complex datasets, it times out. Therefore it suit mypurposes to create a temporary table first and then to open the reportbased on that temporary table.
View 6 Replies
View Related
Jan 7, 2008
i m having a huge problem! how to retrieve data from notepad files using asp.net and store the info in fields in MS-Access/Sql db? Plz help !!
View 1 Replies
View Related
Jul 10, 2006
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
View 4 Replies
View Related
Jul 4, 2006
Hi,
I'm running SQL Server Agent services and SQL Server Integration services under same DomainUser account and the same DomainUser account is also having sysadmin rights in sql server.
And below is the SQLAgent.OUT file details which I'm getting while running the SSIS from SQL Agent job.
[100] Microsoft SQLServerAgent version 9.00.1399.06 (x86 unicode retail build) : Process ID 4012
[101] SQL Server MachineNameXYZ version 9.00.1399 (0 connection limit)
[102] SQL Server ODBC driver version 9.00.1399
[103] NetLib being used by driver is DBNETLIB.DLL; Local host server is
[310] 4 processor(s) and 3072 MB RAM detected
[339] Local computer is MachineNameXYZ running Windows NT 5.2 (3790) Service Pack 1
[432] There are 11 subsystems in the subsystems cache
[125] Subsystem 'ActiveScripting' could not be loaded (reason: The specified module could not be found)
[125] Subsystem 'CmdExec' could not be loaded (reason: The specified module could not be found)
[125] Subsystem 'Snapshot' could not be loaded (reason: The specified module could not be found)
[125] Subsystem 'LogReader' could not be loaded (reason: The specified module could not be found)
[125] Subsystem 'Distribution' could not be loaded (reason: The specified module could not be found)
[125] Subsystem 'Merge' could not be loaded (reason: The specified module could not be found)
[125] Subsystem 'QueueReader' could not be loaded (reason: The specified module could not be found)
[125] Subsystem 'ANALYSISQUERY' could not be loaded (reason: The specified module could not be found)
[125] Subsystem 'ANALYSISCOMMAND' could not be loaded (reason: The specified module could not be found)
[125] Subsystem 'SSIS' could not be loaded (reason: The specified module could not be found)
[364] The Messenger service has not been started - NetSend notifications will not be sent
[129] SQLSERVERAGENT starting under Windows NT service control
[260] Unable to start mail session (reason: No mail profile defined)
[396] An idle CPU condition has not been defined - OnIdle job schedules will have no effect
[LOG] Step 1 of job 'TestJob' (0x4A197E88EAD5134581A35132A546414C) cannot be run because the SSIS subsystem failed to load. The job has been suspended
[LOG] Unable to read local eventlog (reason: The parameter is incorrect)
[LOG] Unable to read local eventlog (reason: The parameter is incorrect)
[LOG] Step 1 of job 'TestJob' (0xE412C2AB10C7A34B87F5DFC8CFD978CA) cannot be run because the SSIS subsystem failed to load. The job has been suspended
[LOG] Unable to read local eventlog (reason: The parameter is incorrect)
Any help will be appreciated.
Regards,
M.S
View 13 Replies
View Related
Apr 12, 2007
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!
View 3 Replies
View Related
Jul 12, 2004
Hi,
I wanted to retrieve all the databases present in an SQL Server and put them in an Dropdown list.
Then retrieve all tables of the selected database
and finally retrieve all data from the selected table (This i know)
How do i accomplish the above two.
Regards
Vijay R
View 3 Replies
View Related
Apr 9, 2006
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?
View 1 Replies
View Related
Jun 23, 2008
I want to remove duplication entries on the basis of compaparing
ApID,StID,CreationDate.If three match only i shoul get result set for deletion
ID ApID StID RoleID CreationDate
40100522008-01-31 20:27:03.850
41101522008-01-31 20:27:03.850
42101522008-01-31 20:27:03.850
90110118122008-01-31 20:27:03.850
90210118122008-01-31 20:27:03.850
90310127422008-01-31 20:27:03.850
30251013422008-02-22 11:43:39.153
90410147422008-01-31 20:27:03.850
30021016422008-02-22 11:28:34.513
317010179422008-02-22 12:35:17.450
9061019422008-01-31 20:27:03.850
43102522008-01-31 20:27:03.850
90710208122008-01-31 20:27:03.850
90810217422008-01-31 20:27:03.850
319410227422008-02-22 12:43:46.030
90910238122008-01-31 20:27:03.850
475010248122008-05-23 14:27:45.317
91210278122008-01-31 20:27:03.850
475110288122008-05-23 14:27:45.317
2638103522008-02-21 16:26:19.877
25711031501282008-02-19 14:23:57.610
32051031422008-02-22 12:47:32.653
91510327422008-01-31 20:27:03.850
310310398122008-02-22 12:08:32.043
2639104522008-02-21 16:26:19.877
91810407422008-01-31 20:27:03.850
9191041422008-01-31 20:27:03.850
32061042422008-02-22 12:47:50.903
92010439422008-01-31 20:27:03.850
9221045422008-01-31 20:27:03.850
32081046422008-02-22 12:48:11.857
9241048422008-01-31 20:27:03.850
32091049422008-02-22 12:48:30.687
2640105522008-02-21 16:26:19.877
92610507422008-01-31 20:27:03.850
31561051522008-02-22 12:27:06.700
92710529422008-01-31 20:27:03.850
92810537422008-01-31 20:27:03.850
309810548122008-02-22 12:07:00.000
30441055522008-02-22 11:50:26.373
9291056422008-01-31 20:27:03.850
93010588122008-01-31 20:27:03.850
93110599422008-01-31 20:27:03.850
44106522008-01-31 20:27:03.850
31611060422008-02-22 12:29:31.687
93310617422008-01-31 20:27:03.850
93410629422008-01-31 20:27:03.850
317410637422008-02-22 12:36:24.170
9351064422008-01-31 20:27:03.850
9361064422008-01-31 20:27:03.850
93810679422008-01-31 20:27:03.850
93910689422008-01-31 20:27:03.850
31101069422008-02-22 12:10:33.467
94010707422008-01-31 20:27:03.850
94110707422008-01-31 20:27:03.850
94210707422008-01-31 20:27:03.850
94310707422008-01-31 20:27:03.850
321910717422008-02-22 12:51:09.687
94410729422008-01-31 20:27:03.850
9451073422008-01-31 20:27:03.850
322110748122008-02-22 12:51:53.560
32221075422008-02-22 12:52:18.373
94610768122008-01-31 20:27:03.850
9471077422008-01-31 20:27:03.850
32231078422008-02-22 12:52:41.717
94810799422008-01-31 20:27:03.850
322010809422008-02-22 12:51:30.467
View 2 Replies
View Related
Nov 23, 2005
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
View 9 Replies
View Related
Nov 16, 2007
Code Block
string commandString = "SELECT Id,Name FROM [DatabaseTable];";
using (SqlConnection conn = new SqlConnection(connectionString))
{
SqlCommand cmd = new SqlCommand(commandString, conn);
conn.Open();
IDataReader rdr = cmd.ExecuteReader();
IList ids = new List();
IList names = new List();
while (rdr.Read())
{
ids.Add((int)rdr[0]);
names.Add((String)rdr[1]);
}
rdr.Close()
}
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...?
Code Block
while(rdr.Read())
{
ids.Add( (int)rdr["Id"] );
names.Add( (String)rdr["Name"] );
}
View 6 Replies
View Related
Mar 5, 2007
when I start work with my application to load the first report take long time.
after its load good.
my first question is:
Is it possible to load the report server when I start work with my application?
second:
I have a matrix report, is it possible to show all the column, but when I print, to print just the last 4 columns?
thanks alot
and good day
View 1 Replies
View Related
Jul 27, 2006
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.
View 3 Replies
View Related
Oct 29, 2004
Hi,
Thanks in advance.
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.
View 1 Replies
View Related
May 14, 2000
How can i retrieve duplicate rows and show in sql server 7.0.
It is very urgent.
regards,
Srinivas.
View 4 Replies
View Related
Mar 29, 2001
Is there a stored proceduire that grabs all the data from a specified table and places it in a file.
View 1 Replies
View Related
May 4, 2006
Hello all,
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)???
The excel routine is this:
=IF(VLOOKUP(F66,'Appendix A'!A5:K56,MATCH(F68,'Appendix A'!A4:K4,1),TRUE)>F46/12*0.125,F46/12*0.125,(VLOOKUP(F66,'Appendix A'!A5:K56,MATCH(F68,'Appendix A'!A4:K4,1),TRUE)))
Thanks!
View 3 Replies
View Related
Apr 11, 2007
Hello,
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.
Please help me out.
Thanx.
R. T.
View 4 Replies
View Related
Jul 20, 2005
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...
View 1 Replies
View Related
Jun 3, 2008
Hi All,
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
thanks
pyd.
View 4 Replies
View Related
Sep 11, 2000
Hi,
I'm using SQL Server 7.0. I have a table which has had some records double loaded into it and some triple loaded. I need to delete all instances of a record except for 1. The records are identical with no unique identifier. Here is what I'm talking about.
Currently in the table is:
ID DATE STATUS
1 07/07/2000 D
1 07/07/2000 D
1 07/07/2000 D
2 07/07/2000 A
2 07/07/2000 A
What I need in the table is:
ID DATE STATUS
1 07/07/2000 D
2 07/07/2000 A
Does anyone know how I might be able to do this?
Thanks in advance,
Darrin
View 2 Replies
View Related
Aug 18, 2006
have a dts package that does txt -> sql server.
i have 200 txt files with the same exact format.
just want to know if i can write a SP passing a parameter that loads this txt files. because i dont wanna create 200 packages or 200 sources to load 200 txt files.
say:
exec SP_loadTXT txt1
or should i use bulk insert?
any approaches are fine. any suggestions are fine too.
View 14 Replies
View Related
Jul 23, 2005
I'm doing some performance reviews and wish to know what tables SQL haspinned in memory and which ones have are loaded through usage ...Is there a way ?Thanks,Craig
View 3 Replies
View Related
Apr 5, 2007
Hi all,
I have created a dts on my Sql server. which gets the data from my log file and insert into my sql 2005 table. In dts i am using 'Flat File Source' and 'SQL Server Destination'. When i execute this through 'SQL Server Business Intelligence Development Studio' it works good.
But when i execute this through my c# code from my web server, it throws me an error 'The specified package could not be loaded from the SQL Server database.'
This exception comes under app.LoadFromSqlServer.
My C# Code :
Application app = new Application();
Package package2 = app.LoadFromSqlServer("\Maintenance Plans\FTPPackage", "Server Name", "UserName", "Password", null);
DTSExecResult result2 = package2.Execute();
string str = result2.ToString();
I hv tried package name like '\Maintenance Plans\FTPPackage' and 'FTPPackage'.
Plz help me to solve this...
Thanks
Harsh
View 8 Replies
View Related
Jun 29, 2007
Hi,
I have created a package and when i was trying to configure a flat file destination, i am getting the following error:
===================================
The component could not be added to the Data Flow task.
Could not initialize the component. There is a potential problem in the ProvideComponentProperties method. (Microsoft Visual Studio)
===================================
Error at Extract Test Flat File [DTS.Pipeline]: The module containing "component "" (245)" cannot be located, even though it is registered.
===================================
Exception from HRESULT: 0xC0048021 (Microsoft.SqlServer.DTSPipelineWrap)
------------------------------
Program Location:
at Microsoft.SqlServer.Dts.Pipeline.Wrapper.CManagedComponentWrapperClass.ProvideComponentProperties()
at Microsoft.DataTransformationServices.Design.PipelineTaskDesigner.AddNewComponent(String clsid, Boolean throwOnError)
Please advise.
Thanks & Regards,
Deepak
View 1 Replies
View Related
Aug 4, 2006
What SSIS components must be loaded onto the server?
To run an SSIS ETL program, exactly what executable, components, programs, etc. must reside on the server that will be running the program. Our developers write an test their ETL programs locally, on their desktops or laptops, but must upload the scripts to the production server for execution. Obviously, they are not going load Visual Studio and SQL 2005, etc. up to the server just to run the scripts (tho SQL 2005 may be on the same box as one of the DB engines used for ETL).
Any info would be appreciated.
View 7 Replies
View Related