directly deleting sql view

i have created a view. And deleted it directly from the database. using Shift+delete. WIll it affect the tables, which has used in that view?No, data and tables are OK....
Dont worry.... :)

You have to be careful thou on what you do with a database it could be millions of dollars lost for the company or not accounted for.
Get yourself test database or play with Pubs first.

Direct Connection to Local SQL express gives error

I'm just starting out here.

I created a simple formview connected to a local file copy of the database by adding the database into the project. It worked fine.

However, I noticed that this copy of the database was not synch'ing with the database with the same name in SQL express. So I created a new connections string to access the SQL database directly out of SQL express. Its just a simple Select * from column. However, this gives an error. Why?

Server Error in '/NETCatMgr' Application.

---

The data types ntext and nvarchar are incompatible in the equal to operator.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: The data types ntext and nvarchar are incompatible in the equal to operator.

This is the connection string

<

addname="CatalogMgrConnectionString"connectionString="Data Source=mymachine\sqlexpress;Initial Catalog=CatalogSQL;Integrated Security=True"providerName="System.Data.SqlClient" />

Doesn't make sense. How can the formview work fine with the SQL integrated into the project but not working with a connection string to the SQL Express proper?

The error seems to be a data operation error other than a connection error. It indicates your code was trying to compare nvarchar value and ntext using "=" operator. Please make sure you choose proper data for comparation. To manipulate text/ntext/image data in SQL Server, you should use specific functions:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_fa-fz_0gs3.asp

|||Its not me -- the problem appears to be some kind of bug in the GridView. The database column is a type nText and the GridView doesn't seem to be able to write code for SQL databases with column types nText. It writes the code with a string comparison and then it crashes... I didn't write any code at all.

Direct access to SQL Server 2005 from Windows Mobile

Hi everyone,

I've created a SQL Server database in Visual Studio 2005 and I am also planning to create a Windows Mobile application to access, edit and update this data.
So I heard that it is possible to directly access to SQL Server from the device using SQLClient rather than creating an SQL ServerCE.

I tried to use that method but I get a connection error. Here is the bit of code that was generated.

this._connection = new System.Data.SqlClient.SqlConnection();
this._connection.ConnectionString = "Data
Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirecto ry|\\MainRestaurantManagementdb.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True;";
}

Can anyone help me on this please as I am stuck and can't figure out how to solve it?

Quote:

Originally Posted by Hezal

Hi everyone,

I've created a SQL Server database in Visual Studio 2005 and I am also planning to create a Windows Mobile application to access, edit and update this data.
So I heard that it is possible to directly access to SQL Server from the device using SQLClient rather than creating an SQL ServerCE.

I tried to use that method but I get a connection error. Here is the bit of code that was generated.

this._connection = new System.Data.SqlClient.SqlConnection();
this._connection.ConnectionString = "Data
Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirecto ry|\\MainRestaurantManagementdb.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True;";
}

Can anyone help me on this please as I am stuck and can't figure out how to solve it?

Moved to SQL forum. This is more of an applications issue then a mobile specific issue.

Dimensional Modelling and Userbased Reporting

We are using SSAS 2005 for the cubes and reports will be created in Reporting Services as well as Proclarity Desktop Professional (all using windows integrated authentication). The reports will be displayed using a Sharepoint portal, again using the windows integrated authentication.

We have a Fact Table like FactRevenues which has revenueamount and a few other measures.

It is linked to 2 Dimension tables - DimProjects and DimCustomers through ProjectCode and CustomerCode.

DimProjects contains 2 dimension fields (apart from many others) - ProjectMgr and ProjectDir.

Similarly DimCustomers contains 3 fields - AccountMgr,AccountDir and EngagementDir.

We have an User Dimension which is the referenced dimension. DimUser contains UserID and other details of the user. We store the NT login(or at least something from which NT login can be obtained) in the UserID.

The five fields mentioned above - ProjectMgr, ProjectDir,AccountMgr, AccountDir and EngagementDir stores the UserID and are linked to the UserID in the User Dimension.

We have created hierarchies/pseudo dimensions which link like RevenueFact-ProjectMgr-User, RevenueFact-ProjectDir-User, RevenueFact-AccountMgr-User,RevenueFact-AccountDir-User and RevenueFact-EngagementDir-User. So, for the MDX expression we can use these to check against the logged in User.

The Problem:

Our requirement is, the person logged in should be able to see only the details (measure values) that are relevant to him/her. Basically, the aggregate of RevenueAmount should be done based on the following conditions:

If the logged in user is a Project Manager for some projects, then the person should only be able to see the aggregated measure for the projects for which he/she is the manager (checked from the ProjectMgr field). Similarly for ProjectDir.

If the logged in user is an Account Manager for some clients, then the person should only be able to see the aggregated measure for the clients that he/she handles (checked from the AccountMgrfield). Similarly for AccountDir and EngagementDir.

There are possibilities, though remote, that a single person can be AccountDir as well as ProjectDir. Basically, the system should support the possiblity for a person to be in any combination of the five fields. This shouldn't be an issue as this will be taken care of automatically once we set up the dynamic dimension security for each of these dimensions.

There will a different set of users - SuperUser/Admins - who will be able to view all the details without any restrictions. We HAVE NO ISSUES WITH THIS AS we created a role specifically for Admins without any restrictions and a role for others (everyone) which will be applied with the dynamic dimension security.

What we tried:

We have actually gone through the links that you have sent earlier, when we were trying to solve the issue. But it seems like we are missing some small thing.

Our MDX for dimension security

Filter( [DIMIRLINE -IR FORM REF NO - IR H CUST - ACCOUNT MGR].[DIMUSERS].[DIMUSERS] = UserName)

UserName supposedly being the function for obtaining the currently logged in user.

But this didn’t seem to restrict the users.

Your syntax for the filter function does not look correct. You need to pass it a set and then the criteria with which to filter that set. I would expect to see something more like the following:

Filter(

[DIMIRLINE -IR FORM REF NO - IR H CUST - ACCOUNT MGR].[DIMUSERS].[DIMUSERS].members

, [DIMIRLINE -IR FORM REF NO - IR H CUST - ACCOUNT MGR].[DIMUSERS].[DIMUSERS].CurrentMember.Name = UserName()

)

|||

This following is the table I created through SSAS browser having UserID,EGName and Mothwisesales Column

where USerID is the member of [Account Mgr].[UsersID] Attribute

I want to show the only row when perticalur User LogsIn(NTlogin) insted of showing the details of all User.He should see only his details(I want to restrict this in Cube level rather on SSRS/Proclarity).

I created a Hierachy i.e UserHierachy -->Role->UserID

Role attribute always contains{User,Manager}.If UserID is belongs to User Role He ll be see only his details from NTLogin and if UserID is belongs to ManagerRole He ll be see all the userdetails.

Please help me regarding this.

Thank u

with regards

Saroj

userID

Mar

chandrashekar.cs

EG11

$700.00

EG12

$495.63

EG14

$1,500.00

EG21

$15,600.83

EG24

$0.83

EG26

$8,726.37

EG34

$2,103.34

EG39

$300.00

EG3P

$197.55

EG41

$5,866.68

EG42

$16,900.00

EG51

$2,200.00

EG52

$1,400.83

EG53

$0.00

EG61

$11,757.92

EG62

$199.96

EG65

$363.40

EG72

$3,400.00

EG74

$2,400.00

madhankumar.s

$228,385.98

ramaprasath.mss

$6,402.35

sarojkumar.nishanka

$6,308.43

sethumadhavan.sb

$284,112.19

sql

Dimension Security

Hi,

I have Created Dimension Security by restricting the user to see only his information whoever login to the system by creating a new role. It is working fine by checking the cubes by changing the role through change user option but when the cube was integrate it to any Analysis services reporting tool(Excel, Proclarity) inspite of the roles mentioned it displays the records of all the user.

Can anyone please help me out in this situation?

guessing wildy...

if it can identify the user, they get a restricted view

if it cannot identify the user, they see everything

maybe you need to switch off some kind of anonymous access

maybe it cannot identify the user when they are coming in via one of these tools? is it possible to display the username somewhere so you can at least dis/prove this?

|||

Hi adolf,

Thanks for the reply.....

Hence we are getting the analysis services cubes to any of this tools by accessing the server directly and any of the processing in cube can be done only in server and the tool is used to only show the data. so i hope there is nothing to do with the reporting tool regarding security. something to be done with in the cube

Dimension Hierarchy does not allow duplicates

Hello,

I am very new to data warehousing.

I have created a hierarchy on "StoreID", "Make" and "Model".

The Make "Harley" should appear below all of our Harley-Davidson store ids. It looks like there are no duplicates of any make or model in the hierarchy when I browse the dimension. I am using BI dev studio and SQL 2005 analysis services.

I'm guessing it's just some property I have not found yet.

Any ideas?

Thanks

Hello! In the middle pane in the dimensions editor, where you create the the user- or natural hierarchies, you can expand the lower to levels(Make and Model) and create attribute relations for the lower levels.

Attribute relations are pointers to what is the next higher level in an user hierarchy.

Drag the attribute Make(from the left window) and make it an attribute relation under Model. Drag StoreID to Make also as an attribute relation.

Have a look at attribute relations in Books On Line or download the performance guide for SSAS2005, pointed to at the top of this news group.

HTH

Thomas Ivarsson

|||

Hi,

Yes, that is the way I have it setup.

StoreID is on top.

Then Make with a relationship to StoreID.

Then Model with a relationship to Make and StoreID.

That is the correct way to do this isn't it?

I get a list of makes under storeid's and models under makes but any given make or model only appears once in the hierarchy.

Thanks

|||

Model should only be related to Make.

And remove the redundant relationships under the dimension key after this change.

HTH

Thomas Ivarsson

|||

Thomas,

I removed the storeid relationship from model.

Didn't see any redundant relationships under the dimension key.

I re-processed it and still have the same issue.

I must be doing something else wrong.

Thanks for the advice!

|||You're going down the right path. However, I think to do what you want to do, you're going to have to create a composite key consisting of the StoreID and the Make for the Make attribute. Therefore, Harley will appear under every store that sells Harley. You're also going to have to create a composite key of StoreID and Model for the Model attribute.|||

Probably the best way to deal with this is to create a "bridge" fact table that holds the relationship between models and stores, then use this table as the intermediate fact table in a many-to-many relationship.

When you put StoreID on a make/model and set the relationships up the way you have, you are implying that each make/model has only one "parent" storeID. When the dimension is being built the storeid attribute basically gets overwritten multiple times and the last one wins and the make/model only shows up under that last storeid.

|||

Hello again! This is how natural hierarchies works. You have a cardinality property on the attribute relation that only can be one-to-one or one-to-many .

Like others have told you you would have to rethink your design of the dimension.

Regards

Thomas Ivarsson

sql

Dimension Hierarchies

When a new hierarchy in created in a dimension, all of the attributes are listed as related to the lowest level of the hierarchy.

I have heard that this restricts SSAS to only creating aggregations at the leaf and all levels for that hierarchy until you re-distibute the attribute relationships. This will then allow SSAS to create aggregations at levels higher than the leaf level.

What I mean by this is that when you have both Year, Month and Day in a hierarchy, you can have Year and Month under Day (not allowing aggregations at something other than the leaf level) or you can put Year under Month and now that will allow aggs on all levels of the hierarchy.

Can someone confirm this?

What are the implications of not defining the relationships - what are the benefits of doing it?

Thanks

Mark

http://mgarner.wordpress.com

Hi Mark,

The "Project REAL: Analysis Services Technical Drilldown" paper seems to confirm what you've surmised:

http://www.microsoft.com/technet/prodtechnol/sql/2005/realastd.mspx

>>

Best Practice: Spend time with your dimension design to capture the attribute relationships within the dimensions.

Important: You must define attribute relationships if you want to design effective aggregates, if you want effective run-time calculations from the formula engine, or if you want valid results in MDX time functions.

In the hierarchy-based nature of SQL Server 2000 Analysis Services (which only supports natural hierarchies), aggregates are designed around hierarchies. In SQL Server 2005 Analysis Services, aggregates are combinations of attributes. User-defined hierarchies are not used. The Storage Design Wizard uses attribute relationships to determine when combinations of attribute rollups will be useful (and thus aggregates will be designed for those attributes). Without relationships, one attribute is as significant as any other attribute, so the Storage Design Wizard simply ignores the attribute and uses the ALL level for the dimension. Thus if you want to design effective aggregates, you must define attribute relationships. Without them the system still returns the proper number, but values must be calculated at run time and aggregates are not useful.

>>

There are more detailed explanations of AS 2005 aggregations elsewhere, such as in the "Designing Aggregations" sections of Teo Lachev's book on AS 2005, and in the PASS 2005 session: " Understanding Analysis Services 2005 Aggregations from Every Angle" (if you have access to the archive).

|||

Deepak,

Thanks for the info. That doc was great. I knew of some of the Project REAL documentation, but not that part.

Thanks for the help.

Mark

http://mgarner.wordpress.com

Dimension creation manually issue

I have created a new dimension manually from a project.But when I rebuild,deploy and process and view browser for that cube, I couldnt see the new dimension from the list of dimensions. Under what condition should the new dimension 'relates' to the cube?

Thanks.

Regards

Alu

You have to add it to your cube and then define right relationship between dimension and measure group|||

You do this in the dimension usage tab in the cube editor in BIDS.

Right click on the dimensions and choose add cube dimension

HTH

Thomas Ivarsson

|||

Thanks.

Regards

Alu

Digital Signature

Hi I have created a Client/Server application. The Client connects remotely to the SQL 2005 server using thier unique user name and password.

The client application allows the users to update a form.

I need to add to the database a digital signature for that user when they update that form. This is intended to be a replacement for a physical signature that would appear on a paper form.

For such an application, most of the signing logic should be at the client level. I'm not sure what is your question and how it relates to SQL Server security - please clarify.

Thanks
Laurentiu

Difficult times deploying a few packages to SQL Server and running as a job

Hi Guys!

I have created a big list of packages, some calling others. They all work fine from my computer using Visual Studio.

When I try to deploy them (building them with deployment turned on and running them either directly from Management Studio or as a job) I get the errors with the password of connection strings. From what I read so far its the encryption process that kills it.

I have tried to add a password to some packages, but it still didnt work (only when run directly on my computer in management studio after deploying to SQL Server, but not as a job).

I have tried to change ProtectionLevel to SecurityStorage, wouldnt let me save in Visual Studio (I understand it is ot allowed in VS because you are saving to File System, how the hell am I supposed to save it to anything else? why is it showing there if its not even valid?).

If anyone can please give me the steps to doing it properly, that would be awesome. I simply need to run the packages from SQL Server! thats all! I have no idea why it has to be soooo difficult :/

A step-by-step guide would be different depending on several factors. Are you planning to store your packages as file system files? Are you using package configuration? How are you going to run the packages (may be via SQL Agent job)?

Personally, I store package as file system files and use something similar to method 4 in this KB article. I hope that helps

http://support.microsoft.com/kb/918760

|||

Hi Refael!

I don't mind saving it to the file system or putting it on SQL Server, as long as it works! :)

How do you actually create this package configuration? and how do you then indicate to the data process that the password is stored there?
I am replicating from Oracle using Microsoft Oracle provider (for which I need the password) to SQL Server.

Thanks so much for your help

Guy

|||

Weird thing: I have changed one package to have a password and when I import it to 'stored packages' or run it from the file system on Management Studio on the server itself (simply copying the files from the development machine to the server) its not asking me for a password and it runs it. does this makes sense?

|||

Just search for package configurations to set connection strings...it would make your packages nicely portables.

|||

I have tried to find out (google) how to make a configuration file in xml but couldn't find much and have no idea what the format should look like.

If you could help me here I would really appriciate it.

|||

guyguy2003 wrote:

I have tried to find out (google) how to make a configuration file in xml but couldn't find much and have no idea what the format should look like.

If you could help me here I would really appriciate it.

http://msdn2.microsoft.com/en-us/library/ms141682.aspxsql

Differential Backup Files

When a new scheduled job is created for a Differential backup, the file specified in the Destination folder is automatically created by SQL Server. After the first time the job runs, is there a way to configure SQL Server to give each Differential file a unique name, including the timestamp (i.e. similar to Full Backup jobs)? I noticed my only options are 'Append to File" and "Overwrite Existing File." If I choose to enable "Backup Set Expiration," the backup job will not run, because it wants to append/overwrite the filename specified.pretty easy to do via T-SQL statement. The following script creates a backup with date and hour as timestamp in file name.

declare @.hour varchar(2), @.date varchar(8)
set @.hour = substring(convert(char(2), getdate(), 108), 1, 2)
set @.date = convert(varchar(8), getdate(), 112)
exec ('use master backup database xxx to disk = ''D:\backup\xxx_db_' + @.date + @.hour + '.bak '' with init')

different values between Relational and MOLAP in a sum measure... BUG?

Hello,
I'm working in a Windows 2003 Server, SQL Server 2000 and Analysis Services 2000.
I have created two models in Analysis Services (MOLAP):
- MODEL 1 gets info directly from the relational FACT table: "FCT_SALES"
- MODEL 2 gets info from a view with: "SELECT * FROM FCT_SALES"
FCT_SALES has a measure (sum and double - the column in the relational table is float).
This measure has 10 rows in the FCT_SALES.
Problem:
When we agregate the measure rows in Query Analyzer, MODEL 1 and MODEL 2 return 20. --> IT'S OK!
When I use Analysis Services (and ProClarity), MODEL 1 returns 20. --> IT'S OK!
When I use Analysis Services (and ProClarity), MODEL 2 returns 17. -->WRONG!!
WHY?
When I make Drill Through (Drill to Detail) in Analysis Services (or ProClarity) to see the rows that compose the value, and export them to Excel, I get the 20. --> IT'S OK!
So, WHY does Analysis Services (and ProClarity) give me 17 ?!?
If the SUM(rows) give me 20, WHY does Analysis Services give me 17 ?!?
Thanks.
Hi guys,
Yesterday nigth, I found the problem: something that shouldn't happen in the source info, happened! Murphy's Law )))
Analysis Services wasn't causing the info inconsistent.
Thanks anyway.

different values between Relational and MOLAP in a sum measure... BUG?

Hello,
I'm working in a Windows 2003 Server, SQL Server 2000 and Analysis Services
2000.
I have created two models in Analysis Services (MOLAP):
- MODEL 1 gets info directly from the relational FACT table: "FCT_SALES"
- MODEL 2 gets info from a view with: "SELECT * FROM FCT_SALES"
FCT_SALES has a measure (sum and double - the column in the relational table
is float).
This measure has 10 rows in the FCT_SALES.
Problem:
When we agregate the measure rows in Query Analyzer, MODEL 1 and MODEL 2 ret
urn 20. --> IT'S OK!
When I use Analysis Services (and ProClarity), MODEL 1 returns 20. --> IT'S
OK!
When I use Analysis Services (and ProClarity), MODEL 2 returns 17. -->WRONG!
!
WHY?
When I make Drill Through (Drill to Detail) in Analysis Services (or ProClar
ity) to see the rows that compose the value, and export them to Excel, I get
the 20. --> IT'S OK!
So, WHY does Analysis Services (and ProClarity) give me 17 ?!?
If the SUM(rows) give me 20, WHY does Analysis Services give me 17 ?!?
Thanks.Hi guys,
Yesterday nigth, I found the problem: something that shouldn't happen in the
source info, happened! Murphy's Law )))
Analysis Services wasn't causing the info inconsistent.
Thanks anyway.

Different Server Name

Hi all,

I have a database created with MS SQL 2000 and I want to restore the database in SQL 2005 Express Edition. The problem is that both of the Sql servers are located on different computers so they have different server names. Is that possible to do it? I have tried to restore using the usual way and it failed. Thank you very much for your help.

Yes, you should be able to restore the database on a new machine (regardless of machine name). Are you hitting an error? If so, what does it say?

Thanks,
Sam Lester (MSFT)

|||TITLE: Microsoft SQL Server Management Studio Express

Restore failed for Server 'KID\SQLEXPRESS'. (Microsoft.SqlServer.Express.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476

ADDITIONAL INFORMATION:

System.Data.SqlClient.SqlError: The backup set holds a backup of a database other than the existing 'Inventory List' database. (Microsoft.SqlServer.Express.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&LinkId=20476

BUTTONS:

OK

The error message is something like above. Any idea how to solve that? Thanks a lot|||

Here's a thread showing how to work around this error:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=694693&SiteID=1

Thanks,
Sam Lester (MSFT)

|||

Hi Sam,

Thank you very much for the link. Now, I have understood what the problem is.

different searches for web matrix and sql server

Hi Everyone. I have created a procedure on sql to obtain information about two tables ( bookinfo and author). The procedure is :

CREATE PROCEDURE FindBookByAuthor
@.Aname char
AS

select b.libcode, b.title, b.subject, a.authorname, b.instock
from bookinfo b join authorinfo a
on b.authorid = a.authorid
where a.authorname like '%' + @.aname + '%'
GO

I have tested this procedure changing the variable @.aname for the surname of the author. This works fine. The problem comes when I execute this procedure from web matrix. I use a textbox to obtain the data for the variable @.aname. I send the data. What happens is that when I run the web page and look for a book through the author the select statement behaves differently and gives me a different output.

I would be glad to know why this happens

ThanksShow some code. Failing that, verify in SQL Profiler that the SQL/Parameters you think you are executing is in fact what is being executed.|||well, the asp.net code is :
--------------------

Sub Button2_Click(sender As Object, e As EventArgs)
Dim objConn As SqlConnection
 Dim dataReader As SqlDataReader
 dim constring as string
 Dim objCmd As SqlCommand
 constring = "server='(local)'; trusted_connection=true; database='dbjaime'"
 objConn = New sqlconnection(constring)
 dim strsql as string
 strsql = "EXECUTE findtitle '" & textboxtitle.text & "'"
 objCmd = New SqlCommand(strSQL, objConn)
 objConn.Open()
 dataReader = objCmd.ExecuteReader()
 'Bind to DataGrid
 dtgbooks.DataSource = dataReader
 dtgbooks.DataBind()
 objConn.Close()
 objConn.Dispose()
End Sub
--------------------

Where "findtitle" is a procedure I showed you before.|||Use SQL Profiler and see if what you are sending is what you think you are in both cases (where it works and where it does not).|||I have tried to use the sql profiler, which I had never used before. It shows me the last executions. Then I have loaded my web page and execute the event "button click" I send you before. Now what I see on the sql profile is:

exec sp_executesql N'EXECUTE findtitle ''design'''

among other stuff. I this what I should get?|||The findtitle line is what I was having you look for.

Execute the code in the way that it works, as well as the way that it does not work, and compare what appears in SQL Profiler. You are saying running the code with Web Matrix produces different results. I am looking to have you isolate exactly why the results are different.|||ok. I'll take a look. Thanks for being so quick.|||I'm not sure if I'm passing the parameters correctly to the procedure findtitle on the line:

strsql = "EXECUTE findtitle ' " & txt1.text & " ' "

I say that they give me different results because when I copy the select statement from the procedure into the SQL analyzer I subtitute the variabe @.btitle for a word such as 'database'. And then, when I execute the web page I insert in the textbox the word database.

am I doing something wrong?

Thanks

Different procedures for SQL 2000 and 2005

I've got a stored procedure that now needs to be a little bit
different when my db has been installed on SQL 2000 vs SQL 2005. It's
created as part of the larger install script and my first thought was
that I keep a single install script and the script creates one or the
other version of the procedure depending on the SQL version that it's
being run on.
But when I try something like this:
if cast(serverproperty('productversion') as varchar(1)) = '8' --
8=2000 9=2005
create procedure xyz as
begin
set nocount on
-- SQL 2000 version
end
else
create procedure xyz as
begin
set nocount on
-- SQL 2005 version
end
Obviously it's not going to work; I get these errors from QA on SQL
2000, with pretty much the same from SSMS on 2005:
Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'procedure'.
Server: Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'else'.
Server: Msg 111, Level 15, State 1, Line 8
'CREATE PROCEDURE' must be the first statement in a query batch.
Can I do this? Or do I have to have two separate scripts, one for
2000 another for 2005 (and next year maybe a third script for 2008),
that are almost identical? Or use dynamic sql inside the if and else
to perform the creates (yuck) ? Or ...?
I've tried googling for how to handle this scenario, but no joy .
Can't believe that I'm the only one to encounter a multi-version
supporting need. How's this usually handled?
Thanks!Hi Mark
You will need to make the procedure definition dynamic SQL
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].
1;xyz]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[xyz]
IF LEFT(CAST(serverproperty('productversion
') AS VARCHAR(128)),
CHARINDEX('.',CAST(serverproperty('productversion') AS VARCHAR(128)))-1) = '
8'
BEGIN
EXEC ( '
create procedure xyz as
begin
set nocount on
-- SQL 2000 version
SELECT ''SQL 2000''
end
' )
END
ELSE
BEGIN
EXEC ( '
create procedure xyz as
begin
set nocount on
-- SQL 2005 version
SELECT ''SQL 2005''
end
' )
END
If you were using version control it would not be an issue to provide two
different sets of scripts and then you would only need to check the version
in the installer once.
John
"Mark Lemoine" wrote:

> I've got a stored procedure that now needs to be a little bit
> different when my db has been installed on SQL 2000 vs SQL 2005. It's
> created as part of the larger install script and my first thought was
> that I keep a single install script and the script creates one or the
> other version of the procedure depending on the SQL version that it's
> being run on.
> But when I try something like this:
> if cast(serverproperty('productversion') as varchar(1)) = '8' --
> 8=2000 9=2005
> create procedure xyz as
> begin
> set nocount on
> -- SQL 2000 version
> end
> else
> create procedure xyz as
> begin
> set nocount on
> -- SQL 2005 version
> end
> Obviously it's not going to work; I get these errors from QA on SQL
> 2000, with pretty much the same from SSMS on 2005:
> Server: Msg 156, Level 15, State 1, Line 2
> Incorrect syntax near the keyword 'procedure'.
> Server: Msg 156, Level 15, State 1, Line 7
> Incorrect syntax near the keyword 'else'.
> Server: Msg 111, Level 15, State 1, Line 8
> 'CREATE PROCEDURE' must be the first statement in a query batch.
> Can I do this? Or do I have to have two separate scripts, one for
> 2000 another for 2005 (and next year maybe a third script for 2008),
> that are almost identical? Or use dynamic sql inside the if and else
> to perform the creates (yuck) ? Or ...?
> I've tried googling for how to handle this scenario, but no joy .
> Can't believe that I'm the only one to encounter a multi-version
> supporting need. How's this usually handled?
> Thanks!
>

Different procedures for SQL 2000 and 2005

I've got a stored procedure that now needs to be a little bit
different when my db has been installed on SQL 2000 vs SQL 2005. It's
created as part of the larger install script and my first thought was
that I keep a single install script and the script creates one or the
other version of the procedure depending on the SQL version that it's
being run on.
But when I try something like this:
if cast(serverproperty('productversion') as varchar(1)) = '8' --
8=2000 9=2005
create procedure xyz as
begin
set nocount on
-- SQL 2000 version
end
else
create procedure xyz as
begin
set nocount on
-- SQL 2005 version
end
Obviously it's not going to work; I get these errors from QA on SQL
2000, with pretty much the same from SSMS on 2005:
Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'procedure'.
Server: Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'else'.
Server: Msg 111, Level 15, State 1, Line 8
'CREATE PROCEDURE' must be the first statement in a query batch.
Can I do this? Or do I have to have two separate scripts, one for
2000 another for 2005 (and next year maybe a third script for 2008),
that are almost identical? Or use dynamic sql inside the if and else
to perform the creates (yuck) ? Or ...?
I've tried googling for how to handle this scenario, but no joy :(.
Can't believe that I'm the only one to encounter a multi-version
supporting need. How's this usually handled?
Thanks!Hi Mark
You will need to make the procedure definition dynamic SQL
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[xyz]')
and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[xyz]
IF LEFT(CAST(serverproperty('productversion') AS VARCHAR(128)),
CHARINDEX('.',CAST(serverproperty('productversion') AS VARCHAR(128)))-1) = '8'
BEGIN
EXEC ( '
create procedure xyz as
begin
set nocount on
-- SQL 2000 version
SELECT ''SQL 2000''
end
' )
END
ELSE
BEGIN
EXEC ( '
create procedure xyz as
begin
set nocount on
-- SQL 2005 version
SELECT ''SQL 2005''
end
' )
END
If you were using version control it would not be an issue to provide two
different sets of scripts and then you would only need to check the version
in the installer once.
John
"Mark Lemoine" wrote:
> I've got a stored procedure that now needs to be a little bit
> different when my db has been installed on SQL 2000 vs SQL 2005. It's
> created as part of the larger install script and my first thought was
> that I keep a single install script and the script creates one or the
> other version of the procedure depending on the SQL version that it's
> being run on.
> But when I try something like this:
> if cast(serverproperty('productversion') as varchar(1)) = '8' --
> 8=2000 9=2005
> create procedure xyz as
> begin
> set nocount on
> -- SQL 2000 version
> end
> else
> create procedure xyz as
> begin
> set nocount on
> -- SQL 2005 version
> end
> Obviously it's not going to work; I get these errors from QA on SQL
> 2000, with pretty much the same from SSMS on 2005:
> Server: Msg 156, Level 15, State 1, Line 2
> Incorrect syntax near the keyword 'procedure'.
> Server: Msg 156, Level 15, State 1, Line 7
> Incorrect syntax near the keyword 'else'.
> Server: Msg 111, Level 15, State 1, Line 8
> 'CREATE PROCEDURE' must be the first statement in a query batch.
> Can I do this? Or do I have to have two separate scripts, one for
> 2000 another for 2005 (and next year maybe a third script for 2008),
> that are almost identical? Or use dynamic sql inside the if and else
> to perform the creates (yuck) ? Or ...?
> I've tried googling for how to handle this scenario, but no joy :(.
> Can't believe that I'm the only one to encounter a multi-version
> supporting need. How's this usually handled?
> Thanks!
>

Different Default Date format for 2 SQL Server Instances

Hi,
We recently had a new environment created. The servers were all installed
as separate instances on the same physical machine.
For my first instance INST1
When I execute the following query exec getMyData '1964-11-19'
in query analyser everything is fine
from my (ASP) website everything is fine
For my second instance INST2
in query analyser everything is fine
from my asp website I get varchar cannot be converted to datetime.
I can only believe that the default settings for the server were different
when each of the SQL Server Installs were performed.
I cannot change the way we pass dates in our website because it is a massive
re-write of everytihg if I do.
Is there some why of changing the default settings of the server after
installing?
I have tried using
sp_configure
SET language
sp_defaultlanguage
and all these methods did not fix my problem.
Any ideas?
JayneK wrote:
> Hi,
> We recently had a new environment created. The servers were all
> installed as separate instances on the same physical machine.
> For my first instance INST1
> When I execute the following query exec getMyData '1964-11-19'
> in query analyser everything is fine
> from my (ASP) website everything is fine
> For my second instance INST2
> in query analyser everything is fine
> from my asp website I get varchar cannot be converted to datetime.
> I can only believe that the default settings for the server were
> different when each of the SQL Server Installs were performed.
> I cannot change the way we pass dates in our website because it is a
> massive re-write of everytihg if I do.
> Is there some why of changing the default settings of the server after
> installing?
> I have tried using
> sp_configure
> SET language
> sp_defaultlanguage
> and all these methods did not fix my problem.
> Any ideas?
When working with dates in character format, you should only ever use a
portable format. Two formats are supported that will never cause
problems related to the server's regional settings:
yyyy-mm-ddThh:mm:ss.mmm (no spaces)
yyyymmdd
What is probably occurring is that one server is using MDY format and
the other is using DMY.
For example:
SET NOCOUNT ON
SET DATEFORMAT MDY
SELECT CAST('1964-11-19' as DATETIME)
SET DATEFORMAT DMY
SELECT CAST('1964-11-19' as DATETIME)
-- Results
1964-11-19 00:00:00.000
Server: Msg 242, Level 16, State 3, Line 9
The conversion of a char data type to a datetime data type resulted in
an out-of-range datetime value.
David Gugick - SQL Server MVP
Quest Software
|||David,
Unfortunately they all have all their language setting exactly the same. So
they are all set to us_english as default language, yet one server acts
differently to the other.
How can we fix this? do we have to uninstall and re-install, can't we hack
a file or something?
"David Gugick" wrote:

> JayneK wrote:
> When working with dates in character format, you should only ever use a
> portable format. Two formats are supported that will never cause
> problems related to the server's regional settings:
> yyyy-mm-ddThh:mm:ss.mmm (no spaces)
> yyyymmdd
>
> What is probably occurring is that one server is using MDY format and
> the other is using DMY.
> For example:
> SET NOCOUNT ON
> SET DATEFORMAT MDY
> SELECT CAST('1964-11-19' as DATETIME)
> SET DATEFORMAT DMY
> SELECT CAST('1964-11-19' as DATETIME)
> -- Results
> 1964-11-19 00:00:00.000
> Server: Msg 242, Level 16, State 3, Line 9
> The conversion of a char data type to a datetime data type resulted in
> an out-of-range datetime value.
>
>
> --
> David Gugick - SQL Server MVP
> Quest Software
>
|||JayneK wrote:[vbcol=seagreen]
> David,
> Unfortunately they all have all their language setting exactly the
> same. So they are all set to us_english as default language, yet one
> server acts differently to the other.
> How can we fix this? do we have to uninstall and re-install, can't
> we hack a file or something?
> "David Gugick" wrote:
The ASP web site client is likely set up different. Go to that PC, open
up QA, and run the example above. The problem is that you are not using
a portable date format and are bound to run into these types of
problems. If you get rid of the hyphens in the date parameter, that will
probably fix the issue. Since you cannot easily change the code
executing the date procedure you wrote, why not just change the
procedure itself to strip the hyphens out using Set @.MyDate =
REPLACE(@.MyDate, '-', '')
David Gugick - SQL Server MVP
Quest Software

Different behavior between VS and IE in rendering reports with fixed headers and a document map

I apologize in advance if this is something already addressed elsewhere (if so - please point me in that direction).

I have created a really simple report that allows 'drill-down' at the group level. To this report I also selected the 'Repeat header rows on each page' and 'Header should remain visible while scrolling' features and all worked as expected after deploying.

I then added a Document Map referencing the group level and that too worked within the VS environment. VS displayed a page that contained the requested group in plain view and I could 'drill down' as expected.

But when I deployed the report and subsequently pulled it up in IE I noticed a slightly different behavior in that when selecting an entry from the document map, the start of that group got hidden behind the fixed header such that what was visible was perhaps 1 or 2 group records down. I had to scroll up to see the start of the group I had selected from the document map and then drill down from there.

Removing both the "Repeat header..." and "Header should remain ...." allows me to select a group from the document map (which is always at the top) - but in doing so I lose the fixed header feature.

Does anyone have any ideas what I am doing wrong?

Bob

I am experiencing this same problem as well. I would also like to know if there is a fix or workaround to this.|||I also have an issue with a similar setup. The report works well in VS 05, after deplying the document map displays a short list of entries, I'm unable to navigate to the bottom of the document map listing and I cannot "type ahead" as in VS environment.

Different behavior between VS and IE in rendering reports with fixed headers and a document map

I apologize in advance if this is something already addressed elsewhere (if so - please point me in that direction).

I have created a really simple report that allows 'drill-down' at the group level. To this report I also selected the 'Repeat header rows on each page' and 'Header should remain visible while scrolling' features and all worked as expected after deploying.

I then added a Document Map referencing the group level and that too worked within the VS environment. VS displayed a page that contained the requested group in plain view and I could 'drill down' as expected.

But when I deployed the report and subsequently pulled it up in IE I noticed a slightly different behavior in that when selecting an entry from the document map, the start of that group got hidden behind the fixed header such that what was visible was perhaps 1 or 2 group records down. I had to scroll up to see the start of the group I had selected from the document map and then drill down from there.

Removing both the "Repeat header..." and "Header should remain ...." allows me to select a group from the document map (which is always at the top) - but in doing so I lose the fixed header feature.

Does anyone have any ideas what I am doing wrong?

Bob

I am experiencing this same problem as well. I would also like to know if there is a fix or workaround to this.