Hi all. I have a database which has almost 300 tables, I've just come across with a problem that I've added almost 60 tables into my diagram. I can only view the entire page if I set the page zoom to %10 and it's not really useful. Anyone has any suggestions for the problem? I need to view the diagram which is able to view more useful wa...
Hi everyone, I am using Windows 7 64 bit, but my Oracle Server I am trying to connect is 32 bit, and so is the SQL Server 2008 and Oracle Client on my machine. I have installed Oracle 10g client (32 bit) with administration tools on my machine and setup Net Services to connect to database. I can connect to all the databases through SQL...
We have been using several nested queries for reporting purposes since the days of SQL Server 2000. select * from (select * from t1) t1 inner join (select * from t2) t2 on........ We recently upgraded our database to SQL Server 2008 Standard from SQL Server 2000 Enterprise. We are noticing some of those old queries are now runn...
I want to lock one record and then no one may make changes to that record. When I release the lock, then people may change the record. In the meantime that a record is locked, I want to show the user a warning that the record is locked and that changes are not allowed. How can I do this? I've tried all the IsolationLevel levels, but ...
I want to randomly choose a value from a small range of integers (less than 200). As an alternative to SELECT RAND() I'm trying to use CAST(CAST(CRYPT_GEN_RANDOM(2) AS INTEGER) AS FLOAT) / 65535 but I'm getting some strange effects. For example: WITH Numbers (num) AS ( SELECT num FROM ( VAL...
Hi, I'm trying to transform a table to an XML struture and I want one of the columns in my table to represent a parent node and the other column to represent a child node. I have got part of the way but I don't have the complete solution. I need the TABLE_NAME column to transform to a xml parent node and the COLUMN_NAME column to trans...
Hi all I have a pretty simple question (and these are typically the ones I spend most of my time tearing my hair out about). I am using a batch file to execute all the .sql queries that are in the same directory as the batch, and to save all their results to various .csv file. Here is my code: @echo off REM Check that all parameters ...
When I run a stored procedure that does truncate table TargetTable; WITH TyreDimensionList as ( SELECT col1, col2, col3 FROM Table1 t1 INNER JOIN Table2 t2 ON t1.col1 = t2.col1 ) Insert into dbo.TargetTable ( col1,col2,col3 ) SELECT col1,col2,col3 FROM ...
I am using Full Text Search with LINQ in my application and as this is not supported by LINQ I use a table-valued function workaround. The function is created on SQL Server 2008. Surprisingly, I get error “The full-text query parameter for Fulltext Query String is not valid” when I search for a simply text e.g. “manager” I used SQL Ser...
Hi, What can be an efficient way for the following problem in SQL 2008? First two are input tables, using which I need to populate the 3rd(DataOut table) Basically, WDATA will have zero or more rows corresponding to each row of DataIn table. I need to populate DataOut table with all the rows, including none matched and multiple matche...
Hi All, I have a scenario where I need to round and then remove the extra zeros from numbers. So if I have a number that I have rounded (12.456400000) I want the zeros to be removed. Is there a function I can use to remove those numbers? The round function appears to leave the zeros in place? As always greatly appreciate the input. ...
Hi Experts, I'm tryin to join two tables. The problem i'm having is that one of the columns i'm trying to join on is a list. So is it possible to join two tables using "IN" rather than "=". Along the lines of SELECT ID FROM tableA INNER JOIN tableB ON tableB.misc IN tableA.misc WHERE tableB.miscTitle = 'help me please' table...
I used the MySQL Migration Toolkit to migrate a SQL Server 2008 database to MySQL. However, it converted all my bit types to tinyint. My data model doesn't like this, because I have a bunch of expressions testing for true/false, and not 0-255. Is there a way to tell the Toolkit to convert these different, or some SQL I can run on the ne...
I'm building a query that has a bunch of optional parameters, some of which are Table-Valued Parameters. The problem that I'm facing is how to most efficiently use the TVPs in this query? Each TVP has the type: TABLE( [variable] nvarchar(30)) I know that I could typically: INNER JOIN @TVP to filter out anything that is not in the ...
In the application development there is a concept of defensive programming. How to implement defensive programming techniques and writing robust code using Transact-SQL? ...
Hi, I have used both but what I am not clear is when I should prefer one over the other. I mean I know stored procedure can take in parameters...but really we can still perform the same thing using Views too right ? So considering performance and other aspects when and why should I prefer one over the other ? ...
Once again the SSRS security wall has hit me. I did a fresh SSRS 2008R2 install. I created a separate account (ssrs) on my box to access SSRS-related services. Upon navigating to localhost/reportserver, I was confronted with a windows authentication popup. I entered the ssrs username and pw and after some time was presented with the...
I have a vb.net app using plain old ado.net to run a s'proc on the database server. The proc simply inserts a record that contains binary data. When this executes, I can see the exec call on the proc being made from within sql profiler. ado.net claims everything worked, no errors. However, a record isn't inserted. There are no key c...
I want to avoid string concatenation to create dynamic SQL query in SQL 2008. How can I achieve the below functionality, if at all, efficiently ? A table stores the various conditions a user had chosen to search DB by using one or more values to search tables from. For e.g. he can give SSN or DOB or both SSN and DOB. So if he gives mu...
Hi folks, We have a simple Table per Type Entity Framework 4.0 model :- ALl classes are all POCO's. Post class is abstract. Discussion and List are classes are concretes, that inherit's from Posts (as shown in the diagram). When we try to save a Discussion, we do the following code :- Posts.AddObject(discussion); And the Sql Ser...