Data SubSet

Is it possible to perform an SQL query on a ReportingServices DataSet... I
would like to write a statement like this:
SELECT COUNT(DISTINCT ServiceID) FROM RS_DataSet WHERE ServiceStatus = "ACTIVE"
I know it would be easy to create a new DataSet with this query... however
RS it getting this table from an application... not directly from a
database...Couldn't you just filter the dataset that you already have?
"MER78" wrote:
> Is it possible to perform an SQL query on a ReportingServices DataSet... I
> would like to write a statement like this:
> SELECT COUNT(DISTINCT ServiceID) FROM RS_DataSet WHERE ServiceStatus => "ACTIVE"
> I know it would be easy to create a new DataSet with this query... however
> RS it getting this table from an application... not directly from a
> database...|||Well that is what I was talking about... but I can't figure out how I would
do that without making invisible tables that filter and count for each
condition... the table I have displaying my DataSet is supposed to show all
the data... but counting say ACTIVE records... would be a subset of that
DataSet.
"B. Mark McKinney" wrote:
> Couldn't you just filter the dataset that you already have?|||I figured out a solution for my problem... with a little help from Google...
for anyone that might b interested...
=CountDistinct(IIF(Fields!ServiceStatus.Value = 1, Fields!ServiceID.Value,
NOTHING))
"MER78" wrote:
> Is it possible to perform an SQL query on a ReportingServices DataSet... I
> would like to write a statement like this:
> SELECT COUNT(DISTINCT ServiceID) FROM RS_DataSet WHERE ServiceStatus => "ACTIVE"
> I know it would be easy to create a new DataSet with this query... however
> RS it getting this table from an application... not directly from a
> database...

Data results to Variables

Hello,

I am using VWD, SQL EE, VB

I have a select statement with a varaiable in it (as below). Rather than displaying the results directly in a DetailsView I want to add the results to a set of variables (Dim XXX as String etc) so I can check to see if each variable contains data or not, before then displaying only those lines which contain data. Can anyone suggest how I go about linking each data field to a variable?

SELECT PID, Postcode, Address1, Address2, Address3, Address4, Address5, Town_City, County, Postcode AS Expr1
FROM postcode
WHERE (Postcode = @.Postcode)

cheers

marco gwiliani

Hi Marco,

If you haven't done so already, i would suggest giving a really good look at the data tutorials here on asp.net. It's well worth the time and the examples are in both VB and C#.

|||

Yes Marco,

I would suggest you start from learning ADO.NET. Here is a good place to start.

http://msdn2.microsoft.com/en-us/library/h43ks021.aspx

Data Parsing in SQL Server views

If column1 in SQL Server column is text: 19980701

What is the syntax in the select statement to convert it to a date
like: 07/01/1998

Thanks for any help

RbollingerYou can use CAST() on a string to get a DATETIME data type. Of course,
you know that you do not formatting in the database in a tiered
architecture. And you know that you should only use the ISO-8601
standards for temporal data. If you want to be a bad programmer, you
can use CONVERT(). It is a proprietary piece of legacy syntax that
will let you pick a national format.

Data Not Copying to MS Access Table

Hello,

I have a Data Flow Source that uses a SQL Command to pull data. In the SQL statement, I used CAST to change all varchar types to Nvarvchar to suit MS Access. I can preview the data from the source. In testing, the SQL statement only pulls about ten records.

I have a Microsoft 2000 Access database table as a destination. Data in each column in the table is required, and all columns have defaults.

I also have a grid data viewer set up. I have the DefaultBufferMaxRows set to 2 so that I can see data going across. When I execute this dataflow, no data is transfered to the Access database table. No data shows up in the dataviewer. There are no errors. The 'Execution Results' tab does not show errors, but indicates that zero rows were transfered. There are no warnings.

How do I begin to isolate the problem? The following is the SQL Statement in the Data Flow Source. Thank you for your help! - cdun2

DECLARE @.CategoryTable TABLE
(ColID Int,
ColCategory varchar(60),
ColValue varchar(500)
)

--and fill it

INSERT INTO @.CategoryTable
(ColID, ColCategory, ColValue)
SELECT
0,
LEFT(RawCollectionData,CHARINDEX(':',RawCollectionData)),
LTRIM(SUBSTRING(RawCollectionData,CHARINDEX(':',RawCollectionData)+1,255))
FROM Collections_Staging

--Assign an ID to each block of data for each occurance of 'Reason:'

DECLARE @.ID int
SET @.ID = 1
UPDATE @.CategoryTable
SET [ColID] = CASE WHEN ColCategory = 'Reason:' THEN @.ID - 1 ELSE @.ID END,
@.ID = CASE WHEN ColCategory = 'Reason:' THEN @.ID + 1 ELSE @.ID END

--Then put the data together

SELECT --cast to Nvarchar for MSAccess
a.ColID,
CAST(a.ColValue as Nvarchar(30)) AS OrderID,
COALESCE(CAST(b.ColValue as Nvarchar(30)),'') AS SellerUserID,
COALESCE(CAST(c.ColValue as Nvarchar(100)),'') AS BusinessName,
COALESCE(CAST(d.ColValue as Nvarchar(15)),'') AS BankID,
COALESCE(CAST(e.ColValue as Nvarchar(15)),'') AS AccountID,
COALESCE(CAST(SUBSTRING(f.ColValue,CHARINDEX('$',f.ColValue)+1,500)AS DECIMAL(18,2)),0) AS CollectionAmount,
COALESCE(CAST(g.ColValue as Nvarchar(10)),'') AS TransactionType,
CASE
WHEN h.ColValue LIKE '%Matching Disbursement%' THEN NULL
ELSE CAST(h.ColValue AS SmallDateTime)
END AS DisbursementDate,
--COALESCE(h.ColValue,'') AS DisbursementDate,
CASE
WHEN i.ColValue LIKE '%Matching Disbursements%' THEN NULL
WHEN CAST(LEFT(REVERSE(i.ColValue),4)AS INT) > 1000 THEN CAST(i.ColValue AS SmallDateTime)
WHEN LEFT(REVERSE(i.ColValue),4) = '1000' THEN NULL
END AS ReturnDate,
--COALESCE(i.ColValue,'') AS ReturnDate,
COALESCE(CAST(j.ColValue as Nvarchar(4)),'') AS Code,
COALESCE(CAST(k.ColValue as Nvarchar(255)),'') AS CollectionReason
FROM @.CategoryTable a
LEFT JOIN @.CategoryTable b ON b.ColID = a.ColID AND b.ColCategory = 'Seller UserId:'
LEFT JOIN @.CategoryTable c ON c.ColID = a.ColID AND c.ColCategory = 'Business Name:'
LEFT JOIN @.CategoryTable d ON d.ColID = a.ColID AND d.ColCategory = 'Bank ID:'
LEFT JOIN @.CategoryTable e ON e.ColID = a.ColID AND e.ColCategory = 'Account ID:'
LEFT JOIN @.CategoryTable f ON f.ColID = a.ColID AND f.ColCategory = 'Amount:'
LEFT JOIN @.CategoryTable g ON g.ColID = a.ColID AND g.ColCategory = 'Transaction Type:'
LEFT JOIN @.CategoryTable h ON h.ColID = a.ColID AND h.ColCategory = 'Disbursement Date:'
LEFT JOIN @.CategoryTable i ON i.ColID = a.ColID AND i.ColCategory = 'Return Date:'
LEFT JOIN @.CategoryTable j ON j.ColID = a.ColID AND j.ColCategory = 'Code:'
LEFT JOIN @.CategoryTable k ON k.ColID = a.ColID AND k.ColCategory = 'Reason:'

WHERE a.ColCategory = 'Order ID:'

Are you doing this on an x64 machine. Is it possible that your package is executed in 64-bit mode? There is no 64-bit version of the JET provider.

There have been many posts about this before. Seaarch this forum to get instructions for making sure the packages are executed in 32-bit mode.

Thanks,

Bob

|||

Hi Cdun,

When you preview the Source, do you find any rows existing for the query? If yes, When you execute the dataflow task can you see any rows in Data grid viewer?

In fact if there are rows, it must be transfered to the destination table.

Thanks

Subhash Subramanyam

|||

Subhash Subramanyam wrote:

When you execute the dataflow task can you see any rows in Data grid viewer?

There are rows in the preview, but no rows in the data grid viewer. I'll check into the 64 bit setting.

|||

Bob Bojanic - MSFT wrote:

Seaarch this forum to get instructions for making sure the packages are executed in 32-bit mode.

Thanks,

Bob

I went to the package properties, debugging, and set Run64BitRuntime to False. I still get the same result The data will preview, but will not transfer into the Access table.

In the Data Flow Task, I'm using an OLEDB source to execute the sql statement. Should I be using an Execute SQL Task instead? The sql statement also uses a TABLE variable. Could this be a problem?

|||There must be something wrong with the way I'm trying to deliver the data to the Access table, because I can't get data to a sql server table destination either.|||

Never mind on this. I resorted to creating a table UDF as the source, and it looks like that will work.

cdun2

data not appearing

In the data flow, I have an "OLE DB source" container that calls an SP. The SP does a select statement at the end. If I hit the "Preview" button I'm seeing the results come back, however, when I go to the columns I'm not seeing anything. I don't see the column name. Is there any reason why I don't seem to see this column coming back?

Thanks,

Phil

That issues has been address in this fourm. Try searching..I just found these:

http://forums.microsoft.com/MSDN/Search/Search.aspx?words=OLE+SOurce+stored+procedure&localechoice=9&SiteID=1&searchscope=forumscope&ForumID=80

Data into XML

Hi, I have a select statement which returns data in sql server 2000, however i need to convert this into an XML document. I have the option of doing it via VB6 however, i do not want to do this as there will be up to 50, 000 records conveting, there must be a simple way just to convert it on the server.

Any ideas appretiatedJust bumping this back up the chain|||Not too sure if this is what you are looking for.

Dim comp As New Data.DataSet

After reading all the data required into your data adapter ect.

Use the final coding to write to an xml file in the spcified location:

comp.WriteXml("C:\xml\TestXml_Complete.xml")

\TestXml_Complete.xml is the name of the xml you will be saving...

Data Insert via SQL Express using VB Issue

I've been doing a lot of research lately to determine the most secure way to issue an insert statement to insert my data into my SQL database. I'm coding this following using Visual Basic:

Protected Sub submitButton_Click(ByVal senderAs Object,ByVal eAs System.EventArgs)Dim strConnAs String ="server=.\SQLEXPRESS;database=C:\SJ-site\SJ\App_Data\SJDb.mdf; User Id=user; password=password"Dim MyQueryAs String ="Insert into sjTable (PostID, Category, TitleofPost, Description, Price, Loc, ContactInfo, DatePosted, Disclaimer) Values (@.Category, @.TitleofPost, @.Description, @.Price, @.Loc, @.ContactInfo, @.DatePosted)"Dim MyConnAs New Data.SqlClient.SqlConnection(strConn)Dim cmdAs New Data.SqlClient.SqlCommand(MyQuery, MyConn)Dim curDateAs String On Error Resume Next MyConn.Open()If Err.Number <> 0Then MsgBox("There was an error connecting to the database.", MsgBoxStyle.OkOnly) Err.Number = 0End If curDate = Now()With cmd.Parameters .Add(New System.Data.SqlClient.SqlParameter("@.Category", categoryDropDown.Text)) .Add(New System.Data.SqlClient.SqlParameter("@.TiteofPost", titleTextbox.Text)) .Add(New System.Data.SqlClient.SqlParameter("@.Description", descriptionTextbox.Text)) .Add(New System.Data.SqlClient.SqlParameter("@.Price", priceTextbox.Text)) .Add(New System.Data.SqlClient.SqlParameter("@.Loc", locationTextbox.Text)) .Add(New System.Data.SqlClient.SqlParameter("@.ContactInfo", contactTextbox.Text)) .Add(New System.Data.SqlClient.SqlParameter("@.DatePosted", curDate))End With If Err.Number <> 0Then MsgBox("There was an error inserting the data into the database.", MsgBoxStyle.OkOnly) Err.Number = 0End If MyConn.Close()If Err.Number <> 0Then MsgBox("There was an erro closing the database connection.", MsgBoxStyle.OkOnly) Err.Number = 0End If Server.Transfer("submitted.aspx")End Sub

The code gives no errors, it directs me straight to thesubmitted.aspx page but when I query the database it is not insertingany data into my database. .

My Error log shows the following:
2006-06-27 12:14:16.68 spid51 Starting up database 'C:\SJ-SITE\SJ\APP_DATA\SJDB.MDF'.
2006-06-27 12:14:26.93 Logon Error: 17828, Severity: 20, State: 3.
2006-06-27 12:14:26.93 Logon The prelogin packet used to open the connection is structurally invalid; the connection has been closed. Please contact the vendor of the client library. [CLIENT: <local machine>]

I am able to log into the database using my account via SQL Server Management Studio Express. I've been all over the forums and google looking for a solution but everything I've been trying to mock up from other people's code hasn't helped. Thanks in advance for your assistance

Hi, do you have 17190 error in your SQL ERRORLOG when SQL startup?

2006-03-24 10:37:09.80 Server Error: 17190, Severity: 16, State: 1.
2006-03-24 10:37:09.80 Server FallBack certificate initialization failed with error code: 1.
2006-03-24 10:37:09.81 Server Warning:Encryption is not available, could not find a valid certificate to load.

And is your SQL service startup account a local account whose password has been changed recently?

If so, try to delete the file in folder

C:\Documents and Settings\<SQL Server startup
account>\Application Data\Microsoft\Crypto\RSA\S-1-5-21-963106725-2405722570-177647575-1023

The last part (S-1-5-21-963106725-2405722570-177647575-1023) may be different for
every account. Then restartSQL Server services to take effect.

|||I went ahead and upgraded to SQL Express 2005 SP1 w/ Advanced Services. I re-built my user accounts and what not. Now, I am still getting no error messages but also when I open the log file the last thing it says is:
2006-06-28 09:06:57.93 spid51 Starting up database 'C:\SJ-SITE\SJ\APP_DATA\SUGARJACKS.MDF'.

The data from the forms still isn't inserting into the database. The code hasn't changed and like I said the only thing I did was upgrade the SQL Express and re-build the user account.
|||On a side note I just wanted to say that yes I did change the database name and yes I did update the connection string accordingly in the code.

Data in one column contains column name of other table

I have one table in one of its column it stored column name of another
table. i want select statement to select that column name. i will explain
this with example
Table1 has columns with name ID,col1 and having data in that column as
(1,tb_col1),(2,tb_col2),(3,tb_col3) etc
and another table has this columndata of table one as column name means
table2 has column (tab_id2,tb_col1,tb_col2,tb_col3)
inthis if i select ID=1 from table1 then select statement should show me
data in column tb_col1 of table2..
if i select ID=2 from table1 then select statement should show me data in
column tb_col2 of table2..
here data in column col1 of table1 may change any time so i want it
dynamically
Message posted via http://www.webservertalk.comYou need to use dynamic sql.
Example:
create procedure dbo.proc1
@.id int
as
set nocount on
declare @.sql nvarchar(4000)
if exists(select * from table1 where [id] = @.id)
begin
set @.sql = N'select [' + (select top 1 col1 from table1 where [id] =
@.id) + N'] from table2'
exec sp_executesql @.sql
end
return @.@.error
go
exec dbo.proc1 1
exec dbo.proc1 2
go
The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html
AMB
"Malkesh Sheth via webservertalk.com" wrote:

> I have one table in one of its column it stored column name of another
> table. i want select statement to select that column name. i will explain
> this with example
> Table1 has columns with name ID,col1 and having data in that column as
> (1,tb_col1),(2,tb_col2),(3,tb_col3) etc
> and another table has this columndata of table one as column name means
> table2 has column (tab_id2,tb_col1,tb_col2,tb_col3)
> inthis if i select ID=1 from table1 then select statement should show me
> data in column tb_col1 of table2..
> if i select ID=2 from table1 then select statement should show me data in
> column tb_col2 of table2..
> here data in column col1 of table1 may change any time so i want it
> dynamically
> --
> Message posted via http://www.webservertalk.com
>|||>> I have one table in one of its column it stored column name of
another table. <<
This is called mixing data and metadata. Good SQL programmers do not
write code like this! Bad SQL programmers use Dynamic SQL and lose
their data integrity.

Data from Three Tables

I have three tables: Contact, Staff, Codes.
My SQL statement is as follows:
SELECT tm5user.contact.full_name, tm5user.contact.phone1,
tm5user.contact.firm, tm5user.contact.city, tm5user.staff.[last],
tm5user.staff.[first],
tm5user.codes.[desc]
FROM tm5user.contact INNER JOIN
tm5user.staff ON tm5user.contact.staff =
tm5user.staff.init INNER JOIN
tm5user.codes ON tm5user.contact.ccode =
tm5user.codes.ccode
The problem is that for each contact record, I get between 1 and 5 records
in my result set. I would like only one record displayed for each contact
record.
How can I do this, I am at a loss.
Please help.
Thanks,
GaryYour Joins are incomplete, means that some key columns are missing, but
impossible for us to solve the problem without knowing the DDL. Post it here
and we could might help you.
The table have a onetomany or manytomany relationsship to each other, but
guessing wont help you further, so try to post the ddl.
HTH, Jens Smeyer.
"Gary Paris" <yada@.somewhereovertherainbow.com> schrieb im Newsbeitrag
news:e2632a7PFHA.1932@.tk2msftngp13.phx.gbl...
>I have three tables: Contact, Staff, Codes.
>
> My SQL statement is as follows:
> SELECT tm5user.contact.full_name, tm5user.contact.phone1,
> tm5user.contact.firm, tm5user.contact.city, tm5user.staff.[last],
> tm5user.staff.[first],
> tm5user.codes.[desc]
> FROM tm5user.contact INNER JOIN
> tm5user.staff ON tm5user.contact.staff =
> tm5user.staff.init INNER JOIN
> tm5user.codes ON tm5user.contact.ccode =
> tm5user.codes.ccode
> The problem is that for each contact record, I get between 1 and 5 records
> in my result set. I would like only one record displayed for each contact
> record.
> How can I do this, I am at a loss.
> Please help.
> Thanks,
> Gary
>|||I was playing around in Enterprise manager and found that in the Codes
table, there were many codes that were the same, but another field called
rec_type was unique. By putting in a where clause, that solved the problem.
Thank you,
Gary
"Jens Smeyer" <Jens@.sqlserver2005.de> wrote in message
news:eZTSGe7PFHA.2520@.tk2msftngp13.phx.gbl...
> Your Joins are incomplete, means that some key columns are missing, but
> impossible for us to solve the problem without knowing the DDL. Post it
> here and we could might help you.
> The table have a onetomany or manytomany relationsship to each other, but
> guessing wont help you further, so try to post the ddl.
>
> HTH, Jens Smeyer.
>
> "Gary Paris" <yada@.somewhereovertherainbow.com> schrieb im Newsbeitrag
> news:e2632a7PFHA.1932@.tk2msftngp13.phx.gbl...
>