sql-server

Sql Server Object Organisation

OK I'm not sure if this is valid, however I have a bug bear with SQL Server, and that is that I cannot organise objects in to a group of objects. Imagine I'm working on a new section of work in a large database and I perhaps have 15 objects that I will be regularly using. What I want to do is sort of "Favourite" them in to a folder so t...

Sharepoint and SQL server Endpoint

I created a LinkedServer on MS SQL Server 2005 pointing to my Active Directory. Nothing too fancy. Simple LinkedServer with ReadOnlyAdmin Account assigned to CONNECTAS Property. Then I created some storedprocedures to retreive some data from the LinkedServer. Again nothing too fancy. Just a few simple LDAP Queries. Then I created a SQL...

How can you name the Dataset's Tables you return in a stored proc ?

I've got the following stored procedure Create procedure psfoo () AS select * from tbA select * from tbB I'm then accessing the data this way : Sql Command mySqlCommand = new SqlCommand("psfoo" , DbConnection) DataSet ds = new DataSet(); mySqlCommand.CommandType = CommandType.StoredProcedure; SqlDataAdapter mySqlD...

For autoincrement fields: MAX(ID) vs TOP 1 ID ORDER BY ID DESC

I want to find the highest AutoIncremented value from a field. (its not being fetched after an insert where I can use @@SCOPE_IDENTITY etc) Which of these two queries would run faster or gives better performance. Id is the primary key and autoincrement field for Table1. And this is for Sql Server 2005. SELECT MAX(Id) FROM Table1 SELEC...

Hit Highlighting with SQl Servr 2008 FTS

This question was here already but there was no answer, so trying one more time - how to do hit highlighting of results with SQL 2008 FTS? So far I found SQLHighlighter but it is commercial product. I also tried solution described in this book http://apress.com/book/view/9781430215943 but performance was extremely poor. As last resort I...

Pivoting tables

Hi all, I have a table like this: serialnumber partnb id actual nominal 1 1 AGR 15,2176803 15,2 1 1 APR 5,8060656 5,8 1 1 DCI 61,9512259 62 1 43 AGR 15,4178727 15,4 1 43 APR 7,235779 7,2 1 43 DCI 52,0080535 52 2 2 AGR 15,2097009 15,2 2 2 APR 5,8009968 5,8 2 2 DCI 61,9582795 62 2 ...

Sharepoint document library as a destination in SSIS

I create a CSV-file from SSIS and I want to "publish it" in a specific folder in our Sharepoint document library. What is the preferred way to this? I have looked at the example with the sharepoint list destination SharePoint List Source and Destination which is quite good but not exactly what I'm out for. I just want to upload a file ...

Query about designing relationships in Sql Server's management studio interface

Imagine I have two tables: Manager, Player.This is for a football team, where several players can play for one manager only and a manager manages several (11) players. When designing the relationship in Sql Server, at the time the popup window comes up with the properties for the relationship, does it matter which side the tables are? ...

get new SQL record ID

How can I get back the autogenerated ID for a new record I just inserted? (Using ASP classic and MSSQL 2005) ...

Convert varchar in format mon-yy to datetime in SQL server

Is there a simple way to convert a date stored in a varchar in the format mon-yy (e.g. "Feb-09") to a datetime which will convert any invalid values to null. I'm currently using string manipulation combined with a case statement but it's rather cumbersome. ...

SqlFunction fails to open context connection despite DataAccessKind.Read present

I've got a SqlServer project with a very simple test for a Table-Valued-Function:- [SqlFunction(TableDefinition = "forename nvarchar(50)", FillRowMethodName = "TestFillRow", DataAccess = DataAccessKind.Read)] public static IEnumerable TestConn(int ID) { using (SqlConnection con = new SqlConnection("context connection=true")) { //con...

Cannot connect to SQL Server remotely or locally after reboot

After some network maintenance last weekend, we had some trouble with our development server which led to us restart it. Some updates were installed during the restart, which may or may not have been a factor. Since then, we have been unable to connect to SQL Server 2005, even locally via Management Studio. Depending on the protocol used...

Readonly Database Query?

I have read quite a bit about the ReadOnly databases. Therefore I was wondering if there would be any benefit in having a readonly database, used heavily to read and query data, and a separate write database used for all the CRUD work when the write database doesn't utilize locking. ...

Convert SELECT to an update statement

SELECT t1.status, t3.guid, t3.objectID FROM Table1 t1, Table2 t2, Table3 t3 WHERE t2.ID = t3.ID AND t1.ID = t2.ID AND t3.Guid IN ('', '', '') How can I convert this to an update statement where I set the t1.status = 1? ...

Is it possible to use the SELECT INTO clause with UNION [ALL]?

In SQL Server this inserts 100 records, from the Customers table into tmpFerdeen :- SELECT top(100)* INTO tmpFerdeen FROM Customers Is it possible to do a SELECT INTO across a UNION ALL SELECT :- SELECT top(100)* FROM Customers UNION All SELECT top(100)* FROM CustomerEurope UNION All SELECT top(100)* FROM CustomerAsia UNION All S...

How to format XML in SQL Server

I am extracting some data from SQL Server. One of the columns is a ntext column that has a blob of XML there. As I extract it, it looks really ugly. Is there any way to format the XML inside a SQL Server 2005 stored proc? ...

Update a table from a temp table

I have a table that stores formulary drug information and needs to be updated daily from a central formulary. The temp table is identical to the drug table. The temp table data could be identical (and on most days will be) to the main table or it could have updated rows or new rows. I have a stored procedure to update the main table, b...

SQL Express remote access

Is there any way to install SQL Express 2005 silently while configuring to allow remote access, or must the configuration be done with the SQL Server Management Studio UI? ...

How do you count the related rows within a query.

I am trying to make a query that pulls out all Tickets for a particular company. Within that table will be a column named [Repeat] What I need the query to do is check to see if there are any other rows that have a matching Circuit_ID within the last 30days of that ticket. "SELECT [MAIN_TICKET_ID], [CompID], [ActMTTR], [ActOTR], [DtCr]...

Trigger to update data on another sql server

Hello, I'm using two SQL Server, one is SQL Server 2000 and the other one is SQL Server 2005. When the table1 in SQL Server 2000 gets updated/inserted/deleted, I have to update another table in SQL Server 2005. Is it possible to achieve that in a trigger? If not, what would be the possible options? Thank you, have a nice day! ...