How To Make JOB Without Xp_regread Procedure
Sep 20, 2005Hi!!
I removed 'xp_regread' procedure for security
after that I can't make JOB in Enterprise Manager
How can I make JOB without 'xp_regread' procedure
thanks~
ADVERTISEMENT
Xp_regread In SQL 2005
Jun 19, 2007I'm having a problem making a call that still works in SQL 2000.
When I call xp_regread, I get the following error:
Msg 22001, Level 16, State 1, Line 0
xp_regread() returned error 5, 'Access is denied.'
My calls work fine when I'm connected normally (either as myself with NT authentication or SQL authentication), but my application is using an application role to provide access to all of the tables and stored procedures. When I set the application role (using sp_setapprole), my call fails. I call a stored procedure in my database, which, in turn, calls master.dbo.xp_regread.
SQL Server is running as Local System.
I don't know what registry permissions I do or don't need in order to get this to work.
Given the error isn't regarding EXECUTE permissions on the procedure, I assume that it is a registry permission issue, not an issue with running the SP.
I found http://support.microsoft.com/kb/887165 related to a similar issue in SQL 2000, but there is no such registry setting when SQL 2005 is installed.
The problem is present in both Vista and WinXP, so operating system doesn't seem to be playing a factor.
I'm creating the Application Role in a SQL2000 compliant manner (EXEC sp_addapprole N'MattAppRole', N'Password123'), and activating it in a similar manner (exec sp_setapprole N'MattAppRole', {ENCRYPT N'Password123'}, 'odbc'). I'm not sure if that matters or not.
If anyone has any ideas what might be causing this problem, I'd really appreciate the help.
Matt
Determining Public Access To Xp_regread And Xp_instance_regread
Aug 31, 2007I am trying to write a query to determine if public role has access to both of these. I need this to satisfy auditing requirements. If anybody knows how to write the t-sql to turn these on, I should be able to use the sql to find out if public role has access to these. Or if you just have a script already or a sp that would be fantastic also.
Thanks
Kyle
Make A Stored Procedure
Jan 7, 2007Hi
In my table i have a datetime field
now i want to delete all records there are more than 1 hour old
can someone help me with this
Alvin
Make Procedure For Stock-quantity
Sep 3, 2007helo alll...,this is my data:my table is item(productid,stock) ,order(customerid,no_order), and orderdetail (no_order,productid,quantity) example: order and orderdetail displayed in gridview....order is displayed like this: customerid no_orderdetail A 1detail B 2 when i click detail in row 2, it's display orderdetail:no_order productid quantity 2 c1 2 2 p1 3 i have make all this is ok. but i want to decrease stok in item with quantity in orderdetail.my code in procedure like:CREATE PROCEDURE [dbo].[order_item](@productid AS varchar,@quantity AS INT)ASBEGINBEGIN TRANSACTIONDECLARE @no_order AS INTDECLARE @stock AS INTINSERT INTO [Orderdetail]([ProductId],[Quantity])VALUES(@productId,@quantity)SET @no_order = SCOPE_IDENTITY()SET @Stock = (SELECT [Stock] FROM [item] WHERE [ProductId] = @productId)UPDATE [item]SET[Stock] = @Stock - @quantityWHERE[ProductId] = @productIdCOMMIT TRANSACTIONENDreturn it can't work..how about his true code in store procedure?ok..., thx..
View 8 Replies View RelatedMake A Call To AS/400 Stored Procedure From DTS
Feb 15, 2000Can DTS make a call to a stored procedure on an AS/400 and accept data from that call. I need to access the AS/400 through OLE/DB for AS/400, execute the call to a stored procedure (the AS/400 stored procedure gets the data from DB2/400, executes some business logic, then presents the record set), and grab the record set returned and dump it into a SQL 7.0 table.
View 1 Replies View RelatedHow To Make BAT File Of Stored Procedure
Mar 29, 2013I have a bulk insert script. I have a stored procedure. I saved the stored procedure as a .sql file in another folder too. I have another program that can "run external program" and the only files it will run is a .bat or .exe. I want my other program to be able to trigger the stored procedure to run. I think this means I need a .bat file.
Here is my stored procedure:
USE [EricaTraining]
GO
/****** Object: StoredProcedure [dbo].[LoadDailyAdjReport] Script Date: 03/29/2013 10:56:42 ******/
SET ANSI_NULLS ON
GO
[code]....
Make The Stored Procedure To Run Daily
Jan 22, 2008
Hi,
I have created a stored procedure that will read the content of the text files of a particular folder. I need to make the stored procedure to run daily so that it will read the new files that is present in that folder. I have written a stored procedure to make the process of reading the file. But i need to know how to make the stored procedure to run daily so that it will automatically read all the files. I have got the information that it can be made possible using dts package. As i dont have any knowledge about dts package can anyone help me how to make this possible.
Thanks in advance for any help.
Regards,
Sangeetha
Make Procedure To Check Balance &> Or &< Totalcost
Sep 9, 2007 helo all...,i have create procedure can decrease totalcost from order table(database:games.dbo) with balance in bill table(database:bank.dbo). my 2 database in same server is name "boy"
i have 2 database like: bank.dbo and games.dbo
in games.dbo, have a table name is order(user_id,no_order,date,totalcost)
in bank.dbo, have a table name like is bill(no_bill,balance)
this is a list of bill table
no_bill balance
111222 200$
222444 10$
this is a list of order table
user_id no_order date totalcost
a 1 1/1/07 50$
when customer insert no_bill(111222) in page and click a button, then bill table became
no_bill balance
111222 150$
222444 10$
when customer insert no_bill(222444) in page and click a button, then message "sorry, your balance is not enough"
mystore procedure like:ALTER PROCEDURE [dbo].[pay]( @no_bill AS INT, @no_order AS int, @totalcost AS money)ASBEGIN BEGIN TRANSACTION DECLARE @balanc AS money SET @balanc= (SELECT [balance] FROM Bank.dbo.bill WHERE [no_bill] = @no_bill) UPDATE [bank.dbo.bill] SET [balance] = @balanc - @totalcost WHERE [no_bill] = @no_bill COMMIT TRANSACTIONEND it can decrease money in bank, but i want it ceck money if balance > totalcost, so balance-totalcost,if balance<totalcost,so error message"sorry, your balance not enough"is it can make in procedure?thx...
Stored Procedure To Make Backup Of Database...
Dec 11, 2006Can anyone point me to the right direction with the stored procedure on making a backup of the database. I am not looking for a scheduled backup. I'm looking for when the stored procedure get executed, the backup start right away. I believe it also require a username and password as well.
Thanks...
Stored Procedure - How To Make View Name Dynamic
May 8, 2012Basically, I'm working on a stored procedure which will retrieve data based on study parameter passed. The datasource is 'Views'. The name of the view is same for every study except that there is corresponding study name included. For example the views names are something like this for study abc 'v_abc_form' and for study def 'v_def_form'.
Below is the select statement I'm trying to use by declaring @study variable but not able to succeed. I'm not sure how to make the table name dynamic.
Select C1, C2, C3
From v_@study_form
T-SQL (SS2K8) :: How To Make Code Into Cursor Within Procedure
Feb 3, 2015i wanna create a procedure for P& L cost sheet , i had done that procedure now include a cursor instead of replacing sql queries .
create procedure pl_test
@fmdate datetime,
@todate datetime,
@categ varchar(2000)
begin
create table #temp
[code]....
how to include cursor on if part and else part
I Need To Make This Stored Procedure 2005 Compatible
Jun 22, 2006Hello.
I need to quickly make this proc compatible with SQL 2005 and am struggling. I have alot of catching up to do.
Basically, it checks for Foreign Key dependencies in a database. There might be a better way to do this in SQL 2005 but for know I really need to get this working. Any help is verry much appreciated!
--------------------------------------------------------
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER Procedure aes.Check_Dependent_Rows_Exist
(@RowID int,
@has_rows int OUTPUT
)
AS
BEGIN
DECLARE @Colname varchar(200), @Tablename varchar(200)
DECLARE @cnt int
DECLARE @temp_row int
DECLARE @owner varchar(25)
DECLARE @ownerid int
DECLARE @lstrSql nvarchar(2000)
-- #1: declare cursor for maximum performance
DECLARE lcur CURSOR LOCAL FORWARD_ONLY KEYSET READ_ONLY FOR
SELECT syscolumns.Name, OBJECT_NAME(fkeyid) AS FkeyTableName
FROM sysreferences
INNER JOIN syscolumns ON sysreferences.fkeyid=syscolumns.id AND fkey1=syscolumns.colid
WHERE OBJECT_NAME(rkeyid)= 'customer'
OPEN lcur
CREATE TABLE #Temp (DependentRows int)
-- #2: only return a bit indicating if dependant rows exist or not
SET @has_rows = 0
FETCH NEXT FROM lcur INTO @Colname,@Tablename
WHILE @@FETCH_STATUS = 0
BEGIN
SET @temp_row = 0
SELECT @ownerid = uid from sysobjects where name = @Tablename
SELECT @owner = [name] from sysusers where uid = @ownerid
SET @lstrSql= 'insert into #Temp Select DependentRows = Count(' + @Colname + ') from ' + @owner + '.' + @TableName + ' where ' +
@Colname + ' =' + CAST(@RowID AS VARCHAR(16)) + ''
--print @lstrSql
EXEC (@lstrSql)
SELECT @temp_row = ISNULL(DependentRows,0) FROM #Temp
IF @temp_row > 0
BEGIN
-- #3: stop processing as soon as dependant rows are found to exist
SET @has_rows = 1
BREAK
END
FETCH NEXT FROM lcur INTO @Colname,@TableName
END
deallocate lcur
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-----------------------------------------------------------------------
error
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.order_detail'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.invoice_header'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.order_header'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.payment'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.cash_on_account'.
(1 row(s) affected)
Cannot grant, deny, or revoke permissions to sa, dbo, information_schema, sys, or yourself.
How To Make A Stored Procedure Into SSIS Package
May 21, 2008
hi,
I need to convert a stored procedure in to a SSIS package, do any body have an idea on this. thanks in advance
Make A Dynamic Cursor In A Stored Procedure
Jul 9, 2006I need im my aplication to meke a "Cursor" in a execution of a stored procedure.
For sample:
In a table with a report definition I have the "Fileds, From, Group, Order " clausulas and I need make a cursor with a contents of this fileds.
How can I do ???
My code:
Declare @idRelat int, @cmd_FROM nvarchar(1024), @cmd_Det nvarchar(50)
SELECT @idRelat = idRelat, @cmd_Det = cmd_DET
FROM Relatórios WHERE Nome = @p_Relat
Declare @Tot_Col smallint, @Tot_Lin smallint, @Campos smallint,
@Aux_Select nvarchar(1024), @Aux_Group nvarchar(1024), @Aux_Order nvarchar(1024)
Select @Tot_Col = 0
Select @Tot_Lin = 0
Select @Campos = 0
Select @Aux_Select = "SELECT " + @cmd_DET + "AS Soma"
Select @Aux_Group = "GROUP BY "
Select @Aux_Order = "ORDER BY "
Declare @a_Local char(1), @a_Linha smallint, @a_Campo nvarchar(50)
Declare cur_Aux insensitive cursor for
SELECT Local, Linha, Campo
From Relatórios_Margens
WHERE (idRelat = @idRelat)
ORDER BY Local, Linha
Open cur_Aux
Fetch cur_Aux into @a_Local, @a_Linha, @a_Campo
While @@FETCH_status = 0 begin
If @a_Local = "C"
Select @Tot_Col = @Tot_Col + 1
Else
Select @Tot_Lin = @Tot_Lin + 1
Select @Campos = @Campos + 1
If @Aux_Group <> "GROUP BY " begin
Select @Aux_Group = @Aux_Group + ", "
If @Aux_Order <> "ORDER BY " begin
Select @Aux_Order = @Aux_Order + ", "
Select @Aux_Select = sSelect + ", " + @a_Campo + " AS Campo" + @Campos
Select @Aux_Group = @Aux_Group + @a_Campo
Select @Aux_Order = @Aux_Order + @a_Campo
Fetch cur_Aux into @a_Local, @a_Linha, @a_Campo
End
Select @Aux_Select = @Aux_Select
-- <<<< MONTA COMANDO SQL
Select @Aux_Select = @Aux_Select + " " + @cmd_FROM + " " + @p_Filtro + " " + @Aux_Group + " " + @Aux_Order
Declare @Cursor_Aux cursor
Set @Cursor_Aux = cursor for @Aux_Select
Open @Cursor_Aux
Not working !!!!
Mssql 2005. How To Make Update Stored Procedure ?
May 1, 2008Hi ~
I made simple stored procedure that is to update user information following as...
ALTER PROCEDURE UpdateUserProfile( @user_id uniqueidentifier, @user_firstname nvarchar(50), @user_lastname nvarchar(50), @user_birth nvarchar(20), @user_gender nvarchar(20) )
AS
UPDATE user_profile SET user_firstname = @user_firstname, user_lastname = @user_lastname, user_birth = @user_birth, user_gender = @user_gender WHERE user_id = @user_id RETURN
When I tried to save this procedure, I faced on "Invalid Object : UpdateUserProfile" error message.
What's the problem ?
Stored Procedure Make String From Table Field
Jul 20, 2005Hallo !I have a Table with a column "ordernumber"ordernumberA12A45A77A88Is it possible to create a stored procedure which makes a string of these column ?Result: string = ('A12','A45','A77','A88')Thanks !aaapaul
View 3 Replies View RelatedTransact SQL :: How To Make Procedure Work For Multiple Values In Parameter
Jun 7, 2015Below is the stored procedure i have it works fine if i have 1 value passed to @invited_by but i want to modify but i want this code to be working for multiple inputs .Lets say if i do
exec [dbo].[sp_GetInvitationStatusTest] 'Test1 . I get the desired output but i want this procedure to work for
exec [dbo].[sp_GetInvitationStatusTest] 'Test1,Test2'.
USE [merck_acronyms]
GO
[code]....
Make SQL Server Distinguish Between Uppercase And Lowercase Characters In A Stored Procedure?
May 10, 2007I would like SQL Server 2000 to distinguish between uppercase and lowercase letters, but only within a single stored procedure. Also, at the end of the sp, I want the original collation to be restored. How will I implement this in my sp?
View 3 Replies View RelatedMake Sql Function To Modify Data Like Sql Procedure To Improve Code Structure And Readability.
Aug 27, 2007Now Sql Function can not modify data as Sql Procedure like other database, it's very troublesome at most case!
in most case, Sql Function is used to improve code structure then it can be maintanced easy! BUT only because it can not modify data, there are 2 troublesome way:
1. Make all callers in the path from Sql Functions to Sql Procedure. and the coder will cry, the code will become very
confusional , and very difficult to maintance.
2. Divide the Sql Function into a thin CLR wrapper to call a Sql Procedure, can only use another connection, BUT can not be in the same transaction context And the code is ugly and slow.
You should not give limitation to Sql Function, should just limit the using context of Sql Function!
The sql code is more difficult to read and maintance than norm code(C#), then the improving code structure and readability should be one of your most important task, that's why microsoft!
Procedure Or Query To Make A Comma-separated String From One Table And Update Another Table's Field With This String.
Feb 13, 2006We have the following two tables :
Link ( GroupID int , MemberID int )
Member ( MemberID int , MemberName varchar(50), GroupID varchar(255) )
The Link table contains the records showing which Member is in which Group. One particular Member can be in
multiple Groups and also a particular Group may have multiple Members.
The Member table contains the Member's ID, Member's Name, and a Group ID field (that will contains comma-separated
Groups ID, showing in which Groups the particular Member is in).
We have the Link table ready, and the Member table' with first two fields is also ready. What we have to do now is to
fill the GroupID field of the Member table, from the Link Table.
For instance,
Read all the GroupID field from the Link table against a MemberID, make a comma-separated string of the GroupID,
then update the GroupID field of the corresponding Member in the Member table.
Please help me with a sql query or procedures that will do this job. I am using SQL SERVER 2000.
Make C#
Jun 30, 2006Can somebody help me convert this SQL2000 Function TO C# Function pleaseCREATE Function dbo.CalculateNextRentDate ( @Rent_Payment_Date datetime, @Frequency varchar(50) , @Number int)RETURNS DATETIMEASbeginDECLARE @NextPaymentDate DatetimeSET @Number = @Number - 1.IF @Frequency = 'month' IF @rent_payment_date = dateadd(month, datediff(month, 0+@Number, @rent_payment_date) + 1, -1) BEGIN SET @NextPaymentDate = dateadd(month, datediff(month, 0, @rent_payment_date) + 2, -1) END ELSE BEGIN SET @NextPaymentDate = dateadd(month, 1+@Number, @rent_payment_date) END return @NextPaymentDateend
View 1 Replies View RelatedMake 2 Or 1
Aug 16, 2007Our company has its Departments And Services.
Now We are making it online.
Both have separate email list, phone numbers, and more.
Will I make one table and adds the field Type (Values: D or S).
Or make them separate.
Remember one thing If we merge them then Email And PhoneNumber Table will also me merge
other wise they will also separate.
What is better.
DTS How Make Our Own?
Mar 29, 2004How to make our own DTS package I have to split the data on my own.. Data is very biig almost 30 to 40 million. I need to splitt them into 10k chunks in database and with my desired table name.
Waiting for a +tive reply.
Regards
Shani ;)
How Can I Make This And Or
Feb 12, 2008
hi i have a tabel in my database i tray to generat report for this tabel
this tabel have all this fields:
company_id
emp_no
seq_no
interval_date
in_time
in_type
out_time
out_type
wage_code
status
i want all his colums can be search
but with company_id
like if he enter company_id and emp_no okay give him result if he enter Company_id and interval_date okay give him result
i write this
Code Snippet
SELECT company_id, emp_no, seq_no, interval_date, in_time, in_type, out_time, out_type, wage_code, status
FROM interval
WHERE (company_id LIKE @CompanyID) AND (emp_no LIKE @EmployeeID) OR
(company_id = @CompanyID) AND (interval_date = @IntervalDate) OR
(company_id = @CompanyID) AND (in_time = @InTime) OR
(company_id = @CompanyID) AND (in_type = @InType) OR
(company_id = @CompanyID) AND (out_time = @OutTime) OR
(company_id = @CompanyID) AND (out_type = @OutType) OR
(company_id = @CompanyID) AND (wage_code = @WageCode) OR
(company_id = @CompanyID) AND (status = @Status)
but in report preview it tell me i must enter intrevalDate ?
How Can I Make This...
Apr 5, 2006Hi have have this problem, I have a table called PABX that has all the callings registry and what I need to do is for each client(PABX.cod_client) I have 2 types of calls (VC1, VC2) , and for these types I need to select all the registries chaging the dialed number(PABX.NRTELEFONE) for the new one (TROCAR.NRTELEFONE) and for those client that doesn't need to change select the PABX.NRTELEFONE
is it possible through SQL Server 2000(via stored procedure) or I'll need to do it by my application using a vector ?
Thanks
How To Make Class
Jan 25, 2008hiiiiiiiiii I am creating a web application using vb.net in which i m using the concept of classes. now i am done all the code for inserting the values in the database using the class but it is difficult to fetch the values from the database using select command and sending them to a WebForm . i want to know how i send send the values coming from the select command to a datagrid or another web controlif possible provide me a sample code thanks for your help
View 2 Replies View RelatedHow Can I Make Checkbox
Feb 2, 2006hello all i have a 2 questions hope that u can help me my first question is: in ms access there was a data type named yes/no and it was a checkbox is there a checkbox data type in sql server 2005?
my second question is i need the connection code between asp.net 2005 and sql server 2005 i searched in here for that code but i got more confuse i found two codes and both are not working so hope u can give me the right connecting code. that's all thanks
How To Make A Certain Log During SP Execution?
Sep 28, 2001hi all,
Now i want to log some information (e.g.time,count...)during SP execution,how can i do it in Sql?
Thanks
Make SQL Available Online
Sep 28, 2005Can anyone tell me how I would go about making my SQL server accessable from the Internet, or know of any good tutorials to get me started, I haven't had much luck looking on google.
I need to access an SQL database from one server on another server for a web application.
Thanks
Need To Make This Query
Jun 28, 2004Hi guys, I have a car_race table which has these fields
car_id int
race_id int
b_car_won varchar // can have 'y' or 'n'
I need to know if the car lost the 1st race but won the next race
And example of that table for car_id 1:
car_id 1
race_id 1
b_car_won 'NO'
car_id 1
race_id 2
b_car_won 'YES'
Now this is the tricky part, the database has some data integrity issues, so this can occur:
car_id 1
race_id 1
b_car_won 'NO'
car_id 1
race_id 3
b_car_won 'YES'
So I cant used a fixed race_id value, need to use the race_id > 1 to know whats the next race. But this raises another issue if I have this in the database:
car_id 1
race_id 1
b_car_won 'NO'
car_id 1
race_id 3
b_car_won 'YES'
car_id 1
race_id 4
b_car_won 'YES'
If I query I'd get 2 rows where race_id > 1. And I only need the first one, because 3 is the next race.
I need to fetch in a single row if possible, the result of the 1st race and the 2nd race. How can I do this?
How Do I Make It Use My Index?
Dec 15, 2004Hello!
I have two tables
users and pictures.
table users have a clustered (PK) index on userid
table pictures have a clustered (PK) index on userid
when I do this query:
"select userid from pictures where userid=123"
then It will do a clustered index seek
But If I do any of those:
"select t2.userid from users t1 left join t2 on t1.userid = t2.userid"
or
"select (select userid from pictures where usedid = t1.userid) from users t1"
It will do a clustered index scan.
How can I force it to seek my index instead of scan?
Thanks!
I Want To Make Project
May 26, 2006i want to make a database for pharmacy using sql server and VB.NET
so any body can give me illuminations
thanks in advance
george albert