Hi Guys: I am trying to get a top parameter dynamically but it does not seem to work
SELECT TOP
(SELECT COUNT(DISTINCT RegionID) AS COUNT
FROM Sales)
*
FROM Sales
but when I hard code the value it works
SELECT TOP
2
*
FROM Sales
any ideas...Thanks
Is it possible to pass the dataset dynamically in ssrs???
I am using sqlserver express edition at present. so in future it may change. is it possible to change the dataset in future or not???
I have a query to pass .csv file data into sql table using bulkinsert . I used :
CREATE TABLE #CSVTest
(CouponCode VARCHAR(200))
/* Insertion From csv Source To Table */
BULK
INSERT #CSVTest
FROM 'C:Apple.csv'
[Code] ....
This worked very fine to me. Now, the thing is i should pass the filename as parameter and so that what ever file i specified in execute command , query should take that file and push that file data from .csv file to sql table.
I am creating simple report in ssrs and pass one parameter only. It will work perfectly (here user enter the parameter value). but i need that i should select the value in drop down box. i had tried many time and did different ways but I am unable to do it.
First i gave the parameter in my sql query in Data set (like WHERE COUNTRY = @COUNTRY) and i checked the Parameters tab in the data set. Here by default comes the Parameter Name: COUNTRY and Parameter value: [@COUNTRY].
Next i select COUNTRY Parameter in the Report Data Pane. and go to properties Here in General Tab: Name COUNTRY Prompt: COUNTRY, Select Get values from query in available values Tab (and also i tried with Select Get values from query in Default Value Tab) and Select Data set: Data Set1, Value field: COUNTRY and Label Field COUNTRY. And Click Ok
And tried to preview the report, it throwing below error
"An error Occured during local report processing. The definition of the report is invalid. The Report Parameter 'COUNTRY' has a DefaultValue or a ValidValue that depends on the report parameter "COUNTRY". Forward dependencies are not valid.
How can I achieve dropdown list.What i missed? Even i unable to do it Multi valued parameters and Cascading parameters.
Actually i am working on SQL Express 2012 version.
I want to change decimal precision dynamically without rounding value
For example
10.56788 value for 2 decimal precision is 10.56.
10.56788 value for 3 decimal precision is 10.567.
---CASE 1 without dynamic parameter---------
DECLARE @DECIMALVALUE AS tinyint
SELECT CONVERT(DECIMAL(10,2),500000.565356) As Amount
[Code] ....
I am getting error as follows......
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '@DECIMALVALUE'
This decimal precision format value will vary company to company ...
I am designing a package to export staging tables into a flat file.The names of the tables will be: TableAStaging_YYYYMM and TableBStaging_YYYYMM. As you can see the names of the tables will be changing each month.
The flat files will have similar naming: C:MyPathFlatFileTableAStaging__YYYYMM and C:MyPathFlatFileTableAStaging__YYYYMM.I want to run the package as an sql job in two steps, one for each table.I need to dynamically pass the table names and file names (together with the path) to the IS package.
Hi
We already used Oracle Datasatage Server the following Query statement for Source and Lookup.here there is parameter maping in the SQl Statement . How can achive in SSIS the Folowing Querystatment?
Query 1: (source View Query)
SELECT
V_RDP_GOLD_PRICE.GDR_PRODUCT_ID, V_RDP_GOLD_PRICE.ASSET_TYPE, V_RDP_GOLD_PRICE.PREFERENCE_SEQ, V_RDP_GOLD_PRICE.RDP_PRICE_SOURCE, TO_CHAR(V_RDP_GOLD_PRICE.PRICE_DATE_TIME,'YYYY-MM-DD HH24:MI:SS'), TO_CHAR(V_RDP_GOLD_PRICE.REPORT_DATE,'YYYY-MM-DD HH24:MI:SS'), V_RDP_GOLD_PRICE.SOURCE_SYSTEM_ID
FROM
V_RDP_GOLD_PRICE V_RDP_GOLD_PRICE
WHERE
REPORT_DATE = (select max(report_date) from V_RDP_GOLD_PRICE where source_system_id = 'RM' )
Query 2: (look up )
SELECT
GDR_PRODUCT_ID,
TO_CHAR(MAX(PRICE_DATE_TIME),'YYYY-MM-DD HH24:MI:SS') ,
TO_CHAR(REPORT_DATE,'YYYY-MM-DD HH24:MI:SS')
FROM
V_RDP_GOLD_PRICE
where
GDR_PRODUCT_ID = :1 and
report_date = TO_DATE(:2,'YYYY-MM-DD HH24:MI:SS') AND
PRICE_DATE_TIME BETWEEN TO_DATE(:2,'YYYY-MM-DD HH24:MI:SS') - 7) AND TO_DATE(:2,'YYYY-MM-DD HH24:MI:SS')
GROUP BY GDR_PRODUCT_ID, TO_CHAR(REPORT_DATE,'YYYY-MM-DD HH24:MI:SS')
please anyone give the sample control flow and how to pass the parameter?
Thanks & regards
Jeyakumar.M
Hi,I have 30 Table with same structure but it will accept different value at RunTime according to AirCraftType. I wand to create a stored prosedure that will accept TableName, FlightDate, FlightNo. whenever i wnat to execute this code in the database I received error :Must declare the variable '@TableName'create proc InsertDataForAirBus330 @FlightNo varchar(5),@TableName varchr(5),@FlightDate datetime
as
insert into @TableName values(@JourneyDate,@FlightNo,18,42,280,3,7,35)plz suggest me how to solve this pboblem...Jasim...
How to create a sql statement that dynamically matches a database name in a stored proc. Somthing like this:
Select Table_name
From Information_schema.Tables
Where Table_type = 'BASE TABLE' and Objectproperty (Object_id(Table_name), 'IsMsShipped') = 0
and table_name like 'Item%Master'
and (DATABASENAME=????????????????)
Hi....I am fresh to asp.net .... i want to get the Request.QueryString Value Dynamically in SQL Query ... I have attached the coding of two files please gothrough and give the solution..First File : -actyearwise.aspx <%@ Page Language="C#" AutoEventWireup="true" CodeFile="actyearwise.aspx.cs" Inherits="acts_actyearwise" MasterPageFile="~/MasterPage.master" %><asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" runat="server"> <asp:FormView ID="FormView1" AllowPaging="false" DataSourceID="AccessDataSource1" Width="80%" align="center" runat="server"> <ItemTemplate> <tr style="font-family: Arial; font-size: small;"> <td align="left" width="7%"> <a id="href1" href="actcheck.aspx?2007" target="_self">2007</a> </td> <td align="left" width="7%"> <a id="A1" href="actcheck.aspx?2006" target="_self">2006 </a> </td> <td align="left" width="7%"> <a id="A2" href="actcheck.aspx?2005" target="_self">2005 </a> </td> <td align="left" width="7%"> <a id="A3" href="actcheck.aspx?2004" target="_self">2004 </a> </td> </tr> </ItemTemplate> </asp:FormView> <asp:SqlDataSource ID="AccessDataSource1" runat="server" OnSelecting="AccessDataSource1_Selecting" ConnectionString="Data Source=.SQLEXPRESS;AttachDbFilename=C:InetpubwwwrooteLawApp_DatasampleDB.mdf;Integrated Security=True;User Instance=True" ProviderName="System.Data.SqlClient" SelectCommand="SELECT * FROM actindex "></asp:SqlDataSource></asp:Content><asp:Content ContentPlaceHolderID="FooterPlaceHolder1" ID="Content2" runat="server"></asp:Content> actyearwise.aspx.csusing System;using System.Data;using System.Configuration;using System.Collections;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Web.UI.HtmlControls;public partial class acts_actyearwise : System.Web.UI.Page{ protected void Page_Load(object sender, EventArgs e) { } protected void AccessDataSource1_Selecting(object sender, SqlDataSourceSelectingEventArgs e) { }}// This page works Properly... In the actyearwise.aspx ..just some years with link... i have entered ...the href page is actcheck.aspx(below).. In this page i want the years dynamically.. please go throughSecond file : -actcheck. aspx <%@ Page Language="C#" AutoEventWireup="true" CodeFile="actcheck.aspx.cs" Inherits="acts_actfiles_actcheck" MasterPageFile="~/MasterPage.master" %><asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" runat="server"> <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" CellPadding="3" DataKeyNames="actno" DataSourceID="AccessDataSource1" Width="100%" AllowSorting="true"> <Columns> <asp:BoundField HeaderText="actno" Visible="False"/> <asp:TemplateField SortExpression="actno"> <ItemTemplate> <table width="80%" align="center"> <tr> <td align="left" width="3%" colspan="3"> <%# Eval("actno") %> </td> <td align="left" width="75%"> <a id="href1" href="actshorttitle.aspx?<%# Eval("actid") %>" target="_self"> <%# Eval("actname") %> </a> </td> </tr> </table> </ItemTemplate> </asp:TemplateField> </Columns> </asp:GridView> <asp:SqlDataSource ID="AccessDataSource1" runat="server" OnSelecting="AccessDataSource1_Selecting" ConnectionString="Data Source=.SQLEXPRESS;AttachDbFilename=C:InetpubwwwrooteLawApp_DatasampleDB.mdf;Integrated Security=True;User Instance=True" ProviderName="System.Data.SqlClient" SelectCommand="SELECT * FROM actindex where actyear = 'check' "> </asp:SqlDataSource></asp:Content><asp:Content ContentPlaceHolderID="FooterPlaceHolder1" ID="Content2" runat="server"></asp:Content> actcheck. aspx.csusing System;using System.Data;using System.Configuration;using System.Collections;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Web.UI.HtmlControls;public partial class acts_actfiles_actcheck : System.Web.UI.Page{ protected void Page_Load(object sender, EventArgs e) { String check = Request.QueryString.ToString(); } protected void AccessDataSource1_Selecting(object sender, SqlDataSourceSelectingEventArgs e) { }} The text i had bold and italics in the actcheck.aspx... is the problem..... From the actyearwise.aspx .. i was passing the actyear value ..... In actcheck,aspx .. i want to get it dynamically in SQL Query...... Please give the solution .. I am using the SQL database.Table name actindexfields nameactnoactnameactidactyear In the actyear field i have different years .... Thanks in advance
View 5 Replies View RelatedI'm trying to dynamically set the TOP value in a query, but it doesn't work. Any other way it can be done?
Doesn't work:
declare @sometopvalue int
set @sometopvalue = 100
select top @sometopvalue
field1,
field2,
field3
from table1 ORDER BY BRDCST_DT_TM field1 desc
I have a query that pulls many values and I want to loop through each value dynamically using a query.
SELECT COL1 FROM TABLE1
Results:
A
B
C
D
I want those results to loop and run this:
SELECT * FROM TABLE2 WHERE COL2 = A
SELECT * FROM TABLE2 WHERE COL2 = B
SELECT * FROM TABLE2 WHERE COL2 = C
SELECT * FROM TABLE2 WHERE COL2 = D
Hello,
I am trying to create a sp dynamically. However each time, I run my sp, which creates the dynamic SQL for the sp, then it automatically changes:
CREATE PROCEDURE .....
to
Alter PROCEDURE ....
Then I am obvoiusly getting the error msg:
Invalid object name.
How can I get rid of that?
HI chaps
my scenario is that, i have a table in which i have column such as col01,col02,col03....col31
i want to retrieve the records related to that column by specifying the name dynamically (by using loop) as fist three character are same ('col??') and i have to do integer increment in last two character (???01...???31). Is it possible that I can use variable for column name?, if yes how? or is there any other way to achieve this task ?
waiting for your reply
regards
Hi,
Using a ssis package I have several tasks.
A variable is also created called FileName.
There is a flat file connection called InputFileName.
initially when I designed the package, I hardcoded the pass of one of the .csv files into the path of the flatfile connection.
There is a dataflow which has flatfile source and oledb destination. These two basically pickup the file and import into the database using mapping.
So therefore the package works and data gets imported into the database.
Then I used the variable to make the filename dynamic. So I removed the hardcoded connectionstring in the InputFileName.
Is this the right thing to do to make the package dynamic? I would like to pass in any .csv filename and so the package runs.
My question is now that the connectionstring is deleted but instead placed an expression for the filename, then how does the system know which file to use?
Thanks
Hi, there;
I am doing a SSIS project, my target is:
I have a source folder which have some *.dbf files (the number of
files will change as time goes on) exported from another application. I want my SSIS package to load data from these dbf
files in the source folder to my SQL2005 database no mather how many
dbf files will be.If the destination table already exists in the database,
the logic logic will do update (delete and import again), if not, create a new table on th fly and then
pump the data.
What I did here is that I have a foreach loop container which contains Script
task, Execute SQL task and a data flow. The Foreach container now can
connect to the *.dbf files successfully, the Script task can read
schema information from each dbf file and create script "Create table ...."
successfully and Execute SQL task can create table in my new empty
database successfully! But I have problem in the data flow task: my OLEDB Source points to the dbf file folder and set Data Access mode to "Table name or view name variable",but when I set Variable Name to a package level variable ("tableName"), I get error: Error at Data Flow Task_Pumping data from Admar [OLE DB Source [2737]]: A destination table name has not been provided.
Exception from HRESULT: 0xC0202042 (Microsoft.SqlServer.DTSPipelineWrap)
Does anybody know what to do to create table and pumping data on the fly? (Using variable as table name)
Big Thanks here.
I need an sql statement that dynamically matches a database name in a stored proc. Here is my attempt
Select Table_name
From Information_schema.Tables
Where Table_type = 'BASE TABLE' and Objectproperty (Object_id(Table_name), 'IsMsShipped') = 0
and table_name like 'Item%'
and (DATABASENAME=????????????????)
Can someone help me out?
Thanks.
I have an FTP Task where I have to set the server password at run-time. I have the ftp connection manager set up. What I normally do is configure the ftp server connection manager using a dtsConfig file but I can't do that in this case. I won't know the serverpassword until I get a parameter from the user.
View 3 Replies View Related
I have CSV file as source for SSIS package every time the filename will be changing like trd_1990M1_1990M12.csv
trd_1991M1_1991M12.csv , trd_1992M1_1992M12.csv so it will vary as per user selection . i need to run the same SSIS package to execute the package whichever the file name.
Please let me know the solution for that how to pass the file name dynamically to SSIS package.
Apologies in advance for the newbie question. I'm new to Integration Services and am trying to build my first project.
I want to dynamically create folders based on the results of a stored procedure. The resultset is limited to one column but can contain multiple rows. (It returns a list of 8-character strings representing dates.) This returned value needs to be combined with the path where the new folders should be created.
Can someone point me in the right direction? I've run the stored procedure and put the resultset into a RecordSet Destination that uses a variable of type Object. (At least, I think I've done it correctly.) But I have not been able to figure out how to access this data to actually create the folders.
Thanks for the help,
Chris
Is it possible from sql to use SAS as an object? And if so how?
I was recently tasked with Data Profiling and sitting next to me is a SAS developer, who just has to run a couple of functions, to do what has taken me days (and I haven't finished!). If I could call out to SAS and run the SAS functions it would be great. I have looked on the net and can see SAS calling out to SQL but not the other way round. Technically I'm probably not up to this but I don't want this to hold me back... so any help would be great.
okay..
Here is my first post from pass.
I am here on sunday getting ready for the first of 5 days of nothing but SQL Server
Every time i come to this thing i still get excited about the whole community thing.
who else from dbforums is going to be here? i would be interesting to put faces with those names.
in any event i will post interesting tidbits from the event if any come to mind. starting with the microsoft givaway of a copy of std edtion 2005 (when released) to any attendee. you get a coupon and you register a pin on a web site and they will send you a copy upon release. its a nice perk i guess, but msdn and the CTPs kind of take the savoir faire out of it.
oh well.
remember to post your name if you are attending.
is it possible to do:(A)declare @numberofitems Int@numberofitems = select max(itemorder)from store, department, etc.and pass the @numberofitems to a #tempStore table, like:(B)(store, department, @numberofitems,...)I got itemorder but not the number of items in each departmentAlex--Sent by 3 from yahoo part from comThis is a spam protected message. Please answer with reference header.Posted via http://www.usenet-replayer.com/cgi/content/new
View 2 Replies View RelatedHello
Following is my code which is not working.
<asp:SqlDataSource ID=events runat=serverSelectCommand="SELECT * FROM TBL_EVENT WHERE SECTION_ID=<%= Session("ID")%>"ConnectionString = "<%$ appSettings:SQLConnectionString %>"</asp:SqlDataSource>
I want to pass the value of Session("ID") into that query. How can I do that?
i have to store some data on a remote sever(MS SQL SERVER2000). The scenario is like 1. The web application runs on a local machine. User (who inputs) uses through LAN.2. The Input should be stored in the remote server. if the remote connection is ok. otherwise it should be saved in local server's database(MS SQL 2000).3. In the application's web.config there is a connection string pointing to the remote server and another one (alternating one) points the local server's database. in scenario like this i first to tested the remote connection. if it is not ok then i initialize the local server's connection like thisprivate MyConnection() { try { connectionSql = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnForRemote"].ToString()); connectionSql.Open(); } catch (Exception ex) { connectionSql = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnForLocal"].ToString()); } finally { connectionSql.Close(); } connectionSql2 = new SqlConnection(ConfigurationManager.ConnectionStrings["Temp"].ToString()); }My problem is when the remote connection is lost it takes almost 1 minute to store in local database. how can i make it more time efficient. Thanks....
View 5 Replies View RelatedHi, how I do create SqlDataSource in the code-behind,setting Select SQL along with its parameters and, finally automically generating Del, Update, Insert SQL.
Is there a way to achieve this goal as it would in VS designer?
Thanks,
Ricky.
Hi! I am using VS 2003 (v 1.1)
I need to generate tables with the values from the Backend (SQLServer 2000) database in C#.Net.
How can i create the tables, tablerows, cells, etc. from the codebehind page of C#. I am having a very little knowledge about dynamic generation.
Give me the complete code with can example (if possible)
Thanks & Regrads
Jai Shankar
I am trying to filter some results using the FilterExpression Property of the SqlDataSource. I have multiple drop down lists with different filtering options. I am trying to change the filter that is applied to a gridview.
Something like this example... http://blogs.vbcity.com/mcintyre/archive/2006/08/17.aspx
Here is some of my code..Private Sub ApplyFilter()
Dim _filterExpression As String = ""
If (Not DropDownList1.SelectedIndex = 0) And (DropDownList2.SelectedIndex = 0) And (DropDownList3.SelectedIndex = 0) Then
_filterExpression = "CategoryName = '{0}'"
End If Me.SqlDataSource1.FilterExpression = _filterExpression
End Sub
Protected Sub DropDownList1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles DropDownList1.SelectedIndexChanged
ApplyFilter()
End Sub
But this doesn't seem to work. The FilterExpression doesn't keep the value. I am looking for a way to dynamically change the FilterExpression. Any Ideas or Suggestions?? Thanks.
I have several Sql Tables, each containing more than 100 fields, on which I need to perform display operation through GridView, Insert and Update through an array of dynamically created and databound TextBoxes.
I would like to use a single page to manage all Sql Tables on the basis of the name of the specific one invoked by the user.
In order to achieve that, I need to dynamically configure the SqlDataSource Web Control present on the page for each Sql Table. In particular I need to generate at runtime the InsertCommand and the UpdateCommand.
On how to do it I have no idea.
Federico
I want to choose a column dynamically depending on a session variable.
In other words, each customer has a different price level. so I need to choose the column that corresponds to thier price level in thier customer record
Any help is appreciated.
below is my futile attempt
SelectCommand="SELECT cove.CATALOG.ITEM_NO,cove.catalog."& <%#= session("pl") %> & ", cove.CATALOG.DESCRIPTION, cove.CATALOG.DESCRIPTION2, cove.CATALOG.DESCRIPTION3, cove.PRODLINE.DESCRIPTION AS Expr1, cove.CATALOG.GRP, cove.COMPANY.PRL, cove.COMPANY.COMPANY FROM cove.CATALOG INNER JOIN cove.COMPANY ON cove.CATALOG.WEB_STATUS <>'[I]' cove.COMPANY.I LEFT OUTER JOIN cove.PRODLINE ON cove.CATALOG.PRODLINE = cove.PRODLINE.CODE WHERE (cove.COMPANY.COMPANY = @company) AND (cove.CATALOG.GRP <> '[ACC]')">
<SelectParameters>
Hi - hope someone can help. We're creating a ASP.NET C# site that allows a user to connect to any of our 100+ SQL Servers and query a database that sits on every server. I'm trying to work out (and failing) how we can set the 'Data Source' in a connection string dynamically using the server name selected by the user in a drop down box. Any help appreciated, thanks in advance,
View 2 Replies View RelatedI have to write a large data migration script to move data from one SQL Server database to another. Is there any way to dynamically specify the server and database name? I would like to do something like the following, (but this does not seem to work):
Delete From [@ServerName].[@ImportToDatabase].[MyTable]
Any help appreciated
Hey all. I'm trying to create a stored proc that will update a variable column, depending on the parameter I pass it. Here's the stored proc:
CREATE PROCEDURE VoteStoredProc
(
@PlayerID int,
@VoteID int,
@BootNumber nvarchar(50)
)
AS
DECLARE @SQLStatement varchar(255)
SET @SQLStatement = 'UPDATE myTable SET '+ @BootNumber+'='+ @VoteID + ' WHERE (PlayerID = '+ @PlayerID +')'
EXEC(@SQLStatement)
GO
I get the following error:
Syntax error converting the nvarchar value 'UPDATE myTable SET Boot3=' to a column of data type int
The update statement is good, because if I use the stored proc below (hard-coded the column), it works fine.
CREATE PROCEDURE VoteStoredProc
(
@PlayerID int,
@VoteID int,
@BootNumber nvarchar(50)
)
AS
UPDATE
myTable
SET
Boot3 = @VoteID
WHERE
PlayerID = @PlayerID
GO
Is there a way to dynamically choose a column/field to select from? Or is my syntax incorrect..?
Thanks!