Hi, I have 3 tables, each table will show records of users posting messages. I have managed to sum the number of times each user has posted a message on each table and now I want to sum these 3 individual values together for each user. Here is what I have managed so far: Table USERMESSAGE: SELECT U.SenderID, COUNT(U.SenderID) AS U_NUM...
how to update database structure using script? I have old db in one server and new db in another server, I have to update the old db structure with new one. Can we do using script? Please any one help me Thank you. ...
I want to write an SQL Server 2005 stored procedure which will select and return the user records from the user table for some userids which are passed to the stored procedure as parameter. How to do this ? I can pass the user ids as a string separated by comma. So that i can use the select * from users where userid in (userids) ...
Example table: CREATE TABLE Log ( logID int identity logDate datetime logText varchar(42) ) logID is already indexed because it is a primary key, but if you were to query this table you would likely want to use logDate as a constraint. However, both logID and logDate are going to be in the same order because logDate w...
I have a really strange scenerio I have a smartclient application and an asp.net webapplication which accesses a set of same WCF services.I have a web.config file in my servicehost project which is like this.Database is on client end and we use VPN to connect to it. <add name="ConnectionProperty" connectionString="server=192.168.0.12\...
Hello, I have a database with merge replication set up on a SQL 2005 server with 1 subscriber. I am having some identity range issues an I started to look at the MSmerge_identity_range table. I have one subscriber but 2 rows for each. For Example(I shortend the guids but for arguments sake they are the same for each row): subid ar...
I am working on a SQL Job which involves processing around 75000 records. Now, the job works fine for 10000/20000 records with speed of around 500/min. After around 20000 records, execution just dies. It loads around 3000 records every 30 mins and stays at similar speed. I asked a similar question yesterday and got few good suggestions...
Application connected to MS SQL Server will create views where a single row result is an analysis including aggregations of 1-10k records. The applicable criteria across the resulting view will have dozens to tens of thousands of results. The view+criteria will then be ordered by some column (user specified) in the view which are most li...
I have 2 tables: "orders" and "visits". in the orders table i'm saving some details about the order including "userIP" and "orderDate". in the visits table i'm saving details everytime a user visiting my web page including "userIP" and "visitDate". i'm using ASP.NET and SQL SERVER 2005. i want to create a statistic table whitin i save t...
I have got a bit of 'brain fade' going on this afternoon, so if anyone can help with this mssql query it would be fantastic. I have a table called 'seasons' with three columns (there are more but not relevant to the example): seasonId, date, tariffId SeasonId is a unique key. A date can only have one tariffid, but a tariffId can have m...
I want to know whether one SP is referenced anywhere. Currently I am checking using SP_DEPENDS. Is there any other way to check this...? ...
i have the a query which is union of two querries, the resulting query is bringing duplicate records, i dont want duplicate records, i tried putting DISTINCT but getting the same result, can anybody help me rectifying this query. i also want to know whether this query is safe from sql injection...i'll be pasting my query below: ALTER PR...
I am working with ASP.NET and SQL Server 2005. I know how to create a stored proceudre but i do not know what to place in a stored procedure when i need to make use of a QueryString. My SQL Statement in CODE = "SELECT * FROM TableName WHERE ID = '" + Request.QueryString("ID") + "'" Now what must i place in my stored procedure to get...
I'm tasked with doing a SQL Server 2000 to 2005 migration. I will be doing a side-by-side migration. After restoring from a backup I plan to do the following: ALTER DATABASE <database_name> SET COMPATIBILITY_LEVEL = 90; DBCC CHECKDB(<database_name>) WITH NO_INFOMSGS DBCC UPDATEUSAGE(<database_name>) WITH NO_INFOMSGS exec sp_updatest...
I have a complex query that I need to use in a subsequent query (actually update statement). I have tried both using a CTE and a temp table. The performance using the CTE is horrible vs the temp table approach. Its something like 15 seconds vs milliseconds. To simplify the test instead of joining the CTE/Temp table in the subsequent q...
I have two different queries: SELECT PB_BANK_CODE, PB_BANK_NAME FROM GLAS_PDC_BANKS WHERE PB_COMP_CODE='1' AND PB_BANK_CODE='025' AND PB_BANK_CODE IN ( SELECT DISTINCT PC_BANK_FROM FROM GLAS_PDC_CHEQUES WHERE PC_BANK_FROM ='025' AND ISNULL(PC_DISCD,'X') != 'C' AND PC_DUE_DATETIME BETWEEN '05/05/2008' AN...
We had a consultant onsite who setup the production DB to mirror to a DR site SQL instance. Asynchronous mirroring set to mirror every minute. It has been synching to these DR site so far. I am picking up these tasks from the guy but ofcourse no knowledge transfer. So, i would like to perform certain tasks and have questions about them; ...
I am having a problem with my SQL Server 2005 where stored procs, specifically ones that run Bulk Inserts, are sometimes taking a really long time to execute. This stored proc, let's call it DatabaseUpdate, shold take about 2 secs to complete. However, between 20-40% of the time it takes much more than that and it is very common that i...
Given the following sample of XML and the select statement that shreds the xml into a relation, what I need is the second column of the select to be the ordinal of the category (ie 1 for the directions and 2 for the colours in this case). Note: The literal value 'rank()' in the select is left a placeholder. I was poking around with usin...
I have a query that returns 10000's of records that are used as plot points on a map. In an effort to reduce load, and increase app speed we're trying to implement what basically amounts to Level of Detail logic. Basically, when zoomed out, display 50% of the points. When zoomed in, display 100% of the points. This is ultimately what I ...