Is it possible to create a database on a server called DBA_Status, and keep status of every database and servers that you are responsible for or do I have to created a DBA_Status on every server that I am responsible for???
Thanks
I'm trying to get a SQL 7 and 6.5 DB to interact, but while there is no problem in SQL7, I cannot create a table called "Public" or a field called "Primary"!!
Does anyone know why this might be and if so where I might get a list of any other "invalid" names??
Thanks in advance,
Damon
I'm trying to figure out how to identify the calling database within a sproc on a different database without using a parameter.
View 2 Replies View Related
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
the subject pretty much says it all, I want to be able to do the following in in VB.net code):
{[If [table with this name] already exists [in this sql database] then [ don't create another one] else [create it and populate it with these values]}
How would I do this?
Can I dynamically (from a stored procedure) generatea create table script of all tables in a given database (with defaults etc)a create view script of all viewsa create function script of all functionsa create index script of all indexes.(The result will be 4 scripts)Arno de Jong,The Netherlands.
View 1 Replies View RelatedI have some code that dynamically creates a database (name is @FullName) andthen creates a table within that database. Is it possible to wrap thesethings into a transaction such that if any one of the following fails, thedatabase "creation" is rolledback. Otherwise, I would try deleting on errordetection, but it could get messy.IF @Error = 0BEGINSET @ExecString = 'CREATE DATABASE ' + @FullNameEXEC sp_executesql @ExecStringSET @Error = @@ErrorENDIF @Error = 0BEGINSET @ExecString = 'CREATE TABLE ' + @FullName + '.[dbo].[Image] ( [ID][int] IDENTITY (1, 1) NOT NULL, [Blob] [image] NULL , [DateAdded] [datetime]NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]'EXEC sp_executesql @ExecStringSET @Error = @@ErrorENDIF @Error = 0BEGINSET @ExecString = 'ALTER TABLE ' + @FullName + '.[dbo].[Image] WITHNOCHECK ADD CONSTRAINT [PK_Image] PRIMARY KEY CLUSTERED ( [ID] ) ON[PRIMARY]'EXEC sp_executesql @ExecStringSET @Error = @@ErrorEND
View 2 Replies View Related
I'm new to using SSIS and have been reading and learning slowly how to use it. I'm trying to create an identical copy of our database for reporting. I've used the Import/Export wizard, but have had some issues with foreign keys and with sql_variant columns.
I've tried searching for anything but haven't had any luck as of yet. I guess I don't even know where to start or what to look for.
Any help would be appreciated. Thanks!
We have a SQL database that uses Active Directory with Windows Authentication. Can users that are members of the Active Directory group that has read/write access to the SQL database create ODBC connections to access the database directly and update the data? They dont have individual logins on the server. They are only members of the Active Directory group that has a login?
View 1 Replies View RelatedI am setting up SQL audit on sql servers in my environment based on requirement. I want to create database specifications ASAP database created. I tried DDL trigger but Audit doesn't support triggers. So I created audit specifications on model database. the only problem with this is every specification created on new database with same name.database specification name includes newly created database name or other methods to create database specifications on newly created databases.
View 6 Replies View RelatedHi,
I just want to know how do you called when you are accessing another pc thru \. I forgot how it is called.
Second, I like to know what [MACH] and [INST]. They don't look like directories.
\MultforestHEALTH[MACH][INST]HEALTH_WAREHOUSEDeploy_20080228p_Vitals_FACT.sql'
Hi, My server went dead(problems with the hard disk), but I have a copy of the whole sql server directory including the database in a external hard disk. I have a new server now and I would like to copy this database (with the reports from reporting services too) from the external hard disk to my new server. can anybody help me please? Thanks.
Note : I have a plain copy of all the sql server directory with all the files including the database not a SQLServer backup done with the wizards.
Cn not do anything with my sql server, everything i trt to do i get this message, user does not have permision, etc, ,
I am running windows Vista Business, SQL SERVER 2005
so what going on here
got rid of my error about user login rights, it was all working yesterday. but for some reason i now get this error CREATE DATABASE permission denied in database 'master'.
An attempt to attach an auto-named database for file C:InetpubwwwrootsqlSiteApp_DatasiteDB.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: CREATE DATABASE permission denied in database 'master'.
An attempt to attach an auto-named database for file C:InetpubwwwrootsqlSiteApp_DatasiteDB.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
Stack Trace:
[SqlException (0x80131904): CREATE DATABASE permission denied in database 'master'.
An attempt to attach an auto-named database for file C:InetpubwwwrootsqlSiteApp_DatasiteDB.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.]
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +734995
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1838
System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +33
System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +628
System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +170
System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +359
System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28
System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +424
System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66
System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +496
System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105
System.Data.SqlClient.SqlConnection.Open() +111
System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +121
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +137
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +83
System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1770
System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +17
System.Web.UI.WebControls.DataBoundControl.PerformSelect() +149
System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +70
System.Web.UI.WebControls.GridView.DataBind() +4
System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +82
System.Web.UI.WebControls.CompositeDataBoundControl.CreateChildControls() +69
System.Web.UI.Control.EnsureChildControls() +87
System.Web.UI.Control.PreRenderRecursiveInternal() +41
System.Web.UI.Control.PreRenderRecursiveInternal() +161
System.Web.UI.Control.PreRenderRecursiveInternal() +161
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1360
i have this in my web.config file <connectionStrings> <add name="ConnectionStringTest" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=C:InetpubwwwrootsqlSiteApp_DatasiteDB.mdf;Integrated Security=SSPI;Connect Timeout=30;User Instance=False" providerName="System.Data.SqlClient" /> </connectionStrings> <appSettings /> <system.web> <!-- Set compilation debug="true" to insert debugging symbols into the compiled page. Because this affects performance, set this value to true only during development. --> <compilation debug="true" /> <identity impersonate="true"/> and my asp connection string is <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionStringTest %>" SelectCommand="SELECT [entryID], [compID], [emailAddy], [answer] FROM [entry]"></asp:SqlDataSource> if i set user instance to true i get a user permission error. it says on sql server management that i have dbo rights on my database, but it wont let me put datareader or write on this login. any ideas? its driving me insane
I have installed new SQLServer2012 instance and my domain user have sysadmin privileges on this instance. I have a restore procedure and it will execute WITH EXECUTE AS 'domainmy username', for all the developers have exec permissions to this procedure. But newly installed server this procedure was failing with the following message. But the same procedure executing fine on other servers.
Msg 262, Level 14, State 1, Line 1
CREATE DATABASE permission denied in database 'master'.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Hi there
I am working on Visual Web Developer Express Edition 2005. When I right click on database explorer to create an SQL server database then I always find the option " Create New SQL Server database " Disabled.
Can any one tell me how to enable that option please ?
How can I execute or access a web page from a DTS package?
Both SQL server AND website are hosted on the same server (a Dual 2.4Gz Xeon with 2Gb RAM, RAID 5 etc)
I have 2 tables in SQL server 2000 that hold orders. These need to be posted into another table at a predefined time (ie: 4:30pm) and at the same time, access a remote address (a web service) and post certain elements of the order back.
Basically, can anyone help me out on how to execute a web page from a DTS.
I do NOT want to access a DTS from a webpage, which is all I'm finding at the moment.
Hello,
Question is, when I try to create relationships in SQL 7 without using the wizard..upon adding the tables I get an errror message that says "Coinitialization has not been called". What does this mean?
CREATE PROCEDURE sp_getT
@m1 int ,
@txn int ,
@Pan varchar(50) ,
@Act varchar(50) OUTPUT,
@Bal Decimal(19,4) OUTPUT,
@CBal Decimal(19,4) OUTPUT
AS
declare @pBal money, @pCbal money, @pAct money
SET NOCOUNT ON
IF @m1 = 200
BEGIN
IF @txn = 31
BEGIN
exec ChkBal @Pan, @pBal output, @pCbal output, @pAct out
END
END
SET @Act = @pAct
SET @Bal = cast(@pBal as Decimal(19,4))
SET @CBal = cast(@pCBal as Decimal(19,4))
return @Act
return @Bal
return @CBal
the above code returns this error message
"Server: Msg 8144, Level 16, State 2, Procedure CheckBalance, Line 0
Procedure or function ChkBal has too many arguments specified."
How do i specify all the arguments i want in the called procedure?
Hi everyone -
I'm stumped on what to cal this, there might even be
a method or pattern named for what i am trying to accomplish...
In the database, a number field is included on each table
When the DAL reads the record from the database, it is passed to
the client - work is possibly done to the record and is sent
back to the DAL for update.
A query is done against the table to retrieve the record again,
the numbers are compared - if they don't match, it is assumed the record
been modified by another user/thread/activity. An error is returned to the client stating the data has been changed.
if the numbers match, the record is updated with the number field being incremented by one.
what is this methodology called (beside crap :-) )
thanks
tony
Hello, perhaps you guys have heard this before in the past, but here iswhat I'm looking for.I have a SQL 2000 table with Social security numbers. We need tocreate a Member ID using the Member's real SSN but since we are notallowed to use the exact SSN, we need to add 1 to each number in theSSN. That way, the new SSN would be the new Member ID.For example:if the real SSN is: 340-53-7098 the MemberID would be 451-64-8109.Sounds simply enough, but I can't seem to get it straight.I need this number to be created using a query, as this query is areport's record source.Again, any help would be appreciated it.
View 9 Replies View Related
Hi
I have a drop down list which gets populated from database but I want to add the default value as
--Select----
This seems like a basic question but I have not been able to find the answer in the help files or by searching this forum.
Is a trigger called for each row updated or is it called once for all rows updated?
for example if I have:
Code Snippet
CREATE TRIGGER mytrigger
ON mytable
AFTER UPDATE
AS
BEGIN
EXEC e-mail-me inserted, N'mytrigger', getdate()
END
and I do this
Code Snippet
UPDATE mytable
SET mycolumn = N'whatever'
WHERE ID > 5 AND ID <= 10
Assuming there is a record for each nteger value of ID, than will mytrigger run 5 times (once for each row updated) or one time (with inserted containing all 5 rows)?
Hi,
When a column is evaluated against an UDF in a SELECT ... or WHERE ... It makes sense that the UDF is called for every row of the SELECT. But is it still true if the UDF is called in a subquery as below?
Code Snippet
SELECT LineID, AnyText FROM dbo.UdfTest WHERE LineID IN (SELECT LineID FROM dbo.F_Bogus())
I've made a test and the SQL Profiler Trace shows that the UDF is called only once.
Can anyone confirm if my test is valid and most importantly that the UDF is called only once? FYI, I never use sub queries. This is to clarify a technical detail for our performance investigation.
Thank in advance for any help.
Here is the code to setup the test:
USE NorthWind
GO
CREATE TABLE dbo.UdfTest (
LineID int Identity(1,1) NOT NULL,
AnyText varchar(200) COLLATE database_default NOT NULL
)
GO
INSERT dbo.UdfTest (AnyText) VALUES ('Test1')
INSERT dbo.UdfTest (AnyText) VALUES ('Test2')
INSERT dbo.UdfTest (AnyText) VALUES ('Test3')
GO
CREATE FUNCTION dbo.F_Bogus (
)
RETURNS @tab TABLE (
LineID int NOT NULL,
AnyText varchar(100) COLLATE database_default NOT NULL)
AS
BEGIN
INSERT @tab (LineID, AnyText) VALUES (1, 'UDF1')
INSERT @tab (LineID, AnyText) VALUES (2, 'UDF2')
INSERT @tab (LineID, AnyText) VALUES (3, 'UDF3')
INSERT @tab (LineID, AnyText) VALUES (4, 'UDF4')
INSERT @tab (LineID, AnyText) VALUES (5, 'UDF4')
RETURN
END
GO
Here is the capture of SQL Profiler when executing the statement:
SELECT LineID, AnyText FROM dbo.UdfTest WHERE LineID IN (SELECT LineID FROM dbo.F_Bogus())
SQL:BatchStarting SELECT LineID, AnyText FROM dbo.UdfTest WHERE LineID IN (SELECT LineID FROM dbo.F_Bogus()) 51 2008-05-06 17:58:31.543
SQLtmtStarting SELECT LineID, AnyText FROM dbo.UdfTest WHERE LineID IN (SELECT LineID FROM dbo.F_Bogus()) 51 2008-05-06 17:58:31.543
SPtarting SELECT LineID, AnyText FROM dbo.UdfTest WHERE LineID IN (SELECT LineID FROM dbo.F_Bogus()) 51 2008-05-06 17:58:31.577
SPtmtCompleted -- F_Bogus INSERT @tab (LineID, AnyText) VALUES (1, 'UDF1') 51 2008-05-06 17:58:31.577
SPtmtCompleted -- F_Bogus INSERT @tab (LineID, AnyText) VALUES (2, 'UDF2') 51 2008-05-06 17:58:31.577
SPtmtCompleted -- F_Bogus INSERT @tab (LineID, AnyText) VALUES (3, 'UDF3') 51 2008-05-06 17:58:31.577
SPtmtCompleted -- F_Bogus INSERT @tab (LineID, AnyText) VALUES (4, 'UDF4') 51 2008-05-06 17:58:31.577
SPtmtCompleted -- F_Bogus INSERT @tab (LineID, AnyText) VALUES (5, 'UDF4') 51 2008-05-06 17:58:31.577
SPtmtCompleted -- F_Bogus RETURN 51 2008-05-06 17:58:31.577
SQLtmtCompleted SELECT LineID, AnyText FROM dbo.UdfTest WHERE LineID IN (SELECT LineID FROM dbo.F_Bogus()) 51 2008-05-06 17:58:31.543
SQL:BatchCompleted SELECT LineID, AnyText FROM dbo.UdfTest WHERE LineID IN (SELECT LineID FROM dbo.F_Bogus()) 51 2008-05-06 17:58:31.543
My package inserts rows into tables with triggers.
The triggers are not being called.
What might be causing this?
Cann someone pls tell me how to create a database in visual studio 2005. :o
View 6 Replies View RelatedI have an application that creates a new database during installation,and I've noticed some strange behavior. Once I've created thedatabase, I am able to immediately create tables and populate lookupdata, provided I remain connected to the server. If, however, Idisconnect and attempt to reconnect immediately, I'll get an errorsaying that my login is invalid for the new database.I can get around this by having my code simply wait 5 seconds beforeattempting to reconnect, but I'm curious to see if anybody here cangive an explaination for why this is happening. Here is a bit ofpseudo code to explain what I'm seeing:open new connectioncreate databasecreate tablespopulate tablesclose connection// open new connection /* can't do this yet, as it would break */for (int a=0; a<5; a++){Thread.Sleep(2000)try{open new connectionbreak;}catch{Debug("still waiting...");}}Running my version of this code, I'll see that "still waiting..."message go past 2-3 times before SQL Server wakes up and realizes thatI'm allowed to connect to it. Anybody know why?Thanks,Jason KesterExpat Software Consulting Serviceshttp://www.expatsoftware.com/---Get your own Travel Blog, with itinerary maps and photos!http://www.blogabond.com/
View 1 Replies View RelatedHi,
I'm using Visual Studio 2005 Pro and SQLExpress that comes with it.
I have my program running fine in XP Pro OS using a window user "Glen" (Computer administrator) with Administrator rights. This means that I installed VS 2005 using this window user "Glen"
I created another windows user "TestUser" (Limited account) in the same physical PC.
I tried to run the program and on the part that I need to access SQL table, I got the error [CREATE DATABASE permission denied in database 'master']
At the same time while using "TestUser" and running sqlcmd (to check if I can connect to SQL), I also got error HResult 0x2, Level 16, State 1.
I read alot on MSDN discussions and related links but it seems that I can't get the solution that I need.
SO HERE ARE MY QUESTIONS :
1. Am I allowed to run my program using user "TestUser" since SQL is installed using "Glen" windows user?
2. Do I need to add access rights to "TestUser" to allow the user to have CREATE rights? (Note : for security reason, I can add other access rights except Administrator)
Thanks in advance for all you help.
Hello everyone:
I have some nightly jobs that execute stored procedure to call the tables? I want to know which table are called by these stored procedures. Is it possible? Any idea will be appreciated.
Thanks
ZYT
I am encountering an issue which is effecting our production environment, none of our sql jobs are now saving any kind of job history, nor are the status of the jobs being saved.
I have run a profiler trace and it seems as if procedure : sp_sqlagent_log_jobhistory is not being called during the execution of any of our jobs
Has anyone else encountered anything similar?
Hi
In a stored procedure the following code snippet 1 checks against duplicate data being inserted. I've tested it with snippet 2 and it works as expected. However, when the procedure is called from ASP.NET the check seems ineffective. I still get the error msg. The application uses a SqlDataSource with the following parameters.
Any suggestions?
Thanks,
Bakis.
PS I want to ask a question on the ASP.NET forum .The login/pwd for this forum "get me in" to the .net forum in the sense that when I log in I see a logout link. I don't get an "ask a question" button though. Is there a separate screening for each forum?
<UpdateParameters>
<asp:Parameter Name="CatItemUID" Type="Int32" />
<asp:Parameter Name="CatName" Type="String" />
<asp:Parameter Name="Item" Type="String" />
<asp:Parameter Name="Quad" Type="Int16" />
<asp:Parameter Name="UID" Type="Int64" />
</UpdateParameters>
snippet 1 :
if (@CatItemComboExists > 0 )
BEGIN
--print @CatItemComboExists
return 0
END
snippet 2:
begin tran
declare @return_status int
EXECUTE @return_status = spUpdateCatItemRec 343, 'blah','blih', 2,3
print @return_status
rollback
error msg only if proc is called from app
Violation of UNIQUE KEY constraint 'IX_tblCatItemUID'. Cannot insert duplicate key in object 'tblCatItemUID'.
I am a beginner in learning SQLServer 7.And I learn it by using its own help file.
The only problem troubles me is that after I do some options in database diagram,there's nothing happen.And then no matter what I do,database diagram panel is always blank.
Hi.. i have created a project which uses SQL 7 as a database and have finished it. MY supervisor wants me
to create a wizard that will be able to install the database on any NT 4.0 machine. How do i start and go about doing
it
Please help
thanks