SQL Server Management Studio Replacement
Can anyone suggest a non java replacement for SQL Server Management Studio for OSX? I currently use Parallels in Crystal mode and run SSMS, but it seems like there should be something better. ...
sql-server
Why am I unable to create a trigger using my SqlCommand?
The line cmd.ExecuteNonQuery(); cmd.CommandText CREATE TRIGGER subscription_trig_0 ON subscription AFTER INSERT AS UPDATE user_data SET msg_count=msg_count+1 FROM user_data JOIN INSERTED ON user_data.id = INSERTED.recipient; The exception: Incorrect syntax near the keyword 'TRIGGER'. Then using VS 2010, connected to the ve...
Hierarchical SQL query not returning level
I have a typical SQL Server hierarchical query: WITH bhp AS ( SELECT name, 0 AS level FROM dbo.BhpNode WHERE parent_id IS NULL UNION ALL SELECT a.name, level + 1 FROM dbo.BhpNode a INNER JOIN dbo.BhpNode b ON b.bhp_node_id = a.parent_id ) SELECT * FROM bhp This seems to match the various examples of hierar...
How do i check if something exist without using count(*) ... limit 1
My code is SELECT COUNT(*) FROM name_list WHERE [name]='a' LIMIT 1 It appears there is no limit clause in SQL Server. So how do i say tell me if 'a' exist in name_list.name? ...
Will implicit data type conversion in SQL affect performance?
I have 2 samples that effectively do the same thing -- Sample 1: command.Parameters.Add(ParamFoo, SqlDbType.Int) command.Parameters(ParamFoo).Value = fooVal Sample 2: command.Parameters.AddWithValue(ParamBar, barVal) Which sample would be recommended? I know that the second sample is making some conversions. Wouldn't it be best to ...
Bulk inserting best way to about it? + Helping me understand fully what I found so far
Hi So I saw this post here and read it and it seems like bulk copy might be the way to go. http://stackoverflow.com/questions/682015/whats-the-best-way-to-bulk-database-inserts-from-c I still have some questions and want to know how things actually work. So I found 2 tutorials. http://www.codeproject.com/KB/cs/MultipleInsertsIn1dbT...
Can SQL Server 2008 use a variable in a WHERE IN clause
Are there any updates in SQL 2008 to allow a variable for the IN of a where clause? Declare @InParams varchar(100) Set @InParams = '1,2' Select * from Category Where CategoryID in @InParams ...
Conversion failed when converting datetime from character string. Linq To SQL & OpenXML
Hi I been following this tutorial on how to do a linq to sql batch insert. http://www.codeproject.com/KB/linq/BulkOperations_LinqToSQL.aspx However I have a datetime field in my database and I keep getting this error. System.Data.SqlClient.SqlException was unhandled Message="Conversion failed when converting datetime from ...
Installing databases after installation of server.
Hello, I my software (coded in C# .NET ) I need the client to install a database server (MySQL in my case). I'm deploying the setup and making user install it like a pre-requisite. After installing the DBMS server. I need to setup databases (that I created) on the client machine. How to do this automatically (as part of installation.)...
Would this rollback/stop all records from inserting?
Hi I been going through this tutorial http://www.codeproject.com/KB/linq/BulkOperations_LinqToSQL.aspx and them make a SP like this CREATE PROCEDURE [dbo].[spTEST_InsertXMLTEST_TEST](@UpdatedProdData nText) AS DECLARE @hDoc int exec sp_xml_preparedocument @hDoc OUTPUT,@UpdatedProdData INSERT INTO TBL_TEST_TEST(NAME) SELECT...
File transfer through SQL Server connection
I have a text file sitting on client machine and want to move it to the database server (MS SQL 2008) but I don't have any access to the server except through the SQL Server client. Can I transfer this file to the server using SQL client connection? ...
sql server query/subquery question
Hi Experts, I have a tables with data like this Id BookId TagId 34 113421 9 35 113421 10 36 113421 11 37 113421 1 38 113422 9 39 113422 1 40 113422 12 I need to write a query (SQL Server) which gives me data according to the tags say if I want bookIds where tagid =9 it should return bookid 113421 and 113422 as it exi...
Can't find which row is causing conversion error
I have the following table: CREATE TABLE [dbo].[Accounts1]( [AccountId] [nvarchar](50) NULL, [ExpiryDate] [nvarchar](50) NULL ) I am trying to convert nvarchar to datetime using this query: select convert(datetime, expirydate) from accounts I get this error: Conversion failed when converting datetime from character string....
Working by group by for grouping data into string format
I have a table that contains some data given below. It uses a tree like structure i.e. Department SubD1, SubD2 ..... PreSubD1, PreSubD1... PreSubD2, PreSubD2... pk_map_id preferences ImmediateParent Department_Id -------------------- ---------------...
Trouble with CTE
I am learning CTE, and tried out the following query WITH fooCTE AS ( SELECT TOP 5 f.bar FROM foobar f ) But it's displaying an error which is quite incomprehensible. Msg 102, Level 15, State 1, Line 5 Incorrect syntax near ')'. Infact when I run the query below, I get the top five values being displayed. SELECT TOP 5 f.bar FROM...
GridView edit problem If primary key is editable (design problem)
I would like to ask about the design of table based on it's editability in a Grid View. Let me explain. For example, I have a table named ProductCustomerRel. Method 1 CustomerCode varchar PK ProductCode varchar PK StoreCode varchar PK Quantity int Note text So the combination of the CustomerCode, StoreCode and ProductCode must be u...
Can't connect to SQL Server 2005 Express from an ASP.NET C# page
Hey guys, I have an MS SQL Server 2005 Express running on a VPS. I'm using pymssql in Python to connect to my server with the following code: conn = pymssql.connect(host='host:port', user='me', password='pwd', database='db') and it works perfectly. When I try to connect to the server from an ASP.NET C# page with the following code: ...
Select statement always execute/select columns in order?
Does all the columns in select statement gets selected one after another as listed? Declare @X, @Y SELECT @X = ColumnA*.25 + ColumnB*2.5, @Y = ColumnA*.5 + ColumnC*1.33, TOTAL = @X + @Y FROM SomeTable Is the above query safe to use? Will total always be selected after @X and @Y are calculated? ...
How to find duplicate values in SQL Server
Hi, I'm using SQL Server 2008. I have a table Customers customer_number int field1 varchar field2 varchar field3 varchar field4 varchar ... and a lot more columns, that don't matter for my queries. Column *customer_number* is pk. I'm trying to find duplicate values and some differences between them. Please, help me to find all...
Advice on software / database design to avoid using cursors when updating database
I have a database that logs when an employee has attended a course and when they are next due to attend the course (courses tend to be annual). As an example, the following employee attended course '1' on 1st Jan 2010 and, as the course is annual, is due to attend next on the 1st Jan 2011. As today is 20th May 2010 the course status rea...