I am trying to eliminate the extra space in columns that have been returned from a DB query...the tables have character lengths of 40, I would like to return just the necessary characters.
Thanks in advance.
Mark.
Did you try rtrim(ltrim(columnname) in the query?
cheers,
Andrew
|||Thanks Andrew,
rtrim(columnname) did it.
Cheers,
Mark.
I am running VS 2005 Professional Edition
Windows XP profession with Service Pack 2
SQL Server 2005 Developer Edition.
WHAT I HAVE DONE:
I have a database running in an instance of SQL Server.
I set this up for merge publication and then set up a SQL Server Mobile Edition Subscription to that publication. After a few oversights I got everything working. The Mobile database replicated just fine. I went back verified all data was there. Can make queries to it.
PROBLEM:
I set up a new dataset to use tables from the SQL Server Mobile database. If I drag one of the tables to the dataset, VS 2005 simply stops responding. It is not using any processor. I click any place on the application and I get the Microsoft Visual Studio Delay Notification saying:
Microsoft Visual Studio is Busy.
Microsoft Visual Studio is waiting for an internal operation to complete. If you regularly encounter this delay during normal usage, please report this problem to Microsoft.
Well... It is more than just a delay. The environment is not using any processor its just sitting here. And I left it running for 2.5 hours... so now this is becoming a big source of pain for me because I need to get that dataset working to finish my business logic. The only option I have is to Kill the process.
Hopefully someone out there can help.
Additional Services running:
IIS (Whatever version comes with Windows XP Pro. I think 5.1)
SQL Server Agent, SQL Server Integration Services, SQL Server Broswer and SQL Server FullTextSearch
UPDATE: I am editing this post with an update.
I noticed that my other tables get added to the dataset just fine. It is when I add one particular table that the entire visual studio simply stops and starts giving the delay notification. I have no idea why this happens... nor do I see any noticeable difference between this table and the rest of them. I went back and made sure that all columns types where directly supported by SQL Mobile Edition and they are.
Kevin,
I've seen this in a couple of circumstances:
1. you had a pre-release version of VS2005 (Whidbey) on your dev machine at some point , then installed the RTM, and something is disconnected in the feature that does the automatic BindingSource generation when you add a new database datasource to your project. this, unfortunately, requires a cleanup and reinstall ofVS2005
2. you are accidentally trying to use a SQL CE 2.0 database as the data source
Darren
|||Darren,
This is a clean install of Visual Studio 2005. No pre release or beta versions have been on this machine.
I created the database a new SQL Server Mobile Edition database and then filled that database via replication of a SQL Server 2005 database.
I am connecting to it via as SQL Server Mobile Edition Data Source.
I did update the first post to add that it is in fact only 1 table that locks up the solution. The other 48 tables load just fine. I don't see any differences in this table from the others except that it has the most columns. It has 52 columns of types int, nvarchar, rowguid, bigint.
Is there anything else you can think of for me to try or check out?
|||Kevin,
Would you be willing to email me a copy of your sdf file and I can try it in my VS2005 install and maybe see what it is about that one table that can't be modeled into a BindingSource? You can contact me through my blog (just Google me).
-Darren Shaffer
|||Darren,
I took the database over to another developers PC who is working on another project. He does not have SQL Server installed but does have Visual Studio 2005 Professional running.
We got the exact same results. All tables could be added to the dataset designer by dragging and dropping but the one table caused the Microsot Delay Notification.
We then decided to try to add the table to the designer by right clicking and saying add table. Then added the table via the query statements. It added just fine that way.
So the problem seems to rear its head only during the Drag -N-Drop method of the Data Set Designer. I am still scratching my head as to why this could be.
I'll will send you a copy of this database to checkout for yourself.
|||Kevin,
Just to give you an update, I did send a copy of your database to Laxmi - we're hoping to get a fix into SP1 of VS2005.
thanks,
Darren
|||Darren,
Thank you for the update.
Can you please eloborate a little as to what the problem was?
|||Kevin,
I got the same behavior you did - you try to add the Vehicles table to a DataSet in VS2005 and it sits forever. I tried repairing the database first, and even tried removing the merge subscription from the SQL Mobile database first. Same behavior. The only thing I think might be involved here is that you have several BIGINT columns in that table and these translate to an INT64 in .NET. I'm wondering if the VSD (Visual Studio for Devices) team anticipated the need to support INT64 in the dataset designer for SQL Mobile data sources.
I'll let you know when I hear back from Laxmi on his investigation of the issue.
Darren
|||Hi Kevin & Darren,
My team mate Mr. Mohit Khullar who was working on this issue has reproduced the problem and found the root cause too. This bug is in the communication of data between desktop designer and device.
Thanks for pointing out the bug. We would try to make this fix into Whidbey SP1.
Thanks,
Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Ev, Microsoft Corporation
|||I wonder if it's possible to be a little more specific...? I found this thread just doing a Google search for "Microsoft Visual Studio .NET 2005 'Delay Notification'" - Since I seem to get this message every now and then and haven't pinned it down to anything specific that causes it. My co-worker just walked up and I showed it to him and asked if he's ever seen it, and he said Yup, lots... Also, it happens on different machines... My home PC, my 2 work PCs, and my VMs.Sometimes it comes back after a bit.. Sometimes (like now) it doesn't! (well, I've waited about 20 minutes.. I figure that's long enough!) ;) Nothing else is open and the CPU is more or less totally idle according to the Task Manager.
It would be nice to know what's causing it, so I can try to avoid certain behaviours.
I have to say that it's Excessively Lame that it says "If you regularly encounter this delay during normal usage please report this prolem to Microsoft." -- But it doesn't say to who, or what email, or what phone number I should report this to! Making me search for something that should have been known to whoever wrote that "Delay Notification" dialog is, as I say.. Lame. :(
Visual Studio 2005 in General ROCKS. This is just one particular very annoying (to me) thing.
Also, the PC is not directly connected to the Internet, so even though when I force-kill it with Task Manager and it asks me to "Please tell Microsoft about this problem." - I can't send it, and it doesn't give me any other way to do so! - That also is another thing about MS products that I find Particularly annoying!
Thanks
- Andrew
PS - Am I EVER glad I selected all and copied before I hit Post! - It just refreshed the screen or something and I lost my entire message! :( Try again... (edit: at least it worked the 2nd time!)
|||
Hi Laxmi,
I thought I would let you know that this problem is NOT reserved just for mobile server connections. This problem is very common for straight Windows Applications. I receive this message VERY often. I thought it was due to my converting a VS2003 solution across to VS2005, but that is not the case either. Although, to clarify, converting a VS2003 solution to VS2005 does seem to cause this message to be raised more often than from a brand new creation of a solution.
I hope this helps identify the bug more specifically.
cheers,
Mark Chimes
|||I think there has been lot of confusion about the context of this thread. There is a particular table schema and when this table is added to DataSet, VS is locked up. There can be other cases of VS locking up which are in no way related to this thread. So, please just dont take this thread as just "VS Locking up" case rather take it as "VS Locking up for a particular SQL Mobile Table Schema".
Thanks,
Laxmi Narsimha Rao ORUGANTI, SQL Server Everywhere Edition, Microsoft Corporation
|||no confusion occuring except when you say the error is restricted to '...a particular table schema'. i am experiencing the exact same problem in a web app i am busy with. i drag a dataset from the toolbox which then creates a default datatable...WITH an adapter ...et voila! i cant say for sure where the designer is trying to link the table adapter to but the result is a dead-end for vs.|||Are you still facing the same problem with Whidbey SP1 or Whidbey SP1 Beta?
Thanks,
Laxmi
I am running VS 2005 Professional Edition
Windows XP profession with Service Pack 2
SQL Server 2005 Developer Edition.
WHAT I HAVE DONE:
I have a database running in an instance of SQL Server.
I set this up for merge publication and then set up a SQL Server Mobile Edition Subscription to that publication. After a few oversights I got everything working. The Mobile database replicated just fine. I went back verified all data was there. Can make queries to it.
PROBLEM:
I set up a new dataset to use tables from the SQL Server Mobile database. If I drag one of the tables to the dataset, VS 2005 simply stops responding. It is not using any processor. I click any place on the application and I get the Microsoft Visual Studio Delay Notification saying:
Microsoft Visual Studio is Busy.
Microsoft Visual Studio is waiting for an internal operation to complete. If you regularly encounter this delay during normal usage, please report this problem to Microsoft.
Well... It is more than just a delay. The environment is not using any processor its just sitting here. And I left it running for 2.5 hours... so now this is becoming a big source of pain for me because I need to get that dataset working to finish my business logic. The only option I have is to Kill the process.
Hopefully someone out there can help.
Additional Services running:
IIS (Whatever version comes with Windows XP Pro. I think 5.1)
SQL Server Agent, SQL Server Integration Services, SQL Server Broswer and SQL Server FullTextSearch
UPDATE: I am editing this post with an update.
I noticed that my other tables get added to the dataset just fine. It is when I add one particular table that the entire visual studio simply stops and starts giving the delay notification. I have no idea why this happens... nor do I see any noticeable difference between this table and the rest of them. I went back and made sure that all columns types where directly supported by SQL Mobile Edition and they are.
Kevin,
I've seen this in a couple of circumstances:
1. you had a pre-release version of VS2005 (Whidbey) on your dev machine at some point , then installed the RTM, and something is disconnected in the feature that does the automatic BindingSource generation when you add a new database datasource to your project. this, unfortunately, requires a cleanup and reinstall ofVS2005
2. you are accidentally trying to use a SQL CE 2.0 database as the data source
Darren
|||Darren,
This is a clean install of Visual Studio 2005. No pre release or beta versions have been on this machine.
I created the database a new SQL Server Mobile Edition database and then filled that database via replication of a SQL Server 2005 database.
I am connecting to it via as SQL Server Mobile Edition Data Source.
I did update the first post to add that it is in fact only 1 table that locks up the solution. The other 48 tables load just fine. I don't see any differences in this table from the others except that it has the most columns. It has 52 columns of types int, nvarchar, rowguid, bigint.
Is there anything else you can think of for me to try or check out?
|||Kevin,
Would you be willing to email me a copy of your sdf file and I can try it in my VS2005 install and maybe see what it is about that one table that can't be modeled into a BindingSource? You can contact me through my blog (just Google me).
-Darren Shaffer
|||Darren,
I took the database over to another developers PC who is working on another project. He does not have SQL Server installed but does have Visual Studio 2005 Professional running.
We got the exact same results. All tables could be added to the dataset designer by dragging and dropping but the one table caused the Microsot Delay Notification.
We then decided to try to add the table to the designer by right clicking and saying add table. Then added the table via the query statements. It added just fine that way.
So the problem seems to rear its head only during the Drag -N-Drop method of the Data Set Designer. I am still scratching my head as to why this could be.
I'll will send you a copy of this database to checkout for yourself.
|||Kevin,
Just to give you an update, I did send a copy of your database to Laxmi - we're hoping to get a fix into SP1 of VS2005.
thanks,
Darren
|||Darren,
Thank you for the update.
Can you please eloborate a little as to what the problem was?
|||Kevin,
I got the same behavior you did - you try to add the Vehicles table to a DataSet in VS2005 and it sits forever. I tried repairing the database first, and even tried removing the merge subscription from the SQL Mobile database first. Same behavior. The only thing I think might be involved here is that you have several BIGINT columns in that table and these translate to an INT64 in .NET. I'm wondering if the VSD (Visual Studio for Devices) team anticipated the need to support INT64 in the dataset designer for SQL Mobile data sources.
I'll let you know when I hear back from Laxmi on his investigation of the issue.
Darren
|||Hi Kevin & Darren,
My team mate Mr. Mohit Khullar who was working on this issue has reproduced the problem and found the root cause too. This bug is in the communication of data between desktop designer and device.
Thanks for pointing out the bug. We would try to make this fix into Whidbey SP1.
Thanks,
Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Ev, Microsoft Corporation
|||I wonder if it's possible to be a little more specific...? I found this thread just doing a Google search for "Microsoft Visual Studio .NET 2005 'Delay Notification'" - Since I seem to get this message every now and then and haven't pinned it down to anything specific that causes it. My co-worker just walked up and I showed it to him and asked if he's ever seen it, and he said Yup, lots... Also, it happens on different machines... My home PC, my 2 work PCs, and my VMs.Sometimes it comes back after a bit.. Sometimes (like now) it doesn't! (well, I've waited about 20 minutes.. I figure that's long enough!) ;) Nothing else is open and the CPU is more or less totally idle according to the Task Manager.
It would be nice to know what's causing it, so I can try to avoid certain behaviours.
I have to say that it's Excessively Lame that it says "If you regularly encounter this delay during normal usage please report this prolem to Microsoft." -- But it doesn't say to who, or what email, or what phone number I should report this to! Making me search for something that should have been known to whoever wrote that "Delay Notification" dialog is, as I say.. Lame. :(
Visual Studio 2005 in General ROCKS. This is just one particular very annoying (to me) thing.
Also, the PC is not directly connected to the Internet, so even though when I force-kill it with Task Manager and it asks me to "Please tell Microsoft about this problem." - I can't send it, and it doesn't give me any other way to do so! - That also is another thing about MS products that I find Particularly annoying!
Thanks
- Andrew
PS - Am I EVER glad I selected all and copied before I hit Post! - It just refreshed the screen or something and I lost my entire message! :( Try again... (edit: at least it worked the 2nd time!)
|||
Hi Laxmi,
I thought I would let you know that this problem is NOT reserved just for mobile server connections. This problem is very common for straight Windows Applications. I receive this message VERY often. I thought it was due to my converting a VS2003 solution across to VS2005, but that is not the case either. Although, to clarify, converting a VS2003 solution to VS2005 does seem to cause this message to be raised more often than from a brand new creation of a solution.
I hope this helps identify the bug more specifically.
cheers,
Mark Chimes
|||I think there has been lot of confusion about the context of this thread. There is a particular table schema and when this table is added to DataSet, VS is locked up. There can be other cases of VS locking up which are in no way related to this thread. So, please just dont take this thread as just "VS Locking up" case rather take it as "VS Locking up for a particular SQL Mobile Table Schema".
Thanks,
Laxmi Narsimha Rao ORUGANTI, SQL Server Everywhere Edition, Microsoft Corporation
|||no confusion occuring except when you say the error is restricted to '...a particular table schema'. i am experiencing the exact same problem in a web app i am busy with. i drag a dataset from the toolbox which then creates a default datatable...WITH an adapter ...et voila! i cant say for sure where the designer is trying to link the table adapter to but the result is a dead-end for vs.|||Are you still facing the same problem with Whidbey SP1 or Whidbey SP1 Beta?
Thanks,
Laxmi
Hello,
I have created SSIS package programmatically, I want to add Lookup transformation,
How can I add column from reference dataset to the transformation?
I have try to add new output column but it gives me an validation error, I write following coed to add new output column to lookup.
IDTSOutputColumn90 outputColumn = this.lookup.OutputCollection[0].OutputColumnCollection.New();
outputColumn.Name = col.Name;
outputColumn.Description = "Staging table output";
outputColumn.TruncationRowDisposition = DTSRowDisposition.RD_FailComponent;
outputColumn.ErrorOrTruncationOperation = "Copy Column";
outputColumn.SetDataTypeProperties(col.DataType, col.Length, col.Precision, col.Scale, col.CodePage);
Please suggest other way to add column from reference dataset to transformation output.
You have to set the reference column name to the appropriate custom property of the output column. You do not have to set any truncation or data type attributes, just get the IDTSDesigntimeComponent90 interface and call SetOutputColumnProperty on it. It should set these attributes for you (assuming your reference metadata is set to the component properly).
It should look like this:
designTimeComponent.SetOutputColumnProperty(outputID, outputColumnID, "CopyFromReferenceColumn", refColumnName);
HTH.
|||Thanks !
Now I have added designtimeComponent property, but I got following error in setting reference table command
"Command text was not set fro the command object" ,before setting designtimeproperties it's working fine
code I used:
this.lookup = this.dataFlow.ComponentMetaDataCollection.New();
// Set component's stock properties.
this.lookup.ComponentClassID = "DTSTransform.Lookup";
this.lookup.Name = "LookupTransform";
this.lookup.Description = "Lookup component";
CManagedComponentWrapper instance = this.lookup.Instantiate();
instance.ProvideComponentProperties();
this.lookup.RuntimeConnectionCollection[0].ConnectionManagerID
= this.package.Connections["OLEDBConnectionLookup"].ID;
this.lookup.RuntimeConnectionCollection[0].ConnectionManager
= DtsConvert.ToConnectionManager90(this.package.Connections["OLEDBConnectionLookup"]);
instance.SetComponentProperty("SqlCommand", "Select id as stg_id1 from [dbo].[Temp2]");
// Attach path between the OLEDB source components Output, and the Sort Component's Input.
this.dataFlow.PathCollection.New().AttachPathAndPropagateNotifications(
this.oledbSource.OutputCollection[0], this.lookup.InputCollection[0]);
instance.AcquireConnections(null);
instance.ReinitializeMetaData();
//Ingore on failuare
this.lookup.OutputCollection[0].ErrorRowDisposition = DTSRowDisposition.RD_IgnoreFailure;
IDTSVirtualInput90 vInput1 = this.lookup.InputCollection[0].GetVirtualInput();
foreach (IDTSVirtualInputColumn90 vColumn in vInput1.VirtualInputColumnCollection)
{
IDTSInputColumn90 col = instance.SetUsageType(this.lookup.InputCollection[0].ID, vInput1, vColumn.LineageID, DTSUsageType.UT_READONLY);
instance.SetInputColumnProperty(this.lookup.InputCollection[0].ID, col.ID, "JoinToReferenceColumn", "stg_id1");
}
IDTSDesigntimeComponent90 designTimeComponent = this.lookup.Instantiate();
designTimeComponent.ProvideComponentProperties();
designTimeComponent.AcquireConnections(null);
designTimeComponent.ReinitializeMetaData();
IDTSOutputColumn90 newColumn = designTimeComponent.InsertOutputColumnAt(this.lookup.OutputCollection[0].ID, 0, "stg_id1", "reference column");
designTimeComponent.SetOutputColumnProperty(this.lookup.OutputCollection[0].ID, newColumn.ID, "CopyFromReferenceColumn", "stg_id1");
designTimeComponent.ReleaseConnections();
instance.ReleaseConnections();
Any suggetions,Can I set component property (Such as SqlCommand) at design time.
|||
Well, this code seems to have many issues. Let's try to fix it iteratively: for the start do not call ProvideComponentProperties twice, it will reset everything. Also, ReinitializeMetadata has to be called only once.
How many upstream columns are coming to your lookup, it seems like you are mapping all of them to stg_id1. Only one can be used for this lookup.
Try to fix these things and then post the cleaned code and the new errors you are getting. Mark the places in the code where the errors are captured.
Hopefully, we will be able to nail it in the next iteration.
|||Thanks Bob !
U mean to say I set all component properties using 'IDTSDesigntimeComponent90' instance not using 'CManagedComponentWrapper' (right?)
Can u explain me how can i set component properties at design time
|||
Hi
Now code is working fine but it gives a Validation Error,''Input column "Name" has datatype which cannot be joined on"
I have to input columns id & name. and I want to join on single column 'id',Now what can I do to remove validation error
// Add the component to the DataFlow task.
this.lookup = this.dataFlow.ComponentMetaDataCollection.New();
// Set component's stock properties.
this.lookup.ComponentClassID = "DTSTransform.Lookup";
this.lookup.Name = "LookupTransform";
this.lookup.Description = "Lookup component";
IDTSDesigntimeComponent90 designTimeComponent = this.lookup.Instantiate();
designTimeComponent.ProvideComponentProperties();
this.lookup.RuntimeConnectionCollection[0].ConnectionManagerID
= this.package.Connections["OLEDBConnectionLookup"].ID;
this.lookup.RuntimeConnectionCollection[0].ConnectionManager
= DtsConvert.ToConnectionManager90(this.package.Connections["OLEDBConnectionLookup"]);
designTimeComponent.SetComponentProperty("SqlCommand", "Select id as stg_id1 from [dbo].[Temp2]");
// Attach path between the OLEDB source components Output, and the Sort Component's Input.
this.dataFlow.PathCollection.New().AttachPathAndPropagateNotifications(
this.oledbSource.OutputCollection[0], this.lookup.InputCollection[0]);
designTimeComponent.AcquireConnections(null);
designTimeComponent.ReinitializeMetaData();
IDTSVirtualInput90 vInput1 = this.lookup.InputCollection[0].GetVirtualInput();
foreach (IDTSVirtualInputColumn90 vColumn in vInput1.VirtualInputColumnCollection)
{
IDTSInputColumn90 col = designTimeComponent.SetUsageType(this.lookup.InputCollection[0].ID, vInput1, vColumn.LineageID, DTSUsageType.UT_READONLY);
}
foreach (IDTSInputColumn90 col inthis.lookup.InputCollection[0].InputColumnCollection)
{
if (col.Name == "id")
designTimeComponent.SetInputColumnProperty(this.lookup.InputCollection[0].ID, col.ID, "JoinToReferenceColumn", "stg_id1");
}
//Ingore on failuare
this.lookup.OutputCollection[0].ErrorRowDisposition = DTSRowDisposition.RD_IgnoreFailure;
IDTSOutputColumn90 newColumn = designTimeComponent.InsertOutputColumnAt(this.lookup.OutputCollection[0].ID, 0, "stg_id1", "reference column");
designTimeComponent.SetOutputColumnProperty(this.lookup.OutputCollection[0].ID, newColumn.ID, "CopyFromReferenceColumn", "stg_id1");
designTimeComponent.ReleaseConnections();
Thanks Bob for solution, can you give me ur e-mail id so I can ask you que. directly if I have
My id omkar.pimplekar@.gmail.com
|||It looks to me that you are selecting every column, as though they all take part in the join, seeforeach (IDTSVirtualInputColumn90 vColumn in vInput1.VirtualInputColumnCollection)
{
IDTSInputColumn90 col = designTimeComponent.SetUsageType(this.lookup.InputCollection[0].ID, vInput1, vColumn.LineageID, DTSUsageType.UT_READONLY);
}
Apply the same filter that you do in the following section, so only select the id column.
|||Yes he is selecting every column, and it is not necessary. You can select only the column you want to join. Replace the two loops with one like this:
Code Snippet
IDTSVirtualInput90 vInput1 = this.lookup.InputCollection[0].GetVirtualInput();
foreach (IDTSVirtualInputColumn90 vColumn in vInput1.VirtualInputColumnCollection)
{
if (vColumn.Name == "id")
{
IDTSInputColumn90 col = designTimeComponent.SetUsageType(this.lookup.InputCollection[0].ID, vInput1, vColumn.LineageID, DTSUsageType.UT_READONLY);
designTimeComponent.SetInputColumnProperty(this.lookup.InputCollection[0].ID, col.ID, "JoinToReferenceColumn", "stg_id1");
}
}
I do not think that is causing your error though. The error is caused by data type mismatch between your "id" and "stg_id1" columns. They need to exactly match in data types.
HTH.
|||Hi,
Thanks Bob,
Now All working fine,again thanks for quick suggetions 
Hi,
I am mapping an entity from SQL 2005 to another entity in another system on SQL 2000. Since the destination system has its own ID generator, I want to keep the generated ID for each row of my table in a column of my table in SQL 2005. The new column is in the dataset now , but I don't know how to update my table to have that column values (The OleDbDestination just insert new items.)
Samy
OLE DB Command transform is used to do updates.
You may want to read the last paragraph on this blog post: http://www.sqljunkies.com/WebLog/ashvinis/archive/2005/02/03/7275.aspx
-Jamie
Hi
I am Creating Click Once Windows Application with Reporting Services 2005.I have created Report and Published on Report Server.In my windows application I am successfully able to view my published report through report viewer control.
Now in my application I am getting a dataset from my custom webservice. I want this dataset data to be added to my report as datasource at runtime on Client Side ,as my report is on Report Server.
waiting for help!!
Thanks in Advance
Pankaj
The report server does not support passing in data sets for reports to run with. You could use the Viewer control in local mode and have the viewer show the report with that data set. Of course by doing this you would loose all of the server add ons (client print for the web control, subscriptions, security, etc).
-Daniel
|||Hi
Thanks for suggession. But will you please tell me which functions/class I have to use to set my new dataset as datasource to my report at client side.as you have mentioned to use reportviewer as localmode.will you please clearly specify.
Thanks
Pankaj
|||Hi Daniel
Or is it possible to get report from reportserver at client side and make it as local report and use it as local report and add datasources to it.?
-Pankaj
|||You can dynamically set the data set using .LocalReport.DataSources.Add. If you look on http://www.gotreportviewer.com/ you will find examples to go by.
You may also want to look into writing a custom Data Processing Extension. By doing this, any report on the server could use your data extension to populate it's data. You can find more information here http://msdn2.microsoft.com/en-us/library/ms154655.aspx.
I'm not sure exactly what you are trying to accomplish so it's hard to say which is the best approach for your problem.
-Daniel
|||The following article may help you to find the most appropriate solution.I have a report services report that I created in local mode that takes data from a dataset and creates a report off of it. I need to add a field into the report that shows some of the data that the user enters in to the textboxes on the web page. Can someone explain to me how to do this or point me to a resource that will show me. Thanks!!
Pass the textbox data as parameters to your report. This entails simply loading the textbox(es) data into a parameter array & passing it to your report. Cheers!
I have a procedure that will return a dataset with an unknown number of columns (the user chooses a date range, and there will be one column per day). Since the columns are not always the same, the report designer doesn't want to help me with this. How can I make this work?
Thanks
Hello my friend,
For performance and ease-of-use reasons, I strongly recommend you take a different approach than using columns in this way, especially for reporting services. Please give details on what you are trying to do (the table structure and the query, etc) and I will try to suggest an alternative to achieving the same result.
Kind regards
Scotty
|||
Currently, I have this table:
CriticalUnitHistory
(
CritcalUnitHistory int (PK),
MarketID int,
UnitLCN int,
CriticalDate datetime,
CriticalReason varchar(50)
)
Every day, I look through a list of computers (each with a UnitLCN that is unique to its city) in different cities (MarketID corresponds to each city), and if its current status satisfies certain criteria, I add a record to this table with the MarketID, UnitLCN, current date and a short description of the criteria that it met to be included on the critical list.
I have been asked to create a report that will take a list of UnitLCNs and MarketIDs, and a date range, and show a table with the UnitLCNs down the left side, the dates across the top, and, if the computer was critical on a certain day, show the CriticalReason in the corresponding cell.
It would look something like this:
MarketID UnitLCN 1/20/2007 1/21/2007 1/22/2007 1/23/2007
1 519 No Contact No Contact
1 234 DL Error DL Error
1 219 GPS Fail GPS Fail
Hope that helps. Thanks for your assistance
|||Hello my friend,
I take it you are having problems generating the data in this way from the original query. Refer to the following url: -
http://www.sqlteam.com/item.asp?ItemID=2955
It is really good. It shows you how to do a cross tab pivot to make data come out in this way. I tested the code myself with my own database tables and it works.
Kind regards
Scotty
