|
You want to backup a table in Sql Server 2008?
|
|
|
|
|
actually the problem is that my project manager has asked me to do a project of merging different databases in sql server 2008 these databases are on oracle, my sql and sql server now the problem is that the database have a column of unicode data
|
|
|
|
|
Kapilkp wrote: actually the problem is that my project manager has asked me to do a project of merging different databases in sql server 2008
Ah, that explains the previous question a bit better
Kapilkp wrote: these databases are on oracle, my sql and sql server now the problem is that the database have a column of unicode data
Create a linked server to MySql and Oracle. Why would unicode be a problem?
|
|
|
|
|
Kapilkp wrote: a column of unicode data
Then put it in an NVARCHAR field. N for Unicode.
|
|
|
|
|
|
Is these differences are correct or not as interviewer said me these are incorrect....please guide me...
Difference between function and SP
Procedure can return zero or n values whereas function can return one value which is mandatory.
Procedures can have input/output parameters for it whereas functions can have only input parameters.
Procedure allows select as well as DML statement in it whereas function allows only select statement in it.
Functions can be called from procedure whereas procedures cannot be called from function.
Exception can be handled by try-catch block in a procedure whereas try-catch block cannot be used in a function.
|
|
|
|
|
Sounds/looks correct, at least for TSQL. You could take the time to verify each statement on a PC, and mail the recruiter the results
|
|
|
|
|
- Wrong; a table-valued function[^] may return more than one value as part of a result-set;
- Correct; functions may not have
OUTPUT parameters; - Wrong; a multi-statement UDF may contain[^]:
- assignment statements;
- control-of-flow statements (except
TRY...CATCH ); DECLARE statements;SELECT statements;- cursor operations referencing local cursors;
INSERT , UPDATE and DELETE statements modifying local table variables; andEXECUTE statements calling extended stored procedures[^];
- Sort-of; a function may call an extended stored procedure[^], but not a regular stored procedure;
- Correct; a function may not contain a
TRY...CATCH block;
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Nice - all by memory?
I should've known the first, disagree with the fourth. Point three I'd expect that the function does not allow DDL, only DML.
|
|
|
|
|
Almost - I had to look a couple up to confirm what I thought.
Why would you disagree with #4 when it's documented in BOL[^]?
The following statements are valid in a function: ... EXECUTE statements calling extended stored procedures.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
Richard Deeming wrote: Almost - I had to look a couple up to confirm what I thought.
Just a little validation - impressive
Richard Deeming wrote: Why would you disagree with #4 when it's documented in BOL[^]?
I wouldn't wanna throw SPs and XSPs on a single heap. One is a script, the other is code.
|
|
|
|
|
5. They might in the future.
|
|
|
|
|
They're apples and oranges.
|
|
|
|
|
Exactly, just like the question and your answer.
|
|
|
|
|
this is my original query:
INVOICE NC_DOC NC_VALUE ND_DOC ND_VALUE RC_DOC RC_VALUE
40842 137 130130 140 60000 3 130130
40842 137 130130 141 85060 3 130130
40842 138 -130130 140 60000 3 130130
40842 138 -130130 141 85060 3 130130
40842 139 190130 140 60000 3 130130
BUT Now i need some query like this
doctype numberDoc doc_value
NC_DOC 137 130130
NC_DOC 138 -130130
NC_DOC 139 190130
ND_DOC 140 60000
ND_DOC 141 85060
RC_DOC 3 130130
|
|
|
|
|
It looks like you have simply added the doctype column!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
yes!! using this 3 columns nc_doc nd_doc and rc_doc , and then transpose as DocType...
any ideas!!!?
|
|
|
|
|
It looks more like unpivot to me.
People say nothing is impossible, but I do nothing every day.
|
|
|
|
|
Hi
How can I get the total number of two types of transaction between two dates per user
Example data:
User TransactionType MovementDate
Avril RECP 2012/01/04
Pat RECP 2012/01/09
Pat SRET 2012/01/09
What I would like is the following:
User Recipts Returns
Avril 1 0
Pat 1 1
This is where I was heading, but as soon as I put in a where clause to limit the date range I get an Error " Incorrect column expression: 'SUM(CASE WHEN stkhistm.transaction_type = 'RECP Then 1 else 0 end)'
SELECT SUM(CASE WHEN stkhstm.transaction_type = 'RECP' then 1 else 0 end) , SUM(CASE WHEN stkhstm.transaction_type = 'SRET' then 1 else 0 end)
FROM vektron.scheme.stkhstm stkhstm
WHERE stkhstm.movement_date>=[?]
|
|
|
|
|
What database are you using, [?] as a parameter looks like Access, the code looks like TSQL and you are mixing the parameter types. A TSQL parameter would be @Date
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Oops sorry - SQL Server 2008 R2 - MS Query - Excel
Seem to have got a liitle further:
This works:
SELECT user_id, SUM(CASE WHEN stkhstm.transaction_type = 'RECP' then 1 else 0 end) ,
SUM(CASE WHEN stkhstm.transaction_type = 'SRET' then 1 else 0 end)
FROM vektron.scheme.stkhstm stkhstm
GROUP BY user_id
Then I tried to add a restriction on the dates:
SELECT user_id, SUM(CASE WHEN stkhstm.transaction_type = 'RECP' then 1 else 0 end) *
SUM(CASE WHEN stkhstm.movement_date > #01/01/2012# then 1 else 0 end) * sum(CASE WHEN stkhstm.movement_date < #01/01/2013# then 1 else 0 end),
SUM(CASE WHEN stkhstm.transaction_type = 'SRET' then 1 else 0 end)
FROM vektron.scheme.stkhstm stkhstm
GROUP BY user_id
But get the same error as before
|
|
|
|
|
As to the dates, you probably want to use BETWEEN -- I do hope you are storing dates as DATE or DATETIME, not strings.
If you execute your command in SSMS, what line does it tell you the error is on?
|
|
|
|
|
Thanks PIEBALDconsult
The problem was amongst other things the dates !!
This works now:
SELECT user_id,
SUM(CASE WHEN (stkhstm.transaction_type = 'RECP' AND stkhstm.movement_date >= {ts '2012-01-01 00:00:00'} AND stkhstm.movement_date < {ts '2013-01-01 00:00:00'}) then 1 else 0 end) ,
SUM(CASE WHEN (stkhstm.transaction_type = 'SRET' AND stkhstm.movement_date >= {ts '2012-01-01 00:00:00'} AND stkhstm.movement_date < {ts '2013-01-01 00:00:00'}) then 1 else 0 end)
FROM vektron.scheme.stkhstm stkhstm
GROUP BY user_id
'BETWEEN' would be a bit more elegant I guess
|
|
|
|
|
I just wonder is there a visual guide in customizing the color coding.
Problem I face with is that I need to change the color for column name. I have tried changing many items, but with no luck.
modified 23-Jan-13 3:27am.
|
|
|
|
|
You can change the colours in options => text editor. Make sure you save and backup the setting if you customise it, losing them is a bitch!
Never underestimate the power of human stupidity
RAH
|
|
|
|