User Created DBs
Jul 1, 2006Hi,
How can i get the names of user created databases?
select Distinct (name) from sysobjects where xtype = 'U', i know this statement for tables but is there a statement for Databases?
thanks
ADVERTISEMENT
SQL Security :: How To Copy Permission Of A User To Newly Created User
Oct 24, 2015How can i assign permissions to a newly created users as of an existing user?
View 3 Replies View RelatedUser Created Sql Queries
Mar 18, 2008Hi Folks,
I am trying to make it that a user can just click on my form and generate a custom sql query.
for example:
If I had three list boxes,I would like my user to be able to click on any of the items in the three boxes and come up with a datagrid displaying the query results.(in effect creating an individual query
i.e
ListBox1
robert redford
dustin hoffman
steve mc queen
ListBox2
butch Cassidy and the Sundance Kid
the Sting
straw dogs
pappilon
List 3
1965
1966(through to)
2008)
So then I want my user to come along click on Dustin Hoffman then click on a film(the sting perhaps) then the year,to find out if Dustin was actually in that film in that particular year...and if he was produce the details.
This will create a "custom" sql statement like
select * from lst 1 ...ect.
What I would like to know is can this actually be done.
This is only a small example of the datbase.I have thousands of films and name ect so it can get quite big.
any help fully appreciated
Gracie
User Groups Not Created
Mar 5, 2008User groups not created by SQL Server 2005 in Logins
SQLServer2005MSFTEUser$ComputerName$MSSQLSERVER
SQLServer2005MSSQLUser$ComputerName$MSSQLSERVER
SQLServer2005SQLAgentUser$ComputerName$MSSQLSERVER
Can these be created. If yes, How?
Dropping User Created Tables From Master Db
Aug 25, 2006Hi,
I have created a bunch of tables in the Master db by mistake.I want to drop those tables.Please tell me away to drop those.
Thanks!!
SQL 2012 :: Listing User Created Objects?
Oct 7, 2015I'm trying to list everything (tables, view, procedures, functions, etc.) that was created by users in a database.
The query which seems to eliminate the most SQL system type objects is shown below.
SELECT *
FROM sys.all_objects SAO
WHERE SAO.is_ms_shipped = 0
order by SAO.type, SAO.name
This still includes some non-user created objects, like the below. See the attachment for details.
fn_diagramobjects
sp_alterdiagram
sp_creatediagram
How can I get rid of these type of objects without filtering on SAO.name LIKE...
SQL 2012 :: Database Schema Creation Date And User Who Created It
Nov 12, 2014Query to find the date/time when a database schema was created and who created it.
View 2 Replies View RelatedSQL Security :: Log Shipped Database Server User SID Is Created Automatically
Jun 11, 2015We have a production server "prod" which has a user say 'test' that lets the users connect to the application and a logon trigger which stops them from connecting to the server through SSMS. I log ship 'prod' to the 'rep' database and the user 'test' is obviously created in the logshipped database 'rep' during the logshipping.
Now the login 'test' does not have any login from server 'rep', But people can still login to the 'rep' server and query the 'rep' db . I checked with SUSER_SNAME and found the SID of the user in rep server which I never created and which is not even present in the login names, Even If I create a new username in the 'prod' db, after logshipping the new user is replicated in the 'rep' server.
SQL Server 2012 :: Finding What Table A User-created Statistics Is Located?
Feb 21, 2014I can easily find user created stat in a databaseSELECT * FROM DB.sys.stats WHERE user_created=1But how do I determine what tables those stats are in? with over 6000 tables I don't feel like looking through all the tables.
View 2 Replies View RelatedFetch Data Of User Who Have Created Profile Within 7 Days - DateDiff Function
May 2, 2015I have added one webpage designed in ASP.Net with C# and sql server 2005 as database. There is table for user registration in which there is a column for ProfileCreationDate the data type of that column is date time .
I would like to fetch data of those user who have created profile within 7 days. For getting desired result I am trying this query.
select Name ,Profession,ProfileCreationDate from tblRegistration where DATEDIFF ( Day , '" + System.DateTime.Now + "',ProfileCreationDate)<7 order by ProfileCreationDate DESC
System.DateTime.Now is a function for getting current date time in C#
The query is neither giving error nor giving desired result.
How To Show The User Selected Values In Page Header By Using Parameters Which I Have Already Created In The Report
Dec 10, 2007Hi ,
I am working on Sql server Reporting Services(Sql Server 2005),
i have designed a Report and deployed that report on Report Server, on this report i need to show the user selected values
in page header by using Parameters which i have already created in the Report.
There are 35 to 40 fields in the Front End(Asp.net2.0)
on passing all these values from front end Report is prompting for parameters which is not desired for my case even though i have made ShowParameterPrompts to false.
Thanks in advance
Srinivas Govada
Get New Database Created Then Running Script To Created Tables / Relationships
Jun 29, 2015trying to get a new database created then running a script to created the tables, relationships, indexes and insert default data. All this I'm making happen during the installation of my Windows application. I'm installing SQL 2012 Express as a prerequisite of my application and then opening a connection to that installed SQL Server using Windows Authentication.
E.g.: Data Source=ComputerNameSQLEXPRESS;Initial Catalog=master;Integrated Security=SSPI; Then I run a query from my code to create the database eg: "CREATE DATABASE [MyDatabaseName]".
From this point I run a script using a Batch file containing "SQLCMD....... Myscriptname.sql". In my script I have my tables being created using "Use [MyDatabaseName] Go CREATE TABLE [dbo].[MyTableName] .....". So question is, should I have [dbo]. as part of my Create Table T-SQL commands? Can I remove "[dbo]."? Who would be the owner of the database? If I can remove the [dbo]., should I also remove dbo. from any query string from within my code?
Failed To Generate A User Instance Of SQL Server Due To Failure In Retrieving The User's Local Application Data Path. Please Make Sure The User Has A Local User Profile On The Computer. The Connection Will Be Closed
Dec 7, 2006This is my first time to deploy an asp.net2 web site. Everything is working fine on my local computer but when i published the web site on a remote computer i get the error "Failed to generate a user instance of SQL Server due to failure in retrieving the user's local application data path. Please make sure the user has a local user profile on the computer. The connection will be closed" (only in pages that try to access the database)
Help pleaseee
Rollback Will Drop Created Tables And Drop Created Tables In Transaction..?
Dec 28, 1999Hi folks.
Here i have small problem in transactions.I don't know how it is happaning.
Up to my knowldge if you start a transaction in side the transaction if you have DML statements
Those statements only will be effected by rollback or commit but in MS SQL SERVER 7.0 and 6.5
It is rolling back all the commands including DDL witch it shouldn't please let me know on that
If any one can help this please tell me ...........Please............
For Example
begin transaction t1
create table t1
drop table t2
then execute bellow statements
select * from t1
this query gives you table with out data
select * from t2
you will recieve an error that there is no object
but if you rollback
T1 willn't be there in the database
droped table t2 will come back please explain how it can happand.....................
Email Address:
myself@ramkistuff.8m.com
How Do We Determine Which User Database Tables Are Mostly Retrieved By User Or Modified By User?
May 22, 2008
Hi,
Please give the T-SQL script for this ? Thanks
Shanth
Generating User Instances In Sql Server Is Disabled. Use Sp_configure User Instances Enabled To Generate User Instances.
Sep 28, 2007When I am in Visual Studio 2005, and I try to add an SQL database, I get the following error "generating user instances in sql server is disabled. use sp_configure user instances enabled to generate user instances." I am currently using SQL server 2005 Express. What do I need to do, to create an SQL database? Thanks in advance.
View 4 Replies View RelatedJob Created Using SQL DMO Cannot Be Run
Mar 22, 2001I've created a job using SQL DMO (in VB6) but it fails to run. When try to manual start the job from the Enterprise Manager, it gives error "cannot start job because it does not have any job server(s) defined."
Here is the code :
Dim oSQLServer As New SQLDMO.SQLServer
Dim oJobServer As New SQLDMO.JobServer
Dim oJob As New SQLDMO.Job
Dim oJobSch As New SQLDMO.JobSchedule
Dim oJobStep As SQLDMO.JobStep
oSQLServer.Connect "mc", "sa", ""
oJob.Name = "JOB_Backup"
oSQLServer.JobServer.Jobs.Add oJob
Set oJobStep = New SQLDMO.JobStep
With oJobStep
.Name = "Step 1"
.StepID = 1
.DatabaseName = "master"
.SubSystem = "TSQL"
.Command = "BACKUP DATABASE [medco] TO DISK = 'E:Projectmedco.BAK' With INIT"
.OnFailAction = SQLDMOJobStepAction_QuitWithFailure
.OnSuccessAction = SQLDMOJobStepAction_QuitWithSuccess
End With
oJob.JobSteps.Add oJobStep
oJob.StartStepID = 1
'Set the schedule.
With oJobSch
.Name = "Schedule 1"
.Schedule.FrequencyType = SQLDMOFreq_Daily
.Schedule.FrequencyInterval = 1
.Schedule.ActiveStartDate = "20010315"
.Schedule.ActiveStartTimeOfDay = "150800"
.Schedule.ActiveEndDate = SQLDMO_NOENDDATE
.Schedule.ActiveEndTimeOfDay = SQLDMO_NOENDTIME
End With
oJob.JobSchedules.Add oJobSch
oSQLServer.DisConnect
Set oJobStep = Nothing
Set oJobSch = Nothing
Set oJob = Nothing
Set oSQLServer = Nothing
Something is missing but ?
Thanks for all your helps in advance,
Ben
Error : Cannot Open User Default Database. Login Failed. Login Failed For User 'server/user'
Nov 22, 2007
i'm using the Enterpirse library logger to write logs into a database.
When choosing connection string i choose the database i want in the "connection properties" dialog box and
push 'Test connection' button.
everything goes well.
then i open the SQL Server Management studio express and connect to the databse to check some things,
from that point on , when i push the 'Test Connection' button in the Enterprise library i get the error:
"cannot open user default database. Login failed. login failed for user My'server/MyuserName'"
even when i close the sql server manager , it is still stuck - the connection test doesn't work anymore....
it only work when i restart the computer.
why ?
Need Identity Value Of New Row Created
Jan 6, 2004I'm buildng an application using the ADO.NET OleDB providor in order to maintain back end compatabiity with both SQL Server and Access Jet4.0. I need to get the Identity key of the new row created as a result of the following insert statement.
OLEDB COMMANT TEXT:
"insert into tblPerson (LastName,FirstName,SSN,BirthDate,Family) values( ?, ?,?,?,?)";
I assume I need to add a select statement immediately following the insert. Can anyone help?
PS - not using stored procedures due to Access jet4.0 limitation
How Leave Db With 1 Ldf While It Was Created With 2 Ldf ?
Jan 18, 2002I want to remove 2 big ldf files (400 mgb and 300 mgb) and start db with only one ldf.
I tried sp_attach_db specifing only mdf file but geting errors
-------
microsoft help PRB: sp_attach_single_file_db Does Not Work for Databases with Multiple Log Files (Q271223)
..... You cannot attach a database that has been created with multiple log files without also attaching all the log files.
----
how can I remove those big ldf files and create new ldf (defult(1mgb)
Why Are My Tables Not Being Created?
May 7, 2008This procedure runs and I see my GM names come up one at a time but I get no tables created.
Sorry about the sloppy code - I'm not a real pro.
declare @@GM Char(100)
declare @SQL VarChar (2000)
Declare spot cursor scroll for
select GM from BIM_Historical_Performance.dbo.Performance_Master
open spot
fetch first from spot
into @@GM
While @@Fetch_Status =0
BEGIN
set @SQL = 'select Comp, Billto, Cust_name as Customer, Branch, BAC, [MgMt_Type], BondValue as Bondbucks , BondValueAlloc as Allocbucks, c1 as Curcode, u1 as CurUnderP$, s1 as Cur3mthBIMsales, p1 as performance, I1 as Inside, [IS_since] as Insidesince, O1 as Outside, [OS_since] as Outsidesince, c2 as CodeM-1, c3 as CodeM-2, c4 as CodeM-3, c5 as CodeM-4 ,c6 as CodeM-5, GM into ' +@@GM + ' from BIM_Historical_Performance.dbo.Performance_Master where BIM_Historical_Performance.dbo.Performance_Master.gm = ' +@@GM
EXEC (@Sql)
Fetch NExt from spot
End
HELP:I Want To Know Who Had Created The New Tables
Oct 31, 2005I want to know who had created the new tables,How can I achieve it?
Can I know it by using the sysobjects table?
How Can I Know Who Had Created The Table In My DB?
Dec 20, 2005How Can I know who had created the table in my DB?
May I log it?
No Tables Created....
Feb 20, 2008Hi everyone.. I have a big problem.. :p
I have created a installer for my application and database.
When I run the installer it only creates the database but no tables created. But in some other workstation it works fine. I dont know what causes this problem....
Please guide me.
RON
________________________________________________________________________________________________
"I won't last a day without SQL"
Cursor Isn't Being Created.
Aug 15, 2007ElementTypeDep_Cursor seems is not being created in this stored procedure:
Code Snippet
ALTER procedure spCopyTemplateElementTypesToIssues
@TemplateRecno integer,
@ProjRecNo integer,
@IssueRecNo integer
as
declare @ElementTypeRecno integer
declare @ElementTypeDepRecno integer
declare @ProjTypeRecno integer
declare @PreElementRecNo integer
declare @PostElementRecno integer
declare @Count integer
DECLARE element_Cursor CURSOR FOR
SELECT ElementTypeRecNo
FROM dbo.tblTemplateElementType
where TemplateRecno = @TemplateRecNo
OPEN element_cursor
FETCH NEXT FROM Element_Cursor into @ElementTypeRecno
--delete from tblElementCPO
WHILE @@FETCH_STATUS = 0
BEGIN
select @count = count (*)
from tblElementCPO
where ProjRecno = @ProjRecNo
and IssueRecno = @IssueRecno
and TemplateRecno = @TemplateRecno
and ElementTypeRecno = @ElementTypeRecNo
if @Count = 0
begin
insert into tblElementCPO(Ignore, ElementTypeRecno, IssueRecno,
ProjRecno, MaxAttemptNum, IsMileStone, ComponentOnly, Phase,
TaskHoursEst, TemplateRecNo, ChangeDate, ChangePerson)
values (0, @ElementTypeRecno, @IssueRecno,
@ProjRecno, 5,0,6,99,
99,@TemplateRecno, getdate(), current_user)
end
FETCH NEXT FROM element_Cursor into @ElementTypeRecno
END
CLOSE element_Cursor
DEALLOCATE element_Cursor
select @Count = count (*)
FROM dbo.tblElementTypeDep
where TemplateRecno = @TemplateRecNo
if @Count > 0 then
begin
DECLARE ElementTypeDep_Cursor CURSOR FOR
SELECT ElementTypeDepRecNo, PreElementTypeRecNo, PostElementTypeRecno
FROM dbo.tblElementTypeDep
where TemplateRecno = @TemplateRecNo
OPEN ElementTypeDep_cursor
FETCH NEXT FROM ElementTypeDep_Cursor
into @ElementTypeDepRecno, @PreElementRecNo, @PostElementRecno
WHILE @@FETCH_STATUS = 0
BEGIN
select @Count = count (*)
from tblElementDepCPO
where ElementTypeDepRecno = @ElementTypeDepRecno
and PreElementRecNo = @PreElementRecNo
and PostElementRecno = @PostElementRecno
if @Count = 0
begin
insert tblElementDepCPO (ElementTypeDepRecno, PreElementRecNo,
PostElementRecno, ChangeDate, ChangePerson)
values (@ElementTypeDepRecno, @PreElementRecNo,
@PostElementRecno, getdate(), current_user)
end
FETCH NEXT FROM ElementTypeDep_Cursor
into @ElementTypeDepRecno, @PreElementRecNo, @PostElementRecno
END
CLOSE elementTypeDep_Cursor
DEALLOCATE elementTypeDep_Cursor
end
called by
Code Snippet
Dim cnQI02414 As New SqlConnection(My.Settings.csQI02414Dev)
Dim cmd As New SqlCommand
Dim reader As SqlDataReader
cmd.CommandText = "spCopyTemplateElementTypesToIssues"
cmd.CommandType = CommandType.StoredProcedure
Dim spmTemplateRecNo As SqlParameter = _
cmd.Parameters.Add("@TemplateRecNo", SqlDbType.Int)
spmTemplateRecNo.Value = _
Me.cbTemplate.SelectedValue
Dim spmProjRecNo As SqlParameter = _
cmd.Parameters.Add("@ProjRecNo", SqlDbType.Int)
spmProjRecNo.Value = _
Me.cbProject.SelectedValue
Dim spmIssueRecNo As SqlParameter = _
cmd.Parameters.Add("@IssueRecNo", SqlDbType.Int)
spmIssueRecNo.Value = _
Me.cbIssue.SelectedValue
cmd.Connection = cnQI02414
cnQI02414.Open()
reader = cmd.ExecuteReader()
' Data is accessible through the DataReader object here.
cnQI02414.Close()
What should I be looking for?
BCP - File Is Not Getting Created
Nov 5, 2007
Hi all,
I have been trying to extract table data into file but its not getting extracted. I am using following command to run.
exec spGetIPAddress @ip = @servername output
select @SQL = 'bcp '
+ char(34)
+ ' select Field from ['
+ @DatabaseName
+ '].[dbo].Temp_ExtractSR39'
+ ' order by Field'
+ char(34)
+ ' queryout '
+ @RenamePathAndFile
+ ' -T -c -CACP -S'
+ @servername
exec master..xp_cmdshell @SQL, no_output
Is there any setting need to be done at database level? or any setting for Sql server?
Thanks in adavance,
Anshu
What To Do With Pkgs Once Created In BIS?
Mar 6, 2007Hi,
I have been in the process of creating some new pkgs in BIS. My question is, once I am done writing the pkgs, what next?
That is, how do I get the pkgs into SQL Server, and how do I schedule them to run?
Also, what if I have the following scenario:
I have created a new SSIS pkg that has several EXECUTE DTS 2000 tasks in them. The DTS packages that are called from the EXECUTE DTS 2000 task are stored in the database under "Legacy". These should still run OK once I "load" the packages to SQL Server 2005, yes?
Where do the SSIS packages "live" in SQL Server 2005? That is, where can I view them.
Thank you!!
UDT Not Being Created At Subscriber
Nov 10, 2005Hi,
View 4 Replies View RelatedDatabase Is Not Being Created
Apr 10, 2006Hi there.
I have generated an SQL Script to script me a database, stored procs, tables, foriegn keys and users, as well as Create command and drop objects.
however it seems that it is not creating the database, if an existing database does not exist, or if it does after it has been dropped. I get this error:
Could not locate entry in sysdatabases for database 'db_name_something'. No entry found with that name. Make sure that the name is entered correctly.
This is the SQL:
/****** Object: Database SafeHands Script Date: 10/04/2006 02:05:30 ******/
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'db_name_something')
DROP DATABASE [db_name_something]
CREATE DATABASE [db_name_something] ON (NAME = N'db_name_something_Data', FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQLdatadb_name_something_Data.MDF' , SIZE = 1, FILEGROWTH = 10%) LOG ON (NAME = N'db_name_something_Log', FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQLdatadb_name_something_Log.LDF' , SIZE = 1, FILEGROWTH = 10%)
COLLATE SQL_Latin1_General_CP1_CI_AS
..
..
any ideas where I am going wrong?
I know that I do not have the "GO" command, however I am trying to run the entire script from C# 2.0, using SQL Server 2000 SP4, so I have read the entire script into a string array, and split each line from the word "GO" as otherwise, I would get an error.
Many thanks
Dependency Not Created
Dec 5, 2007--The Dependency row is not created!
-- I have a procedure "Example 1".
-- When I look at the sys.sql_dependencies I didn't find a row with Table1 dependency.
--If I write the procedure like "Example 2" the dependency Table1 exists in sys.sql_dependencies.
--
--DEFENENTLY A BUG. CAN YOU FIX IT?
--I REALLY NEED IT!!!!!!
Create table Table1 (id int);
GO
--Example 1
CREATE procedure a
as
create table #Test(id int)
Insert into #test
Select id from Table1
DROP TABLE #test;
GO
Select * from sys.sql_dependencies where object_id=object_id('a');
GO
--Example 2
CREATE procedure c
as
Select id into #test from Table1
DROP TABLE #test;
GO
Select * from sys.sql_dependencies where object_id=object_id('c');
DROP PROCEDURE c
DROP PROCEDURE a
DROP TABLE Table1
Where Is The Package I Just Created?
Jul 19, 2007I loaded the import/export wizard into my Tools menu. I ran it and exported a view to an excel worksheet with mixed results (I got the headings, but no data). I want to take a look at the package that should have been created (I selected that option, but the export didn't complete and I couldn't decipher the log to determine whether the package was created).
If it was successfully created, where would I find it in SQL 2005 Express?
Thanks,
Bill
Date Created Defualt Value
Jul 1, 2007in my database i have a "datecreated" feild, its datatype is "datetime", is it possible to set the defualt value as the time now? if so what do I put in the defualt value property? thanks a million si!
View 2 Replies View RelatedHow To Notify A DBA When An Object Is Created ?*V *?
Aug 14, 2000Hi Guys,
Is there a way to notify DBA when a new stored procedure,table,view is created by any user...
Help is greatly appericated..
Jessi..