Tag: sql-server-2005

How do you remotely execute a table valued function in Sql-Server 2000/5?

I have a stored procedure (sql2005) that needs to call a table valued function on a remote server (sql2000). My query is as follows: select * from mytable mt cross apply opendatasource(‘sqloledb’,’Data Source=remoteserver;UID=user;Password=pass’).mydatabase.dbo.mytvf (cast(param1 as numeric(20,0)), @param2, mt.param3) I’m getting an incorrect syntax error near ‘cast’. Is it possible to execute a tvf with this […]

Using CASE statements as a precursor for COUNT function in single (large) SQL query, syntax

I’m having trouble incorporating a bit of logic into a large SQL query. I’m using SQL Server Reporting Services 2005 with the Report Designer, and it only gives you one area to define a single SQL query to populate the report with. Hopefully someone can tell me what’s wrong with my syntax so I can […]

Can I use SQL Server 2008 management studio with SQL Server 2005?

I have management studio 2005 and 2008, can the 2008 version be setup to connect to 2005? I understand there are new features in 2008 that I wouldn’t be able to use, but I mainly use the software to graphical create new tables and assign relationships.

In SQL Server 2005 emulating autonomous transaction

I have needs to keep some of log data in different tables even my transaction is rolled back. I already learned that in SQL Server it is impossible do something like this begin tran t1 insert … insert … select … begin tran t2 insert into log commit tran t2 rollback tran t1 select * […]

Copy all data from one SQLServer database to another on same machine

I want to copy all data from one database to another which has the same structure. The databases reside on the same machine & on same sql server. I have googled a bit & have found solutions like this INSERT states (statecode, statename) SELECT statecode, statename FROM server1.database1.dbo.states But the problem is they are copying […]

open external files or applications from sql command using sql server

i want to open notepad, word or excel files from sql server using some query. is this possible? or is it possbile using C#.NET winform application. ? also, i want sql to use dll files present it my hard drive. how do i do both these tasks? please help me out with the code as […]

How to make dynamic column

I have some data as under Declare @t table (Id int identity,CommaSeperatedValue varchar(100)) Insert Into @t Select ‘Somalia,Vietnam’ Union All Select ‘apple,banana,guava,India,Australia’ There is no limit in the CommaSeperated value. The desired output for the sample provided will be Id Col1 Col2 Col3 Col4 Col5 1 Somalia Vietnam Null Null Null 2 apple banana guava […]

DELETE EXCEPT TOP 1

Is there any way to delete all the rows in a table except one (random) row, without specifying any column names in the DELETE statement? I’m trying to do something like this: CREATE TABLE [dbo].[DeleteExceptTop1]([Id] INT) INSERT [dbo].[DeleteExceptTop1] SELECT 1 INSERT [dbo].[DeleteExceptTop1] SELECT 2 INSERT [dbo].[DeleteExceptTop1] SELECT 3 SELECT * FROM [dbo].[DeleteExceptTop1] DELETE FROM [dbo].[DeleteExceptTop1] […]

Filter for NULL in list

why does filter for NULL in subqueries does not work? I hoped to get the correct result by add NULL to the list of allowed values, for example: SELECT ERP_ServiceProcess.fiStatusNew, RMA.IdRMA FROM ERP_ServiceProcess RIGHT OUTER JOIN RMA ON ERP_ServiceProcess.fiRMA = RMA.IdRMA WHERE (ERP_ServiceProcess.fiStatusNew IN (NULL, 1, 7, 8)) order by ERP_ServiceProcess.fiStatusNew This gives the incorrect […]

sql server check constraints

Am I missing something? When I get SSMS to script a check constraint for create it produces two statements, ALTER TABLE mytable WITH CHECK ADD CONSTRAINT [CK_myconstraint] CHECK (([Time]>=’2011-05-15 20:33:00.000′ AND [Time]<=’2011-05-17 20:31:00.000′)) GO ALTER TABLE mytable CHECK CONSTRAINT [CK_myconstraint] My understanding is that the “with check” should check the constraint on existing data when […]

MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.