SQL Server Read Only Permissions
Hi All, I need to GRANT Read only Permission on few Databases.How Can I acheive this in SQL Server 2005 ? Thanks In Advance Anoop ...
sql-server-2005
Generating DAO with iBATIS problem
Hi, I have created a table in MSSQL having following structure - **Table Name - TestTable** id (int) (Primary key) name (varchar) owner (varchar) I have given Identity specification for column 'id' with auto increment with 1 for each new row. When I created iBatis artifacts for TestTable, the insert function is getting generated, in...
Is there a more ideal way to query data from another database using SQL Server 2005?
I have a stored procedure which gets data from another database on the same server. I will not be hard-coding which database to use, instead this will be configurable. Is this possible without dynamic sql? The best solution I could come up with so far is to first run a stored procedure which uses dynamic sql to generate a bunch of view...
Storing geo data
Hi I'm using ms sql 2005 to store some latlng data. It is currently being stored in a nvarchar column in the following form - "35.6949965,139.7555035" My questions are. Is this the best way to store this kind of data? If I do store the latlng in this way is it going to come and bite me later, or will it mean just that I will have to do a...
How to do automatic data archiving in SQL Server?
Hi, I have table for which every day I want to do automatic archiving. So to be clear every day I want to take information generated during that day and move it into another partition (of same table) not in another archive table. That's because I want old data to be accessible with same query as new ones. I'm using SQL Server 2005, I'v...
Splitting variable length delimited string across multiple rows (SQL)
I have a table that contains in one column a variable length delimited string for example: 20,0, 5,,^24,0, 0,,^26,0, 0,,^ 281,0, 0,,^34,0, 2,,^48,0, 2,,^44,0, 2,,^20,0, 10,,^ 20,5, 5,,^379,1, 1,,^26,1, 2,,^32,0, 1,,^71,0, 2,,^ What i need to do is split this string so that each number after the ^ character is returned on a new ...
SQL Server Computed Columns - split on line break
I have a column with name and address data in a single column: Jo Bloggs Address Line 1 Address Line 2 PostCode Is it possible to then create two computed columns based on this, and if so how? e.g. RecipientName Jo Bloggs Address Address Line 1 Address Line 2 PostCode I'm using SQL Server 2005 ...
Free substitution for DBDefence
Hello. I'm looking for a tool to hide my database stuff (I'm working with Sql-Server 2005) (I want that even DBA couldn't access DB information). I found DBDefence utility. But it requires money. Is there a tool with similar functionality for free? Thank you for your help! ...
SQL LIKE Command trouble searching for '
Hi I am facing a problem with the like command in SQL, I want to search for special characters within a column . The special characters are a single quotation mark ' and { and }.. I have tried placing these special characters under [] but still it doesn't work for ' I have also used the except option but that was also of no help.. Wa...
Search by date SQL server 2005
Hello everyone i want search data from invoices and client by today date I'm using GETDATE() for example two tables 1 Client - ID int - Name Varcher 2 Invoice - ID int - ClientID int - date Datetime - Total money query Select * from client c inner join invoice i on c.id = i.ClientID where i.date = g...
How to query for rows that have highest column value among rows that have same value for one of the columns
I have UserScores Table with data like this: Id userId Score 1 1 10 2 2 5 3 1 5 I would like to have a query or SQL block that can give me the following output Id userId Score 3 1 5 2 2 5 That is, I would like to pick rows that are unique by 'user id' that belong...
SQL Server: how to determine what will break when downgrading a database?
We're building an application for a client with the assumption that they'd be upgrading to a minimum of SQL Server 2005 from SQL Server 2000. We're finished our application, built on 2005, and are ready to integrate. Turns out that they're not going to upgrade their DB server. So, now we're stuck with trying to sort out what will break...
Is a Primary Key necessary in SQL Server?
This may be a pretty naive and stupid question, but I'm going to ask it anyway I have a table with several fields, none of which are unique, and a primary key, which obviously is. This table is accessed via the non-unique fields regularly, but no user SP or process access data via the primary key. Is the primary key necessary then? Is...
SQL - "IF" in Where Clause
I'm trying to write a stored procedure that will have 6 bit value flags as parameters and a couple of other values. The pseudo sql I want to write is something like: SELECT * FROM theTable WHERE IF @flagA = 1 THEN theTable.A = 1 IF @flagB = 1 THEN theTable.B = 1 IF @flagC = 1 THEN theTable.CValue = @cValue ...
Column creation and use
Hi, I am fairly new to SQL, so not sure if this is just a SQL compiler thing or not. When I have used SQL before it's been through JAVA or PHP, never just straight SQL. I am using SQL Server 2005 and attempting to add a column to my table, and then populate it. Here is what I have now: ALTER TABLE House DROP COLUMN CustomerType ALTE...
Using "WITH OVER" statement - How to start new sequence number when another group of records are started?
Using WITH OVER or by using other method, how to start new sequence number for another group of records? It is SQL Server 2005. E.g. how to get following ouput (I am talking about RowNum column in expected output)? Table: id name 100 A 200 B 300 C 200 B 200 B Expected ouput: RowNum id 1 100 1 200 2 ...
search between date with query have calculation and counter
Hello everyone i have big query to calculation and counter by clinic ID SELECT nc.ID AS ClinicID, nc.Name AS ClinicName, SUM(cr.CountRecept * cs.Price) AS TotalPriceService, SUM(cr.TotalPaid) AS TotalPaid, SUM(cs.Price * cr.Company_Percentage / 100) AS TotalInsurance, SUM(cr.CountRecept) AS TotalCountRecept FROM ...
How do I add a second subscriber to SQL 2005 Transactional Replication publication
I have setup SQL 2005 Transactional Replication, with one master database acting as the publisher, and one subscriber. It's running fine. Now, I want to add a second subscriber to this publication. I created the subscription on the second server, and created the snapshot ok, but when it starts to syncronize I get the error "Cannot d...
SQL: Having trouble with query that gets percentages using aggregate functions
Hello all, I'm not an expert in SQL by any means, and am having a hard time getting the data I need from a query. I'm working with a single table, Journal_Entry, that has a number of columns. One column is Status_ID, which is a foreign key to a Status table with three values "Green", "Yellow", and "Red". Also, a journal entry is logged ...
SQL Server 2005: Basic Insert / Record Logic Help Needed
Hi. I am designing a social networking site that has a "wall" feature like the others out there today. The database has an alerts table that stores some user action worthy of sharing with his friends. For example, when a user updates his status, all of his friends are notified. The below table shows two status updates from two unique use...