How To Tailor CREATE DATABASE For An Arbitrary Instance
Sep 20, 2006
Suppose the following:
As part of a product install (using InstallShield)...
I create a SQL Server Express instance (say "X") via a silent install
I supply a script to create a database in instance X.
The idea, of course, is to have a fully automated install. But there's one problem I can't quite see how to work around:
- the CREATE DATABASE statement needs the name of a file to contain the database, and that file needs to be in a folder that belongs to the instance (e.g. Microsoft SQL ServerMSSQL.1, Microsoft SQL ServerMSSQL.2, etc).
Is there a syntactic variant that allows me to avoid this problem?
Thanks
Josh
View 4 Replies
ADVERTISEMENT
May 1, 2006
Would appreciate some help with this one. I need to run a query on a known table an arbitrary database. The DB name will be selected at runtime.
What I've tried:
DECLARE @DBName varchar(10)
SET @DBName = 'WBTST'
SELECT TOP 100 PERCENT Col1, Col2
FROM [@DBName].dbo.Table1
GROUP BY Col1
Result:
Invalid object name '@DBName.dbo.UPR00100'.
I've also tried without the square brackets around @DBName in the FROM clause. This results in: Incorrect syntax near '.'.
Thanks for the help
Wes
View 2 Replies
View Related
Mar 2, 2007
Hello...
Is there any way to create a new database directly as a user instance. I guess this means creating a new mdf/ldf pair which is detached from the server after its created.
Thank you...
View 9 Replies
View Related
Feb 5, 2008
In my machine , we were used befour sql server2000 and now we are using sql server 2005 ,
So, I have both 2000 and 2005 installed on my machine ,
on my machine i am working on Custome Paging and one function ROW_NUMBER() is not working ,because of i think i am still working on 2000 instance on locally,
Can any one know how can we create an instance of sqlserver 2005 database and work with that?
I want to work with sql server 2005.
View 1 Replies
View Related
Aug 25, 2015
We have installed 2014 sql server on a stand alone VM and can create alerts without issue. We recently did a cluster install of 2014, and when trying to create alerts get the following error:
Msg 14262, Level 16, State 1, Procedure sp_verify_performance_condition, Line 52
The specified object_name ('Databases') does not exist.
Specifically, we are trying to create a database alert on percent log used that will kick off a job to back up the log.
We are on Windows 2012R2 o/s. In the drop downs in SSMS under alerts, we do not have all the options we have in the stand alone instance - they are not there. Is this a bug? Is there a CU?
View 2 Replies
View Related
Apr 13, 2007
Hi
I've never had to do this, but when I downloaded the Web Workflow Approvals Starter Kit, it requested that I install the database into a User Instance of .SQLEXPRESS.
Now the problem is, I've installed it onto a default instance, so I was wondering whether you can create a named instance on top of a default instance... and if so, how would you do that?
Cheers
Chris
View 3 Replies
View Related
Oct 23, 2007
I'm setting up a simple SSRS implementation for a non-profit organization, using two servers hosted at a data center. The first server has SQL Server Standard Edition and Reporting Services installed. I've designed and deployed a number of useful reports on this server.
I was hoping to isolate this first server by installing IIS and SSRS on the second server, have users browse from the Internet to that second server (over SSL, of course), and have all reports served up from databases (and, presumably, the report server database) on the first server.
During the installation of SSRS on the second server, however, I'm being prompted to specify the service account. According to the help text:
"Reporting Services. Service accounts are used to configure a report server database connection. Choose a domain user account if you want to connect to a report server database on a remote SQL Server instance. If you are using a local report server database, you can use a domain user account or Local System to run the service."
I believe I want to configure SSRS to connect to a report server database on a remote SQL Server instance; therefore, it appears that I need to enter a domain user account. The only problem is, neither server belongs to a domain; they are members of a simple two-server workgroup.
Does SSRS, configured to connect to a report server database on a remote SQL Server instance, require a domain? Does what I'm hoping to accomplish require a domain? Creating a two-server domain seems like overkill for this implementation, doesn't it?
I appreciate any comments and suggestions. Thanks!
View 1 Replies
View Related
Mar 30, 2006
How can I select all the "filler" dates.
CREATE TABLE tbDateList
( ItemDate datetime )
INSERT INTO tbDateList (ItemDate)
SELECT '01/01/2006' UNION ALL
SELECT '01/10/2006'
SELECT * FROM tbDateList
DROP TABLE tbDateList
In the select statement, I want to select all dates in tbDateList, but I also want to include dates that are not in the list.
Eg: the above select * will return the 2 dates, but I want it to return the 10 dates, 01/01/2006 - '01/10/2006'
Any suggestions?
Mike B
View 4 Replies
View Related
Jan 13, 2007
Hi,
I want to move one database from the source SQL Server 2000 instance to a new SQL 2000 instance in another machine. I have five user databases in this source SQL instance. How should be my approach to move this single database out of this ? My understanding is restoring this database in the new instance, copying all logins to the new instance and then copying the jobs, DTS packages, alerts, operators only specific to this database will do it. Please let me know if this is exactly what I should do ..
Thanks in advance..
Regards,
Himansu
View 1 Replies
View Related
Jan 13, 2007
Hi,
I want to move one database from the source SQL Server 2000 instance to a new SQL 2000 instance in another machine. I have five user databases in this source SQL instance. How should be my approach to move this single database out of this ? My understanding is restoring this database in the new instance, copying all logins to the new instance and then copying the jobs, DTS packages, alerts, operators only specific to this database will do it. Please let me know if this is exactly what I should do ..
Thanks in advance..
Regards,
Himansu
View 4 Replies
View Related
Mar 22, 2007
I have two tables:
Headers(id int, time datetime)
Data(id int, product_id int, property_id int, value float)
Data.id references Headers.id
Headers.id is a primary key,
Data has clustered index (id, product_id, property_id)
Headers has several thousand rows, Data several million. I want to return all rows from Data for a given product_id and a given property_id such that Header.id is in a given range.
Right now I am doing
SELECT id, time, value
FROM Headers H, Data D
WHERE
H.id = D.id AND
H.time >= @StartTime AND
H.time <= @EndTime AND
D.product_id = @ProductID AND
d.property_id = @PropertyID
This query can take 10+ seconds to run, though once I run it for a given product_id, queries for different values of property_id are much faster. Try a different product_id, and it takes longer. Given that there are millions of records in Data, is it reasonable for it to take this long? The index was suggested by Query Analyzer's Index Tuning Wizard, and I tried a couple variations on the query without any noticeable performance improvement. But, I'm no DBA...anyone have any tips? I googled a bit but couldn't figure out the right way to phrase my question to find any good info...thanks in advance
View 1 Replies
View Related
Apr 11, 2003
CREATE FUNCTION fctisnumericex(@c varchar(1))
RETURNS int AS
BEGIN
RETURN CASE WHEN ASCII(@c)>=ASCII('0') AND ASCII(@c)<=ASCII('9') THEN 1 ELSE 0 END END
CREATE FUNCTION fctstringincrement (@string varchar(255),@maxlen int)
RETURNS varchar(255) AS
BEGIN
DECLARE @@posr int
DECLARE @@posl int
DECLARE @@c varchar(1)
DECLARE @@token1 varchar(255)
DECLARE @@token varchar(255)
DECLARE @@token3 varchar(255)
DECLARE @@i int
/* emulates parts of the behaviour of s_modformatting::substringincrement */
/* 1. find the place where the numeric token starts from the right */
/* if we didn't find any non-numeric part then it might well be that the rightmost digit is already numeric */
IF dbo.fctisnumericex(SUBSTRING(@string,DATALENGTH(@string),1))=1
BEGIN
SELECT @@posr=DATALENGTH(@string)
END ELSE BEGIN
SELECT @@i=DATALENGTH(@string)
SELECT @@c=SUBSTRING(@string,@@i,1)
WHILE dbo.fctisnumericex(@@c)!=1 BEGIN
SELECT @@i=@@i-1
IF @@i<1 BEGIN BREAK END
SELECT @@c=SUBSTRING(@string,@@i,1)
END
SELECT @@posr=@@i
END
/* so have we got any numeric part inside that string? */
IF @@posr>0 BEGIN
/* yep. see how long it lasts */
SELECT @@i=@@posr
SELECT @@c=SUBSTRING(@string,@@i,1)
WHILE dbo.fctisnumericex(@@c)=1 BEGIN
SELECT @@posl=@@i
SELECT @@i=@@i-1
IF @@i<1 BEGIN BREAK END
SELECT @@c=SUBSTRING(@string,@@i,1)
END
/* separate now the parts of the string */
IF @@posl>1 BEGIN SELECT @@token1=SUBSTRING(@string,1,@@posl-1) END ELSE BEGIN SELECT @@token1='' END
SELECT @@token=SUBSTRING(@string,@@posl,@@posr-@@posl+1)
IF @@posr<DATALENGTH(@string) BEGIN SELECT
@@token3=SUBSTRING(@string,@@posr+1,DATALENGTH(@string)-@@posr) END ELSE BEGIN SELECT @@token3='' END
/* increment the numeric part */
SELECT @@token=convert(varchar(255),convert(int,@@token)+1)
END ELSE BEGIN
/* no numeric part at all. start with 1 at the end */
SELECT @@token1=@string
SELECT @@token='1'
SELECT @@token3=''
END
/* recompose the string and trim to max length if necessary */
RETURN SUBSTRING(@@token1+@@token+@@token3,1,@maxlen)
END
View 4 Replies
View Related
Dec 8, 2000
Hi everybody,
In Oracle, we can create any no. of instances on a single machine and each instance will have
a separate database. Like that, is it possible to create more than one instance on a single machine.
and also is it possible to create instances in SQL Server Version 7.0.
Any help would be appreciated.
tks in advance,
Sri
View 2 Replies
View Related
May 8, 2007
Hi to every one,
i am pavan working as Oracle DBA. now i am learning sqlserver2000.
b cos i need to maintain one sqlserver database.
i have one doubt. i installed sql server 2000 in my machine. now i want to creace second instance on same machie. how can i do it.
i heared we can maintain 32 servers in single machine in sql server
how can i create second instance.
and any one send me pdf /documentation on sqlserver 2000
thanks
pavan
pavan
Associate Oracle DBA
View 1 Replies
View Related
Oct 18, 2007
Hi.
I have Ms project server 2007 and SQL Enterprise 2005. I want to create a OLAP cubes. I follow the installation guides but I don't have MSSQL.2 directory.
How i create this? I read it is necessary to create an instance, but how i do that? I'm not a expert in SQL.
Thanks
--
Hernandezz
View 1 Replies
View Related
May 14, 2007
Hi I'm a newb with zero knowledge of SQL!
I have a SBS 2003 R2 Prem box with instances for Sharepoint, WSUS, MSFW and SBSMonitoring.
I have installed SP2 for SQL2005 but now I can't use the SBS CD to create a new instance or a default instance because it is an older version than SP2.
Can anyone advise how to create a default and/or new instance?
Many thanks in advance.
View 6 Replies
View Related
Nov 8, 2007
How i can create new instance for sqlserver 2005?
View 4 Replies
View Related
Apr 25, 2007
I am using SQL Server 2005 and need to add a set of Database tables and other objects for each user of an application.
The simplest way seems to be to duplicate the database inside the same instance. What are the guidelines/pros/cons for determining when you should create a new named instance versus just adding more databases to the same instance.
Thanks,
Rick
View 5 Replies
View Related
Nov 16, 2006
Hi Guys
Im new to SQL and I need to create a new instance ???
Help
View 2 Replies
View Related
Oct 2, 2007
I am using SQL express and Visual web developer on windows Vista.
When I try to create a new database the following message appears.
CREATE DATABASE permission denied in database master (error 262)
I log on to my computer as an administrator.
Help appreciated
Prontonet
View 4 Replies
View Related
Jun 22, 2015
How is the best way to make a function for summing an arbitrary number of times values (table parm?)- I 've read it's necessary to convert to seconds, sum then convert back, but Im' wondering if there's an alternative.
Here's the example I want to sum:
00:02:01:30
00:01:28:10
00:01:01:50
00:06:50:30
00:00:01:50
View 8 Replies
View Related
Feb 3, 2006
Hi,
I did install SQL-server developer edition. During the installation it asked me the name of de instance. I let the installation create the default Named instance.
Now I want to add an instance with a new name. What is the best way to do this? Is this the same as creating a Notification service?
Doeb
View 6 Replies
View Related
Dec 26, 2007
In a previous answer about beginning to use SQL Server 2005 Studio you say "Mention your Instance name /user name and password to connect to the server. "
Please advise where I should go to creat the instance name and password. It appears my Studio is running but I have not been asked for an "Instance Name" or password. What, exactly, does instance name mean and how can I tell if I have everything installed correctly?
Thank you.
View 8 Replies
View Related
Feb 19, 2008
I've uninstalled SQL Server Express and installed SQL Server Enterprise, all the tools where installed, but it didn't installed any server (instance?), and the old {local}SQLEXPRESS is still there, but it really doesn't exist.
- I look up at mipc/manage and there's no sql server running.
- I try to add an instance using the surface configuration, but it gave my this error:
Code Snippet
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)
How do I completly remove sql server from my computer, or, how do I add an instance of sql server?
View 4 Replies
View Related
Aug 10, 2006
I have a clean SQL 2005 install, and would like to setup a new/empty instance for an application suite (3rd party) to reside in.
Is there any way to do this?
View 5 Replies
View Related
Jun 26, 2007
If there is one instance in a server and I would like to create another instance, what should I do ?????
Or if my computer is not included any instance, How can I create new instance on my computer ?
????
Please give me any suggestions ......
View 1 Replies
View Related
Nov 27, 2007
I have access to a server with sql server 2005 workgroup edition. It runs an instance of sql server for this huge application we use. I would like to create a new instance of Sql server 2005 so I can play around with it and learn how to use the 2005 version. How can you create a new instance? I want this instance to run alongside the current instance. I can't find out how to do this anywhere. I'm sure it's possible though. Thanks
View 3 Replies
View Related
Feb 10, 2005
Run this script:
bulk insert viewdisp
from 'd:downloadsassessment05_preloadviewdisp.txt'
with (
check_constraints,
datafiletype = 'widechar',
firstrow = 1,
maxerrors = 0
)
go
Get this error:
Could not create an instance of OLE DB provider 'BULKIMPORTSTREAM'.
OLE DB error trace [Non-interface error: CoCreate of DSO for BULKIMPORTSTREAM returned 0x80040154].
SQL Server 2000 - SP3a
Logged in as sa with system admin, bulk insert admin, dbo, ddl admin, local admin, king of the world... you name it.
Is this a OLE DB Driver or MDAC issue?
Anyone else ever had this issue. You would think that Microsoft would have all of their error messages on their own WebSite, but that must be too much trouble for them.
View 3 Replies
View Related
Apr 25, 2008
Hi all,
I've got two SQL Server 2000 (SP ??) instances (on two separate machines; Win Server 2003 Standard) that I've inherited. I want to use one of them as a reporting instance of production for a single ~4GB database, updated nightly.
In other DBMS's I'd set up log shipping or a simple dump-and-load to keep the two in sync, but I'm not very familiar with SQL 2000 (I used to admin a SQL Server 7 back-in-the-day but have been on Sybase ASE, MySQL (blech) and 'Orable since).
Any suggestions to do this easily and (fairly) painlessly?
Would I want to set up replication between the two? If so, which flavor?
-- To me, this seems a bit overkill. Plus I hate to muck with production unless I really need to
Would I use DTS to do this?
-- Seems straightforward but as I understand it, DTS under-the-covers is a bcp-type process, which can be fairly slow.
Or a simple dump-and-load (with copy)?
-- This seems the best option as we're already doing a nightly dump. However, the data will have to be shuffled off to the other server (or some sort of network share set up that it can access) and then a script fired off when the dump is complete. This seems the most "brittle" of the three options (if the dump hasn't finished yet, then the script copy and import will fail, etc.)
Surely this has been done over and over again (searching the archives didn't tell me anything, but the site search tool isn't that great).
Thanks!!
View 2 Replies
View Related
Jul 13, 2013
I am using SQL server 2008 standard edition. Is there a way to create a new server Instance by a scrip except the default instance.?
View 2 Replies
View Related
Jul 14, 2006
I have installed sqlserver 2005 and by mistake i have made a default instance. I previously had sqlserver 2000 installed on my machine. Now I want to create a new instance of sqlserver 2005 but dont know how to do it .... Please help me on this
View 1 Replies
View Related
Feb 27, 2007
I have been looking at this statement for allowing access from a remote location to a specific database on my SQL Server (2005) from a client application.
Is it possible to do this using this method? I see lots of examples using this to expose Web Services of DB objects, however I need the client application to be able to authenticate to and access all objects in the database. If so, could some one provide a simple example of the arguments to use? For example, which protocol arguments are valid for this? I would like to use SQL Server Authentication. Is this possible?
Regards,
-Troy
View 1 Replies
View Related
Jun 8, 2007
We regularly deploy our product to client's sites and install a named instance of SQL Server 2005 and Reporting Services on their Windows Server. We use the instance name "lgs" during SQL setup and it automatically ensures we have a http://server/Reports$lgs and http://server/ReportServer$lgs to work with.
A recent deployment had some trouble with their OS and we have needed to reinstall the Reporting Services component of SQL. After reinstalling, the virtual directories for Reports$lgs and ReportServer$lgs are gone. I have used the Reporting Services Configuration tool to create new virtual directories but when I enter Reports$lgs or ReportServer$lgs as the names I get an error. The error says that the virtual directories cannot
be created due to illegal characters in the names.
How can I create the same virtual dirs that would have normally been created during initial installation?
Thanks,
- Jason
View 1 Replies
View Related
