Using the table structure below how would I create a bcp bulk insert that would handle the XML data. It is important that this is run from command line. CREATE TABLE [dbo].[MyTable]( [Id] [uniqueidentifier] NOT NULL DEFAULT (newid()), [MyXmlField] [xml] NULL Thanks in advance... ...
Hi Folk, I'm trying to see if exists something to create a sequence with partition logic. I need a sequence number that depend on other primary key ex: id_person sequence id 1 | 1 1 | 2 2 | 1 3 | 1 1 | 3 so the sequence must depend on the id_person partition. Is there something like this on or...
I have two tables (TableA and TableB). create table TableA (A int null) create table TableB (B int null) insert into TableA (A) values (1) insert into TableB (B) values (2) I cant join them together but still I would like to show the result from them as one row. Now I can make select like this: select (select A from tableA) as...
I am getting the error from the application as following with SQL server 2005 "Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0" How can i find the stage where this error raised? how can i found the missing transaction or the stored...
I have a view on two tables (stored using SQL 2008) with millions of records. CREATE VIEW VwSalesAge AS SELECT Transactions.ID ,Transactions.Amount ,Customer.ID ,Customer.Name ,Customer.Age FROM Transactions INNER JOIN Customer ON Transactions.CustomerID=Custo...
Hi I'm designing a database to hold my domain objects. I have various validation rules on the objects such as string length and if it must be filled. These things are checked in the C# code. Does it make sense to duplicate this rules in the database, such as setting nvarchar(150) instead of nvarchar(max) and setting nullable to false f...
I am getting an SqlException in the logs of .NET 3.5 app, I am looking for the corresponding number (value of the property SqlException.Number). System.Data.SqlClient.SqlException: A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer av...
I have a table with an XML column in SQL Server 2k8. The following SQL retrieves some XML: SELECT TOP 1 my_xml_column FROM my_table Let's say it returns me the following XML <a> <b /> <c> <d /> <d /> <d /> </c> </a> What I would like to get is /a /a/b /a/c /a/c/d /a/e In other words, how can I get SQL Server to...
Hi folks, I have two linq (to EF4) queries, which return different results. The first query contains the correct results, but is not formatted/projected right. the second query is what i want but it missing some data. Schema Query 1 var xxxx = (from cp in _connectedClientRepository .GetConnectedClients(new[] { "LogEntr...
I have this code in PowerShell, that executes SQL query for UPDATE table: $Connection=new-object data.sqlclient.sqlconnection "server=server;database=mydb;trusted_connection=true;" $Connection.open() For( $i = 0; $i -le $ActID.Length; $i ++ ){ $cmd = New-Object System.Data.SqlClient.SqlCommand $cmd.Connection = $Connection $cmd.Comma...
I'd like to get the ID of a particular row from within a Firebird 2.1 stored procedure. I can't seem to remember the syntax, and it's driving me nuts. This is how the code might look in TSQL, used by Microsoft SQL Server. @ID = SELECT ID FROM ADDRESS WHERE POBox = :POBOX AND ExtendedAddress = :EXTENDEDADDRESS AND ...
A friend has suggested I post here as I'm in need of a bit of help! DB Layout: **salestable** salesorder [primary, unique] (sales order number) salesman (salesperson id) **salesline** salesorder [many sales line to single salestable relationship] saleprice (line amount) isaccessory (0 or 1) I'd like to, in a single select, sum sales...
I have a database containing orders and each order has an associated location. Currentl, when a user is logged in ,I am using Bing Maps API to geocode each order location and then compute driving distance to the logged in user. Based on these distances, the user via a dropdownbox can then specify the maximal distances with the results di...
I try to run the same query in several dbs in mysql: def m='xxx' def dbs = ['DB05DEC05','DB06DEC06','DB07DEC07','DB08DEC08','DB09DEC09','DB10DEC10'] def sql =Sql.newInstance("jdbc:mysql://localhost:3306", "root","", "org.gjt.mm.mysql.Driver") dbs.each{ db-> sql.eachRow("select * from ${db}.mail where mid=$m", { println "\t$db ${it.mid...
I am currently writing an application for monitoring SQL queries produced by a web application. I am interested in monitoring the SQL queries being sent by a given application passively. That is, I don't want a proxy - instead I want to run the application to sit separate of the web application / database, and simply read the SQL queries...
How can I execute whole sql file into database using SQLAlchemy? There can be many different sql queries in the file including begin and commit/rollback. ...
I want to find the fastest way to get the min and max of a column in a table with a single Linq to SQL roundtrip. So I know this would work in two roundtrips: int min = MyTable.Min(row => row.FavoriteNumber); int max = MyTable.Max(row => row.FavoriteNumber); I know I can use group but I don't have a group by clause, I want to aggrega...
Hi, I'm inexperienced with SQL and I'm not sure how to form this query correctly. Select comment.text, (COUNT(parent.id)-1) AS depth FROM comments AS comment, comments AS parent WHERE comment.lft BETWEEN parent.lft ...
I already have a table which consists of data. I need to alter table to add two new columns which are not null. How can i do that without loosing any existing data? NOTE: The new columns are not null fields. ...
What do others do to simplify the creation of simple, serial surrogate keys populated by a SEQUENCE (a.k.a. GENERATOR) in Firebird >= 2.1? I finc the process comparatively arduous: For example, in PostgreSQL, I simply type: pg> CREATE TABLE tbl ( > id SERIAL NOT NULL PRIMARY KEY, > ... In MySQL, I simply type: my> CREATE TABL...