I am using the SQL Server 2005 JDBC driver. I need to write a piece of code that makes use of the streaming ResultSet. That is as soon as I get the first row, a worker thread should be able to begin processing on it without waiting for the second row to arrive. When the second row arrives, the second worker thread should start processing on this new row without waiting for the third row to arrive and so on. Usually, with a ResultSet, I need to wait for all the rows to arrive first before I can start navigating the rows in the ResultSet. But in my code, I need to start navigating the ResultSet even as more rows are pouring in from the DBServer. How can I do that? Any pointers in this direction will be helpful. Further, I want to know will setfetchsize be of any help here? If I set the setfetchsize value to 1, does that mean that as soon as I get the first row, I can start working on this row of the resultset(viz start navigating the ResultSet) without waiting for the second row to arrive in the ResultSet?
I am creating a simple SSRS table report through Report Builder. My dataset is looking for the stored procedure . When I execute the Stored procedure through SSMS I get resutset for certain parameters. I execute the dataset  (Store procedure) through query designer in dataset properties and I get results back. But when I try to run the report and see the preview, I do not get any results displayed. I been looking on the same issue form last 3-4 days and have not found any clue.
Following is the stored procedure I am using. Also I am passing multivalued parameter through report as well, and I am using spilt function to seperate the libraryid I am reading from parameter values. This works fine. I have similar kind of four other reports and with different stored procedure which exactly follow the same method , like multivalue parameters and other criteria are also very similar. All other reports works just fine.. This perticular report has issue for displying results, following is the stored procedure I am usingÂ
We€™re doing a website for a TV station, and they want to have a live TV broadcast on their website, our experience is basically in CMS and portal solutions, but we never had any experience in live streaming, so this is all new to us, so we€™re looking for a technical and financial proposal with your suggestions and recommendations, this is the details of the project with some of the inquiries we have: -The signal is SDI (Pal 25 fps) -The output (online streaming) 15 fps -The user will have two options to view the movie in: 1. Low: 56K 2. High: 300K -The video should come within a customized designed page, probably as a code we can place inside an html table€™s cell, not as a link for external link -We€™re expecting about 200 users for the video each month; each user€™s logon time expected 10 min. -We have a Red hat Linux on our server that will host the site, and we use php/MySql technology.
Inquires: 1. Why Windows media is better then Real in terms of performance, can you provide us with a comparison table to include it to our client? 2. If we choose Windows media, what are the requirements we need to have, form both; the TV station side, and in our Linux server? And how much would it cost? 4. Is there any hardware required from the TV station side such as DVR, or some sort of encoder, if so; what€™s you€™re recommendations (type/ brand), please note that this is a TV station and it needs a high quality professional hardware 5. How (and this is probably the most ambiguous task) 5.1. Do you broadcast the digital signal coming from the encoder (I guess) to the server? 5.2. And then place this signal on a PHP page? Do you give us a link to embed in our code? 6. If we choose to host the live streaming on a separated server and include the URL in our code, would that still required from our server to support Windows media, or it doesn€™t matter as long as the video streaming is hosted on a Windows streaming server 7. How much disk space does it need to host a live streaming (with the details mentioned above)?
We would really appreciate your comments and suggestions, as this is our 1st time to dealing with live streaming.
Interested in feedback from the SQL grand wizards (and would-be wizards) that haunt these forums.
Let's say you need to constantly stream data into an OLTP system. We are talking multiple level hierarchies totaling upwards of 300 MB a day spread out not unlike a typical human sleep cycle (lower data during off-peak, still 24/7 requirements). All data originates from virtual machines running proprietary algorithms. The VM/data capture infrastructure needs to be massively scalable, meaning that incoming data is going to become more and more frequent and involve many different flat record formats.
The data has tremendous value when viewed both historically as well as in real-time (95% of real-time access will be read-only). The database infrastructure is in it's infancy now and I'm trying to develop a growth plan that can meet the needs of the business as the data requirements grow. I have no doubt that the system will need to work with multiple terabytes of data within a year.
Current database environment is a single server composed of a Dell PowerEdge 2950 (Intel Quad Core 5355, 16 GB RAM, 2 x 73 GB 15K RPM SAS ) with an attached Dell PowerVault MD1000 (15 x 300 GB 10K RPM SAS in RAID 5+0 [2x7] w/hot spare) running Win 2k3 64-bit and SQL Server 2005 x64 Standard, 1-CPU.
I am interested in answering the following questions:
Based on the scaling requirements of the data capture and subsequent ETL, what transmission method would you find most favorable? For instance, we are weighing direct database writes via stored procedures for all VM systems versus establishing processes to collect, aggregate and stream CSVs into a specialized ETL environment running SSIS packages that load data and then call SQL Stored procedures to scrub and prepare for production import. The data will require scrub routines that need access to current production data, so distributing the core data structures to multiple ETL processing systems would be expensive and undesireable. Cost is very important to the overall solution design. In terms of database infrastructure, how would you maximize business value while keeping cost as low as possible? For instance, do you think there is more value in an ACTIVE/ACTIVE cluster (2 x CPU licenses) where one system acts as ETL and the other as OLTP or would you favor replication of production data from ETL to OLTP or (vice-versa). With the second scenario, am I mistaken in thinking we could get away with a Server/CAL licensing model for the ETL server?. Are there any third party tools that I should research that would greatly aid me here?
I appreciate all feedback, criticism, and thoughts.
Hello everyone. I have an interesting problem. We have a SQL database with a field that is an image type. In it are records where the image field are text files. What I would like to do is look at these files and stream them line by line and do some processes for each line read. So let's say a client machine uploaded a file called myfile.txt into this database. I would like the asp.net (vb) application tkae this file and read each line and do some processing. I looked at memorystream and streamreader but just can not figure this out. Can you please help? Thank you Kameron
I want to insert lots of data into two tables. For this I want to use table valued parameters and a stored procedure. So, what is the better way for best performance? Using two stored procedures or a single procedure with two parameters? How does SQL Server consume the data if I use a single procedure with two parameters. Is it really streaming the data? I mean, is SQL Server already starting to insert the first rows as soon as it gets it even if the client is still sending more and more rows and than the same with the next table? e.g. I have a procedure like this:
CREATE PROCEDURE [dbo].[usp_UpdateElements] ( @tvpElementsToInsert As [dbo].[tvpElements] Readonly, @tvpElementValuesToInsert As [dbo].[tvpElementValues] Readonly) AS                Begin                    Insert Into Elements Select * From @tvpElementsToInsert;                    Insert Into ElementValues Select * From @tvpElementValuesToInsert;                End
I need to store file(s) to SQL without streaming / reading at Server. I have created a Web API with AngularJS and SQL. e.g.Â
var fileType = httpRequest.Files[file].ContentType; Â Â Â Â var fileStrm = httpRequest.Files[file].InputStream; Â Â Â Â var fileSize = httpRequest.Files[file].ContentLength; Â Â Â Â byte[] fileRcrd = new byte[fileSize]; Â Â Â Â var file_Name = Path.GetFileName(filePath); Â Â Â Â fileStrm.Read(fileRcrd, 0, fileSize);
Is it possible to send file data to SQL (in bytes) without streaming / reading at server?I don't want to put a load on server for large files. just read  the data and send them to SQL where SQL will do the streaming and store data as varbinary.
CREATE TABLE [dbo].[IntegrationMessages] ( [MessageId] [int] IDENTITY(1,1) NOT NULL, [MessagePayload] [varbinary](max) NOT NULL, )
I call the following insertmessage stored proc from a c# class that reads a file.
ALTER PROCEDURE [dbo].[InsertMessage] @MessageId int OUTPUT AS BEGIN
INSERT INTO [dbo].[IntegrationMessages] ( MessagePayload ) VALUES ( 0x0 )
SELECT @MessageId = @@IDENTITY
END
The c# class then opens a filestream, reads bytes into a byte [] and calls UpdateMessage stored proc in a while loop to chunk the data into the MessagePayload column.
ALTER PROCEDURE [dbo].[UpdateMessage] @MessageId int ,@MessagePayload varbinary(max)
AS BEGIN
UPDATE [dbo].[IntegrationMessages] SET MessagePayload.WRITE(@MessagePayload, NULL, 0) WHERE MessageId = @MessageId
END
My problem is that I am always ending up with a 0x0 value prepended in my data. So far I have not found a way to avoid this issue. I have tried making the MessagePayload column NULLABLE but .WRITE does not work with columns that are NULLABLE.
My column contains the following: 0x0043555354317C... but it should really contain 0x43555354317C...
My goal is to be able to store an exact copy of the data I read from the file.
Here is my c# code:
public void TestMethod1() { int bufferSize = 64; byte[] inBuffer = new byte[bufferSize]; int bytesRead = 0;
byte[] outBuffer;
DBMessageLogger logger = new DBMessageLogger();
FileStream streamCopy = new FileStream(@"C:vsProjectsSandboxBTSMessageLoggerInSACustomer3Rows.txt", FileMode.Open);
try { while ((bytesRead = streamCopy.Read(inBuffer, 0, bufferSize)) != 0) { outBuffer = new byte[bytesRead];
//This calls the UpdateMessage stored proc logger.LogMessageContentToDb(outBuffer); } } catch (Exception ex) { // Do not fail the pipeline if we cannot archive // Just log the failure to the event log } }
How much performance hit should I expect to use FOR XML to generate XML string directly from SQL Server 2000? I like this approach because it is simple and easy. However, others told me that it has a big performance hit. They prefer to use a COM+ component to create XML string from a Resultset. Any suggestions? Thanks.
Hi,I have 3 tables as follow :Kanji :kanji_id....References :ref_id....KanjiRefskref_idkanjikref_idrefkref_value....So, there is a many-to-many relationship between Kanjis and References(one kanji may have more than one reference type, and a reference typemay be set to more than one kanji).For example, one kanji may have the value 'abc' for reference type #1,and the value 'def' for reference type #2, another kanji may also havethe reference type #1, but have instead the value '123' and so on...I have two questions :1) How to get all kanjis that do NOT have the reference #3 (ref_id = 3)within their list of references?2) How to get the value of all the kanjis that have the reference #3,but still get other kanjis that do not have the reference #3...forexample, if I had 3 kanjis, with the two first having values 'abc' and'def' for reference #3, and the last one having no reference #3, I'dlike to get that resultset :kanji_id kref_value1 'abc'2 'def'3 NULLI manage to get all the kanjis that have reference # 3 with thefollowing query :SELECT kanji_id, kref_valueFROM Kanjis INNER JOIN kanjiRefs ON kref_idkanji = kanji_idWHERE kref_idref = 3however, this obviously does not include kanjis having no reference#3...any help would be greatly appreciated, thanks! :)ibiza
I have a search query on my page. However, since the resultset may be very large, I want to retreive only those results that are currently shown on the gridview page.Often a user won't browse through more than a couple of pages, so it's b-*** to send all the records to the client.I know that with a sp you can define that you want to retrieve the first or second x records, but I want to do this with SQL...is that possible?
Hi I have 2 Sql statements within a stored procedure.But second one crashes my app - I'm getting object not found I need some help when writing several querys into the same sp, do I have to define some thing special? thanks
I am trying to write a select statement that will return the values of two columns from two different tables in the same statement. I believe the join word needs to be used somewhere, but I am having difficulty with it.
Table1 (table name) Column1 (column name) ------ a b
Table2 (table name) Column2 (column name) ------ c d
Hi,This is what I want to do. For a data acquisition query, if conditionA is met, set weight to 1, else if condition B is met, set weight to 2etc.Is this possible? I thought about using CASE function but to noavail.Using the classical Northwind db as target db, and its employees tableas target table, if [note] column contains 'BA', then I want to setweight to 1, else if it contains 'BTS', then I want to set weight to 2etc. Weight or the like is an auxillary artificially created column.Something likeDECLARE @col intselect firstname, lastname, @col =(CASE noteswhen '%BA%' then 1when '%BTS%' then 2else 0END as aiCOL)-- and I'd like to alias @col as aiCOL or whateverfrom employeeswhere notes LIKE'%BA%'ORnotes LIKE '%BTS%'failed.Thanks.
According to the BOL documenation of the ExecuteSQLTask you can populate a variable with a resultset. I specified a variable of type 'Object' and followed the instructions and everything is fine.
Now I have TWO issues.
1) I want to filter some unwanted rows from this variable and add into another recodset. For filtering i am using foreach loop and putting condition on variable. It works fine till here, but how to update another 'Object' type variable.
2) How to make the result set as source in a data flow task. I want the filtered result set to become source in a data flow
Hi, I have used Red Gates SQL bundle to make an identical db on sql2005 express from sql 2000. When i run my query against the sql2000 i get what I want, but when i run it against the sql2005, i get nothing. Anyone got any suggestions to why? web.config: <!-- <add name="TPLConnectionString" connectionString="Data Source=[SQL2000],1433;Network Library=DBMSSOCN;Initial Catalog=TPL;User ID=??????;Password=????????;" providerName="System.Data.SqlClient" /> --> <add name="TPLConnectionString" connectionString="Data Source=[SQL2005]\SQLEXPRESS,1433;Integrated Security=true;Network Library=DBMSSOCN;Initial Catalog=TPL;User ID=?????;Password=???????;" providerName="System.Data.SqlClient" /> default.aspx.cs dbConnection.Open(); String ISPQuery = "SELECT STATEMENT"; SqlCommand command = new SqlCommand(ISPQuery, dbConnection); SqlDataReader reader = command.ExecuteReader();
I have a sp: mysp_getstuff it contains the following:SELECT Adress,City FROM tblUserData WHERE UserName='john'as you can see it returns 2 columns.I also have another SP: mysp_GetNr. This sp returns an integer.I want to call mysp_getnr from mysp_getstuff and add the result to the 2 columns in a column named 'Number'So the resultset from mysp_getstuff should be:Adress, City, Number (in which the number column contains the result from mysp_GetNr)How can I do that?
I am having some problems exporting to Excel using SQL 7.
I have a DTS package which runs a query that returns one value and pumps the data to an Excel spreadsheet. Everything is working except the data is not populating the correct cell in the spreadsheet. How can I control which cell SQL pumps the data to?
I'm trying to number rows in a resultset by a grouping.
e.g. if my data looks like this:
personname persondata ----------- ----------------- person1 data person1 more data person1 other data person2 stuff person2 more stuff person2 even more stuff person2 lots of stuff person2 last bit of stuff person3 info person3 more info
I want it to return this, with x as a sub-numbered value (like an identity for each grouping):
x personname persondata -- ----------- ----------------- 1 person1 data 2 person1 more data 3 person1 other data 1 person2 stuff 2 person2 more stuff 3 person2 even more stuff 4 person2 lots of stuff 5 person2 last bit of stuff 1 person3 info 2 person3 more info
I am trying to do a join which involves more than 3 tables. One is a parent table and the other two table have 1:n relationship with that parent table.
But duplicate records are being returned in the resultset. How do I eliminate duplicate records?
This is my query.
SELECT table1.* from table1 left outer join table2 on table1.id=table2.id left outer join table3 on table1.id=table3.id
I tried doing DISTINCT here but with no success.
SQL with distinct clause.
SELECT distinct table1.id, table1.* from table1 left outer join table2 on table1.id=table2.id left outer join table3 on table1.id=table3.id
This is the error I get: The text data type cannot be selected as DISTINCT because it is not comparable
I have the below CTE that I just can't seem to get to give me the right results. Basically what im trying to do is use the first query to show the "sources" that are involved in each inquiry and the second query to show which of those have became "admissions" the thing is the counts of the sources when the CTESource query is ran alone is different than my query to join the two tables.
Code: With CTESource(Total, ID, Source, Program) AS ( SELECT count(Inquiry.ID) as Total, Referral.InquiryID_fk, Source, Inquirer.Program from Referral Inner Join Inquiry on Inquiry.ID = Referral.InquiryID_fk Inner Join Inquirer on Inquirer.ID = Inquiry.InquirerID_fk
[code]....
The total inquiries can be higher than the source totals since a source isnt required in the system as well as there does not have to be admissions regardless of inquiry count.
The output I need is ( '|' means cols separator) : Manufact1 | No of recs from HW for this Manufact | nulls ... null | null | HW1 from Manufact1 details ... null | null | HW2 from Manufact1 details ... Manufact2 | No of recs from HW for this Manufact | nulls ... null | null | HW3 from Manufact2 details ... ...
Hello all, I want to be able to be able to return a resultset from a Stored Procedure.
Something like :
CREATE PROCEDURE LSNOnAJob @MyJobNo AS INT,@MyLsn VarChar(10) OUTPUT
AS
SELECT @MyLsn = dbo.TSample.ISmpShortCode FROM dbo.TJob INNER JOIN dbo.TSample ON dbo.TJob.IJobN = dbo.TSample.IJobN WHERE (dbo.TJob.IJobN = @MyJobNo) GO
I pass the IJobN into the Sproc and it should give me a resultset back that contains 5 Ismpshortcode's (which is the resultset I want to pass back to Access XP). But the value that gets returned is the last result from the recordset.
I'm obviously doing something a bit stupid, so any help would be greatly appreicitated.
I have made some stored procedures to check if a user is involved with a certain record. basically every stored procedure contains the following logic.
example spCheckClientRelated: select @res = count(*) from client_role where client_id = @cid and employee_id = @eid
if (@res = 0) begin ... next select end if (@res = 0) begin ... next select end .... return @res end
so far so good. But the final check in CheckClientRelated tests if a user is related to one of the sales projects for that client.
I allready have the spCheckSalesProjectRelated that returns 1 or 0 similar to the example above
so I want to find an efficient method that selects all the sales_project_id 's from the sales_project table where client_id = @cid (i use offcourse select @sid = sales_project_id from sales_project where client_id = @cid at the moment)
And then I have to execute the spCheckSalesProjectRelated method for each @sid and @eid. This if offcourse where my problem is located. I don't know how to do a fast check for every selected @sid, until spCheckSalesProjectRelated returns 1
As you probably can determine from my question, sql is not really my domain, and I'm certainly not an expert, but I don't mind reading or looking up some stuff, so even a clue or a direction to look in would be most appreciated
Hi,I would like to write a stored procedure that returns a resultset.e.g. select student_id, student_subject, subject_marksfrom student_resultswhere student_class_no = ?The input parameter is student_class_no (see ? as per above SQL).The output is a resultset, with each result consisting of student_id,student_subject, subject_marks.Can anyone advise how this is done in sql server stored procedure?Thanks,June Moore.
Hello,Quick, and possibly strange, question.I am doing some work testing the running time of some dynamic SQLstatements on a remote machine. What I would like to do is executethe SQL on the remote machine, without returning the result set to thecalling machine (this would skew the results, as my connection to theremote machine is rather slow).I believe SET ROWCOUNT 0 would work in principle, but thedocumentation says:"Causes Microsoft® SQL Server™ to stop processing the query after thespecified number of rows are returned."I don't want processing to stop until all the records have beenidentified. I also don't know if "SET ROWCOUNT" works with openquery(althought a non-openquery solution would also be acceptable).The code will be running in a stored procedure, if that matters.Any input would be much appreciated.Phil
