Tag: database

Exclude rows if LEFT JOIN table record count is more than one

Two tables: Table1 fax_history fax_key 1001 1002 Table2 > fax_history_status fax_key Status 1001 NEW 1001 SUCCESS 1002 NEW Now I need to write a join query which will return only fax_key=1002 record because fax_key=1001 has MORE THAN ONE record in fax_history_status table. So the query result should be: fax_key status 1002 NEW

Group by is throwing error in SQL Server

I want to group by service name to all my record and I am using this query in SQL Server but it’s throwing an error select max(c.service_id) as service_id, a.ser_id, b.UserID, SQRT(POWER(69.1 * ( @latitude – b.Latitude),2) + POWER(69.1 * ( b.Longitude – @longitude ) * COS(b.Longitude / 57.3), 2)) as distance, c.service_name from aspnet_bawe_services […]

How can I generically detect if a database is 'empty' from Java

Can anyone suggest a good way of detecting if a database is empty from Java (needs to support at least Microsoft SQL Server, Derby and Oracle)? By empty I mean in the state it would be if the database were freshly created with a new create database statement, though the check need not be 100% […]

SQL Server: Create new query that matches prices and values from two separate queries

I have a database problem that I have no idea how to code so any kind of help will be much appreciated. Basically it is matching different schedules with their corresponding prices. I have two queries . . . First query contains prices based on whether it is a peak hour (Hour 7 to 22 […]

Call a user defined function defined in schemaA from a view defined in SchemaB SQL Server

I am having a problem to call a function from my view. They are both in different schemas, so I have something like this: View [SchemaA].[ViewName] WITH SCHEMABINDING AS SELECT DISTINCT [SchemaB].[functionName](value) as ‘variable’ FROM //Several selects with joins The problem is that I am getting this error: “Cannot schema bind view ‘SchemaA.view’. ‘SchemaB.functionName’ is […]

Store aggregate data in a table or a view?

I am doing a project in MS SQL server and i am currently concerned about the following case. CartItems (table) CartID ItemID Value 1 51 100 1 52 100 If I want to have a cart sum available as a fixed value and not to be calculated with SUM for each query do I a) […]

Using ASP.NET tables generated by aspnet_regsql.exe in a SQLite database

I’m building a small ASP.NET MVC site where I want to use SQLite. While I already know how I will be connecting to the database (using DbLinq), I don’t understand how to put the ASP.NET tables generated by aspnet_regsql.exe into an SQLite database. I’ve used the regsql tool before with SQL Server, but never with […]

Getting Started with Sync Framework

I’m having some trouble getting started with the Sync Framework. In doing research, it looks like the Sync Framework is a better fit for what we need than replication or a custom-rolled solution. Basically, we need to keep some datasets synced from our central office with every store in the company to overcome shortfalls with […]

Table Group By – Tsql –

alt text http://img187.imageshack.us/img187/8453/testhc3.png alt text http://img145.imageshack.us/img145/3306/test2bn4.png The first picture is my query. I need to obtain results in second picture. select OrarioA, OrarioB, MAX(VW_DettaglioOrariLinee_FromAToB.IDOrario), dbo.VW_DettaglioOrariLinee_FromAToB.IDDettaglioOrarioA, dbo.VW_DettaglioOrariLinee_FromAToB.IDDettaglioOrarioB FROM dbo.VW_DettaglioOrariLinee_FromAToB INNER JOIN Tb_Linee ON dbo.VW_DettaglioOrariLinee_FromAToB.IDRelA = Tb_Linee.IDRelA AND dbo.VW_DettaglioOrariLinee_FromAToB.IDRelB = Tb_Linee.IDRelB INNER JOIN dbo.periodi ON dbo.VW_DettaglioOrariLinee_FromAToB.IDOrario = dbo.periodi.IDOrario INNER JOIN dbo.relgiornisettimanaorarilinee ON dbo.VW_DettaglioOrariLinee_FromAToB.IDOrario = dbo.relgiornisettimanaorarilinee.IDOrario This is […]

Mysterious CPU Activity in MS SQL 2005

I am looking at the activity monitor in MS SQL Management Studio 2005, and I see an entry from a login that I created, and it’s using the tempdb, and the last command is always “SELECT INTO”. And everytime I hit “Refresh” in activity monitor, that entry’s “CPU” column goes up by like 60. The […]

MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.