I need to take all the values in an excel file to a temporary/physical table in SQL Server 2005. I don't need the Import export method. I tried the following linked server method: SELECT * INTO db1.dbo.table1 FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Driver={Microsoft Excel Driver (*.xls)};DBQ=c:\renju.xls', 'SELECT * FROM...
Is there a 'nice' way to check whether an network host is up making use of 'palin' SQL? I thought of some MS-SQL specific command or maybe some getting the return value from a ping script on the server itself. Does anyone has an idea on this? ...
I want to alter a field from a table which has about 4 million records. I ensured that all of this fields values are NOT NULL and want to ALTER this field to NOT NULL ALTER TABLE dbo.MyTable ALTER COLUMN myColumn int NOT NULL ... seems to take forever to do this update. Any ways to speed it up or am I stuck just doing it overnight dur...
I'm leaving out all the cursor setup and the SELECT from the temp table for brevity. Basically, this code computes a running balance for all transactions per transaction. WHILE @@fetch_status = 0 BEGIN set @balance = @balance+@amount insert into @tblArTran values ( --from artran table @artranid, @trandate, @typ...
The database is MS SQL Server Express 2005. The database is in simple mode. I am trying to restore an existing database to a new server. I copied the MDF and LDF files to a new server. I attached the MDF and verified that the correct LDF was associated with it. After attaching the database, I compared the data from both databases ...
Hi, I am using MsSQL server 2008, I am using CONTAINSTABLE with the search term "can", but the search query is not returning anything thought there are no stoplists attached to that catalog. SELECT DISTINCT c.ID FROM City c INNER JOIN CONTAINSTABLE(City, (Name), '"can"') AS city_tbl1 ON city_tbl1.[KEY] = c.ID To make sure that this ...
I want to present a schema to my BA team. Select TABLE_NAME, col.DATA_TYPE from INFORMATION_SCHEMA.COLUMNS col order by col.TABLE_CATALOG, TABLE_NAME, TABLE_SCHEMA, col.ORDINAL_POSITION I want to present 3 rows of sample data as well but I wanted to pivot it out so they get an output that has the following 3 columns: Table_Name Dat...
If a sql server's server collation is case sensitive and a database is case-insensitive, will queries be case-sensitive or not? I thought that it was supposed to be based on the database rather than the server collation, but having done a short test that seems not to be the case. Anyone know for sure? ...
My SQL Server 2005 doesn't restore a backup because of active connections. How to force it? ...
Sort of a 101 question: with multiple files in a user defined filegroup, what is SQL Servers behavior when inserting rows? Round robin? Write until full and move on to the next? I have found some interesting posts regarding tempDb on this subject that suggest round robin, but nothing conclusive. ...
In my stored procedure, I send emails with sp_send_email. My stored procedure will be run in different environments; some will have emailing enabled, some won't. If I run sp_send_email without it being enabled, I (quite rightly) get this error message SQL Server blocked access to procedure 'dbo.sp_send_dbmail' of component 'Database...
Background: I am generating pieces of a much larger XML document (HL7 CDA documents) using SQL FOR XML queries. Following convention, we need to include section comments before this XML node so that when the nodes are reassembled into the larger document, they are easier to read. Here is a sample of the expected output: <!-- ********...
The title probably doesn't make much sense, so I'll try to be descriptive here in the subject. Consider 2 tables in MSSQL2005: Cases table: id int, caseNo string Events table: id int, caseID int, eventDate date/time I need a select statement for a view which will return single rows of: cases.caseNo, events.eventDate (date part on...
Hi, I'm trying to specify backup, the scripting to a new job as described in http://support.microsoft.com/default.aspx/kb/930615 However, the option Script Action to Job is not available to me (disabled). Could it be insufficient rights for the logged on user? Which rights must I grant the user? Thanks, Anders, Denmark ...
Is there a faster way to select the distinct count of users from a table? Perhaps using row_number, partitioning, or cross apply? I just can't think of it right now. Example: Table UsageLog UserId Date StoreNumber Alice 200901 342 Alice 200902 333 Alice 200902 112 Bob 200901 112 Bob 200...
In a data warehouse, are there disadvantages to creating clustered indexes on fact tables? (most of the time, it will be on the datetime column) Would you answer yes or no "by default..."? If I shouldn't create clustered indexes by default, then why? (I know the pros of clustered indexes, but what are some cons?) References http://...
I've been trying to figure out why SQL Server has db_owner schema and db_owner role? This is very confusing. I've been searching for answers and so far this is how my understanding goes: All tables and objects (such as constraints etc) belong to a schema. DBO being the default schema. A user may be given permission to edit each obje...
I've got a number of ASP.Net websites (.Net v3.5) running on a server with a SQL 2000 database backend. For several months, I've been receiving seemingly random InvalidOperationExceptions with the message "Internal connection fatal error". Sometimes there's a few days in between, while other times there are multiple errors per day. The ...
I have SQL 2k8 server and reporting services installed on a server inside a domain. I also have a webserver that is outside the domain. I'm trying to run a reporting services report from the webserver (either through a URL or the report viewer component). I have managed to authenticate (as detailed in this post ), and now the report ki...
Jimmy Nilsson discusses his COMB guid concept here. This concept is popular in NHibernate, among other circles, for its supposed performance value over standard GUIDs which are typically far more random. However, in testing, this does not appear to be the case. Am I missing something? Test case: I have a table called temp (not a temp ...