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

Create Relationship Programmatically

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

Programmatically Create SQLNCLI DSN

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

How To Create A New DB On A Remote Server Programmatically?

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

Programmatically Create SqlServer JobSchedule

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

Programmatically Create A Lookup Transform

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

Programmatically Create A Report Model

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

Create SSIS Package Programmatically In VB6

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

Programmatically Create Data Sources

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

How To Create Report Parameter Programmatically?

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

Programmatically Changing A DataReader's SQL Statement

Oct 26, 2005

Hi,

View 7 Replies View Related

Programmatically Create AS2005 Local Cube (C#)

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

Programmatically Create FlatFileSource And OleDB Destination...........

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

How To Create An Excel Connection Manager Programmatically ?

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

Programmatically Create A Report History Snapshot

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

Programmatically Create A Data Conversion Transformation

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

How To Programmatically Create SQL 2005 Maintenance Plan

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

Create &&amp; Filter AS2005 Local Cube Programmatically

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

Programmatically Create A Report Model SMDL File.

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

Setup And Upgrade :: LocalDB - Create And Connect To Database Programmatically

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

SQL Server 2012 :: Create Dynamic Update Statement Based On Return Values In Select Statement

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

Programmatically Accessing An SQLDataSource With A SELECT COUNT(*) Query.

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

Accessing Query Execution Plan Results Programmatically

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

How To Create SQL Server Express Server (INSTANCENAME) Programmatically?

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

Help Me Create A SQL Statement

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

Create A Uniqueidentifier In SQL Statement

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

How To Create A Custom SQL Statement.

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

Create A Path In Statement?

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

Generating Create Statement

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

Name In CREATE USER Statement?

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

Create New Column In Select Statement

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

Help SQL Statement Create Calculate Percent ?

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

Best Way To Create Dynamic Update Statement

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







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