I'm developing a multi-database system. I want the difference between two dates in seconds. In SQL Server I got: DATEDIFF(second,stardate,enddate) In MySql: TIME_TO_SEC(TIMEDIFF(stardate,enddate)) My question: Does Sql Ansi have functions to manage DateTime values? i.e.: There are datetime functions generic for all databases? ...
This is locale specific to the US wherein it considered that the start of a week is Sunday; I want to be able to ask SQL to give me the date of the next Sunday relative to today [getDate()]. If today is Jan 15 it should return Jan 18; if today were Sunday it should return the following Sunday which is the 25th. This would be trivial to w...
I was looking at postgres question and it called for updating a table with random values. I only noticed it was targeted at postgres after answering it, but in answering it I hit a mystery. Here is my sample code in question: create table #Buildings([Use] varchar(50), n int) insert #Buildings select null,null from sysobjects upda...
I am retrieving the database list by executing the sp_databases stored proc. Is there any way I can exclude system databases from this list? I do not want to use the query SELECT name FROM dbo.sysdatabases where dbid > 6. Thanks ...
Here is the situation: I have a table value function with a datetime parameter ,lest's say tdf(p_date) , that filters about two million rows selecting those with column date smaller than p_date and computes some aggregate values on other columns. It works great but if p_date is a custom scalar value function (returning the end of day in ...
Hi I am using asp .net web form search in c#, for example with fields: - name - fruit I want to write a sproc that can search name or fruit. If I knew that both would have values, I would pass two parameters into the sproc and would write: Select * from myTable where name =@name and fruit = @fruit However sometimes, either name or f...
I have a website that has over 400,000 items. Some similar, some vastly different. We want to provide a way to search these items the best way possible. After being delivered the website it was using full text indexing. The solution is basic at best, woefully inadequate at worst. So what is the best way to search these items? The...
I am writing code to migrate data from our live Access database to a new Sql Server database which has a different schema with a reorganized structure. This Sql Server database will be used with a new version of our application in development. I've been writing migrating code in C# that calls Sql Server and Access and transforms the dat...
I am working on an old database I inherited from my predecessors. In it, some float fields contains NaN where there should be a null. The following SQL doesn't work because it doesn't recognize NaN. UPDATE xxx SET column= null WHERE column=NaN How can I do this? ...
I've got a report in SSRS 2008 that is using a web service as one of its data sources. The basic look of the XML returned is <table> <row> <column1>data</column1> <column2 xsi:nil="true" /> <column3>data</column3> </row> </table> Any tags with the "nil" attribute are showing up as blank on the report. I...
Hi i wish to modify a string. In ssis I have a step which is a "Derived column transformation editior". I have a string such as: edit=style?form=exy?test=x~~StringIWantToRemove I wish to remove "~~StringIWantToRemove" "~~" is the delimiter "StringIWantToRemove" is a random string og any value (apart from the delimiter) I would try ...
I have created a report in SSRS that contains 2 matrices (not tables/lists), each with a subtotal. What I am trying to work out is if there is any way to calculate a 'grand total' at the bottom of my report that is the sum of the subtotals from my 2 matrices. Matrix 1: 10 10 10 5 10 5 5 ...
Hello everyone. So I'm trying to migrate a MySQL-based app over to MS Sql Server 2005 (not by choice, but that's life). In the original app, we used almost entirely ANSI-SQL compliant statements, with one significant exception -- we used MySQL's group_concat function fairly frequently. group_concat, by the way, does this: given a tabl...
Let's say I have a table "foo" that I want to script out using SMO. Is there a way to have it so that in the generated script the table has a different name such as "foo2"? Database db = sqlServer.Databases["testdb"]; Table foo = db.Tables["foo"]; foo.Name = "foo2"; If I do this, I get this exception when I try to set foo.Name: "Can...
I receive this error when I try to connect to SQL Server 2005. I have enabled TCP/IP, Named Pipes, and restarted the server but that is not working. ...
I have an existing SQL Server 2005 database that runs our accounting/inventory application. We are looking at using a new on-line ordering framework - which has it's own database. If we use this new framework, we will need to transfer the on-line ordering data (inventory, prices, orders, customers) - almost realtime - to and from, our ...
I have a SQL query that is supposed to pull out a record and concat each to a string, then output that string. The important part of the query is below. DECLARE @counter int; SET @counter = 1; DECLARE @tempID varchar(50); SET @tempID = ''; DECLARE @tempCat varchar(255); SET @tempCat = ''; DECLARE @tempCatString varchar(5000); SET @t...
I need to put versions onto a SQL Server 2005 database and have these accessible from a .NET Application. What I was thinking is using an Extended Properties on the Database with a name of 'version' and of course the value would be the version of the database. I can then use SQL to get at this. My question is does this sound like a go...
I have a replication setup it was up and running yesterday, now I can't connect to the distributor and I don't know why. The publisher is on a Domain and talks to the subscriber (not on the domain) via VPN. I can ping the publisher from the subscriber and vice-versa. I can log in to MSSQL on the publisher from the subscriber. The publi...
We're planning to move a SQL 2000 database to SQL 2005, and I am familiar with the ability in 2005 to create tables or other objects under a variety of owner/schemas. We didn't really have that ability in SQL 2000, so I'm wondering what my guidelines/best practices would be for creating/managing multiple schemas. Should I create one sc...