Security - Stored Procedure - View - Table

Oct 20, 2007

Hi all .
I am going to consult you about a security concept .
In my database I have a set of tables (eg. City , Country , ...)
and I have a corresponding View for each Table (eg. V_City , V_Country , ...)
and There are a set of Add/Delete/Update/List stored procedures for each object , the user which logs on to database has only EXECUTE Privilege on defined stored procedures and does not have any kind of access to any other object in database , In your opinion , Can Inserting into/Updating Views instead of Inserting into/Updating Tables cause any problem?and Is this model help improve security? Not that I access SQL Server Database from a .Net App.

Thanks in advance.

View 9 Replies


ADVERTISEMENT

Using Stored Procedure In View As A Table ?!

Aug 28, 2007

Hi guys
I have a stored procedure that a make crosstab table , In this table the main column is "job titles" these jobs  must be ordered  in certain  way , for example "1st managers then engineers  … workers  … " so In the table that   job titles are defined  there is also a column named "Ranking" so the" job titles" could be sorted appropriately by ranking order .
The problem is I cannot have the "Ranking" column with my crosstab table so I need to load it in a view or something like that.
Any Idea?
 

View 8 Replies View Related

Stored Procedure Table Names To View

Sep 7, 2005

I need help to create a stored procedure to get the table names from a database and create a Union view. There are over 100 tables.

View 3 Replies View Related

Joining N-table In View Or Use N-stored Procedure?

Nov 14, 2007

I have three table and I have to fetch some data from each one. This can be done by calling three diffrent stored procedures for each one.But it can be done with view and joining these three tables and only one time calling this view and getting the same result.(These joins can be from diffrent database too)

Which one is better View and joining these three tables and call this view one time or calling three stored procedures in for example .net side.


Sincerely

Kianoosh

View 7 Replies View Related

CREATE TABLE/VIEW From Stored Procedure Or SELECT...

Jun 8, 2006

Can anyone tell me how can I create a table in (SQL Server 2000) direct from a stored procedure execution or from a SELECT result?

I need something like this: CREATE TABLE < t > FROM <sp_name p1, p2, ...>or like this:

CREATE TABLE < t > FROM SELECT id, name FROM < w > ...

Thank you!

View 6 Replies View Related

How To Change View Code Or Table Form Stored Procedure + -

Jun 1, 2008

need help with my problem
i have this view
this code change the value field "new_unit" evry month from 1 > 2 > 3 > 4
like this evry 4 month it return to 1 >2..........
------------------------------------------ for example

if i put
unit_date = 01/05/2008
and unit=1
than new_unit=1




Code Snippet

SELECT empID, ShiftType, unit_date, unit, ISNULL(NULLIF ((unit - DATEDIFF(mm, unit_date, '01/01/' + CONVERT(varchar, YEAR(GETDATE())))) % 4, 0), 4)

AS new_unit

FROM dbo.empList




my question is how to create a stored procedure that move forward (all the employee) the "new_unit" field
in +1 OR "unit_date" value MONTH +1


like create stored procedure name "plus" + so if i run this name stored procedure name "plus"

the stored procedure go to the viewor table and change the code view or table value

so i forward all the the "new_unit" or "unit_date" value IN one (change the cycle)+1

it doesn't matter if it change the "unit" value in the table "dbo.empList" or "unit_date" value

the important thing is that i can forward +1 or backward -1

evry time i run the stored procedure i get +1 (in the "new_unit")
and olso
create stored procedure name "minus" + so if i run this name stored procedure name "minus"
this stored procedure that move backward the the "unit" value in the table "dbo.empList" or "unit_date" value in -1

TABLE dbo.empList

empid ShiftType unit_date unit

----------------------------------------------------------------------------
11111 6 01/01/2008 1
2222 8 01/03/2008 4
3333 9 01/04/2008 3

TNX for the help

View 7 Replies View Related

Calling A Stored Procedure From A View OR Creating A #tempTable In A View

Aug 24, 2007

Hi guys 'n gals,

I created a query, which makes use of a temp table, and I need the results to be displayed in a View. Unfortunately, Views do not support temp tables, as far as I know, so I put my code in a stored procedure, with the hope I could call it from a View....

I tried:

CREATE VIEW [qryMyView]
AS
EXEC pr_MyProc


and unfortunately, it does not let this run.

Anybody able to help me out please?

Cheers!

View 3 Replies View Related

Stored Procedure - Security

Dec 22, 2006

Is it a safe way to use a paramter (which fetch values from querystring) in the "where-part" of my stored procedure? Or is it an securityrisk because I dont know what the user is writing in the url-field?   
 I got the following sqldatasource which grab the value (from querystring) into the my parameter.
<asp:SqlDataSource ID="SQLDataSource"
ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
runat="server"
SelectCommand="My_StoredProcedure"
SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:QueryStringParameter QueryStringField="Myparameter" Name="City" Type="string" />
</SelectParameters>
</asp:SqlDataSource>
 

View 4 Replies View Related

Stored Procedure Security?

Oct 31, 2007

Hi all,

this should be a easy question, but I can't really seem to find anything on it...

Here's the scenerio:

n-tier web app, with asp/iis/sql... All database calls are done via stored procedures with the same user (lets call the user: webuser)

webuser has NO access to the db in question, but it is granted EXEC on all stored procedures.

My question is, when a user tells the web app to say delete a record, the application server (iis) makes a call to the database with the webuser security cred's... It says execute the delete stored proc.

webuser has the ability to do this, so it happens. However, in what context (this may not be the right word) does the stored procedure execute?

ie: which user does the stored proc exeucte as. It can't be webuser can it? Because webuser does not have access to the base tables.

Does the stored proc execute as the user that created it?

I'm confused...

thx all!

View 4 Replies View Related

Stored Procedure Security

Mar 19, 2008

Hi,

I have a Stored Procedure in one database that grabs data from another database. I don't want the user to be able to read data from the tables that the stored procedure Selects from but I would like the user to be able to run the stored procedure. Pretty standard request I think.

What I have done is to give the user in question a login then assigned them the Execute Permissions on the stored procedure. Unfortunately they are still unable to run the stored procedure from my web app. I have "allow anonymous access" turned on but I am still getting an error when the user tries to execute the stored procedure.

Am I missing something here or could there be a bigger issue?

Thanks,
Patrick

View 5 Replies View Related

Stored Procedure Security

Sep 4, 2007



Hi,

I have a stored procedure spoc_CreateNewUser.

I have a role called 'creator'. I want that no one else other than creator should be able to execute this procedure through an asp.net application or directly.If anyone attempts, it should be logged.

HOw do I do this. Please explain the answer. I am new to this.

Ron

View 1 Replies View Related

CLR Stored Procedure Security

Jan 11, 2008

Hi,


I created a CLR stored procedure, and added a web service reference, am using the generated proxy class

to call the web service, currently am facing the following security issues. I think am missing

something, what are the possible patterns to call a web service from the CLR Stored procedure.



Thank you



---------------------------------------

The following is the error that is happening each

time I call the stored procedure


A .NET Framework error occurred during execution of

user defined routine or aggregate

'MyStoredProcedure':
System.Security.SecurityException: Request for the

permission of type 'System.Net.WebPermission, System,

Version=2.0.0.0, Culture=neutral,

PublicKeyToken=b77a5c561934e089' failed.
System.Security.SecurityException:
at System.Security.CodeAccessSecurityEngine.Check

(Object demand, StackCrawlMark& stackMark, Boolean

isPermSet)
at System.Security.CodeAccessPermission.Demand()
at System.Net.HttpWebRequest..ctor(Uri uri,

ServicePoint servicePoint)
at System.Net.HttpRequestCreator.Create(Uri Uri)
at System.Net.WebRequest.Create(Uri requestUri,

Boolean useUriBase)
at System.Net.WebRequest.Create(Uri requestUri)
at

System.Web.Services.Protocols.WebClientProtocol.GetWe

bRequest(Uri uri)
at

System.Web.Services.Protocols.HttpWebClientProtocol.G

etWebRequest(Uri uri)
at

System.Web.Services.Protocols.SoapHttpClientProtocol.

GetWebRequest(Uri uri)
at

System.Web.Services.Protocols.SoapHttpClientProtocol.

Invoke(String methodName, Object[] parameters)
at MyWebServiceProxy.HelloWorld()
at MyStoredProcedure()

View 3 Replies View Related

Stored Procedure Security Ideas??

Jan 21, 2004

Does anyone know how you can distribute Stored Procedures that work with a third party application but keep the contents / code of those Stored Procedures from being viewed and edited by the end users who have purchased the application as well as own and operate the SQL Server??

Thanks

View 4 Replies View Related

Stored Procedure Migration And Security

Apr 30, 2008

I have an ASP page which is supposed to populate a dropdown based on values returned from a stored procedure. This ASP page is working on our old server. I recently moved all the pages, code, ect over to our new server including migrated to SQL Server 2005 (from 2000 I think), as you can tell, somthing broke...

I assume this is security related however, all the relevant security is set correctly as far as I can tell. I am granting "Execute" access for the stored procedure and "Select" access for all the referenced tables. Am I missing something? Is there another security setting which needs to be set?

View 1 Replies View Related

Security Hole In Stored Procedure

Jul 20, 2005

here's my stored procedure:CREATE PROCEDURE proc@id varchar(50),@pswd varchar(20),@no_go int OUTPUTASSET NOCOUNT ONSELECT user_id FROM profileWHERE user_id=@id AND pswd=@pswdIF @@ROWCOUNT = 0BEGINSET @no_go = 1ENDELSEBEGINSELECT date,date_mod FROM ansWHERE user_id=@idSET @no_go = 0ENDUsing the PERL odbc_more_results function I can retrieve the data inthe second select statement whether the rowcount is 0 or not. Anysuggestions how to stop this

View 3 Replies View Related

Stored Procedure Security Question

Jul 20, 2005

Dear GroupI'm having two stored procedures, sp_a and sp_bContent of stored procedure A:CREATE PROCEDURE dbo.sp_aSELECT * FROM aGOContent of stored procedure B:CREATE PROCEDURE dbo.sp_bSELECT * FROM bGOI have created a user that has execute permissions for both procedures.When I run procedure A, all works fine but when running procedure B I'mgetting an error saying that the user must have SELECT permissions on tableB.Both tables are owned by dbo, and the security role for the user doesn't hasany SELECT permission on table a and b.I'd be grateful if anyone could point me in a direction why this error mightcome up for procedure B but not for A,with a possible solution without giving the user SELECT permissions.Thanks very much for your help!Martin

View 1 Replies View Related

Stored Procedure Only Works In When App Uses Trusted Security

Dec 23, 2005

Hi,I have a .NET application that connects to a SQL 2000 database usingtrusted security. It eventually calls a stored procedure that receives3 parameters - nothing special.If I simply change the connection string to use a valid Userid andPassword it still connects to the DB w/o problems but when it executesthe SP I get the following:System.Data.SqlClient.SqlException: Invalid length parameter passed tothe substring function.I change nothing but the login. Same store procedure, same parameters.Any ideas?

View 6 Replies View Related

More Info: Stored Procedure Security Question

Jul 20, 2005

Dear GroupI have found that table A had SELECT permissions for 'Public' but not tableB.Giving 'Public' SELECT permissions on table B did the trick.HOWEVER, I don't want anyone to be able to do a direct SELECT on table A orB but only give them access to the data by using the stored procedures. Isthere any way this can be set up?Thanks for your efforts!Have a nice day!Martin"Martin Feuersteiner" <theintrepidfox@hotmail.com> wrote in message news:...[color=blue]> Dear Group>> I'm having two stored procedures, sp_a and sp_b>> Content of stored procedure A:> CREATE PROCEDURE dbo.sp_a> SELECT * FROM a> GO>> Content of stored procedure B:> CREATE PROCEDURE dbo.sp_b> SELECT * FROM b> GO>> I have created a user that has execute permissions for both procedures.> When I run procedure A, all works fine but when running procedure B I'm> getting an error saying that the user must have SELECT permissions on> table B.>> Both tables are owned by dbo, and the security role for the user doesn't> has any SELECT permission on table a and b.> I'd be grateful if anyone could point me in a direction why this error> might come up for procedure B but not for A,> with a possible solution without giving the user SELECT permissions.>> Thanks very much for your help!>> Martin>[/color]

View 6 Replies View Related

Dynamic Security Stored Procedure Repeatedly Called

Jan 26, 2007

I have implemented an SSAS stored procedure for dynamic security and I call this stored procedure to obtain the allowed set filter. To my supprise, the stored procedure is being called repeatedly many times (more than 10) upon establishing the user session. Why is this happening?

View 20 Replies View Related

Should I Use A View Or A Stored Procedure

Apr 5, 2007

I'm modifying a pretty big web application and the programmer who built it used all stored procedures and no views.  Does anyone know why someone would do this?  I realize that you can't pass parameters with views and insert/update/delete records with views, but he even used stored procedures for queries like: SELECT * FROM myTable WHERE myVal > 0 ORDER BY myVal Is it more efficient to put this in a stored procedure compared a view?  

View 1 Replies View Related

View Vs. Stored Procedure

Jul 7, 2000

Are there performace benefits to using a select from a View instead of a stored procedure that returns the same dataset? I am concerned about when we ramp up to 100's of users.

View 3 Replies View Related

Stored Procedure Vs View

Jan 23, 2006

I like the security of using stored procedures. It seems I am able to do anything with it that I can with a view. Why would I choose a view over a sproc?

View 3 Replies View Related

Stored Procedure Or View?

Mar 14, 2007

Hello all I am not quite a beginner but not an expert at SQL. I'm kind of in a bind and need some help. I have a table that shows me statuses of tickets (open, pending, closed), some tickets could have as much as 25 rows/ticket. I want to try to avoid that but at the same time keep track of the time. Here's what I need to happen...

with the data example below I need to take the ((closed date - first open date) - total of Waiting time). This will give me total time duration of the ticket. I'd like to either write a stored procedure or create a view that would do this for me. Any one have ideas?

CallID DateStopTimeStopCallStatus
002161772006-01-2005:39:24Open
002161772006-01-2005:39:27Open
002161772006-01-2005:40:13Open
002161772006-01-2005:40:24Pending
002161772006-02-0716:05:47Pending
002161772006-02-2117:26:22Pending
002161772006-02-2117:29:06Pending
002161772006-02-2117:29:08Open
002161772006-03-0316:35:10Open
002161772006-04-0515:12:26Open
002161772006-04-0515:17:09Open
002161772006-04-1414:37:49Open
002161772006-04-1414:37:54Awaiting
002161772006-04-1911:20:30Awaiting
002161772006-04-1912:12:34Awaiting
002161772006-04-1912:12:37Awaiting
002161772006-04-1912:12:58Awaiting
002161772006-04-1912:13:00Closed[/b]

View 20 Replies View Related

Stored Procedure From The View

Sep 25, 2007

How can I create a stored procedure that combines the results from three views, and puts them in a temp table?

View 20 Replies View Related

Using A Stored Procedure In A View

Jan 1, 2008

Hi all,
I want to know if there is a way to use a stored procedure in a view OR
a table value function OR
use the store procedure in table value function.

If any of these is a possibility, it would help. So far i have learnt that extended stored procedures can be accessed in table value functions.

Thanks.

View 1 Replies View Related

Using A Stored Procedure In A View

Jan 1, 2008

Hi all,
I want to know if there is a way to use a stored procedure in a view OR
a table value function OR
use the store procedure in table value function.

If any of these is a possibility, it would help. So far i have learnt that extended stored procedures can be accessed in table value functions.

Thanks.

View 1 Replies View Related

Question On Internal Activation Stored Procedure Security Context

Jun 25, 2006



CLR function has the following few lines which is invoked from Internal Activation Stored Procedure:

SqlCommand command = Connection.CreateCommand();

command.CommandText = "CREATE ASSEMBLY " + """ + AsmName + """ +" AUTHORIZATION [dbo]"+ " FROM " + "'" + regasm.UncPath + "'" + " WITH PERMISSION_SET=SAFE";

command.ExecuteNonQuery();

I am getting the following error:

"Could not impersonate the client during assembly file operation."

The CLR function is invoked from Service Broker internal activation stored procedure.

"SELECT user_name()" returns dbo just before CREATE ASSEMBLY execution.

SqlContext.WindowsIdentity.Name is "NT AUTHORITYSYSTEM" as the Data Engine runs with the LocalSystem account.

How do I create a the necessary security context for "CREATE ASSEMBLY" to succeed ?

Service Broker Queue activation with EXECUTE AS = "SELF", "OWNER", domain account or dbo, all result in the above error. The Service Broker assembly having the internal activation stored procedure is registered "unsafe".

Many Thanks.

View 13 Replies View Related

Help Coverting A Stored Procedure To A View

Aug 13, 2007

Can someone help me convert this stored procedure to a view? It is using two UDFs.
I appreciate this very much!@Start datetime,
@End datetime

AS

SELECT

C.Client_ID, (SUM(COALESCE(PR.AmountPaid,0))+ SUM(COALESCE(SC.SC_AMOUNT,0))) AS SumOfpmts, C.OrgName, C.FirstName, C.LastName, C.Sal1, C.Sal2, C.Sal3, A.Address, A.Address_Line2, A.City, A.State, A.Zip, A.Country, C.Title,
dbo

.getLevel(SUM(COALESCE(PR.AmountPaid,0))+ SUM(COALESCE(SC.SC_AMOUNT,0))) as pmtLevel,
dbo

.getLevelDesc(SUM(COALESCE(PR.AmountPaid,0))+ SUM(COALESCE(SC.SC_AMOUNT,0))) as Description
FROM

tblClients C INNER JOIN
tblPMTs P

ON C.Client_ID = P.Client_ID INNER JOIN
tblPMTReceipts PR

ON P.PMT_ID = PR.PMT_ID INNER JOIN
tblClientAddresses A

ON C.Client_ID = A.Client_ID LEFT OUTER JOIN
tblSoftCreditsPMTS SC

ON C.Client_ID = SC.SC_Client_ID

WHERE

(PR.PaymentDate BETWEEN @Start AND @End)
GROUP

BY C.Client_ID, C.OrgName, C.FirstName, C.LastName, C.Sal1, C.Sal2, C.Sal3, A.Address, A.Address_Line2, A.City, A.State, A.Zip, A.Country, C.Title

ORDER

BY pmtLevel
RETURN 

View 3 Replies View Related

Updating A View Through A Stored Procedure.

Oct 14, 2007

Hi i have a page in which a user fills out info on a page, the problem i am getting is that when the save button is clicked all text box values apart from one are saving to the database this field is the "constructor_ID" field. The save button performs a stored procedure, however there is a view which is doing something as well, would it be possible to write a stored procedure which would update the view at the same time?
CREATE PROCEDURE sp_SurveyMainDetails_Update
@Constructor_ID  int,@SurveyorName_ID int,@Survey_Date char(10),@Survey_Time char (10),@AbortiveCall bit,@Notes  text,@Survey_ID int,@User_ID int,@Tstamp timestamp out AS
 
DECLARE @CHANGED_Tstamp timestampDECLARE @ActionDone char(6)SET @ActionDone = 'Insert'
SET @CHANGED_Tstamp = (SELECT Tstamp FROM tblSurvey WHERE Survey_ID = @Survey_ID)IF @Tstamp <> @CHANGED_Tstamp --AND @@ROWCOUNT =0 BEGIN  SET @Tstamp =  @CHANGED_Tstamp  RAISERROR('This survey has already been updated since you opened this record',16,1)  RETURN 14 ENDELSE
   BEGIN
SELECT * FROM tblSurvey WHERE  Constructor_ID   = @Constructor_ID   AND  --Contractor_ID  = @Contractor_ID  AND  Survey_DateTime = Convert(DateTime,@Survey_Date + ' ' + LTRIM(RTRIM(@Survey_Time)), 103) AND  IsAbortiveCall = @AbortiveCall     IF @@ROWCOUNT>0                          SET @ActionDone = 'Update'
UPDATE tblSurvey SET    Constructor_ID   = @Constructor_ID   ,  SurveyorName_ID   = @SurveyorName_ID ,     Survey_DateTime = Convert(DateTime,@Survey_Date + ' ' + LTRIM(RTRIM(@Survey_Time)), 103) ,  IsAbortiveCall = @AbortiveCall ,  Note  = @Notes               WHERE Survey_ID = @Survey_ID AND Tstamp = @Tstamp IF @@error = 0 begin                        exec dhoc_ChangeLog_Insert    'tblSurvey',  @Survey_ID,  @User_ID,  @ActionDone,  'Main Details',  @Survey_ID
    end else BEGIN  RAISERROR ('The request has not been proessed, it might have been modifieid since you last opened it, please try again',16,1)  RETURN 10   END SELECT * FROM tblSurvey WHERE Survey_ID=@Survey_ID     
END
--Make sure this has saved, if not return 10 as this is unexpected error
--SELECT * FROM tblSurvey
DECLARE @RETURN_VALUE tinyintIF @@error <>0 RETURN @@errorGO
 This is the view;
CREATE VIEW dbo.vw_Property_FetchASSELECT     dbo.tblPropertyPeriod.Property_Period, dbo.tblPropertyType.Property_Type, dbo.tblPropertyYear.Property_Year, dbo.tblProperty.Add1,                       dbo.tblProperty.Add2, dbo.tblProperty.Add3, dbo.tblProperty.Town, dbo.tblProperty.PostCode, dbo.tblProperty.Block_Code, dbo.tblProperty.Estate_Code,                       dbo.tblProperty.UPRN, dbo.tblProperty.Tstamp, dbo.tblProperty.Property_ID, dbo.tblProperty.PropertyStatus_ID, dbo.tblProperty.PropertyType_ID,                       dbo.tblProperty.Correspondence_Add4, dbo.tblProperty.Correspondence_Add3, dbo.tblProperty.Correspondence_Add2,                       dbo.tblProperty.Correspondence_Add1, dbo.tblProperty.Correspondence_Phone, dbo.tblProperty.Correspondence_Name,                       dbo.tblPropertyStatus.Property_Status, dbo.tblProperty.Floor_Num, dbo.tblProperty.Num_Beds, dbo.vw_LastSurveyDate.Last_Survey_Date,                       dbo.tblProperty_Year_Period.Constructor_ID, dbo.tblProperty_Year_Period.PropertyPeriod_ID, dbo.tblProperty_Year_Period.PropertyYear_ID,                       LTRIM(RTRIM(ISNULL(dbo.tblProperty.Add1, ''))) + ', ' + LTRIM(RTRIM(ISNULL(dbo.tblProperty.Add2, '')))                       + ', ' + LTRIM(RTRIM(ISNULL(dbo.tblProperty.Add3, ''))) + ', ' + LTRIM(RTRIM(ISNULL(dbo.tblProperty.PostCode, ''))) AS Address,                       dbo.tblProperty.TenureFROM         dbo.tblPropertyType RIGHT OUTER JOIN                      dbo.tblProperty LEFT OUTER JOIN                      dbo.tblProperty_Year_Period ON dbo.tblProperty.Property_ID = dbo.tblProperty_Year_Period.Property_ID LEFT OUTER JOIN                      dbo.vw_LastSurveyDate ON dbo.tblProperty.Property_ID = dbo.vw_LastSurveyDate.Property_ID LEFT OUTER JOIN                      dbo.tblPropertyStatus ON dbo.tblProperty.Status_ID = dbo.tblPropertyStatus.PropertyStatus_ID ON                       dbo.tblPropertyType.PropertyType_ID = dbo.tblProperty.PropertyType_ID LEFT OUTER JOIN                      dbo.tblPropertyPeriod ON dbo.tblProperty.PropertyPeriod_ID = dbo.tblPropertyPeriod.PropertyPeriod_ID LEFT OUTER JOIN                      dbo.tblPropertyYear ON dbo.tblProperty.PropertyYear_ID = dbo.tblPropertyYear.PropertyYear_ID
   
 

View 1 Replies View Related

Is It Possible To Create A View From A Stored Procedure

Jan 3, 2008

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.

View 9 Replies View Related

When To Use ? Stored Procedure V/s View In SQL Server

Dec 6, 2005

hi,
Can someone tell me when to use SQL Server View as oppose to Stored Porcedure?
Currently we do everything with SQL Server stored procedure. I mean, even if we have to display some report, we use Stored Procedure.
In what situations and senarios views are better and one should consider them over Stored Procedure?

View 13 Replies View Related

View Creation Using Stored Procedure

Apr 21, 2001

Hi

I need to create a view using a stored procedure .

The task is to Upload multiple sql server tables sourcing data from flat files as well as SQL server tables .It is the process of Data migration.
After loading few tables,I need to create a view on thoes tables which can be used (queried )to load furthe tables.

I need to AUTOMATE THIS PROCESS .Means Once I schedule the job .It should take fire the stored procedures one after another .
I am thinking to create a view though a stored procedure .
You can suggest me alternate ways to do same .


Sujit

View 1 Replies View Related

Stored Procedure - Can I Use 4-part Name With A &#34;VIEW&#34;?

Aug 3, 2001

Joe, thank you for the answer to my post.

Can I also use a

SELECT field1 FROM DBName.dbo.TableName with a "VIEW" in this other database (that's on the same server)?

Also, in my sp, I have the following:

SELECT DISTINCT Store.[DemoID#], Progstats.ProgramName, Progstats.[Program#], ZCHAIN.STR_NAME, ZCHAIN.[STR#], ZCHAIN.ADDRESS,
ZCHAIN.City, ZCHAIN.ST, ZCHAIN.ZIP, ZCHAIN.[PHONE#], Store.D1, Store.Status, Store.AgencyCompleted,
Store.Reason, Store.LeadName, Store.DemonstratorName, Store.UpdatedOnline
FROM (Store INNER JOIN Progstats ON Store.[Program#] = Progstats.[Program#]) INNER JOIN ZCHAIN ON Store.[TD#] = ZCHAIN.[ID#]
WHERE (((Store.[DemoID#])=@DemoID)) AND Progstats.Status=1;
GO

ZCHAIN has now become this "VIEWZCHAIN" in this other database. So, could I simply relace "ZCHAIN" with "DB2.dbo.VIEWZCHAIN.STR_NAME" which is actually now a 4-part name?



Thanks,
John Wilson

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved