|
I'd be interested to see what you have now.
|
|
|
|
|
I was just guessing, based on the fact that there would generally be a comma separator in such expressions. However, from the further messages it seems that OP had not checked the documentation for proper format of the command.
|
|
|
|
|
Rick
Thanks for your input. I have tried that already and it didn't work for me. it gives me another error: "Data type mismatch in criteria expression"
Is the anyone out the who can suggest what I should do with this error? I will really appreciate in advance.
Thank you
|
|
|
|
|
Hello experts. I have noticed something that is unfamiliar to me in my database knowledge.
My application creates tables and inserts initial values into them based on user specifications. More than two tables are created in the process. The table creation and inserts are done within a transaction. When an error occurs, the transaction is rolled back.
I just noticed that when the transaction is rolled back, all inserts are erased as expected. However, the tables created within the transaction are not dropped from the database. This is unusual to me. I don't know if I am missing something or that is how it happens with SQL server (2008 R2).
Is there any way the tables can be dropped without specifying them one at a time in code? Please help.
|
|
|
|
|
Not something I have ever needed to do. However why is explicitly dropping the table and issue, you already trap the error and have a rollback simply add the test and drop in that trap.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Similar to below? That would remove the created table (and does)
BEGIN TRANSACTION
CREATE TABLE Test (Id BIGINT)
INSERT INTO Test (Id) VALUES (1)
ROLLBACK
SELECT *
FROM information_schema.tables
WHERE TABLE_NAME = 'Test'
Danzy83 wrote: However, the tables created within the transaction are not dropped from the database. This is unusual to me.
It'd be erroneous. Have you changed the locking-options?
FWIW, it'd probably be the wisest to use a temp-table, not a real one.
|
|
|
|
|
In almost all database systems that I have worked with, DDL statements such as CREATE and DROP are not considered part of a transaction.
|
|
|
|
|
Shameel wrote:
In almost all database systems that I have worked
with, DDL statements such as CREATE and DROP are not considered part of a
transaction.
Yes but it appears that it is part of TSQL which is what the poster asked about.
|
|
|
|
|
Thanks for pointing out. I didn't know that unlike Oracle, DDL statements are transactional in SQL Server.
|
|
|
|
|
Dear All
I Change the language for the database in Sql Server 2008 ( To Arabic_CI_AS )via the optional tab in database proprieties .
but when i Insert new record( have Arabic Text) in any table the text change to ???? .
thanks for any body help me .
T.h
Thaer
|
|
|
|
|
Possibly a typeface/font issue. Check the settings of the tool you are using.
|
|
|
|
|
Change your field type from VARCHAR to NVARCHAR. NVARCHAR is the unicode definition and will accept double byte characters.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
i need to save my table data containing unicode in a file format and for future i need to import that file so the data should not be lost
Kp
|
|
|
|
|
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.
|
|
|
|