Casting An In-memory Resultset To A DataSet For Use In XML File Generation
Sep 13, 2006
I am trying to use the results of a query to build an XML file (this will eventually be data from a number of RDBMS's and will undergo transformations and unions prior to being saved as a package variable).
I have saved the results of my query to a result set variable (ADOResultSet) using the Recordset Destination.
I then try to use a script task to read the variable and create an XML file. I get a runtime error Unable to cast COM object of type 'System.__ComObject' to class type 'System.Data.DataSet'.
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Â
Hi Everyone, I have been developing client server application using VC++.NET & SQL Server 2000. Now I installed my SQL Server in Windows Server 2003 & Windows XP client machine. The connection between the SQL Server & Client is working well. But I have a problem while executing my application which is developed for user entry. When I adding a new row the each machine generating the same primary key value in my dataset but the real primary key value storing in the database is correct. Why these problem? Can someone help me? Thanks in Advance Jose
I have an issue in generating the report in sql reporting services. I need to display a report in a table format. The datas of the table should be from two different sql tables. I have tried to write a stored procedure that returns two result sets from two different tables. As reporting services takes datas only from the first result set, i tried to write two different stored procedures each displays one result set. Then i have created two datasets with that two different stored procedure. Even then i cannot proceed as i was not able to use two different dataset in a single table because i was setting the datasetname to one dataset, when i try to retrieve the fields from another dataset i was able to retrieve only first and count values. Then i tried using sub reports. As sub reports for a dataregion(table) repeats for every row of the main report i was not able to fetch the correct datas. Atlast i have tried combining the query using join and wrote a single stored procedure. This stored procedure returns a single result set retrieves data from two tables satisfying the conditions. The issue i am facing with this is, the first table has only one row satisfying the condition and the second table has three rows satisfying the same condition, as i am using join query for the three rows returned by the second table the first table datas are getting duplicated for the rest of the rows in the second table. As I found using join query is the only resolution for the output which I need, and also I have to avoid the duplication of the records. Hence let me know for any solutions.
I have shown the sample datas that is duplicating which is indicated as bold. Phonenumber, Attemptdate and calloutcome are from first table and start time and endtime is from second table. As there are three different datas for the second table, first table datas are duplicated
Does anybody know how could I calculate the new date(adding @c to @bor subtracting @b to @c), having for example a declaration like this:DECLARE @a CHAR(12)DECLARE @b DATETIMEDECLARE @c INTSET @b='3.04.04';SET @c=6and to calculate the number of days between two dates with this kindof declaration(@a-@b or @b - @a):DECLARE @a CHAR(12)DECLARE @b DATETIMEDECLARE @c INTSET @a='12.2.04';SET @b='3.04.04';Thanks in advance.
Here is the issue. I have ReadOnly Access to a database. All of the Columns are set to NVARCHAR(1000) by default. I cannot change them. I want to load the DataSet into memory and change the DataType of the columns from NVARCHAR(1000) to INT(4). The data is in integer (i.e. 4,5,123) format (but stored as a string), but is coming across as strings. The charting software I am using won't implicitly convert these Strings to Int or Double. How can I change an entire column to Int?
I have a requirement of generating an "Sales Report" excel file and mailing that file to a particular email id. I have generated the excel file through bcp command and mail is working fine.
However formatting of excel file is required by end user for eg. Cells to be merged and borders to be given for cells for which i think bcp command will not work.
Hi, I'm running an application on a server which grabs data from a database table on another server using SqlConnection, SqlDataAdapter and DataSet. The application then updates every row in that DataSet's DataTable and the updates are saved back using DataAdapter. The code is pretty much straightforward code that you would find on MSDN documentation for using DataSets. The table contains a little over a million rows. When I run the application, I get an error saying the Server Application is not available. Upon looking into the application event log, I get this message. aspnet_wp.exe was recycled because memory consumption exceeded the 306 MB (60 percent of available RAM) How do I get round this? I thought DataSets were supposed to handle large datatables comfortably without having memory issues. -Thanks
I am working to archive some old data from a data warehouse using SQL server and SSIS. The data will be read and denormalized, then shipped out to a delimited text file.
The rowcount of the incoming data is significant, call it 10M+ rows per unit of work (one text file).
There are development advantages of using a stored proc for the data source - mainly ease of changing the denormalization logic as required. Wondering if there are performance advantages of an embeded query for the data source instead?
It was mentioned by one developer that when using a stored procedure, the output stream from the proc and subsequent SSIS steps cannot start until the full procedure processing is complete; i.e. the proc churns out its' result set in one big chunk.Â
He hinted that an embedded query does not have this same effect, but I am not sure that is accurate.
How to move one table or a ResultSet to a separate database .sdf file in compact edition?
For example, I have handheld installed the compact edition to do some order works. After the job done, I want move the new table Orders to our server or other place. Of course I don't want to move everything in the .sdf file. So, what is the best way to move table Orders out to a sparate .sdf file? (In my case, I can not use RDA or replication. )
When I insert the fields using parameters, the params look like this: paramValues(10) = New SqlParameter("@RegHrs", SqlDbType.Decimal) paramValues(11) = New SqlParameter("@OTHrs", SqlDbType.Decimal) paramValues(12) = New SqlParameter("@TotHrs", SqlDbType.Decimal)
The fields in the SQL table are defined as: RegHrs - decimal (9,2) OTHrs - decimal (9,2) TotHrs - decimal (9,2)
Before I insert the row (from w/in the VB code), I stop the code and verify that the values being placed into the params are: 5.85 0 5.85
After the SQL insertion, the values w/in the SQL Table contain: 6 0 6
with the help of the following code am able to read the text file now i need to insert values to the datatable in dataset can anybody help me on this regard
how shall i spilt them and add each value to two fields of the datatable
for example i have the fields in text file as Username1:Password1 Username2:Password2 etc i need to add the username seperatelt to a field called username in table and password in password field of the datatable an example with code would be better and helpful
I have some questions about creating SQL Server CE databases. Based on my experiments and what I've read on these forums, it looks like there are a couple ways to create a database schema. I can edit the database schema via the Server Explorer in Visual Studio, or use an external program like SQL Server Management Studio and somehow convert those files to .sdf files.
I find Visual Studio's built in tools to be cumbersome to use and limited in functionality, and using SSMS seems like a roundabout way of approaching the problem. I understand Microsoft will be releasing better tools with Orcas, but in the meantime, I'm wondering if there are alternative ways to generate database schemas.
For instance, I find Visual Studio's DataSet designer fairly easy to use. The DataSet designer generates schema definitions (.xsd files), and an instantiated DataSet can both read and write schema definitions via Read/WriteXMLSchema. Furthermore, DataAdapter's Fill and FillSchema methods can be used to push a schema from a database to a DataSet. So, can I somehow go the other direction and push a schema from a DataSet to a database? It seems like all the tools are there...
For example, if I create the DataSet schema, could I use a small app to create a new .sdf file, instantiate a DataSet, write the schema from the DataSet to the database, and then save the .sdf file? Or given the generated .xsd file, is there any way to create a SQL database from that?
Hi everyone,I have a question. I am using a DataSet file as my data layer in my app. I'm trying to create a event TableAdaptor where one of the queries has two paramaters (@start & @end). Those two variables are used to search for items that have start dates that fall in between the two. Every time I try to use this, the smart tab always brings up three paramaters (DataTable, start as date, end as date). How the hell does that DataTable get thrown in there. I executed the query in the builder section and it only asks for the two variables.Any idea on this one?Thanks!
I am trying to upload a file in ASP.net 2.0 to a SQL database using the FileUpload control. I am doing this by way of a typed dataset.Here is my code.Dim rta As New ResponseTableAdapterDim rdt As ResponseDataTable = rta.GetResponseByID(1)Dim rr As ResponseRow = rdt(0)Dim fs As New FileStream(fu.PostedFile.FileName, FileMode.Open, FileAccess.Read)Dim br As New BinaryReader(fs)Dim image() As Byte = br.ReadBytes(fs.Length)br.Close()fs.Close()rr.UploadFile = imagerr.NameFile = fs.Namerta.Update(rdt)The code runs without an error but afterwards I find that nothing has been stored in the UploadFile cell in the database but the file name has been stored in the NameFile cell in the database. Any ideas?Your help is much appreciated. James
SQL 2005 Std, 8gb ram, boot ini switches for /PAE and /3GB, AWE enabled and the min/max memory settings for a single instance respectively set to 1024 and 7168. Performance-wise the system is a bit slower than before (with SQL2000) and there seems to be alot of paging. The SQL service is also running under the "LocalSystem". In this case, do I need to specify the local "SYSTEM" to have rights to "Lock pages in memory"? I have a feeling there is a misconfiguration somewhere. Can someone point me in the right direction? Thanks.
I have a well-structured but also very large binary data-set that is generated by a C++ application every five minutes. The data needs to be accessed by SQL applications. Since data is generated every five minutes, performance is key, both for write and read. The data set is about 500MB.If data is written to the file system, the write performance doesn't involve SQL server. For reading it, I have a CLR to read the portions of the data that I need based on offset and length. That works and is very fast. The problem is that data is stored in the file system, so it is not self-contained within the database.
A second option that I haven't explored yet, is to write the data into a table as VARBINARY(MAX). I would read the data using SUBSTRING with appropriate offset and length. Performance of SQL write/read of binary data of this size, and whether there is a third option I haven't thought off. I'm using SQL Server 2014.
Anyone knows what the size should the Paging File be for a 8 P 16 Core Opteron server with 128GB memory installed? The server will be running SQL 05 Ent and IIS, on top of Win2k3 R2 x64 Ent.
I have seen some different behaviors when I am writing SQL Server compact edition Database file into SD Memory card or any user removable storage media.
The following Steps for reproducing these behaviors.
1. Set your database location is in the SD Memory card. 2. Create a database and establish session. 3. Writing first record into the database is done 4. Remove your SD card manually from the system 5. Try to write a Second record into the database 6. You will get the error from this "SqlCeWriteRecordProps" API call - This is fine. 7. Put your SD card back into the system. 8. Writing Second record again into the database is done - This is fine.
So far the behaviors are good after that
9. Remove your SD card manually from the system again 10. Try to write a Third record into the database 11. You will suppose to get the error from this "SqlCeWriteRecordProps" API call but you won€™t get that error €“ I don€™t know why? 12. Then Try to write a Forth record into the database 13. You won't get any error from the API call "SqlCeWriteRecordProps" and return status is success. 14. Now put your SD card back into the system. 15. Then write Fifth record but this time its writing record Third, Fourth and Fifth into the database.
Note: I am clearing (means Free) my record structure everything after calling this function "SqlCeWriteRecordProps". So I don€™t know why its wring Third, fourth and fifth record into the database.
We are hosting a 140 GB database on SQL Server Version 7 and Windows2000 Advanced Server on an 8-cpu box connected to a 15K rpm RAID 5SAN, with 4 GB of RAM (only 2 GB of which seem to be visible to theOS) and a 4 GB swap file. (The PeopleSoft CIS application will notpermit us to upgrade to SQL 2K.) We recently upgraded the server from4 to 8 cpus and the SAN disks from 10K to 15K drives. But we stillhave heavy SAN disk usage, sometimes at 100%, and read queues oftenaveraging 4 and peaking at 12.The CPUs are loaded at only 20-50%. (The politics are such that it iseasier to throw hardware at the problems.)We are looking into archiving, converting from RAID 5 to RAID 10, andat splitting the mdf file into several file groups in an attempt toget more disk heads into play. (We are also looking at rewriting theapplication to reduce the read volume and frequency.) Does anyone haveany other ideas?Incidentally, does swapfile get used when the physical memory equalsthe OS maximum? If the OS can only see 2 GB and we have 2 GB (actually4 GB) of memory, is the 4GB local swap file on the C drive unused?Thanks in advance for any assistance.
I have an SSIS script task using c#. i need to refere an .xsd dataset in the c# code. i tried to set property below.Build action to Content or Compile Copy to output directory Copy always But still i m unable to use the dataset in my code.
I have a report with multiple datasets, the first of which pulls in data based on user entered parameters (sales date range and property use codes). Dataset1 pulls property id's and other sales data from a table (2014_COST) based on the user's parameters. I have set up another table (AUDITS) that I would like to use in dataset6. This table has 3 columns (Property ID's, Sales Price and Sales Date). I would like for dataset6 to pull the Property ID's that are NOT contained in the results from dataset1. In other words, I'd like the results of dataset6 to show me the property id's that are contained in the AUDITS table but which are not being pulled into dataset1. Both tables are in the same database.
I have a small number of rows in a dataset, Table 1. There is a CLOB on a large dataset, Table 2. They join on a PK. I would like to retrieve this CLOB and add it to the data flow for Table1. In short I want to emulate the following:
Table 1: Small table without CLOB, 10 rows. Table 2: Large table with CLOB, 10,000,000 rows
select CLOB from table2 where pk = (select pk from table1)
I want this to return the CLOBs for the small number of rows in Table 1. The PK is indexed obviously so it should be a fast look up.
Table 1 and Table 2 live on different Oracle databases. How do I perform this operation efficiently in SSIS? It seems the Lookup and Merge Join wont do this.
I have a report with multiple datasets, the first of which pulls in data based on user entered parameters (sales date range and property use codes). Dataset1 pulls property id's and other sales data from a table (2014_COST) based on the user's parameters.
I have set up another table (AUDITS) that I would like to use in dataset6. This table has 3 columns (Property ID's, Sales Price and Sales Date). I would like for dataset6 to pull the Property ID's that are NOT contained in the results from dataset1. In other words, I'd like the results of dataset6 to show me the property id's that are contained in the AUDITS table but which are not being pulled into dataset1. Both tables are in the same database.
Ok I am faced with working with XML on a regular basis, which is fine.
DECLARE @ViewSN INT IF NOT EXISTS (select null from tblviews where viewcode = 'loadAtTerm') --<workflowEventType>loadAtTerminal</workflowEventType> insert into tblviews (ViewName,Description,OutBoundForm,StoredProcSN,TriggersReply,ViewCode,DispXactLayer,DispXactViewType,DispXfcTag,Comments) select 'QC:WF-LoadAtTerminal','This View Corresponds to the XML for loadAtTerminal in Omnitracs Workflow','0',NULL,'0', 'loadAtTerm','MCOM','MCOM',NULL,NULL
[code]...
What would be really useful is to be able to present any xml file and automatically parse the NODE names into a memory variable table and then the fields of each node in another.
Can someone please help me with this? I'm losing all my hair trying to figure it out. I've tried all that I can think of. I am getting the Error converting data type varchar to numeric.
Thanks Sam "O"
cmdInsert = New SqlCommand( "INSERT INTO tmp_blank (sessionid, ssn,job,sun,mon,tue,wed,thu,fri,sat,totcol) SELECT '" & Session.SessionId & "', ssn,job,sun,mon,tue,wed,thu,fri,sat, (cast(sun as numeric(4,2)) + cast(mon as numeric(4,2)) + cast(tue as numeric(4,2)) + cast(wed as numeric(4,2)) + cast(thu as numeric(4,2)) + cast(fri as numeric(4,2)) + cast(sat as numeric(4,2))) as totcol from " & strTableName, conTimeCard)
I'm new to SQL Server and I have to finish a VB.Net program from an employee that has left the company.
I have a table with column 'vanafdatum' witch has datatype char but they stored only dates in this column ( like this: 13/09/2006).
When I try to run the next querie I receive an error message "The conversion of a char data type to a datetime data type resulted in an out of range datetime value".
This is the querie I try to run
SELECT MAX(CAST(vanafdatum AS datetime)) AS vanaf_datum FROM tarieven WHERE (vanafdatum < (SELECT getdate()))
Is there any way I can cast the char datatype to a datetime datatype without getting this error. I allready tried to change the data type from char to datetime, but doesn't work either. A possible solution is to erase the column an manually fill in all the fields again in the data type datetime, but that seems a little stupid to do. So I wonder if there is a solution that can change the data type for all rows in the database without losing any data.
I've got an ftp task that will be downloading a couple of files each night. today they're called
blah20060830blah
the date value in the middle changes each day. I'm trying to adjust this value with an expression. The expression doesn't want to cast my getdate function into a string that this property will accept. I know i'm missing something stupid.
I would like to know if casting is supported in SQL Server Compact Edition. I get an error when i attempt to cast a datetime or bigint value to nvarchar or ntext. CAST(datetime AS nvarchar(15))
Does anybody knows if its supported or how could i cast values?
I found out the data I need for my SQL Report is already defined in a dynamic dataset on another web service. Is there a way to use web services to call another web service to get the dataset I need to generate a report? Examples would help if you have any, thanks for looking
Hi,I've come across a problem that requires i cast a string to a date, had a go but can't get it to work properly.SELECT EventID, EventName, EventShortDesc, EventLink, EventStartDateFROM dbo.tbl_EventWHERE (CAST EventStartDate AS DATE) > GetDate()ORDER BY EventStartDate DESCBasically i only want events which haven't expired (were before the current date) to be returned. EventStartDate is held as an varchar(10) therefore needs to be cast before i can compare. Is this the best way or is there a better one? If it is how do i get it to work?Any help would be great thanks,drazic19
