Hi,
I want to write a sp with structure:
PROCEDURE TEST
@dbname1 varchar(25)
@dbname2 varchar(25)
AS
1. change current database to another, example for DB1 database
2. execute commands or block commands in DB1
3. change current database to another, example for DB2 database
4. execute commands or block commands in DB2
...
Can I do that? Who can help me? Thanks.
qhbaby@hotmail.com
To be clear:
You have a store procedure and inside you make Updates or Inserts against another Server. But that name can change and I dont want to change it manually in everyplace.
Per example:
SELECT *
FROM Himalaya.DBName.dbo.tblUserGroup
WHERE fldUserID = 7300
I have several Selects, Updates and Inserts pointing to that server.
What I can do if I want to change from Himalaya server to another server?
The same with the Database Name.
Thank you
Hi
I have a old report that was pointing to a different database, and i changed the connection string for that report and changed the field names (As per my new stored Procedure). In some places I keep getting this run time error, i have tried looking for all the hidden references to this field and i cannot find any parameter that the old database had. The error messages that i get are as follows:
[rsFieldReference] The Hidden expression for the table €˜tblContactInfo€™ refers to the field €˜PL_Admin_ShowInKit€™. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.
[rsFieldReference] The Hidden expression for the table €˜tblContactInfo€™ refers to the field €˜PL_FA_ShowInKit€™. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.
[rsFieldReference] The Hidden expression for the table €˜tblWithdrawals€™ refers to the field €˜PW_Comment€™. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.
Can Someone please help me out as to how should i get rid of these error messages, they are kinda driving me nuts.
Regards,
Karen
/*Given*/CREATE TABLE [_T1sub] ([PK] [int] IDENTITY (1, 1) NOT NULL ,[FK] [int] NULL ,[St] [char] (2) NULL ,[Wt] [int] NULL ,CONSTRAINT [PK__T1sub] PRIMARY KEY CLUSTERED([PK]) ON [PRIMARY]) ON [PRIMARY]GOINSERT INTO _T1sub (FK,St,Wt) VALUES (1,'id',10)INSERT INTO _T1sub (FK,St,Wt) VALUES (2,'nv',20)INSERT INTO _T1sub (FK,St,Wt) VALUES (3,'wa',30)/*Is something like the following possible.The point is to change the value of the variableinside the query and use it in the calculated field.This doesn't compile of course, but is therea way to accomplish the same thing?*/DECLARE @ndx intSET @ndx = 1SELECT(a.FK+ (CASE WHEN @ndx > 0THEN (SELECT @ndx = b.WtFROM _T1sub bWHERE b.Wt = a.Wt)ELSE 0 END)) as FKplusWTFROM _T1sub a/*Output would look like this:*/FKplusWT-----------112233/*I know, I can get this output just by addingFK+WT. This is not about that.This is about setting vars inside a query*/thanks, Otto Porter
View 1 Replies View RelatedI am listing detail transaction lines in a table sorted by account and order number.
the problem is that I only want to see the detail if the sum of a value field is zero for all the transactions in an order otherwise ignore the detail for that order.
I was trying Group by and Having but this doesn't seem to do what I need.
Being relatively new to Reporting services, any nudge in the right direction would be useful.
How I can rebuild master db to change the Code Page of the current SQL7 , to convert my SQL7 from code page 850 to 1252 ? I have huge data in the old code page dbs , Does rebuild the master db mean reinsert the data again ?
View 1 Replies View RelatedI am currently working on a website that deals with sales of products. For one of my pages for the website I need it to be able to change the current sales information for a specific product.
The top part of the following code selects the specific product however I cannot get the update query to work.
Code:
$describeQuery = "SELECT p.ID, p.NAME, dt.[Year], dt.[Month], dt.SalesVolume FROM Products p
join
(select ProductCode, sum(SalesVolume) as SalesVolume, [Year], [Month] from MonthlySales
group by ProductCode, [Year], [Month])dt
on dt.ProductCode = p.ID WHERE [NAME] = '$desiredProduct' AND [Year] = '$desiredYear' AND [Month] = '$desiredMonth'";
$editQuery = "UPDATE MonthlySales SET SalesVolume = '$NewSales' WHERE ID = '$desiredProduct' AND Year = '$desiredYear' AND Month = '$desiredMonth'";
$query = sqlsrv_query($link, $describeQuery);
Hi All,There is some additional info I forget on this same topic I just posted.I have a database design question, pls give me some help..I want to define tables for salesman's sales target commission . Thecommission could be given per EITHER sales amount of : Group of Products ORGroup of Brand. e.g : the data example :For one salesman_A :product_1, product_2, product_3 etc.. => sales = $100 - $200 =>commission = 5%product_1, product_2, product_3 etc.. => sales = $201 - $400 =>commission = 10%Brand_A, Brand_B, Brand_C .. etc => sales = $100 - $200 =>commission = 2.5%Brand_A, Brand_B, Brand_C .. etc => sales = $201 - $400 =>commission = 5%Below is my table design, is this a good design or something is wrong here ?Thank you for your help.CREATE TABLE Sales_Commission_Header (Sales_ID Char(4) ,Sales_Commission_Group Char(4),Note Varchar(30),Constraint Sales_Commission_Header_PK Primary Key(Sales_ID,Sales_Commission_Group))Alter Table Sales_Commission_Header Add ConstraintFK_Sales_Commission_Header Foreign Key (Sales_Commission_Group)References Commission_Group_Header(Sales_Commission_Group)CREATE TABLE Sales_Commission_Detail (Sales_ID Char(4) ,Sales_Commission_Group Char(4),Sales_From Decimal(12,2) ,Sales_To Decimal(12,2) ,Commission Decimal(5,2),Constraint Sales_Commission_Detail_PK Primary Key(Sales_ID,Sales_Commission_Group, Sales_From, Sales_To))Alter Table Sales_Commission_Detail Add Constraint FK_Sales_CommissionForeign Key (Sales_ID, Sales_Commission_Group) ReferencesSales_Commission_Header(Sales_ID, Sales_Commission_Group)--------------------------------------------** ALTERNATIVE _1 :CREATE TABLE Commission_Group_Header (Sales_Commission_Group Char(4) Primary Key,Note Varchar(30))CREATE TABLE Commission_Group_Detail_Product (Sales_Commission_Group Char(4),Product_ID VarChar(10), -- This product_ID will be FKreference to master productConstraint Commission_Group_Detail_Product_PK PrimaryKey(Sales_Commission_Group, Product_ID))Alter Table Commission_Group_Detail_Product Add ConstraintFK_Commission_Group_Detail_Product Foreign Key (Sales_Commission_Group)References Commission_Group_Header(Sales_Commission_Group)CREATE TABLE Commission_Group_Detail_Brand (Sales_Commission_Group Char(4),Brand_ID VarChar(10), -- This brand_ID will be FKreference to master brandConstraint Commission_Group_Detail_Brand_PK PrimaryKey(Sales_Commission_Group, Brand_ID))Alter Table Commission_Group_Detail_Brand Add ConstraintFK_Commission_Group_Detail_Brans Foreign Key (Sales_Commission_Group)References Commission_Group_Header(Sales_Commission_Group)** ALTERNATIVE _2 :CREATE TABLE Commission_Group_Header (Sales_Commission_Group Char(4),Group_Type Char(1), -- 'B': Brand Group 'P': Product GroupNote Varchar(30),Constraint Commission_Group_Header_PK Primary Key(Sales_Commission_Group,Group_Type))CREATE TABLE Commission_Group_Detail (Sales_Commission_Group Char(4),Group_Type Char(1), -- 'B': Brand Group 'P': Product GroupProduct_Brand_ID VarChar(10),Constraint Commission_Group_Detail_PK Primary Key(Sales_Commission_Group,Group_Type, Product_Brand_ID))Alter Table Commission_Group_Detail Add ConstraintFK_Commission_Group_Detail Foreign Key (Sales_Commission_Group)References Commission_Group_Header(Sales_Commission_Group, Group_Type)The PROBLEM here is : with Product_Brand_ID , I CAN NOT make foreign keyinto both Master Product and Master Brand.So which one is better design ?split the Commission_Group_Detail into Two tables, product and brand , andmake the FOREIGN KEYto master product and master brand (previous mail)ORcombile Commission_Group_Detail for Product and Brand into one table likeaboveand NOT make any FK to master Product or Brand ?Thank you for your help,Tristant
View 1 Replies View Related
Hello,
I'm new to SQL and need help with a query. Not sure if this is the right place.
I have 2 tables, one MASTER and one DETAIL.
The MASTER table has a masterID, name and the DETAIL table has a detailID, masterID, and value columns.
I want to return a populated MASTER table with entries based on the DETAIL.value.
SELECT MASTER.*
FROM MASTER
WHERE DETAIL.value > 3
This is a simplified version of my problem. I can't figure out how to set the relationship between MASTER.masterID and DETAIL.masterID. If I do an INNER JOIN, the number of results are based on the number of DETAIL entries. I only want one entry per MASTER entry.
Hope this makes sense.
How can I do this?
GrkEngineer
Guys,1st of all.. sorry about my english ok? Im from Brazil...Im trying to get a RS connecting through 2 databases with the querybelow:SELECTscar..osbalcao.numeroos,ccell.dbo.atrib_os.osFROMccell.dbo.atrib_osJOIN scar.dbo.osbalcao on ccell.dbo.atrib_os.os =scar.dbo.osbalcao.numeroosWHERE scar.dbo.osbalcao.localprod = '257'AND scar.dbo.osbalcao.status in ('EM')On a SQL IDE i got a good RS, but, when i try to use that same query onDelphi + ZEOS, the SQL Server tells me that "The database namecomponent of the object qualifier must be the name of the currentdatabase".I really dont understand why. I can only tell that the error isreturned from SQL Server.Have anyone got the same trouble?[]'sTiago Gigli
View 1 Replies View RelatedFor inserting current date and time into the database, is it more efficient and performant and faster to do getDate() inside SQL Server and insert the value
OR
to do System.DateTime.Now in the application and then insert it in the table?
I figure even small differences would be magnified if there is moderate traffic, so every little bit helps.
Thanks.
Hi,
In a stored procedure is there a way of accessing a table presend in another database.
Please provide pointers on how i can do this.
Thanks,
Raj
Hi,
In a stored procedure is there a way of accessing a table present in another database.
Please provide pointers on how i can do this.
Thanks,
Raj
Hi,
Is there any command that gives the current database name.
Regards
Kishore
How to get current database name using stored procedure?
View 2 Replies View RelatedHi,
I want to know a sql query that would show the current database name.
I have tried looking into the online books but couldn't find the right query.I hope u can do better.This is urgent.Thanxs. -- Vijay
Hi,
I'm creating a generic procedure in the master db and I'm getting the database name as a input parameter.
How do I set the database inside the procedure , according to this parameter?
T-Sql does not accept "USE" inside procedures .
I tried "sp_defaultdb" but when I select from a specific table in the procedure it still does not identify the table as belonging to that database, it still looks for the table in the master db.
I am writing a Sql Function in CLR. That function receives some data for processing. For processing the data, the function requires some additonal data to be fetched from the same database.
So, how does a CLR function execute SELECT or other Sql statements (or does it need to open up a SqlConnection for the purpose)???
When I create a database from inside of Visual C# Express, why can't I view it with the manager from the SQL Server Express Edition? I mean, it does not appear at all. Is there any problem?
View 5 Replies View RelatedI'm trying to execute a stored procedure within the case clause of select statement.
The stored procedure returns a table, and is pretty big and complex, and I don't particularly want to copy the whole thing over to work here. I'm looking for something more elegant.
@val1 and @val2 are passed in
CREATE TABLE #TEMP(
tempid INT IDENTITY (1,1) NOT NULL,
myint INT NOT NULL,
mybool BIT NOT NULL
)
INSERT INTO #TEMP (myint, mybool)
SELECT my_int_from_tbl,
CASE WHEN @val1 IN (SELECT val1 FROM (EXEC dbo.my_stored_procedure my_int_from_tbl, my_param)) THEN 1 ELSE 0
FROM dbo.tbl
WHERE tbl.val2 = @val2
SELECT COUNT(*) FROM #TEMP WHERE mybool = 1
If I have to, I can do a while loop and populate another temp table for every "my_int_from_tbl," but I don't really know the syntax for that.
Any suggestions?
Hi:
I need to know the current database name in executing a stored procedure,
which will trancatte the log of that database.
(in a dynamic multiple customerID oriented database environment,
ex. custID_101_db, custID_102_db, .....)
Hello,
When I open a query analyzer , of a particular database for example 'AB'
when writting a query how can I get the databasename that I am executing
i.e in the result I should get the DB name.
I was trying select name from master..sysdatabases . But it was returning all DB names.
Also while running a query , can I prompt for the DB name , so that that particular db name can be used to run that procedure or query.
Thanks in advance
I was wondering if it was possible to check what server database you are currently connected to, using T-SQL, when executing commands in Query Analyzer. Even though you choose the server and database when connection, sometimes by habit you may connect to the wrong server & DB, and execute an .SQL file. Is it possible to put a line of T-SQL at the beginning that performs this pseudo-code, as a safety feature:-- While in Query Analyzer with a certain .SQL file open:If current server <> 'TheCorrectServer' and current DB <> 'TheCorrectDB' then cancel this .SQL file execution.
View 3 Replies View RelatedI have a *.sql script that creates database tables, and I need to modify the database to enable the service broker. In addition, the actual name of the database is not known in advance - it is set per instance of the application.
I know I need to do:
ALTER DATABASE dbname SET ENABLE_BROKER
But I must avoid including the name of the database in the script. I did wonder if this would work:
DECLARE @DB varchar(50)
SELECT @DB = DB_NAME()
ALTER DATABASE @DB SET ENABLE_BROKER
But I just get a syntax error. Presumably this also rules out setting the database name as a parameter to the script (SqlParameter stuff)
The only option I can think of is dynamically creating the statement, either in T-SQL or in the calling .NET environment.
Any thoughts?
Ruth
Can someone please help me with the following error?
I am trying to create a trigger that copies data from a table in one database to another. However I am getting the error 'Cannot create trigger on databasename.dbo.tablename as the target is not in the current database.
Below is the SQL I am using to create the trigger:
create trigger addtotable1
on databasename.dbo.tablename
for insert
as
insert into anotherdatabase.dbo.tablename
select databasename.dbo.tablename.userid, databasename.dbo.tablename.username, biadatabasename.dbo.tablename.issuperuser
from databasename.dbo.tablename, inserted
where databasename.dbo.tablename.userid = inserted.userid
and inserted.roleid = 3
Andrew Ling
I have a vb.net 2005 application and i created a SQL Server 2005 db inside my App_Data folder and I need to know how to connect to this database so that I can query it from my vb code. thanks
View 2 Replies View RelatedHi, I've just started using SQL Server Express with VWDE 2005 and I have a database with one table called EVENTS which has a datetime column called DATE. I want to select records where the DATE value is in the next two months.First I tried selecting records where the DATE value is >= today. I used the query builder to produce... SELECT [Date], [Title] FROM [Events] WHERE [Date] >= GETDATE()but got the error "Undefined function 'GETDATE' in expression".Am I missing something obvious?
View 5 Replies View RelatedHi,
I'm trying to create an SQL script that creates a new login account for each of our databases. The one remaining problem with the sql is it could not set the current database using a variable(e.g. USE @dbasename). I tried looking for another way of setting the current database but could not find one.
Is there any other way of setting the current database other than the USE command?
Any help/suggestion will be appreciated.
Thanks in advance,
Julius
Is there an sp_zzzzzz function to return the name of the current database?I would like to use this name as a variable in a stored procedure in orderto create names for further databases (by appending a tag, such asMYDATABASE_BLOB001, ..._BLOB002 etc.Thanks.
View 4 Replies View RelatedHello,I want to select the names and number of rows of all tables in thecurrent database, whose name starts with 'sys'my query is:select o.[name], sum(i.[rows]) as numbersfrom sysobjects oinner join sysindexes ion (o.[id] = i.[id])where o.[name] like 'sys%'and i.indid=1group by o.[name]the result of the query is (depending of the database) :name numberssysaltfiles 14syscharsets 114syscolumns 4934syscomments 2035sysconfigures 38sysdatabases 7sysdepends 5524sysdevices 6sysfilegroups 1sysfulltextcatalogs 0sysfulltextnotify 0sysindexes 98syslanguages 33sysmessages 3795sysobjects 1285syspermissions 806sysproperties 0sysreferences 0sysservers 1systypes 26sysusers 14sysxlogins 3But when I count the number of rows of "sysobjects", I getnumbers=1298, which is different from the result displayed above andsame goes for "syscolumns".I also tried "dbcc updateusage [0]" to update the SQL Server but Ididnt help me.Can anyone please advice me on this behaviour?
View 2 Replies View RelatedWhen executing a stored procedure that is defined in another database, as:
USE db1;
EXEC db2.dbo.sproc;
Is there a way in the stored procedure "sproc" to determine that the caller made the call from db1?
i need to know the name of the current database in variable
like "Northwind"
so i can use it inside PROCEDURE
tnx
Dear all I create this html file on the fly in my asp.net application abd what i would like to do is to store it inside my sql2005 database. What would be the best way?The html file itself is not really big. Probably not more then 600 - 800 characters most. I was thinking the text type fields of the database and then when retreiving it dump it inside in a file and save the file with html extention. Are there any better sugestions? Thank you for your time
View 3 Replies View Related