Transact-SQL Forum

Test Post for Moderators

Tue, 24 Nov 2009 22:56:42 Z

This is a test post for Moderators to use when testing the 'self-answer'/points question.

Just reply, and immediately mark it as answer. System latency may require several hours for points to display.

This thread will be deleted by 11/30

You may be only one person in the world, but you may also be the world to one person.

subtracting millisecond from datetime

Wed, 25 Nov 2009 07:22:28 Z

Why the below query returns 2009-11-25 23:59:59.997 (input value) as result?

SELECT DATEADD(ms,1,'2009-11-25 23:59:59.997')

Asharaf

asharafmail@gmail.com

Aggregate errors

Wed, 25 Nov 2009 06:38:38 Z

Hi all, need help on this query. I need to select the total rents to be paid and I also need to know a count of each amount because rents can be equal or different depending on the service charges. There may be more than one service charge which is why I am using a sum of the serv.amount. My problem is that I cannot include the sum(x) in a count statement. Can anyone help on how to do this accurately, not very good with aggregate functions. my code is below:

DECLARE @ID BIGINT
SET @ID=543355

SELECT DISTINCT

--rent.valuedate   as rentdate,
--rent.amount      as rent,
--serv.amount      as services,
--expense.amount   as expense,
--ins.amount       as insuarance,

(rent.amount+sum(serv.amount)+expense.amount +ins.amount) as totalrent,
count((rent.amount+sum(serv.amount)+expense.amount +ins.amount)) as numRents

FROM operation op
  INNER JOIN rent  ON co.Did = rent.ownerdid
  INNER JOIN ins  ON rent.ownerdid = ins.aptdid
  INNER JOIN expense ON ins.expnumber=expense.nid
  LEFT OUTER JOIN serv     on ins.servaptdid= serv.did

where 1=1
And op.did=@id
group by rent.amount,serv.amount,expense.amount,ins.amount

Restore

Wed, 25 Nov 2009 05:15:58 Z

I have a valid backup in one server (admindb)
i want to restore that backup file into another server(AdminRS)
how to do that?

Dynamic stored procedure execution form in SQL Server 2005 Management Studio is missing.

Wed, 25 Nov 2009 06:15:50 Z

Hello!

I'm not sure what happened. But when I right click on a store procedure, the execute option is gone. I'm not sure what happened. I double checked my permissions, everything seems to be right. I'm looking how I can get that option back. Running Sql2005.

Thanks!

Rudy

Access Object

Wed, 25 Nov 2009 06:05:49 Z

I ve created the user "Tom"
I gave rights to access only select tables on sample db
now i want to give rights to view only particular tables like salesorder
other than sales order he wont access

How to give privileges?

How to delete/drop all the tables from SQL Server Database without using Enterprise Manager?

Wed, 13 Sep 2006 08:42:47 Z

How to delete/drop all the tables from SQL Server Database without using Enterprise Manager?

I tried using DROP Tables, Truncate Database, Delete and many more but it is not working. I want to delete all tables using Query Analyzer, i.e. through SQL Query.

Please help me out in this concern.

Nishith Shah

Temporary Tables inside a procedure

Wed, 25 Nov 2009 05:18:46 Z

Why this is not working in SQL Server?

CReate Proc TestProc as

Begin

  Select * into #abc from sys.tables

  Drop table #abc

  Select * into #abc from sys.objects

End

I am converting some procs from Sybase to SQL server 2005 and this works fine in Sybase .Is there any work around other than giving different name to temp tables each time I use it? I dont want to use dyanamic SQL.

variable in Order By clause will affect output

Wed, 25 Nov 2009 04:55:46 Z

Please refer to following TSQL scripts:

-----------------------------------------------
create table #test(
select_value nvarchar(100),
sort_order_1 int,
sort_order_2 int,
)

insert into #test values('field1_value1', 1, 2)
insert into #test values('filed1_value2', 2, 1)

declare @result nvarchar(max) = ''
declare @parameter int = 1

select @result = @result + ' ' + select_value
from #test
order by (case @parameter when 1 then sort_order_1 else sort_order_2 end)

select @result

drop table #test
-----------------------------------------------

Replace @parameter with constant 1 and run again, you will find the value of @result is different.
Any idea how it happens?
BTW, I tested in SQL 2000 (8.00.2040 SP4 Standard Edition) & SQL 2008 (10.0.1600.22 RTM Standard Edition (64-bit))

What is the best way to design a table for keeping a schedule?

Tue, 24 Nov 2009 11:55:05 Z

I want to create a table or tables that will be used to store appointments for multiple people. This is similar to what a business would use to schedule for four people and to manage their appointments. There are a few things that struggle with as far as conceptualization of this.

One of them is potential of the days in the future. I'm referring to the way a calender works and how there are different number of days in different months. It is the variation in the number of days that would make it difficult. Similarly, if someone wants to schedule an appointment in the following year. a particular date doesn't fall on the same day each year. For example today is 11/24/2009 which is a Tuesday. Nest year, 11/24/2010 is a Wednesday. I think this pattern is consistent, that it adds one day of the week each year for subsequent year. But there are also leap years to consider which adds a day in February. This is every year that the last two digits are evenly divisible by four.

I originally was trying to think of how to make the database for the calender dynamic, as far as adding a new row for each day as needed. Similar to a table that would hold employees adds a new row for each new employee inserted. But this doesn't seem possible given the way the calender works. So then I thought I would have to make a table or tables that are preformatted with the dates and days of the week for that year for each of the years that I want included in the db. That way it is structured to deal with the anomalies of the subsequent years. It would make sense to design it out a few years.

The other thing that I was struggling with is the complexity that comes with having a schedule for multiple people. This is coupled with having 2 or 4 time slots per hour for each work day. This could end up being (with a 1 hour lunch break) 14 or 28 time slots a day for one person, depending on if they are in 30 minute or 15 minute intervals. If there are 4 people on the schedule then this turns into 56 or 112 slots per day for the group of people.

Can someone please help me understand how to deal with this. Am I on the right track? Any advice would be greatly appreciated.

String pattern - TSQL 2005

Wed, 25 Nov 2009 04:57:29 Z

Hi, how to print below string with PROPER text ALIGNMENT using simple T-SQL 2005? Thanks.
                1
               22
              333
             4444
            55555
           666666
          7777777
         88888888

Login via Sproc

Tue, 24 Nov 2009 10:53:20 Z

when i tried to execute the login as Sproc
it gives the following error

Msg 102, Level 15, State 1, Procedure test_user, Line 5

Incorrect syntax near '@login'.

Msg 319, Level 15, State 1, Procedure test_user, Line 5

Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
========================================================

create

PROCEDURE [dbo].[test_user] (@login varchar(100),@pwd varchar(100))

--DECLARE @sql nvarchar(150)

BEGIN

CREATE

DEFAULT_LANGUAGE

=[english];

END

How to import data from Access2007 to SQL 2005

Tue, 29 Jan 2008 19:53:21 Z

Can anyone help me out with importing data from access2007 to sql 2005???

Thanks! I appreciate it!

month/year datetime comparisons

Tue, 24 Nov 2009 20:28:46 Z

I have to get just the month and year from a datetime field and do comparisons.

It doesn’t seem like this works 100%. There are always records that slip through the cracks

I’ve tried this

when

cast(month(due_date_next_payment) as varchar(2)) + '/' + cast(year(due_date_next_payment) as varchar(4))

> =

cast(month(DATEADD(month ,1, GETDATE())) as varchar(2)) + '/' + cast(year(DATEADD(month ,1, GETDATE())) as varchar(4))

then etc..

and this

when

DATENAME(month,due_date_next_payment) + ' ' + DATENAME(year, cs_loan_information.due_date_next_payment)

datename(month, dateadd(month ,1, Getdate()))+ ' '+ datename(year, dateadd(month ,1, Getdate()))

then 0

Any suggestions?

Exception Error 5123 - Unable to attach database

Tue, 29 Jan 2008 20:52:30 Z

Hello.

Our GIS group is working with SQL Server 2005 in conjunction with ESRI's ArcGIS software. SQL Server is being used as a test bed until we formally migrate our existing Microsoft Access mdb's over. So, at this time we are conducting various tests and have the freedom to "blow up" our databases.

We are currently testing ArcSDE Workgroup edition, which will only work with SQL Server Express (SSE). SSE will let us create spatial joins and relationships, while maintaining data integrity. So one possible workaround was to detach the database from SQL Server 2005 and then attach it to SSE. Unfortunately, we ran into a problem in re-attaching the database back to SQL Server 2005.

The exact steps are as follows:

Back up database using back up tool in SQL Server 2005.
Detached database from SQL Server 2005.
Attached database to SQL Server Express (hereafter SSE).
Created a table in database while it was attached to SSE.
Detached database from SSE.
Attempt to attach database to SQL Server 2005 failed
Successfully restored database in SQL Server 2005 using Restore tool.

Step number 6 above failed and generated the following error:

TITLE: Microsoft SQL Server Management Studio
------------------------------

Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\LNRIS_SQL_Test.mdf'. (Microsoft SQL Server, Error: 5123)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=5123&LinkId=20476

Questions:

Why did I get this error?
Can incorrect or missing permission or ownership settings generate this kind of error?
When should one detach/attach a database?

I welcome your assistance and suggestions.

fastest way to copy huge data

Tue, 24 Nov 2009 15:26:23 Z

Hi,
Probably this question has been asked many time before. Reading up on the documentation and comments from people I find it difficuilt to conclude which way is the fastest to copy data within sql server. Below is the scenario i want to deal with, your input will be highly appreciated:

I want to copy huge chunk of data (around 60-70 GB of data) in one go. Data source as well as destination is sql server. Both source and destination are on same sql server instance (so network or linked servers). I will be copying data from many tables in to the same table structure in destination database e.g. sourcedb..table1 into destinationdb..table1, sourcedb..table2 into destinationdb..table2 etc. It will be incremental insert also going forward (so cant use SELECT ... INTO ... FROM). Most of the tables contain over and around 50 million records. Dont have DTS or SSIS, or I have to do it through .Net or t-sql.

I thought about following:
BCP: fast but I am concerned about time, coz one have to take the data out and then bring it back in, it can effect time as well as space issue
.NET SqlBulkCopy: While reading up on SqlBulkCopy I found that using INSERT....SELECT statement is faster if the source and destination both reside on the same sql server
INSERT....SELECT : Can cause timeout issues and I dont know if it will be quite fast

Basically I want to avoid timeout issues as well as want to make sure that data copies as fast as possible (I dont have access to DTS or SSIS).

Any suggestions based on you experience.

Thanks
Khalid

I was givin a quiz with 10 questions

Tue, 24 Nov 2009 22:35:15 Z

Hi,
I was given a quiz with 10 questions to answer and I was wondering if anyone can help. I will post the code to generate the tables including the questions below.

/*
Test item #1:
Return the First Name, Last Name, Product Name, and Sale Price for all products sold in the month of October 2005.

Test item #2:
Return the list of Customer that have made no purchases.

Test item #3:
Return the First Name, Last Name, Sale Price, Recommended Sale Price, and the difference between the Sale Price
and Recommended Sale Price for all Sales.
The difference must be returned as a positive number.

Test item #4:
Return the average Sale Price by Product Category.

Test item #5:
Add the following Customer and Sale information to the database.
FirstName: Chris
LastName: Kringle
City: Henryville
State: IN
Zip: 47126
ProductID: 3
SalePrice: 205
SaleDate: 12/31/2005

Test item #6:
Delete the customer(s) from the database who are from the state of Maine ('ME').

Test item #7:
Return the Product Category and the average Sale Price for those customers who have purchased two or more products.

Test item #8:
Update the Sale Price to the Recommended Sale Price of those Sales occurring between 6/10/2005 and 6/20/2005.

Test item #9:
Number of Sales by Product Category where the average Recommended Price is 10 or more dollars greater than the average Sale Price.

Test item #10:
Return the Sale Date, Sale Price, and the running total for all sales orders by Sale Date.
*/
--CREATE DATABASE SqlTest
--GO

USE SqlTest
GO

IF OBJECT_ID('Sales') > 0
    DROP TABLE Sales

GO

IF OBJECT_ID('Customers') > 0
    DROP TABLE Customers

GO

IF OBJECT_ID('Products') > 0
    DROP TABLE Products

GO
CREATE TABLE Customers (
    CustomerID INT IDENTITY PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    City VARCHAR(50),
    State CHAR(2),
    Zip VARCHAR(10)
)

GO

GRANT SELECT ON Customers TO public
GO

CREATE TABLE Products
(
ProductID TINYINT IDENTITY PRIMARY KEY,
ProductName VARCHAR(20),
RecommendedPrice MONEY,
Category VARCHAR(10)
)

GO

GRANT SELECT ON Products TO public
GO

CREATE TABLE Sales

(
SaleID INT IDENTITY PRIMARY KEY,
ProductID TINYINT NOT NULL REFERENCES Products(ProductID),
CustomerID INT NOT NULL REFERENCES Customers(CustomerID),
SalePrice MONEY NOT NULL,
SaleDate SMALLDATETIME NOT NULL
)

GO

GRANT SELECT ON Sales TO public
GO

INSERT INTO Products(ProductName, RecommendedPrice, Category)

VALUES('DVD',105,'LivingRoom')

INSERT INTO Products(ProductName, RecommendedPrice, Category) VALUES('Microwave',98,'Kitchen')

INSERT INTO Products(ProductName, RecommendedPrice, Category)

VALUES('Monitor',200,'Office')

INSERT INTO Products(ProductName, RecommendedPrice, Category)

VALUES('Speakers',85,'Office')

INSERT INTO Products(ProductName, RecommendedPrice, Category) VALUES('Refrigerator',900,'Kitchen')

INSERT INTO Products(ProductName, RecommendedPrice, Category)

VALUES('VCR',165,'LivingRoom')
INSERT INTO Products(ProductName, RecommendedPrice, Category) VALUES('CoffeePot',35,'Kitchen')

GO

INSERT INTO Customers(FirstName, LastName, City, State, Zip) VALUES('John','Miller','Asbury','NY','23433')

INSERT INTO Customers(FirstName, LastName, City, State, Zip) VALUES('Fred','Hammill','Basham','AK','85675')

INSERT INTO Customers(FirstName, LastName, City, State, Zip) VALUES('Stan','Mellish','Callahan','WY','38556')

INSERT INTO Customers(FirstName, LastName, City, State, Zip) VALUES('Adrian','Caparzo','Denver','CO','12377')

INSERT INTO Customers(FirstName, LastName, City, State, Zip) VALUES('Mike','Horvath','Easton','IN','47130')

INSERT INTO Customers(FirstName, LastName, City, State, Zip) VALUES('Irwin','Wade','Frankfurt','KY','45902')

INSERT INTO Customers(FirstName, LastName, City, State, Zip) VALUES('George','Marshall','Gallipoli','ND','34908')

INSERT INTO Customers(FirstName, LastName, City, State, Zip) VALUES('Frank','Costello','Honolulu','HI','23905')

INSERT INTO Customers(FirstName, LastName, City, State, Zip) VALUES('Billy','Costigan','Immice','SC','75389')

INSERT INTO Customers(FirstName, LastName, City, State, Zip) VALUES('Shelly','Sipes','Lights','AZ','35263')

INSERT INTO Customers(FirstName, LastName, City, State, Zip) VALUES('Chirsty','Melton','Spade','CA','97505')

INSERT INTO Customers(FirstName, LastName, City, State, Zip) VALUES('Amanda','Owens','Flask','CN','50386')

INSERT INTO Customers(FirstName, LastName, City, State, Zip) VALUES('Brittany','Smits','Bourbon','KY','24207')

INSERT INTO Customers(FirstName, LastName, City, State, Zip) VALUES('Kristy','Bryant','Tarp','FL','58960')

INSERT INTO Customers(FirstName, LastName, City, State, Zip) VALUES('Kelly','Street','TableTop','ID','57732')

INSERT INTO Customers(FirstName, LastName, City, State, Zip) VALUES('Tricia','Hill','Camera','ME','46738')

INSERT INTO Customers(FirstName, LastName, City, State, Zip) VALUES('Holly','Raines','Compact','MS','35735')

INSERT INTO Customers(FirstName, LastName, City, State, Zip) VALUES('Natalie','Woods','Woods','IN','87219')

INSERT INTO Customers(FirstName, LastName, City, State, Zip) VALUES('Wendy','Hilton','Action','KY','47093')

GO

INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(1,1,130,'2/6/2005')

INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(2,2,97,'1/7/2005')

INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(3,3,200,'8/8/2005')

INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(4,4,80,'4/9/2005')

INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(5,5,899,'10/10/2005')

INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(6,6,150,'10/11/2005')

INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(3,7,209,'12/12/2005')

INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(4,8,90,'5/13/2005')

INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(6,9,130,'6/14/2005')

INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(2,14,85,'6/19/2005')

INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(3,15,240,'9/20/2005')

INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(1,16,99,'7/21/2005')

INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(1,17,87,'3/22/2005')

INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(2,18,99,'1/23/2005')

INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(6,19,150,'3/24/2005')

INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(5,5,900,'3/10/2005')

INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(4,6,86,'8/11/2005')

INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(2,7,88,'8/12/2005')

INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(3,8,198,'12/13/2005')

INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(1,9,150,'5/14/2005')

INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(6,14,99,'7/19/2005')

INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(6,15,104,'9/20/2005')

INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(3,16,270,'2/21/2005')

INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(4,17,90,'7/22/2005')

INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(1,1,130,'3/6/2005')

INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(2,2,102,'4/7/2005')

INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(1,3,114,'11/8/2005')

INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(5,4,1000,'5/9/2005')

INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(5,5,1100,'10/10/2005')

INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(3,6,285,'6/11/2005')

INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(2,7,87,'10/12/2005')

INSERT INTO Sales(ProductID, CustomerID, SalePrice, SaleDate) VALUES(3,8,300,'7/13/2005')

GO

chuckdawit

Selecting a float Value

Tue, 24 Nov 2009 20:08:56 Z

Hello,

I have a table with column A, data type float. There are some values in that, say 20.00 or 302.90
When i do this query

Select * from tableA
where ColumnA=20.00

not getting any results , even value is there....
I did try like
Select * from tableA
where ColumnA='20.00'

Still No Results

I converted and tried again

Select * from TABLEA
where Cast( ColumnA as varchar(50)='20.00'

Still no luck:(

What I am missing? any help!!

T-SQL Solutions: Most Intuitive vs. Best Performing

Tue, 10 Nov 2009 22:27:49 Z

I have noticed several times in this forum that a perfectly good T-SQL solution came under attack because was not the best performing solution.

Yes, performance is important, but that is not the only factor in the life of a DBA or database developer. Foremost they have to be able to deliver solutions, frequently "under the gun".

Therefore, my view is, that a good DBA/developer has to have a repertoire of intuitive, easy-to-remember and reliable solutions, because SS performance not always important, but human performance is. Also a good DBA/developer has to know performance enhancing techniques, because performance issues are important as well in operating a SQL Server system.

What do you think?

Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com

using where clause with Freetexttable, partition table

Mon, 23 Nov 2009 14:37:08 Z

Hello..

I am using SQL Server 2005 and have a table with millions rows; this table contains data for more than one project (defined by 'projectID' tinyint column).

Also these data in multiple languages (defined by 'lang' tinyint column).

I am using full text search (FreetextTable) for searching; the results are nice when searching in the whole of table.

But when I want to specify search in one project or one language, the query will be too slow and returns wrong results.

That is because I use (where clause) with (freetexttable) and at the same time, I am using the (top_n_by_rank) parameter of the (freetexttable).

My problem discussed in these links:

http://www.developmentnow.com/g/104_2004_11_0_0_399982/Containstable-problem.htm

http://www.sql-server-performance.com/articles/all/tb_search_optimization_p1.aspx

http://www.sql-server-performance.com/articles/all/tb_search_optimization_p2.aspx

but that solution is not convince.

So I hope to find the best solution for this! I am thinking for partitioning the table and searching in specific petitions.

I overhear that ORACLE DB supporting virtual partitioning for the tables to solve such a problem.

Any help please..

Thanks.

how to implement procedure for end-day batch processing ?

Tue, 24 Nov 2009 02:56:16 Z

i would like to roughly arrange the code as following

create proc end_day_proc
as
begin  --begin of end-day processing
      --some sql statements
      ...
      exec sub_task1
      ...
      exec sub_task2
      ...
      exec sub_task3
      ...
      exec log


end --end of end-day processing

each task reads and writes to DB,and i hope all the things done in ONE transaction,which means if an error occur in any of the sub_tasks,the transaction should be rollbacked.
The log procedure invoked in the end_day_proc writes the result－success or failure,to DB
In the other hand,if an error occurs in any sub_task,the error should be logged(by calling a log procedure in the sub_task).

Now, if an error in a sub_task is logged in DB,while in outer proc－end_day_proc, an subsequential action rollbacks the transaction.
how can the logging action in the subtask avoid be rollback?

Any suggestion will be appreciated.Thanks.

Andrew

Interested in BI related technologies

help with joining two query

Tue, 24 Nov 2009 21:30:43 Z

I have two sets of query

First is as follows

SELECT FULLKEY, LOAD_ID, PREMISE_NO, RISK_NO, PACKAGE_CATEGORY,RowNum=ROW_NUMBER()OVER(PARTITION BY FullKey ORDER BY PREMISE_NO) FROM

COVERAGE WHERE LOAD_ID = '20090630'

AND Status_sw = 'L'

AND RISK_NO = 1 AND PACKAGE_CATEGORY IN ('P', 'S')

AND Status_sw = 'L'

And Form_type in ('A','B','C','D','E','F','G')

Second is as follows

select FULLKEY, LOAD_ID, POLICY_NO, STATUS_SW, AMEND_DATE, PREMISE_NO, STREET_NO,

STREET_NAME, TERRITORY, CITY, PROVINCE, POSTAL_CODE, RATING_TERRITORY, PCODE_PROTECTION,

FIREHALL_LOCATION, RISK_NO, FORM_CODE, PACKAGE_CATEGORY, PACKAGE_TYPE, FLT_COUNT,

RISK_START_DATE, PREFERRED_RATE, OCCUPANCY, PROTECTION, SMOKE_DETECTOR,

RESIDENCE_TYPE, YEAR_BUILT, STRUCTURE,CONSTRUCTION_CODE, TOTAL_NET, ROOF_CONST,

HEAT_PRIMARY, HEAT_SECONDARY,RowNum from

(select cte.fullkey,cte.premise_no,cte.load_id,cte.policy_no,cte.status_sw,cte.amend_date,

cte.street_no,cte.street_name,cte.territory,cte.city,cte.province,cte.postal_code,cte.rating_territory,cte.pcode_protection,

cte.firehall_location,cte.RISK_NO,cte.FORM_CODE,cte.package_category,cte.PACKAGE_TYPE,cte.FLT_COUNT,cte.RISK_START_DATE,cte.PREFERRED_RATE,cte.OCCUPANCY,

cte.PROTECTION,cte.SMOKE_DETECTOR,cte.RESIDENCE_TYPE,cte.YEAR_BUILT,cte.STRUCTURE,cte.CONSTRUCTION_CODE,cte.TOTAL_NET,

cte.ROOF_CONST,cte.HEAT_PRIMARY,cte.HEAT_SECONDARY,cte.RowNum from

(

SELECT B.fullkey,B.premise_no,B.load_id,left(B.fullkey,10) as policy_no,B.status_sw,convert (varchar(8),B.Amend_date,112) as amend_date,

B.street_no,B.street_name,B.territory,B.city,B.province,B.postal_code,B.rating_territory,B.pcode_protection,

B.firehall_location,A.RISK_NO,A.FORM_CODE,A.Package_Category,A.PACKAGE_TYPE,A.FLT_COUNT,CONVERT(VARCHAR(8),A.RISK_START_DATE,112) AS

RISK_START_DATE,A.PREFERRED_RATE,A.OCCUPANCY,A.PROTECTION,A.SMOKE_DETECTOR,A.RESIDENCE_TYPE,A.YEAR_BUILT,A.STRUCTURE,A.CONSTRUCTION_CODE,A.TOTAL_NET,

A.ROOF_CONST,A.HEAT_PRIMARY,A.HEAT_SECONDARY,RowNum=ROW_NUMBER()OVER(PARTITION BY A.FullKey ORDER BY B.PREMISE_NO)

FROM

RISK A INNER JOIN PREMISE B

ON A.FULLKEY = B.FULLKEY

AND A.LOAD_ID = '20090630'

AND A.LOAD_ID = B.LOAD_ID

AND A.RISK_NO = '1'

AND A.PACKAGE_CATEGORY IN ('P','S'))cte

right outer JOIN

(select fullkey,premise_no,load_id,left(fullkey,10) as policy_no,status_sw,convert (varchar(8),Amend_date,112) as amend_date,

street_no,street_name,territory,city,province,postal_code,rating_territory,pcode_protection,

firehall_location,RowNum=ROW_NUMBER()OVER(PARTITION BY FullKey ORDER BY PREMISE_NO)

from premise where load_id = '20090630') X

on cte.fullkey = X.fullkey

and cte.RowNum = X.RowNum)common

The first query returns 259237 rows

Where as the secont query returns 42820 rows

Now I want to put a inner join between both of these query and get the 42820 rows.

Any suggestion on joining these queries will be appreciated

select from two tables and add a category

Mon, 23 Nov 2009 21:49:08 Z

I have two different tables: clients & companies.

I want to select
client_id and client_name from clients
company_id and company_nam from companies

and i want to add a category column so if the row was from clients tables it should should as L but if it's from companies tables then it should show as C

also client_name & companiy_name must be listed under one column, let's say full_name

can any one help plz...

Jassim Rahma

2 versions of case, 1 works, 1 doesn't, why???

Tue, 24 Nov 2009 21:21:43 Z

We have a problem here at work, and trying to figure out the difference. I have used sample data below...Here's the setup for a sample table to run:

if object_id ('tempdb..#animal') is not null drop table #animal
if object_id ('tempdb..#tool') is not null drop table #tool

create table #animal
(
	animal_id int,
	animal_type varchar(25)
)

create table #tool
(
	tool_id int,
	tool_type varchar(25),
	tool_animalid int
)

insert into #animal	(animal_id, animal_type)
			select 1,'horse'
	union select 2,'cat'
	union select 3,'bird'
	union select 4,'dog'

insert into #tool	(tool_id, tool_type,	tool_animalid)
			select 1,'leash',4
	union select 2,'saddle',1

/*
Select * from #animal
Select * from #tool
*/

Now when I run the following 2 statements, 1 works and one doesnt - the problem is with the case - I thought they were essentially the same thing??

This does NOT work (give correct results)

Select a.animal_type, t.tool_type, case t.tool_type when null then 'No Match' else 'Match' end as IsMatched
from #animal as a
left join #tool as t
on a.animal_id = t.tool_animalid

this DOES INDEED work:

Select a.animal_type, t.tool_type, case when t.tool_type is null then 'No Match' else 'Match' end as IsMatched
from #animal as a
left join #tool as t
on a.animal_id = t.tool_animalid

Can someone please explain the difference in the 2 versions of the case statement? Thanks.

Im using SQL Server 2005, Microsoft SQL Server 2005 - 9.00.3077.00 (Intel X86) Dec 17 2008 15:19:45 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

non-ANSI compliant join operators

Tue, 24 Nov 2009 21:17:03 Z

I have moved a database from SQL Server 2000 to SQL Server 2005. Currently it is running in SQL 2000 compatiblitiy level (80). I need to switch it to SQL Server 2005 (90) compatibility level. Before I an do that, any code that currently has non_ANSI compliant join operators must be coverted to be ANSI compliant. My problem is that where the optional table has conditions set to it, nothing gets picked up. How can I restrict the predicate on the optional table, but still retrieve data about the main table?

The attached code is an example of code that works using the *= operator but does not work using left outer join.

SELECT n.ID,n.COMPANY,c.PRODUCT_CODE
 FROM Name n , Activity c 
 where n.ID *= c.ID  
 and n.ID='ABCDEF'		
 and c.ACTIVITY_TYPE = 'CONTACT' 
 and c.PRODUCT_CODE = 'RECRUIT'

SELECT n.ID,n.COMPANY,c.PRODUCT_CODE
 FROM Name n left outer join Activity c 
 on n.ID = c.ID  
 where n.ID='ABCDEF'		
 and c.ACTIVITY_TYPE = 'CONTACT' 
 and c.PRODUCT_CODE = 'RECRUIT'

Problem With OPENROWSET

Tue, 24 Nov 2009 18:52:36 Z

I'm having a problem with a openrowset statement, in a database some tables return the openrowset and some don't

This Statement Works perfectly

select b.* into #users from OPENROWSET('SQLOLEDB', 'Trusted_Connection=yes;Integrated Security=SSPI;Data Source=MS-BI-02;Initial Catalog=dwhtanque','Select * from dbo.usuarios') b

This doesn't work

select b.* into #users from OPENROWSET('SQLOLEDB', 'Trusted_Connection=yes;Integrated Security=SSPI;Data Source=MS-BI-02;Initial Catalog=dwhtanque','Select * from dbo.td_cliente) b

and return the following error

OLE DB provider "SQLNCLI" for linked server "(null)" returned message "Deferred prepare could not be completed.".
Msg 8180, Level 16, State 1, Line 1
Statement(s) could not be prepared.
Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.td_cliente_tc'

the database server it's a SQL Server 2005, I'm not using linked servers, both table are in the same database
it's driving me crazy, Any ideas ????

Run index fragmentation on all databases

Thu, 12 Nov 2009 12:51:10 Z

I'm trying to run my index defrag process on all databases, I can easily write the query to pull out fragmentation information, but to run the defragmentation process is rather tricky on all databases, one way is to create a stored procedure on each and every database and run it against all the databases.

I am trying to wtite the query below so that it will run on every database using msforeachDB. It keeps failing due to the variables.

 
SET NOCOUNT ON

DECLARE @fragrate float
SET @fragrate = 30.0 

DECLARE @rbrirate float
SET @rbrirate = 50.0 

DECLARE @indexinfo TABLE(objectid int, indexid int, partitionnum bigint, frag float)

DECLARE @objectid int
DECLARE @indexid int
DECLARE @partitioncount bigint
DECLARE @schemaname nvarchar(130) 
DECLARE @objectname nvarchar(130) 
DECLARE @indexname nvarchar(130) 
DECLARE @partitionnum bigint
DECLARE @partitions bigint
DECLARE @frag float
DECLARE @command nvarchar(4000) 

INSERT INTO @indexinfo
SELECT
    object_id AS objectid,
    index_id AS indexid,
    partition_number AS partitionnum,
    avg_fragmentation_in_percent AS frag
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > @fragrate AND index_id > 0

DECLARE partitions CURSOR FOR SELECT * FROM @indexinfo
OPEN partitions

FETCH NEXT FROM partitions
	INTO @objectid, @indexid, @partitionnum, @frag

WHILE (@@fetch_status <> -1)
    BEGIN
        IF (@@fetch_status <> -2)
		BEGIN

			SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
			FROM sys.objects AS o
			JOIN sys.schemas as s ON s.schema_id = o.schema_id
			WHERE o.object_id = @objectid

			SELECT @indexname = QUOTENAME(name)
			FROM sys.indexes
			WHERE  object_id = @objectid AND index_id = @indexid

			SELECT @partitioncount = count (*)
			FROM sys.partitions
			WHERE object_id = @objectid AND index_id = @indexid

			-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
			IF @frag < @rbrirate
				SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE'
			IF @frag >= @rbrirate
				SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD'
			IF @partitioncount > 1
				SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10))

			EXEC (@command)

		END
        FETCH NEXT FROM partitions
			INTO @objectid, @indexid, @partitionnum, @frag
    END

CLOSE partitions
DEALLOCATE partitions

I tried

   
declare @sql varchar(max)
set @sql = '
  
-- define index defrag rate for this run
DECLARE @fragrate float
SET @fragrate = 30.0 -- set to do something if defrag rate HIGH

DECLARE @rbrirate float
SET @rbrirate = 30.0 -- set to rebuild if defrag rate EXTREME

-- define table holding index information
DECLARE @indexinfo TABLE(objectid int, indexid int, partitionnum bigint, frag float)

DECLARE @objectid int
DECLARE @indexid int
DECLARE @partitioncount bigint
DECLARE @schemaname nvarchar(130) 
DECLARE @objectname nvarchar(130) 
DECLARE @indexname nvarchar(130) 
DECLARE @partitionnum bigint
DECLARE @partitions bigint
DECLARE @frag float
DECLARE @command nvarchar(4000) 

-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function 
INSERT INTO @indexinfo
SELECT
    object_id AS objectid,
    index_id AS indexid,
    partition_number AS partitionnum,
    avg_fragmentation_in_percent AS frag
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, ''LIMITED'')
WHERE avg_fragmentation_in_percent > @fragrate AND index_id > 0

-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT * FROM @indexinfo
OPEN partitions

-- Loop through the partitions.
FETCH NEXT FROM partitions
	INTO @objectid, @indexid, @partitionnum, @frag

WHILE (@@fetch_status <> -1)
    BEGIN
        IF (@@fetch_status <> -2)
		BEGIN

			SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
			FROM sys.objects AS o
			JOIN sys.schemas as s ON s.schema_id = o.schema_id
			WHERE o.object_id = @objectid

			SELECT @indexname = QUOTENAME(name)
			FROM sys.indexes
			WHERE  object_id = @objectid AND index_id = @indexid

			SELECT @partitioncount = count (*)
			FROM sys.partitions
			WHERE object_id = @objectid AND index_id = @indexid

			-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
			IF @frag < @rbrirate
				SET @command = N''ALTER INDEX '' + @indexname + N'' ON '' + @schemaname + N''.'' + @objectname + N'' REORGANIZE''
			IF @frag >= @rbrirate
				SET @command = N''ALTER INDEX '' + @indexname + N'' ON '' + @schemaname + N''.'' + @objectname + N'' REBUILD''
			IF @partitioncount > 1
				SET @command = @command + N'' PARTITION='' + CAST(@partitionnum AS nvarchar(10))

			EXEC (@command)
			PRINT N''Executed (Defrag '' + CAST(@frag AS varchar(12)) + ''): '' + @command
		END
		-- Get the next record to process
        FETCH NEXT FROM partitions
			INTO @objectid, @indexid, @partitionnum, @frag
    END

-- Close and deallocate the cursor.
CLOSE partitions
DEALLOCATE partitions

'

exec sp_MSforeachdb @sql

Clusterd index

Tue, 24 Nov 2009 18:05:29 Z

Hello

I'm running the following query

select   id

           ,participantid

           ,accountid 

from ParticipantAccountx

ID is a primary key and there is a clusterd index on it. ParticipantID and AccountID are foreign keys, and there is a nonclustered index on each one of them.

I just reorganized the clustered index, but when I run the query, the optimizer is choosing to do am Index Scan on the nonclustered index on ParticipantID.

Why is that?

Abdallah, PMP, ITIL, MCTS

PIVOT question

Thu, 12 Nov 2009 14:02:32 Z

I have a simple query that gives me the correct data.....

I just need to "pivot" the data and after reading the MSDN article on PIVOT I am still not having any luck.

The simple query is:

SELECT [Process Data].[_TIMESTAMP], [Process Data].[_NAME], [Process Data].[_VALUE]
FROM [Process Data]
WHERE ((([Process Data].[_NAME]) Like '%TANK30%') AND (([Process Data].[_TIMESTAMP]) Between #11/9/2009# And #11/11/2009#))
ORDER BY [Process Data].[_TIMESTAMP] DESC;

Query yields data like this:

**Process Data Query1**
_TIMESTAMP	_NAME	_VALUE
11/10/2009 11:59:56 PM	TANK30TC1.TANK30TC1.192_168_15_204.iSeries01.Main	126
11/10/2009 11:59:56 PM	TANK30TC1.TANK30TC1.192_168_15_204.iSeries01.SetPoint1	160
11/10/2009 11:59:56 PM	TANK30TC1.TANK30TC1.192_168_15_204.iSeries01.SetPoint2	0
11/10/2009 11:59:56 PM	TANK30TC1.TANK30TC1.192_168_15_204.iSeries01.Main	126
11/10/2009 11:59:56 PM	TANK30TC1.TANK30TC1.192_168_15_204.iSeries01.SetPoint1	160
11/10/2009 11:59:56 PM	TANK30TC1.TANK30TC1.192_168_15_204.iSeries01.SetPoint2	0
11/10/2009 11:58:56 PM	TANK30TC1.TANK30TC1.192_168_15_204.iSeries01.Main	126
11/10/2009 11:58:56 PM	TANK30TC1.TANK30TC1.192_168_15_204.iSeries01.SetPoint1	160
11/10/2009 11:58:56 PM	TANK30TC1.TANK30TC1.192_168_15_204.iSeries01.SetPoint2	0
11/10/2009 11:57:56 PM	TANK30TC1.TANK30TC1.192_168_15_204.iSeries01.Main	126
11/10/2009 11:57:56 PM	TANK30TC1.TANK30TC1.192_168_15_204.iSeries01.SetPoint1	160
11/10/2009 11:57:56 PM	TANK30TC1.TANK30TC1.192_168_15_204.iSeries01.SetPoint2	0
11/10/2009 11:56:56 PM	TANK30TC1.TANK30TC1.192_168_15_204.iSeries01.Main	127
11/10/2009 11:56:56 PM	TANK30TC1.TANK30TC1.192_168_15_204.iSeries01.SetPoint1	160
11/10/2009 11:56:56 PM	TANK30TC1.TANK30TC1.192_168_15_204.iSeries01.SetPoint2	0

What I need is for it to use the grouped "_TIMESTAMP" for the rows, the "_NAME" for the column headings and "_VALUE" for the value.

IE

                         _NAME1   _NAME2     _NAME3
_TIMESTAMP1     _VALUE1   _VALUE2   _VALUE3
_TIMESTAMP2     _VALUE1   _VALUE2   _VALUE3
_TIMESTAMP3     _VALUE1   _VALUE2   _VALUE3

I can't seem to get the syntax right.......

Is it posible to combine an delete and insert statement in the same stored procedure

Tue, 24 Nov 2009 14:20:02 Z

hi

Is it posible to combine an delete and insert statement in the same stored procedure.

What I want is to delete first and then insert new.

if it is posible how ?

best regard

jylland

Loading a complex XML (no schema) into a table

Tue, 24 Nov 2009 04:52:30 Z

Hi everybody,

I have SQL Server Express 2008, so I can not really run SSIS :( I want to import XML file like this

<?xml version="1.0"?>
<!--Timesheets and Jobs created at E I Day Tripping on 16-Apr-2009 at 3:00:07 PM-->
<Time siteName="E I Day Tripping" currPeriodEnd="03-Apr-2009">
  <Timesheet levelCode="SE0033" team="EI" periodEndDate="03-Apr-2009" periodForDate="03-Apr-2009" tsType="W" tsNumber="SYSTEM">
    <Booking bookingDate="03-Apr-2009" project="SKSWOOD" jobCard="SS-CPCA070801" workCode="D01" skillCode="EL03" shiftCode="DT" locationCode="OFFSHORE EI" normal="12"/>
  </Timesheet>
</Time>

into a new table(s). What are my options?

Thanks in advance.

Premature optimization is the root of all evil in programming. (c) by Donald Knuth

Naomi Nosonovsky, Sr. Programmer-Analyst

My blog

How do i check if the top 3 values and bottom two values = 3 in a column

Tue, 24 Nov 2009 15:49:39 Z

Hi,

I have table that has following fields

T_Id client_Id Submit_dt asam_values

1 15 1/1/2009 1,2,3,4,5,6

2 15 1/1/2009 1,1,1,1,1,1

etc.

I want to select those rows if the asam_values 1 or top 3 or first value is 3 and 2 of the bottom 3 is a 3 then i want to select that row else omit it?

How can i do it..

Any help will appreciated.

Thanks,

Karen

DTS package Dynamically used

Mon, 23 Nov 2009 23:23:02 Z

How Can I transfer Tables from One Server, Database to another server,database using Dynamic Properties task (Configuration files) or Global Variables. The table schema needs to copy first from the source server to Destination Server and then the data needs to be copied.

I tried two different way but I couldn't figure out how to dynamically get the table schema and create the table definition to destination server?

Date calendar 1 year

Tue, 24 Nov 2009 13:15:45 Z

Expert,
I need date calendar a year by using SQL, please..

Regards,

Jonel

SELECT NOT NULL COLUMN

Tue, 24 Nov 2009 15:25:33 Z

Linked Servers Slow

Fri, 24 Oct 2008 11:41:33 Z

Hi Everyone,

I'm in the UK and I'm connecting to a server in Canada.
On my London machine can connect directly to my server in Canada through Management Studio and execute a stored proc instantly (16ms). If I make the same call through a four part name logged into my local server in London, it takes 500ms. This is too slow for me. Is there some way to speed up my linked server given the way I've set it up above.
I can't beleive it's adding 0.5s to every call.

The linked server has the following settings...

Collation compatible True

Data Access True

RPC True

RPC Out True

Use Remote Collation True

Collation Name <Blank>

Connection Timeout 0

Query Timeout 0

Changing these setting made no difference.

The security is set up to "Be made using this security context:"

When I try and set it up using any other option I get problems...

I've tried setting up my authentication for my linked server differently but it just won't work. In the linked server properties window, I add a user mapping in the SQL Server Mappings Table. This is a user I can log in as and I've tried checking the impersonate check box and entering the same login and password in the remote login boxes as the local login.

I get the following error messages when I select the following options...

Not to be made Gave the following error:

"Access to the remote server is denied because no login-mapping exists" This clearly isn't true.

Be made without using a security context Gave the following error:

OLE DB provider "SQLNCLI" for linked server "orbcasql02" returned message "Invalid authorization specification".

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "SQLNCLI" for linked server "orbcasql02" reported an error. Authentication failed.

Msg 7303, Level 16, State 1, Line 1

Cannot initialize the data source object of OLE DB provider "SQLNCLI" for linked server "orbcasql02".

Be made using the login’s current security context Gave the following error:

Msg 18456, Level 14, State 1, Line 1

I've tripple checked the user name and password.

This last one is particularly confusing because there has been no mention of the user db and I was logged in as OPSWriter. I did change the setting when logged in as db though so I guess it must have rembered that????

I'd be very grateful of some help since this kind of latency is going to cause real problems.

Nigel Jones

update openquery remote table with join

Thu, 26 Mar 2009 14:54:29 Z

Hi All,

I have a problem with OPENQUERY update on remote table. I've googled for days, and can't solve the problem.
Here is the problem:
Local server MS SQL 2005.
Remote server: MySQL.
The linked server is communicating through MySQL ODBC 5.1

I created the following code for update (tried many other version too, this is the last one):

update openquery(Remoteserver,'select products_id, products_price, products_last_modified, products_stock from products1')
set
products_price=A.products_price,
products_stock=A.products_stock,
products_last_modified=getdate()
FROM
(select * from vi_products_update) AS A INNER JOIN
openquery(octopus, 'select products_id from products1')AS B
ON A.products_id=B.products_id

When I run the query the columns are updated with the same value, for each record. It looks the value is the first which match the criteria. Each (remote and local) table has as primary key products_id. In this example I used as result set for local a view, but I have the same result if I use a table.
the products_last_modified column is updated in order, and if I update for e.g. the products_price with a constant that is ok too. The problem should be somewhere with join, but I can' get it where.
THX for any help

Highest unique entry

Tue, 24 Nov 2009 14:57:50 Z

Short simple question, what SQL keyword should I be using in my where clause to get only a single unique NCR number but the highest version for each one? This needs to be done without a group by as there are many more fields than just the two shown and they're not aggregated (causes an error).

ID                Version
NCR09-0001	1
NCR09-0001	2
NCR09-0001	3
NCR09-0002	1
NCR09-0002	5
NCR09-0002	6
NCR09-0002	3
NCR09-0002	4
NCR09-0002	2
NCR09-0003	1
NCR09-0003	45
NCR09-0003	46
NCR09-0003	47
NCR09-0003	48
NCR09-0003	49
NCR09-0003	50

Querying inserted and deleted tables using triggers

Tue, 24 Nov 2009 15:15:35 Z

Hello,

In regards to triggers...

Where are the Inserted and deleted tables located and how do I query those tables?

Any help would be appreciated.

Dave

Dave SQL Developer

Designing

Tue, 24 Nov 2009 10:20:43 Z

one dropdownlist is of some city names and other one is of locations in that city.

so if user select any city then all the locations of that city should be come into 2nd dropdownlist (DropDownListLocation).

how to design the database?