Cretae a view in one DB with a stored procedure from another DB

Hennie de Nooijer

Hi,

Ik created an application with visuals basic.NET. This has a
connection string to one database, let's say 'A'. In this database a
stored procedure is called which should execute a string (which is
passed by the) VB tool. This string is a CREATE VIEW statement en this
should be executed in another database let's say 'B'.

I tried this in Transact - SQL

EXEC('USE B;' + Query)

An error occurs : CREATE VIEW should be the first in a batched
statement.

Could anyone help me with this one?

Greetz,
Hennie

Jul 20 '05 #1

Subscribe Reply

1461

Simon Hayes

"Hennie de Nooijer" <hd********@hot mail.com> wrote in message
news:19******** *************** **@posting.goog le.com...

Hi,

Ik created an application with visuals basic.NET. This has a
connection string to one database, let's say 'A'. In this database a
stored procedure is called which should execute a string (which is
passed by the) VB tool. This string is a CREATE VIEW statement en this
should be executed in another database let's say 'B'.

I tried this in Transact - SQL

EXEC('USE B;' + Query)

An error occurs : CREATE VIEW should be the first in a batched
statement.

Could anyone help me with this one?

Greetz,
Hennie

You need a GO right before the CREATE VIEW statement, and this won't work in
dynamic SQL. In any case, this isn't really a good way to create a database
object - it would probably be a lot easier for your client application to
connect directly to database B. Or if you really want to do it from SQL,
then why not create the stored procedure in database B and call it from
database A?

It might help if you can explain what you're trying to achieve, and someone
may be able to suggest a better solution, as creating objects dynamically
from a user application can create significant problems with security and
maintenance. But since you didn't say what your goal is, you may have a good
reason for doing this.

Simon

Jul 20 '05 #2

Similar topics

8102

Execute Big Dynamic SQL in Stored Procedure to Create View

by: MD | last post by:

I am trying to create a dynamic SQL statement to create a view. I have a stored procedure, which based on the parameters passed calls different stored procedures. Each of this sub stored procedure creates a string of custom SQL statement and returns this string back to the main stored procedure. This SQL statements work fine on there own. The SQL returned from the sub stored procedure are returned fine. The datatype of the variable that...

Microsoft SQL Server

5637

Using "SELECT * " is a bad practice even when using a VIEW instead of a table?

by: serge | last post by:

Using "SELECT * " is a bad practice even when using a VIEW instead of a table? I have some stored procedures that are identical with the difference of one statement in the WHERE clause. If I create a single View and specify also in this View the WHERE clause that is common in these stored procedures, I will have the new stored procecures changed to be like:

Microsoft SQL Server

1921

To View or not to View, that is the question...

by: Lauren Quantrell | last post by:

In my old MDB databases, I constructed a lot of "subQueries" to filter out records, then based a new query on the subQuery. This results in huge speed increases on large datasets. However... In Accesss2000 ADP Project with SQL Server backend: Is there any benefit to creating Views, then basing a Stored Procedure on that View, rather than on the tables/table joins themselves? Since I can't pass parameters to a View, all I can really do...

Microsoft Access / VBA

4949

Using form criteria in ADP view?

by: M Wells | last post by:

Hi All, Is it possible to use form criteria in a query / view in an Access 2003 ADP with SQL Server as the backend? ie something like: select * from mytable where recid = forms!! I'm trying to do something like this as the basis for a subform but I

Microsoft Access / VBA

3713

DataGrid and SQL Server view

by: Matt | last post by:

Hi, I'm trying to fill an ASP.NET DataGrid from a SQL-Server View. Note that this is a View and not a Stored Procedure. If i write the SQL instructions directly in the code, or if i use a stored procedure, it works great. But if the same SQL instructions are in a view, it won't work. Here it goes :

ASP.NET

1849

Stored procedure or view?

by: Justin | last post by:

I am creating a web app for a client in VS.NET using ASP.NET with C#. I need to query three tables in a database using one parameter and display the results on the page. my question is should I use a Stored Procedure or view in SQL Server to get the data from multiple tables? What is the difference? and where can I find a some articles on write code to display the results of the SP or view? Thanks, Justin.

ASP.NET

104

10894

ORDER BY in VIEW not working

by: Beowulf | last post by:

I have the view below and if I use vwRouteReference as the rowsource for a combo box in an MS Access form or run "SELECT * FROM vwRouteReference" in SQL Query Analyzer, the rows don't come through sorted by Numb. Everything I've read on the web suggests that including the TOP directive should enable ORDERY BY in views. Does someone have an idea why the sorting is not working correctly for this particular view? thanks. CREATE VIEW...

Microsoft SQL Server

2657

view vs stored procedure [ when and where to use view ]

by: Sureshbari | last post by:

Dear All, I have four table in database , each table contain the 5 lacs record, i have created a view on that four table using union clause. and now i am call that view form my code like select * form myview where name like '%Xya%' after that i am getting the memory fault .

Microsoft SQL Server

13227

Creating a View from a Stored procedure

by: apothecary | last post by:

Hello Newbie here. Is there a way of creating a VIEW...using a stored procedure. I am basically trying to create a view to return some data that I am getting using a stored procedure. I have created the procedure and when I execute this its working ok. The stored procedure uses a datefrom and dateTo which I have set up by

Microsoft SQL Server

10168

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...

C / C++

9959

The easy way to turn off automatic updates for Windows 10/11

by: Hystou | last post by:

Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...

Windows Server

9837

Discussion: How does Zigbee compare with other wireless protocols in smart home applications?

by: tracyyun | last post by:

Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...

General

8833

AI Job Threat for Devs

by: agi2029 | last post by:

Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...

Career Advice

7381

Access Europe - Using VBA to create a class based on a table - Wed 1 May

by: isladogs | last post by:

The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...

Microsoft Access / VBA

5279

Trying to create a lan-to-lan vpn between two differents networks

by: TSSRALBI | last post by:

Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...

Networking - Hardware / Configuration

3929

transfer the data from one system to another through ip address

by: 6302768590 | last post by:

Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

C# / C Sharp

3532

How to add payments to a PHP MySQL app.

by: muto222 | last post by:

How can i add a mobile payment intergratation into php mysql website.

PHP

2806

Comprehensive Guide to Website Development in Toronto: Expert Insights from BSMN Consultancy

by: bsmnconsultancy | last post by:

In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

General