SQL 2005: Creating Correct And Incorrect Versions Of A Database

Jun 20, 2006

I work at a place which is currently running SQL 2000, but they are
planning to migrate to 2k5. I was thinking that this is the perfect
opportunity to fix all the weaknesses we have had in our data model for
the longest: primary keys and foreign keys with different names, use of
character columns for boolean fields, use of integer columns for
toggles, no referential integrity, etc.

So, even if I create my Utopian perfect data model and modify all of
our data loaders to use it, our live website must use the old incorrect
version because there is way too much work involved in redoing the
code.

My question then becomes: if I have a correct version, how easy and
with what approach would one take the data in the correct one and
mirror it to the poorly designed schema?

View 4 Replies


ADVERTISEMENT

Using SET To Correct Integers In A Dbo.Table: Incorrect Sysntax Near The Keyword ‘SET’?

Jan 31, 2008

Hi all,

I have a wrong €œdbo.Samples€? table:
SampleID SampleName Matrix SampleType ChemGroup ProjectID
1 Blueriver01 Soil QA VOCs 1
7 Greentree01 Water Primary VOCs 1
8 Greentree02 Water Duplicate VOCs 1
9 Greentree03 Water QA VOCs 2
10 Greentree11 Soil Primary VOCs 1
11 Greentree11 Soil Duplicate VOCs 1
12 Greentree11 Soil QA VOCs 3
13 Redrock01 Water Primary VOCs 1
14 Redrock02 Water Duplicate VOCs 1
15 Redrock03 Water QA VOCs 2
16 Redrock11 Soil Primary VOCs 1
17 Redrock12 Soil Duplicate VOCs 1
18 Redrock13 Soil QA VOCs 3



I used the following sql code to correct the wrong ProjectIds:

USE ChemDatabase
GO
ALTER TABLE Samples
SET ProjectID = 4 WHERE SampleID = 7
SET ProjectID = 4 WHERE SampleID = 8
SET ProjectID = 5 WHERE SampleID = 9
SET ProjectID = 4 WHERE SampleID = 10
SET ProjectID = 4 WHERE SampleID = 11
SET ProjectID = 6 WHERE SampleID = 12
SET ProjectID = 7 WHERE SampleID = 13
SET ProjectID = 7 WHERE SampleID = 14
SET ProjectID = 8 WHERE SampleID = 15
SET ProjectID = 7 WHERE SampleID = 16
SET ProjectID = 7 WHERE SampleID = 17
SET ProjectID = 9 WHERE SampleID = 18
GO


I got the following error message:
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'SET'.


Please help and tell me what it is the right syntax for my €˜SET€™
used in this sql code. I think there are more mistakes in this set of sql code. Please enlighten me and advise me how to make this set of code right.


Thanks,
Scott Chang

View 4 Replies View Related

Correct Syntax For Parameters When Creating SQL Job

Dec 30, 2007

I'm trying to create a SQL job in SQL Server and am a little unclear about the formatting.Here's a snippet from the stored procedure that creates the job:CREATE PROCEDURE [dbo].[spArchive]     @DB            varchar(30),    @Date        DateTimeAS EXEC msdb.dbo.sp_add_jobstep @job_name = 'ArchiveIncentives'     , @step_id = 1    , @step_name = 'ArchiveAHD'    , @subsystem = 'TSQL'    , @command = 'spArchiveAHD ''@Date'''    , @on_success_action = 3     , @on_fail_action = 2     , @database_name = '@DB'     , @retry_attempts = 1   In this case, the job will be calling this stored procedure:CREATE PROCEDURE [dbo].[spArchiveAHD] (    @dtArchiveBefore DateTime)AS I'm unclear about these lines:    @command = 'spArchiveAHD ''@Date'''    @database_name = '@DB'   Do they look correct to you or should I drop some/all of the apostrophes?Robert W. 

View 5 Replies View Related

Incorrect Syntax Near The Keyword CONVERT When The Syntax Is Correct - Why?

May 20, 2008

Why does the following call to a stored procedure get me this error:


Msg 156, Level 15, State 1, Line 1

Incorrect syntax near the keyword 'CONVERT'.




Code Snippet

EXECUTE OpenInvoiceItemSP_RAM CONVERT(DATETIME,'01-01-2008'), CONVERT(DATETIME,'04/30/2008') , 1,'81350'




The stored procedure accepts two datetime parameters, followed by an INT and a varchar(10) in that order.

I can't find anything wrong in the syntax for CONVERT or any nearby items.


Help me please. Thank you.

View 7 Replies View Related

Incorrect Syntax Near 'XML'-While Creating XML Schema

Jul 3, 2007

 Hi,       Im trying to create a xml schema like     CREATE XML SCHEMA COLLECTION BooksSchemaCollection ASN'<?xml version="1.0" encoding="UTF-16"?><xsd:schema elementFormDefault="unqualified"   attributeFormDefault="unqualified"   xmlns:xsd="http://www.w3.org/2001/XMLSchema" >    <xsd:element name="book">        <xsd:complexType mixed="false">            <xsd:sequence>                <xsd:element name="name" type="xsd:string"/>                <xsd:element name="author" type="xsd:string"/>                <xsd:element name="publisher" type="xsd:string"/>                <xsd:element name="cost" type="xsd:integer"/>                <xsd:element name="comments" type="xsd:string"/>            </xsd:sequence>                              </xsd:complexType>    </xsd:element></xsd:schema>';  

But when i execute , im getting a error  like Incorrect syntax near 'XML' .any one know why its comming?????? Thanks 

View 1 Replies View Related

Error Creating Table - Incorrect Syntax

May 27, 2014

Have been given this code to create a table

CREATE TABLE 'emaillist' (
'id' INT(11) NOT NULL AUTO_INCREMENT,
'clientname' VARCHAR(200) NOT NULL DEFAULT '0',
'email' VARCHAR(200) NOT NULL DEFAULT '0',
PRIMARY KEY ('id')
);

when I execute it says

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'emaillist'.

What the correct code should be?

View 3 Replies View Related

T-SQL Error In Creating A Stored Procedure That Has Three Parameters: Incorrect Syntax Near 'WHERE'

Feb 17, 2008

Hi all,

I copied the the following code from a book to the query editor of my SQL Server Management Studio Express (SSMSE):
///--MuCh14spInvTotal3.sql--///
USE AP --AP Database is installed in the SSMSE--
GO
CREATE PROC spInvTotal3
@InvTotal money OUTPUT,
@DateVar smalldatetime = NULL,
@VendorVar varchar(40) = '%'
AS

IF @DateVar IS NULL
SELECT @DateVar = MIN(InvoiceDate)

SELECT @InvTotal = SUM(InvoiceTotal)
FROM Invoices JOIN Vendors
WHERE (InvoiceDate >= @DateVar) AND
(VendorName LIKE @VendorVar)
GO
///////////////////////////////////////////////////////////////
Then I executed it and I got the following error:
Msg 156, Level 15, State 1, Procedure spInvTotal3, Line 12
Incorrect syntax near the keyword 'WHERE'.
I do not know what wrong with it and how to correct this problem.

Please help and advise.

Thanks,
Scott Chang

View 18 Replies View Related

Sql Server 2005 Enterprise Versions

Apr 26, 2006

Hi,

I would like to know what are the differences between these versions:







SQL Svr Enterprise Edtn 2005 Win32 English MVL

SQL Svr Enterprise Edtn 2005 Win32 English MVL 1 Processor License

SQL Svr Enterprise Edtn Win32 English Lic/SA Pack MVL

SQL Svr Enterprise Edtn Win32 English Lic/SA Pack MVL 1 Processor License

SQL Svr Enterprise Edtn Win32 English Lic/SA Pack MVL Comp Promo

SQL Svr Enterprise Edtn Win32 English SA MVL

SQL Svr Enterprise Edtn Win32 English SA MVL 1 Processor License

SQL Svr Enterprise Edtn Win32 English SA Step Up MVL

SQL Svr Enterprise Edtn Win32 English SA Step Up MVL 1 Processor License



Regards



Ayzan

View 1 Replies View Related

How To Backup, Or Store Different Versions Of A Database?

Aug 11, 2006

Hello,
I am working on a project. We do not have a DBA, so  I am performing some of these tasks.
I recently created a new database with about 10 tables. I have set up the key and primary key relationships between the data files.
How do I save the database through differenct stages of it's evolution? Suppose I populate the database, but later on decide that I we need to re-start from scratch and go back to the new database I created that is not populated. How do I save different versions of a database through it's evolution/progression?
Thanks in advance, Bill

View 1 Replies View Related

Creating New SQL Server 2005 Database

Feb 10, 2008

Hello all,
I am new to MS SQL Server and am having troubles figuring out how to create a new database file. I have installed everything I can and and still cannot find any where to add a database file. I am upgrading from Access to SQL and feel like I am missing something... I will eventually need to create a couple of SQL databases and connect from Classic ASP and ASP.net in different applications. Before I worry about how to connect I need ot create something to connect to.

Any help would be greatly appreciated.
Thanks
Panhead

View 1 Replies View Related

Multiple Versions Of SQL Server 2005 - What Order To Install?

Jun 15, 2007

I currently have three SQL Server instances installed on my notebook. One is v8, which I believe is used by an accounting application. The other two are as follows:-

SQL Server MSSMLBIZ

SQL Server SQLEXPRESS



The second one (SQLEXPRESS) fails to start generating the following error:-

The SQL Server (SQLEXPRESS) service terminated with service-specific error 17058 (0x42A2).



Other services listed in SQL Server Configuration Manager are:-

SQL Server Integration Services - which runs apparently normally.

SQL Server Analysis Services (MSSQLSERVER)

SQL Server Reporting Services (MSSQLSERVER)

SQL Server Browser



The last thre of these are run under LocalSystem.



I have been trying to install the SQLServer SP2 to the SQLEXPRESS instance for some number of days now, but each time it runs, it fails to install.



I am beginning to wonder if there is a sequence of installation for each of the above instances that should be followed. Can anyone give me any suggestions, or answers to the above problems?



Thanks,

View 3 Replies View Related

Creating A Database In SQL 2005 - Using Windows 2003

Nov 3, 2006

when i try to create a new database i get a error: The server could notload DCOMwhat is wrong and how can i fix it?i have updated mssql/win 2003 with the lates updatesRgdsBobby

View 1 Replies View Related

Creating A Unicode SQL Server 2005 Database

Nov 14, 2007

Hi,

We have recently been looking into creating our SQL Server 2005 database as a unicode database using UTF-8 encoding. However initial search's suggest that SQL Server 2005 does not support the creation of UTF-8 db's and only supports UCS-2. Is this the case? If so then why is it not supported as most other vendors (Oracle, IBM) support it. If it is possible to create a UTF-8 database could you provide some details on how to go about this?

Thanks,
Cathal

View 1 Replies View Related

Versions Of Windows Allowed For SQL Server 2005 Developer Edition

Jul 8, 2007

Hi, I am going to get a new laptop and was wondering in particular which versions of Windows Vista will run SQL Server 2005 Developer Edition. I want to be able to use all the features of SQL Server 2005, so IIS is needed for example ( I will be building end to end BI soutions using SQL Server Management Studio, SSIS, Analysis Services and Reporting Services.


Chris

View 13 Replies View Related

Visual Studio 2005 Shows Wrong SQL Server Versions

Apr 2, 2007

Visual Studio 2005 shows SQL Server 2005 installed product versions differently than the actual. As seen below VS2005 displays 9.00.3042.00 as the version of SQL Server 2005 however it is 9.00.3050.00 in SQL Server Management Studio. [Look at Installed Products section after selecting About MS Visual Studio from Help menu ]



SQL Server Analysis Services
Microsoft SQL Server Analysis Services Designer
Version 9.00.3042.00

SQL Server Integration Services
Microsoft SQL Server Integration Services Designer
Version 9.00.3042.00

SQL Server Reporting Services
Microsoft SQL Server Reporting Services Designers
Version 9.00.3042.00



I have VS2005 SP1 and SQL2005 SP2 installed. Why VS2005 reports versions wrong?

View 1 Replies View Related

How To Start Creating DataBase Diagram In SqlServer 2005?

Feb 29, 2008

HI,
How can i start to create database diagram in Sqlserver 2005.
Please provide any URL(s).
 
Thanks and Regards,
Rafeeq

View 1 Replies View Related

SQL Server 2005: Question About Creating A User For A Database

Dec 12, 2005

I'm not a DBA, so I don't know where to look for information on this.

In SQL Server 2005, I can simply no longer create a user for the database I created. It asks that the user be associated with a login. That login is found in the Security folder of the database instance.

The question is: what is this instance security folder for? I don't recall having to do this in SQL Server 2000.

Any elaboration or links to such information would be appreciated.

View 3 Replies View Related

Problems Creating Database On SQL Srvr. 2005 Ent. Edtn.

Dec 17, 2006

Posted - 12/17/2006 : 07:23:22



Hi,

I have just started to learn SQL Server 2005, and am currently using the MCTS SQL Server 2005 Self-Paced Training Kit. I am literally at the very start, and already seem to have hit a snag and can't figure it out. From browsing here in the past I recall people here seemed quite clued up, and I was hoping someone might be able to assist.

I am using SQL Server 2005 Ent. Edtn. (trial) on Win XP Pro (SP2). I am basically trying to run a sample script copied from book to create a database and primary, secondary and log files. Whilst I have the theory down, and thought I understood the SQL (have used other DBMSs in the past, MySQL/ACCESS etc.) I am getting an error when trying to run the following code when connected to the default instance on my PC. The code I am trying to run is:


create database Sales

ON

primary

(name = SalesPrimary,

filename = E:Sales_DataSalesPrimary.mdf,

size = 50mb,

MAXSIZE = 200,

filegrowth = 20),

Filegroup SalesFG

( name = SalesData1,

filename = F:Sales_DataSalesData1.ndf,

size = 200mb,

maxsize = 800,

filegrowth = 100),

( name = SalesData2,

Filename = F:Sales_DataSalesData2.ndf,

Size = 400mb,

Maxsize = 1200,

Filegrowth = 300),

Filegroup SalesHistoryFG

( name = SalesHistory1,

filename = F:Sales_DataSalesHistory1.ndf,

size = 100mb,

maxsize = 500,

filegrowth = 50)

LOG ON

(name = Archlog1,

Filename = G:Sales_DataSalesLog.ldf,

size = 300mb,

maxsize = 800,

filegrowth = 100)

I find I get the following error message:

Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'E'.
Msg 132, Level 15, State 1, Line 16
The label 'F' has already been declared. Label names must be unique within a query batch or stored procedure.
Msg 132, Level 15, State 1, Line 22
The label 'F' has already been declared. Label names must be unique within a query batch or stored procedure.

Anyone have any ideas? Your help is greatly appreciated!

Thanks,

Will

View 8 Replies View Related

Attn Microsoft : Incorrect Screenshot - SQL Server Integration Services (SSIS) Hands On Training - Creating Custom Components

Jun 25, 2006

Hi All,

I am not sure if this is a correct forum to discuss on the document posted @ http://www.microsoft.com/downloads/details.aspx?familyid=1c2a7dd2-3ec3-4641-9407-a5a337bea7d3&displaylang=en on SQL Server Integration Services (SSIS) Hands on Training - Creating Custom Components.

I am assuming Microsoft Developers are constantly monitoring this forum.

In the document - SSIS Creating a Custom Transformation Component .doc on Page 2 -
Exercise 1 - Writing the no-op data flow transformation component -
Task 1 - Create a new C# Class Library Project

The textual description talks about creating a new Visual C# Class Library project in VS 2005 but the screenshot accompanying it shows the creation of new "Integration Service Project" in VS 2005.

Please change the screenshot appropriately to avoid confusions.

Thanks,
Loonysan

View 1 Replies View Related

Correct Setup For SQL Server 2005 For Web Project!

Oct 11, 2007

Ive been trying to get some type of Blogpost tutorial Etc on how to set up SQL Server 2005 to serve data to a website1 How do I setup users?    a) Can I have 3 roles?       1a) Owner of DB can read/write       2a) reader Can Only read from database      3a) Writer. Can only write to database How would I set this up? How can I call all these from ASP.NET depending on what the user is currently using on the website?    eg:       Just serving pages with content (reader)      Forms (writter)      admin (owner)I also need to have the SQL keep sessions (Ive already ran aspnet_reqSQL.exe) and created all that im just unsure what type user can access all thisAny tutorials on how to set up a whole WEb application project from DB to VS 2005? Thanks         

View 3 Replies View Related

Issues With Microsoft SQL Serever 2005 Express Edition And Other Versions Of SQL Express

Feb 20, 2007

I have uninstalled the SQL Server Express Edition that I have installed from the CDs that were given to me during a Chicago Conference when READY TO  LAUNCH Visual Studio 2005, SQL SERVER 2005, and Biz Talk 2005.
Then I went to microsoft website: http://msdn.microsoft.com/vstudio/express/sql/register/default.aspx and downloaded and installed the so called Microsoft SQL Server 2005 Express Edition and I got the messages Error that you read below. Then I Uninstalled Microsoft SQL Server 2005 Express Edition  and went again to msdn website and downloaded Microsoft SQL Server 2005 Express Edition Advanced Services SP1 and installed it. I got again the same message as below.
 
MESSAGE:
 
1. First comes a window with the title: €œsetup.exe €“ Unable to Locate Component€?
And it displays a message:
This application has failed to start because MSTDCPRX.dll was not found.
Re-installing the application may fix this problem.
2. After I click the OK button of this window it comes another window with the title: €œMicrosoft SQL Server 2005 Server Setup€?
And it displays a message: Failed to load SqlSpars.dll
 
 
Does anybody can tell what is going on with the 3 times I tried to installed different SQL Server 2005 Express Edition and I get the same message?????
 
Thanks for your help and support when you have time to respond.
Sincerely,
TonyC
 
MORE  INFORMATION ON THE:
C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGFiles
 
1. C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGFilesSQLSetup0001_B3-XP_Core.txt
Error: Action "LaunchLocalBootstrapAction" threw an exception during execution.  Error information reported during run:
"C:Program FilesMicrosoft SQL Server90Setup Bootstrapsetup.exe" finished and returned: 87
Aborting queue processing as nested installer has completed
Message pump returning: 87
 
2. C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGFilesSQLSetup0001_B3-XP_Core(Local).txt Running: InvokeSqlSetupDllAction at: 2007/1/20 1:22:8
Error: Action "InvokeSqlSetupDllAction" threw an exception during execution.
Unable to load setup helper module : 87
Message displayed to user
     Failed to load SqlSpars.dll
Error: Failed to add file :"C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGFilesSQLSetup0001_B3-XP_.NET Framework 2.0.log" to cab file : "C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGSqlSetup0001.cab" Error Code : 2
Running: UploadDrWatsonLogAction at: 2007/1/20 1:22:17
Message pump returning: 87
 
3. C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGFilesSQLSetup0001_B3-XP_Datastore.xml
 <
 <S,<Scope Type="SetupStateScope" Id="">
      <Property Id="machineName">B3-XP</Property>
      <Property Id="logDirectory">C:Program FilesMicrosoft SQL Server90Setup BootstrapLOG</Property>
    <Property Id="logSummaryFilename">C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGSummary.txt</Property>
      <Property Id="logSequenceNumber">1</Property>
      <Property Id="primaryLogFiles">{ ["C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGFilesSQLSetup0001_B3-XP_.NET Framework 2.0.log"], ["C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGFilesSQLSetup0001_B3-XP_Support.log"], ["C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGFilesSQLSetup0001_B3-XP_Core.log"], ["C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGSummary.txt"], ["C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGFilesSQLSetup0001_B3-XP_Core(Local).log"] }</Property>
      <Property Id="watsonFailedAction">InvokeSqlSetupDllAction</Property>
      <Property Id="watsonFailedActionErrorCode">87</Property>
      <Property Id="watsonFailedFunction">sqls::InvokeSqlSetupDllAction::perform</Property>
    <Property Id="watsonFailedFunctionErrorCode">87</Property>
      <Property Id="watsonSourceFileAndLineNo">setupsqlsetupactions.cpp@1709</Property>
      <Property Id="watsonModuleAndVersion">setup.exe@2005.90.3042.0</Property>
      <Property Id="watsonMsi">None</Property>
      <Property Id="watsonMsiAndVersion">None</Property>
      <Property Id="watsonSourceFile">setupsqlsetupactions.cpp</Property>
</<Scope> 
 
 
 
 
 
 

View 9 Replies View Related

Correct Way To Attach Database When Uploading.

Dec 26, 2007

hello,
After I FTP a ASP.NET website to a webserver,  APP_DATA directory and all,   I then use the SQL publishing wizard to create a script on the local machine and run the script on the webserver/SQL server, it builds ok.  But I get an error "another database with the same name".  Duplicate of the database in APP_DATA directory that is already attached by the connection string.  So this isn't correct.
Second attempt:  I FTP an ASP.NET website up to a server.  Then I open the SQL management studio and attach the database in the website's APP_DATA folder.  (never running a .sql script)The database attaches.  The name of the database is the complete path to the database in the APP_DATA directory by default.  The website functions.  But if I try to look at a table I get an errors.  It gets to the point the database will not even expand.  Then I start getting errors in the browser that NETWORK SERVICE can not open it's default database.  So the website fails also.  So this doesn't work either....
 So I guess my question is, should I remapped the path of |DataDirectory| before I FTP it?  To a path where the other common system databases reside,  (I don't think that would be necessary.)    Then FTP the website without the contents of the app folder(no database).  Then use the script built with the SQL Publishing wizard to build the database,  and attach the database in SQL Management Studio?  ( I'm afraid of over playing and corrupting the server).
If I attach the database to manage it and the connection string attaches the database to run it,  will I always get errors?  What am I missing here?   
Thanks jamesqua for your help so far,  I understood the blog you sent me too.  But how do I modify it so I can use SQL Management studio to manage the database?  
Once again from My Uncle Bob "Things are simply awful,  or awfully simple"
-KK

View 5 Replies View Related

Transact SQL :: Want To Correct Database Design

Apr 29, 2015

I have below database already on one of the environment and its surprisingly designed somewhat in the past.now I want to correct it with one default filegroup with one primary and one log file, same time i am concerned for data as its production and no test environment is there, any way which ensure full consistency and steps i need to do...

CREATE
DATABASE [Sample]
ON 
PRIMARY
(
NAME =
N'Sample_Data',

[code]....

View 11 Replies View Related

Correct Way To Create Indices? SQLServer Express 2005

Feb 22, 2006

Hi,
Apologies if this has been asked before, i've done a search but can't find a definitive answer.
I've created a table in an SQLExpress 2005 db using Server Managment Studio Express.
 My intention is to use GUID fields as surrogate PK's. I therefore wanted to add a additional index to prevent duplicate records being added to the table. Not having used SQLServer before could someone confirm or deny that this is the correct way to do this. The PK field [EPISODEID{unique identifier}] is set as a non-clustered index. And i've created a second clustered index using the two fields that create a unique record. I've added a screen shot if that is any help.

Thanks
Chris
 
 
 
 

View 2 Replies View Related

Please Correct This Error While I Retriving Image From Database

Sep 4, 2007

private void Page_Load(object sender, System.EventArgs e){    // Put user code to initialize the page here    MemoryStream stream = new MemoryStream ();    SqlConnection connection = new       SqlConnection (@"...");    try    {        connection.Open ();        SqlCommand command = new           SqlCommand ("select Picture from Image", connection);        byte[] image = (byte[]) command.ExecuteScalar ();           stream.Write (image, 0, image.Length);        Bitmap bitmap = new Bitmap (stream);        Response.ContentType = "image/gif";        bitmap.Save (Response.OutputStream, ImageFormat.Gif);    }     finally    {        connection.Close ();        stream.Close ();    }
Error:       byte[] image = (byte[]) command.ExecuteScalar ();   
Unable to cast object of type 'System.Int32' to type 'System.Byte[]'.

View 1 Replies View Related

Problem With Creating New SQL Server Database In VWDExpress Using SQL Server 2005 Express

Jul 1, 2006



I have two problems on creating new SQL server database in Visual Web Developer 2005 Express using SQL server 2005 Express.

The 1st problem: (It is resolved)

After installed SQL Server 2005 express and Visual Web Developer 2005 Express. When I tried to create a SQL server database in VWD the following error occurred:

"connection to SQL Server files (*.mdf) require SQL server express 2005 to function properly. please verify the installation of the component or download from the URL: go.microsoft.com/fwlink/?linkId=49251"

I was stuck with this problem for few days. I did several times of install/reinstall of VWPExpress and SQL server Express, made very sure following proper de-installation sequence as specified in the release readme. But it did not resolve the problem.

Latter I found out that if I login with another Windows XP user account, I don't get the problem.

Changing the security authority of XP file systems and IIS didn't help.

And to prove that I was doing the installation correctly, I tried a new installation of the VWDExpress with SQL server 2005 express in another machine, it worked and I could create new empty SQL database.

After 2 days of frustrating changing of options, parameters, server names, messing with the registry etc.... Finally, I found a way to overcome this problem.

The solution is:

In Visual Web Developer 2005 Express, select:

Tools -> Options -> Database Tools -> Data Connections,

Change the setting: "SQL Server Instance Name (blank for default)" to "SQLEXPRESS". (where SQLEXPRESS is the default server instance name)

That solved this 1st problem.

I still don't know the exact reason for this problem. But I recalled I did installed the VWDExpress few weeks ago, I might have installed it without selecting SQL server 2005 express at that point. I guess the subsequent install/re-install did not clean up those junk in the registry.

I still have the 2nd problem:

The "Create new SQL database..." option is always grayed out when I right click on the Data connections in the "Database Explorer" tab.

I have a bypass for this difficulty for time being:

I have to do it by using the "Add connection....", put in the server name: "myserver/SQLEXPRESS" and a new database name.

Any input form anyone ? Appreciated!

View 3 Replies View Related

Error Creating First SQL Express Database Via VWD 2005 Express: User Does Not Have Permission To Perform This Action

Aug 18, 2006

I get an error dialog when I try to create a new SQL database, both via the Add New Item dialog and the property wizard of a new SqlDataSource control. The error is:


Local Database File:

User does not have permission to perform this action.

I've searched for help with this.

I ensured the App_Data folder exists and I added the local ASP.NET account to the group that have R/W access to it (although the RO flag is in an unchangeable tri-state on the folder).
The SQL Server Express error log is clean and indicates full functionality.
Everything is running locally.
No VWD installation errors.

Any ideas?

Thank you!

View 3 Replies View Related

Selection From Database Is Incorrect

Apr 26, 2007

I have over 50 rows in this table and my bizIDNum is always getting set to 4. Please take a look at the code and tell me whats wrong. If I do the select statement in the database the return number is correct. here is the code
 
// db connection
SqlConnection sqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["work4tipsConnectionString"].ConnectionString);
sqlConnection.Open();
string sqlSelect = "SELECT MAX(bizID) FROM business";
SqlCommand sqlCommand = new SqlCommand(sqlSelect, sqlConnection);
//sqlCommand.Parameters.Add("@Username", SqlDbType.VarChar).Value = username;
string bizIdNum = (string)sqlCommand.ExecuteScalar();
sqlConnection.Close();
sqlConnection.Dispose();

View 2 Replies View Related

Incorrect Results With T-sql Query In SQL Server 2005

Apr 18, 2006

I'm seeing some change in behavior for a query in SQL Server 2005 (compared to behavior in SQL Server 2000).  The query is as follows:
------------
create table #projects (projectid int) insert into #projects select projectid from tblprojects where istemplate = 0 and projecttemplateid = 365

Select distinct tblProjects.ProjectID
from tblProjects WITH (NOLOCK) 
     inner join #projects on #projects.projectid = tblprojects.projectid  
     Inner join tblMilestones WITH (NOLOCK) ON tblProjects.ProjectID = tblMilestones.ProjectID   
          and tblProjects.projectID in (
               select projectid 
               from tblMilestones 
               where (parent = 683691 AND PrimaryDate between '4/15/2006' and '4/22/2006' )
                    and enabled = 1  )
------------
This is dynamic SQL generated by the application when a user requests a report with variable parameters.  It works fine in SQL Server 2000.  It outputs 47 records which is correct. 

In SQL Server 2005, for some reason, the DISTINCT keyword is behaving as a TOP operator and outputs just 1 record.  (Results of Showplan Text at the end of this post).

If I modify the query even the slightest bit by:
1) Changing "where (parent = 683691 AND PrimaryDate between '4/15/2006' and '4/22/2006' )
    and enabled = 1  )"
 To " where (parent = 683691 AND PrimaryDate between '4/15/2006' and '4/22/2006' ) )
    and enabled = 1  "

2)  Changing " Select distinct tblProjects.ProjectID"
 To   " Select distinct tblProjects.ProjectID+''"

3) Removing the Distinct keyword, storing into a Temp table, then performing a distinct on the temp table

4) Adding: OPTION (FORCE ORDER)

5) OR completely fixing the query (remove redundant loops, etc)

...it works fine (outputs 47 records).  It also works if I created new tables (eg. tMilestones instead of tblMilestones) and inserted about 10 records into each and ran the query referencing these new tables.

I reindexed the tables, updated stats, updated usage, ran DBCC FREEPROCCACHE, changed MaxDOP settings...nothing makes the query behave the way it does in SQL Server 2000 without modifying the query/adding the query hint.

Have you come across this?  Any ideas on what might be causing the "TOP" operation.  (Somewhat resembles the bug mentioned in this article: http://www.kbalertz.com/Feedback_910392.aspx - but this was apparently fixed POST-SQL Server 2000 SP4 - so has it not made it into SQL Server 2005 yet?).

I will appreciate any new insights you might have on this issue. 
Thanks much,
Smitha


P.S. Results of Showplan Text:

StmtText                      
------------------------------
SET STATISTICS PROFILE ON

(1 row(s) affected)

StmtText                                                                                                                                                                                                                                                                                                                                                                                                                                                          
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Select distinct tblProjects.ProjectID from tblProjects WITH (NOLOCK) 
inner join #projects on #projects.projectid = tblprojects.projectid 
Inner join tblMilestones WITH (NOLOCK) ON tblProjects.ProjectID = tblMilestones.ProjectID  
and tblProjects.projectID in (
select tblMilestones.projectid from tblMilestones
where (parent = 683691 AND tblMilestones.PrimaryDate between '4/15/2006' and '4/22/2006' ) 
and tblMilestones.enabled = 1  )

(1 row(s) affected)

StmtText                                                                                                                                                                                                                                                                                                                                                                      
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  |--Stream Aggregate(DEFINE:([ExpesiteProductionCopy].[dbo].[tblProjects].[ProjectID]=ANY([ExpesiteProductionCopy].[dbo].[tblProjects].[ProjectID])))
       |--Nested Loops(Inner Join, OUTER REFERENCES:([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID]))
            |--Nested Loops(Inner Join, OUTER REFERENCES:([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID]))
            |    |--Filter(WHERE:(CONVERT_IMPLICIT(tinyint,[ExpesiteProductionCopy].[dbo].[tblMilestones].[Enabled],0)=(1)))
            |    |    |--Nested Loops(Inner Join, OUTER REFERENCES:([Uniq1014], [ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID]) OPTIMIZED)
            |    |         |--Merge Join(Inner Join, MERGE:([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID], [Uniq1014])=([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID], [Uniq1014]), RESIDUAL:([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID] = [ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID] AND [Uniq1014] = [Uniq1014]))
            |    |         |    |--Sort(ORDER BY:([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID] ASC, [Uniq1014] ASC))
            |    |         |    |    |--Index Seek(OBJECT:([ExpesiteProductionCopy].[dbo].[tblMilestones].[byPrimaryDate]), SEEK:([ExpesiteProductionCopy].[dbo].[tblMilestones].[PrimaryDate] >= '2006-04-15 00:00:00.000' AND [ExpesiteProductionCopy].[dbo].[tblMilestones].[PrimaryDate] <= '2006-04-22 00:00:00.000') ORDERED FORWARD)
            |    |         |    |--Index Seek(OBJECT:([ExpesiteProductionCopy].[dbo].[tblMilestones].[byParentID]), SEEK:([ExpesiteProductionCopy].[dbo].[tblMilestones].[Parent]=(683691)) ORDERED FORWARD)
            |    |         |--Clustered Index Seek(OBJECT:([ExpesiteProductionCopy].[dbo].[tblMilestones].[projectid]), SEEK:([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID]=[ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID] AND [Uniq1014]=[Uniq1014]) LOOKUP ORDERED FORWARD)
            |    |--Index Seek(OBJECT:([ExpesiteProductionCopy].[dbo].[tblProjects].[PK_tblProjects_1]), SEEK:([ExpesiteProductionCopy].[dbo].[tblProjects].[ProjectID]=[ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID]) ORDERED FORWARD)
            |--Top(TOP EXPRESSION:((1)))
                 |--Nested Loops(Inner Join)
                      |--Table Scan(OBJECT:([tempdb].[dbo].[#projects]), WHERE:([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID]=[tempdb].[dbo].[#projects].[projectid]))
                      |--Clustered Index Seek(OBJECT:([ExpesiteProductionCopy].[dbo].[tblMilestones].[projectid]), SEEK:([ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID]=[ExpesiteProductionCopy].[dbo].[tblMilestones].[ProjectID]) ORDERED FORWARD)

(15 row(s) affected)

StmtText                     
-----------------------------
SET STATISTICS PROFILE OFF

(1 row(s) affected)

 

 

View 6 Replies View Related

Incorrect Syntax Near '-' While Trying To Backup Database

Feb 4, 2008



Hi
I have a database with an '-' in the name. If I try and backup the database I get an error saying Incorrect syntax near '-'. This off course means the '-' is an illegal character but is there a way around this

Backup statment exec: BACKUP DATABASE DB-01 TO SmartTrade_Bak

View 5 Replies View Related

Report Builder - Filter Dropdown Order Post 2005 SP2 Incorrect

Mar 7, 2007

Hi All,

Hope someone can help here. Since installing Service Pack 2 onto our SQL 2005 instances our report models are not working correctly. When selecting fields within the models as filters, and getting them to prompt when the report is run, the ordering of the data within the filters does not match the selected ordering in the model definition. The ordering is random each time the report is run.

This issue does not happen when setting the filter up, the data appears in the correct order, but when running the report the filter data is incorrect.

I have spent the last 4 hours building a new system and testing this with each stage of service packing. The RTM version of SQL 2005 is ok, SP1 is also OK, but when SP2 is applied the ordering fails.



Any ideas?



Many thanks in advance.



Regards



Nick

View 4 Replies View Related

Access Database Synchroniser Fails With 'incorrect Format Or Content'

Feb 23, 2007

I've installed ADS on a windows mobile 5.0 device and am trying to run the sample Wizard application. It connects to the test database on the device OK, but fails to connect to the database on the desktop with the error:
"The HTTP request failed due to incorrect format or content. Try restarting the IIS server"


I've tried changing the port from 1024 in case it was already in use, but this doesn't change the error. Nor does restarting IIS (v5.1). IIS appears to be running correctly on the desktop.

Any ideas what to try next?

thanks
irs

View 8 Replies View Related

Creating A New Connection With Microsoft SQL Server 2005 Compact Edition From Visual Studio 2005 IDE

Jul 25, 2007



Hello, How are you?
I have a problem when I try to create a new connection with Microsoft SQL Server 2005 Compact Edition from Visual Studio 2005 IDE. When I€™m going to choose the data source, the SQL Server 2005 Compact Edition provider doesn€™t appear in the list. I installed the SQL Server 2005 Compact Edition from this page http://www.microsoft.com/downloads/details.aspx?FamilyId=%2085E0C3CE-3FA1-453A-8CE9-AF6CA20946C3&displaylang=en. In the additional information says by installing SQLServerCE31-EN.msi installs the provider (System.Data.SqlServerCe.dll) in the GAC (global assembly cache) and registers the OLEDB provider (sqlceoledb30.dll). So, I don€™t understand why couldn€™t I create a new connection with Microsoft SQL Server 2005 Compact Edition from Visual Studio 2005 IDE?



I appreciate your help€¦

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved