Is there a function that works in both MS Access and SQLServer 2k5+ that will convert a string to a date? It seems CDate works in access and Convert works in SQLServer, but I'd like a function that works in both. Thanks! ...
I often find myself doing something along the following lines in sql server 2005 : Step1: create view view1 as select count(*) as delivery_count, clientid from deliveries group by clientid; Step2: create view view2 as select count(*) as action_count, clientid from routeactions group by clientid; Step3 : select * from view1 ...
Hi, my application is developed on classic asp, but also uses asp.net as I am migrating the application on .Net. Its using SQL server as database and hosted on Windows server 2003. Now the problem is that the application continue to work perfectly fine for a long time but then after some time SQL server gives timeout error and it could ...
I know this is subjective, but I'd like to know peoples opinions and hopefully some best practices that I can apply when designing sql server table structures. I personally feel that keying a table on a fixed (max) length varchar is a no-no, because it means having to also propogate the same fixed length across any other tables that use...
Is there an inherent cost to using inline-table-valued functions in SQL Server 2008 that is not incurred if the SQL is inlined directly? Our application makes very heavy use of inline-table-valued functions to reuse common queries, but recently, we've found that queries run much faster if we don't use them. Consider this: CREATE FUNC...
I have already created the program to restart a SQL database but it only works if the client has the rights. This is going to be done on a local network from a client computer when they can't get a person that has the password on the phone. Any thoughts I'm currently using the servicecontroller to start and stop database. When I don't ha...
I have a mirrored database and I need to make some changes to it. Mainly, adding a view and some stored procedures. Now I know that if you make schema changes you are supposed to remove mirroring and the mirror database, make your changes to the principal, then backup-restore the principal and restore mirroring. Is this the case for s...
I just starting to query XML within a SQL Server database. I am having trouble with the most basic query. Here is a simplified example. How do I return description? The SELECT statement below is what I am using, but it returns nothing. SELECT Incidents.IncidentXML.query ('data(/dsIncident/IncidentInformation/Description)') AS Descrip...
I have a typed xml document stored as text. So I use CONVERT the data type to xml by using a Common Table Expression in order to be able to use XML methods: WITH xoutput AS ( SELECT CONVERT(xml, t.requestpayload) 'requestpayload' FROM TABLE t WHERE t.methodid = 1) SELECT x.requestpayload.query('declare namespace s="http://blah...
Using SQL Server 2005: How can I read a file into a SPROC using T-SQL? So, imagine I have a CSV file like so: ID,OtherUselessData<br/> 1,asdf<br/> 2,asdf<br/> 3,asdf<br/> etc... I basically want to do this: Select * from mytable where id in (select id from txtFile) ...
I have the following T-SQL code (in SQL 2000) which functions correctly: INSERT INTO M2MDATA01.dbo.cspopup (fcpopkey, fcpoptext, fcpopval, fnorder, fcpopacces) SELECT CSP69.fcpopkey, C...
We currently have a failover sql cluster with two nodes. For a new large project which we have determined to be business critical, our development team is requesting a new 2 node failover sql cluster. Our server department has responded saying that they do not want to implement a cluster for us, and instead employee multiple virtual ...
I have a table (several actually) that contain a lot of columns (maybe 100+). What's best performance-wise when updating rows in the table, if only a few columns have been changed. To build the UPDATE statement dynamically only updating the changed columns. To build a parameterized UPDATE-statement containing all columns, including tho...
I have a scenario where I want to display a bunch of data on a web page. The data gets populated into database by some process (which I have no control over) only at (7:00 AM) a specific time in the morning or manually starting the process. (P.S The same data is also being used in some other place on the web page in a different form, ...
I am at a loss how to maintain it on a web farm. Could somebody tell me how to use sqlserver to maintain it? ...
So, recently a DBA is trying to tell us that we cannot use the syntax of SELECT X, Y, Z INTO #MyTable FROM YourTable To create temporary tables in our environment, because that syntax causes a lock on TempDB for the duration of the stored procedure executing. Now, I've found a number of things that detail how temporary tables work, s...
I have a stored procedure that runs against a local database, and fills a temp table. I'd then like to connect to a remote database and query it based on the values of the local temptables. Is that possible? Thanks. ...
How to set connection pool to 100 in sql server 2005 instance ...
I have two tables: Table 1 has Episode and Code, with Episode as distinct. Table 2 has Episode and Code, but Episode is not distinct (other fields in the table, not relevant to the task, make each row unique). I want to copy Table 1's Code across to Table 2 for each episode. The current code to do this is as follows: UPDATE Table2 SE...
We use a third-party product to manage our sports centre membership. We have several membership types (eg. junior, student, staff, community) and several membership statuses (eg. annual, active, inactive, suspended). Unfortunately the product only records a member's current membership type and status. I'd like to be able to track the way...