Users Cannot View Stored Procedure Text

Apr 4, 2007

In SQL 2005 (we use Enterprise 64-bit SP2), the users cannot see the
text of the stored procedures, functions, etc.

This is a production database, so I cannot give them rights to modify
them, but they need to be able to see what the procs are doing.

I didn't have this problem in 2000 - how can I adjust the privileges
to allow them to view the contents of the Programmability objects?

thanks for any insight!!
Tracy

View 2 Replies


ADVERTISEMENT

See Application Users (View Server State) Within A Stored Procedure

Mar 17, 2008

Hello, i have a problem regarding stored procedures and view server state.

I have an application with a lot of stored procedures, one of them checks data of the connected users.
In SQL 2000 i had no problem getting this information, but in SQL server 2005 i do.

my stored procedure looks like this:


ALTER PROCEDURE [dba].[applsp_GetConnectionInfo]

(

@DBName varchar(100)

)

WITH EXECUTE AS OWNER AS

BEGIN

SET NOCOUNT ON



DECLARE @sCollationMaster VARCHAR(128);

DECLARE @sSqlString VARCHAR(900);



-- Determine collation from master database because collation from master and ultimo database may differ

SELECT @sCollationMaster = CAST(databasepropertyex('master', 'Collation') AS VARCHAR);



SET @sSqlString =

'SELECT max(status) AS Status, max(isnull(SCISUSENAME, ''ULTIMOLOGIN'')) AS Login

, MAX(Rtrim(Rtrim(convert(varchar(255), nt_domain)) + nt_username)) AS NTUser

, max(Rtrim(hostname)) AS Host, MAX(Rtrim(program_name)) AS Program

FROM master.dbo.sysprocesses JOIN dba.SCONNECTIONINFO on SCISPID = CAST(spid AS VARCHAR)

AND ( SCISUSENAME = ISNULL(loginame, '''') COLLATE ' + @sCollationMaster + ' OR ISNULL(loginame, '''') = ''ULTIMOLOGIN'')

WHERE ...... AND DB_NAME(dbid) = ''' + @DBName + '''

GROUP BY hostprocess

ORDER BY Login

';



EXEC(@sSqlString);

END

I've granted view server state permissions to my user 'dba' which is the db_owner.
When i execute the query in the stored procedure seperatly as dba i get all the info i need, but when i execute the stored procedure i don't see anything.

I seem to have the same problem with sp_who2
Executing it gives me information about everyone but when i put in a stored procedure like this:

alter procedure test

with execute as owner as

begin


EXEC sp_who2

end
I just see information about myself


View 5 Replies View Related

Stored Procedure To Get All Users Or All Users Of A Specific Country

Apr 30, 2008

hi alli've got two tables called "webusers" (id, name, fk_country) and "countries" (id, name)
at the meantime, i've a search-page where i can fill a form to search users. in the dropdown to select the country i included an option which is called "all countries".
now the problem is: how can i make a stored procedure that makes a restriction to the fk_country depending on the submitted fk_country parameter?it should be something like
SELECT * FROM webusers(if @fk_country > 0, which is the value for "all countries"){    WHERE fk_country = @fk_country}
who has an idea how to solve this problem?

View 9 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 To Grant All Users Db_owner

Jul 20, 2005

I am trying to come up with a stored procedure to grant all usersdb_owner role (rather than have to manually check the users eachtime)Thanks in advance for your helpTY

View 1 Replies View Related

Transact SQL :: Stored Procedure For Total Count For New Users

Jul 14, 2015

I need to create a stored procedure for total count of the user's. If  User from front end  invites other user to use my tool, that user will be stored into a table name called "test",lets say it will be stored as"Invited 1 User(s)" or if he invites 2 users it will store into table as "Invited 2 User(s)."

But now we have changed the concept to get the ISID (name of the user)  and now when ever the user invites from the front end, the user who have invited should stored in two tables "test" and " test1" table .

After we get the data into test and test1 table i need the total count of a particular user from both tables test and test1.

if i invite a user , the name of the user is getting stored in both test and test1 tables.Now i want to get the count of a user from both tables which should be 1,but its showing 2.

Reason: Why i am considering the count from 2 tables is because before we were not tracking the usernames and we were storing the count in single test table.,but now we are tracking user names and storing them in both tables(test and test1).

Here is my sample  code:

I need to sum it up to get the total user's from both the table but I should get 1 instead of 2 

SELECT
(select distinct COUNT(*) from dbo.test
where New_Values like  '%invited%'
and Created_By= 'sam'
+
(select distinct count (*)  from dbo.test1
where invited_by ='sam'

View 8 Replies View Related

1 To 1, 1 To Many. Create One Stored Procedure For Crystal Reports(CR). For Any CR Users Also.

Jul 12, 2007

Situation: If possible, create one stored procedure for Crystal Reports(CR). For any CR users ou
there, looking for coding suggestion also. Thank you for your assistance.

Currently, CR has a header(Report Header) coming from 1 to 1 tables, there is a parameter
which is passed in, allowing it to retrieve one record for the header(report header or RH).

The CR then has 4 subreports in which each has its own stored procedure. This I believe happens
because the Report Header records relationship to the subreport is 1 to many. The 3 remaining
subreports relationship to the Report Header is also a 1 to many. The main problem is the
subreports is that there may or may not have any records based on this, the subreport is
suppressed within CR and thus there can be alot of unused white space on the 1st page and one
of the subreports prints on a 2nd page when it could have been on the 1st page.

Example:

Main Stored Procedure(sp)
RH Tables: aaa, bbb, ccc, ddd are 1 to 1 record tables and have a @xyz parameter.

Results of subreports and their associated procedure are varchar(8000) decriptions. Each line
should be counted in some manner in the stored procedure(sp) and then should be counted in CR
to avoid excess white space. To complicate matters subreport 2 to has font, bold, showbox but
can have different font sizes. These variations could cause different line space requirements.
Any ideas?


Each line should be counted in some manner in the stored procedure and then should be counted
in CR. There is a count of records for each the main stored procedure.

RH has 1 record to many records in subreport 1 with same @xyz parameter.
RH has 1 record to many records in subreport 2 with same @xyz parameter.
RH has 1 record to many records in subreport 3 with same @xyz parameter.
RH has 1 record to many reords in subreport 4 with same @xyz parameter.

Because of the relationships, its seems impossible to create one stored procedure which give
in one select statement with all the 1 to 1, 1 to many relationsips, as stated above. I thought
concatinating i.e. 3 records together and then parsing it out some how in CR, along with
utilizing the i.e. 3 record count to help count lines. Thought of some how creating a temporary
table matrix for 8pt - 28pt for line and spacing considerations.







View 1 Replies View Related

Creating Stored Procedure To Send Email To Multiple Users

Sep 20, 2007

Hi Everybody,

I am trying to setup a stored procedure that runs through a Reminders table and sends an email to users based on DateSent field being smaller than todays date. I have already setup the stored procedure to send the email, just having trouble looping through the recordset.




Code Snippet


CREATE PROCEDURE [dbo].[hrDB_SendEmail]

AS

BEGIN


DECLARE @FirstName nvarchar(256),

@LastName nvarchar(256),

@To nvarchar(256),

@ToMgr nvarchar(256),

@Subject nvarchar(256),

@Msg nvarchar(256),

@DateToSend datetime,

@Sent nvarchar(256),

@ReminderID int,

@RowCount int,

@Today datetime,

@Result nvarchar(256)

-- Get the reminders to send


SELECT

@ReminderID = r.intReminderID,

@DateToSend = r.datDateToSend,

@FirstName = e.txtFirstName,

@LastName = e.txtLastName,

@To = e.txtEmail,

@Subject = t.txtReminderSubject,

@Sent = r.txtSent

FROM


(auto_reminders r INNER JOIN employee e ON r.intEmployeeID = e.intEmployeeID) INNER JOIN ref_reminders t ON r.intReminderType = t.intReminderTempID

WHERE


(((r.datDateToSend)<20/12/09) AND

((r.txtSent)='False'))

-- Send the Emails


WHILE(LEN(@To) > 0)

BEGIN


EXEC @Result = sp_send_cdosysmail @To, @ToMgr, @Subject, @Msg

END

-- Mark the records as sent

IF @Result = 'sp_OAGetErrorInfo'


BEGIN


SELECT @Sent = 'Error'

END
ELSE


BEGIN


SELECT @Sent = 'True'

END
UPDATE auto_reminders

SET


auto_reminders.txtSent = @Sent, auto_reminders.datDateSent = @Today

WHERE


intReminderID = @ReminderID

END

GO







From the code you can probably tell I am new to writing stored procedures, so I apologise for any obvious errors. My major problems are :-


how to loop through each record

how to get todays date

whether the struture of the procedure is correct
Also, if you think there is an easier way or a better method, please suggest it. I am open to any suggestions you may have,

Thanks in advance
Ben

View 1 Replies View Related

SQL 2012 :: Allow Low Privilege User To Execute Stored Procedure To Create DB Users

Jul 22, 2014

I have a requirement to allow a user to restore a database and then create database users and add them to the db_owner database role. The user must not have sysadmin rights on the server.

The database restore works ok by placing the user in the dbcreator role.

There is a stored procedure to create the database user and alter role membership, I want the user to execute the sp as a different, higher privilege account so as not to give the user underlying permission to create users in the database.

USE [master]
GO

/****** Object: StoredProcedure [dbo].[sp_create_db_users] Script Date: 22/07/2014 13:54:46 ******/
SET ANSI_NULLS ON
GO

[Code] ....

The user has execute permission on the stored procedure but keeps getting the error:

Msg 916, Level 14, State 1, Line 2

The server principal "Mydomainadmin1" is not able to access the database "Mydatabase" under the current security context.

Mydomainadmin1 has dbowner to Mydatabase and sysadmin rights for server. If the 'execute as' is changed to 'caller' and run by mydomainadmin1 it works so the issue is between the execute sp and the actual running of the procedure.

View 1 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

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

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

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

Accessing A 'View' From Within A Stored Procedure

Jun 7, 2004

Hiya folks,
I'n need to access a view from within a SProc, to see if the view returns a recordset and if it does assign one the of the fields that the view returns into a variable.

The syntax I'm using is as follows :

SELECT TOP 1 @MyJobN = IJobN FROM MyView

I keep getting an object unknown error (MyView). I've also tried calling it with the 'owner' tags.

SELECT TOP 1 @MyJobN = IJobN FROM LimsLive.dbo.MyView

But alas to no avail!


Any offers kind people??

View 12 Replies View Related

Call Stored Procedure From View

Sep 7, 2006

Hello all,
does anyone know if it's possible to call a stored procedure from a view.

Thnx,
Patrick

View 9 Replies View Related

Creating A View In A Stored Procedure

Apr 2, 2004

Hi Everyone

Im trying to create a view from within a stored procedure and are having problems. Is it possible to do this? And if so, how? I've been trying with the code below.

CREATE PROC upProcName AS

DECLARE @Variable varchar(50)

CREATE VIEW vwName AS

SELECT DISTINCT Table1.*, Table2.*
FROM dbo.Table1
INNER JOIN dbo.Table2 AS BUG
ON Table1.Col1 = Table2.Col1
WHERE LI.accname = @Variable

GO

Any Thoughts ideas would be great

Cheers

View 7 Replies View Related

Need Help To Create A View/Stored Procedure

Jun 24, 2008

Experts

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

Any advice?

View 4 Replies View Related

Create View Within A Stored Procedure

May 21, 2007

hi,

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?

thank you

View 20 Replies View Related

Creating A View From A Stored Procedure

Sep 19, 2007

HelloNewbie here.Is there a way of creating a VIEW...using a stored procedure. I ambasically trying to create a view to return some data that I amgetting using a stored procedure.I have created the procedure and when I execute this its working ok.The stored procedure uses a datefrom and dateTo which I have set up bytweaking the getdate() and getdate()-2.In other words can you create a view like thisCREATE VIEW view_testASexec proc_testGOAny help will be greatly appreciated.Remmy

View 1 Replies View Related

Cretae A View In One DB With A Stored Procedure From Another DB

Jul 20, 2005

Hi,Ik created an application with visuals basic.NET. This has aconnection string to one database, let's say 'A'. In this database astored procedure is called which should execute a string (which ispassed by the) VB tool. This string is a CREATE VIEW statement en thisshould be executed in another database let's say 'B'.I tried this in Transact - SQLEXEC('USE B;' + Query)An error occurs : CREATE VIEW should be the first in a batchedstatement.Could anyone help me with this one?Greetz,Hennie

View 1 Replies View Related

View Stored Procedure Permissions

Jul 20, 2005

We are running SQL Server 2000 Developer Edition. I don't want tomake the developers the sysadmin or even the dbo in the userdatabases. Is there a way to give them access to only view thepermissions for the stored procedures in the user database withoutmaking them dbo?When I take them out of the db_owner role, when they open a storedprocedure they no longer see the permissions tab. I would like forthem to see the permissions tab and be able to view the permissionsbut not change the permissions.Is that doable?

View 1 Replies View Related

Can I Add Alter View In The Stored Procedure?

May 13, 2008

Hi,
I am using SQL 2000( Version: 8.00.000)
I want to ask can I add View in the stored procedure like the following:


Create Procedure sp_createTblLogReasonColorView
@m int,
@y int
AS
BEGIN

ALTER View vw_tblLogReasonColor
AS
Select * From tblLogReasonColor where Year(n_Delivery_Date) = @y AND Month(n_Delivery_Date) = @m

END

Thanks for you reply.

View 1 Replies View Related

Can I Call A Stored Procedure In View?.

May 5, 2008



Is it possible to call a soted procedure in view?.

If so, how to call?.

Please post syntax.

Thanks

View 7 Replies View Related







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