Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

Parameters Expand / Collapse
Author
Message
Posted Friday, November 04, 2011 12:31 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:35 AM
Points: 12,206, Visits: 9,169
Nice question, thanks.



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1200392
Posted Friday, November 04, 2011 4:07 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 2:25 PM
Points: 1,253, Visits: 13,546
good question! thanks!


rfr.ferrari
DBA - SQL Server 2008
MCITP | MCTS

remember is live or suffer twice!
Post #1200459
Posted Friday, November 04, 2011 11:46 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, April 14, 2014 5:33 AM
Points: 107, Visits: 220
I knew this and thats what I focused on too.
The other errors you can choose occurs further down in the code, so I choosed the ',' error.

Regards Marten
Post #1200804
Posted Monday, November 07, 2011 5:36 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, October 24, 2012 8:17 PM
Points: 1,588, Visits: 247
Good question regardless of the minor errors. Thanks.

http://brittcluff.blogspot.com/
Post #1201328
Posted Monday, November 07, 2011 8:17 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 7:29 PM
Points: 565, Visits: 65,732
As everyone has stated already this should have started by saying in SQL 2008 or higher. In SQL 2005 there is no correct answer.
Post #1201459
Posted Friday, November 11, 2011 8:39 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 3:30 PM
Points: 1,525, Visits: 1,833
Thank you for an interesting question.

I liked the parameters with the dynamic t-sql string, so I thought I'd try it out. I don't work with linked servers, but I was interested to see if I could use this parameter syntax. No, I got a syntax error. Looking at the BOL syntax, it looked to me as if the [ AT linked_server_name ] was optional, but apparently not?
Post #1204216
Posted Tuesday, November 15, 2011 6:41 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 6:40 AM
Points: 3,683, Visits: 4,818
Carla Wilson-484785 (11/11/2011)
Thank you for an interesting question.

I liked the parameters with the dynamic t-sql string, so I thought I'd try it out. I don't work with linked servers, but I was interested to see if I could use this parameter syntax. No, I got a syntax error. Looking at the BOL syntax, it looked to me as if the [ AT linked_server_name ] was optional, but apparently not?

The "AT linked server" is optional, however, for a local query one would not likely use dynamic SQL with parameters to populate a variable, as it can be done directly ,e.g.
SELECT @Var1 = ColumnA FROM Table WHERE ColumnX = @Var2

The use of placehoders was designed explicitly for remote execution, to eliminate the need to build strings, converting variables to varchar etc.
It does appear to make the code more readable as well...


____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Post #1205919
Posted Tuesday, November 29, 2011 3:50 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, December 13, 2012 12:23 PM
Points: 163, Visits: 41
In Sql Server 2005 you cann´t set a variable like this:

@x varchar(10) = '12345'

Only works in Sql 2008.
Post #1213599
Posted Friday, March 02, 2012 3:56 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, February 11, 2014 9:09 AM
Points: 483, Visits: 242
Great learning process on Linked Servers executions.

Thank you.
Post #1261075
Posted Thursday, December 26, 2013 7:55 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, March 20, 2014 6:00 AM
Points: 588, Visits: 276
philip.cullingworth (11/3/2011)
When running this in SQL2005 I get the following response (after answering the question)

Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.
Msg 137, Level 15, State 2, Line 7
Must declare the scalar variable "@CustAccount".

You can not set the value of a variable at the same time as declaring it until SQL 2008.


I had the answer correct, but I looked at the DECLARE statements again, read the question and noticed "...SQLServer2005 and greater..." The actual answer is "...error...assignment."

"Prior to SQL Server 2008, assigning a default value (or initial value) to a local variable is not allowed; otherwise this error message will be encountered."
Post #1525970
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse