I am trying to build a stored procedure reference two diffrent tables with same name but diffrent schemas based on the login_user or who calls the procedure. I have developed the following procedure but still ot able to get it..The table test_table exists two times within the system
User1.test_Table
User2.TestTable
if i am user1 calling the procedure should reefernce user1.test_table and vice versa for user 2.
alter procedure dbo.test_proc (@pUsername varchar(150)) with Execute as caller as Begin Select System_User Select * from Test_Table End
hi, i'm trying to debug a stored procedure (in sql server 2005) with visual studio 2005, but all my tables and procedures are inside a schema and when i configure my login in sql server for being in sysadmin role, it dosn't map the schema so i need to write the schema before the tables and procedures, and i don't need it in that way... how can i do to have a login in sysadmin role and a schema maping in than login???,
I am a fairly experienced programmer, but new to SQL Server - I understand basic DB theory well enough, but don't have much practical experience with using SQL Server.
I'm working on a project at the moment, where, as part of the spec, users can create 'systems' in the database. For example, in a parts database for a pumping station, there may be 10,000 parts. Rather than have one huge database for, say, 10 pumping stations, we would prefer to have 10 smaller databases, each dedicated to its own system. The schemas would be identical.
I think one approach to this would be have an empty database in SQL server (with the correct tables/schemas/relationships etc) and then copy that within SQL server, with a new name (the system name), probably using a stored procedure.
My question: Is this possible, is there already a stored procedure in SQL Server (2000) to do this, or do I have to write one? Writing a SP to physically create the database from scratch would be a nightmare, I'm hoping there is a simple 'copy_db to new_db' type stored procedure. Maybe there is a program can read a DB and create a script to re-create the DB under a new name?
I have to create a stored procedure that accepts a column name and returns the name of the tables that have that column name. I'm not sure how to get it to accept the column name. Here's what I have so far:
create proc spTheNameOfTheProcedure as select table_name from information_schema.columns where column_name = XXXXXX
Say i had a system table in that table i had dbo.user then format (dd,mm,yyyy) after that i had dbo.user_backup_ 10_12_2007,dbo.user_backup_ 10_13_2007,dbo.user_backup_ 10_14_2007,dbo.user_backup_ 10_15_2007,dbo.user_backup_ 10_16_2007
If i use the SQL command SELECT * FROM paychexdb.dbo.sysobjects WHERE name like 'users_backup_%' AND xtype = 'U' AND name not in( SELECT Top 3 name FROM paychexdb.dbo.sysobjects WHERE name like 'users_backup_%' AND xtype = 'U' ORDER BY Name DESC)
results would be 10_12_2007,10_13_2007.
So i want to put those results in a backup file and keep top 3 which is 10_14,10_15,10_16. I want to create a store procedure that i can execute and do that for all my tables. this stored proc (sp) should first backup the table if it doesn't exist and then delete all but the most recent 3
I am trying to write a stored procedure that generates a PDF file for example my PDF file will look something like this (there should be spaces between the columns):
First Name Last Name Address Mike Mik Jr 141552 South Charlie D 1422141
Lets say my table name whichthat has all these data is called dbo.TestTable I spent so much time in google and I have not found one simple good example. Can you help me please
Is it possible to drop and then create a view from a stored procedure? Like the way you can drop and create a temp table. I want to create a view of the fields in a table something like: But I cannot include the field names, they may be changed by an admin user. If exists view 'custom_fields" drop view 'custom_fields' Create view custom_fields Select * From tblCustomFields And make this a view in the db named custom_fields. And I want to call it from a button click in my UI.
I need to use a stored procedure that will create a table. The table name must be passed to the stored procedure. This is what I have so far, but it does not allow me to run it.
alter procedure dbo.createNewBUtable ( @BU as varchar(50) ) as set nocount on; create table @BU ( BUid varchar(50) primary key, BUinfo varchar(50) )
Let me start by saying that SQL is not my strong suit so please bear with me.
Here's my situation I'm building a property portfolio system with db structure as follows
tblProperty (The master table - linked 1 to many via propertyID) tblValuations (columns valuationID, propertyID, currentValue, valuationDate)
For each property in tblProperty the valuations table can hold multiple valuation entries.
I need to have a summary that shows the number of properties a user has listed and crucially I need to be able to find the latest valuation for each property & add them together to retrieve the total value of a users portfolio
I'm totally stumped with this so any help/advice would be gratefully received
When I use comands insert and update with VB5 and ODBC, one temporary stored procedure is created in database tempdb to each command executed. These stored procedures are deleted only when the connection is closed. My program use comands insert and update inside a loop, and a lot of temporary stored procedure are generated and full the database tempdb. When it occur, others systems are afecteds.
My questions: Why it occur ? Wich have created this stored procedure ? How to avoid it occur ?
The versions are: SQL Server 6.5 Visual Basic 5.0 SQL Server ODBC Driver 2.65.0240
I have no right to connect to my hosting company using MS SQL2000 client. And I have to use stored procedure, can any body tell me is it possible to create stored procedure ( I need is complex one with loops of if loops). If possible please list the syntax.
I tried to create a stored procedure but instead of opening up to a new stored procedure it displays an exist stored procedure. I erased the code and typed in my code now i received this error message.
MS SQL-DMO (ODBC SQLState:42000) Error 2729: Procedure ‘spUpdate_date_time’ group number 1 already exists in the database. Choose another procedure name
I am trying to create a database within a stored procedure, so that the database name is generated each time. Please review the attached code, as sql seems to error out on '@dname'
yesterday i was trying to create Stored procedure but it fails i don't know why
CREATE proc GetBooksbyBorrowerID @Borrower_id INT AS BEGIN SELECT A.BORROWER_ID ,a.ISBN, b.book_Title,b.LANGUAGE, CONVERT(VARCHAR,a.borrowed_from_date,103)"Borrowed On(dd/mm/yyyy)" FROM borrower_details a, book_mst b WHERE a.borrower_id=@Borrower_id AND a.ISBN = b.ISBN END GO EXEC SP_Task1 10001
Which permissions do I need to have (as a User) to create a Stored Procedure Which other entity's properties do I need to change in order to create a Stored Procedure
I am trying to create a view or Stored Procedure between different table
Table1 consist of the follwing Fields:
Ref_No: String hold the reference number, Unique Details: String
Table2: MasterRefNum : String, not Unique SubscriberRefNum : String, not Unique
What I am trying to do is that when the user enter a refernece number the system should return back 1- the details where Ref_No = the required refernece number 2- get all the SubscriberRefNum from Table2 where MasterRefNum = the required refernece number and from the Table1 get the details for those SubscriberRefNum numbers
Im am wandering if it is possible to create two views in two different tables from within the same stored proc:ex create proc myProc as use [myDb1] go create view myV1 as select * from mytable go use [myDb2] go create view myV2 as select * from mytable go
go --- of course the go's are not allowed in a sproc, the create statement must be the first of a query batch and a vew can not have the databaase name preapended like when creating a table plus one can not use the "use" word in a proc, I tried using exec to bypass the "first statement in a batch" and go restrictions but have not been able to overcome the "use [myDb]" restriction, is there a way to solve this problem?
Yes it looks like a stupid question but when i right click stored procedures and click new stored procedure, it gives me a QRY analyzer style window and all i can do is save the qry as a regular .qry file ?
Is there a stored procedure installed by sql server 2000 that I cancall and just pass in the name of a new database and have it createthe database for me? If not, how do I do it in sql? Thanks.
Is it possible to dynamically create an sql create view statement then execute that sql statement? Or because create views must be the first statement in a query batch, it's not possible?
I'm currently struggeling in creating some SQL script to create stored procedures. I found the following example on MSDN:
Code SnippetUSE pubs IF EXISTS (SELECT name FROM sysobjects WHERE name = 'au_info2' AND type = 'P') DROP PROCEDURE au_info2 GO USE pubs GO CREATE PROCEDURE au_info2 @lastname varchar(30) = 'D%', @firstname varchar(18) = '%' AS SELECT au_lname, au_fname, title, pub_name FROM authors a INNER JOIN titleauthor ta ON a.au_id = ta.au_id INNER JOIN titles t ON t.title_id = ta.title_id INNER JOIN publishers p ON t.pub_id = p.pub_id WHERE au_fname LIKE @firstname AND au_lname LIKE @lastname GO
The thing is, I want to change and use it like this:
Code SnippetUSE pubsIF NOT EXISTS (SELECT name FROM sysobjects CREATE PROCEDURE ...USE pubsGOALTER PROCEDURE au_info2 ... But that does not seem to work.. I get the following error: Code SnippetIncorrect syntax near the keyword 'PROCEDURE' Any idea's? Any help is appreciated! Kind regards, Frederik
In SQL Manager I can right click on a stored procedure, table, etc. and I am presented with a list of options one of which is "Create >". Clicking this I can get a script that will create the respective stored procedure or table and write the create script to the clipboard, a file, or an SQL query window. I want to automate this to essentially selectively "back up" our data base by creating one large script that will create the tables and stored procedures from the database. Is there an API, tool, or library call that I can get access to the code that implements these menu selections?
