Need To Create &&<Query&&> Statement Programmatically
Jun 2, 2006
I am creating a web application that uses a using a web service to get data for my reports. Since the webservice only accepts 1 parameter called "sql" (the sql select statement), I am using the report's query string to get the data.
Here is the data source and dataset info I am using:
DataSource
Name: WebService
Type: XML
Connection string: http://localhost/myWeb/myWebService.asmx
Credentials: No credentials
DataSet
Query tab:
Name: WebService
Data source: WebService
Command type: Text
Query String: <Query><SoapAction>......</SoapAction></Query>
Here is a sample of the <Query> string that I use when I first build the report:
------------------------------------------------------------------------------
<Query>
<SoapAction>http://tempuri.org/GetDataset</SoapAction>
<Method Namespace="http://tempuri.org/" Name="GetDataset">
<Parameters>
<Parameter Name="sql" Type="String">
<DefaultValue>Select * From Customers</DefaultValue>
</Parameter>
</Parameters>
</Method>
<ElementPath IgnoreNamespaces="true">GetDatasetResponse{}/GetDatasetResult{}/diffgram{}/NewDataSet{}/Results</ElementPath>
</Query>
------------------------------------------------------------------------------
When the user selects a report in the web application, they are prompted for information about the sql statement, and then I can rebuild the <Query> xml fragment, substituting the new sql statemet for the default one. for example, the statement "Select * From Reports" would be replaced with "Select * From Customers where LN = 'Smith'".
Then I want to attach that new <Query> statement to the report and run it. How can I set this information in the report? I can't find anything that talks about it, but there must be some way!
Thanks in advance for your help!
View 13 Replies
ADVERTISEMENT
Jun 6, 2007
Hi,
I have metadata that stored my table structure and relationship. I would like to know is it possible to create table relationship programatically? Any sample?
Thank you
View 1 Replies
View Related
Apr 22, 2008
Hi all,
I need to be able to create a DSN through code that connects to SQL Server using SQL Native Client. I have found the following article: http://support.microsoft.com/kb/q184608/. This demonstrates most of what I need, but it does not show how to set the username and password, which is something that I need to do.
Any ideas how I can do this?
Regards,
Stephen.
View 8 Replies
View Related
Jan 26, 2008
hi
my domain is hosted on a remote server
related databases are stored on a seperated DataServer
I have the name of that DataServer
and i need to create new DBs programmatically
I use this statement on my local machine
sqlstat= "CREATE DATABASE dbc_" + dbid + " ON PRIMARY" + "(Name=db_" + dbid + ", filename = '" & Server.MapPath("~/app_data") & "" + dbfilename + "')"
but it will not work , because i dont know the physical path
i cant use Server.MapPath()
How could i do?
thanks
View 6 Replies
View Related
Apr 5, 2006
I wanted to programmatically create SqlServer JobSchedule
The job is to copy data from a particular table in one Database to another table in another DataBaseUser should be able to Schedule/modify Date and Time on which a job is to be executed is to be configured through a UI Screen (WebForm.aspx)
I am using VSStudion2003 ,ASP.net with VB.net with SQL2000 as database .
ANY idea on how to do this ?????
Please help me?
Thank u all in advance
View 1 Replies
View Related
Oct 31, 2006
Hi,
i'm creating a Lookup programmatically. But i can't find out how to assign the ConnectionManager that references the lookup data.
Do you have an example for me?
Many thanks in advance.
Stefan L.
View 4 Replies
View Related
Jul 14, 2007
Hello,
What we'd like to do is programmatically generate and maintain a report model based on how the individual install is configured. I've found some documentation that makes me believe this is possible, but I'm hoping someone can nudge me in the right direction. The ReportingServices2005 class has several model related methods (CreateModel) and I found the .xsd for the semantic model XML file
(http://schemas.microsoft.com/sqlserver/2004/10/semanticmodeling/SemanticModeling.xsd),
but no real documentation on how to put together a Report Model document from scratch. Looking at the files generated from BIDS is somewhat overwhelming and I have no idea where I'd pull most of the information.
Finally, I want to create Report Model(.smdl) file without Report Model Designer.
I want to Implement CreateModel Method to create new model, any source code sample..??
Any help that can be provided would be greatly appreciated. Thanks!
Sandip.
View 4 Replies
View Related
Nov 28, 2007
Hi,
I am working on modifying a VB6 app that dynamically creates DTS packages to copy data from one database to another depending on the selections made in UI. The project currently uses DTSPackage object library and DTSDataDump Scripting object library.
We are in the process of upgrading the server to SQL 2005. I am exploring the possibility of replacing code that generates DTS packages on the fly with SSIS packages.
Is it feasible to do this in VB6 ? I have referred to similar posts which focus mainly on VB.NET or C#.
Any help with white paper or sample code would be appreciated.
Thanks in advance
View 6 Replies
View Related
Apr 20, 2006
Is there a way to do this? It does not appear as part of the standard SSIS API.
Thanks.
View 4 Replies
View Related
Dec 30, 2006
Hi,
I'm working on custom report manager. It manages "report entities" and
"report templates" (actually, RDLs uploaded on the server) and stores
one-to-many relation between them. The task is to store
"MasterEntityID" report parameter in every RDL and keep it up in actual
state whether RDL is being assigned to another entity or new RDL is
being uploaded and assigned. I've covered the first issue with
SetReportParameters() web method, but how should I deal with the second
one? Uploaded RDL may be short of the param, so I have to add it
programmatically while uploading.
Thanks,
Anatoly
View 6 Replies
View Related
Mar 4, 2008
Greetings,
Here's what I need to do. I need to programmatically create offline cubes for multiple customers. Using VS2005 (C#) and SQL Server 2000, I am able to do this using the CREATE GLOBAL CUBE command. However, I need to create cubes from SQL Server 2005 as we have upgraded and the CREATE GLOBAL CUBE command does not work as it did before. I've search and found examples using AMO, but they create a server cube. I already have a server cube.
I need to be able to create filtered offline cubes from the existing server cube. I've also read much on XMLA, but that doesn't do it for me either. The code below creates an empty offline cube. However, is there a way to use this to create a cube with data?
I am creating this in a windows application.
Server server = new Server();
server.Connect(@"Data Source=C:MyLocalCube.cub");
server.Disconnect();
Can anyone help? This is driving me nuts!
View 3 Replies
View Related
Nov 8, 2006
Hi,
I am trying to programmatically in C# create FlatFileSource and OleDB Destination?
I would like your help.... How about column mapping.
I would appreciate your reply.....
View 1 Replies
View Related
Sep 18, 2007
I am following the samples in the online books. I've got an OLEDB connection manager and source component defined. I now want to create an Excel connection manager and destination component (see below). How can I do this?
Dim conMgr As ConnectionManager = package.Connections.Add("OLEDB")
conMgr.ConnectionString = "Provider=SQLOLEDB.1;" "Data Source=FLL-EIS;Initial Catalog=DataLoad;" & _
"Integrated Security=SSPI;"
conMgr.Name = "SSIS Connection Manager for OLE DB"
conMgr.Description = "OLE DB connection to FLL-EIS."
' Create and configure an OLE DB source component.
Dim source As IDTSComponentMetaData90 = dataFlowTask.ComponentMetaDataCollection.New
source.ComponentClassID = "DTSAdapter.OleDbSource"
' Create the design-time instance of the source.
Dim srcDesignTime As CManagedComponentWrapper = source.Instantiate
srcDesignTime.ProvideComponentProperties()
' Assign the connection manager.
source.RuntimeConnectionCollection(0).ConnectionManager = _
DtsConvert.ToConnectionManager90(conMgr)
' Set the custom properties of the source.
srcDesignTime.SetComponentProperty("AccessMode", 2)
srcDesignTime.SetComponentProperty("SqlCommand", Dts.Variables("User::gsQuery").Value.ToString)
' Connect to the data source,
' and then update the metadata for the source.
srcDesignTime.AcquireConnections(Nothing)
srcDesignTime.ReinitializeMetaData()
srcDesignTime.ReleaseConnections()
' Create and configure an OLE DB destination. (This is where I need help, as this code does not work)
Dim destination As IDTSComponentMetaData90 = dataFlowTask.ComponentMetaDataCollection.New
destination.ComponentClassID = "DTSAdapter.ExcelDestination"
' Create the design-time instance of the destination.
Dim destDesignTime As CManagedComponentWrapper = destination.Instantiate
' Where can I find documentation about the various ComponentClassIds and Properties???
destDesignTime.ProvideComponentProperties()
destDesignTime.SetComponentProperty("ExcelFilePath", Dts.Variables("User::gsExcelFile").Value.ToString)
destDesignTime.SetComponentProperty("TableName", Dts.Variables("User::gsSheetName").Value.ToString)
View 6 Replies
View Related
Dec 20, 2006
Hi,
To programmatically invoke a subscription we can use -
exec ReportServer..AddEvent 'TimedSubscription', '575F96C6-A1BD-49FD-9C2F-934FC9658780'
How can we programmatically cause a Manual Report History Snapshot to be created.
I know it can be done using the Webservice. How would we do it using the ReportServer stored procedures. Which SP/SP's can we call. With what parameters.
TIA,
Sam.
View 4 Replies
View Related
May 10, 2006
Hello,
does anyone know of a good example of how to programmatically add a Data Conversion Transformation to a package? I have come so far that I have the component in my dataflow task, but I don't know how to set the properties of it. That is, how to determine the columns that should be converted and to what data type etc.
I (as far as I understand) need a data conversion transformation since I have not managed to create (via the designer) a package that reads data from an AS400 via DB2OLEDB and stores it in a SQL Server 2005 Database. I keep getting the error "string cannot be converted from unicode to non-unicode" (or something like that) and by searching this forum I learned that the data conversion component might do the trick.
I added this conversion to a manually designed package and it solved the error, but I don't know how to re-create this package programmatically. I would really appreciate some help on this.
Also, if someone has managed to import data from AS400 to SQL server 2005 via OLEDB and NOT got the string conversion error, please let me know!
Regards,
Annika
View 3 Replies
View Related
Nov 26, 2007
I need to programmatically create a SQL 2005 maintenance plan at hundreds of customer sites. Each plan will be a little different because of path names, database names, etc., so I can't use one canned plan without modifications. I know that SQL 2005 maintenance plans are XML packages and SQL jobs, so scripting isn't an option. I've seen mention of using the .Net API to create the plans, but I can't find any examples. SMO doesn't seem to have any objects for managing maintenance plans. Can someone point me to an example of using the API to perform this task?
Thanks,
Tom
View 1 Replies
View Related
Mar 12, 2008
Although I can create the local cube, the cube that is created is very large because it brings over all accounts for the employee. In AS2K, it filtered automatically and only returned accounts that had revenue, which is what I'm trying to do using AS2005. I've read some of the responses about using XMLA and AMO, but I am not sure how to use these within the VS2005 windows application that I use to create and distribute the local cubes. Does anyone have a good example of how to use XMLA, AMO or anything else that would allow me to programmatically create and filter offline cubes using C#?
For instance, this code DOES create an empty cube. But how would I go about getting data into this cube?
Server server = new Server();
server.Connect(@"Data Source=c:MyLocalCube.cub");
server.Disconnect();
View 2 Replies
View Related
Jul 16, 2007
Hello,
I'm looking for Programmatically genration of Report Model .smdl file.
Please guide me or suggest some links so that ui can refer same.
Is there any smdl genrator or any classes for the same...?
Thanks in advance.
Sandip
View 3 Replies
View Related
Jul 10, 2012
I need to be able to create & connect to temporary databases programmatically, and NOT using the command-line, within a C# program. How would one go about doing this?
View 7 Replies
View Related
Jan 9, 2015
Ok I have a query "SELECT ColumnNames FROM tbl1" let's say the values returned are "age,sex,race".
Now I want to be able to create an "update" statement like "UPATE tbl2 SET Col2 = age + sex + race" dynamically and execute this UPDATE statement. So, if the next select statement returns "age, sex, race, gender" then the script should create "UPDATE tbl2 SET Col2 = age + sex + race + gender" and execute it.
View 4 Replies
View Related
Jun 20, 2007
I've found example code of accessing an SQLDataSource and even have it working in my own code - an example would be Dim datastuff As DataView = CType(srcSoftwareSelected.Select(DataSourceSelectArguments.Empty), DataView) Dim row As DataRow = datastuff.Table.Rows(0) Dim installtype As Integer = row("InstallMethod") Dim install As String = row("Install").ToString Dim notes As String = row("Notes").ToString The above only works on a single row, of course. If I needed more, I know I can loop it.The query in srcSoftwareSelected is something like "SELECT InstallMethod, Install, Notes FROM Software"My problem lies in trying to access the data in a simliar way when I'm using a SELECT COUNT query. Dim datastuff As DataView = CType(srcSoftwareUsage.Select(DataSourceSelectArguments.Empty), DataView) Dim row As DataRow = datastuff.Table.Rows(0) Dim count As Integer = row("rowcnt") The query here is "SELECT COUNT(*) as rowcnt FROM Software"The variable count is 1 every time I query this, no matter what the actual count is. I know I've got to be accessing the incorrect data member in the 2nd query because a gridview tied to srcSoftwareUsage (the SQLDataSource) always displays the correct value. Where am I going wrong here?
View 6 Replies
View Related
Mar 19, 2007
Does anyone know if it is possible to access the execution plan results programmatically through a stored procedure or .NET assembly? I have the code sample
SET SHOWPLAN_XML ON; query... SET SHOWPLAN_XML OFF;
but it can only be run from the interface. I have tried a couple of solutions including dynamic sql to try to capture the results in a variable or file with no luck.
Does anyone know of a way to programmatically capture this information? We are doing some research with distributed query processing of dynamically generated queries using multiple processing nodes, and it would be helpful to know an estimate of how large the query is before sending it away to be processed.
I have looked at the dm_exec_query_stats view; but it can only be run on a query that has already been executed. I need to know the execution plan before the query is executed. If there is a way to get a query to show up in this view without being executed, then that would work as well.
Thanks -- MT
View 3 Replies
View Related
Jun 13, 2006
In deploying SQL Express, how do I create a new server (INSTANCENAME)programmatically?What would be the best approach to accomplish this.Thank you so much,
View 1 Replies
View Related
Oct 27, 2005
I have a database that is far from being rational! I need to query the tables to get a hierarchy resultset. Here is the structure:levelOne levelOne_A levelTwo levelThree levelFourmy problem is that table levelOne_A and table levelTwo have data that are considered to fall underneath levelOne. However the data is far different from each other so I can not able merge both into one.so the it should look like this:levelOne levelOne_A AND levelTwo levelThree levelFourAny ideas how I can get my hierarchy structure?Thanks for your help!
View 1 Replies
View Related
Jan 20, 2008
Hello, I am trying to autogenerate a new guid in my sql statement. Here is a sample of my code:string comments = "CREATE TABLE comments (domaininfoid UNIQUEIDENTIFIER PRIMARY KEY NOT NULL," +
"comment TEXT NOT NULL)";
How can I make sql auto generate a guid?
View 2 Replies
View Related
Mar 25, 2008
im creating a custom sql statement where my code starts like tt.. its a double query and how do i link the 2nd part to the first part (select * from PO where 1=1)?<script runat="server"> protected void CheckBox1_CheckedChanged(object sender, EventArgs e) { strquery += " and PO between " + textbox1.text + " and " + textbox2.text; } protected void CheckBox2_CheckedChanged(object sender, EventArgs e) { strquery += " and Dlvdate between " + textbox3.text + " and " + textbox4.text; }</script> im a serious newbie with C#
View 3 Replies
View Related
Oct 17, 2013
I'm using SQL Server 2012 for my databases.
Now I'm trying to write a search module. Therefore I have to create a view, with all datas to make it easy for searching. Unfortunately I have a table, which creates a tree (let's say, it's a navigation tree). For the view, I like to have the navigation as a path:
/Main/FirstSub/SecondSub
My navigation tbl looks like this:
Code:
IDParentIDText
10Main
20otherMain
[Code]....
View 4 Replies
View Related
Jun 10, 2007
Hi,
I am new to SQL Server and need your help.
I have a table (old_table) with about 500 columns and each column of type varchar. I have to generate a table (new_table) which has all the columns of the old table but with different data types.
Is there a way I can use the syscolumns to generate a “create table� statement?
Thanks.
View 4 Replies
View Related
Mar 27, 2008
Hi.
I was wondering if I could have a query like this:
CREATE USER 'Firstname Lastname' FOR LOGIN 'DOMAINuser' WITH DEFAULT_SCHEMA=[dbo]
The combination of firstname and lastname does not seem to work.
I have been using:
sp_grantdbaccess 'DOMAINuser', 'Firstname Lastname'
but i cannot specify DEFAULT_SCHEMA with that sp.
Any suggestions?
View 1 Replies
View Related
May 28, 2014
I am trying to create a new column 'COL_4' and, in the same step, create a case statement off of the new col. I know that the code below will not execute. I realize that I could get ride of COL_4 in my code below and concatonate but I need to keep Col_4 in the output.
SELECT
COL_1
,COL_2
,COL_3
,COL_4 = COL_1 + COL_2
,COL_5 = CASE
WHEN COL_1
THEN 'SOMETHING'
END
FROM TABLE_1
;
View 1 Replies
View Related
Apr 13, 2006
I have a following table :
ID ------- Answear ------ Vote
1 ------- 1|2|3|4 ------- 3|5|3|2
2 ------- 1|2|3|4 ------- 2|5|3|1
3 ------- 1|2|3|4 ------- 2|5|7|2
So, I need to create : ,
ID ----- Answear ------ Vote ----- Total ----- Percent
1 ----- 1|2|3|4 ------ 3|5|3|2------ 13-----23.08|38.46|23.08|15.38
2 ----- 1|2|3|4 ------ 2|7|8|1------ 18-----.....
3 ----- 1|2|3|4 ------ 2|5|7|2------ 16-----.....
Any one help me ?
Thanh you very much.
PS :(3/13)*100|(5/13)*100|(3/13)*100|(2/13)*100 after calculate : 23.08 % |38.46 %|23.08 %|15.38 %
View 6 Replies
View Related
Jul 23, 2005
In general, What is the best approach in creating a dynamic updatestored procedure, that can handle recieving varying input paramters andupdate the approporiate columns.
View 6 Replies
View Related