How To Set Store Procs And Table Rigths In One Shot ??
Jan 22, 2008
Dear all,
I am not so in database administration and I wold like to get some glue how to do what I am looking for.
I have a set of procedure and view in my Database HISTORY, then I define Grant the Execute rights to all those procedure for user NTAuthorityNETWORKSERVICE.
Then I have a second database named CONFIG where table might be accessed by store procedure of the database HISTORY. So first of all, in addition to set the EXECUTE grant access to my strore proces of database HISTORY, I need to set the SELECT grant access to all tables from database CONFIG on which are access by my storeprocs of database HISTORY.
First of all is it correct to set rights like this ?
Second, is there a way to set the access right of all my desire procedure in one shot ( script or something else ) becasue I have doen it manually and its painfull ?
If yes how to do that
thanks for help
regards
serge
View 4 Replies
ADVERTISEMENT
May 19, 2004
Is it possible that i can use a store proc to modify all the rest of my store procedures that i have in my DB ??
I have so many created allready and it will be to long to go throught each one of them to modify my text inside.
what i wish to do is a store proc that will allow me to loop to all my store proc of the current DB and look inside for specific text that i would like to change with the new value !!
any advise or example..
thanx.
View 4 Replies
View Related
Feb 19, 2008
I donot know how to use SQL NOCOUNT . I have written store procs for select and insert , Below are the store procs ,Can anyone tell me how to use SQL NOCOUNT in this StoreProcs???
Select:
if exists (select * from dbo.sysobjects where id = object_id('[dbo].[GetAllLinks]') )drop Procedure [dbo].[GetAllLinks]
GO
CREATE Procedure [dbo].[GetAllLinks]
AS
Select * from UsefulLink
Go
Insert Storeproc:
if exists (select * from dbo.sysobjects where id = object_id('[dbo].[InsertLink]') )drop Procedure [dbo].[InsertLink]
GOCREATE Procedure [dbo].[InsertLink]
@Title varchar(100),
@Location Varchar(100),@ID int OUTPUT
AS
Set @ID = -1Begin Transaction
Insert Into UsefulLink(Title, Location) Values
(@Title,@Location)if @@error <> 0
RollBack Transaction
Else
BEGIN
Commit Transaction
Select @ID = @@identity
END
Go
View 3 Replies
View Related
Sep 25, 2006
We are in the midst of cleaning up database01. There are objects in database01 that are widely used in store procedures that reside in database02.
Problem is: I need to know which tables and view from database01 are in use by store procedures in database02. This is important because if I accidently decommission tables that are currently in use by production class s-procedures, it will not be pretty. A sample output could look like:
StoreProc UsingObject
sp001 ctblCodeName
sp002 tblUnitSales
so003 tblSalesTrans
.......... .................
I am not a programmer and none of our programmers here claim that there is any solution to this problem without spending thousands on s/w licenses. Any simple solution + code snippet that will help me resolve this problem by myself would be incredibly valuable.
In disstress and frustration . . . .
JJOSHI
View 4 Replies
View Related
May 9, 2006
I need to create a database and then create tables in it..
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'DB1')
DROP DATABASE [DB1]
-- create a new database
CREATE DATABASE [DB1] ON PRIMARY
( NAME = N'DB1', FILENAME = N'D:DB1.mdf' ,
SIZE = 51200KB , MAXSIZE = UNLIMITED, FILEGROWTH = 30720KB )
LOG ON
( NAME = N'DB1_log', FILENAME = N'D:DB1_log.ldf' , SIZE = 2048KB , MAXSIZE = UNLIMITED , FILEGROWTH = 30720KB )
COLLATE Latin1_General_CI_AS
CREATE TABLE [DB1].[dbo].[SALES](
[PERIOD] [int] NOT NULL,
[LOC] [nchar](3) COLLATE Latin1_General_CI_AS NOT NULL
) ON [PRIMARY]
If i run the above statement one after one, they work; however, if I run all of them together (or in sp), the following error raised:
Msg 2702, Level 16, State 2, Line 43
Database 'DB1' does not exist.
May I know how can i create a database and then immediately the tables in sp!
Thanks
View 5 Replies
View Related
Sep 8, 2006
I need to run stored procs based on a list in a code table. In other words, it reads the name of a stored proc from a table and runs it and does this for all the rows in the table. I think the ForEach loop container will do what I need and there is an ADO enumerator option but the documentation does not tell you how to use this. From what I can tell, you need to get a dataset into an SSIS variable first and then you plug the variable name into the ForEach ADO enumerator. Is that correct? If so, can someone tell me how to get a dataset into a variable?
Thanks
View 1 Replies
View Related
Jan 17, 2006
Hello all ...
I'm looking at writing some customized insert, update and delete stored procs for a replication target. For various reasons I would like to write a "one size fits all" custom stored proc for each of these tasks.
It looks like I can get the data values passed as parameters just fine.
I was wondering if there's a way to also pass the source schema and table name as parameters, or to determine these on the fly in my all purpose stored procs. Some replication products refer to these types of values as "tokens" that can be included in the replication data stream sent to the target.
I can adjust the source database replication publications, and article definitions, but I cannot modify the actual source database tables to include these as values in data columns. It is possible a view that contains these elements as strings might fly, but I was hoping to avoid cluttering the source database.
A handy trick or technique would be helpful!
Thanks!
DB
View 3 Replies
View Related
Oct 12, 2004
SQL Server 2000:
Question 1
If you drop / rename a table and then recreate the table with the SAME NAME, what impact does it have on stored procedures that use these tables? From a system perspective, do you have to rebuild / recompile ALL the stored procedures that use this table?
I had a discussion with someone that said that this is a good idea, since the IDs of the tables change in sysobjects and from a SQL SERVER query plan perspective, this needs to be done...
Question 2
If you Truncate a Table as part of a BEGIN TRANSACTION, what happens if an error occurs? Will it Rollback? The theory is that it won't because Truncate doesn't utilize the logs where as Delete From uses the SQL Logs?
Thanks!
View 1 Replies
View Related
Oct 18, 2002
I have a table and want to add 20 more columns to it. I'd like to name these newly added columns such as fname1,fname2....
I don't want to type them mannully. I tried to use loop. But when I use "alter table add" in the loop, I couldn't find a way to substitute the column name in the loop.
Is there any other good solution to this?
Thanks
View 3 Replies
View Related
Oct 18, 2002
I have a table and want to add 20 more columns to it. I'd like to name these newly added columns such as fname1,fname2....
I don't want to type them mannully. I tried to use loop. But when I use "alter table add" in the loop, I couldn't find a way to substitute the column name in the loop.
Is there any other good solution to this?
Thanks
View 1 Replies
View Related
Oct 18, 2002
I have a table and want to add 20 more columns to it. I'd like to name these newly added columns such as fname1,fname2....
I don't want to type them mannully. I tried to use loop. But when I use "alter table add" in the loop, I couldn't find a way to substitute the column name in the loop.
Is there any other good solution to this?
Thanks
View 3 Replies
View Related
Jun 13, 2007
Hello. I was using the new sys.dm_db_index_operational_stats function which is nice for seeing counts of insert/update/delete actions per table index, bla bla bla... Anyways, question, can I do the same thing with Profiler? meaning, can I trace stored procs and sopmehow see the proc exec WITH each table it does actions against? Not talking about filtering on table names in the text, talking I just want to run an application, which uses all stored procs, and see every table used by that execution of the proc, and also the number of rows inserted,updated,deleted.... If so, which Profiler events/columns must I flick on to gather that? Thanks, Bruce
View 4 Replies
View Related
Oct 1, 2001
To anyone who knows how to use sybase. One of our guys has put in a sybase system and not told the rest of us about it and know their is a problem and it is in my lap. Surprise, surprise.
Just asking does antyone know how to rebuild a sybase database after it has been recovered from previous data and is now twice the size that it was origonally. Please help if any one can>
Many thanks to any one who can help.
View 1 Replies
View Related
Jan 11, 2008
how do i implement Snap shot replication..?
(IN SQL EXPRESS SERVER)
thanks.
View 1 Replies
View Related
Dec 1, 2006
I have one control table to store all related table name
Table ID TableName
1 TableA
2 TableB
In Table A:
RecordID Value
1 1
2 2
3 3
In Table B:
RecordID Value
1 1
2 2
3 3
How can I get the result by select the Table list first and then combine the data in table A and table B?
Thank you!
View 1 Replies
View Related
Jan 9, 2005
I have 1 table with a huge amount of data that I recive from someone else in a flat file format. I want to be able to filter through that data and scrub it and find out the good data and bad data from it.
I'm scrubbing the data using different stored procs that i've created and through a web interface that the user can pick which records they wish to create.
If I were to create a new table for clean records, what is the syntax to keep Appending to that table through the data that i'm obtainig via the stored procs that i've created.
Any thoughts or suggestions are greatly appriciated in advance
Thanks again in advance
RB
View 1 Replies
View Related
Jul 21, 2015
i have multiple folders in a directory and each folder contains multiple files of same extension but with different formats(columns) and names(xmp: file aand file b). We have a data task in which we are joining(merge) both files and loading into table..should i use foreach, but then it takes 1 file at a time and i need the other file also to join it in data flow.
View 4 Replies
View Related
Sep 20, 2004
i am having a problem with the merge replication.
has over 400 thousand records and is running on a hp server with a raid 5 configuration.
i am trying to create a merge replication on another server with the same configuration.
i am facing problems in 2 places.
firstly i created a push subscription and ran the snap shot agnt.
but the snap shot agent times out and expires .. as a result of which i am not able to create the initial snap shot on the subscribers end.
next i thought i would manualy create a snap shot
and so i backed up the dabase from the publisher and restores it on the subscription side.
now i tried to create a new subscription by chosing the option... the dabase alredy conists of the data base and schema
and i get a n error saying
"SQL Server Enterprise Manager encountered errors creating push subscriptions for the following Subscribers:
ICE: Error 20086: Publication '%s' does not support the nosync type because it contains a table that does not have a rowguidcol column
what do i do to solve this problem....
please try and give me a solution to solve either ways.
thanks,
--ggupta
View 9 Replies
View Related
Jan 15, 2008
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.
View 1 Replies
View Related
Jun 25, 2007
Hi, all experts here,
I am wondering if tempdb stores all results tempararily whenever I query a large fact table with over 4 million records which joins another dimension table? Since each time when I run the query, the tempdb grows to nearly 1GB which nearly runs out all the space on my local system drive, as a result the performance totally down. Is there any way to fix this problem? Thanks a lot in advance and I am looking forward to hearing from you shortly for your kind advices.
With best regards,
Yours sincerely,
View 11 Replies
View Related
Dec 25, 2014
I need to capture changes in my table and store it in another table. Here is how my table would look like.
CREATE TABLE [dbo].[ABC](
[Hno] [nchar](10) NULL,
[Status] [smallint] NULL,
[Date] [datetime] NULL
) ON [PRIMARY]
[Code] ....
In return I need to check column Status for whenever the value has changed and need to store that in my table. If there are two records for which value in column Status is same, I need to pick only one of the records and that being the earliest of them and therefore the Date field is mentioned in my table . My output should be something like below.
100 02014-12-20 00:55:44.667
100 12014-12-22 00:55:44.723
100 02014-12-24 00:55:44.723
100 12014-12-26 00:55:44.723
View 4 Replies
View Related
Aug 18, 2015
How to purge data in transaction table or we can delete some data and store in separate table in data warehouse?
View 7 Replies
View Related
Dec 3, 2007
Hi guys, now i was able to get the results of the query i made. and the remaining problem now is how can i store the results to my database? here's the my code: protected void btnGenerate_Click(object sender, EventArgs e) { _con = new SqlConnection(database.evgConnection()); _comm = new SqlCommand("EV_usp_SearchNoticeLocation", _con); _comm.CommandType = CommandType.StoredProcedure; _comm.Parameters.AddWithValue("@Locationid", ddlLocation.SelectedValue); _adapt = new SqlDataAdapter(_comm); DataSet objDataset = new DataSet(); _adapt.Fill(objDataset); GridView1.DataSource = objDataset; GridView1.DataBind(); foreach(DataRow dr in objDataset.Tables[0].Rows) { Response.Write(dr["Docownerid"].ToString()); } } how can i store all the Docownerid to another database table?
View 1 Replies
View Related
Aug 13, 2002
How do I store a PDF in a table column?
for example
I want to store "c:est.pdf" in myTable.ColumnName
Please help!
View 3 Replies
View Related
Aug 9, 2007
Hi,
I have a question. I have a standard query that retrieves the list of users from a database. The query uses "exist" statement and inside it I have another smaller query. The smaller query which used withing exist statement is called numerous times during the query execution. I am wondering if I can speed things up by storing result of a smaller query in a table rather than asking DB to run it again and again. The query takes over a minute to run and I am trying to find a way of speeding it up.
Thanks a lot
Serge
View 2 Replies
View Related
Jun 8, 2006
Hello, Folks. I am not sure if this can be done (and a brief search ofthis group didn't yield any results that I thought would do the trick)but if it can be done it would make my life easier so here goes.First, what I am trying to accomplish is to make a program I've writtenautomatically update itself if there is a newer version on the network.Second, my application runs on one or more computers at manygeographically separated locations. I have remote control softwarethat enables me to remotely update and troubleshoot my application butI since I have to pay for this access per computer I don't have theability to control each and every computer that runs my software (forthose clients with more than one computer that uses my software).Third, my application uses as its data storage system MSDE 2000 (withcurrent service pack).Fourth, my users are not technical people and I want to rely on them aslittle as possible.Fifth, not all of the workstations are Internet enabled or connected,so having the application check a website for an update is notfeasible.Finally, to bring those different pieces together, what I want to beable to do is connect to and control the "server" (the computer withMSDE installed on it) and store in the database what the currentversion number is along with the current program files. Then, when theapplication launches, it will check that stored version number againstit's own version number to determine if there is a newer versionavailable. If there is a newer version available I want it to updateitself (that workstation) by retrieving that newer software from thedatabase. I want to avoid file sharing because it's problematic andsince I already have SQL Server (MSDE) running on the network I'd liketo use that.Is there some way that I can either a) store my program files (one EXEapproximately 5MB in size and one OCX approximately 250KB in size) in atable to be retrieved by my application so it can update itself or b) away to get SQL Server (MSDE) to read a file locally on the server andreturn it to my application just like it would any recordset so I couldjust tell it where the file was and let it read it from whicheverfolder it's stored in?I've looked at making a column of datatype Binary, and perhaps that'sthe way I'll go (re-creating the field to the correct size each time Ido an update or perhaps making it really big so I don't have to worryabout the size). But I thought I'd ask around first since this isn'tanything I know much about.Thank you for your time and help.--HC
View 2 Replies
View Related
May 7, 2004
HI,
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 ?
CREATE PROCEDURE PROC2
AS
BEGIN
CREATE TABLE #SAATHI(INTCUSTOMERID INT,CHREMAIL NVARCHAR(60),INTPREFERENCEID INT,CHRPREFERENCEDESC NVARCHAR(50))
INSERT INTO #SAATHI
EXEC PROC1
ST......1,
ST......2,
END.
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 .
CREATE PROCEDURE PROC2
AS
BEGIN
CREATE TABLE #SAATHI(INTCUSTOMERID INT,CHREMAIL NVARCHAR(60),INTPREFERENCEID INT,CHRPREFERENCEDESC NVARCHAR(50))
INSERT INTO #SAATHI
EXEC MY_PROC
ST......1,
ST......2,
END.
BUT, HERE I WANT TO USE FIRST DATASET ONLY , HOW TO USE IT ?
THANKS.
View 4 Replies
View Related
Jan 14, 2007
Hello All,
My question is how to store images in a database table (in SQL Server), and how to retrieve them?...
Thanks,
View 2 Replies
View Related
Apr 16, 2007
How can I store more than one category in a products table?
For exampe: I have a dvd website where the admin can add new dvd's. On the website all the categories are listed with a checkbox. If the dvd is a action comedy the admin have to check these two checkboxes. But how do I store that in the sql database an retrieve it?
Thanks
David
View 3 Replies
View Related
Jul 7, 2005
I'm writing a search engine and I want to make a search and then, after
I've recieved the results, put them into some sort of temporary(in
mememory) table so that I can do another query on that temporary table.
I saw something about temp tables (in T-SQL) in the
help guide, but I still want to know how to do a query on a regular
table and then store those results in a temporary table to perform a
query on that. Could anyone show me some example or something?
View 3 Replies
View Related
May 10, 2006
I would like to store and read .doc file in a table of sql server using C#.
Can some body help me on this? my id is padam76@gmail.com.
Thanks & Regard,Padam Kumar Tripathi
View 1 Replies
View Related
Sep 24, 2001
Can you store images in a database or in a table, or do you have to only store the image path/name in the database while the image is in another folder? IF anyone knows, please share with me. Thanks.
View 2 Replies
View Related
Mar 5, 2007
Hi
I am new to SQL programming. This is what I am trying to do in store procedure.
Alter a table - adding two columns.
Then update those columns
Both the above action in same procedure .
When I execute them - it complains that the columns I am adding in the first part of store procedure , does not exists.
So the SP looks like this
create store procedure <name> as
alter table <name> ADD
column_1 nvarchar (256),
column_2 nvarchar (256);
update table
set
column_1 = <condition>
column_2 = <condition>
The SQL complains -
invalid column name column_1
invalid column name column_2
Can some one help please...
View 3 Replies
View Related
