Best Configuration For Large MSSQL 2K5 Server?

Apr 27, 2007

Hey Everyone,

I'm building a new PC for a set of very large MS-SQL databases (currently weighing in at approx 5TB, and should grow significantly going forward as well). This is for personal business-related use -- I do not have access to a database expert, I am not in an enterprise (self employed), etc., so I am turning here for help.

I'm wondering what type of machine I should get to handle this type of database. It will not particularly be under particularly heavy load (just a few simultaneous connections, not too many queries running at once), but is obviously a very large database (with over 22 billion rows of data). I don't know what type of hardware setup would be optimal for this machine (mainly processors).

What I have spec'd out at the moment is:
11 * SAMSUNG SpinPoint T Series HD501LJ 500GB 7200 RPM SATA 3.0Gb/s Hard Drives
1 * ASUS DSBF-D/SAS Dual Socket 771 Intel 5000P SSI EEB 3.61 Server Motherboard
1 * Areca ARC-1220 PCI-Express x8 SATA II Controller Card
1 * Kingston 4GB(2 x 2GB) 240-Pin DDR2 FB-DIMM DDR2 667 (PC2 5300) ECC Fully Buffered Dual Channel Kit Server Memory Model KVR667D2D4F5K2/4G
2 * Intel Xeon 5060 Dempsey 3.2GHz Socket 771 Active or 1U Processor Model BX805555060A

Priced on newegg, comes to a total of $3638.34

In the <$4000 range, can you guys think of anything that would be superior? The 11*500GB's I want to run in RAID50, and the Areca card seems to be one of the best on the market (not to mention has 24 ports for future scalability). The RAM is FB-DIMM's, Xeons..... am I missing anything?

Also, I'm a bit concerned about performance on the Xeons. The ones I chose are dual cores. Since there arent going to be toooo many simul queries (really, often, prolly 1 at a time even), should i try for single core processors?

Anyways, thanks for the help.

View 11 Replies


ADVERTISEMENT

Server Configuration For MSSQL 2000 And MSSQL 2005

Sep 6, 2006

Does enabling/disabling Data Execution Prevention have a performanceimpact on SQL 2000 or SQL 2005?For SQL best performance - how should I configure for:Processor Scheduling:Programs or Background servicesMemory Usage:Programs or System Cache

View 9 Replies View Related

SQL 2005 Express Setup Issue (The SQL Server System Configuration Checker Cannot Be Executed Due To WMI Configuration )

Sep 22, 2007

I am getting following error when trying to install SQL express 2005 on XPSP2.


TITLE: Microsoft SQL Server 2005 Setup
------------------------------

The SQL Server System Configuration Checker cannot be executed due to WMI configuration on the machine SIGMA-805539A79 Error:2147944122 (0x800706ba).

For help, click: http://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.06&EvtSrc=setup.rll&EvtID=70342


I tied re-installing WMI using http://blogs.msdn.com/jpapiez/archive/2004/12/09/279041.aspx link but could not get it working.


Do i need IIS installed? Its not installed on this box...

please suggest something... i am stuck...

Thanks,

View 3 Replies View Related

SQL Configuration Manager And SQL Server Surface Area Configuration Tools

May 2, 2007

I have just finished installing SQL 2005 Ent Edition on Win 2000 Adv Server, SQL2005 SP2, and SP2 Hotfix KB934458. After the installation, I could see and configure all services via SQL Configuration Manager and SQL Server Surface Area Configuration tools. This worked for a couple of days and now both configuration tools no longer detect SQL2005 components. SQL Server Surface Area Configuration issued an error that said "No SQL Server 2005 components were found on specified computer. Either no components are installed, or you are not an administrator on this computer. (SQLAC)". SQL Configuration Manager did not list any installed services. I don€™t know what caused this. Anyone has any idea? Please help! Below is the Installation Report which shows installed components.

===================================



The following components are installed on this server

DEV

Analysis Services

[Version: 9.2.3042.00 Edition: Enterprise Edition Patch level: 9.2.3054 Language: English (United States)]

Database Engine

[Version: 9.2.3042.00 Edition: Enterprise Edition Patch level: 9.2.3054 Language: English (United States)]

Reporting Services

[Version: 9.2.3042.00 Edition: Enterprise Edition Patch level: 9.2.3054 Language: English (United States)]

QA

Analysis Services

[Version: 9.2.3042.00 Edition: Enterprise Edition Patch level: 9.2.3054 Language: English (United States)]

Database Engine

[Version: 9.2.3042.00 Edition: Enterprise Edition Patch level: 9.2.3054 Language: English (United States)]

Common components

Integration Services

[Version: 9.2.3042.00 Edition: Enterprise Edition Patch level: 9.2.3054 Language: English (United States)]

Notification Services

[Version: 9.2.3042.00 Edition: Enterprise Edition Patch level: 9.2.3054 Language: English (United States)]

Workstation Components

[Version: 9.2.3042.00 Edition: Enterprise Edition Patch level: 9.2.3054 Language: English (United States)]

===================================

View 1 Replies View Related

Run Mssql 6.5 Client And Mssql 7.0 Server On Same Nt Workstaton?

Oct 26, 1999

Hello:

I am currently work on mssql 6.5. On my workstation, I have mssql 6.5
cient software.

However, I would like to install mssql 7.0 server on my nt workstation
and work with it to become familiar with 7.0. Can I install mssql 7.0
server on my nt workstation? Can mssql 6.5 client coexist with mssql 7.0
on the same machine if they are in different directories?

Thanks.

David Spaisman

View 1 Replies View Related

Package Configuration Wizard:-SQL Configurations Configuration Filter Not Working

May 23, 2006

Hi --I was wondering if this is a bug when I add new data in my table SSIS Confiurations and give wizard a new Configuration filter the package configuration wizard can not see the new values --the old values from the previous configuration are still showing---is there any known workaround or forced refresh I can do

thanks in advance Dave

Background:

SQL Package Configurations are most important because they provide the possibility of a central configuration store for your entire enterprise!!!!!!!! and is in my mind the only way to go

http://sqljunkies.com/WebLog/knight_reign/archive/2005/01/24/6843.aspx



Wizard results:

Name:
ETL

Type:
SQL Server

Connection name:
ETLConfiguration

Any existing configuration information for selected configuration filter will be overwritten with new configuration settings.

Configuration table name:
[dbo].[SSIS Configurations]

Configuration filter:
PT_CUST_ABR

Target Property:
Package.Variables[User::gsPreLoad].Properties[Value]
Package.Variables[User::gsPostLoad].Properties[Value]
Package.Variables[User::gsLoad].Properties[Value]
Package.Variables[User::gsFlatFilename].Properties[Value]
Package.Variables[User::gsFileName].Properties[Value]
Package.Variables[User::gsCDOMailTo].Properties[Value]
Package.Variables[User::gsCDOMailSubject].Properties[Value]
Package.Variables[User::giRecordCount].Properties[Value]
Package.Variables[User::giFileSize].Properties[Value]
Package.Variables[User::giBatchID].Properties[Value]
Package.Variables[User::gdFileDateCreated].Properties[Value]
Package.Connections[MyDatabase].Properties[ServerName]
Package.Connections[MyDatabase].Properties[InitialCatalog]





USE [ETLConfiguration]
GO
/****** Object: Table [dbo].[SSIS Configurations] Script Date: 05/23/2006 13:34:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SSIS Configurations](
[ConfigurationFilter] [nvarchar](255) COLLATE Latin1_General_CI_AS NOT NULL,
[ConfiguredValue] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[PackagePath] [nvarchar](255) COLLATE Latin1_General_CI_AS NOT NULL,
[ConfiguredValueType] [nvarchar](20) COLLATE Latin1_General_CI_AS NOT NULL
) ON [PRIMARY]

View 3 Replies View Related

How Many Result-rows Does Mssql Return Should Be Used Asynchronous Method To Use Mssql Cursor?

Aug 11, 2004

How many result-rows does mssql return should be used asynchronous method to use mssql cursor, can get the best performance in any time in any result offset?

i want to make the cursor fast in any time whatever how many results returned

View 2 Replies View Related

Configuration Manager - Which Configuration Type To Chose ?

Jan 14, 2006

It seems to me, that the best way is to have one Environment Varible containing the name of the SQL Server, so that you can look up the configuration in the SSIS Configuration Table when you run the package.

Is this the preferable way of doing it ? I would like to hear some positive/negative comment of why chosing a configuration type instead of another.

It seems to me that putting all of the configuration in the Environment variable is harder work but most secure (server breakdown vs table corruption/database error...)

Let's have some comments

View 3 Replies View Related

Database Migration Plan - (mssql/msde To -&> Pgsql/mssql)

Feb 10, 2008

Hi,

i was planning to create a database migration tool ..
its a certain database of a DMS (document management system) to
another DMS (two different DMS)... from DMS using msde 2000 server .. and tranfer to a DMS using a postgre sql or mssql .. depends ..

they have different table structures and names . . :D

i was thing of what language shall i use.. or what language is the best to work on this kind of project :)

hoping for your kind help guys. thanks :)



br
Frozenice

View 1 Replies View Related

Creating Index In MSSQL 2000 From MSSQL 2005

Mar 24, 2008

Hi,

I am a bit new to the MSSQL server. In our application, we use so many SQL queries. To imporve the performance, we used the Database enigine Tuning tool to create the indexes. The older version of the application supports MSSQL 2000 also. To re-create these new indexes, I have an issue in running these "CREATE INDEX" commands as the statements generated for index creation are done in MSSQL 2005. The statements include "INCLUDES" keyword which is supported in MSSQL 2005 but not in MSSQL 2000.

Ex:-

CREATE INDEX IND_001_PPM_PA ON PPM_PROCESS_ACTIVITY

(ACTIVITY_NAME ASC, PROCESS_NAME ASC, START_TIME ASC, ISMONITORED ASC)

INCLUDE

(INSTANCE_ID, ACTIVITY_TYPE, STATUS, END_TIME, ORGANIZATION);


Any help in creating such indexes in 2000 version is welcome.

Thanks,
Suresh.

View 2 Replies View Related

Generating Script For MSSQL 2000 From MSSQl 2005?

May 3, 2008

Hello
We are using SQL 2005 and now we are planning to use SQL 2000. what are the ways to do the process.

We taken the script spcificall for 2000 and run it in SQL 200. But we are getting the error in SCRIPT?

Could you please give me the step to do?

Thanks,
Sankar R

View 6 Replies View Related

MSSQL Express 2005 Vs. MSSQL 2000

Jun 15, 2006

Ben writes "I have a sql script that doesn't function very well when it's executed on a SQL 2000 server.

The scrpt looks like this:


---------------------------------------------------------------------------------------------------
USE [master]
GO
IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname = N'SSDBUSERNAME')
EXEC sp_addlogin N'SSDBUSERNAME', N'SSDBPASSWORD'
GO
GRANT ADMINISTER BULK OPERATIONS TO [SSDBUSERNAME]
GO
GRANT AUTHENTICATE SERVER TO [SSDBUSERNAME]
GO
GRANT CONNECT SQL TO [SSDBUSERNAME]
GO
GRANT CONTROL SERVER TO [SSDBUSERNAME]
GO
GRANT CREATE ANY DATABASE TO [SSDBUSERNAME]
GO
USE [master]
GO
If EXISTS (Select * FROM master.dbo.syslogins WHERE loginname = N'SSDBUSERNAME')
ALTER LOGIN [SSDBUSERNAME] WITH PASSWORD=N'SSDBPASSWORD'
GO
GRANT ADMINISTER BULK OPERATIONS TO [SSDBUSERNAME]
GO
GRANT AUTHENTICATE SERVER TO [SSDBUSERNAME]
GO
GRANT CONNECT SQL TO [SSDBUSERNAME]
GO
GRANT CONTROL SERVER TO [SSDBUSERNAME]
GO
GRANT CREATE ANY DATABASE TO [SSDBUSERNAME]
GO
USE [master]
GO
IF EXISTS (select * from dbo.sysdatabases where name = 'ISIZ')
DROP DATABASE [ISIZ]
GO
USE [SurveyData]
GO
exec sp_adduser 'SSDBUSERNAME'
GRANT INSERT, UPDATE, SELECT, DELETE
TO SSDBUSERNAME
GO
USE [SurveyManagement]
GO
exec sp_adduser 'SSDBUSERNAME'
GRANT INSERT, UPDATE, SELECT, DELETE
TO SSDBUSERNAME
---------------------------------------------------------------


I need to be converted to a script that can be executed on both MSSQL 2000 and MSSQL 2005.

I was wondering if somebody there could help me with this problem?!

Thanks,
Ben"

View 1 Replies View Related

Migration MSSQL 2000 32-bit To MSSQL 2005 64-bit

Nov 17, 2007

I've been tasked to move our production databases on MSSQL 2000 to 2005. I've supported MSSQL since version 6.5 and performed migrations to successor versions.

Current Environment is MSSQL 2000 32-bit with current Service Packs.

I've performed mock migrations on Test servers upgrading all Production instances simultaneously from MSSQL 2000 to 2005 32-bit. The Test environment is identical to Production minus server name, IP etc. Also I have a separate server with MSSQL 2005 installed where I use the DETACH / ATTACH and BACKUP / RESTORE method for migration / acceptance testing. There are approximately 30 databases totaling 70 GB. This has gone as expected and fairly successful. Vendors have been coordinated with to update code and staff for acceptance testing.

I'd prefer going directly to MSSQL 2005 64-bit instead if possible due to memory benefits etc. This is where I'd like some feedback prior to borrowing a 64-bit server for testing.

Upgrade options:

1. Is it better to migrate from MSSQL 2000 32-bit to 2005 64-bit via:
a. DETACH / ATTACH
b. BACKUP / RESTORE
c. Is one method more advantageous relating to the end result?
2. Regarding XP clients, have issues been experienced with the default SQL Server driver or is an alternate recommended for XP clients to connect to a MSSQL 64-bit server databases?
3. If you have performed this migration and have relevant experience please pass them along.

View 3 Replies View Related

SQL Server Large Transaction Log

Feb 3, 2006

I'm working with a database that has a relatively small amount of data.  The size of the data file for this database is in the 100-110 MB range, which gives an idea of its size and how much data is in it.  What is slightly troubling and baffling, is that the transaction file for this same database is significantly larger than the data file, at about 2.5 GB.  What could be happening that is causing this transaction log to be larger than the actual database size? 
Thx

View 1 Replies View Related

Sql Server 2005 Sql Server Surface Area Configuration Classical Problem

Feb 18, 2008

hi ; everybody
i have almost installed sql server 2005 enterprise edition on windows xp sp2 and i havent connected to any server .i have taken error:40 with my all attempts.tcp/ip protocol have been enabled on sql server configuration manager.i have tried to start sql server surface area configuration but every my attempt have been resulted a fault which is below.
[No SQL Server 2005 components were found on the specified computer. Either no components are installed, or you are not an administrator on this computer. (SQLSAC)]
i dont suppose that i did something wrong when i setupped.because i read every article on the internet about how to make setup.also; i setupped numbers of times on my pc.
but i didnt accomplished.
i thank for any help.please; give your solution explicitly.

View 8 Replies View Related

SQL Server Admin 2014 :: Server Configuration Change Events?

Nov 3, 2015

I'd like to capture the change event when it happens and have both a record posted to a table and an email notification to the DBA.

View 7 Replies View Related

Sql Server Setup Could Not Connect To The Database Service For Server Configuration

Jan 2, 2008

hi all
i get the following message error when installing sql server 2005 express :
<< sql server setup could not connect to the database service for server configuration the error was :[microsoft][gestionnaire de pilotes odbc] source de données introuvable et nom de pilote non spécifié >>
is there any help
thanks and best regards

View 6 Replies View Related

SQL Server Setup Could Not Connect To The Database Service For Server Configuration

Jun 4, 2006

OS: XP-SP2

Not able to install SQL Express 2005.
------------------------------

SQL Server Setup could not connect to the database service for server configuration. The error was: [Microsoft][SQL Native Client]SSL Provider: The certificate chain was issued by an authority that is not trusted.
Refer to server error logs and setup logs for more information. For details on how to view setup logs, see "How to View Setup Log Files" in SQL Server Books Online.

For help, click: http://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.06&EvtSrc=setup.rll&EvtID=29515&EvtType=lib%5codbc_connection.cpp%40Do_sqlScript%40OdbcConnection%3a%3aconnect%40connect%40x80090325

Needles to say, above link is useless..



I had managed to install previously, but ran into problems while trying to upsize an MS-Access database, I've tried everything from creating certificate to hacking away at the registry...Log-on with a different account, etc...Anybody knows how to get around this?

Here's part of the log file:




Microsoft SQL Server 2005 9.00.1399.06
==============================
OS Version : Microsoft Windows XP Professional Service Pack 2 (Build 2600)
Time : Sat Jun 03 20:09:53 2006

Machine : YANICK
Product : Microsoft SQL Server Setup Support Files (English)
Product Version : 9.00.1399.06
Install : Successful
Log File : e:Program FilesMicrosoft SQL Server90Setup BootstrapLOGFilesSQLSetup0005_YANICK_SQLSupport_1.log
--------------------------------------------------------------------------------
Machine : YANICK
Product : Microsoft SQL Server Native Client
Product Version : 9.00.1399.06
Install : Successful
Log File : e:Program FilesMicrosoft SQL Server90Setup BootstrapLOGFilesSQLSetup0005_YANICK_SQLNCLI_1.log
--------------------------------------------------------------------------------
Machine : YANICK
Product : Microsoft SQL Server VSS Writer
Product Version : 9.00.1399.06
Install : Successful
Log File : e:Program FilesMicrosoft SQL Server90Setup BootstrapLOGFilesSQLSetup0005_YANICK_SqlWriter_1.log
--------------------------------------------------------------------------------
Machine : YANICK
Product : MSXML 6.0 Parser
Product Version : 6.00.3883.8
Install : Successful
Log File : e:Program FilesMicrosoft SQL Server90Setup BootstrapLOGFilesSQLSetup0005_YANICK_MSXML6_1.log
--------------------------------------------------------------------------------
Machine : YANICK
Product : SQL Server Database Services
Error : SQL Server Setup could not connect to the database service for server configuration. The error was: [Microsoft][SQL Native Client]SSL Provider: The certificate chain was issued by an authority that is not trusted.
Refer to server error logs and setup logs for more information. For details on how to view setup logs, see "How to View Setup Log Files" in SQL Server Books Online.
--------------------------------------------------------------------------------
Machine : YANICK
Product : Microsoft SQL Server 2005 Express Edition
Product Version : 9.00.1399.06
Install : Failed
Log File : e:Program FilesMicrosoft SQL Server90Setup BootstrapLOGFilesSQLSetup0005_YANICK_SQL.log
Last Action : InstallFinalize
Error String : SQL Server Setup could not connect to the database service for server configuration. The error was: {Microsoft}{SQL Native Client}SSL Provider: The certificate chain was issued by an authority that is not trusted.
Error Number : 29515
--------------------------------------------------------------------------------

View 3 Replies View Related

Connecting Surface Area Configuration To Server On Clustered Server

May 8, 2006

I've run into a problem after upgrading production servers running under Microsoft Cluster Server, Windows Enterprise Server 2003 SP1, and SQL Server 2005 SP1 Enterprise Edition.

Once I completed the upgrade and applied the patch, I needed to examine the values set in the Features component of the Surface Area Configuration tool. The tool attempts to connect to localhostinstancename, but reports the following error:

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error:26 - Error Locating Server/Instance Specified)(Microsoft SQL Server)

I'm logged into the virtual server via Remote Desktop, but the SAC tool thinks I'm logged into the physical server supporting the virtual server. I seem to be caught between a rock and a hard place here, as I can't enable remote connections without first gaining access on the server itself.

Any help will be greatly appreciated.

View 3 Replies View Related

Asp.net Configuration Sql Server

Mar 10, 2008

Hi All,
Using Visual studio 2005 you can create a simple database to enable the use of login using asp.net configuration by clicking on website on the menu bar. By default the database is created and stored in the App_data file. However my service provider has a separate server for SQL (SQL Server), therefor it will not work.
How can I use this Asp.Net Configuration to create a database on my sql server?
Hope this question makes sense!!

View 3 Replies View Related

SQL Server Configuration

Apr 6, 2001

To get an understanding of the SQL Server technology and the hardware requiremnets. To configure a VLDB system for OLTP what is a typical hardware configuration that will be used.

No of Rows:10 Million
No of Tables : 50

Will have to spilt this out into many databases, or will one database be able to handle this. Assume that the database is normalized.

Any suggestion or information will be of great help. Does MS have any information relating to this information on their web site?

Murali Vallath

View 2 Replies View Related

SQL Server 6.5 Configuration

Jul 20, 1998

I am about to rebuild the primary SQL Server 6.5 server at our site. i would like to hear from those experienced in this regarding the optimal configuration settings. I have Microsoft`s recommendations, but want to hear from some real-world experiences. I have 1GB of RAM and a 27 GB hard drive.

View 2 Replies View Related

New Server Configuration

Jun 21, 2002

We are upgraded from sql 7.0 to sql 2000. We have a brand new server in which we are installing it on. We are trying to decide on the best setup for it. The db are a datawarehouse. So mainly used for input. Gets updated with new data once a day.

We were going to have:
Partician 1 : Raid 1 - OS and Applications

Now we are trying to decide on the other particians:
We have 6 36 Gig Drives to work with. One of them is going to be a hot swappable So basically we have 5 36 Gig drives to work with. I've read that its better to divide up the data/logs/tempdb. Would the following setup be good for performance or if not what would you recommend: We were thinking of making all of them Raid 5. My network guys say that with Raid 5 I'll end up with about 70 % so that accounts to about 127 Gig. So I was thinking the following:


partition 2 - Data Files 112 gig
Partition 3 - Logs 10 Gig
Partition 4 - Tempdb 5 gig

Any suggestion would be greatly appreciated - trying to get this set up with the best possible scenerio for performance.

Stacy

View 3 Replies View Related

Server Configuration

Nov 12, 1998

I have to build NT server for a SQL database which is just over 1.1 Gb. Does anybody have any suggestions for server configuration that I should look into
i.e.
Splitting the data and trans log across different disks.
Amount of memory ....
etc.

Any info would help
Thanks

View 2 Replies View Related

Help!!! For Sql Server Configuration

Oct 5, 2007

Dear Friends


I have windows xp sp2 on my laptop with 512 mb ram , 60 gb hdd. and amd 2 ghz processor. i have installed microsft.net 2.0 framework and every other prerequist for sql server 2005 enterprise edition. i have also installed sql server 2005 enterprise edition. but my problem is that i m not getting any servername to connect in the sql server management studio. plz can anybody tell me how i have to make connection in the sql server 2005 enterprise edition. (i must mention that i m using my laptop stand alone.)

Thanks in advance

View 4 Replies View Related

SQL SERVER SMP Configuration

Jul 20, 2005

If installing SQL Server 2000 ( Standard Edition or EE) on any of theWindows 2000 Server with multiple processor, do we have to to do anyadditions configuration to utilize SMP ?

View 1 Replies View Related

Configuration Of SQL Server

Oct 6, 2006

Hello, I am from Brazil, for this my English He/she would like to know how I can
configure SQL Sever Express to Turn in my Local pc. If they can help am very
grateful. Wenderson Castro

View 3 Replies View Related

SQL Server DSN Configuration

Aug 11, 2007

I have SQL Server 2000 loaded on my standalone Laptop - using Windows XP Pro.

When adding a system DSN at the prompt "Which SQL server do you want to connect to?" I have five options even though I have I SQL server. I have an option of two (local) servers and three (MYSQLSERVER) servers.

How can I delete the duplications?

Thanks in advance.

View 4 Replies View Related

Sending Large Binary To SQL Server Via ADO.NET

Feb 27, 2007

Hi,I am having some trouble with my ASP page sending a file to a SQL Server 2005 database running on another machine. An exception is generated as follows:A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)  It seems to work ok with smaller files, but when I attempt to upload a 11MB file, it gives me the above exception. I upped the httpRuntime maxRequestLength in the web.config to 2048. Any Ideas? Here is my code:1 if (FileUploader.HasFile)
2 {
3
4 // call the stored proc
5 SqlConnection conn = new SqlConnection();
6 conn.ConnectionString = "Password=password;Persist Security Info=True;User ID=MyUser;Initial Catalog=MyDb;Data Source=MySqlServerMachine;Connect Timeout=300";
7 conn.Open();
8
9 SqlCommand cmd = new SqlCommand();
10 cmd.Connection = conn;
11 cmd.CommandType = CommandType.StoredProcedure;
12 cmd.CommandText = "spAddTestBinary";
13
14 SqlParameter param = new SqlParameter();
15 param.ParameterName = "@binaryParam";
16 param.SqlDbType = SqlDbType.VarBinary;
17 param.Direction = ParameterDirection.Input;
18 param.Value = FileUploader.FileBytes;
19 cmd.Parameters.Add(param);
20
21
22 cmd.ExecuteNonQuery();
23
24
25 conn.Close();
26 }
  Here is my table:1 BinaryTable(
2 [id] [int] IDENTITY(1,1) NOT NULL,
3 [myBinary] [varbinary](max) NULL,
4 CONSTRAINT [PK_BinaryTable] PRIMARY KEY CLUSTERED
5 (
6 [id] ASC
7 )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
8 ) ON [PRIMARY]
My stored proc:1 ALTER PROCEDURE [dbo].[spAddTestBinary]
2 -- Add the parameters for the stored procedure here
3 @binaryParam varbinary(MAX)
4 AS
5 BEGIN
6 -- SET NOCOUNT ON added to prevent extra result sets from
7 -- interfering with SELECT statements.
8 SET NOCOUNT ON;
9
10 -- Insert statements for procedure here
11 Insert into BinaryTable
12 (myBinary)
13 Values
14 (
15 @binaryParam
16 )
17 END
 

View 3 Replies View Related

Large Move From Access To SQL Server 200

Jan 12, 2004

Hi,


My client has a rather large database with some very large reports. Some of the reports have around 20 sub-reports a piece. We have decided to move the client's application to a .NET web application and would migrate them to SQL Server 2000.

The only problem is now, designing the reports. I have tried doing what Microsoft says (converting to stored procedures and views) but I keep getting syntax errors on the SQL side of things when I cut and paste.

For example, the following code is taken from Access :

SELECT tblProjects.fldCountry, tblProjects.fldDescription, tblOrganizations.fldAcronym, tblProjects.fldProjID, Max(tblProjYears.fldStartDate) AS MaxOffldStartDate, Max(tblProjYears.fldEndDate) AS MaxOffldEndDate, qryProjLocsWithFEData.fldProjPeriodID
FROM (tblProjects INNER JOIN tblOrganizations ON tblProjects.fldOrgID = tblOrganizations.fldOrgID) INNER JOIN (tblProjYears INNER JOIN qryProjLocsWithFEData ON tblProjYears.fldProjPeriodID = qryProjLocsWithFEData.fldProjPeriodID) ON tblProjects.fldProjID = tblProjYears.fldProjID
GROUP BY tblProjects.fldCountry, tblProjects.fldDescription, tblOrganizations.fldAcronym, tblProjects.fldProjID, qryProjLocsWithFEData.fldProjPeriodID
ORDER BY tblProjects.fldCountry, tblOrganizations.fldAcronym, tblProjects.fldProjID;

But when I try that in SQL Query Analyzer i get the error : The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

I'm pretty sure it's on the tblProjects.fldDescription Group By, but if I leave it out, it still throws an error. Anybody have any ideas?

Thanks

View 5 Replies View Related

SQL Server 2008 :: Virtual Server Disk Configuration?

Mar 14, 2011

I have been tasked with moving our SQL server estate onto new 64bit SQL 2008 Virtual servers on a VM base. Each Virtual server will be attached to our SAN that i will have no control over. Do i ask for multiple LUNs pretending that there is a COS), Etemp), FData) and Glog) disk structure or do I just present a very big space as a single C: drive and let it go.We are consolidating lots of old physical servers onto fewer (more powerful) virtual servers (according to the VM and SAN administrators)

1. Configure your SAN properly

2. Don't overallocate shared resource

View 6 Replies View Related

Cannot See SQL Server 2005 Services In SQL Server Configuration Manager

May 10, 2006



I cannot see any services within "SQL Server 2005 Services" in SQL Server Configuration Manager.

I am new to SQL Server 2005 and recently installed all of the SQL Server 2005 64-bit components on to a Windows 2003 64-bit server. I can create and view databases via SQL Server Management Studio. I can also start and stop the SQL Server service for the default instance via SQL Server Management Studio. But I should be able to start/stop the service via SQL Server Configuration Manager and I believe I'm supposed to use SQL Server Configuration Manager to make changes to the services.

I'm concerned that something may have become corrupted and is effected the SQL Server Configuration Manager. Any suggestions?

Thanks in advance,

Joann





View 5 Replies View Related

Server Configuration Manager 64-bit Server Instance Not Showing

May 1, 2015

After installing SQL Server Express Advanced 2012 64-bit edition, we are unable to see the 64-bit Server Network Configuration in the left pane.  We need to edit the TCP/IP and Named Pipes to enable them.  All that is showing is the 32-bit instance for our Acronis backups.how to view or add a SQL server instance to the Server Network Configuration msc?

View 3 Replies View Related







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