sql-server-2005

Inserting records to an interface

I have set up a stored procedure for inserting a single record into a table. The reason for this is to create a general interface for people to use so I can change the table structure that it points to without anyone noticing or having to change code on their end in the future. The problem occurs when the user has to insert many records...

In SQL Server 2005, how can I write a query to list all login, their server role, correspond user in all db, db role?

Hi guys, I'm not clear about the security-related catalog views in SQL Server 2005 or 2008. I want to list all logins, their server roles, their correspond users in all database, all database roles in one query. How can I write the query? I know there are some catalog views to use, but I'm not familiar with their relation. These catalo...

INFORMATION_SCHEMA.COLUMNS not updated

I notice this weird behavior where I change the column type from ntext to nvarchar(max) and in the INFORMATION_SCHEMA.COLUMNS the data_type remains ntext ... Is there something i must execute for the metadata to be refreshed? I'm on sql server 2005 ...

SQL server select distinct rows using most recent value only

I have a table that has the following columns Id ForeignKeyId AttributeName AttributeValue Created Some of the data may look like this: 1, 1, 'EmailPreference', 'Text', 1/1/2010 2, 1, 'EmailPreference', 'Html', 1/3/2010 3, 1, 'EmailPreference', 'Text', 1/10/2010 4, 2, 'EmailPreference', 'Text', 1/2/2010 5, 2, 'EmailPreference', 'Htm...

IF there is 24 hours between dates CASE statment

I am trying to create a case statment saying if 1 day or less has passed between my 2 date parameters then do this otherwise do this..... ...

Integer division in sql server

In Microsoft SQL Server 2005, why do the following commands produce integer results? SELECT cast(151/6 AS DECIMAL(9,2)) SELECT 151/6 ...

Arithmetic overflow error converting expression to data type int

I'm having an issue on the query below. The issue is: Msg 8115, Level 16, State 2, Line 1 Arithmetic overflow error converting expression to data type int The issue is located in this line of the code below. INNER JOIN Test t ON a.AccountNO <> t.AccountNo Any ideas? WITH TEST AS ( SELECT DISTINCT AccountNo, SUBSTRING(APa...

What is Difference between truncate table A and Delete from A

Possible Duplicates: SQL Server - bulk delete (truncate vs delete) Whats the difference between TRUNCATE and DELETE in SQL What is Difference between truncate table A and Delete from A? which one better to use for deleting the records from table. ...

Get's database's name?

How can I get’s all database’s name that created in sql server in local system. thanks.. ...

How do I connect to the database with a published web application?

I published my website using IIS and now when I browse to my website it is working properly but not connecting to the database so it always shows a login failure. I've checked my connection string in web.config file and it is right. What could be causing this? ...

Convert sql subquery to list or varchar

Hi, I want to know if we can convert a subquery result to comma separated list in varchar datatype. For eg. If I have a product table. And I have product image table with foreign key of product. now I want to list all product with select query that should have a column with list of productImage table's pk list for each product. I'm us...

Grant SELECT, UPDATE, INSERT, DELETE to all tables except 1 (or more) in SQL Server 2005

The user I have should have access to all tables in a database - SELECT, INSERT, UPDATE, DELETE and EXECUTE (ASP code to blame :-P) except for 1 table e.g. users. When granting db_datareader and db_datawriter this gives them full access to everything and removing the DELETE permission on the users table will not work. There are over 60...

What is the best solution for POS application?

I'm current on POS project. User require this application can work both online and offline which mean they need local database. I decide to use SQL Server replication between each shop and head office. Each shop need to install SQL Server Express and head office already has SQL Server Enterprise Edition. Replication will run every 30 min...

What is the best way to save the articles?

I got a project from my client to create a site similar to STACKOVERFLOW for Printing media. So I want to know where should I store the article heading, body, images etc.? In databaser? in XML? I want to make them searchable and can easily be picked by search engine. Waiting for your replies. Thanks Ali Adravi ...

Trying to understand a SQL where clause

I am hoping someone can help interpret the last code line (@Active=1....) for this SQL clause: SELECT DISTINCT LOC_ID ,LOC_CODE ,ADDR_LINE_1 ,ADDR_LINE_2 ,ADDR_LINE_3 ,CITY ,STATE ,COUNTRY ,POSTAL_CODE ,COMPANY ,OPERATION_TYPE ,PROCESS ,ADDR_LINE_1 + ',' +ADDR_LINE_2+ ',' + CITY + '-' ...

generate where clause dynamically with dynamic column names

Hi, I have data in the SQL 2005 table as below. Table Name: FilterData CategoryID ColumnID AnswerCode -------------------------------- 0349 Q15 02 0349 Q15 03 0349 Q16 04 Table Name: TransactionData CategoryID Q15 --------------------------------- 0349 01 0349 02 0349 03 0349...

Linked Server Dangers

Is there any danger is creating a linked server? I want to create linked server to a DB2 database from SQL Server 2005. Will the DB2 database be effected by this at all (performance wise) or is this all handled on the SQL Server side? ...

SQL can you use IF statement in a UDF and how do you do that?

I am trying to create a UDF that does 2 different things depending on the time. Below is my code. I am wondering if you can use the IF statement in a UDF because I am etting 4 errors, incorrect syntax near Begin and Returns and also a Return Statement with return value cannot be used in this context....Any suggestions? SET ANSI_NULLS ON...

Need help creating complex T-SQL SELECT statement.

I am trying to make SELECT statement for following situation and need help to make this SELECT statement. It's SQL Server 2005. When the select statement is run, it should return rows which have SentDate as NULL assuming that there are no duplicate PersonID in table. It will return result set with Status as 'Initial Record' (as the same...

Will using the TABLOCK and HOLDLOCK hints in SQL Server 2005 COMPLETELY prevent inserts until the end of the transaction?

I need to retrieve the identity field generated by SQL Server 2005. Usually I would just use SCOPE_IDENTITY or add an OUTPUT CLAUSE to the insert, however neither of these method help in this situation: as there is an INSTEAD OF trigger attached to the table. Next I considered @@IDENTITY, but as there is also another trigger attached to ...