Hi,
I am trying to work on a stored procedure that will work with multiple database. I have a prototype of multiple databases. Those are named as the following: ts2_aldkm_app, ts2_aldkp_app, ts2_aldkt_app. The middle part of the database name corresponds to the site name e.g aldkm corresponds to site aldkm etc. Each database has one table tblCustomer which is scripted as follows:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblcustomer]') and OBJECTPROPERTY( id, N'IsUserTable') = 1)
drop table [dbo].[tblcustomer]
GO
CREATE TABLE [dbo].[tblcustomer] (
[RecKey] [int] NOT NULL ,
[CustID] [text] COLLATE SQL_Latin1_Gene ral_CP1_CI_AS NULL ,
[sales_ytd] [money] NULL ,
[sales_lstyr] [money] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
Now I need to create a stored procedure that should work with any of the three ( or more ) database if proper parameters are passed. I need to pass the database name, custid, sales_ytd, sales_lstyr and a sql paramter to process the database name.
When I am creating the stored procedure I am getting the following error:
Server: Msg 306, Level 16, State 1, Procedure process_custome r, Line 13
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.
I am not sure why I am not getting this error. Any help to resolve this is highly appreciated. Thanks.
STORED PROCEDURE CODE:
create procedure process_custome r
--DECLARE
@dbname varchar(255),
@custid int,
@sales_ytd money,
@sales_lstyr money,
@site varchar(10),
@SQL varchar(50)
as
set @dbname = 'ts2_' + @site + '_app'
set @SQL = 'use ' + @dbname
select custid, sales_ytd, sales_lstyr
from tblcustomer
where
custid = @custid
and sales_ytd = @sales_ytd
and sales_lstyr = @sales_lstyr
4 13135 ck9663 2,878
Recognized Expert Specialist
You can do an IF inside your SP - IF @parameterdbname = this
-
select and do this
-
IF @parameterdbname = this
-
select and do this
-
IF @parameterdbname = this
-
select and do this
-
or
do a - EXEC('select * from ' + @parameterdbname + '.dbo.tblCustomer where condition = true')
-
Good luck
-- CK
Thanks for your help CK. I appreciate it very much. Actually I think I could not explain the problem I am having properly. I need to use the database based on the parameter passed to the site. The correct procedure logic will be as follows:
STORED PROCEDURE CODE:
create procedure process_custome r
@custid varchar(50),
@sales_ytd money,
@sales_lstyr money,
@site varchar(10)
as
use 'ts2_' + @site + '_app'
select custid, sales_ytd, sales_lstyr
from tblcustomer
where
custid = @custid
and sales_ytd = @sales_ytd
and sales_lstyr = @sales_lstyr
With the above I am getting the following error:
Server: Msg 154, Level 15, State 1, Procedure process_custome r, Line 13
a USE database statement is not allowed in a procedure or trigger.
So, I need to use the site parameter in order to determine which database to use. Any further thoughts? Sorry for the confusion. Regards.
You can do an IF inside your SP - IF @parameterdbname = this
-
select and do this
-
IF @parameterdbname = this
-
select and do this
-
IF @parameterdbname = this
-
select and do this
-
or
do a - EXEC('select * from ' + @parameterdbname + '.dbo.tblCustomer where condition = true')
-
Good luck
-- CK
ck9663 2,878
Recognized Expert Specialist
Thanks for your help CK. I appreciate it very much. Actually I think I could not explain the problem I am having properly. I need to use the database based on the parameter passed to the site. The correct procedure logic will be as follows:
STORED PROCEDURE CODE:
create procedure process_custome r
@custid varchar(50),
@sales_ytd money,
@sales_lstyr money,
@site varchar(10)
as
use 'ts2_' + @site + '_app'
select custid, sales_ytd, sales_lstyr
from tblcustomer
where
custid = @custid
and sales_ytd = @sales_ytd
and sales_lstyr = @sales_lstyr
With the above I am getting the following error:
Server: Msg 154, Level 15, State 1, Procedure process_custome r, Line 13
a USE database statement is not allowed in a procedure or trigger.
So, I need to use the site parameter in order to determine which database to use. Any further thoughts? Sorry for the confusion. Regards.
Yes. On my pseudocode, your @site is my @parameterdbnam e. What I gave you is a pseudocode that you can use as pattern, not actual code.
-- CK
Thanks for your help. I appreciate it.
Yes. On my pseudocode, your @site is my @parameterdbnam e. What I gave you is a pseudocode that you can use as pattern, not actual code.
-- CK
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: DarthMacgyver |
last post by:
Hello,
I recently wrote a survey application. Each question is very similar.
The first questions gives me a problem when there are multiple people
taking the survey (The Database connection Timed out) I am using the
Data Access Application Blocks as ASP.NET (using VB.NET) and SQL 2000.
In there first question there can be up to 27 answers. So I figured
instead of making 27 different trips to the database I woulc just
concatenate my...
|
by: Jon LaRosa |
last post by:
Hi all -
I have a web application and I want to be able to do some basic error
handling. For example, here is one error I would like to catch and
display in a useful way for the user:
-----------------
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
UPDATE statement
|
by: xAvailx |
last post by:
I have a requirement that requires detection of rows deleted/updated by
other processes. My business objects call stored procedures to create,
read, update, delete data in a SQL Server 2000 data store. I've done
a fair amount of research on concurrency handling in newsgroups and
other resources. Below is what I've come up as a standard for
handling concurrency thru stored procedures. I am sharing with everyone
so I can get some comments...
|
by: Paul Hale |
last post by:
Hi all,
being new to c# I'm trying to find the best way of passing multiple records
to insert into a sql database via a stored procedure.
I'm using visual studio 2005 RC
SQL server 2005 and C# of course.
My scenario is as follows:
I have a multiline textbox with which users can enter numerous items(one per
line) and all are then added to an Arraylist and then seperatly validated
using a Regex.
Once all valid entries are collated I would...
|
by: Andrew |
last post by:
Hey all,
I am very new to ASP.Net (and .Net in general), but that isn't stopping the
boss from wanting to begin new projects in it. This latest project has me
kinda stumped and after a couple days of struggling, I figure asking you all
(the experts) will keep me from going down some dark and dangerous road.
The project I have is a fairly simple one, in theory anyway. The gist is to
create a page where the user enters an IDNumber,...
| |
by: James Radke |
last post by:
Hello,
I have a multithreaded windows NT service application (vb.net 2003) that I
am working on (my first one), which reads a message queue and creates
multiple threads to perform the processing for long running reports. When
the processing is complete it uses crystal reports to load a template file,
populate it, and then export it to a PDF.
It works fine so far....
|
by: James Radke |
last post by:
Hello,
I am looking for guidance on best practices to incorporate effective and complete error handling in an application written in VB.NET. If I have the following function in a class module (note that this class module represents the business layer of code NOT the gui layer):
Public Function Test(ByVal Parm1 As Integer, ByVal Parm2 As Integer) As SqlDataReader
' Declare the SQL data layer class
Dim oSQL As New...
|
by: Jurgen Defurne |
last post by:
I am currently designing an application which should be accessible from
different interfaces. For this I like to be using stored procedures to
process the contents of form submissions and dialog screens.
After studying the PG database, it seems to me that I can fulfill the
following requirements.
a) The basic contents of the internal data dictionary can be used to
check incoming fields from on their length and permitted contents.
|
by: bonneylake |
last post by:
Hey Everyone,
Well this is my first time asking a question on here so please forgive me if i post
my question in the wrong section.
What i am trying to do is upload multiple files like gmail does. I found a script that does this on easycfm.com (Topic 13543).
But anyway when i try to upload multiple files it will create multiple records in my database (like it should), but it wont upload multiple files. What ever file i choose to upload...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
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...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
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,...
|
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...
|
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...
|
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...
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |