link SQL 2005 server

Any body tryed to link SQL2000 to SQL2005 or other way arround.

add sql2k to sql2005, and vice versa, as linked server is a supported scenario.

for example, from a connection to your sql 2005, run the following commands to add sql2k as a linked server:

sp_addlinkedserver 'sql2k'
sp_setnetname 'sql2k', <machine name>\<instance name>

where 'sql2k' is the linked server name of your choice, <machine name> is the hostname of the machine where your sql 2000 is installed, and <instance name> is the name of your sql 2000 instance. If it is a default instance, then simply
sp_setnetname 'sql2k', <machine name>sql

link SQL 2005 server

Any body tryed to link SQL2000 to SQL2005 or other way arround.

add sql2k to sql2005, and vice versa, as linked server is a supported scenario.

for example, from a connection to your sql 2005, run the following commands to add sql2k as a linked server:

sp_addlinkedserver 'sql2k'
sp_setnetname 'sql2k', <machine name>\<instance name>

where 'sql2k' is the linked server name of your choice, <machine name> is the hostname of the machine where your sql 2000 is installed, and <instance name> is the name of your sql 2000 instance. If it is a default instance, then simply
sp_setnetname 'sql2k', <machine name>

Line between page columns?

Hi all,

My report's body section is split into 2 columns. I need to be able to add a vertical line to the dividing point of these columns so that it is visually more recognisable. How can I do this?

Thank you,

Stephen.

Any ideas? I am having real trouble with this.

Stephen.

Line 1: Incorrect syntax near

Hello All,

Me saying " has any body come across such error would be
underestimating".

Well I am getting a very peculiar and unique error "Line 1: Incorrect
syntax near 'Actions'."

Explaining you the scene is the following Stored Proc.

This stored proc is execute from a VB code in the .net application as
like: -
{Try
Connection.Init_Variables()
cn.ConnectionString = Connection.gstrConnection
ResDb.ConnectionString = Connection.gresConnection
cn.Open()
With sqlCmd
..Connection = cn
..CommandText = "DSP_Get_Required"
.CommandType = CommandType.StoredProcedure

..Parameters.Add("@.ActionId", SqlDbType.Int, 9).Value = ActionID
..Parameters("@.ActionId").Direction = ParameterDirection.InputOutput

..Parameters.Add("@.PersonID", SqlDbType.Int, 9).Value = PersonID
..Parameters("@.PersonID").Direction = ParameterDirection.InputOutput

..Parameters.Add("@.ReturnMessage", SqlDbType.VarChar, 1000).Value =
ReturnMessage.ToString
..Parameters("@.ReturnMessage").Direction =
ParameterDirection.InputOutput

..Parameters.Add("@.Exists", SqlDbType.Bit, 1).Value = Exists
..Parameters("@.Exists").Direction = ParameterDirection.InputOutput

..Parameters.Add("@.Days", SqlDbType.Int, 9).Value = 0
..Parameters("@.Days").Direction = ParameterDirection.InputOutput

..Parameters.Add("@.StartDate", SqlDbType.DateTime).Value = Now()
..Parameters("@.StartDate").Direction = ParameterDirection.InputOutput

..Parameters.Add("@.EndDate", SqlDbType.DateTime).Value = Now()
..Parameters("@.EndDate").Direction = ParameterDirection.InputOutput

..Parameters.Add("@.OutCome", SqlDbType.VarChar, 20).Value = "Stop"
..Parameters("@.OutCome").Direction = ParameterDirection.InputOutput

..Parameters.Add("@.Evaluate", SqlDbType.Int, 9).Value = 0
..Parameters("@.Evaluate").Direction = ParameterDirection.InputOutput

..Parameters.Add("@.DbName", SqlDbType.VarChar, 100).Value =
ResDb.Database.ToString
..Parameters("@.DbName").Direction = ParameterDirection.InputOutput

..ExecuteReader(CommandBehavior.Default)
}

On Execution I get the subjected Error "Line 1: Incorrect syntax near
'Actions'."
Any Ideas from your all experience to get away from this error will be
helpful. Look forward to read somebody soon.

Stored Proc:-
{SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE ID =
OBJECT_ID(N'dbo.DSP_Get_Required_ActionS') AND OBJECTPROPERTY(id,
N'ISPROCEDURE') = 1)
DROP PROCEDURE dbo.DSP_Get_Required_ActionS
GO

CREATE PROCEDURE DSP_Get_Required_ActionS
@.ActionID INT OUTPUT,
@.PersonID INT OUTPUT,
@.ReturnMessage Varchar(1000) OUTPUT,
@.Exists BIT OUTPUT,
@.Days INT OUTPUT,
@.StartDate DATETIME OUTPUT,
@.EndDate DATETIME OUTPUT,
@.OutCome VARCHAR(20) OUTPUT,
@.Evaluate INT OUTPUT,
@.DbName VARCHAR(100) OUTPUT
AS

SET NOCOUNT ON

--DECLARE @.PopulateSQL AS NVarchar(4000)

DECLARE @.Rule_ID AS NUMERIC(9)
DECLARE @.Curr_ActionSubType AS VARCHAR(20)
DECLARE @.Eval_SubType AS VARCHAR(20)
-- DECLARE @.OutCome AS VARCHAR(20)
-- DECLARE @.Evaluate AS INT
-- DECLARE @.Days AS INT
DECLARE @.Message AS VARCHAR(1000)
DECLARE @.Mandatory AS BIT

-- This is the variable used to interpret the Precedant subtype
DECLARE @.Prec_Subtype AS VARCHAR(20)

-- DECLARE @.Exists AS BIT --this is supposed to be the deceision maker
variable to be used within the precedant check.

DECLARE @.Precedant_SubTypes_Cnt AS INT--This is the variable used to
recordcount the Precedant Subtypes to be checked
DECLARE @.Counter AS INT -- Counter used to loop through the Table of
precedant Subtypes.
DECLARE @.ROWCOUNT AS INT -- Temporary ROWCOUNT

SET @.Counter = 1

--Process to retrive @.Curr_ActionSubType Variable
CREATE TABLE #Curr_ActionSubType(ActionSubType VARCHAR(100))
EXEC ('INSERT INTO #Curr_ActionSubType SELECT SubType FROM '+ @.DbName
+'.resadm.action WHERE '+@.DbName+'.resadm.action.Actionid = '+
@.ActionID+' AND ' +@.DbName+'.resadm.action.status =''A''')
SET @.Curr_ActionSubType = (Select ActionSubType from
#Curr_ActionSubType)
DROP TABLE #Curr_ActionSubType

--Process to retrive @.StartDate Variable
CREATE TABLE #StartDate(StartDate DATETIME)
EXEC('INSERT INTO #StartDate SELECT CONVERT(DATETIME,
'+@.DbName+'.Resadm.Action.DateofAction + '' ''+
'+@.DbName+'.Resadm.Action.TimeOfAction)
FROM '+@.DbName+'.resadm.action
WHERE '+@.DbName+'.resadm.action.Actionid = '+ @.ActionID +' AND
'+@.DbName+'.resadm.action.status =''A'''
)
SET @.StartDate = (Select StartDate from #StartDate)
DROP TABLE #StartDate

SET @.Rule_ID = (SELECT Rule_ID FROM Rules WHERE Curr_Subtype =
@.Curr_ActionSubType and Status <>0 GROUP BY RULE_ID)
SET @.Eval_SubType = (SELECT Evaluate_Subtype FROM Rules WHERE
Curr_Subtype = @.Curr_ActionSubType and Status <>0 GROUP BY
Evaluate_Subtype)
SET @.OutCome = (SELECT OutCome FROM Rules WHERE Curr_Subtype =
@.Curr_ActionSubType and Status <>0 GROUP BY OutCome)
SET @.Evaluate = (SELECT Evaluate FROM Rules WHERE Curr_Subtype =
@.Curr_ActionSubType and Status <>0 GROUP BY Evaluate)
SET @.Days = (SELECT Days FROM Rules WHERE Curr_Subtype =
@.Curr_ActionSubType and Status <>0 GROUP BY Days)
SET @.Message = (SELECT Message FROM Rules WHERE Curr_Subtype =
@.Curr_ActionSubType and Status <>0 GROUP BY Message)
SET @.Mandatory = (SELECT Optional_Mandatory_Precedant FROM Rules WHERE
Curr_Subtype = @.Curr_ActionSubType and Status <>0 GROUP BY
Optional_Mandatory_Precedant)

-- create the temporary table for the Subtypes to be evaluated
CREATE TABLE #Preceding_SubTypes_Details
(SubTypes_LIST_ID INT IDENTITY (1, 1) NOT NULL,
RULE_ID NUMERIC(9),
SubType VARCHAR(20),
)

-- insert the current subtype that needs to be evaluated.
INSERT INTO #Preceding_SubTypes_Details
SELECT Rule_ID, Prec_Subtype
FROM Rules_Details
WHERE Rule_ID = @.Rule_ID

-- create the History table for Reference
--sk/*Modified to accomodatethe need ot dynamic database name to
retrive from the different Resman databases
CREATE TABLE #dsHistory ( ActionID INT,
PersonID INT,
ActionTypeID VARCHAR(1),
DateofAction DATETIME,
Status VARCHAR(1),
Subtype VARCHAR(6),
ActionTypeName VARCHAR(30),
ActionSubtypeID VARCHAR(6),
EffectCandidateCurrentState VARCHAR(10),
TaxCode VARCHAR(6)
)
EXEC ('INSERT INTO #dsHistory SELECT
'+@.DbName+'.Resadm.Action.ActionID, '
+ @.DbName+'.Resadm.Action.PersonID,
'+@.DbName+'.Resadm.Action.ActionTypeID, '
+ 'CONVERT(DATETIME, '+@.DbName+'.Resadm.Action.DateofAction + '' ''+
'+@.DbName+'.Resadm.Action.TimeOfAction)DateofActio n, '
+ @.DbName+'.Resadm.Action.Status, '+@.DbName+'.Resadm.Action.Subtype, '
+ @.DbName+'.ResAdm.Action_Types.ActionTypeName,
'+@.DbName+'.Resadm.Action_subtypes.ActionSubtypeID , '
+ @.DbName+'.Resadm.Action_subtypes.EffectCandidateCu rrentState,
'+@.DbName+'.Resadm.Person.TaxCode '
+ ' FROM '+@.DbName+'.Resadm.Action '
+ ' INNER JOIN '+@.DbName+'.ResAdm.Action_Types WITH(NOLOCK) '
+ ' ON '+@.DbName+'.ResAdm.Action_Types.ActionTypeID =
'+@.DbName+'.Resadm.Action.ActionTypeID '
+ ' INNER JOIN '+@.DbName+'.ResAdm.Action_SubTypes WITH(NOLOCK) '
+ ' ON '+@.DbName+'.Resadm.Action.subtype =
'+@.DbName+'.ResAdm.Action_SubTypes.actionsubtypeid '
+ ' INNER JOIN '+@.DbName+'.Resadm.Person WITH(NOLOCK) '
+ ' ON '+@.DbName+'.Resadm.Person.PersonID =
'+@.DbName+'.Resadm.Action.PersonID '
+ ' WHERE '+@.DbName+'.Resadm.Action.actionID <>
CONVERT(VARCHAR,'+@.ActionID+')'
+ ' AND '+@.DbName+'.Resadm.Action.PersonID =
CONVERT(VARCHAR,'+@.PersonID+')'
+ ' AND '+@.DbName+'.Resadm.Action.Status =''A'' '
+ 'AND (CONVERT(DATETIME,'+@.DbName+'.Resadm.Action.Dateof Action +''
''+ '+@.DbName+'.Resadm.Action.TimeOfAction) > '
+ ' ISNULL(( SELECT
MAX(CONVERT(DATETIME,'+@.DbName+'.Resadm.Action.Dat eofAction + '' ''+ '
+ @.DbName+'.Resadm.Action.TimeOfAction)) '
+ ' FROM '+@.DbName+'.Resadm.Action '
+ ' WHERE ('+@.DbName+'.Resadm.Action.PersonID =
CONVERT(VARCHAR,'+@.PersonID+')) AND '
+ ' ('+@.DbName+'.Resadm.Action.Subtype =''ZERO'') '
+ ' AND ('+@.DbName+'.Resadm.Action.Status=''A'')),0)) '
+ ' ORDER BY CONVERT(DATETIME,'+@.DbName+'.Resadm.Action.DateofA ction +
'' ''+ '+@.DbName+'.Resadm.Action.TimeOfAction )DESC, ActionID DESC')

--sk*/
SET @.EndDate = (SELECT ISNULL((SELECT DateOfAction
FROM #dsHistory
WHERE SubType = @.Eval_SubType), getdate()))

-- set the rowcount to retrieve the number of check to be carried out

SET @.Precedant_SubTypes_Cnt = (SELECT COUNT(*) FROM
#Preceding_SubTypes_Details)

WHILE @.Counter <= @.Precedant_SubTypes_Cnt
BEGIN
SET @.Prec_Subtype = (SELECT SubType from #Preceding_SubTypes_Details
WHERE SubTypes_LIST_ID = @.Counter)

SET @.ROWCOUNT = (SELECT COUNT(*) FROM #dsHistory WHERE Subtype =
@.Prec_Subtype)

IF @.ROWCOUNT > 0
BEGIN
SET @.Exists = 1
END

IF @.ROWCOUNT = 0
BEGIN
IF @.Mandatory = 1
BEGIN
SET @.Message = @.Message + ' ' + @.Prec_Subtype + ' Is Missing; '
SET @.Counter = @.Precedant_SubTypes_Cnt
SET @.Exists = 0
END
ELSE IF @.Mandatory = 0
BEGIN
SET @.Message = @.Message + ' ' + @.Prec_Subtype + ' Is Missing; '
SET @.Exists = @.Exists
END
END

SET @.Counter = @.Counter+1
END

IF @.Exists = 0
BEGIN
EXEC(
' UPDATE '+@.DbName+'.Resadm.Action '
+ ' SET '+@.DbName+'.Resadm.Action.Status = ''I'' '
+ ' WHERE '+@.DbName+'.Resadm.Action.ActionID = '+@.ActionID+' SET
@.ReturnMessage = '+@.Message
)
END
ELSE
IF @.Exists = 1
BEGIN
SET @.ReturnMessage = @.Message
END

IF @.Rule_ID = Null
BEGIN
SET @.ReturnMessage = 'Validation Rule Not Present'
END
-- Select 'Exist value : ', @.Exists, 'Return message is : ',
@.ReturnMessage
DROP TABLE #Preceding_SubTypes_Details
DROP TABLE #dshistory

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO}Sandesh (sandesh27uk@.gmail.com) writes:
> Me saying " has any body come across such error would be
> underestimating".
> Well I am getting a very peculiar and unique error "Line 1: Incorrect
> syntax near 'Actions'."

You will have to excuse me, but the error looks very standard and
commonplace to me.

This is what you get when you entagle yourself into a web of dynamic
SQL. Look at http://www.sommarskog.se/dynamic_sql.html#Dyn_DB for
suggestions of alternative strategies.

My bets goes on the UPDATE statement at the end. Overall, including
the database name in the column qualifications is overkill.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Limited Query Builder in sql2005 sms

Can any body point me in the right direction before i pull all my hair out, (not much left).

Moving from sql 2000 to 2005, yes very pretty, but all i want 2do is quickly query a table, like u could in 2000 by right click the table name and select Query, my experiance of 2005 has been very frustrating, why remove useful features, not impressed.

anyway

Is it possible to invoke, from within the sql2005 sms, the Query Builder that is used in the Report Designer as this builder has 100% more functionality than the cut down version that comes standard in the very expensive sql2005 sms.

Any ideas?

Cheers

First, let me respond to clarify a 'mis-statement':

the very expensive sql2005 sms

SSMS is suprisingly enough, included with SQL Server. It is NOT sold separately, there is not a ''price' for SSMS.

Now, if you wish to use the query builder function, similar to the Access/Excel/SQL 2000 Query Builder, it is still available.

Unfortunately, it doesn't have a dedicated menu entry. Hopefully, that will be corrected in a future release.

To use the query builder functionality, follow these steps (grumble if you want, but these are the steps...):

Right-click on a table, select [Open Table] The table will open in Grid format. If it is a large table, it could take some time, so you may wish to click on the 'Red' stop button in the bottom of the data pane. Then, in the toolbar, on the left, will be icons for the Diagram, Criteria, and SQL panes. Choose the ones you desire. Then, when your query is constructed, click on the [Execute SQL] button (the red exclamation mark).|||

Thank you for your condesending reply, nice to see you didnt actualy answer my question. in fact after a bit of browsing on and off this site this same limitation towards productivity has been brought up many times without being recified, as far back as 2005, your instructions are typical of the vacuum packed training many "experts" seem to excel in. if i opend a table for query like you suggest, a table that could have over 500,000,000 records in it, good use of server resources that, once ive managed to cancel the request, after about 10 clicks i could get a visual query editor of sorts, but i can only open that table once, very useful, we also get a "change type" button thats never active, start typing "insert into <tablename>" into the sql pain and the columns change in the criteria pain like as you would get if the could select "change type". As ive said previously these limitations were highlight by other users as far back a s2005, we still seem to be waiting for a decent interface.

As a paying customer, when i buy software, i receive, if im lucky, a cd to install, this cd installs the software I've purchased, usually, as far as im conserned the software that gets installed is what ive paid for, it dosent tell me this bit is free, or that bit is free, so give the, its free, what do u expect attitude. The problems ive experianced after purchasing this expensive product seems to have been shared by many within this forum, without be rectified. but not to worry it will all be sorted in the next version that us punters will have to pay up for again.

nice touch about the shoes, but when i buy a new pair of shoes, i pay decent money for them, because i can, and i get a pair that are as comfortable as my old ones, they have been Well designed and tested propely and are fit for purpose. glad i dont wear m$ shoes.

thanks again for you help and suggestions, your a credit to your profession! in my opinion.

|||The product can always can be as good as the responses from the field are. Microsoft introduced the http://connect.microsoft.com portal where customer can suggest new features and put up bugs which they encountered. These sites are monitored by the product teams, in fact they are doing periodic rounds to discuss effort for changes and impact on the product for bringing updates / feature in Serverice Releases / Service Packs. Feel free to post a connect suggestion and you will see that it will be picked up and answered soon.

Jens K. Suessmeyer

http://www.sqlserver2005.de

Limit rows to 25 in the body

Im trying to limit the body of the report to 25 rows before page break. Any help would be appreciated.

Hi, Erik,

Look for the Page Breaks section in this topic: http://msdn2.microsoft.com/en-us/library/ms157328.aspx

Download the latest Books Online update:
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

This posting is provided "AS IS" with no warranties, and confers no rights.