Is there any performance loss by setting up a view and creating a stored procedure to return a recordset from a view versus setting up the stored procedure to return the recordset from the tables directly?
I am moving an app from Access 2003 to C#/SQL Server 2005. The Access app has one front end exe and two back end databases. One back end db is for UK users, one for US users. The tables and structures are identical, but the data is different. UK users link to the UK back end, US users to the US backend. (The queries are in the front end)
In SQL Server, the view and stored procs will be in one database (KCom), the US data tables in another database (KUS), and the UK data tables in another (KUK). All databases are on the same server.
My question is how to let the views and stored procs in KCom know whether to pull the data from KUK or KUS.
In the front end app, I use ADO.Net to deal with the SQL Server databases.
I have not been able to find a model for this, but it must be somewhat common.
I willl have a lost of nested views and stored procs, but as a simple example, say I have a view in KCom which is just "Select * From Invoice Where InvDate > '01/01/2007'. The ADO request would come from the front end app which would know whether it wanted the data from KUK or KUS. How would I get the view to get the data from one as opposed to the other (KUK vs KUS) ?
Is there some other strategy for this situation, say use of connection strings? If anyone has an idea, or knows of an explanation somewhere on the net I'd greatly appreciate it.
I am using dtswizard to export my db but it doesn't export triggers and all stored procedures. Am I doing anything wrong or is there a better way to do an exact copy of my DB? I am not able to use backup/restore because my db is on a server to which I don't have admin-right, and I have to pay my hoster to create a backup and email me. TIAThomas
How do I search for and print all stored procedure names in a particular database? I can use the following query to search and print out all table names in a database. I just need to figure out how to modify the code below to search for stored procedure names. Can anyone help me out? SELECT TABLE_SCHEMA + '.' + TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'
Novice question. Would someone explain tell me what a view is used for? Also I am confused about the difference between a function and a stored procedure. They both seem like functions to me.
For those intersted here is our TOC and the book's link. You can preorder at this point. We are sticking to the Nov. timeframe, but we may get it done sooner.
Chapter 1 Introducing SQLCLR Chapter 2 Building a Procedure Chapter 3 SQLCLR Strucutre & Common Tasks Chapter 4 Creating Objects Chapter 5 Compare & Contrast Chapter 6 Replacing TSQL Objects Chapter 7 Using the Base Library Chapter 8 Using Procedures in Apps Chapter 9 Error Handling Chapter 10 Administration Chapter 11 Case Study
Hi,Right, i have a fairly good understanding of SQL. However, i have a fairly basic understanding of SQL Server.I know Storedprocedures are really good and i'm starting to use them. I understand they are good for making inserting, updating very easy.However when you look at a SQL Server database you get various folder, this leaves me a little confused with what they are all used for? whats the difference between these?Thanks in advance!sorry for the basic question, i'll try to challange you next time
We have recently begun using transactional replication to keep the data in our SQL Servers synchronized in a geographically dispersed environment. We replicate our tables but we have never replicated views, stored procedures, or user functions in our production systems. We are thinking of doing so but wonder if the overhead of running the replication agents doesn't outweigh the benefits of having replication assist with the occassional change to these design elements.
Is anyone on this forum replicating views, sprocs, and user functions? What has your experience been?
I would like to use the view of the company data in the following stored procedures
without needing to pass a value into the CompanyID identity column. Is there a way to do this? The following code contains the view, the insert trigger on the view, and the desired stored procedures.
Also, this code is given me problem as well the error message says it cannot convert varchar to int. This code is located in the insert trigger.
INSERT INTO State(StateName) VALUES(@StateName)
Here is the code of the company view, notice the C.CompanyID it is used in the GetCompany stored procedure.
FROM Company AS C INNER JOIN Address AS A ON C.AddressID = A.AddressID INNER JOIN State AS S ON A.StateID = S.StateID INNER JOIN Phone AS P ON C.PhoneID = P.PhoneID
Here is code for the insert trigger on the view
CREATE TRIGGER InsertTriggerOnCustomerView ON ViewOfCompany INSTEAD OF INSERT AS BEGIN
DECLARE @CompanyName VARCHAR(128) DECLARE @AddressID INT DECLARE @Street VARCHAR(256) DECLARE @City VARCHAR(128) DECLARE @StateID INT DECLARE @StateName VARCHAR(128) DECLARE @ZipCode INT DECLARE @Country VARCHAR(128) DECLARE @PhoneID INT DECLARE @PhoneNumber VARCHAR(32) --DECLARE @CompanyID INT
INSERT INTO Address(Street, City, StateID, Country, ZipCode) VALUES(@Street, @City, @StateID, @ZipCode, @Country)
SET @AddressID = SCOPE_IDENTITY()
INSERT INTO Phone(PhoneNumber) VALUES(@PhoneNumber)
SET @PhoneID = SCOPE_IDENTITY()
INSERT INTO Company(CompanyName, AddressID, PhoneID) VALUES(@CompanyName, @AddressID, @PhoneID)
END
The stored procedures are here.
CREATE PROCEDURE GetCompany ( @CompanyID INT ) AS BEGIN SELECT CompanyName,Street, City, StateName, ZipCode, Country, PhoneNumber FROM ViewOfCompany WHERE CompanyID = @CompanyID END GO
CREATE PROCEDURE AddCompany ( @CompanyName VARCHAR(128), @Street VARCHAR(256), @City VARCHAR(128), @StateName VARCHAR(128), @ZipCode VARCHAR(128), @Country VARCHAR(128), @PhoneNumber VARCHAR(32) ) AS BEGIN INSERT INTO ViewOfCompany VALUES(@CompanyName, @Street, @City, @StateName, @ZipCode, @Country, @PhoneNumber) END GO
I am writing a program that transforms a generic MS SQL database to make it compatible with our application. In order to make the transformation, I have to access all the elements of the generic database programmatically.
I know that the Master database contains all this information. Is there a query that allows me to access the "non-system" tables, sps, views, and functions?
For Tables, I need to know the Name(s) of the tables, the column names, the column type, ALLOW Nulls, Primary Key, Identity Seed settings, and Triggers.
For SPs, I need to know the Name(s) and access the SP source code (assuming it's unencrypted).
For Views, I need to know the Name(s) and access the Views Source
For functions, I need to know the Name(s) and access the function source.
I can see various tables in the Master database from management studio, like sys.objects, sys.tables - which look like they have the info I need. However, when I run a query against master like:
select * from sys.objects .. I get an error:
Msg 208, Level 16, State 1, Line 1 Invalid object name 'sys.objects'.
I am moving a fairly large app from Access 2003 to C#/SQL Server 2005.
The app has one front end mde, but it has two backend data mdbs, one is used in the US and one in the UK.
The two backend mdb's are identical in structure, but of course the data is different. There are 150 tables or so, and we plan to maintain separate databases after the upgrade.
Now, of course, all of the queries are in the front end, the backend db's contain only tables.
In the new world, I do not want to have to maintain the same views and stored procs in two different SQL Server databases.
Is there a way to have all views and stored procs in one database, but have them draw on the data in the two other databases. There must should be a way to deal with this scenario in SQL Server.
I'm curious to know what templates/standards you guys use for T-SQL stored procs. I've been using the following standard:
Code Snippet IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = '<proc name>') BEGIN DROP Procedure <proc name> END GO
CREATE PROCEDURE <proc name> @parm1, @parm2 AS BEGIN TRANSACTION <transactionName>; BEGIN TRY --DO WORK HERE END TRY BEGIN CATCH SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS ErrorSeverity, ERROR_STATE() as ErrorState, ERROR_PROCEDURE() as ErrorProcedure, ERROR_LINE() as ErrorLine, ERROR_MESSAGE() as ErrorMessage; IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION <transactionName>; END CATCH; IF @@TRANCOUNT > 0 COMMIT TRANSACTION <transactionName>; ELSE
ROLLBACK TRANSACTION <transactionName>; GO
If you guys have any advice/critisizm, please post it as I'm constantly looking into bettering this as much as possible.
Please help, I am trying to write a dynamic search stored procedure using three fields. I use the same logic on the front end using VB to SQL server which works fine, but never tried a stored procedure with the logic. Can you please help me, construct a stored procedure. User can choose any of the three(progno, projno, contractno) fields as a where condition.
I ma using asp.net as front end with sql server backend.
CREATE PROCEDURE dbo.USP_Searchrecords (@ProgNO nvarchar(50), @ProjNOnvarchar(50) , @ContractNOnvarchar(50)) AS DECLARE @myselect nvarchar(2000) DECLARE @psql nvarchar(2000) DECLARE @strsql nvarchar(2000) SET NOCOUNT ON
@psql = "SELECT * FROM Mytable"
IF @ProgNO <> '' then strsql = WHERE ProgNO = @ProgNO end if
If @ProjNO <> '' then if strsql <> '' then strsql = strsql & " and ProjNO =@ProjNO ELSE strsql = wHERE ProjNO =@ProjNO END IF END IF
If @ContractNO <> '' then if strsql <> '' then strsql = strsql & " and ContractNO =@ContractNO ELSE strsql = wHERE ContractNO =@ContractNO END IF END IF
We have modified a column name for a table. Now we need to find out all the database objects (stored procedures, functions, views) which access this table so that we can modify them. This is a very big database and its not easy to open the code for each object and check if its access that table. To add to the complexity, its not just one table and column but a number of tables have been modified.
I have a stored proc which is doing a search from a few tables by doing an inner join. When I enter a new search string, the proc takes some time to get the results. Whereas when I run this again after a few times, it gives results immediately. I know it is due to proc cache but still is there any way to sor this out.
Another problem is that, if a enter a string which is not available in the database itself then it takes a very long time. But takes relatively less time if the string exists in the database.
I didn't want to maintain similar/identical tables in a legacy FoxPro system and another system with SQL Server back end. Both systems are active, but some tables are shared.
Initially I was going to use a Linked Server to the FoxPro to pull the FP data when needed. This works. But, I've come up with what I believe is a better solution. Keep in mind that these tables are largely static - occassional changes, edits.
I will do a 1 time DTS from FP into SQL Server tables.
I then create INSERT and UPDATE triggers within FoxPro.
These triggers fire a stored procedure in FoxPro that establishes a connection to the SQL Server and fire the appropriate stored procedure on SQL Server to CREATE and/or UPDATE the corresponding table there.
In the end - the tables are local to both apps.
If the UPDATES or TRIGGERS fail I write to an error log - and in that rare case - I can manually fix. I could set it up to email me from within FoxPro as well if needed.
Here's the FoxPro and SQL Server code for reference for the Record Insert:
basically, what I am trying to achieve to 2 types of search functions...
Search for All terms (easy and complete) and search for Any Terms...
the way I have gone about this so far is to in my asp.net app, split the search string by spaces, and then search for each word, and merging the resulting dataset into the main return dataset.
this, however has a few problems. the result dataset will contain duplicate values, and i am running queries in a loop.
What i am looking for is a one-stop-shop stored procedue that will split the search string, loop through each word, and add the results to a return table, ONLY if it does not exist already within the return table.
Can anyone point me in the right direction... basically with the splitting of the string and the looping through the words...the rest i think i can handle...
or any other hints/tips/tricks would also be helpful.
Is there a way to namespace groups of stored procs to reduce confusion when using them?
For C# you can have ProjectName.ProjectSection.Classname when naming a class. I am just wondering if there is a way to do the same with SQL stored procs. I know Oracle has packages and the name of the package provides a namespace for all of the stored procs inside it.
Am I correct in understanding that if you create a view or proc with schemabinding that this would prevent a user from droppping a table/column associated with the view or procedure you have created
Hi, I am still learning the bells and whistles of SQL Server and was wondering if I can find out the query that caused my trigger to fire, so that I can log this in another audit table. I have an If Update ( My_Column ) trigger set up, where once an update happens to My_Column much information from the updated row along with , Host_Name and App_Name is sent. I also want to send the exact query used to update it, any ideas? Any comments, suggestions will be greatly appreciated. Thanks, Kartik
I have a view that contains a complex query. A few of the columnscall a function that returns a specific output. I also use a functionto do a join as well.For example:SELECT l.ID, dbo.getStatus(l.ID) AS statusIDFROM tableName ALEFT OUTER JOIN dbo.Status_LKP s ON dbo.getStatus(l.Leg_ID) =s.statusIDFor 800 records, this query takes about 9 seconds. I realize that foreach record, the function is executed on a per row basis, so I amlooking for alternatives.Does anyone know of other ways to accomplish something of the same?Basically I would like to include UDFs in a query and use those UDFsin the where and join clauses.Thanks
CREATE PROCEDURE PROC1 AS BEGIN SELECT A.INTCUSTOMERID,A.CHREMAIL,B.INTPREFERENCEID,C.CHR PREFERENCEDESC FROM CUSTOMER A INNER JOIN CUSTOMERPREFERENCE B ON A.INTCUSTOMERID = B.INTCUSTOMERID INNER JOIN TMPREFERENCE C ON B.INTPREFERENCEID = C.INTPREFERENCEID WHERE B.INTPREFERENCEID IN (6,7,2,3,12,10) ORDER BY B.INTCUSTOMERID
END
IF I AM USING THIS PROC AS I/P TO ANOTHER PROC THEN IT GIVES NO PROBLEM AS I CAN USE ?
BUT IF , I USE ANOTHER PROC SIMILAR TO THE FIRST ONE WHICH GIVES SLIGHTLY DIFFERENT RESULTS AND GIVES TWO SETS OF RESULTS,THEN WE HAVE A PROBLEM,HO TO SOLVE THIS :-
CREATE PROCEDURE MY_PROC AS BEGIN SELECT A.INTCUSTOMERID,A.CHREMAIL,B.INTPREFERENCEID,C.CHR PREFERENCEDESC FROM CUSTOMER A INNER JOIN CUSTOMERPREFERENCE B ON A.INTCUSTOMERID = B.INTCUSTOMERID INNER JOIN TMPREFERENCE C ON B.INTPREFERENCEID = C.INTPREFERENCEID WHERE B.INTPREFERENCEID IN (23,12,10) ORDER BY B.INTCUSTOMERID
END
SELECT A.INTCUSTOMERID,MAX(case when A.intpreferenceid = 23 then '1' else '0' end) + MAX(case when A.intpreferenceid = 12 then '1' else '0' end) + MAX(case when A.intpreferenceid = 10 then '1' else '0' end) AS PREFER FROM CUSTOMER GROUP BY A.INTCUSTOMERID ORDER BY A.INTCUSTOMERID END
WHICH NOW GIVES ME TWO SETS OF DATA , BOTH REQUIRED THEN HOW TO USE ONE SET OF RESULTS AS I/P TO ANOTHER PROC AND OTHER SET OF RESULTS AS I/P TO YET ANOTHER PROC .
Hi all,As all of you are aware you can Encrypt your Triggers/Stored Procedures/Views And Functionsin Sql Server with "WITH ENCRYPTION" clause.recently i came across a Stored procedure on the Net that could reverse and decrypt all Encrypted objects.i personally tested it and it really works.That's fine (of course for some body)Now i want to know is it a Known Bug for Sql Server 2000 and is there a permanent solution for Encrypting mentioned objects.Thanks in advance.Best Regards.
Hi I am writting Stored Procedures that have to be built on the base of other tables specially created for this purpose, is it better to create these intermediate tables as views or as functions returning tables? I guess views would be lighter on performance as they would not be created on the fly?
Is it possible to standardize sql views or functions to filter data for the user, before they view the report? For example if I have a SQL view that filters for "green, cotton-made socks", can this somehow be an option among others before a user presses "view report?"
A response is greatly appreciated. If this is possible, how ?
I have a strange problem, when I define an after update trigger on a view I have a message saying: "Server: Msg 208, Level 16, State 4, Procedure after_update_tblpcl_AnalogValue_Minutely, Line 1" although I am the owner of the view and its in my DB, but when I make the trigger as "Instead of Update" it works fine.
I am working in a project where the business model is complex enoughthat many common retrieval functions become difficult to develop andmaintain in single query statements or functions.I have found the logic is easier to implement (and later modify whenthe code is no longer freshly remembered), by implementing theprocessing layers in nested views, and functions that callsub-functions (UDFs), not too unlike object based programming ornon-DBMS procedural languages. In some cases, the views and functionsare nested three deep.So far, from a design standpoint, this practice is working very well.Code that would be a recusive mess is reduced to two or three simplercode blocks. With the proper indexing and query structure, performanceseems to be satisfactory (time will tell).In MS SQL Server, is there anything which makes this practice unsound?Examples:CREATE VIEW vw2 AS SELECT * FROM tbl1 WHERE ...CREATE VIEW vw3 AS SELECT * FROM vw2 WHERE ...Application uses: SELECT * FROM vw3-or-CREATE FUNCTION udf2 AS SELECT * FROM tbl1 WHERE ...CREATE FUNCTION udf3 AS SELECT * FROM udf2 WHERE ...Application uses: SELECT udf3(param) AS value