Dataset Not Populating Table

Feb 7, 2008

Hello,

Just when I thought I was starting to understand SSRS just a little and then I encounter a strange (maybe not too strange) of an issue.

I have a DataSet which runs a stored procedure and it requires 10 parameters. When I run it in the dataset portion itself it runs fine however, it will not work when I choose to Preview it. I refreshed my dataset and even rebooted my machine. Can anybody shed some light on this?

Thanks

View 6 Replies


ADVERTISEMENT

Populating A Table With A Dataset

Apr 17, 2007

I am running a program that populates tables on my local database by querying another database. 

View 2 Replies View Related

Populating A DataSet From A Stored Procedure

May 25, 2007

Hi all,
Im still relatively new to SQL Server & ASP.NET and I was wondering if anyone could be of any assistance. I have been googling for hours and getting nowhere.
Basically I need to access the query results from the execution of a stored procedure. I am trying to populate a DataSet with the data but I am unsure of how to go about this.
This is what I have so far:-
1    SqlDataSource dataSrc2 = new SqlDataSource();2    dataSrc2.ConnectionString = ConfigurationManager.ConnectionStrings[DatabaseConnectionString1].ConnectionString;3    dataSrc2.InsertCommandType = SqlDataSourceCommandType.StoredProcedure;4    dataSrc2.InsertCommand = "reportData";5    6    dataSrc2.InsertParameters.Add("ID", list_IDs.SelectedValue.ToString());7    8    int rowsAffected;9    10   11   try12   {13        rowsAffected = dataSrc2.Insert();14   } 
As you know this way of executing the query only returns the number of rows affected. I was wondering if there is a way of executing the procedure in a way that returns the data, so I can populate a DataSet with that data.
 Any help is greatly appreciated.
Slainte,
Sean
 
 
 

View 2 Replies View Related

Populating A Table

May 19, 2004

I have a table with a list of products,once I enter the data into the table and start using it on my web site as a drop down list,the list is sorted as an alphabetical list,is there are way to have a single drop down list but still be able to group the those products,in order words force them not to get sorted aphabetically.


Thanks

View 11 Replies View Related

Populating A Table

May 11, 2007

Hi, I have two tables:

1. RubricReportDetail with columns LocalPerf, Age
2. SppIndicator with columns Pct, Age

How can I populate the values of LocalPerf with Pct by matching

RubricReportDetail.Age = SppIndicator.Age ??

Please help me. Thanks in advance.

View 15 Replies View Related

Error Populating Table

May 14, 2008

Hi all,

I have a table that looks something like this: -

county fname sport
------ ----- -----
surrey tara squash
surrey tara hockey
surrey tara tennis
kent tom tennis
kent tom football
kent tom rugby


I want to read through the sport table and create a distinct list of sports which can be used to create a new table that would look like: -

County fname squash hockey tennis football rugby
------ ----- ------ ------ ------ -------- -----
surrey tara YES YES YES
kent tom YES YES YES

I am using the following code: -
DECLARE @sql NVARCHAR(MAX)
SELECT @sql = 'create table ey_report_temp (county nvarchar(100),fname nvarchar(100), ' +
STUFF((SELECT DISTINCT ',[' + sport + '] nvarchar(100) '
FROM ey_report FOR XML PATH('')), 1, 1, '') + ')'

SELECT @sql
exec sp_executesql @sql

SELECT * from ey_report_temp

--------------------------------------------

DECLARE @county nvarchar(max)
DECLARE @fname nvarchar(max)
DECLARE @sport nvarchar(max)

DECLARE merge_cursor CURSOR FAST_FORWARD
FOR SELECT county, fname, sport from ey_report

OPEN merge_cursor
FETCH NEXT FROM merge_cursor INTO @county, @fname, @sport
WHILE @@FETCH_STATUS = 0
BEGIN

select @sql = N'
update ey_report_temp
set ' + @sport + ' = ''YES''
where county = ''' + @county + '''
and fname = ''' + @fname + ''''
print @sql
exec sp_executesql @sql
if @@ROWCOUNT = 0

begin
select @sql = N'
insert into ey_report_temp (
county, fname, ' + @sport + '
) values ( ' + @county + ', ' + @fname + ', ' + @sport + ')'

exec sp_executesql @sql

end
FETCH NEXT FROM merge_cursor INTO @county, @fname, @sport
END
CLOSE merge_cursor
DEALLOCATE merge_cursor
select * from ey_report_temp

drop table ey_report_temp

This creates the new table fine however, when it trys to poulate I get the following EM, can anybody help? thanks in anticipation

(1 row(s) affected)

(0 row(s) affected)

update ey_report_temp
set squash = 'YES'
where county = 'surrey'
and fname = 'tara'

(0 row(s) affected)
Msg 128, Level 15, State 1, Line 4
The name "surrey" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

update ey_report_temp
set hockey = 'YES'
where county = 'surrey'
and fname = 'tara'

(0 row(s) affected)
Msg 128, Level 15, State 1, Line 4
The name "surrey" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

update ey_report_temp
set tennis = 'YES'
where county = 'surrey'
and fname = 'tara'

(0 row(s) affected)
Msg 128, Level 15, State 1, Line 4
The name "surrey" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

update ey_report_temp
set tennis = 'YES'
where county = 'kent'
and fname = 'tom'

(0 row(s) affected)
Msg 128, Level 15, State 1, Line 4
The name "kent" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

update ey_report_temp
set football = 'YES'
where county = 'kent'
and fname = 'tom'

(0 row(s) affected)
Msg 128, Level 15, State 1, Line 4
The name "kent" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

update ey_report_temp
set rugby = 'YES'
where county = 'kent'
and fname = 'tom'

(0 row(s) affected)
Msg 128, Level 15, State 1, Line 4
The name "kent" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

(0 row(s) affected)

View 7 Replies View Related

Populating A Lookup Table

Jul 9, 2006

I'm trying to do a table lookup in t-sql and runing into problems. I have two tables, City and County..



Table City:

CityID CountyID CountyName

1 3 NULL

2 2 NULL

3 1 NULL



Table County:

CountyID CountyName

1 Los Angeles

2 Contra Costs



I want to populate the NULL "CountyName" field in table City with the values from table County but I can't make it work! Any help appreciated.



Barkingdog

View 1 Replies View Related

Help On Populating Dimension Table

Dec 30, 2007



While populating dimsnion table the requirement is that the 1st record in all dimension should be " Not Available"

and if the fact table has a null value for that dimension it shouldbe tied to " Not Available" record in dimension
Please help hwo to do both the things.
JJ

View 5 Replies View Related

Populating A SQL Table With SmallDateTime Values

Mar 24, 2008

I have a table that I would like to update with the current dateTime of Today. The Table Field is called "DateTimeStamp" with a format of SmallDateTime -- I'm using VS 2008.
When I use the following command and then cmd.ExecuteNonQuery -- the table is correclty filled in with the exception of the DateTimeStamp field. Instead of 3/23/2008, I get the value 1/1/1900 ... 
SqlCommand cmd3 = new SqlCommand("UPDATE CameraPods SET CameraBlock1_ID = 98, CameraBlock2_ID = 99, Status = 1, DateTimeStamp = 3/3/2008");
any ideas?
    ---Jim

View 3 Replies View Related

Populating A Table With The Results Of A SQL Query

Jul 12, 2004

Is there any way i could take the first 50 results of a sql query and store them into 1 table in access and take the next 50 and store them into a second table in access? Is there any SQL statement that will direct where the output gets directed
to?

OR is there a way to have the sql reults paird up with a autonumbered ID?

Doc

View 2 Replies View Related

Populating Staging Table Using SSIS

Oct 6, 2006

Hi,

I have a flat file with columns from a geographical hierarchy such as:

Country Zone State County City Store Sub Store , etc.

The file also has data columns for months to the right of the above columns such as:

Jul Aug Sept ......... basically 25 of these columns for two years' data for one product and another set of 25 columns for another kind of product. A typical record in the file looks like:


Country Zone State County City Store Substore

USA Southeast FL Hillsborough Tampa walmart Fletcher



May04_ProdA ....Jun06_prodA May04_ProdB...Jun06ProdB
144 160 180 158

I need to upload this data into a staging table in SQL Server 2005 using SSIS, I created a table with the geographical hierarchy columns but am trying to figure out a way to load the monthly data. I can create 50 columns for the 50 months ( 25 months for each product) but that would be very crude.

Is there a better way of inserting data from this flat file into a destination table? I need all the data in the staging table in one upload.

Thanks.






View 11 Replies View Related

Populating FACT Table In ORACLE

Aug 1, 2006

I am having a torrid time trying to populate a FACT table in ORACLE.

I have so far tried two approaches -

1. Identified the change type for records - meaning I mark the record as inserts or updates based on the availability of the key in the FACT table. I used a conditional split and then used two OLE DB destination tasks to load the FACT table. But this failed as I was pretty much inserting on both the conditions (inserts and updates). So this attempt would count as a batch update process attempt.

2. Tried to also write a stored procedure in ORACLE destination database and then use that stored procedure to execute record by record using OLE DB Transformation task. But this did not fly as well due to the fact that SSIS could not understand the oracle SP and could not parse the information out.

Thank you

View 1 Replies View Related

XML Source Populating CR LF To SQL2005 Table

Oct 31, 2006

In my SSIS package I used the Web Service task to populate a SSIS variable with data from an XML feed. Then in the Data Flow I used a XML Source to import that data into a table on my SQL Server. When I looked at the data i noticed that the some of the fields contained CRLF pair. These CRLF pairs are being only populated by xml nodes that contain no data and are self-terminated. Below is a sample of the XML feed that is causing the problems. the DR_M_Initial and DR_Type1 nodes are populating CRLF's to the table.

Can anybody give me some insight on this problem. I am performing a workaround using the SQL REPLACE function in an Update statement to strip out ch(10) and ch(13), but this just seems a little cluegy to me.

Sample XML
---------------------------------------------------------
<?xml
version="1.0" encoding="utf-16" ?>

- <ROOT xmlns="">


<ROWRESULT>



<DR_NUMBER>1</DR_NUMBER>

<DR_L_NAME>SMITH</DR_L_NAME>

<DR_F_NAME>JOE</DR_F_NAME>

<DR_M_INITIAL />
<DR_TYPE1 />
</ROWRESULT>
</ROOT>

Thanks,
Tim Frakes

View 1 Replies View Related

Adding And Populating A Column To Existing Table

Dec 5, 2005

Sorry I'm pretty new to SQL so I don't know if this is a simple question. I have a table, and I am trying to add a column to the table and populate this column using what would be called an 'IF' function in Excel.

Basically 'column A' has numbers in it. I want SQL to look at 'column A' and if the first 5 digits of the number in 'column A' are 00001, then put 'description A' into new column 'column B'. If the first 5 digits of the number in 'column A' are 00002, then put 'description B' into 'column A' etc.

Any ideas?

View 2 Replies View Related

Populating Empty Table With Sequential Numbers?

Jan 15, 2015

I have a column within a table which is already truncated/deleted all records within (Microsoft SQL 2008). I have to now populate the column with sequential numbers up to 50,000 records arbitrary numbers (doesn't mater) up to 7 characters.

what SQL statement I need to write that will automatically polulate the newly empty table with A000001,A0000002,A0000003, or any form for that matter etc so that I can sort number the records within the table.

I have approximately 50000 records which I need to sequentially entered and I really dont want to number the column manually via hand editing.

View 5 Replies View Related

Masking Table Of ID Numbers - Populating A Column

Aug 29, 2013

I have a table of id numbers that I wish to mask. My thought was to create a new column for this new id number and populate it with a unique sequential value - start at 1 and go as high as needed. My problem is that I cannot recall how to populate that column with a number...

View 4 Replies View Related

Populating A Table In SQL Server 2005 Express From A Text File (*.txt)

Apr 10, 2008

I'm experienced using queries to extract data, but I'm new to actually creating tables, except through Access. I work with many records of data, so populating the data by hand is not an option. I created a test database (ValTest) and a test table within that database (ClaimTest). I created a text file (DataTest.txt) with the same layout as I defined for ClaimTest. I want to populate ClaimTest with the data from DataTest.txt. I was told the only way to do that using SQL Server Express was to use a utility called BCP. So I found a page on the Microsoft website talking about BCP. It gave some examples, so I opended up the Command Line window, pointed to the directory that contains the database and text file and typed "BCP ValTest.ClaimTest in DataTest.txt -T -c". I get 3 errors: Named Pipes Provider: Could not open a connection to SQL Server [2], Login timeout expired, and An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.

I tried looking around and finding where I could change the settings, in case that was indeed the problem, but was unable to find anything. Does anyone have any suggestions?

Thanks

View 10 Replies View Related

Fact Table With 3 Keys From Dimension Tables - Avoid Populating NULLs

Jun 10, 2014

I created a Fact Table with 3 Keys from dimension tables, like Customer Key, property key and territory key. Since I can ONLY have one Identity key on a table, what do I need to do to avoid populating NULLs on these columns..

View 3 Replies View Related

Data Warehousing :: Populating Fact Tables With Surrogate Key From Dimension Table?

Sep 11, 2015

How do I correctly populate a fact table with the surrogate key from the dimension table?

View 4 Replies View Related

Populating An Access Combo Box With Large Amount Of Data Causes Table Lock In SQL Server

Jul 20, 2005

I have a combo box where users select the customer name and can eithergo to the customer's info or open a list of the customer's orders.The RowSource for the combo box was a simple pass-through query:SELECT DISTINCT [Customer ID], [Company Name], [contact name],City,Region FROM Customers ORDER BY Customers.[Company Name];This was working fine until a couple of weeks ago. Now wheneversomeone has the form open, this statement locks the entire Customerstable.I thought a pass-through query was read-only, so how does this do atable lock?I changed the code to an unbound rowsource that asks for input of thefirst few characters first, then uses this SQL statement as therowsource:SELECT [Customer ID], [Company Name], [contact name],City, Region Fromdbo_Customers WHERE [Company Name] like '" & txtInput & "*' ORDER BY[Company Name];This helps, but if someone types only one letter, it could still bepulling a few thousand records and cause a table lock.What is the best way to populate a large combo box? I have too muchdata for the ADODB recordset to use the .AddItem methodI was trying to figure out how to use an ADODB connection, so that Ican make it read-only to eliminate the locking, but I'm striking outon my own.Any ideas would be appreciated.Roy(Using Access 2003 MDB with SQL Server 2000 back end)

View 2 Replies View Related

Populating Sql Table With Xml From Sql Table

Apr 13, 2005

I need to populate a queue table with an orderid, and the rest of the order in an xml auto element format going into a text field.
The data is coming from another sql server table. I was going to do this is an sp and started with below

declare @request text
set @request = (select orderid,* from neworder
For XML auto,ELEMENTS)
However it errors on the statement. I can run the select by itself fine, but need to store the output.
Any ideas

View 1 Replies View Related

How To Use A Table With Table Name Starting @ Symbol In Dataset

Jun 2, 2007



Hi ,



I have a database with a table name called @budget_tot.



When I run a query in report designer --dataset, it throws an error thinking this is a variable rather

than a table.



Same query works without error in SQL 2005 query window.



example Query;

Select T1.U_Budget From [@budet_tot] T1



Sonny

View 3 Replies View Related

Converting DataSet To New SQL Table

Aug 10, 2006

Hello Everyone,
I have a dataset that I created from an external database using an ODBC connection.  I would like to take that dataset and create a new table in an SQL 2005 database.  Can anyone point me in the right direction.  The problem that I am having right now is getting the object types etc.
 
Thank you!!

View 1 Replies View Related

Problem: Dataset Into SQL Table

Mar 12, 2008

I am reading a CSV file into a dataset and trying to insert this dataset into an existing SQL table.  The problem I am having lies in the fact that the dataset I have contains 2 columns, and the SQL table has 20+ columns. 
I can't get any data to be inserted into the SQL table from the dataset, I really don't know what I am doing wrong here.  Thanks for any help or suggestions. Public Function DatasetToSQL() As DataSet

Dim ds As DataSet
Dim dr As DataRow
Dim da As SqlDataAdapter
Dim punchtime As String
Dim eventtype As String
Dim sqlconn As New SqlConnection(GetConnectionString())

Try
ds = ConnectCSV()
sqlconn.Open()
For Each dr In ds.Tables(0).Rows
punchtime = ds.Tables(0).Rows.Item(0).ToString eventtype = ds.Tables(0).Rows.Item(1).ToString
da = New SqlDataAdapter("INSERT INTO ClockData (DateTime, EventTypeID) VALUES (" + punchtime + "," + eventtype + ")", sqlconn)
da.Update(ds, "CSVData")
Next


Catch ex As Exception

End Try

End Function 

View 3 Replies View Related

How To Save A Dataset To A Sql Server Table?

Sep 21, 2007

Scenario:  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

View 7 Replies View Related

Friendly DataSet Table Names

May 25, 2006

I have a stored procedure that returns a set of tables based on data in a table for a company.  These tables are being used to create drop down lists for criteria selection for the client.  We have a Javascript based control that will make use of these based on the control name.  So... ideally, I would like the stored procedure to return tables named "EmployeeID", "ResourceName", so that I can accurately name the controls when they are being created.  The data that is returned is not static, so, for example, client 1 may see the EmployeeID drop down, while client 2 may not.
I searched and didnt see anyone that was able to accomplish this in this way, but wanted to post something here before I moved forward in a different direction. 
I am thinking that I will likely return an extra table with the field names and their corresponding tables (i.e. "EmployeeID"/"Table0", "ResourceName"/"Table1", etc...)
Thanks,
Josh

View 2 Replies View Related

##table Schema Not Showing In Dataset

Jan 16, 2008

I have a ##table (dynamic openquery to oracle - don't ask) but am unable to see the dataset schema in the dataset window

I've tried putting FMTONLY statements in - no difference
I've also tried changing the query to be text e.g. EXEC proc @param,..... - no difference

Anyone know of a workaround?

View 3 Replies View Related

Changing The Dataset Of A Table Dynamically

May 1, 2007

Hi all,

Is there a way to change the dataset being used by a table dynamically ?

Regards,
Neil

View 2 Replies View Related

SQL Server 2008 :: Populate One Dataset In SSRS Based On Results From Another Dataset Within Same Project?

May 26, 2015

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.

View 0 Replies View Related

Integration Services :: Perform Lookup On Large Dataset Based On A Small Dataset

Oct 1, 2015

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.

View 2 Replies View Related

Reporting Services :: Populate One Dataset In SSRS Based On Results From Another Dataset Within Same Project?

May 27, 2015

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.

View 3 Replies View Related

How Can I Use SQL Reporting Services To Get A Dynamic Dataset From Another Web Service As My Reports Dataset?

May 21, 2007

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

View 2 Replies View Related

DataSet With Multiple SELECT Statements To The Same Table

Apr 29, 2008

Hi,
 OK, trying to return the results from two SQL statements into a DataSet using SqlDataAdapter. The SELECT statements query the same table but are looking for different records based on the date that the records were inserted - the 1st query looks for records fro the current month and the 2nd one looks at the same records but for the previous month. The goal is to be able to do some math within a repeater and get the difference between the two records.
Sounds easy enough and it has worked for me in different variations of the same idea but not this time - here's the code:
Protected Sub buildPartsReport(ByVal varHC)        objConn.Open()        sSQL = "SELECT ap.id,item_model,item_sn,aircraft_id,item_loc=item_type+ ' on ' +(SELECT tnum FROM T_Aircraft WHERE id=aircraft_id),apt.tot_time As endTimes,apt.tot_cycles as endCycles FROM T_Aircraft_Parts ap, T_Aircraft_Parts_Totals apt WHERE ap.id=apt.part_id AND report_date= '" & rD & "' AND item_type LIKE 'engine%';SELECT ap.id,apt.part_id,apt.tot_time as startTimes,apt.tot_cycles as startCycles FROM T_Aircraft_Parts ap, T_Aircraft_Parts_Totals apt WHERE ap.id=apt.part_id AND report_date= '" & oldRD & "' AND item_type LIKE 'engine%'"        Dim objCommand As New SqlDataAdapter(sSQL, objConn)        DS = New DataSet()        objCommand.Fill(DS)        Repeater1.DataSource = DS        Repeater1.DataBind()        DS.Dispose()        objCommand.Dispose()        objConn.Close()    End Sub
Sorry if it wrapped a bit. The "rD" and "oldRD" are the variables for the date ranges (currently set to static numbers for testing). I'm getting the following error when I run this on an ASP.Net page:
System.Web.HttpException: DataBinding: 'System.Data.DataRowView' does not contain a property with the name 'startTimes'.
The code works fine when run via the Query Tool on the SQL server (SQL 2005 Std) though it produces two distinct "tables" which I'm guessing is the problem. I've tried variations on the code including creating a 2nd dataset and then attempting a merge (no joy) and I've tried the ".TableName" route but it complains about trying to add the tablename twice.
Thoughts? I need to get this to work - it is part of a reporting component for an application that I'm developing and I'm stuck. Thanks as always...

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved