Could any one tell me what the following parameters do under SQL Server 6.5:
(right-click on a server and choose configure, under the server options tab,
click on the parameters button).
-T3632
-m
They were present when I installed MS SQL Server on one system and caused
problems, but were NOT present when I installed on another system.
I can`t find them anywhere in the online documentation.. please help!
Thanks in advance..
Hi all,
From the "How to Call a Parameterized Stored Procedure by Using ADO.NET and Visual Basic.NET" in http://support.microsft.com/kb/308049, I copied the following code to a project "pubsTestProc1.vb" of my VB 2005 Express Windows Application:
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlDbType
Public Class Form1
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim PubsConn As SqlConnection = New SqlConnection("Data Source=.SQLEXPRESS;integrated security=sspi;" & "initial Catalog=pubs;")
Dim testCMD As SqlCommand = New SqlCommand("TestProcedure", PubsConn)
testCMD.CommandType = CommandType.StoredProcedure
Dim RetValue As SqlParameter = testCMD.Parameters.Add("RetValue", SqlDbType.Int)
RetValue.Direction = ParameterDirection.ReturnValue
Dim auIDIN As SqlParameter = testCMD.Parameters.Add("@au_idIN", SqlDbType.VarChar, 11)
auIDIN.Direction = ParameterDirection.Input
Dim NumTitles As SqlParameter = testCMD.Parameters.Add("@numtitlesout", SqlDbType.Int)
NumTitles.Direction = ParameterDirection.Output
auIDIN.Value = "213-46-8915"
PubsConn.Open()
Dim myReader As SqlDataReader = testCMD.ExecuteReader()
Console.WriteLine("Book Titles for this Author:")
Do While myReader.Read
Console.WriteLine("{0}", myReader.GetString(2))
Loop
myReader.Close()
Console.WriteLine("Return Value: " & (RetValue.Value))
Console.WriteLine("Number of Records: " & (NumTitles.Value))
End Sub
End Class
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
The original article uses the code statements in pink for the Console Applcation of VB.NET. I do not know how to print out the output of ("Book Titles for this Author:"), ("{0}", myReader.GetString(2)), ("Return Value: " & (RetValue.Value)) and ("Number of Records: " & (NumTitles.Value)) in the Windows Application Form1 of my VB 2005 Express. Please help and advise.
Thanks in advance,
Scott Chang
I have a SSRS report with four parameters,and I want to be able to enter information for two of the parameters and run the report opposed to all four of them. However, when I select allow blanks and only select the parameters that I want to run the report by, the report come back blank..Essentially, I want to be able to the run report by different parameters without having to enter information for all parameters at the same time.
View 2 Replies View RelatedHi,
I am interested if anyone else has come across performance problems with the SQL Server linked servers to SQL Server. I suspect that the OLE DB Provider that I am using perhaps has some performance issues when passed parameters.
I have set the dynamic paramters option on, and use collation compatible.
Hi,
I have an app in C# that executes a query using SQLCommand and parameters and is taking too much time to execute.
I open a SQLProfiler and this is what I have :
exec sp_executesql N' SELECT TranDateTime ... WHERE CustomerId = @CustomerId',
N'@CustomerId nvarchar(4000)', @CustomerId = N'11111
I ran the same query directly from Query Analyzer and take the same amount of time to execute (about 8 seconds)
I decided to take the parameters out and concatenate the value and it takes less than 2 second to execute.
Here it comes the first question...
Why does using parameters takes way too much time more than not using parameters?
Then, I decided to move the query to a Stored Procedure and it executes in a snap too.
The only problem I have using a SP is that the query can receive more than 1 parameter and up to 5 parameters, which is easy to build in the application but not in the SP
I usually do it something like
(@CustomerId is null or CustomerId = @CustomerId) but it generate a table scan and with a table with a few mills of records is not a good idea to have such scan.
Is there a way to handle "dynamic parameters" in a efficient way???
Hello:
I just recently bumped into this problem and I think I know what's causing it. This is the setup:
Report Parameters: FromDate, ToDate, DivisionalOffice, Manager, SalesRep
dsCalendarEvents Parameters: FromDate.Value, ToDate.Value, DivisionalOffice.Value,
dsDivisions Parameters: N/A
dsManager Parameters: DivisionalOffice.Value
dsSalesRep: DivisionalOffice.Label
When I query the ReportServices WS and scan the parameter dependencies for SalesRep it says there are four dependencies: FromDate, ToDate, DivisionalOffice and Manager!!!
If I change "dsSalesRep" to use "DivisionalOffice.Value" the ReportingServices WS parameter dependency scan returns only one dependency for "SalesRep" parameter!!!( This is the correct behavior )
Has anybody seen this behavior and more importantly, is there a work around?
Regards,
Hello, this is my code: SqlCommand cmd = new SqlCommand("INSERT INTO Users (Username,Password) " +
"VALUES ('@username','@password' ", new SqlConnection(my_ConnectionString));
cmd.Parameters.Add("@username", SqlDbType.NVarChar, 50).Value = txtUsername.Text;
cmd.Parameters.Add("@password", SqlDbType.NVarChar, 50).Value = txtPassword.Text
cmd.Connection.Open();
cmd.ExecuteNonQuery();
cmd.Connection.Close(); But in the database, the row inserted is exactly this:"@username" "@password"I mean, the parameters are not inserted :SPlease, tell me the error in the code...Thank you so much,Carlos.
Hi,
I am new to SQL Server. I want to connet to SQL Server database and fetch records. SQL Server will be present in some other server.
For connecting to SQL Server is it necessary to create DSN. Is it possibel to connect to SQL Server with out DSN
I want following parameters are enough for opening connetion are not:
MyConnObj.Open _
"Provider = sqloledb;" & _
"Data Source=172.16.1.60;" & _
"Initial Catalog=TESTATV;" & _
"User ID=sa;" & _
"Password=p@ssW0rd;"
As per my assumption
Data Source is your server name
Initial Catalog is your database name
please clarify me on this.
thanks
Hi!I need how to pass variables struct witt n fields and n recordsto procedures in transac sql in one bbdd sql server 2000,this parameters are in/out.Thanks.
View 1 Replies View RelatedHi all,
I'm writing a web interface and calling reports from report server. I first used the ReportViewer control. But it seemed to be impressively faster to directly call the report URL and link that directly in a frame.
This all works fine. But I want to dynamically add the parameter values that are used by the report in my URL string. To find out those, I have written a stored procedure:
CREATE PROCEDURE dbo.spIIMGetReportParameters
@sReportname varchar(255)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @xmlParameter xml
SELECT @xmlParameter =
(SELECT Parameter
FROM [catalog]
WHERE [Name] = @sReportname)
SELECT parameter.Name.value('.','nvarchar(50)') AS ParameterName
FROM @xmlParameter.nodes('/Parameters/Parameter/Name') as parameter(Name)
END
Then I loop through the result and add parameters to the Url string.
But sometimes I get parameters that are not needed.
Is there another way to find at runtime which parameters a report expects?
Thanks,
Jan
Hi!
I have a report (*.rdl) in my report server, and this report calls a dataset (by an ObjectDataSource of the reportviewer) that has query parameters. I try to run the report but the following error appears:
An error has occurred during report processing.
Query execution failed for data set 'DataSet3_generar'.
Must declare the scalar variable "@Param1".
I read that, when you create dataset parameters that the report should inherits these parameters automatically. And when you upload the file into the report server, when you select it in the report manager and see its properties, it should appear a link of parameters.
But my report doesn't inherits the dataset parameters automatically, I tried to do it manually using the properties window of visual studio, but It does show the link of parameters in the report manager.. but doesn't link the report parameters with the dataset query parameters.
I don't know if the problem is that I am using SQL server Express, because the Report Builder doesn't work properly (with all its functions)
So, is there a way of solve this using SQL server express? Or I need SQL server 2005?
If there's nothing to do with the server.. then how can I made the link between dataset and report item using visual studio?
Thanks! Any suggestion will help me a lot!
There is a brown bar in Report Server that holds the report parameters. This bar seems to be CLOSED by default. Is there a way to set the default so that the parameter bar is OPEN and the users can see the parameters?
Thanks.
I need to query a linked server (which is Oracle) with some parameters. When I try to use a four part SQL statement, it does not work. But when I try to use OPEENQUERY statement, it works just fine. Problem comes when I need to send some parameters with the OPENQUERY'S 'query' part.
For example, the following statement works just fine:
SELECT *
FROM OPENQUERY(OracleLinked, "SELECT ACCOUNTNUMBER, POSTINGDATE FROM ORA_SERVER.FINANCEENTRY WHERE DATEOFENTRY BETWEEN '2000.01.01' AND '2000.01.31'")
But If I try to use:
DECLARE @DynamicSQL VARCHAR(1000),
@StartDate VARCHAR(10),
@EndDate VARCHAR(10)
SET @StartDate = '2000.01.01'
SET @EndDate = '2000.01.31'
SELECT @DynamicSQL = "SELECT ACCOUNTNUMBER, POSTINGDATE FROM ORA_SERVER.FINANCEENTRY WHERE DATEOFENTRY BETWEEN '" + @StartDate + "' AND '" + @EndDate + "'"
--SELECT @DynamicSQL
SELECT *
FROM OPENQUERY(OracleLinked, @DynamicSQL)
it does not work.
Well, I did some research and found out that OPENQUERY does not accept variables for its arguments. See the link below:(http://msdn.microsoft.com/library/psdk/sql/ts_oa-oz_5xix.htm)
Then is there any way I can accomplish what I want to on the Remote server?
Thanks in advance for your help.
Hi
SQL server 2000 DTS Problem
I'm trying to pass the value of a global variable
as an input parameter in a 'execute sql task' DTS object.
However, SQL refuses to accept the global variable
assigned to the input parameter '?'. When you run the
DTS package the error message 'no value given for one or
more parameters' appears.
When you save / exit the DTS object by clicking OK, and then go
back into it, the 'input Global variables'
parameter field is empty again.
Searched msdn/knowledgebase to no avail - please help ! Thanks
Help much appreciated please !
I have a table :
Person(Id bigint , FirstName nvarchar(50) , LastName nvarchar(50),Address nvarchar(100))
and I have a sp for search data in "Person" table:
CREATE PROCEDURE SelectPerson
@First nvarchar(50) = NULL,
@Last nvarchar(50) = NULL,
@Address nvarchar(100) = NULL
[Code] ....
And there exists 500 record in Person.
I execute SelectPerson by this code:
"
DECLARE@return_value int
EXEC@return_value = [dbo].[SelectPerson ]
@First = 'ام',
@Last = NULL,
@Address= NULL
SELECT'Return Value' = @return_value
GO
"
but result don't has any rows.why?
I will like to configure the Report or the Report server to pass parameters to Inetrnet Explorer when creating the report.
Is there a way to do that ?
Hi, don't know if anyone can help me but I desperately need to roll a report out which relies on an SQL 7.0 datasource.
Current situation - Reporting Services 2000 up and running on an SQL 2000 box - no problems getting reports to run from the native machine but I cannot get the thing to make a hop to our SQL 7.0 box.
I've created three data sources - OLEDB, Native and ODBC. For the OLEDB and Native I am getting a DATABASEPROPERTYEX error - I assume here that reporting services is polling the SQL 7.0 box with an unsupported method.
For ODBC I CANNOT get the parameters to work - I've read all sorts of stuff about using unnamed parameters, so I've put a ? in the query and I'm getting Invalid parameter number and invalid parameter number errors (I suspect it is getting a bit confused, but then so am I).
So I thought I'd try a linked server, sure enough with a fixed parameter I can get through the linked server no problem, but again I can't work out the syntax/series of events needed to use a parameter with this, I'm starting to suspect it can't be done with an Exec statement.
So - first question - is there any way around the DBPROPERTY EX problem so I can use OLEDB/SQL Client connectivity and secondly please can someone explain what exactly has to go in the parameters box when I press ... on the data source box, 'cos it's doing my head in.
Thanks
Rich
Hi,
I was just wondering if something could be explained to me.
I have the following:
1. A table which has fields with data types and lengths / sizes
2. A stored procedure for said table which also declares variables with datatype and lengths/ sizes
3. A function in written in VB .net that uses said stored procudure. The code used to add the parameters to the sql command also requires that i give a data type and size.
How come i need to specify data type and length in three different places? Am i doin it wrong?
Any information is greatly appreciated.
Thanks
Im using SQL Server 2000 with Visual Studio .Net using Visual Basic..
The syntax for Oracle's INSTR function is
instr (string1, string2, [start_position], [nth_appearance])
string1 is the string to search.
string2 is the substring to search for in string1.
start_position is the position in string1 where the search will start. This argument is optional. If omitted, it defaults to 1. The first position in the string is 1. If the start_position is negative, the function counts back start_position number of characters from the end of string1 and then searches towards the beginning of string1.
nth_appearance is the nth appearance of string2. This is optional. If omiited, it defaults to 1.
In SQL Server, we are having CHARINDEX and PATINDEX functions. But they will not accept the fourth paremeter (nth_appearance)
Do anybody know the solution for this ????
i have a url that opens my report with no entries in the parameters i then add &Search=96200 at the end of the URL and still the parameter is blank
View 2 Replies View RelatedIn the t-sql 2012 that is attached message, is a query that always comes up with the same results. It does not make any difference what start and end dates are given to this t-sql. The results are always the same.
DECLARE @StartDateDATETIME
DECLARE @EndDateDATETIME
SET@StartDate= '2013-07-01'
SET@EndDate= '2015-08-01'
; WITH Com_House_1 AS (
[Code] ....
We've developed several reports where you can type in / select variables and run them, affecting their outcomes.
We'd like to change this so if the user clicks on a link from our Intranet, this takes them straight to the report with the appropriate parameter(s) - rather like the "jump to report" function within a report - i.e. the url for that function...
Is this possible?
Steve
Hi,
I installed SQL Server 2005 Enterprise Edition and then executed SP2. So, when I try to move master database to another place, I don't obtain to change the Startup Parameters, because the instance SQL Server (MSSQLSERVER) in SQL Server 2005 Services in the SQL Server Configuration Manager isn't shown. I observed that if not execute the SP2 the instance SQL Server (MSSQLSERVER) is shown.
What it can is happening ?
Somebody knows some thing on this?
Please, somebody could help me ?
Thanks,
Antonio
I've been tasked with creating a stored procedure which will be executed after a user has input one or more parameters into some search fields. So they could enter their 'order_reference' on its own or combine it with 'addressline1' and so on.
What would be the most proficient way of achieving this?
I had initially looked at using IF, TRY ie:
IF @SearchField= 'order_reference'
BEGIN TRY
select data
from mytables
END TRY
However I'm not sure this is the most efficient way to handle this.
Is there a way to combine 2 stored procedures with a different set off parameters.
Basically my 1st stored procedure has the following parameters:
1.@PlanID
2.@FinancialYearID
3.@RangetypeID
My second stored proc has the following:
1.@FinancialYearID
2.@IndicatorID
3.@VersionID
I have researched and so far nothing seems to be working. There is a conflict between the FinancialYearID of the 1st and 2nd stored procs.
My overall result is the combination of the 1st and 2nd storedprocs in 1.
I have created a link server in SQL Server 2005 to connect to Oracle 7.3. The driver in link server is MS-OLEDB. I have created a dynamic procedure in SQL Server 2005 using OpenQuery method to connect to Oracle. When I execute this procedure in SQL this gives me the results I want from Orcale.
In SSRS 2005, I am calling this procedure with the schema name.When I do Refresh,this should automatically list me the Parameters I am using in procedure. It is NOT listing me the AUTOMATIC generation of parameters.
Please help..
Deepak
Hi all,
I need to know how, and if, possible to create a multiple value parameter in SQL Server 2000 Reporting Services. I need this for a client of mine. Any help/tips/etc will be greatly appreciated.
Thank you,
I have a Reporting Services 2005 report which uses cascading parameter multi-select drop-down boxes. The report runs perfect after making initial selections. However, each subsequent parameter selection causes the page to refresh to a blank page until the View Report button is clicked. What I would like to happen (which should be the norm) is for the user to be able to make all necessary selections without the page auto-refreshing after each selection. The user would then click the View Report button to regenerate the report. We are currently using reports which contain up to 5 cascading parameters. This becomes very time-consuming when the page refreshes after each selection.
Has anyone come across a solution or code alternative to this problem??
Any help would be most appreciated.
Thank you.
Tony
Good morning all,
I have a report which measures supplier performance for the previous month. It takes an age to generate so I am trying to cache a copy to a null location first thing in the morning to speed up the process. The problem I'm having is in getting the report to select the first day and last day of the month for the two parameters that the report needs to run.
In BIDS the report runs perfectly when previewed, however, when it is deployed to the report server I get the following error: 'Error during processing of €˜RP2€™ report parameter. (rsReportParameterProcessingError)'
I have used the following two statements for the default values of the parameters, which work in BIDS, so I can't understand why they don't when it's deployed. I'm also sure there is an easier way of doing this, but after about an hour searching yesterday and not finding anything it only took me about half that time to use these statements:
for opening date:
=IIf(Month(Now()) = 1, CDate("01/12/" & CInt(Year(Now())-1)), IIf(Month(Now()) = 2, CDate("01/01/" & Year(Now())), IIf(Month(Now()) = 3, CDate("01/02/" & Year(Now())), IIf(Month(NOw()) = 4, CDate("01/03/" & Year(Now())), IIf(Month(Now()) = 5, CDate("01/04/" & Year(Now())), IIf(Month(Now()) = 6, CDate("01/05/" & Year(Now())), IIf(Month(Now()) = 7, CDate("01/06/" & Year(Now())), IIf(Month(Now()) = 8, CDate("01/07/" & Year(Now())), IIf(Month(Now()) = 9, CDate("01/08/" & Year(Now())), IIf(Month(Now()) = 10, CDate("01/09/" & Year(Now())), IIf(Month(Now()) = 11, Cdate("01/10/" & Year(Now())), IIf(Month(Now()) = 12, CDate("01/11/" & Year(Now())), CDate("01/12/1900")))))))))))))
for closing date (pretty similar really, this is the parameter with which the report server finds an error):
=IIf(Month(Now()) = 1, CDate("31/12/" & CInt(Year(Now())-1)), IIf(Month(Now()) = 2, CDate("31/01/" & Year(Now())), IIf(Month(Now()) = 3, CDate("28/02/" & Year(Now())), IIf(Month(Now()) = 4, CDate("31/03/" & Year(Now())), IIf(Month(Now()) = 5, CDate("30/04/" & Year(Now())), IIf(Month(Now()) = 6, CDate("31/05/" & Year(Now())), IIf(Month(Now()) = 7, CDate("30/06/" & Year(Now())), IIf(Month(Now()) = 8, CDate("31/07/" & Year(Now())), IIf(Month(Now()) = 9, CDate("31/08/" & Year(Now())), IIf(Month(Now()) = 10, CDate("30/09/" & Year(Now())), IIf(Month(Now()) = 11, CDate("31/10/" & Year(Now())), IIf(Month(Now()) = 12, CDate("30/11/" & Year(Now())), CDate("31/12/1900")))))))))))))
The only caviate to using these statements is that it wont recognise when a leap year occurs, other than that, if it would work when deployed to the report server it would work perfectly for the purposes of what we need.
If anyone can see the flaw, or knows of a better and easier way of doing this please let me know.
Humble thanks,
Paul
Hi all,
I am having a problem. I know of hidden parameters in SQL 2005 RS, but I need to have this functionality as well for SQL 2000 RS. I read on the Net that MS SQL 2000 RS Service Pack 1 adds this functionality. I have installed SP4 for SQL 2000, then I installed RS SP1 on my laptop, but I don't see that functionality in VS 2003 Enterprise Architect. I even installed RS SP2 on my other machine, but still no joy. Am I missing something here or is Microsoft lying about this functionality? My client will be upgrading to SQL 2005 sometime in the near future (if they don't change their minds again), but I need to implement this kind of functionality now. I can't wait for them to upgrade to SQL 2005. This parameter I need to hide in the reports is necessary to ensure the reports show data by specific department. I'll implement it in such a way that when they call the report from the web application, I'll pass the departmentID to the report via the query string.
P.S. It is not possible for me to achieve this using JOINs in the queries. The parameter lists themselves depend on this value.
Thanks a lot in advance for this
Ciao,
What is the correct command line parameters to SQLEXPR32.exe that will default the Log On As: properties for the instance to use the Built-in account of Local System & Local Service?
I need it to be set to Local System immediately on install without having to go into the configuration manager. When I run the SQLEXPR32.exe right now I get the Network Service defaulted.
Thanks for any help,
Greg
greg@ram-software.com
Hi
Microsoft confirmed that the error message "The command line parameters are invalid" issue mentioned above is a bug and has been resolved in the next "drop" of SQL Server 2005.
has this error been fixed by microsoft. Cos i still get this error!!!!!!!!!
I am also trying to run the package from command line and when i click on execute the package it runs half way and hangs. What could be the problem????.
But when i run it from my Studio and stored package it works fine!!!
Thanks,
Jas
I need to create a stored procedure that will have about 10-15 queries and take 3 parameters.
the variables will be: @lastmonth, @curryear and @id
@lastmonth should inherit Session variable intlastmonth
@curryear should inherit Session variable intCurrYear
@id should inherit Session id
One example query is SELECT hours FROM table WHERE MONTH ='" + Session("intLastmonth") + "' AND YEAR ='" + Session("intCurrYear") + "' AND [NUMBER] = '" + Session("id")
The rest of the queries will be similar and use all 3 variables as well.
How can I go about this and how will queries be seperated.