I have a very generic set of three tables that store all of my data, which works brilliantly (we are talking small quantities of data here) DataContainer - Manages 'records' PK - DataContainerId FK - ParentDataContainerId FM - ModelEntityId DataInstance - Manages versioning PK - DataInstanceId FK - DataContainerId IsCurrent [bi...
An interview question: write SQL Server query to return 30th to 40th record of a table A my answer: select top 10 * from (select top 40 * from tb desc) asc select top 40 * from A where id not in(select top 30 id from A) which of above 2 is more efficient? why? ...
I am migrating the database layer of our application from MSSQL to MySQL and came across this exception when I tried to migrate an MSSQL table to MySQL: Row size too large. The maximum row size for the used table type ,not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs) Turns out the MSSQL table has lots of...
Hi, I am wondering why the following fails: SELECT price<500 as PriceIsCheap and forces you to do the following: SELECT CASE WHEN (price<500) THEN 1 ELSE 0 END as PriceIsCheap When, as per the answer to this related question, the conversion table says that an implicit conversion should occur. ...
Hello: I have a SQL 2008 version database (version number 655). If I try to create for attach it to a SQL 2005 or SQL 2000 Server I get an error: "This database is version 655. This server is compatible with version 612 or previous". Of course I can attach the database to a SQL 2008 server without errors. I know I can generate SQL Comm...
When I'm using Management Studio Express to drop a database there is an checkbox "Close existing connections." which close open database and enforce it to drop. How can i drop a database programmatic by closing open connections and check it's ever exists before? ...
What is the best way of loading a fact table incrementally using SSIS? ...
Further to my recent questions, I've now closed most of the connections that our web application was leaving open. However, the connection created by the function below remains open. Can anyone identify why it is not closing? public DataTable GetSubDepartment(int deptId) { DataTable dt = new DataTable(); using (SqlConnection ...
I'm trying to install SQL Server 2005 Developer Edition on my Windows 7 Ultimate laptop. When the installation runs, I am getting the following error :- A component that you have specified in the ADD_LOCAL property is already installed. To upgrade the existing component, refer to the template.ini and set the UPGRADE property to the nam...
I have a database called MyDB in a Microsoft SQL Server 2008 Express instance using mixed mode authentication. The application using the database MyDB currently connects using Windows Authentication, using the current user's Windows credentials. This login is a member of the 'public' server role, and has a user mapped to it in the MyDB d...
I have a method that either adds or updates a record in a DB (SQL Server), and it returns the RecordID as an (Int32) Output Parameter, and a success/failure result as (Int32) Return Value. Given that I'm specifying the type of these parameters, why do I have to cast them when they are returned? I expected to used the following: enquir...
Just started looking into encryption using keys and certificates in sql server 2005/08 and although it looks very good I'm not too sure why I should use it over sql server security permissioning. For example, I have a table with sensitive data in, such as user-name/passwords. I can either encrypt the data using say ENCRYPTBYCERT, or s...
Time-out occurred while waiting for buffer latch type 2 for page (1:1535865), database ID 6. This is an error message I got for five times while trying to create an index CREATE NONCLUSTERED INDEX YearIndx ON dbo.Papers ( PublicationYear ) the papers table is about 20,000,000 records and 175 GB Thank you in advance. ...
Hi folks, I'm trying to accomplish the following: Grab the db schema Grab any constraints* Alter tables Add/Drop tables I'm currently using pyodbc backend for Django. I would like to perform all these tasks within a view file. I'm using the following in order to grab fields of tables starting with 'core_': SELECT table_name,or...
If I encrypt data using ENCRYPTBYCERT in sql server 2005/08, what happens if the certificate used to encrypt it with is dropped from the database? The data is still encrypted, but the certificate is no longer there so can't be decrypted. What do I do? How do I get the original plaintext back? Recreating the certificate with the same ...
Hi everyone, i am creating a trigger and receiving some error, which i m not able to understand. Pls can anyone help me with that. create or REPLACE TRIGGER trig_data BEFORE INSERT ON data_db REFERENCING OLD AS OLD AND NEW AS NEW FOR EACH ROW BEGIN SELECT RAHUL_SEQUENCE.NEXTVAL INTO :NEW.USERID FROM DUAL; END; E...
Back in 1989, when I used to program with Oracle 5.2.3 on UNIX and VAX/VMS platforms, I considered SQL*PLUS as having the richest super-set of built-in functions. ORACLE*FORMS also had the ability to embed SQL statements within triggers. That was then, 21 years ago. At present, which other RDBMS' have come close, have the same, or more f...
Try the following on MSSQL 2005: select convert(char(2), 123) The key here is that char(2) is too small to accept the value '123'. I would expect to see a truncation error here, but instead the value "*" is returned. Update: A few of the answers showed how to cast in a way that will cause an error. That's not really what I need. We h...
I am trying to dynamically write some xml for a webservice. I have recently been shown how to use the FOR XML AUTO clause for my sql query, but I am not sure If one I am writing the query properly and on top of this I am not sure how to return the document values using (SqlConnection oCn = new SqlConnection()) { oCn.ConnectionStri...
Is there any way to initialize the value of two variables in the InitExpression of a For Loop Container in SSIS? For example: InitExpression: @BeginDate = (DT_DBTIMESTAMP) "1/1/1900"; @EndDate = (DT_DBTIMESTAMP) "1/1/2007" EvalExpression: @BeginDate < GETDATE() AssignExpression: @BeginDate = @EndDate; @EndDate = DATEADD("Month", 1,...