Hi Team, I am using SQL Server 2005. I have heard that we can use a table variable to use instead of LEFT OUTER JOIN. What I understand is that, we have to put all the values from the left table to the table variable, first. Then we have to UPDATE the table variable with the right table values. Then select from the table variable. Ha...
I think that the ability of some reflective managed environments (e.g. .NET) to add custom metadata to code entities in the form of attributes is very powerful. Is there any mechanism for doing something similar for databases? Databases obviously already have a fair amount of metadata available; for example, you can get a list of all ta...
Using sql server 2000 Date Column datatype is varchar Table1 ID Date 01 20100122 01 20100123 01 20100124 01 20100125 01 20100126 01 20090127 01 20090128 01 20090129 01 20090130 01 20090131 01 20090201 01 20100202 01 20090203 01 20100204 01 20100205 02 ..... From the above table, from January 26 to February 03, the year was displa...
Hello, On my PC I have a small program to run SQL Server queries and gets the results back. I don't want to duplicate the DB on the server, I want to call that program on my PC from the server (The server runs Linux OS). I was thinking of using a web-service to communicate with my PC (using C# maybe), I can attach my PC to a Dynamic DN...
Hi all. I need a software that let me compare and synchronize two database on two different server. I found this, and was great until i deploy the site and put the database online. Now I can't connect to remote server. The standard port of SQL server is opened to my IP. Anyone use it? Do I miss something? Does anyone know a better soft...
I have am OLEDB Connection configured in the connection managers and I want to use it in a SCRIPT. The script needs to call a stored proc and then create buffer rows. I have added the connection to the connections available to the script and this is my code. Boolean fireagain = true; SqlConnection conn = new SqlConnection(); conn = (S...
I have a stored procedure that receives a string parameter "OrderByColumn" and builds dynamic query accordingly. This is the part of my stored procedure code: ROW_NUMBER() OVER (ORDER BY CASE WHEN @OrderByColumn='Date' AND @OrderDirection=0 THEN tbl_Docs.Date END ASC, CASE WHEN @OrderByColumn='Count' AND @OrderDirection=0 THEN tbl_...
What structure of a team should one keep in mind when making a team at the starting of the project ? Like what should be the ratio of Senior Software Engg. to Software Engg. or how many freshers should be attached to the team etc. and many other points. Please share any point that has an importance when deciding the structure of a team...
What is the advantage of using FAST_FORWARD for defining a cursor? Is it better for performance? why? ...
I have a table with two linked columns, a compulsory boolean and an optional date. There can only be a date when the boolean is FALSE. So I have this structure: CREATE TABLE FOO ( FOO_ID INT IDENTITY(1, 1) NOT NULL, MY_DATE DATETIME, MY_BOOLEAN BIT DEFAULT 0 NOT NULL, CONSTRAINT FOO_PK PRIMARY KEY (FOO_ID) ); And I've...
Hello, So far, after creating DB with all the schema, all I have done so for was accessing them (tables) by reference through ConnectionStrings. Now, twice, I've read that it's better to create a DB user and access the DB trhough that user by including him in the c*onnectionString*. I'd like to know why so? Thank for helping ...
Hi, I'm using Doctrine 1.1.2 as my ORM framework in combination with Zend Framework and a MSSQL server back-end. What I am trying to do is to store a blob to the database, but Doctrine is throwing me errors when I try. The column in the database is of type varbinary(max), and the model in Doctrine is configured as follows: $this->hasCo...
I have a query as follows select * from table where col1 = @param1 and col2 = @parm2 And another select * from table where col1 = @param1 Is it possible do both operations in same query based on parameter passed, if it is null query all or when parameter has value select them. My queries are very big and i have to create 2 version...
Hello folks, I'm working on a project where a PHP dialog system is communicating with a Microsoft SQL Server 2008 and I need more speed on the PHP side. After profiling my PHP scripts, I discovered that a call to mssql_connect() needs about 200 milliseconds on that particular system. For some simple dialogs this is about 60% of the who...
Hi, I'm writing sql query to get post and only last comment of this post(if exists). But I can't find a way to limit only 1 row for right column in left join. Here is sample of this query. SELECT post.id, post.title,comment.id,comment.message from post left outer join comment on post.id=comment.post_id If post has 3 comments I get 3...
I'm having trouble using the SQL BCP process to load up my tables with data. I'm calling it from a .NET application, so I execute the xp_cmdshell executable to run the bcp command. Here is what one of these commands looks like: EXEC master..xp_cmdshell 'bcp "[D001Test.Restore].[dbo].[GeneralComments]" in "<DataFile>" -q -c -t "|_|" -r "...
Problem description: In our project we have one "production database" and many "devs". Problem is how to manage and install the changes. We already have some procedure but its take a lot of time and sometimes cause errors. We can't lose the data - so we cant use "drop table" but only "alter table". Our actual "db versioning procedur...
I have an SP in SQL Server which runs hundreds of times a minute, and needs to check incoming traffic against a database. At the moment it does the following INSERT INTO table SELECT @value1,@value2 WHERE NOT EXISTS (SELECT * FROM table WHERE value1 = @value1 AND value2 = @value2); However, I could also go with IF NOT EXISTS(SELECT...
I am trying to get some old VB6 code to work with SQL Server Compact. I can connect, open the database and all seems well. I can run insert select commands which work. However the ADODB.Recordset RecordCount property always returns -1 even though I can access the Fields and see the data. Changing the CursorLocation = adUseClient ...
Hi I think If a trigger run On Insert, and the insert statement was in transaction, an error in the trigger will return to the transaction and I can catch it and ROLLBACK. Is that correct? Is there problems or concerns about that? Thanks ...