Question About The Account SQL Runs Under

Dec 11, 2006

Hello everyone.
I am pretty new to SQL Server, but I have been reading a lot lately.
One of the things that I have gone over extensively lately is backups (I didn't understand it til recently.)

I have a question: right now, I need to be able to backup my SQL database to a file server; both the DB and TLogs. I don't have room for them on the local drive right now, so this is my last option.

Here is the catch.
The SQL server is not part of our domain. The file server box IS part of our domain.

In order to backup across the network, I need to change the account that SQL and the agent runs under.

Would this work:

Create a new account on the SQL box and make it part of the administrators group. Make the SQL server and SQL agent run under that account.

On the File server, create a local account. Make it the same (username and password) that was created on the SQL box. This should allow me to backup my DB to the share.

Would that work?

Lastly, by changing the account SQL runs under, does that change anything in the way that SQL runs? Does it affect the way users authenticate to SQL (Right now, they authenticate using SQL authentication)

Still new and learning. I appreciate the help

View 14 Replies


ADVERTISEMENT

What Account Runs SQL Server Service?

Sep 18, 2001

We are debating whether to run the SQL Server service as Local System, a domain user without local admin rights, or a domain user with local admin rights. MSDN recommends local admin rights, but doesn't require them.
I would like to get some idea of how the real world handles this. If you run as a local admin, how do you handle the security implications? And if you run without local admin rights, what gotchas have you run into with extended stored procs, replication, etc?

Thanks,
Jerry Ratner

View 1 Replies View Related

A Procedure Runs Slow As A Job But Runs Fast Executed In A Query Window

Apr 23, 2008

Performance issue.


I have a very complex Stored Procedure called by a Job that is Scheduled to run every night.
It's execution takes sometimes 1 or 2 hours and sometimes 7 hours or more.

So, if it is running for more than 4 hours I stop the Job and I run the procedure from a Query Window and it never takes more than 2 hours.

Can anyone help me identify the problem ? I want to run from the Job and not to worry about it.

Some more information:
- It is SQL 2000 Enterprise with SP4 in a Cluster (It happens the same way in any node).
- The SQL Server and SQL Agent services run using a Domain Account that have full Administrative access.
- When I connect to a Query Window I also use a Windows Account.

- There is no locks or process bloking or being blocked while the job is running.
- Using the Task Manager the processor activity is ok, no more than 30 % in any processor.

View 15 Replies View Related

Whether To Use Local System Account Or Domain Account For Service Account

Jan 5, 2006

During install of SQL Server 2005, we can of course use a domain account or the built-in system account for running the services.  I lean toward domain for obvious reaons but would like to know a +/- to each option and why I'd choose one over the other and what consequences or limitations one may encounter if I choose one over the other.

View 6 Replies View Related

Stored Procedure Just Runs And Runs

Oct 9, 2001

I have a stored proceedure (which I will tag on at the end for those interested) which is taking at least 15 minutes to run when executed, but completes in 1 minute when the tsql statement is run in Query Analyser. Why is this?

I suspect that it may be connected to table indexing, but why then is this bypassed when QA is used?

Any advice appreciated.

Derek


************************************************** ***********************
IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'dbo.sp_ValidateAIGL') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE dbo.sp_ValidateAIGL
GO

CREATE PROCEDURE dbo.sp_ValidateAIGL
@IGLPeriodIDInt,
@IGLProgramIDInt

AS
/* ************************************************** ************************
Name:sp_ValidateIGL
Author:CL
Date:19-Jan-2001
Notes:
************************************************** ************************ */

--SET NOCOUNT ON

DECLARE@TaxYearChar(5),
@FrequencyChar(1),
@PeriodNo Int,
@ProgramLogIDInt

SELECT@TaxYear = TaxYear,
@PeriodNo = PeriodNo,
@Frequency = Frequency
FROMtbl_IGLPeriods
WHEREIGLPeriodID = @IGLPeriodID

SELECT @ProgramLogID = (SELECT ProgramLogID FROM tbl_SYSProgramLogs WHERE IGLProgramID = @IGLProgramID AND IGLPeriodID = @IGLPeriodID)

CREATE TABLE #IGLErrors
(
KeyFieldChar(24),
ErrorIDInt,
DescriptionVarChar(255)
)

-- *** Global Non Program Specific Data Errors ***
-- CHECK - that there are records in the DEB_IGL_PAYROLL_OUTPUT file.....none and the routine failed...
IF NOT EXISTS(SELECT * FROM tbl_OUT_Payroll WHERE IGLProgramID = @IGLProgramID)
INSERT INTO #IGLErrors SELECT NULL, 100, 'No records were processed by the IGL run!'

SELECT * FROM #IGLErrors

-- CHECK - search for any records where the employee's EXPENSE_CODE is NULL
INSERT INTO #IGLErrors
SELECT DISTINCT
NULLIF(EmpNo, ''),
2,
'Employee "' + COALESCE(NULLIF(RTRIM(EmpNo), ''),
'<Missing Employee>') + '" (Organisation Unit - ' + COALESCE(RTRIM(OrgUnitCode),
'<No Organisation Unit>') + ') does not have a EXPENSE_CODE Code.'
FROM tbl_OUT_Payroll
WHERE NULLIF(ExpenseCode, '') IS NULL
ANDIGLProgramID = @IGLProgramID

SELECT * FROM #IGLErrors
-- CHECK - check that the BALANCE of DEBITs match the balance of CREDITs
IF (SELECT SUM(Cash) FROM tbl_OUT_Payroll WHERE IsCredit = 1 AND IGLProgramID = @IGLProgramID) <> (SELECT SUM(Cash) FROM tbl_OUT_Payroll WHERE IsCredit = 0 AND IGLProgramID = @IGLProgramID)
INSERT INTO #IGLErrors SELECT NULL, 3, 'The total cash value for DEBIT elements does not match the total cash for CREDIT elements.'

SELECT * FROM #IGLErrors
-- *** Program 1 and 2 errors ***
IF @IGLProgramID IN (1, 2)
BEGIN
-- CHECK - search for any records where the employee's COST_CENTRE is NULL
INSERT INTO #IGLErrors
SELECT DISTINCT
NULLIF(EmpNo, ''),
1,
'Employee "' + NULLIF(RTRIM(EmpNo), '') + '" (Organisation Unit = ' + RTRIM(OrgUnitCode) + ') does not have a COST_CENTRE Code.'
FROM tbl_OUT_Payroll
WHERE NULLIF(CostCenter, '') IS NULL
ANDIGLProgramID = @IGLProgramID

SELECT * FROM #IGLErrors

-- Check for EMPLOYEEs that were not transfered to the PAYROLL output (usually caused by missing ORG_UNITs or not picked up in
-- the DEB_VIEW_APPOINTEE view...)
INSERT INTO #IGLErrors
SELECT DISTINCT
EMP_NO,
11,
'Employee "' + RTRIM(EMP_NO) + '" was excluded from the summary. Check their Organisation Unit codes!'
FROM PSELive.dbo.COSTING_OUTPUT
WHERENOT EMP_NO IN (SELECT DISTINCT EmpNo FROM tbl_OUT_Payroll WHERE IGLProgramID = @IGLProgramID)
ANDPERIOD_NO = @PeriodNo
ANDTAX_YEAR = @TaxYear

SELECT * FROM #IGLErrors

-- Check that there are no ELEMENTS in the COSTING_OUTPUT table that don't exist in the tbl_IGLElements table
INSERT INTO #IGLErrors
SELECT DISTINCT
ELEMENT,
12,
'Element "' + RTRIM(ELEMENT) + '" does not exist in the IGL Interface Elements table!'
FROM PSELive.dbo.COSTING_OUTPUT
WHERE ELEMENT NOT IN
(
SELECT DISTINCT Element
FROM tbl_IGLElements
)
ANDPERIOD_NO = @PeriodNo

SELECT * FROM #IGLErrors

END

-- *** Add a error to indicate the number of errors ***
IF EXISTS (SELECT * FROM #IGLErrors)
INSERT INTO #IGLErrors
SELECT 0,
0,
'Warning, there are ' + CAST(Count(*) AS VarChar(5)) + ' recorded errors!'
FROM#IGLErrors

-- Transfer the records to the ErrorsLog table ready for the user to view...
DELETE FROM tbl_SYSErrorsLog
INSERT INTO tbl_SYSErrorsLog (IGLProgramID, OutputLogID, KeyField, ErrorID, Description)
SELECT@ProgramLogID,
@IGLPeriodID,
KeyField,
ErrorID,
Description
FROM #IGLErrors
ORDER BY ErrorID

DROP TABLE #IGLErrors

SELECT *
FROM tbl_SYSErrorsLog
ORDER BY ErrorID

--SET NOCOUNT OFF

GO
GRANT EXECUTE ON dbo.sp_ValidateAIGL TO Public
GO

View 2 Replies View Related

SA Account (DBA System Account) Granting Priveleges But SQL Server 2000 Not Applying Them

Dec 4, 2006

I have been running a script in SQL Server 2000 as sa also as a Active Directory user who has administrator rights (I tested both approaches SQL Server then Windows Authentication) in Query Analyser which grants execute rights to the stored procedures within the database instance and Query Analyser does not give any errors when I run the script. I have made sure that each transaction has a go after it. I then return to Enterprise Manager, check the rights (I apply them to roles so that when we create another SQL Server user we just grant him/her rights to the role) and discover that the role has not been granted the rights. I seems to be occurring only with 2 of the procedures. Is there a known bug that might be causing this?

yours sincerely

Craig Hoy

View 9 Replies View Related

DTS Fails As A Job With Service Startup Account As &#34;System Account&#34;

May 9, 2002

I have several DTS jobs that runs well as a job with my nt login account for the SQL agent service startup account, but if I use the System account
they fail with this error.
" Error opening datafile: Access is denied. Error source: Microsoft Data Transformation Services Flat File Rowset Provider"

The data has change access to the System account under the NT security.

Thank you in advanced.

Jorge

View 2 Replies View Related

Xp_cmdshell Does Not Execute For Non-sysadmin Account Even With Proxy Account

Mar 2, 2004

Hi all, i hope you can help me.

Basically a dts package has been setup that pulls in data from another companies server, this data requires to be on-demand i.e individual users can pull in updates of the data when they require it.

I am using xp_cmdshell and dtsrun to pull in the data. This obviouly works fine for me as i am a member of sysadmin.

Books online quotes " SQL Server Agent proxy accounts allow SQL Server users who do not belong to the sysadmin fixed server role to execute xp_cmdshell"

So i went to the SQL Server Agent Properties 'Job System' tab and unchecked 'Non-sysadmin job step proxy account' and entered a proxy account.

The proxy account has been setup as a Windows user with local administrator privilages and even a member of the sysadmin server role - just in case.

Now when i log onto the db with my test account - a non-sysadmin - and attempt to run the stored proc to import the data i recieved the message 'EXECUTE permission denied on object 'xp_cmdshell', database 'master', owner 'dbo' '

hmm... so basically i have either misunderstood BoL or there is something not quite right in my setup.

I have search the net for a few days now and yet i can find no solution.

Can anyone help?

View 2 Replies View Related

Domain Account Vs Local Account For SQLServerAgent

Jul 20, 2005

Hi there,BOL notes that in order for replication agents to run properly, theSQLServerAgent must run as a domain account which has privledges to loginto the other machines involved in replication (under "SecurityConsiderations" and elsewhere). This makes sense; however, I waswondering if there were any repercussions to using duplicate localaccounts to establish replication where a domain was not available.Anotherwords, create a local windows account "johndoe" on both machines(with the same password), grant that account access to SQL Server onboth machines, and then have SQL Server Agent run as "johndoe" on bothmachines. I do not feel this is an ideal solution but I havecircumstances under which I may not have a domain available; mypreliminary tests seem to work.Also, are there any similar considerations regarding the MSSQLSERVERservice, or can I always leave that as local system?Dave

View 1 Replies View Related

Domain Account Without A SQL Login Account

Apr 25, 2007

I have a situation that I have discovered in our QA database that I need to resolve. When I looked at the Activity Monitor for our server, I discovered that a process is running under a domain user account for one of our .Net applications. The problem is that that domain user account has not been created as a SQL login account on the server. I am trying to figure out how someone can log in to the database server with a domain user account that has not been added to SQL Server as a login account.



Does anyone have any insight on this? I don't like the idea of someone being able to create domain account that can access the database without me granting them specific access.



- Larry

View 6 Replies View Related

Only One SSIS Job Runs...

Feb 12, 2007

Hi,

Currently I have two SSIS jobs on my machine. The problem I'm having is, only one of the jobs executes succesfully, the other one fails for incorrect user login. Both jobs use the same configuration database and all the packages on both jobs have the protection level set to "DontSaveSensitive". Both jobs have been deployed in the exact same manner, yet only one succeceeds and the other fails.

Can anybody tell me why this is going on?

View 2 Replies View Related

Vbs Script Runs As Standalone, But Not In Dts Or Sql Job

Jun 19, 2001

I have a vbs script to try to prove that I can perform vbs scripting in either a job step or a dts package
The script is

dim rs, sql,adoconn, adocommand, dataconnstring

set adoconn = createObject("ADODB.CONNECTION")
Set rs = CreateObject("ADODB.Recordset")
set adocommand = CreateObject("ADODB.Command")
adoconn.ConnectionString = "Provider=SQLOLEDB;Server=myserver;Database=pubs;U id=myuser;Pwd=mypass;"

adoconn.Open
sql = "select * from import"
rs.Open sql, adoconn,adOpenForwardOnly
while rs.EOF = false
sql = "insert into zz default values"
adocommand.ActiveConnection = adoconn
adocommand.CommandText = sql
adocommand.Execute

rs.MoveNext
wend

rs.Close
adoconn.Close
set adoconn = nothing


However,
when I run this from windows explorer it works fine,
but when I try to run it as an activeX script, I get the error ActiveX scripting: Function not found

As a cmdexec step in a job, I use the line c:inetpubwwwrootvbsvbstest1.vbs
It failed with the error
The process could not be created for step 1 of job 0x119BDBD264AD9B4597A9302786F0E250 (reason: %1 is not a valid Win32 application). The step failed.


What is wrong with the vbs script ?, or do I need to invoke it a different way ?

thanks in advance

View 1 Replies View Related

Can I Create A Job That Runs Other Jobs, Or.....

Aug 15, 2000

I have jobs (DTS packages) for several different tasks that I would like to run sequentially, rather than trying to estimate how long each will take and schedule them individually.

Any suggestions?

Thanks,
Cara

View 2 Replies View Related

HELP: Package Runs...job Fails!

Dec 18, 2000

I'm trying to run a job which moves data from one machine to another. I can manually execute the package successfully, but the job always fails. I get the dreaded error message 18456, "login failis for user "". I have double checked the logins for both machines,(sql server agent AND the sql server authentication).
Also, I can run a select statement (from server_ to server_2) successfully from query analyzer i.e.

Select *
from <linked_server_name>.<database>.<database_owner>.<table_name>

however, if I run this query from server_2 to server_1 I get the failed login message similar to the error message found in the job history. Since both servers accept nt logins, where is my problem? They are both set up as linked servers, and they are both mssql oledb.
Any suggestions will be appreciated!
thanks

View 1 Replies View Related

DTS Package Runs, But Not When Scheduled

Sep 15, 1999

I have created a package that takes a Visual FoxPro .dbf and imports into SQL7. If I run the job, it works fine. If I schedule the job it fails stating that I can't find the .dbf, the same one that it just found when run manually. What gives?

View 1 Replies View Related

Procedure Compiles And Runs But...!!!!!!!!!!!!

May 13, 2002

My procedure compliles and runs. l 'm running it as 'exec Statement' it should prompt me for the start and end date but it does not. Where
have l gone wrong in my logic l've declared all the variables need. Please advice


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Statement]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Statement]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO


Create Procedure Statement
As
Begin

declare @Startdate As datetime
declare @Enddate As datetime

declare @Customer_No As char(15)
declare @loanno As char(15)

declare @transaction_date As datetime
declare @transaction_type As char(3)
declare @reference As varchar(20)
declare @notes As varchar(255)
declare @transaction_amount As decimal (9,2)
declare @transaction_description As varchar(50)

declare @debit_amount As decimal (9,2)
declare @credit_amount As decimal (9,2)
declare @counter As int
declare @balance As decimal (9,2)
declare @user_changed As char(8)


declare c2 CURSOR FOR
SELECT loan_no
FROM loan
where customer_no = @Customer_No
ORDER BY loan_no

OPEN c2
FETCH NEXT FROM c2 INTO @loanno

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN

declare c1 CURSOR FOR
SELECT transaction_record.loan_no,
transaction_record.transaction_date,
transaction_record.transaction_type,
transaction_record.reference_no,
transaction_record.notes,
transaction_record.transaction_amount,
transaction_type.[description]
FROM transaction_record
inner join transaction_type
on transaction_type.transaction_type = transaction_record.transaction_type
where loan_no = @loanno and transaction_Date between @startdate and @enddate and transaction_amount <> 0
ORDER BY transaction_date

OPEN c1
FETCH NEXT FROM c1
INTO @transaction_date,
@transaction_type,
@reference,
@notes,
@transaction_amount,
@transaction_description

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
If (@transaction_amount < 0)
Begin
set @credit_amount = @transaction_amount
set @debit_amount = 0
End
Else
Begin
set @debit_amount = @transaction_amount
set @credit_amount = 0
End

If (@counter = 0)
Begin
set @balance = @transaction_amount
End
Else
Begin
set @balance = @balance + @transaction_amount
End

insert into Statement (customer_no,loan_no,
transaction_date,transaction_type,transaction_desc ription,
reference, notes, debit_amount, credit_amount, balance,
user_changed, transaction_no)

Values (@Customer_No,
@loanno,
@transaction_date,
@transaction_type,
@transaction_description,
@reference,
@notes,
@debit_amount,
@credit_amount,
@balance,
@user_changed,
@counter)

set @counter = @counter+1

FETCH NEXT FROM c1
INTO @transaction_date,
@transaction_type,
@reference,
@notes,
@transaction_amount,
@transaction_description
End
CLOSE c1
DEALLOCATE c1

FETCH NEXT FROM c2 INTO @loanno
End
CLOSE c2
DEALLOCATE c2
END;
Go

View 1 Replies View Related

UDF That Runs A Dynamic Query

Mar 11, 2008

Good afternoon,
i'm new to Functions on the SQL server

I'm trying to create a dynamic query that would select the the column passed to the function from a certain table

my table called selected_Date, and has StartDate, and EndDate columns

when the user select for example "StartDate", i pass this as a variable to the function which runs the query. but i always gets back the passed string as a result..

here is my table

StartDate | EndDate
---------------------
20071231 | 20080306


here is my function knowing that i'm passing "Start" as a variable:


Code:

ALTER FUNCTION tu_efgn_int.Get_StartEndDates(@DateType varchar(10))
RETURNS varchar(8)
AS
BEGIN
DECLARE @vDate varchar(15)

Set @vDate= (Select @DateType + 'Date' From dbo.ps_tbl_SelectedDates)

RETURN @vDate
END



@vDate always returns "StartDate" as result instead of 20071231 why is that ?

View 2 Replies View Related

Trigger Error When App Runs

Oct 5, 2005

I have SQL Server 2000, and our web application is in WebObjects.

I built a trigger on a table that indicates if certain fields in a record have been changed since the last time a report was run from the application.

This trigger runs fine through the Query Analyzer, and runs fine with a direct input through enterprise manager. However, when the WebObjects application tries to update the table, and error is thrown.

Is anyone familiar with a reason why an application would throw an error on an update, when the DB tools do not? If we disable the trigger, the application has no problem updating the table.

Here is the relevant portion of the trigger:

create trigger t_press_run_change
on dbo.press_run_line_item
for insert, update, delete
as

updatepress_run_line_item
setis_changed = 1
from deleted d
join press_run_line_item p on p.press_run_line_item_id = d.press_run_line_item_id
where(p.is_changed = 0 or p.is_changed is null)
AND ((isNull(p.print_quantity,0) <> isNull(d.print_quantity,0))
OR (isNull(p.spoilage_pct,0) <> isNull(d.spoilage_pct,0))
OR(isNull(p.ad_dimension_id,0) <> isNull(d.ad_dimension_id,0))
OR(isNull(p.quarter_fold_id,0) <> isNull(d.quarter_fold_id,0))
OR(isNull(p.max_qty_per_shipment,'') <> isNULL(d.max_qty_per_shipment,''))
OR(isNull(p.packaging_max_height,'') <> isNull(d.packaging_max_height,''))
OR(isNull(p.packaging_max_weight,'') <> isNull(d.packaging_max_weight,''))
OR(isNull(p.packaging_skids,'') <> isNull(d.packaging_skids,''))
OR(isNull(p.packaging_turns,'') <> isNull(d.packaging_turns,''))
OR(isNull(p.packaging_cartons,'') <> isNull(d.packaging_cartons,''))
OR(isNull(p.preprint_delivery_time,'') <> isNull(d.preprint_delivery_time,''))
OR(isNull(p.contact_id,0)<> isNull(d.contact_id,0))
OR(isNull(p.address_company,'')<>isNull(d.address_company,''))
OR(isNull(p.address1,'')<>IsNull(d.address1,''))
OR(isNull(p.address2,'')<>IsNull(d.address2,''))
OR(isNull(p.address_city,'')<>IsNull(d.address_city,''))
OR(isNull(p.address_state,'')<>IsNull(d.address_state,''))
OR(IsNull(p.address_zip,'')<>IsNull(d.address_zip,''))
OR(isNull(p.address_Country_id,'')<>IsNull(d.address_country_id,''))
OR(isNull(p.client_printer_id,'')<>IsNull(d.client_printer_id,'')))

View 2 Replies View Related

System Runs Slow

Jul 18, 2006

Some users complain that there computers run slow around noon. later it is fine. Anybody can tell me what is the problem? We have entivirus software installed on. Also i am runing back up database on the server. Many thanks.

View 6 Replies View Related

Scheduled Job Runs Forewer

Oct 12, 2007

Hello,
I have scheduled job that are scheduled to run every 2 hours.
Its exec stored procedures. Normal execution time is up to 10 min.
But recently job not running successfully, it’s just executes forever. So I had to kill that job and I run it manually.
Please help,
I have no idea why it stops executing on its own by the schedule.

Thank you,

View 14 Replies View Related

Bcp Only Runs On Master Database

Mar 5, 2004

Hello,

I can't get bcp to run on any database but 'master'. I get a 'failed to login' error. i'm logged in as 'sa' with full permissions.

Please help
Thanks

View 3 Replies View Related

Error When VB Runs DTS Package

Apr 27, 2004

Hi ,

I am trying to run DTS package (stored on SQL Server) using Visual basic but i am getting the following error

Runtime error '-2147217843 (80040e4d)'

Login failed for 'MyUserID'


*************************************
i used the following code in VB program

Sub Command1_Click()
Dim dtsp As New DTS.Package
dtsp.LoadFromSQLServer _
ServerName:="MyServer", _
ServerUserName:="MyUserID", _
ServerPassword:="MyPassword", _
PackageName:="DTSDemo"
dtsp.Execute
End Sub

*************************************

I can Run the package direct from SQL server but get error by Vb program. Any idea why it is so.

Thanks

View 3 Replies View Related

Procedure Runs With An Error???

May 24, 2004

This is my procedure and the error is incorrect syntax near '01'

DECLARE @returnDay int

--Looking at current date,
SELECT @returnDay = DatePart(day,GetDate())
--If is the 7th of the current moth then
If @returnDay = 24

EXEC master.dbo.xp_sendmail
@query = 'SELECT a.HospitalName,a.HospitalCode,c.ProductName,b.UnitsDiscarded,d.FateOfProducts,b. DateEntered,
b.DateCompleted,b.CompiledBy FROM test.dbo.Units b inner join Hospitals a ON (a.HospitalID = b.HospitalID)
inner join Products c ON (b.ProductID = c.ProductID)
inner join FateOfProducts d ON (d.FateID = b.FateID)
where b. DateEntered = DateAdd(month, -1, Convert(CHAR(8), GetDate(), 121) + '01') order by a.HospitalID',
@recipients=test@hotmail.com', @message='Submitting Results for the previous month',
@subject=' results for previous month', @attach_results = 'true', @separator = '/s'

SELECT @@ERROR As ErrorNumber


What am I missing here now, I am quite new to stored procedures


REgards

View 12 Replies View Related

Trigger That Runs On Update

Feb 7, 2008

I'm new to SQL and am having some trouble figuring out Triggers. We use Microsoft Dynamics SL (formerly Solomon) for our accounting. We have a table that only has data in it while we're processing checks called PRCHECKTRAN. We'd like to build a report that uses the information in this table but it's only available until we "keep" the checks that we're printing. This creates an added step because we have to stop our Payroll process to go out and print the reports. It's been suggested to use a trigger to make a copy of the records in the PRCHECKTRAN table so we've got them after the checks are "kept." We're wanting something that's done behind the scenes so the user can continue to do things the way they always have while giving us access to the data past the payroll period.

Basically as soon as we calculate checks, PRCHECKTRAN is populated. Once the checks are accepted as good, PRCHECKTRAN is cleaned out. I think I need an update or insert trigger that copies all records over to another table that we'll create called something like PRCHECKTRAN_HOLD. I'm just not familiar enough with SQL to write it. Any direction you can provide would be greatly appreciated. Thanks.

View 3 Replies View Related

Job Fails Though Package Runs

Dec 31, 2007



Like the name says, I'm trying hard, but I'm obviously missing something. Okay, I'm new to SSIS. I'm trying to write a job that deletes some Excel files on the server, copies some Excel files from a Sharepoint instance to the location where the deleted files were, runs an executable to manipulate those files, and then imports them to my SS instance.

the package runs fine when I run it from Managment Studio after installing it on the server. The 'execute task' fails when I run the job containing the package. After creating a proxy to have the SQL Server agent Service run with appropriate permissions, I thought I would have been rid of permissions issues (I gave the proxy way too much permission in an effort to narrow in on my issue), but I could still be missing something.

The job doesn't get into the package enough to even start writing to my log file, and all I get in the job history is the very cryptic 'DTExec: The package execution returned DTSER_FAILURE (1) ' error. If anyone has ideas, I would greatly appreciate being shown the error of my ways.

Thanks!

View 6 Replies View Related

How To Stop Job If Runs For More Then Specified Time?

Nov 14, 2007

Friends, I have one job running for more then esteemeted hours. I would like to stop the job if it runs for more then 30 mins. What I need to do for that? Thanks.

View 1 Replies View Related

MDX Query Runs Out Of Memory

Jan 15, 2008

Hi,


I am running the following MDX query through a DataReader and a Ado.Net Connection.

SELECT
{[Measures].[Deuda Total Nacional],
[Measures].[Deuda Total Nacional Maximo],
[Measures].[Cupo Nacional],
[Measures].[Porcentaje Utilizacion Maximo],
[Measures].[Pago Minimo Estado Cuenta],
[Measures].[Deuda Ultima Facturacion],
[Measures].[Dias Mora],
[Measures].[Dias Mora Maximo],
}
ON COLUMNS
,[Cuenta].[Cuenta].[Cuenta]*[Cuenta].[Rut].[Rut]*[Cuenta].[Dv].[Dv] ON ROWS

FROM [Bd Rtd]
WHERE [Tiempo].[Mes].&[2007-09-01T00:00:00]

The thing is, when I have about 10 thousand rows It runs in about 50 seconds which is good, but when I run this query and I have processed the cube with 100 thousand rows it runs out of memory and crashes.

I'm working in a shared development server with 1GB of memory for my project.

Is there any way to make it run anyway?? I mean even if it has to swap.


thanks


By the way when this thing goes into production it will have 1.5 million rows

View 2 Replies View Related

Funny Error During Runs

Jan 26, 2007

Can any one please help explain why this error has happened, it is not a normal error of SSIS,

"SSIS Debug host has encountered a problem and needs to close. We are sorry for the inconvenience"

It has 2 buttons Debug and Close, this error happened in the middle of a script task.

View 1 Replies View Related

Sql Parameter Error When Runs On Server

Oct 26, 2007

When im testing in local machine... my app works fine... but when i publish it on server... the sql DateTime parameter get an error: 
ERROR ON SERVER : String was not recognized as a valid DateTime
my code:SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["StringDeConexao"].ConnectionString);
SqlCommand oCmd = new SqlCommand("spcCadLjItem", con);oCmd.CommandType = CommandType.StoredProcedure;
 
oCmd.Connection.Open();SqlTransaction trans = con.BeginTransaction();
oCmd.Transaction = trans;
oCmd.Parameters.Add(new SqlParameter("@dt_exclusao", SqlDbType.DateTime));
oCmd.Parameters["@dt_exclusao"].Value = DBNull.Value;
oCmd.ExecuteNonQuery();
 When I inspect the type of parameter on my machine its ok.. SqlDbType its DateTime... and runs perfectly...Why it doesnt work when i publish on server???????
 tks!!
 

View 1 Replies View Related

Stored Procedure Runs Slow Only Sometimes

Jan 6, 2006

When i execute a stored procedure it generally takes about half a second to run but sometimes it takes 20 to 30 seconds.  I am the only one using the server so I know it is not due to other traffic.  I have looked at Profiler and nothing looks out of the ordinary.  Another observation is that the slow ones are always near eachother.  I will have about 10 fast executions and then 3 slow ones and then back to fast ones.  Has anyone seen anything like this before? 

View 5 Replies View Related

DTS Local Package Runs But Not Its Scheduled Job

May 11, 2000

I have a Local DTS Package that I created that runs fine. I have scheduled it to run daily but it errors out when run it from the agent. This is an excerpt of the error from the Job history.

Delete from Table [Intranet].[dbo].[Employees] Step, Error = -2147217887 (80040E21)

This Local DTS Package, deletes the records in the file and reimports the data from an AS400 file that is recreated for me daily.

The only thing that has occured to me so far is that perhaps the SQL Agent Service Account needs permission to delete the records. (I'm using NT Security).

Am I on the right track or do you think it's something else?

View 1 Replies View Related

Drop PK And Query Runs Faster. Why?

Aug 23, 2000

I have a query which joins multiple tables. This query has suddenly begun to take up to 2 minutes to run (vs. 5-10 seconds previously).

No major change in number of records in the tables (currently about 220,000). When I remove the PK from one of the tables which then forces a tablescan, the query returns to running in 5-10 seconds. If I add the PK back, performance is back to 2 minutes.

Any suggestions short and/or long term?

Thanks for any help.

View 1 Replies View Related

Sql Srvr 6.5 Only Runs From Command Prompt

Oct 11, 1999

We have a development server that will only run SQL Server from a DOS command prompt. Each time I reboot the server the services don't autostart, and I am unable to start them manually using Service Manager. Does anyone know what I can do to fix this problem?
Thanks

View 1 Replies View Related







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