Sunday, 13 August 2006
|
| SQL Server 2000 / [USA edition] = UK ??? Davidc@Estockware.Com 18:34:27 |
| | Hey guys,
this may be a stupid question but i have to ask it nonetheless because
i'm just not sure. Ok here goes, if I purchase for example SQL Server
2000 Ent 1 Proc [USA edition] , will I have any problem with installing
it if in UK, i plan to move there soon, the only issue i can think of
is support, evertyhing else should be the same, right? Any help would
be appreciated, Thanks in advance
|
| |  2 answer | Add comment |
|
| SQL Server Management Studio can't 'see' the 2005 engine?? But can see 2000 ?!? Jack 13:25:29 |
| | Hello,
I had SQL2000 server running fine on the windows 2003 box. I then installed
SQL 2005 Express. They both worked together fine. I then uninstalled SQL
2005 Express and installed SQL 2005 Server. But when I open SQL Server
Management Studio, I can only connect to the SQL 2000 engine. In the Object
explorer, it says v8.0.2039 (which I think is SQL 2000 Server, because I can
see the existing SQL 2000 databases).
How can I get SQL Server Management Studio to 'see' the SQL 2005 database
engine so I can create tables? I *think* its running because there is the
'sqlservr.exe' process running, and during installation there is no issues.
When I open SQL Server Management Studio, I choose 'Database Engine', then
my local Servername with Windows Authentication. How do I connect to the SQL
2005 instance?
Thanks,
Jack.
|
| | 5 answers | Add comment |
Saturday, 12 August 2006
|
| Weird DTS Problem Kr 20:43:19 |
| | have a dts package that is run by a scheduled job which was running
successfully for several months and suddenly stopped working.
The dts package does the following: Truncates the table, does a data
pump from a specific excel file to a table on sql server, deletes the
excel file. The job does the following:
Step 1: Look to see if a file exists and if so, run the dts package. On
success of this step the job goes to step 2.
Step2: Looks to see if any files still exist after the first step
Step3: Failure notification. To be triggered on failure of step 1 or
2.
The owner of the job is the Account that is used to run the
SQLServiceAgent and in the SysAdmin server role.
The owner of the job is also in the SysAdmin server role.
Both the accounts are Windows Accounts.
The job runs at schedule time, finds the file, and runs the dts
package. The first part of the dts package runs successfully - The
table is cleared. However it is not doing the data pump part. There
are no failure notifications. The job then goes to step two and sends
a message that the file still exists. The dts task is set to output an
error file which is not being updated, which tells that the task is not
executed. If the dts package is run manually, then the package runs
successfully.
Any help would be appreciated.
Thanks
KR
|
| | 1 answer | Add comment |
|
| SQL 2005-Config Manager contains no data Jack 16:15:28 |
| | Hi,
I can't seem to connect to SQL 2005. When I open Configuration Manager,
these is no information in any of the items in the tree.
I checked that WMI was configured correctly at:
http://msdn2.microsoft.com/en-us/library/ms188690.aspx
I can't see any instances at all here.
However, when I use Management Studio I CAN connect to an instance of SAL
2000 and see databases I have previously defined.
I'm running WIN 2003 with SQL 2005 standard and login as Administrator. SQL
books on line don't seem to have anything to say about this.
How can I get SQL Server Management Studio to 'see' the SQL 2005 database
engine so I can create tables? I *think* its running because there is the
'sqlservr.exe' process running, and during installation there is no issues.
I had SQL2000 server running fine on the windows 2003 box. I then installed
SQL 2005 Express. They both worked together fine. I then uninstalled SQL
2005 Express and installed SQL 2005 Server. But when I open SQL Server
Management Studio, I can only connect to the SQL 2000 engine. In the Object
explorer, it says v8.0.2039 (which I think is SQL 2000 Server, because I can
see the existing SQL 2000 databases).
Any help is greatly appreciated...
Jack.
|
| | 1 answer | Add comment |
|
| SQL Server 200 Developer vs Enterprise versions Jim Devenish 13:00:59 |
| | I am having some problems in the process of moving an Access back-end
to an SQL Server back-end. Everything works but there are severe
problems of response times when the number of users builds up.
At present we are using the Developer version before committing to the
Enterprise version. BOL (quoted below) seems to suggest that, apart
from licensing, they are the same.
Can anyone please confirm that this is so. If not, what is the
difference?
I may explain in another topic the problems I am facing but I first
wish to be clear on this particular question.
---------------------------------------------------------------------------------------------------------------
Quote from BOL
MicrosoftВ® SQL ServerВ™ 2000 is available in these editions:
SQL Server 2000 Enterprise Edition
Used as a production database server. Supports all features available
in SQL Server 2000, and scales to the performance levels required to
support the largest Web sites and enterprise online transaction
processing (OLTP) and data warehousing systems.
SQL Server 2000 Developer Edition
Used by programmers developing applications that use SQL Server 2000 as
their data store. Although the Developer Edition supports all the
features of the Enterprise Edition that allow developers to write and
test applications that can use the features, the Developer Edition is
licensed for use only as a development and test system, not a
production server.
|
| | 5 answers | Add comment |
|
| Error in Creating table in Sql server 2000 ---- 02:06:12 |
| | AA!
I am having problems on creating table through Enterprise Manager. It
gives me Error 1038 i.e. is as follows
Unexpected Error
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot use
empty object
or column names. Use a single space if necessary.
|
| | 3 answer | Add comment |
Friday, 11 August 2006
|
| How to switch off automatic blanking of table views in enterprise manager ? Daniel Wetzler 18:29:10 |
| | Hi Sqlserver experts,
I use the SQL Server enterprise manager of MSSQL 2000 regularly.
I'm often annoyed by the automatic blanking of the table views.
If this happens then mostly with the hint :
"The Results pane have been cleared to conserve server resources.
To re-establish the result set, run query again."
Is there any possibility to switch that blanking off ?
Best regards,
Daniel Wetzler
|
| | 5 answers | Add comment |
|
| Trouble with Impersonate User, ODBO and Analysis Services Exits Funnel 08:42:58 |
| |
Hello,
I apologize if this question is a bit vague and slightly off topic but I
couldn't find an Analysis Services and/or ODBO specific newsgroup. In
any event, I'm trying to address an issue in some code I've inherited
from a colleague who is on vacation and unfortunately, it deals with
ODBO, COM and Analysis Services, three topics with which I'm pretty
unfamiliar. Having said that here is the situation as best I can explain
it: the code uses the C++/COM interface to ODBO to access MSAS. The code
works fine in general but there is a problem when accessing an MSAS
server located on a machine other than that on which the client is
running which deals with the security context under which the logged in
user functions. Our end users (optionally) pass us a set of credentials
(username/domain/password) which (when passed) they would like to serve
as the authenticating credentials for Analysis Server. In an effort to
address the original issue before leaving for vacation my colleague
added a call to LogonUser( ) followed by a call to
ImpersonateLoggedOnUser( ) before the call to CoCreateInstance or any of
the ODBO initialization calls. The two new calls are only made when
we're passed credentials by the end user. When I test the code with
passed credentials, neither call fails, but it seems that they don't
have the desired affect.
Here are my observations based on running my test code and then browsing
the security event log on the machine on which Anlaysis Services is running:
(1) If credentials aren't passed and LoginUser/ImpersonateLoggedOnUser
aren't therefore called, then the event log indicates two events, one
538, one 540 both corresponding to the user logged onto the client machine.
(2) If credentials are passed and as a result we make the calls to
LoginUser/ImpersonateLoggedOnUser, then the event log indicates an event
538 by user 'Ananymous Logon'.
A few questions then:
(1) Is there anyway to tell from the security log, which application
logged the events? I'd hate to think that I'm on a wild goose chase and
that these aren't Analysis Server events I'm looking at. On a related
note, is there anyway to view a history of who has logged on through one
of the MSAS management tools?
(2) Obvisouly, my real problem is scenario (2) above. What we've done
doens't seem to work, so the question is: is there some other way, given
a set of network credentials, to use them to authenticate through ODBO
against a remote Analysis Services server?
If anyone could shed any light on any of this, I'd really appreciate it.
Thanks in advance for any replies.
-exits
|
| | Add comment |
|
| Flattening Parent Child Hierarchy: Urgent please help Dip 07:09:09 |
| | Hi Expert,
How do I flatten a Parent Child hierarchy to regular flat data: please
provide some SQL code:
I have now:
Task_ID, Parent_Task_ID, Task_NameLevel
11Project Management1
21Costing2
31Estimating2
42Task13
52Task23
63Task33
73Task43
I want to have:
Level1Level2Level3
Project ManagementCostingTask1
Project ManagementCostingTask2
Project ManagementEstimatingTask3
Project ManagementEstimatingTask4
Please help, I am stuck!
Thanks in advance.
Soumya
|
| | 2 answer | Add comment |
|
| Compare Two Queries - Help Brian Schultz 02:12:38 |
| | Hi All,
I have two database that are duplicates of each other - but are on
different servers.
I need to write a script that will do a select from one table and then
compare it to another select of that table - but on the db on the other
server.
Is it possible to do that in a script? If so, how?
Thanks in advance.
|
| | 2 answer | Add comment |
|
| Tying together tables J Evans 1970 01:39:13 |
| | Hello. I've got a table I'm trying to tie to two other tables. The
problem is that there is nothing distinct between the 3 tables. Yes,
I know... But this is what I have to work with. Let me explain
exactly what it is I'm trying to do with a little background history.
First, the fast food company I work for has registers in their stores.
We capture TLD files from the registers every 15 minutes. On a daily
basis those files are brough to headquarters where they are put in 3
different tables - parSalesHdr, parSalesDetail and
parSalesDetailModifier. The header table has the register number, the
time the order wazs tendered, who was the cashier, gross order total,
etc. The detail record has the details of that order - what was
ordered, the price of the items, what was discounted, when it was
discounted, etc. The modifier table has the modifiers of what is in
the detail table - no pickes, add onions, etc. Okay, so now the
register company has added the possibility of a new file to be picked
up if we want it. It is the reduction file. This file contains
information for orders where an item was deleted from an order after
the order was totaled. This is a bad thing - it allows theft. We want
to use the r eduction file to find out who is doing this. A deletion
from an order requires a manager to swipe their card on the register to
allow a deletion. The reduction file contains that - who swiped their
card, for what item, the cost of the item.
Basically what I want to do is to tie what is in that Reduction file to
the detail table and header table. The detail and header table
diferent date/time stamps, but none of them match the date/time stamp
in the reduction file. The header fille has the time the first item
was placed and the time the cash was tendered. The detail table has
the time the cash was tendered. The reduction file just has the time
the manager card was swiped. The only thing I can see to do is try to
match the reduction time to be between the first item order time and
the cash tender time. Then I can match on the items being deleted from
the order. The detail table has a field called [after] which would
indicate an item being deleted as well. The [after] field will have the
quantity of the items being deleted from the order after it is totaled.
So I can use that as well. The problem comes in when there are many
of the same items being sold. Some have been deleted - some not.
There's no real way to match those up.
That's my question - is there some what to tie the reduction table to
the detail and header tables that I'm not seeing? I've got table
creations/inserts and the query I running to ties them all together
below.
Thanks,
Jennifer
Create Table parSalesHdr
(parSalesHdrID bigint, unitnumber int ,registernumber int ,
posemployeenumber int, posemployeename nvarchar(30),
grossordertotal money,ordertotaltime datetime,
amounttendertime datetime, BusinessDay DateTime)
Insert into parSalesHdr (parSalesHdrID, unitnumber, registernumber,
posemployeenumber, posemployeename , grossordertotal ,ordertotaltime,
amounttendertime , BusinessDay) values (5948325, 608, 3,7,
'Larry',6.11,'8/30/05 12:11:06 am', '8/30/05 12:18:26 am', '8/30/05')
create Table parSalesDetail
(parSalesHdrID bigint, parSalesDetailID bigint, quantity int,
itemprice money,[after] int, positem nvarchar(20),
amounttendertime datetime, BusinessDay smalldatetime, UnitNumber int)
Insert Into parSalesDetail (parSalesHdrID , parSalesDetailID ,
quantity,
itemprice ,[after] , positem ,
amounttendertime , BusinessDay , UnitNumber) values (5948325,
26143916, 1, 4.8900, 1, 'WC-ML', '2005-08-30 00:18:26.000',
'2005-08-30 00:00:00.000', 608)
Insert Into parSalesDetail (parSalesHdrID , parSalesDetailID ,
quantity,
itemprice ,[after] , positem ,
amounttendertime , BusinessDay , UnitNumber) values (5948325,
26143917, 1, 4.8900, 1, 'WC-ML',
'2005-08-30 00:18:26.000',
'2005-08-30 00:00:00.000', 608)
Insert Into parSalesDetail (parSalesHdrID , parSalesDetailID ,
quantity,itemprice ,[after] , positem ,
amounttendertime , BusinessDay , UnitNumber) values (5948325,
26143918, 7, 4.1900, 7, 'WB-ML',
'2005-08-30 00:18:26.000',
'2005-08-30 00:00:00.000', 608)
Insert Into parSalesDetail (parSalesHdrID , parSalesDetailID ,
quantity,itemprice ,[after] , positem ,
amounttendertime , BusinessDay , UnitNumber) values (5948325,
26143919, 1, 4.1900, 0, 'WB-ML',
'2005-08-30 00:18:26.000',
'2005-08-30 00:00:00.000', 608)
Insert Into parSalesDetail (parSalesHdrID , parSalesDetailID ,
quantity,itemprice ,[after] , positem ,
amounttendertime , BusinessDay , UnitNumber) values (5948325,
26143920, 7, 4.1900, 7, 'WB-ML',
'2005-08-30 00:18:26.000',
'2005-08-30 00:00:00.000', 608)
Insert Into parSalesDetail (parSalesHdrID , parSalesDetailID ,
quantity,itemprice ,[after] , positem ,
amounttendertime , BusinessDay , UnitNumber) values (5948325,
26143921, 4, 4.1900, 4, 'WB-ML',
'2005-08-30 00:18:26.000',
'2005-08-30 00:00:00.000', 608)
Insert Into parSalesDetail (parSalesHdrID , parSalesDetailID ,
quantity,itemprice ,[after] , positem ,
amounttendertime , BusinessDay , UnitNumber) values (5948325,
26143922, 1, 4.1900, 1, 'WB-ML',
'2005-08-30 00:18:26.000',
'2005-08-30 00:00:00.000', 608)
CREATE TABLE [dbo].[ParReductionFile] (
[UnitNumber] [int] ,
[ReductionType] [int] ,
[RegisterNumber] [int] ,
[CashierNumber] [int] ,
[CashierName] [nvarchar] (16) ,
[ReductionDate] [datetime] ,
[ReductionTime] [datetime] ,
[ReductionCode] [char] (1) ,
[ManagerNumber] [int] ,
[ManagerName] [nvarchar] (16) ,
[ReductionValue] [decimal](18, 4) ,
[OriginalQuantity] [int] ,
[NewQuantity] [int] ,
[ProductID] [nvarchar] (50) ,
[ProductName] [nvarchar] (50)
)
insert into parReductionFile (UnitNumber
,ReductionType,RegisterNumber,CashierNumber,
CashierName,ReductionDate,ReductionTime,ReductionCode,ManagerNumber,
ManagerName,ReductionValue,OriginalQuantity,NewQuantity,ProductID,ProductName)
values(608,2,3,7,'Larry Shirley', '8/30/2005','12:12:00
AM','A',30,'ADRIANE PATTERSO',4.59,1,0,'WB-ML', 'WB-ML')
insert into parReductionFile (UnitNumber
,ReductionType,RegisterNumber,CashierNumber,
CashierName,ReductionDate,ReductionTime,ReductionCode,ManagerNumber,
ManagerName,ReductionValue,OriginalQuantity,NewQuantity,ProductID,ProductName)
values(608,2,3,7,'Larry Shirley', '8/30/2005','12:12:00
AM','A',30,'ADRIANE PATTERSO',18.36,4,0,'WB-ML', 'WB-ML')
insert into parReductionFile (UnitNumber
,ReductionType,RegisterNumber,CashierNumber,
CashierName,ReductionDate,ReductionTime,ReductionCode,ManagerNumber,
ManagerName,ReductionValue,OriginalQuantity,NewQuantity,ProductID,ProductName)
values(608,2,3,7,'Larry Shirley', '8/30/2005','12:12:00
AM','A',30,'ADRIANE PATTERSO',32.13,7,0,'WB-ML', 'WB-ML')
insert into parReductionFile (UnitNumber
,ReductionType,RegisterNumber,CashierNumber,
CashierName,ReductionDate,ReductionTime,ReductionCode,ManagerNumber,
ManagerName,ReductionValue,OriginalQuantity,NewQuantity,ProductID,ProductName)
values(608,2,3,7,'Larry Shirley', '8/30/2005','12:12:00
AM','A',30,'ADRIANE PATTERSO',32.13,7,0,'WB-ML', 'WB-ML')
insert into parReductionFile (UnitNumber
,ReductionType,RegisterNumber,CashierNumber,
CashierName,ReductionDate,ReductionTime,ReductionCode,ManagerNumber,
ManagerName,ReductionValue,OriginalQuantity,NewQuantity,ProductID,ProductName)
values(608,2,3,7,'Larry Shirley', '8/30/2005','12:12:00
AM','A',30,'ADRIANE PATTERSO',4.89,1,0,'WC-ML', 'WC-ML')
insert into parReductionFile (UnitNumber
,ReductionType,RegisterNumber,CashierNumber,
CashierName,ReductionDate,ReductionTime,ReductionCode,ManagerNumber,
ManagerName,ReductionValue,OriginalQuantity,NewQuantity,ProductID,ProductName)
values(608,2,3,7,'Larry Shirley', '8/30/2005','12:13:00
AM','A',30,'ADRIANE PATTERSO',4.89,1,0,'WC-ML', 'WC-ML')
SELECT
h.unitnumberUNIT,
h.registernumber REG,
h.posemployeenumber EENUM,
h.posemployeenameEMPNAME,
d.itemprice * d.afterTOTAL,
h.grossordertotal[ORDER TOTAL],
h.amounttendertimeTENDTIME,
d.afterATD,
d.positem[POS ITEM],
convert(nvarchar(12),r.reductiondate,101) + ' ' +
convert(nvarchar(12),r.reductiontime,108) as ReductionTime,
r.ReductionType,
r.RegisterNumber,
r.CashierNumber,
r.CashierName,
r.ManagerNumber,
r.ManagerName,
--r.ReductionValue,
r.OriginalQuantity,
r.NewQuantity,
r.ProductName
from parreductionfile r, parsaleshdr h, parsalesdetail d
where h.businessday between '8/30/05' and '8/30/05' and
h.unitnumber = 608
and convert(nvarchar(12),r.reductiondate,101) + ' ' +
convert(nvarchar(12),r.reductiontime,108) between
h.ordertotaltime and h.amounttendertime
and h.parsaleshdrid = d.parsaleshdrid
and d.unitnumber = r.unitnumber
and d.positem = r.productname
and d.after > 0
and d.after = r.originalquantity - r.newquantity
and d.quantity = r.originalquantity
|
| | 11 answers | Add comment |
|
| ALTER TABLE from sqlcmd script Jeff_in_MD 01:32:06 |
| | Hi,
I'm trying to add a column to a table, then update that column with a
query. This is all within a single batch. Sqlcmd gives me an error on
the update, saying "invalid column xxx", because it doesn't know the
column got added. We used to get around this in "osql" by using the
EXECUTE command, like: EXEC ("ALTER TABLE tbl ADD newfield varchar(255)
not null default ' '")
However, it looks like sqlcmd actually checks each query within the
script before it starts running, and throws the error because the field
isn't there at the time.
If need be I can just do a SELECT INTO and add the column there, but
it's a pain in the butt and I'm moving a LOT of data just to do what I
want. And no, I can't go back to where the table is created and add
the column. Does anyone have any suggestions? TIA!
- Jeff
|
| | 2 answer | Add comment |
|
| Error 22272: Cannot load the DLL xpstar.dll... Pupkin 01:20:53 |
| | Hi,
I'm running SQL Server 2000 on a Windows 2003 server. It's serving
several small web site databases.
About a month ago my automatic backups stopped working, and whenever I
try to edit or set up a new maintenance plan or export or import data
through Enterprise Manager, I get the following error:
Error 22272: Cannot load the DLL xpstar.dll, or one of the DLLs it
references. Reason: 127(The specified procedure could not be found.).
I've also tried doing these tasks with some third-party apps (EMS,
Teratrax), and I get the same errors.
I've Googled this but the two fixes I've read about (reinstalling MDAC
and reinstalling SP4) didn't work for me.
I'm not sure what caused it -- probably a Windows update -- because I
didn't notice it until several weeks later.
Any idea what I can do to fix this without completely reinstalling SQL
Server? Its other functionality is unaffected; maybe it's a Windows
problem?
At a loss. Thanks.
|
| | 3 answer | Add comment |
Thursday, 10 August 2006
|
| TRAN_ABORT gone? NiTiN 21:32:12 |
| | Hi!
I've been trying out SQL Server 2005's new Try-Catch exception handling
and I tried using the following form:
SET XACT_ABORT ON
BEGIN TRY
END TRY
BEGIN CATCH TRAN_ABORT
END CATCH
However, I get a syntax error on TRAN_ABORT.... removing it gives me
the expected output. Did Microsoft decide to do away with the
TRAN_ABORT keyword?
The syntax above (with the TRAN_ABORT) can be found at:
http://www.informit.com/articles/article.asp?p=327394&seqNum=8&rl=1
I did find some other articles (including MS SQL Server 2005 BOL) that
entirely left out the TRAN_ABORT keyword.
|
| | 2 answer | Add comment |
|
| 'MSDASQL' headache bigHairy 20:32:19 |
| | Hello - hope this is in the right group:
We have just started with linked servers and have successfully created
a view on SQL Server linked to a Progress database. I can query this
view happily in Query Analyzer.
I have created an ASP.NET application to display this view in a
datagrid but I get the following error:
System.Data.SqlClient.SqlException: OLE DB provider 'MSDASQL' reported
an error. at System.Data.SqlClient.SqlConnection.OnError(SqlException
exception, Boolean breakConnection) at
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
exception, Boolean breakConnection) at
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject
stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior
runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream,
BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData() at
System.Data.SqlClient.SqlDataReader.get_MetaData() at
System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds,
RunBehavior runBehavior, String resetOptionsString) at
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean
async) at
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String
method, DbAsyncResult result) at
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String
method) at
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
behavior, String method) at
System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior
behavior) at
System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior
behavior) at System.Data.Common.DbDataAdapter.FillInternal(DataSet
dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords,
String srcTable, IDbCommand command, CommandBehavior behavior) at
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior) at
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet) at...
The code in my ASP.NET application looks fine and works on non-linked
views on the same server. Do I need to add a command or change a
setting on SQL Server?
|
| | 2 answer | Add comment |
|
| Change Logical Name in Script Ryan 16:42:08 |
| | I'm trying to restore about 70 databases onto a new SQL server and
wanted to script the creation and restore. I've done the creation with
no problems, but on the restore, the logical names (of the original
data) are all over the place and were historically wrong.
So, when I use the script below... I've had to work out the Logical
name for the data and the log file and alter the script accordingly.
Creation
------------
Create Database MyDatabase ON (NAME = MyDatabase_data, FileName =
'D:\Database\MSSQL\Data\MyDatabase.mdf') LOG ON (NAME = MyDatabase_log,
FileName = 'D:\Database\MSSQL\Data\MyDatabase.ldf') COLLATE
SQL_Latin1_General_CP1_CI_AS
Restore
-----------
RESTORE FILELISTONLY
from disk =
'D:\Database\MSSQL\BACKUP\2006-08-07\MyDatabase_db_200608072100.BAK'
restore database MyDatabase
from disk =
'D:\Database\MSSQL\BACKUP\2006-08-07\MyDatabase_db_200608072100.BAK'
with REPLACE,
MOVE 'SomeOtherRubbish_Data' TO
'D:\Database\MSSQL\Data\MyDatabase.mdf',
MOVE 'SomeOtherRubbish_Log' TO
'D:\Database\MSSQL\Data\MyDatabase.ldf'
go
When I then look at the properties of the database, it shows the old
convention which I don't want.
So, even though I've been neat creating the database, it gets
overwritten with the odl rubbish name. How can I change the logical
name so that I can have a nice and neat naming convention ?
Oh, Yes I know I added the collation when creating the database, but
that's another thing that we need to address at some point.
Thanks in advance
|
| | 2 answer | Add comment |
|
| SQL Server 2005 + SQL Server Express Farseer 09:22:23 |
| | Hi,
I have SQL Server 2005 installed on my dev system. I am working on an
app that i would like to distribute SQL Server with. SQL Server
EXpress edition seem to be ideal for this. My questions are:
-If i installed Express Edition, Can both SQL Server 2005 and Express
Edition coincide peacefully on the same PC?
-Which one will Visual Studio see? (currently i created by database
using VS, which i assumed created a SQL Server 2005 database, since
that is the sql server i have installed).
-When VS2005 is installed, which SQL Server is installed and how can i
tell which one VS2005 is using if i have both SQL servers (2005 and
Express) installed?
thanks much
|
| | 2 answer | Add comment |
|
| Import multiple csv into multiple tables Chicagoboy27 02:25:24 |
| | Is there a way to import multiple csv files from a directory into sql
2005? The situation I have right now is that I have a folder with
multiple csv files that i need to import into sql 2005. I can do it
with the import wizard but it takes to long. The files will be updated
monthly. The first row in the files contains all the header information
which may change monthy. What I am looking to do is import all of these
csv into tables. One csv file into for one table. Ideally I would like
to use the name of the csv file as the name of the table. Any bump in
the right direction would be apprecieted
|
| | 1 answer | Add comment |
|
| Get files in different folder using sql Guest 02:05:50 |
| | Hi All,
I have a multiple files, but they are store in different directory on
the server. I want open those files and insert it into the database
using bcp.
Example files structure dir:
\\xyz\123\abc\text1.txt
\\xyz\123\abc\text2.txt
\\zyz\123\999\text2.txt
bcp "dabase" in \\xyz\123\abc\text1.txt -c -S"servername' -Usa
-Ppassword -T".
is there away to loop througth each dir, get the files, excecute the
bcp, then go to next folder.
Please help. Thanks in advance.
Ted Lee
|
| | 2 answer | Add comment |
|
| On to Bulk Insert issues Ted 01:54:14 |
| | OK, I tried this:
USE Alert_db;
BULK INSERT funds FROM 'C:\\data\\myData.dat'
WITH (FIELDTERMINATOR='\t',
KEEPNULLS,
ROWTERMINATOR='\r\n');
And I got the following errors.
Msg 4864, Level 16, State 1, Line 3
Bulk load data conversion error (type mismatch or invalid character for
the specified codepage) for row 1, column 4 (f_asset_classes_id).
Msg 4866, Level 16, State 8, Line 3
The bulk load failed. The column is too long in the data file for row
1, column 6. Verify that the field terminator and row terminator are
specified correctly.
Msg 7399, Level 16, State 1, Line 3
The OLE DB provider "BULK" for linked server "(null)" reported an
error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 3
Cannot fetch a row from OLE DB provider "BULK" for linked server
"(null)".
One limitation I encountered is that there doesn't seem to be a way to
tell MS SQL Server that the fields are optionally enclosed by quotes.
That is, text fields are enclosed by quotes while, e.g., numeric
fields, are not, and that these optional quotes are NOT to be included
in the data in the fields.
I do not know what "State 1" vs "State 8" is supposed to mean.
The table in question in this example allows nulls in several columns,
and in the flat file, nulls are represented by consecutive tabs. Might
this be causing trouble for the Bulk Insert statement?
In other cases, where I have to use something like bulk insert,
involves several columns containing dates. I know MS SQL supports the
format used in the file (by reading the documentation for cast
operations), but is there an easy way to tell MS SQL which of the
supported date formats to use when reading this data. I've read bcp
should be useful for this, but I have yet to figure that out.
In about half of the cases where I load data from a file, the data is
loaded once when the database is first created, and in the rest, there
is new data to be loaded every business day; so I need to be able to
submit the required command from the command line, and thus invoke it
using a perl script.
BTW: I have ordered a couple books on T-SQL, but they have yet to
arrive.
Thanks
Ted
|
| | 11 answers | Add comment |
|
| Using Union and Join in a single view Jason.Langdale@Gmail.Com 01:32:48 |
| | I have 3 tables I want to use in a view. Table A has field 1,2,3,4,5
and table B has field 1,2,3,4,5. I want to do a union on these. (I have
done so successfully if I stop here) I also want to join table C which
has field 1,6,7,8,9. I would like to join on field 1 and bring in the
other fields. I can join table C to A or B. I can union table A and B
but I do not know how to both union A and B then join C. Can someone
please help me? Thanks in advance.
|
| | 7 answers | Add comment |
|
| How to restrict evil create scripts? Ward Bekker 01:30:43 |
| | Hi,
For a service I'm working on I need to ask the user for their database
create script. It's used to re-create the users database schema in a
temporary database on a in-house server in an automated fashion.
For security reasons, I need to be sure that the create script can only
create tables, columns etc and not things like snooping in other
databases and/or formatting the server.
Can you give me pointers about what the minimum grants are to let good
script execute successfully and evil scripts fail?
Regards,
Ward
|
| | 4 answer | Add comment |
|
| Cant install SQL Server '05 EE, VB 2005, or Visual Web Dev 2005 Pine 01:21:08 |
| | hi! i just joined this group today after a colleague told me about it.
anyway, i'm new to SQL and haven't tried installing any version of it.
I do have vb 6.0 though on my pc. My problem is that I can't install
SQL server 05 EE, VB 2005 or VW 2005. I have downloaded all of the
installer from the Microsoft site as well as the dotnetfix which is
supposed to install the .net framework 2.0.
whenever i try to install SQL server EE, it is telling me that it has
found beta components. but i don't have any beta versions of SQL. I
tried uninstalling my Vb 6.0 thinking its causing the error, but same
thing happened.
The next thing I did is I uninstall my .net 2.0 and when i try to
install VB or VW 2005, i got to the step where it is trying to download
the .net framework 2.0 but it is taking so long. Question: is the
dotnetfix.exe which installed .net 2.0, different from the .net 2.0
that the setup.exe of VB2005 will download? if yes, may i know if there
is a direct site where i can manually download and install it so the
VB2005 will just skip that part and go on with the installation?
When i downloaded the exe files for SQL, .net 2.0, VB and VW, i did
select the SAVE option and saved them on my external mini hard drive so
I can install it at home. Does that make any difference? could that
cause the error?
pretty clear that i am a newbie with this stuff and i never thought
that it would be this hassle to install them heheheh any help, in a
detailed way if possible, would highly be appreciated.
thanks so much!
manny
|
| | 3 answer | Add comment |
Wednesday, 9 August 2006
|
| Skip rows where the first column with letter 'S' - BCP Guest 23:08:11 |
| | Hi All,
I have this data file with fix length(see below). I am able to insert
it into the database using bcp, but now I want to skip (do not insert)
the row which start with letter 'S' into the database. Is there away to
do it? By the way I am using -F2 option to skip the first record.
Here is my data:
Record 1 04
XXX
2 13106900240120042003040045061 Testing N POLYDOROS TRUST
EEE
2 12621241640280041004040045633 What are they MARTIN &
XXXXX
S C10000032004004098500000596110000005000010000000019613000000576497500
S X10000032000002098500000596130000000000000000000019613000000573497000
Thanks for your help.
Ted Lee
|
| | 1 answer | Add comment |
|
| Table structure and data transfer from SQL2000 to Access (.mdb) Jc 18:20:17 |
| | Hello. I want to ask about the possibility of copying both a table
structure and it's contents from a
SQL server table to a table within MS access. The problem cannot be
solve with a permanent table structure at the target location.
The names of the columns are essentially data with the application and
so are subject to change. I am targeting a solution using SQL Query
Manager.
The approach I have tried (with failure) is
SELECT *
INTO <linkedserver table>
FROM <local table>
This should create and copy. However, I am not sure if this is
achievable with this approach.
Refer to the dialogue;
-------------------------------------------------------
USE MASTER
GO
EXEC sp_addlinkedserver
@SERVER = 'Freddie',
@PROVIDER = 'Microsoft.Jet.OLEDB.4.0',
@SRVPRODUCT = 'OLE DB Provider for Jet',
@DATASRC = 'C:\temp\HMIS_Recipe.mdb'
-- I am not sure if this is required
EXEC sp_addlinkedsrvlogin 'Freddie', false, 'sa', 'Admin', NULL
SELECT * FROM Freddie...FRED -- This is OK
SELECT * INTO #Temp FROM Freddie...FRED -- This is OK
-- This fails - Refer error
SELECT * INTO Freddie.FRED65
from #temp
Server: Msg 2760, Level 16, State 1, Line 1
Specified owner name 'Freddie' either does not exist or you do not have
permission to use it.
-- This also fails and I thought reflected the above select with naming
- Refer error
SELECT * INTO Freddie...FRED65
from #temp
Server: Msg 117, Level 15, State 1, Line 2
The object name 'Freddie...' contains more than the maximum number of
prefixes. The maximum is 2.
EXEC sp_dropserver 'Freddie',
@droplogins = 'droplogins'
------------------------------------------------------------
Thank you.
Regards JC...
|
| | 3 answer | Add comment |
|
