If looking at CASCADE doesn't help, then re check your foriegn key assignments. Your description implies that the employee table is the parent of the department table which sounds backwards.
In most companies, you can delete an employee and still have the department, but you can't delete the department if it still has employees. This is where you use CASCADE to delete the department and all employees in the department (is that...
rob313
Last Activity: Sep 20 '07, 08:07 PM
Joined: Sep 10 '07
Location:
-
There was a synax error in the case statement. Try this
SELECT
CASE
when name = 'MIKE' then 'CHRIS'
when name = 'AJ' then 'SARAH'
else name
END as name
,address
FROM dbo.CustomerLeave a comment:
-
The problem is that you have a variable in the dynamic SQL, so the optimizer receives variables but they are not defined to the optimizer, only in you procedure. Change your procedure as follows where you end quote before a variable, then concatenate the variable and then start a new begin quote. I only did the first three variables here, so you will have to do the rest. A good debug technique would be to put a PRINT @p_build statement before...Leave a comment:
-
You question and code sample are a little confusing, but I think this is what you want:
SELECT
CASE
WHEN sv11.S11M14 = 'D' THEN 'OUT'
WHEN sv11.S11M14 = 'U' THEN 'IN'
WHEN sv7.InOut = 'I' THEN 'IN'
WHEN sv7.InOut = 'O' THEN 'OUT'
ELSE ' '
END AS IN_OUT
FROM ...
Only the first WHEN condition will be processed.Leave a comment:
-
Opps, I forgot a comma in between the two case statements.
select case
when SUBSTRING(task_ name, 1, 3)= 'PTO'
then Replace(task_na me,'PTO','PTO_H oliday')
when SUBSTRING(task_ name, 1, 7)= 'Holiday'
then Replace(task_na me,'Holiday','P TO_Holiday')
ELSE task_name
END as task_name,
CASE
when SUBSTRING(proj_ name, 1, 9)= '9900-2831'
then Replace(proj_na me,'9900-2831','II...Leave a comment:
-
You need to use two CASE statements like this:
select case
when SUBSTRING(task_ name, 1, 3)= 'PTO'
then Replace(task_na me,'PTO','PTO_H oliday')
when SUBSTRING(task_ name, 1, 7)= 'Holiday'
then Replace(task_na me,'Holiday','P TO_Holiday')
ELSE task_name
END as task_name
CASE
when SUBSTRING(proj_ name, 1, 9)= '9900-2831'
then Replace(proj_na me,'9900-2831','II Internal')...Leave a comment:
-
In that case, this should work:
INSERT INTO CRF (CORE_UID,
ACCT_NUM_MIN,
ACCT_NUM_MAX,
BIN,
BUS_ID,
BUS_NM,
ISO_CTRY_CD,
REGN_CD,
PROD_TYPE_CD,
CARD_TYPE)
SELECT UID,
LEFT(ACCT_NUM_M IN,16),
LEFT(ACCT_NUM_M AX,16),
BIN,
BUS_ID,
BUS_NM,
ISO_CTRY_CD,
REGN_CD,
PROD_TYPE_CD,
CARD_TYPE
FROM...Leave a comment:
-
If the user is dbo then SQL Server does not check any other permissions (grant or deny), so that would be why you are seeing this behavior. You will need to remove the user from dbo and grant that account the needed permissions or role.Leave a comment:
-
If you are running these two statements right after one another with nothing in between, then I would say the first statement is useless. I'm not sure why you would recover a backup of the model database into the pubs database and then restore a backup of the pubs database into pubs. I notice it has the KEEP_REPLICATIO N setting on, but I don't see how that would alter things. Anyone else?Leave a comment:
-
OK, since you have said B has a composite key then you need something like this (I assumed that CORE_UID, BIN was the composite key, so adjust as needed):
INSERT INTO CRF (CORE_UID,
ACCT_NUM_MIN,
ACCT_NUM_MAX,
BIN,
BUS_ID,
BUS_NM,
ISO_CTRY_CD,
REGN_CD,
...Leave a comment:
-
It would help to know what column(s) comprise your primary key on table B. You say that A nad B have different unique columns, but your insert statement only limits duplicates based on table A's unique key. If table B has a different unique key then that is what you need to check fo in your not in statement. Also, a NOT EXISTS should be faster, but first you need to figure out what is unique on table B.Leave a comment:
-
I would recommend using SQL Agent for scheduling the job. You may need to make your query more robust depending on the reason you check for users in the database. If you are implying that you only perform a backup if there are no users connected, then your procedure would need to loop until that condition occurs -- SQL Server can successfully backup a database with active connections, so I'm not sure why you check for that.Leave a comment:
-
In the step name tab, just give the step a name like 'Execute procedure', make sure the database is the correct database where your procedure lives, and type in exec sp_your_proc_na me in the command section. Then move to the Scheduile tab and click the button to add a new schedule and choose the frequency etc that you want the job to tun. If you have operators setup on your server, then you can use the notification tab to have the job email you...Leave a comment:
-
Are you using windows authentication? If so, make sure that a Windows group does not have execute authority on the procedure. I would also check to make sure the user acount that is the problem does not have an elevated role like db_owner or a system role like sysadmin. I'm assuming that you verified that the application is actually using the login you have been denying permissions on.Leave a comment:
-
You probably want either sp_droplogin or sp_denylogin.Leave a comment:
-
What you need to do is use SQL Agent. You can schedule the job to run nightly at your prferred time. When you create a new SQL Agent job, you then ad a job step. In this job step you specify the SQL command you want to run. I'm assuming you are using SQL Server 2000 since you refer to DTS. Using SQL Server Enterprise manager, expand the SQL Server you are working with, then expand the 'Managment' tree, then expand 'SQL Agent' tree and right...Leave a comment:
No activity results to display
Leave a comment: