MS SQL Server :: ResultSet Property Not Set Correctly?

Problems With The Query, ResultSet Property Not Set Correctly, Parameters Not Set Correctly, Or Connection Not Established Cor

Oct 22, 2007

I have the following query in an ExecuteSQL Task:

Insert Into Table2
Select * From Table1 Where Column1Val = '4'

As you can see, I don't need any parameters so I havent configured any. Also, there should not be any result set so I shouldnt need to configure a resultset parameter.

Why is the above query failing with

Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly

View 4 Replies View Related

OLE DB: How To Set The Property DBPROP_SSCE_TRANSACTION_COMMIT_MODE Correctly?

Jan 30, 2007

Hello,

because of the fact that the database sdf-file is stored on a cf-card, i want that all commit transactions will be flushed to the file immediately.
So i want to open the database with the above property. But i don't know the type and value i have to place into the variant. The following code fragment gave me the error "DB_S_ERRORSOCCURRED when i try to set the property with IDBInitialize::SetProperties() :

//Initialize property DBPROP_SSCE_TRANSACTION_COMMIT_MODE
dbprop_ssce_session[0].dwPropertyID = DBPROP_SSCE_TRANSACTION_COMMIT_MODE;
dbprop_ssce_session[0].dwOptions = DBPROPOPTIONS_REQUIRED;
dbprop_ssce_session[0].vValue.vt = VT_I4;
dbprop_ssce_session[0].vValue.lVal = DBPROPVAL_SSCE_TCM_FLUSH;

//Initialize property set DBPROPSET_SSCE_SESSION
dbpropset[1].guidPropertySet = DBPROPSET_SSCE_SESSION;
dbpropset[1].rgProperties = dbprop_ssce_session;
dbpropset[1].cProperties = 1;
//(there is another property for the path not shown here)

I would appreciate it when someone out there knows the answer and could tell me.

Kind regards,
Andre

View 4 Replies View Related

Color Property Formatting Not Working Correctly

Apr 30, 2007

Hi,

I have a problem with a conditional format of the Color property in a matrix report. When the value of the textbox is greater than 0, the color should be Red otherwise it should be Blue. This is implemented via an IIF statement and works perfectly in Preview in Visual Studio 2005.

When the report is deployed, the formatting appears to be ignored and all values are the default value of Black.

Anyone else experienced this problem and, if so, is there a workaround?

View 3 Replies View Related

Currentversion Property Not Set Correctly When Adding To Package

Feb 22, 2008

Hello all

I have a ssis component that begins like below. I recently added a new customproperty to the package and needed a way to detect when an upgrade from an old version was needed. As per microsofts directions I tried to use the currentversion property for this. This seemed to work fine until I noticed that newly added component to the dataflow keep starting at version 0 and going thru an upgrade making an duplicate of the new property. As below the new version has currentversion = 2 as a property. So why is this ignored. Or how do i make a component start out with the proper version so no upgrades happen?

Code Snippet
namespace itworkz.ssis.CustomComponents
{
[DtsPipelineComponent(
DisplayName = "ODS Tool v2",
Description = "proccess data for the operational data store",
ComponentType = ComponentType.Transform,
CurrentVersion = 2,
UITypeName = "itworkz.ssis.CustomComponents.OdsTool2UI, OdsTool2, Version=1.0.0.0, Culture=neutral, PublicKeyToken=3276bb0510a3c305"
)]

public class OdsTool2 : PipelineComponent
{

Hope someone can explain this

View 1 Replies View Related

Reporting Services :: SSRS Report Does Not Display Resultset Through Dataset Returns Resultset

Oct 10, 2012

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

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

[code]....

View 4 Replies View Related

Changing Code Page Property Using Property Expression Doesn't Work

Jun 16, 2006

I am having problems exporting data into a flat file using specific code page. My application has a variable "User::CodePage" that stores code page value (936, 950, 1252, etc) based on the data source. This variable is assigned to the CodePage property of desitnation file connection using Property expression.

But, when I execute the package, the CodePage property of the Destination file connection defaults to the initial value that was set for "User:CodePage" variable in design mode. I checked the value within the variable during runtime and it changes correctly for each data source. But, the property of the destinatin file connection doesn't change and results in an error.

[Flat File Destination [473]] Error: Data conversion failed. The data conversion for column "Column01" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".

[DTS.Pipeline] Error: The ProcessInput method on component "Flat File Destination" (473) failed with error code 0xC02020A0. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.

If I manually update the variable with correct code page and re-run the ETL, everything works fine. Just that it doesn't work during run-time mode.

Can someone please help me resolve this.

Thanks much.

View 5 Replies View Related

Value Of A Readonly Property Of Custom Task Is Not Updated In Property Window

Apr 17, 2008

Hi,

I developed a simple custom control flow component which has several read/write properties and one readonly property (lets call it ROP) whichs Get method simple returns the value of a private variable (VAR as string). In the Execute method the VAR has a value assigened. When I put the value of ROP or VAR into MsgBox I can see the correct value. However when I execute the component I can not see the value of the ROP in the property window. I see the property but its value is empty string. For example when I put a breakpoint to postexecute or check the property before click OK in a MsgBox I would expect that the property value would be updated in SSIS as well. Is there a way how to display correct values of custom tasks properties in property window?

Thanks for any hints.

View 3 Replies View Related

(URGENT) Cannot Be Written To The Property. The Expression Was Evaluated, But Cannot Be Set On The Property

May 7, 2008

Untill recently I had a smooth running SSIS package,but suddenly it throws error syaing
"OnError,,,,,,,The result of the expression

"@[User:trTextFileImpDirectory] +"SomeTextStringHere"+ @[User:trANTTextFileName] +(DT_STR,30,1252) @[User:taging_Date_Key]+ "SomeTextStringHere"
" on property "ConnectionString" cannot be written to the property. The expression was evaluated, but cannot be set on the property."

I have child SSIS package running under a parent package (through execute package task)

I have few flat file connection managers in child package for text file import , in which I am building text file path dynamically at run time by assigning an expression in connection string property of connection manager.
The Expression is as follows

"@[User:trTextFileImpDirectory] +"SomeTextStringHere."+ @[User:trANTTextFileName] +(DT_STR,30,1252) @[User:taging_Date_Key]+ +"SomeTextStringHere"

Where @[User:trTextFileImpDirectory] is a variable which contains path of directory containg text
files.Value in this variable is assigned at runtime from parent package's variable,which in turns fetch
value from a configuration file on local server.

With my current configuration this path has been configured to some other server's directory over network ( I.e my package picks text files from some other servers folder over network)

While
"Some string here"+ @[User:trANTTextFileName]" part of file name string.

(DT_STR,30,1252) @[User:taging_Date_Key] Contain the date of processing ,value in this variable is also picked up at run time from parent package variable.

1) So can someone give me some insight into possible reason of failures.
2) Is it possible that problem arises if directory (from which I m picking text files) is assigned password or is there exist some problem in accessing forlders over network ?
3) Or there can be some problem in package configuration at design time( I.e where I m assigning value in variable from parent package vriables)?

View 10 Replies View Related

Referencing One Item's Hidden Property In Order To Set Another's Hidden Property

Feb 15, 2007

Hello,

I have a group I'll call G4.

The header table row for G4 contains 3 textboxes containing the sums of the contents within G4. The header table row for G4 is visible while it's contents, including the G4 footer table row, is kept invisible until the report user drills down into the group.

When the report user drills down into G4 the footer table row becomes visible and the sums of the contents of the group are displayed for a second time.

At this point I want the sums in the header to be set to invisible when the sums in the footer are made visible by the drilldown.

When I try to reference the hidden property of textbox66 in the G4 footer in order to set the hidden property of header textbox57 in the G4 header I get to this point...

=IIF(reportitems!textbox66.

When it fails to give me an option of choosing the .Hidden property and instead only gives me a .Value.

If I complete the IIF statement manually so that it spells out .....

=IIF(ReportItems!Textbox66.Hidden = False, True, False)

...the report chokes on it.

So my question is, how do I reference the hidden property of one or more textboxes in a group to use as condition checks to set the hidden property of another textbox in that same group?

Thank you for any help you can provide. We are only now beginning to implement reporting services and I have not yet had the chance to research this in greater detail for lack of time.

View 1 Replies View Related

FOR XML Vs. Resultset

Apr 9, 2003

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.

View 3 Replies View Related

How To Get This Resultset...

Sep 24, 2006

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

View 2 Replies View Related

Limited Resultset Without SP

Sep 22, 2006

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?

View 1 Replies View Related

Query Resultset

Mar 31, 2005

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

View 5 Replies View Related

Resultset From Two Tables

Feb 1, 2008

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

results should be:
a,c
b,d

Thanks in advance.

View 14 Replies View Related

Weigh Resultset

Jul 20, 2005

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.

View 3 Replies View Related

Resultset As Source

Mar 20, 2007

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

Thanks

Dharmbir

View 3 Replies View Related

ResultSet Streaming

Jan 10, 2008

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?

View 1 Replies View Related

Empty Resultset From Sql2005

Aug 23, 2006

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=[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();

Please help!!!

Best regards
Terje Kristensen

View 2 Replies View Related

Adding Column In Resultset From SP

Dec 27, 2006

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?

View 1 Replies View Related

Multiple Resultset Limit?

Feb 15, 2005

Is there a limit to the number of result sets that a SQL query can return at one time?

View 4 Replies View Related

Exporting SQL Resultset To Excel

Jan 3, 2003

Hi,

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?

Thanks.

View 3 Replies View Related

Numbering Rows In A Resultset

Nov 19, 2001

Hello all,

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

Any ideas?

Thanks,

Dan

View 2 Replies View Related

Getting Unique Rows From The Resultset

Nov 15, 2006

Hi,

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

Please help.

View 4 Replies View Related

CTE ResultSet Counts Vary

Jan 18, 2013

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.

View 1 Replies View Related

Resultset With Summary Rows

Feb 26, 2004

Hi,

I have problem with query/sp which need to return one resultset with strucure like:

summary row1
detail row1
detail row2
detail row3
summary row2
detail row4
detail row5
...

Tables:
T_HW (HW_ID INT, CatID INT, other cols (NVARCHARs, INTs, DATETIMEs, NTEXTs etc.)
T_Category (CatID INT, Manufact NVARCHAR (100), other cols)

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 ...
...

it needs to be one result recordset

Thx in advance,
MST78

View 4 Replies View Related

Return A Resultset From A Stored Pro

Mar 16, 2004

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.

View 3 Replies View Related

Sp Fast Loop Through Resultset

May 16, 2008

Hi,

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

thx in advance.

View 3 Replies View Related

Finding Count In A Resultset

Jun 19, 2008

I have a select with a few joins producing this results table:

1 Name1 Date1
1 Name1 Date1
1 Name1 Date1
2 Name1 Date2
2 Name1 Date2
2 Name1 Date2
2 Name1 Date2

How can I turn it to this:

1 Name1 Date1 Count3
2 Name1 Date2 Count4

View 15 Replies View Related

How To Insert Resultset Into New Table

Jul 11, 2013

I 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] ....

View 8 Replies View Related

How To Put A Resultset Of A System SP Into A Table

Oct 30, 2006

Hi,
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

View 3 Replies View Related

SQL Task Resultset To Email

Sep 20, 2007

Hello,

I have data (using Full Result Set) and would like to email values.

Query output from SQL Task:
ID Client
1 ABC
2 Acme
3 Holtz

Goal email output (inside Script Task):

The following records found today:
ID Client
1 ABC
2 Acme
3 Holtz

Thank you in advance.

Note: I know how the email works, it's just passing the resultset I need help on.

View 3 Replies View Related

Problem With View Resultset

Nov 12, 2007

How can I limit the resultset returned from an inner join?
I have:
tableA tableB
------ ------
1 1
1 2
2 1
2 2
3 1
3 2
3 3
... ...

I want the resultset to look like this:
tableA tableB
------ ------
1 1
2 1
3 1
... ...

tableA is the table that should limit the resultset. I have tried distinct, group by and havin without any luck.

View 6 Replies View Related