Calling Parameterised Views From VB
Jul 20, 2005
I'd really appreciate your help creating a parameterised view in SQL
Server, which I can then invoke from VB5. I'm using DAO and ODBC to
connect to SQL Server 2000.
I can open Tables and Views, but can't find how to create or use a
"query" parameter. If I use the "?" (in the SQL Enterprise manager
View design mode), the syntax checker verifies the query ok, but I
then get the message "Parameters cannot be used in this query type".
It doesn't like the %var% terminology at all here.
I am sure I could build the full query in VB and save it to the Server
"on-the-fly", but this partly defeats the object, doesn't it?
I could really do with a couple of pointers: (a) how to define the
view/query in Ent Manager and (b) how to invoke it from VB, supplying
the parameter.
Thanks ever so in anticipation.
Phil
View 9 Replies
ADVERTISEMENT
Jun 15, 2007
I am using SqlServer2005 and asp.net 2005. I have a large database for which I have to provide reports for it. I need a report that user will specify a date, and the report will be run, from this month & year and I will calculate the first day of the month and the last day of the month and these will serve as the input parameters for a long SELECT query.For my previous reports i've used views . But for this functionality i need a parameterized view, which i cant create.Please help me how to create a parameterized view.... Thanks in Advance...
View 3 Replies
View Related
Aug 15, 2005
HiNew to sql server. using beta2 2005. What is the way to implementparameterised queries? I need the queries to be updatable as these wouldwork as recordsets that vb.net forms will be bound to for data entry.ThanksRegards
View 2 Replies
View Related
Oct 6, 2006
Dear ppl,
I am using Merge Replication between SQL Server 2005 (Publisher) and Pocket PC (Subscriber). I have a .NET compact framework appliction on the Pocket PC that replicates data from the server.
I am using SqlCeReplication class on the Pocket PC application to synchronise the data. By default, when i call the Syncrhonise() method, it pulls all the data from the server. What I want is to pass a parameter from the Pocket PC and filter the data based on that paramter. E.g. from the Employee table, I want only those Employee that belongs to a CompanyID that I pass as a parameter.
Is there a way to do this, so that i can pass parameters from my PDA application (Windows Mobile), and make the Filters specified in the Publication to use that parameter to filter out the rows.
Regards
Nabeel Farid
View 1 Replies
View Related
Jul 20, 2005
Hey...Why do I sometimes experience low performance when I use a parameter insteadof an exact value?For example the following performs very bad:declare @TypeID integerselect @TypeID = 10select ID from t_Table1 t1, t_Table2 t2 wheret_t1.ID = t_t2.FID andt2.Type = @TypeIDbut this query performs ok:select ID from t_Table1 t1, t_Table2 t2 wheret_t1.ID = t_t2.FID andt2.Type = 10Jakob
View 1 Replies
View Related
Apr 9, 2007
Hi,
I want a matrix report which displays Sales Data or Discount Data (Column) Vs Year (Row). I want to have a parameter which displays a text as 'Sales', 'Discount'.
When I Select 'Sales', the report should display 'Sales Amount' Vs 'Year' and when I select 'Discount', the report should display 'Discount Data' Vs Year. I am getting all the data from OLAP Cube.
Any help is highly appreciated.
Thanks,
S Suresh
View 1 Replies
View Related
Sep 26, 2007
Hi, i have a text file destination which i am trying to output data to via an SSIS package. I cannot seem to be able to set up the package so that i execute a parameterised stored procedure and insert the data into a text file. I have set up the following
connections:
a. ADO.net connection to the SQL 2005 database
b. Flat file destination, which links to an empty file on the network : D:GCDReportsfeedsgmr_p201.fs_20070724
ANy quick help would be greatly appreciated.
David
View 3 Replies
View Related
Oct 18, 2006
I want to do an update query like the following:UPDATE tblUserDetails SET DeploymentNameID = 102 WHERE (EmployeeNumber = @selectedusersparam)Is there some simple way to add the @selectedusersparam as value1,value2,value3 etc. or do I have to input it with this type of syntax:UPDATE dbo_tblUserDetails SET dbo_tblUserDetails.DeploymentNameID = 102WHERE (((dbo_tblUserDetails.EmployeeNumber)=value1 Or (dbo_tblUserDetails.EmployeeNumber)=value2));Help appreciated.Many thanks.
View 5 Replies
View Related
Jan 22, 2007
Hi,
We have an issue with our replication configuration when viewed through replication monitor. Parameterised Filters and joined filters don't appear in the gui. However, when we script the publication all the filters are present.
This issue only seems to occur when we have a remote distributor.
I should also point out that we have a merge push topology that uses a custom RMO synchronisation component on a separate server to either the publisher or the distributor. Also all the databases in the topology are called the same name. This has caused us other issues relating to this topology in particular so I raise it here as well although I don't expect it to be the case in this instance.
Any help would be greatly appreciated in clarifying this matter.
View 1 Replies
View Related
Apr 3, 2006
Fellow database developers,I would like to draw on your experience with views. I have a databasethat includes many views. Sometimes, views contains other views, andthose views in turn may contain views. In fact, I have some views inmy database that are a product of nested views of up to 6 levels deep!The reason we did this was.1. Object-oriented in nature. Makes it easy to work with them.2. Changing an underlying view (adding new fields, removing etc),automatically the higher up views inherit this new information. Thismake maintenance very easy.3. These nested views are only ever used for the reporting side of ourapplication, not for the day-to-day database use by the application.We use Crystal Reports and Crystal is smart enough (can't believe Ijust said that about Crystal) to only pull back the fields that arebeing accessed by the report. In other words, Crystal will issue aSelect field1, field2, field3 from ReportingView Where .... eventhough "ReportingView" contains a long list of fields.Problems I can see.1. Parent views generally use "Select * From childview". This meansthat we have to execute a "sp_refreshview" command against all viewswhenever child views are altered.2. Parent views return a lot of information that isn't necessarilyused.3. Makes it harder to track down exactly where the information iscoming from. You have to drill right through to the child view to seethe raw table joins etc.Does anyone have any comments on this database design? I would love tohear your opinions and tales from the trenches.Best regards,Rod.
View 15 Replies
View Related
Sep 12, 2007
I have an issue using parameterised reports connecting to Oracle using "ODBC" and "Microsoft OLE DB Provider for Oracle" using parameteried reports. The following error is generated "ORA-01008 not all variables bound (Microsoft OLE DB Provider for Oracle)" and a similiar one for ODBC. It works fine for simple reports. Do these 2 drivers have issues passing parameters for a remote Oracle query?
Thanks.
View 4 Replies
View Related
Sep 6, 2007
Which is more efficient? One large view that joins >=10 tables, or a few smaller views that join only the tables needed for individual pages?
View 1 Replies
View Related
Jun 28, 2007
Hello.
Newbie here. I've only been using SQL for about a year now and have some minor questions about sql objects that reference other objects.
We have some views which reference other views in the joins. I will call one the primary view and the one being referenced in the joins as the secondary view.
Recently we made changes to the secondary view.
After which the primary views which referenced it would not work because of this change and had to be 'refreshed' by using drop/create scripts which essentially just dropped it and recreated the exact same view. I do not recall the exact error message that was returned other than it seemed to suggest that it could no longer see the secondary view since it had been changed. Nothing in the primary view was changed in any way, just the secondary.
Some here where I work have suggested off hand that this was a recompile of the primary view because the contents of the secondary changed.
My questions are:
1. Exactly why did this happen and is there a proper name for it when it does?
2. The same problem does not seem to occur when we have stored procedures referencing views in the joins which had just been changed. Why is that?
Thanks for any help on the matter. I greatly appreciate it.
View 3 Replies
View Related
Feb 22, 2007
Hello,
to make a report easier I'm developing it using a view of joined views of joined views.
Is there any significant performance penalty as opposed to just having one big select?
Cheers.
View 1 Replies
View Related
Mar 14, 2006
Hello There,I'm trying to create a view that has calculations dependent oncalculations, where the problem resides is that each time I make acalculation I must create an intermediate view so I can reference aprevious calculation.for example lets say I have my_table that has columns a & b. now I wanta view that has a & b, c = a + b, and d = c + 1.this is grossly simplified, the calculations I actually use are fairlycomplex and copying / pasting them is out of the question.so what I have is my_view_a which makes column c, and my my_view_finalwhich makes column d (however, in my real application I have 5 of theseviews, a/b/c/d/e/)is there anyway I can consolidate all these views into one? I wasthinking of using a stored procedure with temp tables or somethingalong those lines.I just which I can use the aliases that I create for c in d in onestep.any insight would be greatly appreciated.
View 5 Replies
View Related
Oct 25, 2006
Hi all, i have a DTS package that i call from a C# app, i had it working great until i decided to use an activeX script to do the data transformations instead of the row copy. I need to use ActiveX to add a standard name to the last column in the destination table. the problem is the task is executing without errors (from c#) but nothing is happening, its failing silently. If i modify the Data Transformation back to a standard column mapping (with separate DTSTransformations for each column) it works fine, but as soon as i use activeX to handle the transformations it doesn't work. Can anyone tell me what i may be doing wrong. heres the calling code from C# if(f.Name.Substring(13,7).ToLower() == "product")
{
try
{
activity.Log("Starting Product DTS Package...");
DTS.Package2Class package = new DTS.Package2Class();
object pVarPersistStgOfHost = null;
package.LoadFromSQLServer(
"192.168.8.8",
"username",
"thepassword",
Microsoft.SqlServer.DTSPkg80.DTSSQLServerStorageFlags.DTSSQLStgFlag_Default,
null,
null,
null,
(string)ConfigurationSettings.AppSettings["productDTSPackage"],
ref pVarPersistStgOfHost);
package.GlobalVariables.Item(1).Value = f.FullName.ToString();
package.Execute();
package.UnInitialize();
//force release of COM object
System.Runtime.InteropServices.Marshal.ReleaseComObject(package);
package = null;
}
catch(Exception e)
{
activity.Log(string.Format("Failureprocessing {0}",WorkingPath + f.Name) +" - "+ e.Message);
} and the activex i tried to use for the transformations is: '**********************************************************************' Visual Basic Transformation Script'************************************************************************' Copy each source column to the destination columnFunction Main() DTSDestination("Yesmail Id") = DTSSource("product series") DTSDestination("Customer CKM CustId") = DTSSource("product family") DTSDestination("Product SKU") = DTSSource("transaction date") DTSDestination("product model name") = DTSSource("serial number") DTSDestination("serial number") = DTSSource("product model name") DTSDestination("transaction date") = DTSSource("Product SKU") DTSDestination("product family") = DTSSource("Customer CKM CustId") DTSDestination("product series") = DTSSource("Yesmail Id") DTSDestination("fileName") = DTSGlobalVariables("sourceFile").Value <-- this is why im using the activex for this field to get a Global Variable. Main = DTSTransformStat_OKEnd Functionthe weird thing is the PACKAGE runs fine from ENTERPRISE MANAGER with this activex, it just doesn't do it from my Calling app, perhaps i have missed something i need to change in the package constructor? BTW: i do have my assembly signed in C# for the COM wrapper.thanks in advance, mcm
View 1 Replies
View Related
Jan 6, 2008
Hello,
I have done extensive work with Classic ASP for 9 years now. Working with Stored Proceedures etc
However now am working in c# am adviced by a friend that its best to use the
1. The "Object data source" to call SP
2. Are there any documentations pointers on best practices, how its done ?
I want to use the "on click event" in my class file.
thanks
Ehi
View 3 Replies
View Related
Jan 9, 2008
Hello,
are there any sample codes that show you how to execute a SP in .net
1. Using a class
2. Calling the class in the onlick_button function ?
thanks
Ehi
View 1 Replies
View Related
Jan 20, 2004
I'm developing a new Stored Proc that will be taking information in to enter customer and order info. Looking at the current Stored Proc its using alot of If statements and I'm thinking of breaking the sp up into different Stored Procs and calling them all from One main stored proc. I know how to do this but I was wondering how it would effect performance. So should I simply keep all the stuff in one proc or module it out into multiple ones to make it easier to follow and read?
Thanks
View 1 Replies
View Related
Sep 18, 1998
Does anyone have an example of a Stored Procedure calling
another stored procedure and passing it parms.
What I would like to do is: Have a stored procedure select
data from a table and pass that data to another stored
procedure.
Thanks in advance,
Rodney
View 3 Replies
View Related
Sep 14, 2007
Hi,
Can I call SP in the Trigger? Googled for this but din gt any satisfactory answer.
Thnkx,
Rahul Jha
View 13 Replies
View Related
Mar 26, 2008
I'm working in SQL Server 2005. I have an existing SP that does exactly what I need (it's the aspnet_UsersInRoles_IsUserInRole SP). I want to reuse this SP and use it's return value as a field in the SP I'm writing. How do I go about doing this? I could take the logic out of the called SP and wrap it in a function; but, I would really like to reuse the SP aspnet_UsersInRoles_IsUserInRole. Thanks in advance for any assistance.
View 7 Replies
View Related
Jan 8, 2007
hi,
i'm trying to call a dts package from vb.net.
i got 2 examples which both don't work.
first one gives me a [DBNETLIB][ConnectionOpen(Connect()).]SQL Server does not exist or access denied error.
source code:Dim serverName As String = "SERVERNAME"Dim oPackage As New DTS.Package()Dim oStep As DTS.StepDim pVarPersistStgOfHost As Object = Nothing
oPackage.LoadFromSQLServer(serverName, "USERID", "PASSWORD", DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, _ "DTSPASSWORD", Nothing, Nothing, "DTSPACKAGENAME", pVarPersistStgOfHost)
For Each oStep In oPackage.Steps oStep.ExecuteInMainThread = TrueNext
oPackage.Execute()
Dim err As LongDim source, description, message As StringFor Each oStep In oPackage.StepsIf oStep.ExecutionResult = DTSStepExecResult.DTSStepExecResult_Failure Then oStep.GetExecutionErrorInfo(err, source, description) message = String.Format("ErrorCode: {0}, Source: {1}, Description: {2}", err.ToString(), source, description)Else message = "Success"End IfNext
oPackage.UnInitialize()oPackage = Nothing
second example tries to create a dts package dynamically. this time i get the error that the CustomTask can not be casted, somehting about the QueryInterface.
source code:Dim oPackage As New Package2()Dim oConnection As Connection2Dim oStep As Step2Dim oTask As TaskDim oCustomTask As BulkInsertTask
oConnection = oPackage.Connections.[New]("SQLOLEDB")oStep = oPackage.Steps.[New]()oTask = oPackage.Tasks.[New]("DTSBulkInsertTask")oCustomTask = CType(oTask.CustomTask, BulkInsertTask) <-- error
With oConnection .Catalog = "CATALOG" .DataSource = "SERVERNAME" .ID = 1 .UseTrustedConnection = True .UserID = "USERID" .Password = "PASSWORD"End With
oPackage.Connections.Add(oConnection)oConnection = Nothing
With oStep .Name = "InsertGemal" .ExecuteInMainThread = TrueEnd With
With oCustomTask .Name = "InsertGemal" .DataFile = "D:ImportGemal.dat" .ConnectionID = 1 .DestinationTableName = "Gemal" .FieldTerminator = ";" .RowTerminator = "
"End With
oStep.TaskName = oCustomTask.Name
With oPackage .Steps.Add(oStep) .Tasks.Add(oTask) .FailOnError = TrueEnd With
oPackage.Execute()
oPackage.UnInitialize()oPackage = Nothing
any help highly appreciated!
t.i.a.,ratjetoes.
View 3 Replies
View Related
Feb 22, 2007
Hello,
I am trying to call a SQL Function in C#, as I want to get the value binded back to the column in a datagrid.
Any answers will be appreciated ...............Urgent.
:)
View 4 Replies
View Related
Jan 17, 2008
Hello,
I have the class below. And trying to execute it on a button click event.
What am i doing wrong ?
Thanks
Here is the button click event1 protected void Button1_Click(object sender, EventArgs e)
2 {
3 signup_data_entry signup = new signup_data_entry();
4 signup.signup_data_entry();
5
6 }
Here is my class file. please advice
1 public class signup_data_entry
2 {
3 public signup_data_entry()
4 {
5 //SqlConnection con = new SqlConnection("cellulant_ConnectionString");
6 SqlConnection con = new SqlConnection(ConfigurationManager.AppSettings["cellulant_ConnectionString"]);
7
8 SqlCommand command = new SqlCommand("Cellulant_Users_registration", con);
9 command.CommandType = CommandType.StoredProcedure;
10
11 con.Open();
12
13 //string IP = new string();
14
15
16 command.Parameters.Add(new SqlParameter("@RegionID", SqlDbType.Int, 0, "RegionID"));
17 command.Parameters.Add(new SqlParameter("@RegionDescription", SqlDbType.NChar, 50, "RegionDescription"));
18
19 command.Parameters[0].Value = 4;
20 command.Parameters[1].Value = "SouthEast";
21
22 int i = command.ExecuteNonQuery();
23
24
25 }
26 }
View 6 Replies
View Related
Feb 8, 2008
Hi All,
I have problem in calling the SP from ASP.NET application
@DefApp nvarchar(255)= '' ,@DefBusFunction nvarchar(255)= '' ,@DefImpact nvarchar(255) = '',AS Begin declare @sql nvarchar(4000)declare @whereClause nvarchar(4000)DECLARE @return_value intdeclare @sqlWhere nvarchar(4000) select @sql = 'SELECT DefApp, DefBusFunction, DefImpact FROM Def LEFT JOIN ZFunction ON (def.DefApp = ZFunction.App) AND (Def.DefBusFunction = ZFunction.BusFunction)' if @DefImpact <> '' Set @whereClause = ' where DefImpact ' SET @whereClause = @whereClause + ' = ''' + @DefImpact + '''' set @sqlWhere=@sql + @WhereClauseEXEC @sqlWhereEnd
and i am calling this SP from my ASP.net application to fill the SSRS report. I have written code in ASP.NET like:
sqlCmd = new SqlCommand("subbusample", conn);
sqlCmd.CommandType = CommandType.StoredProcedure;sqlCmd.Parameters.Add(new SqlParameter("@DefImpact", SqlDbType.NVarChar, 255, txtValue3.Text.ToString()));
sqlCmd.Parameters.Add(new SqlParameter("@DefBusFunction", SqlDbType.NVarChar, 255, txtValue1.Text.ToString()));
sqlCmd.Parameters.Add(new SqlParameter("@DefApp", SqlDbType.NVarChar, 255, txtValue2.Text.ToString()));
RptViewer.ProcessingMode = Microsoft.Reporting.WebForms.ProcessingMode.Remote;
RptViewer.ServerReport.ReportServerUrl = new System.Uri("http://servername/ReportServer");RptViewer.ShowParameterPrompts = false;
RptViewer.ServerReport.ReportPath = "/folder name/Subbu_Sample";
when i execute it, i am not able to fill the report with data.
Please let me know what i am doing wrong.
Thanks,
SR.
View 6 Replies
View Related
Mar 7, 2008
In my shared hosting environment where I am making large volumes of database calls, I am very anxious to preserve my heavily throttled threads-I presume when I make a database call, that it is going to a separate server and any threads that server may be using are in addition to my " hosting plan ".
My question (s) is, is it therefore a no-brainer to always make calls to:Dim oResult As IAsyncResult = iDBCmd.BeginExecuteReader()arWaitHandles(0) = oResult.AsyncWaitHandleiIndex = WaitHandle.WaitAny(arWaitHandles, 60000, False)
i.e. run all calls to the database asynchronously?
Is it really releasing a thread back to my asp.net App while it is waiting? (I.e. Is it worth doing)
View 4 Replies
View Related
Aug 18, 2004
I'm new to using DTS packages and I'm running into a problem. Hopefully someone can help me out. I have an ASP.NET page that needs to call a DTS package. Everything seems to be working right up until I call opkg.Execute. The package seems to run but nothing happens. After further investigation I have found what looks like a permissions issue. this is the message I'm getting"
Error Number:
-2147467259 {Integer}
Error Description:
"The Microsoft Jet database engine cannot open the file ''. It is already opened exclusively by another user, or you need permission to view its data." {String}
This is obviously a permissions issue. I'm just not sure who the DTS package is running as and where to set the permissions.
Any ideas????
Thanks
View 1 Replies
View Related
Aug 31, 2001
How to do this?thanks :-)
View 1 Replies
View Related
Sep 15, 2000
Does anyone know the command that can be used to call a dts package directly from VB?
regards,
reshma
View 1 Replies
View Related
Jun 3, 2000
Hello,
What is the syntax of calling stored procedure from vb
Is there any1 can please give me a example of how to call sp with passing parameters, thanks a lot.
Thanks
View 1 Replies
View Related
May 27, 2004
I have a DTS package created in a databasse and need to call that DTS package from a stored procedure.Is there a way that I can do it?
View 2 Replies
View Related
Mar 10, 2006
If you have a begin tran in a sp that calls another sp and that called sp fails. can you execute a roll back from the called sp, or should it return and error which will roll back the begin tran in the original sp
View 1 Replies
View Related
