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.
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?
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.
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
Hey everyone I'm having trouble finding a way to pass a particular paramater, my main goal of this is to create a custom paging and sorting control for the DataList, but we wont worry about all the un-nessesarry code, here is what I am dealing with... Sub Page_Load()
Dim Conn As SqlConnection Dim Query As String Dim SqlComm As SqlCommand Dim myDataReader As SqlDataReader
' Define connection object Conn = New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
' Define query to retrieve main category values Query = "SELECT [productMainCategory] FROM [User_Products] WHERE [productMainCategory] = @productMainCategory" ' Define command object SqlComm = New SqlCommand(Query, Conn)
' Iterate through records and add to array list While myDataReader.Read() IDList.Add(myDataReader("productMainCategory")) End While
' Close DataReader and connection objects myDataReader.Close() myDataReader = Nothing Conn.Close() Conn = Nothing
' If page has not been posted back, retrieve first page of records If Not Page.IsPostBack Then Paging() End If
End Sub The Entire Point to this bit of code is to open a connection to the database find the category productsMainCategory, count all the fields with the Name 3D Art --> and save that number into an Array List where I will be using it later on in my code......The big old problem here is that it counts every single field in the productsMainCategory, Columb.........I need to figure out how to pass it a parameter so that it only counts my 3D Art --> fields, I have tried using this bit of code but nothing SqlComm.Parameters("@productMainCategory").Value = "3D Art -->"Does anyone have any ideas of how I might go about donig this?Thank You..
I have a stored procedure that I want to pass in a number of months and use it in my where caluse. I need to minus the number of months that as passed in by @TaskMonths. But it is minusing days not months. @TaskMonths intWHERE (tblTasks.Caller = @Caller) AND (tblTasks.DueDate BETWEEN CONVERT(varchar, GETDATE() - @TaskMonths, 101) AND GETDATE()) I apprecaite any help.
I'm trying to pass a comma delimited list of numbers to a parameter to use in an IN of my Where clause. Dim MySqlParamSelected As New SqlParameter("@Selected", SqlDbType.Int) Cmd.Parameters.Add(MySqlParamSelected) MySqlParamSelected.Value = Session("intSelected") WHERE tblSelected.Selected_ID IN (@Selected) It will work if I only pass it one number (e.g. 78), but when I pass it more than one (e.g. 78,79) it fails. Here is the error: Msg 119, Level 15, State 1, Line 4Must pass parameter number 7 and subsequent parameters as '@name = value'. After the form '@name = value' has been used, all subsequent parameters must be passed in the form '@name = value'. Does anyone know how I can correctly pass multiple numbers to use in my IN?
How do i pass a parameter to a stored proc using this command: object SqlHelper.ExecuteScalar (SqlConnection connection, string spName, params object[] parameterValues) let's say that my parameter name is @ID and the value is IDval ..
Does any1 know if when A SQL pass through query is executed in access (to a SQL srv dbase) wether or not access will wait until that call is completed before running other steps (in an macro say). I don't think it does but would appreciate a more definitive answer.
Anybody know if the PASS Conference in Orlando is going to happen or not? The website www.sqlpass.org is in a holding pattern saying that the conference is "POSTPONED," but I would have thought a post about the intention to resched or cancel the gathering would be in order.
I placed a post regarding this issue previously but no success. So I thought I explain everything properly this time in a new post. Thanks
I have created a stored procedure which passes variables to the ssis package and then executes the package. The two variables inside the ssis package are @FileName and @ConnectionPath As you can see from the below stored procedure, xp_cmdshell is used to execute the package. If only the first variable is used in the package and the @connectionPath variable is hardcoded inside the package then package runs fine. Problem is in this particular call as you see below because @ConnectionPath is included.
Error: 2007-08-08 08:46:01.29 Code: 0xC0202009 Source: BaseProfiles2 Connection manager "CounterpartyExposure" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB Provider for ODBC Drivers" Hresult: 0x80004005 Description: "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified". End Error
if only the output is run in the query analyser then the error is:
The identifier that starts with 'Data Source=gblond088sjyMSQL_curves_DEV1, 2025;Initial Catalog=CounterpartyExposure;Provider=SQLNCLI.1;Integrated Security=SSPI' is too long. Maximum length is 128.
I have created this sp to and i cant figure out how to use the top 3 as a paramter. I want to be able to use any # like top1,2,3,4,5 etc. I not sure how to compose the syntax i have my sp below.
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go
-- ============================================= -- Author:<Author,,Name> -- Create date: <Create Date,,> -- Description:<Description,,> -- ============================================= ALTER PROCEDURE [dbo].[sp_moveBackupTables] @serverName varchar(50) --Parameter AS BEGIN
declare @tableName varchar(50) --Variable
--Declare Cursor DECLARE backup_Cursor CURSOR FOR select name from adventureworksdw.dbo.sysobjects where name like 'MyUsers_backup_%' and xtype = 'U' and name not in(select top 3 name from adventureworksdw.dbo.sysobjects where name like 'MyUsers_backup_%' and xtype = 'U' order by name desc)
OPEN backup_Cursor
--Move to initial record in the cursor and set variable(s) to result values FETCH NEXT FROM backup_Cursor INTO @tableName
--Loop through cursor records WHILE @@FETCH_STATUS = 0 BEGIN --dynamically build create table Declare @SQL varchar(2000) Set @SQL = 'CREATE TABLE ' + @serverName + '.dbo.' + @tableName + '( [Id] [numeric](18, 0) NOT NULL, [Field1] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [Field2] [numeric](18, 0) NOT NULL, [Field3] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Field4] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Field5] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, CONSTRAINT [PK_1' + @tableName + '] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY]'
EXEC(@SQL)
--Insert values into new dynamically created table Declare @backupTableRecords varchar(1000)
Set @backupTableRecords = 'Insert into ' + @serverName + '.dbo.' + @tableName + ' SELECT * FROM AdventureWorksDW.dbo.'+ @tableName
exec(@backupTableRecords)
--Drop old table Declare @dropTable varchar(200) set @dropTable = 'drop table adventureworksdw.dbo.' + @tableName
exec(@dropTable)
--Move to the next record in the Cursor and set variable(s) value(s) FETCH NEXT FROM backup_Cursor INTO @tableName END CLOSE backup_Cursor DEALLOCATE backup_Cursor END
HiWe still use SqlServer 2000 and DTSs.My question is: Can I pass a parameter to a DTS and if yes then how ?We would like to plan our screen like this.The user will input a parameter on the screen/form and then the DTS willbe activated using the value inputed.We do have how to work around it if parameters can't be passed but itwould make things a lot easier and straightforward if we could.Thanks !David Greenberg
Hello, I placed a post regarding this issue previously but no success. So I thought I explain everything properly this time in a new post. Thanks
I have created a stored procedure which passes variables to the ssis package and then executes the package. The two variables inside the ssis package are @FileName and @ConnectionPath As you can see from the below stored procedure, xp_cmdshell is used to execute the package. If only the first variable is used in the package and the @connectionPath variable is hardcoded inside the package then package runs fine. Problem is in this particular call as you see below because @ConnectionPath is included.
Error: 2007-08-08 08:46:01.29 Code: 0xC0202009 Source: BaseProfiles2 Connection manager "CounterpartyExposure" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB Provider for ODBC Drivers" Hresult: 0x80004005 Description: "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified". End Error
if only the output is run in the query analyser then the error is:
The identifier that starts with 'Data Source=gblond088sjyMSQL_curves_DEV1, 2025;Initial Catalog=CounterpartyExposure;Provider=SQLNCLI.1;Integrated Security=SSPI' is too long. Maximum length is 128.
I see a lot of posts asking for help with this type of problem but cannot find an answer that works. After 24 hours of trying, using examples from the Microsoft documentation, it's been utterly impossible to pass a parameter to a report via URL.
To make sure I'm not fighting more than one problem at the same time, I've stepped away from the production report I was trying to produce and created a simple report with a parameter named "p1". The only field on this test report is a textbox that displays the value of the parameter "p1". If I set the report up so that the user is queried for a value for "p1", the report works as expected -- the parameter is echoed back on the report.
The same thing can't be done via the URL. If I follow the docs and tack something like &rs:Command=Render&p1=hello onto the URL, I'm prompted for a value and/or told 'p1 is missing a value', depending on the visibility and default options I set for the parameter in the report definition.
I've tried every possible combination of settings I can think of for a parameter in the report definition, and every single one fails. Is this a task that's beyond RS abilities?
Any body can help me on passing parameters in to an MDX query?
sample :
SELECT NON EMPTY { [Measures].[Gross Profit], [Measures].[Sales Amount] } ON COLUMNS, NON EMPTY { ([Employee].[Employee Department].[Employee].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [Adventure Works] where [Employee].[Department Name].[engineering] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
instead of engineering i want to pass a parameter Department. I created another dataset for it
WITH MEMBER [Measures].[ParameterCaption] AS '[Employee].[Department Name].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[Employee].[Department Name].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Employee].[Department Name].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [Employee].[Department Name].ALLMEMBERS ON ROWS FROM [Adventure Works]
Parameter is populating perfectly but how i edit my MDX to accept that parameter ?
hi, I am new to sql server reporting services. When i pass more than 30 parameters ,my report is showing parameters which is not a desired one even though i am making showparameterPrompts property to false.
can any one tell me how to pass more no of parameters to the Report.
I have two reports, one master report & one child report. If I click a patricular value in Territory column, It should take me to the child report where only rows which matches with the territory should be displayed. Since I'm new to SSRS, Kindly some guide me.
Please note that, in the child report I should not get any dropdown boxes for selection.
How can I pass NULL to a parameter, if now entry is made in the textbox? Dim KeywordParam As New SqlParameter("@Keyword", Me.KeyWordText.Text) MyCommand.Parameters.Add(KeywordParam)
I'm trying to pass my SqlDataSource a parameter that is defined in the code-behind file for the same page. I've tried the method below but it does not work. Is there a better way?SubmitForm.ascx page: <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ connection string...... %>" SelectCommand="sp_CourseMaterialShipment_GetCourses" SelectCommandType="StoredProcedure"> <SelectParameters> <asp:Parameter DefaultValue="<% ProgramID %>" Name="programID" Type="Int32" /> </SelectParameters></asp:SqlDataSource> SubmitForm.ascx.vb page:Private ProgramID as string = "25"Public ReadOnly Property ProgramID() As String Get Return _ProgramID End GetEnd Property ThanksJason
Hi, I have a SQL table with a column field type varchar(max). It stores large amounts of text (actually HTML). I am trying to write an insert statement but cant figure out how to pass varchar(max) value to a parameter. I am using VB. The value is being passed from a formview control label. I get the following error: "Conversion from string "@manualtext" to type 'Integer' is not valid." from this line: dbComm.Parameters.Add("manualtext", SqlDbType.NText, "@manualtext") Here is what I have so far: ' Get HTML text file from formview control label Dim t As Label = CType(Me.FormView2.FindControl("manualtextlabel"), Label) ' get record number from querystring Dim param = CType(Request.QueryString("record"), String) ' connect to DB Dim myconnection As SqlConnection myconnection = New SqlConnection() myconnection.ConnectionString = _ ConfigurationManager.ConnectionStrings("MYCMSConnectionString").ConnectionString ' SQL statement Dim strSQL As String = "INSERT INTO dataentry " & _ "(project, manualtext) VALUES (@project, @manaultext)" Dim dbComm As New SqlCommand(strSQL, myconnection) ' create and pass value to parameters dbComm.Parameters.Add("project", SqlDbType.NVarChar, 50, "@project") dbComm.Parameters("project").Value = param.ToString() dbComm.Parameters.Add("manualtext", SqlDbType.NText, "@manualtext") dbComm.Parameters("manualtext").Value = t.Text Try myconnection.Open() dbComm.ExecuteNonQuery() Catch ex As Exception Response.Write(ex.Message) Response.End() Finally If myconnection.State = ConnectionState.Open Then myconnection.Close() End If End Try ' **************** Dim newloc = "viewdetail.aspx?record=" + param Response.Redirect(newloc) End Sub Thanks for the help.
VB ASP.NET 2.0 How do I get the Authenticated UserName passed to a Select Command of a data control ? I'm not clear on how to get the logged in username from User.Identity.Name as a string or how to pass it to the Select Command of a control. I've had some success with SelectParameters / ControlParameter's in the Master Control context, but otherwise I don't understand how to create a Parameter for use with the SQL. I want to show the user data from a database, based on who is logged in . Help, greatly appreciated. Chris
How can I get the UserID to pass to the sql statement? The date is being passed but nothing else Thank you. My code:Public Function GetEmployeeByID(ByVal Today As Date, ByVal UserID As String) As SqlDataReaderDim dtToday As DateTime dtToday = DateTime.Today 'Create connectionDim con As New SqlConnection(_connectionString) 'Create commandDim cmd As SqlCommand = New SqlCommand()With cmd .Connection = con .CommandText = "SELECT UserID FROM ATTTblAttendance WHERE UserID = @UserID And Today = " & dtToday" -->The UserID no showing up here .CommandType = CommandType.Text 'Add Parameters.Parameters.AddWithValue("@UserID", UserID) .Parameters.AddWithValue("@Today", Today) End With 'Return DataReader con.Open()Return cmd.ExecuteReader() End Function
I have know this error but have completely forgotten how to take care of it. How do I pass the integer values to the SQL statement? ERROR: Syntax error converting the nvarchar value 'Label' to a column of data type int. WHERE (LinkInfo.L_State = @State) AND (LinkInfo.CG_ID > @CatIDLow) AND (LinkInfo.CG_ID > @CatIDHigh) <SelectParameters> <asp:ControlParameter ControlID="TextBox1" Name="State" PropertyName="Text" /> <asp:ControlParameter ControlID="Label2" DefaultValue="0" Name="CatIDLow" PropertyName="Text" /> <asp:ControlParameter ControlID="Label3" DefaultValue="1899" Name="CatIDHigh" PropertyName="Text" /></SelectParameters> CODE BEHIND public partial class TopandBottomSelect : System.Web.UI.Page{ int CG_IDlower; int CG_IDupper; protected void Page_Load(object sender, EventArgs e) { } protected void RadioButtonList1_SelectedIndexChanged(object sender, EventArgs e) { if (RadioButtonList1.SelectedIndex == 0) { CG_IDlower = 0; CG_IDupper = 1900; Label1.Text = Convert.ToString(CG_IDlower); Label2.Text = Convert.ToString(CG_IDupper); } else { CG_IDlower = 1899; CG_IDupper = 3000; Label1.Text = Convert.ToString(CG_IDlower); Label2.Text = Convert.ToString(CG_IDupper); } GridView1.DataBind(); }}
Can a table be passed as parameter to a T-SQL procedure or function?One can define a temporary table within a procedure, and the system then manages its lifetime, keeps it separate from other instances executing the same procedure, etc: - CREATE PROCEDURE dbo.name (@Parameter1, @Parameter2) AS BEGIN DECLARE @name TABLE (column list) etcPerfect: but I need to be able to have another [recursive] procedure process this table. How do I make it available to the second procedure? If I write EXEC procedure @Namean error message "Must declare the variable '@Name'" is produced. Within the 2nd procedure, an attempt to name a table within the parameter list results in another error message, "Incorrect syntax near the keyword 'TABLE'". If I can't pass it as argument/parameter, then how do you process a temporary table in another procedure? Do I pass a cursor to it? Or is it quite impossible?Regards, Robert Barnes.
Hi I have a stored procedure which will do some thing like this create proc xxx ( @useralias varchar(32) ) set @useralias = '''' + @useralias + ''''; select * from users where useralias = @useralias EX: Select * from users where useralias = 'Santhosh'. But, when i give the query like this EXec xxx santhosh It gives the error message ...no column by name santhosh or Invalid column. So, i thought i will pass the same from the UI EX: string str = "'" + santhos + "'". But, the probem with this is, it is not giving any results. So, how do i get around with this prob? Thanks! Santhosh
I am trying to do the following. I am capturing the logged in username from Windows Authentication then stripping the domain name using the following codeProtected Sub SqlDataSource1_Selecting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs) Handles SqlDataSource1.Selecting Dim Bareusername e.Command.Parameters("@strusername").Value = My.User.Name Bareusername = My.User.Name.Replace("MyDomain", "") e.Command.Parameters("@LoginName").Value = Bareusername End Sub I use the derived value as the where clause in a select query Select * from Master Where AccountName = @LoginName. This allows a user to only see their own information in DetailsView1. This is working as desired. I have another column in the Master table named ID. This ID is the key that I need to reference to allow users to see and edit data in other tables. So I would like to be able to do something like Select ID from Master where AccountName = @LoginName and be able to pass this value to a Select statement against another table(Awards) for a DetailsView2 on the same page. Alternatively the value that I am trying to query is in the first row of DetailsView1. Is it possible to access the value in that field and pass it in the query for DetailsView2? This would be simpler if possible.I am not sure how to go about this and would appreciate any guidance that you could provide. I have found a lot on the web regarding this based on a selected value but not through the method that I am trying to use. Thanks in advance,Ken
Hi Friends, Is it possible to pass the name of table to the User Defined Function in SQL and use it inside the function. Any help would be appreciated. Fazal.
hi i am using formview i have a date field textbox which selects date from ajax calender now my point is when i make this field blank at that time in my gridview rather then displaying the date field blank it shows me the value 1 Jan 1900 this is the default date of sqlserver which it returns when we leave the field '' now when i pass the value null it automatically takes it as '' in my class file my function is public object checkdate(TextBox txtdate) { if (txtdate.Text == "") { return null; } else { return txtdate.Text; } } in my cs file i wrote object checkemptydate = obj.checkdate(nextvaccinationdate); string values = patientID + ",'" + vaccination + "',convert(varchar,'" + vaccinationdate + "',106)," + checkemptydate + ",'" + remarks + "'"; note that i even tried this too string values = patientID + ",'" + vaccination + "',convert(varchar,'" + vaccinationdate + "',106)," + null + ",'" + remarks + "'"; and string values = patientID + ",'" + vaccination + "',convert(varchar,'" + vaccinationdate + "',106),convert(varchar,'" + checkemptydate + "',106),'" + remarks + "'"; but nothing is working it passes '' not null how could i solve this problem? shweta
Hello, I have two controls on a page. One is for cost and other for date. Both the fields are not mandatory. User can leave them blank and submit the page. The problem is when page is submitted values are passed to a stored procedure but if user hasn't entered anything in these fields then what should i pass to the store procedure. data type in store procedure are money for cost and datetime for date Vinod Suri
