What Should I Do When I Want To Save A Select Resultset,but Not In A New Table,in Memory Is The Best?
Sep 25, 2006What should i do when i want to save a select resultset,but not in a new
table,in memory is the best?
thanks!
ADVERTISEMENT
Casting An In-memory Resultset To A DataSet For Use In XML File Generation
Sep 13, 2006I 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'.
Public Sub Main()
'MsgBox(Dts.Variables("ADORecordSet").Value.ToString)
Dim mySet As DataSet
mySet = (CType(Dts.Variables("ADORecordSet").Value, DataSet))
mySet.WriteXml("C: est.xml", XmlWriteMode.WriteSchema)
Dts.TaskResult = Dts.Results.Success
End Sub
I am very unfamiliar with Visual Basic and am unsure of how to cast the COM object to a DataSet. Do I need to marshal the COM object?
Thanks
Can't Save Or Compile Package (insufficient Memory)
May 7, 2007When I compile or save a SSiS package in the SQL Server Business Intelligence Development Studio I get the error
Failure saving package. (Microsoft Visual Studio)
Insufficient memory to continue the execution of the program.
Sometimes, if I wait longer time, the package can suddenly be saved.
I have 2 GB of RAM und over 15 GB of free space on my disk. Thus, there should be anough space to save the package.
What should I do to resolve this problem? It seems to be a bug.
To splitt the package would not be a solution.
Insufficient Memory To Continue Error When Attempting To Save SSIS Package
May 12, 2006When attempting to save an SSIS package in Visual Studion I receive the following error message detailed below. If I attempt to "Save As" to another location, I then receive an insufficient storage error. The development machine has over 1.5 GB of available physical memory and several GB of disk space availabe to save my 16 MB package. I have checked the event log and have found no related messages in the Application or Server logs.
Any suggestions on how to determine the cause or resolution of this error message would be greatly appreciated.
Failure saving package. (Microsoft Visual Studio)
Insufficient memory to continue the execution of the program. (Microsoft.SqlServer.ManagedDTS)
Advanced Error Message Details
Failure saving package. (Microsoft Visual Studio)
------------------------------
Program Location:
at Microsoft.DataTransformationServices.Design.Serialization.DtrDesignerSerializer.SerializePackage(IDesignerSerializationManager manager, Package package, TextWriter textWriter)
at Microsoft.DataTransformationServices.Design.Serialization.DtrDesignerSerializer.SerializeComponent(IDesignerSerializationManager manager, IComponent component, Object serializationStream)
at Microsoft.DataWarehouse.Serialization.DesignerComponentSerializer.Serialize(IDesignerSerializationManager manager, Object value)
at Microsoft.DataWarehouse.VsIntegration.Designer.Serialization.DataWarehouseDesignerLoader.Serialize()
at Microsoft.DataWarehouse.VsIntegration.Designer.Serialization.BaseDesignerLoader.Flush(Boolean forceful)
at Microsoft.DataWarehouse.VsIntegration.Designer.Serialization.BaseDesignerLoader.Flush()
at Microsoft.DataWarehouse.VsIntegration.Designer.Serialization.DataWarehouseContainerManager.OnBeforeSave(UInt32 docCookie)
===================================
Insufficient memory to continue the execution of the program. (Microsoft.SqlServer.ManagedDTS)
------------------------------
Program Location:
at Microsoft.SqlServer.Dts.Runtime.Package.SaveToXML(String& packageXml, IDTSEvents events)
at Microsoft.DataTransformationServices.Design.Serialization.DtrDesignerSerializer.SerializePackage(IDesignerSerializationManager manager, Package package, TextWriter textWriter)
Feed Stored Procedure With SELECT Resultset
Jul 20, 2005I have two SQL Server stored procedures, PROC1 and PROC2. PROC1 hasabout 50 input parameters. PROC2 is the main procedure that does somedata modifications and afterwards calls PROC1 using an EXECUTEstatement.The input parameter values for PROC1 are stored in a table in mydatabase. What I like to do is passing those values to PROC1 using aSELECT statement. Currently, all 50 parameters are read and stored ina variable, and afterwards they are passed to PROC1 using:EXEC spPROC1 @var1, @var2, @var3, ... , @var50Since it is a lot of code declaring and assigning 50 variables, I waswondering if there is a possibility to run a statement like:EXEC spPROC1 (SELECT * FROM myTable WHERE id = 2)Any help on this is greatly appreciated!
View 1 Replies View RelatedReporting Services :: SSRS Report Does Not Display Resultset Through Dataset Returns Resultset
Oct 10, 2012I 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
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
[code]....
SQL Server 2008 :: SELECT On Column Name From Querys Resultset In Same Query?
May 9, 2015I have a column colC in a table myTable that has a value (e.g. '0X'). The position of a non-zero character in column colC refers to the ordinal position of another column in the table myTable (in the aforementioned example, colB).To get a column name (i.e., colA or colB) from table myTable, I can join ("ON cte.pos = cn.ORDINAL_POSITION") to INFORMATION_SCHEMA.COLUMNS for that table catalog, schema and name. But I want to show the value of what is in that column (e.g., 'ABC'), not just the name. Hoping for:
COLUMN_NAME Value
----------- -----
colB 123
colA XYZ
I've tried dynamic SQL to no success, probably not executing the concept correctly..Below is what I have:
CREATE TABLE myTable (colA VARCHAR(3), colB VARCHAR(3), colC VARCHAR(3))
INSERT INTO myTable (colA, colB, colC) VALUES ('ABC', '123', '0X')
INSERT INTO myTable (colA, colB, colC) VALUES ('XYZ', '789', 'X0')
;WITH cte AS
(
SELECT CAST(PATINDEX('%[^0]%', colC) AS SMALLINT) pos, STUFF(colC, 1, PATINDEX('%[^0]%', colC), '') colC
FROM myTable
[code]....
SQL In-Memory :: Table Memory Optimization Advisor Validation Passed But Cannot Migrate
Jul 13, 2015I am looking to test this feature - and the "Transaction Performance Collector" has recommended me a table to port to In-Memory OLTP.
I have now tried the "Table Memory Optimization Advisor" tool.
After a couple of tweaks to the table design - the tool is now passing validation but the tool is not allowing to progress to the next step:
Could it be down to not having enough memory? But would this not show in the advisor?
How To Insert Resultset Into New Table
Jul 11, 2013I have this query running perfectly fine and the result set looks like this
NumberRows Time_stamp
----------- -----------------------------
940 2013-07-11 18:00:00.357
Now i want to insert these two columns values in a new table.
DECLARE @dCurrentTime DATETIME
DECLARE @dCurrentTimeMinus5 DATETIME
--Declare @counttotal int
SET @dCurrentTime = GETDATE()
SET @dCurrentTimeMinus5 = DATEADD(minute, -5, @dCurrentTime)
[Code] ....
How To Put A Resultset Of A System SP Into A Table
Oct 30, 2006Hi,
How to put a resultset of a System Stored Procedure into a table?.
For Ex,
I want to put the result of sp_Databases into a table
Help On Comparing And Modifying A Resultset Or Table
Jun 14, 2007HI everybody need help badly on the following
have to recordsets with the following with the following SQL
SELECT place, count(minus)as countminus
order by place
group by place
THE RESULT WOULD BE
PLACE countminus
ABC 10
DEF 50
HIJ 5
KLM 2
MNO 0
MY OTHER QUERY IS
SELECT plus, count(plus) as countplus
GROUP BY plus
ORDER BY plus
THE RESULT WOULD BE:
PLUS COUNTPLUS
ABC 10
DEF 20
my problem is i need to add another column to the first resultset (if possible) or create a table with the same field and values of the first result set with the additional column OF THE second resultset COUNTPLUS...
with this condition..
I need to loop with single row from the column PLACE on the first result set and compare it with the column PLUS on the second result set. if there is a value of the PLACE=PLUS get the corresponding value of the COLUMN COUNTPLUS AND WRITE it on the COUNTPLUS ON THE NEW TABLE.. ELSE THE VALUE IS 0 this i would do it up to the END OF FILE EOF...
THE RESULT WOULD BE THIS.....
PLACE countminus COUNTPLUS
ABC 10 10
DEF 50 20
HIJ 5 0
KLM 2 0
MNO 0 0
I WAS WORKING ON THIS BUT COULDN'T GET THROUGH IT ANYTHING WOULD BE A BIG HELP FOR ME FROM YOU GUYS
Adding A Column To A Resultset In A SP WITHOUT Using A Temp Table
Feb 7, 2007I have the following tablestblGroupsGroupID intGroupName nvarchar(50)tblGroupMembersGroupID int (FK)UserID intI need a stored proc which:returns the groupID and name of all the groups of which userid 5 is a member AND also return the number of members that a group has (so the numbers of records in tblGroupMembers with a specific groupID)I have 2 sp's:myspGetGroupMembersCount which takes as input a groupID and has as output an integer valuemyspGetGroupsforUser which must return the entire resultsetSo say that userID 5 is a member of GroupID 6,18 and 22the following must be the resultset:UserID GroupID GroupName Members5 6 bla 132 5 18 yes 17 5 22 whatever 200 I think I need to call myspGetGroupMembersCount from within myspGetGroupsforUser and add it to the resultset (I dont want to work with a temptable)...but I dont know how...
View 2 Replies View Related(JDBC) Can You Retrieve A Resultset From A Table-value Function?
Jan 31, 2008I suppose that this could also be called: Can you retrieve a parameterized view with JDBC? Either accomplishes the same basic task.
I have looked in the documentation, and there really isn't much that talks about JDBC and functions - just stored procedures - which do not returned a CURSOR type according to Microsoft's documentation.
So far, I can retrieve scaler type data in both procedures and functions, and retrieve data sets through executeQuery with a provided sql query. How to return a resultset in Java using procedure or function has still eluded me. (<sarcasm intended> And I thought Java could do just about anything. Who would've thought...)
Any ideas would be greatly appreciated. Resources, books, etc. Google hasn't offered much help with SQL Server and JDBC.
Thanks,
Ken
How To Save Listbox Multiple Select Values
Jan 11, 2005Hi,
I have an ASP.NET form that stores it's data in MSDE but I just added a multi-select ListBox to the form and I'm having a hard time coming up with a way of writing that data to the database. Should I write the values into a column on the same table where I store the rest of the data from the form (values separated by a comma) or shouild I create another table (one to many) and store the data there. I like the second option, but I'm not sure how to loop through each value and write it to the database table.
I grab the values for the selection as follow:
foreach (ListItem lstItem in lbAttendees.Items)
{
if (lstItem.Selected == true)
{
grpList.Add(lstItem.Value.ToString());
}
}
but I'm not sure on what to do next and could use some help.
Thanks
Germano
T-SQL (SS2K8) :: Second Resultset Of Stored Procedure Into Temp Table
Jun 6, 2014I'm working on building a report and asked a developer which table some data comes from in an application. His answer was the name of a 3500 line stored procedure that returns 2 result sets. I could accomplish what I'm trying to do using the second result set, but I'm not sure how to put that into a temporary table so that I could use it.
Here's my plan according to the Kübler-Ross software development lifecycle:
Denial - Ask the developer to make sure this is correct (done)
Despair - Look hopelessly for a solution (where I am now)
Anger - Chastise developer
Bargaining - See if I can get him to at least swap the order that the resultsets are returned
Acceptance - Tell the users that this can't be done at present.
JDBC - How Robust? Loading ResultSet Into SQL SERVER Table
Apr 24, 2008
I have a program that I'm working on that utilizes a JDBC connection to a SQL 2005 server. A result set is generated from a legacy system. The data that is pulled into this result set is in the same layout as a table in SQL 2005 server.
My question is, once I have good data loaded into the ResultSet object can I pass it to the JDBC to load it into the table?
Or do I have to use the ResultSet.get(string) to pull out each value row by row, populate a string, and then use the string in an insert statement that I pass to JDBC?
Are there tricks or optimizations I can do for this type of JDBC transfer?
--Thanks--
Create Rich Text From A Select And Save It In A Field
Jun 28, 2007Hi,
I want to execute some queries inside a stored procedure, get the data and create from them some strings with formatted parts (bold, italic, underline, different color fonts) and save them in a text field. These strings are going to be displayed in some DBRichText control in a database application built with Delphi. Is there an easy way to create these complex rtf formats in MSSQL
or some ideas regarding this problem?
Best Regards,
Manolis Perrakis
SQL Server 2012 :: Simple XML To Table Resultset From Stored Procedure
Oct 29, 2014I got some xml that is essentially an html table that I need to turn into a standard table resultset from a stored proc. If you take this xml and save it as html that is the desired resultset I am looking for. I realize the <td> tags repeat so I would just prefer 'col' + positional index for the col name. Keep in mind that <td> could be is 1 to n.
<table>
<tr>
<td>cell1</td>
<td>cell2</td>
<td>cell3</td>
[Code] .....
This is my attempt but I can't figure out how to get separate cols
declare @GridData xml = '<table><tr><td>cell1</td><td>cell2</td><td>cell3</td></tr><tr><td>cell4</td><td>cell5</td><td>cell6</td></tr><tr><td>cell7</td><td>cell8</td><td>cell8</td></tr></table>'
select T.C.value('.', 'nvarchar(max)')
from @GridData.nodes('//tr') T(C)
SQL Server 2012 :: Dynamic Query To Print Out Resultset From A Table?
Sep 9, 2015I wan to print out the dynamic query result so that i can use as a script for some tasks.This is the scenario wher i got stuck, i am not able to print out the result as it return only the last value because of OUTPUT param limitation
Is there any way to print all the 3 INSERT stmt.
IF OBJECT_ID ('tempdb.dbo.#temp') IS NOT NULL
DROP TABLE #temp
CREATE TABLE #temp (Command varchar(8000))
INSERT INTO #temp
SELECT 'INSERT INTO Test1(column1,column2)values(1,2)'
UNION ALL
SELECT 'INSERT INTO Test2(column1,column2)values(1,2)'
[code]....
What Is The Best Approach To Export A Table Or Subtable(resultset) To A Separate .sdf File?
May 3, 2007hi, guys
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. )
Thanks.
Trying To Save Editted Textbox Value In Table But Original Value Saves Instead - Trouble With Table Update Query
Jan 9, 2008This program gets the values of A and B passed in. They are for table columns DXID and CODE. The textbox GET1 is initialized to B when the page is loaded. When I type another value in GET1 and try to save it, the original initialized value gets saved and not the new value I just typed in. A literal value, like "222" saves but the new GET1.TEXT doesn't.
View 1 Replies View RelatedTransact SQL :: Store Resultsets Of Stored Procedure Returning More Than One Resultset In Two Different Table?
Apr 20, 2015I have on stored procedure which returns mote than one resultset i want that to store in two different temp table how can achieve this in SQL server.
Following is the stored procedure and table that i need to create.
create procedure GetData as begin select * from Empselect * from Deptend
create table #tmp1 (Ddeptid int, deptname varchar(500),Location varchar(100))
Insert into #tmp1 (Ddeptid , deptname ,Location )
exec GetData
create table #tmp (empid int , ename varchar(500),DeptId int , salary int)
Insert into #tmp (empId,ename,deptId,salary)
exec GetData
Saving The Full Resultset Of Execute Sql Task Directly Into Sql Server 2005 Table
May 8, 2007Hi friends,
I couldn't find links for this issue.
1) How to write the contents of a dataset or a full resultset (from execute sql task) directly into a Sql Server 2005 table.
2) Since I have hundreds of Resulting columns, I want to create the Destination table based on the structure of the dataset.
How can we achieve this?
Thanks
Subhash Subramanyam
SQL Server 2005 - Save Tran Save Point Name Case Sensitive?
Feb 11, 2006Hello:I didn't find any documentation that notes save point names are casesensitive, but I guess they are...Stored Proc to reproduce:/* START CODE SNIPPET */If Exists (Select * From sysobjects Where Type = 'P' and Name ='TestSaveTran')Drop Procedure dbo.TestSaveTranGoCreate Procedure dbo.TestSaveTranAsBeginDeclare@tranCount int--Transaction HandlingSelect @tranCount = @@TRANCOUNTIf (@tranCount=0)Begin Tran localtranElseSave Tran localtranBegin Try--Simulate Error While ProcessingRAISERROR('Something bad happened', 16, 1)/*If this proc started transaction then commit it,otherwise return and let caller handle transaction*/IF (@tranCount=0)Commit Tran localtranEnd TryBegin Catch--Rollback to save pointRollback Tran LOCALTRAN --<< NOTE case change--Log Error--Reraise ErrorEnd CatchEndGo--Execute Stored ProcExec dbo.TestSaveTran/*Should receive the following message:Cannot roll back LOCALTRAN. No transaction or savepoint of that namewas found.*//* END CODE SNIPPET */What is really strange, if there is a transaction open, then no erroris thrown. So if you execute as so:/* START CODE SNIPPET */Begin Tran--Execute Stored ProcExec dbo.TestSaveTran/* END CODE SNIPPET */There is no "Cannot roll back LOCALTRAN...." message.Questions:1-)Can someone confirm save point names are case sensitve and this isnot happening because of a server setting?2-)Is this a logic error that I am not seeing in the example codeabove?We have changed our code to store the save point name in a variable,which will hopefully mitigate this "problem".Thx.
View 4 Replies View RelatedError While Trying To Save Table.
May 28, 2008Hi,
i have a table called fundperformance and i opened that table in design view and deleted a column and added another column to that table and when i try to save the changes i made to it i get the below error.
'Fund' table saved successfully'FundPerformance' table- Unable to create index 'PK_FundPerformance'. The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.FundPerformance' and the index name 'PK_FundPerformance'. The duplicate key value is (1).Could not create constraint. See previous errors.The statement has been terminated.
what should i do to fix it.
Thanks
Karen
Save Any File Into A Table
Feb 2, 2005I have a asp.net application where i can choose to save any file(could be jpg or dat or txt or xml etc etc) into a table.So far,i've manage to save it into an SQL server table in a column(set to Image datatype).But when i get the data from the table,put it into a byte array and recreate it.I notice that the files can be recreated correctly if they are of type .txt files.But jpg files can't be recreated and trying to open the jpg files gives an error.I don't think it is the file saving that is the problem since any files can be save.Rather it is the file recreating that is the problem.Can anyone help?
View 3 Replies View RelatedSave Or Commit To A Table.
Apr 7, 2006I'm rusty on the tsql side. How do you save to a table from a sql statement. I'm testing for sp's.
View 14 Replies View RelatedHow Can We Save A Table In Other Database
Sep 11, 20061-I want to save records from a table located in my database1 on Server1 to a non existing table in another database2 in another server2. (The destination table doesn t exist, I want it to be created with T-SQL). How can I do that using T-SQL
2- Also, is there an other better practice to back up my table before I do some dammage inintentially and destroy the table or the records forever or does SQL server do that automatically for us.
Thanks a lot for your advice.
How To Save A Dataset To A Sql Server Table?
Sep 21, 2007Scenario: I have created a dataset from an excel file to display it on a screen. Now I would like to save this same dataset in a SQL Server table.
Any ideas would be greatly appreciated.
Thanks
How To Save Trigger Into Table In Sql 2005
Dec 22, 2007I have found the node for Triggers in SQL Server 2005's Management Studio and tried to create a 'New Trigger...' but when I save, it saves it to an .sql file but do not attach it to the table.What am I doing wrong here? I can't seem to attach it. And where is this Assisted Editor ...i can't seem to find it anywhere
View 2 Replies View RelatedSave DELETED Entries To New Table
Mar 23, 2008Hi all
I would like to know if its possible to "Save" records when they get deleted.
For example: I have a table, tblUsers, with coulmns, UserID, Name, Surname, etc...
In VWD I've created a GridView which shows everything on a webpage. I've also added a confirm return('Are you sure you want to delete the user?') option in OnClientClick field. What i want to achieve is, have some sort of log file, or log table if you want to call it that, of which users has been deleted by the end user. So, in later stages, i can see who deleted who, when, where, etc... - by building a report or view.
All this should go to a seperate database or seperate table, it doesnt really matter.
My delete query:DELETE FROM [tblUsers] WHERE [UserID] = @UserID
Save Pictures In Sql Server Table
Aug 15, 2004how can i save pictures in sql server and retreive them
View 1 Replies View RelatedHow Do I Save My Query Results Into New Table
Apr 29, 2008How do I save my query results into new table.... The ORIGINAL COLUMN Of course before parsing--- But the only data I want is in the three no name columns---(NO Column Name),(NO Column Name),(NO Column Name)I don’t want the original column saved back but I think it existing in the final query is blocking my Insert Into---
View 2 Replies View Related