I have SQL 2008 R2 version. The server has enough disk space where the SQL is running. But the log file is not refreshed. in other words a new sql server log file does not create a new file if the old one is full.
View 4 RepliesI've installed Reporting Services 2008R2 STD edition on my local system. I was trying to configure the Report Server, I am able to connect to the Report Server but the ReportDB and ReportTempDB are getting created in Database Engine instead of Report Server, when I connect to Report Server from SSMA this is what I see (Attached) Screen 1.
The screen2 is of Reporting Service Config Manager, When I connect to the report server using the URL what I am seeing is blank last screen shot attached.
I am looking to create a server wide trigger which will fire whenever a database gets created, I would like to know if there are any gotchas or anything that I need to look out for/test specifically.
The idea is to automatically grant some permissions and take some other actions whenever a database gets created.
I use below trigger to email me when a database is created or dropped.
CREATE TRIGGER [DDL_CREATE_DATABASE_EVENT]
ON ALL SERVER
FOR CREATE_DATABASE
AS
DECLARE @bd VARCHAR(MAX)
DECLARE @tsql VARCHAR(MAX)
SET @tsql = EVENTDATA().value
[Code] ...
Is there a way we can get notification when a table is created or altered or dropped ?
The views are in XYZ production database and user needs the list of indexes on the tables on which the views has been created.
query to find list of indexes on the tables on which the views has been created.
If I create a memory table inside a while loop, I expect that every loop the memory table is (newly) created and thus empty.
But when I test this, I see that on the second, third, ... run the inserted data in the memory table is still present and not cleared.
This can be reproduces with the following code:
DECLARE @tbl TABLE
( [id] BIGINT )
DECLARE @tbl2 TABLE
( [value] BIGINT )
INSERT INTO @tbl
VALUES (1), (2)
[Code] ....
/*
Expectation: twice 11 and 12 in @tbl2
Actual result: three times 11 and 12
*/
SELECT *
FROM @tbl2
Is this a wrong assumption or is this a bug in SQL Server? (Tested on: SQL Server 2014 and 2008).
CREATE TABLE [dbo].[Access](
[AdminID] [int] NOT NULL,
[FnID] [int] NOT NULL,
CONSTRAINT [PK_AccessFn] PRIMARY KEY NONCLUSTERED
[Code] ....
why the cluster and non cluster indexed created on the same key?
trying 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?
We have just installed SQL and C# Express. We have lots of experience with SQL, but none with C#. With both the SQL and C# apps running; if we create a new table, view etc, we cannot see that new object from C# only the objects that existed when we opened the apps for the first time (each time).
If we close everything and re-open everything from scratch, the new objects show..!?!?
Thank you for any information on this difficult problem.
Hi,
Does any has a script to track how many objects created on a server on all databases with in specified date range?
Appreciate your help.
Thanks
The app users getting terminated after getting the following message.
Exception: XML document could not be created because server memory is low. Use sp_xml_removedocument to release XML documents
The SQL platform is SQL 2005 64bit version running on Windows 2003 SP2.
Was wondering if anyone else has had issues like this and if so what could be the possible resolution.
Thanks,
vkumar
On our SQL 2008, we'd like to receive a mail when a DB is created, and, if possible get the name of the newly created database IN the mail.
'till now get get mail I didn't find out how to get the name of the DB in the mail... should I use WMI ?
My database mail is configured and I created a WMI event alert with this query:
SELECT * FROM CREATE_DATABASE
+ Additionnal message in the options of the alert...
But I don't know how to get the name of the newly created db unfortunately.
I attached .mdf and .ldf from the app_data folder in an asp.net web site. I set the configuration to SSPI. The trouble is, I get this error
requested by the login. The login failed.
Login failed for user 'IN-XYZmattaniah'.
If I create a database and connect to it using SSPI I do not get this error. I assume there is something I am missing.
Well you would think it was pretty simple. If I create a Database on Server a last week, take a backup of that database and put it on server b today, the CRDate in sysdatabases still shows the date of last week instead of today. I need to find the actual date for all 150+ servers from sql 7 - sql 2005 in our domain. Any ideas? I had heard that maybe SQL DMO had an object that might be helpfull but I have not been able to look it up anywhere or see how to use it using sp_oa procs.
Any help would be greatly appreciated.
Hi there,
Background: I've created an application to run on windows mobile 5.0 devices. It replicates to a publication on a server. To do this it goes through ASP.net. This creates the database on the Mobile Devices.
Issue: Problem is the initial download of the database is taking 2hours plus to replicate (create) for the first time. So I'm trying to create the .sdf file on the server, zip it, and send it across to the handheld. All subsequent replications take about 5 minutes.
Question: Does anyone know how, or have examples for creating a Mobile CE database on the server. I need to create it using ASP.net and go through a pre-existing publication where a hostname used for filtering.
http://msdn2.microsoft.com/en-us/library/ms173009.aspx explains how to create this in SQL Server Management Stuidio. How do you do the same thing over ASP?
Can this be done using SMO? How do you create a SQLServerCompactEdition database using normal .NET Framework? Thanks for your help!!
Hi allI have a question concerning sql database mdf files. In the old days I would user a ms access database. This file would be stored with the actual web files and would utilise a dsn connection. I have noted when designing with vwd 2005 express it allows you to use 2 methods of creating a mdf database. You can either create it as an attachment mdf or you can create it directly using sql manager. My question is, if you create the mdf database as an attachement file can you store it in the same manner as if you where using a ms access database, meaning can you store it with the web site's files so it uses the file storage allocated size and then create a connection similar to a dsn (but for sql) to the isp's sql engine or does it have to be uploaded to the isp' s sql server.The reason for this question is some of my customers do not want to pay the extra cost to have an sql allocation, however I do not want to go back to using old asp methods to create advanced sites as I prefer using stored procedures. Any help will be appreciated
View 4 Replies View RelatedI am a new SQL Server developer using Visual Basic 6 Professional Edition. I am using ADO and SQLDMO to develop my application. The SQL Server is Version 7.0, SP2 and SP3 (two different servers).
I am having difficulty gaining access, using ADO, to databases I create using SQLDMO. I create the database, create the DBFILE object, create a login and attach it to the database, create a user, assign the created login to the user,
and assign the created user to the 'db_datareader', 'db_datawriter' and 'public' roles. the login object was set to 'standard', not NT. the SQL Server is set to 'mixed' NT -SQL Server login mode. Thw SQL Server is running under the 'system account'. Using SQL Server Enterprise Manager, everything looks OK. An attemp to connect using the ADO connection object fails, giving an error of Login failed for user 'engbom3admin'.
Connecting to the SQL Server using SQLDMO uses the 'sa' user name with a blank password.
I can successfully connect to the created database using the ADO connection object using the 'sa' user name and blank password. Using the SQL Server Enterprise manager, I cannot see anything at all different between the 'sa' and my created 'engbom3admin' user. I've manually set the created user to have 'db_owner' roles, etc. Same result. I'm stumped. I've read all I can gather from the Microsoft SQL Server books. I'm still stumped.
I would greatly appreciate any help, information or tips you could provide. Thank you in advance.
Sincerely,
Bob Wohlers
SVP, IS Datamax Corporation
Hi,
Instead of runing a report through the schedule that I have created, I am invoking the SQL Agent Job that has been created for the schedule of the report, using the system stored procedure sp_startjob. Is this a recommended approach? Are there drawbacks for this approach?
Subash
[URL] Following instructions from the above URL I have executed the script on master database (Source: SQL Server 2005). Later a new stored procedure is created in master database, executed Exec sp_help_revlogin, copied the output and executed the same on required instance (Destination: SQL Server 2008R2).
All the logins are successfully created but when I checked despite what the permissions these logins have in the source server all logins are created with public roles. Is it an expected behaviour with revlogins hence do we need to manually assign its role and map to its respective databases or am I missing anything in creating these logins.
I created a linked server on server A to server B as follows...
USE master;
GO
EXEC sp_addlinkedserver
'B',
N'SQL Server'
GO
...in mgt studio (in a connection to A) from my local where as a person with sys admin rights on both A and B I'm having no problems connecting to either and running queries. When I run a query from a connection on A as select * from .[a db on B] .[an owner on B].[a table on B], I get an error that says Login failed for user 'NT AUTHORITYANONYMOUS LOGON'.
I believe both A and B are in the same domain
the default behavior that I saw checked in the linked server's security properties was "connection will be made using the logins current security context".
when change the default and clicked and entered "this security context" as domainuserid and password, rerun the query, I get error Login failed for user 'domain nameuserid'.
is there a simple way to get this to work, at least for test purposes? I even tried playing with the login mappings but nothing seemed to work.
Using VWD 05 Express & SQL Server 05 Express, how do I transfer my database to my host that is using SQL Server 2005.
On the SQL Server express web site http://msdn.microsoft.com/vstudio/express/sql/powerful/ one of the features listed for SQL Server 05 Express is: Transfer any database created in SQL Express to other editions of SQL Server 2005.
Thanks
why my insert or into is not working in my SQL Server R2 2008. I have a code I am using on an existing table and trying to put the data in a brand new table but it keeps giving me an error
select mCid, caucasian, aa, api, aian, mr, his, max(val)
into memberrand
from MEMBERV2_RAND
cross apply (
select AA union all
select API union all
select AIAN union all
select MR union all
select HIS union all
select CAUCASIAN
) v(val)
group by mCid, AA, API, AIAN, MR, HIS, caucasian
;
The error is: Msg 1038, Level 15, State 5, Line 1..An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name.Do I have to actually create this table with no values first and then run the query? I was hoping this was sort of a make a table query
I run the following:
EXECUTE dbo.DatabaseBackup
@Databases = 'F1SB',
@Directory = 'F:SqlBackup2014',
@BackupType = 'FULL',
@Compress = 'Y',
@Encrypt = 'Y',
[code]...
I cannot see the file created in the directory. The account under which sql server the agen job run have full privileges on it and is sysadmin.Then i run the Command in ssms
BACKUP DATABASE [F1SB] TO DISK = N'F:SqlBackup2014<server>F1SBFULLIGS-DB01_F1SB_FULL_20150510_214455.bak' WITH NO_CHECKSUM, COMPRESSION, ENCRYPTION (ALGORITHM = AES_256, SERVER CERTIFICATE = [serverCertificate])
and I get this error message:
Msg 3013, Level 16, State 1, Line 13
BACKUP DATABASE is terminating abnormally.
I'm executing a sp name [uspGeneral_Getinfo]. It return a global temporary table's name and I want to get data from that table.
[192.168.2.11] = Linked Server
Declare@LinkedServer varchar(40)='[192.168.2.11].DBPharm.dbo.',@OutPutTableName varchar(50)=' ',@SQL nvarchar(max)Set@LinkedServer=Rtrim(Ltrim(@LinkedServer))Set@SQL=' Declare @OutPutTableName varchar(50),@SQL nvarchar(max) EXEC '+@LinkedServer+'[uspGeneral_GetDomainDataNew] 9,null, @OutPutTableName OutPut '+' Set @SQL='''+'Select * from '''+'+ @OutPutTableName'+' Exec sp_executesql @SQL 'Select@SQL
Above code are executing from another server [192.168.2.10].But getting an error that .I can't able to search where the temp table created in linked serve tempdb or ...?
Database name 'tempdb' ignored, referencing object in tempdb.
Database name 'tempdb' ignored, referencing object in tempdb.
Database name 'tempdb' ignored, referencing object in tempdb.
Database name 'tempdb' ignored, referencing object in tempdb.
Msg 208, Level 16, State 0, Line 38
Invalid object name '##table1FD1B81Bx4EAFx4FFDx9F6Fx15B77B6445F'.
I have created a database in Visual Studio 2005 by adding a new "SQL Database" item to my project, and then designing the tables with Server Explorer.
How can I add this database to my local instance of SQL Server Express, and to my SQL Server 2000 database engine?
We've been running a mirrored database (using certificates since we don't have a domain) and it's all working well. Last week we decided to add a witness for automatic failovers, but for some reason I just can not get the witness to connect to the Partner2 server.
See screenshot here
Please help me troubleshoot this - I re-created the endpoints / users / certificates but it's still not working. Where can I get more information on what exactly the problem is? Can I test the endpoints somehow?
I have set the environment set for AutoRecover (for every 3 minutes and Keep information for 7 days under the SSMS 2014 Menu: Tools -> Option ->Environment -> AutoRecover).
I've rebooted the box and restarted the SQL Server service and nothing seems to create the files.
I am new to SQL Programming. I am learning the basics. I am trying to create a simple query like this -
SELECT
Column_1,
Column_2,
Column_3,
10*Column_1 AS Column_4,
10*Column_2 AS Column_5,
-- I am not being able to understand how to do this particular step Column_1*Column_5 As Column_6
FROM Table_1
First 3 Columns are available within the Original Table_1
The Column_4 and Column_5 have been created by me, by doing some Calculations related to the original columns.
Now, when I try to do FURTHER CALCULATION on these newly created columns, then SQL Server does not allows that.
I was hoping that I will be able to use the Newly Created Columns 4 and 5 within this same query to do further more calculations, but that does not seems to be the case, or am I doing something wrong here ?
If I have to create a new column by the name of Column_6, which is actually a multiplication of Original Column_1 and Newly Created Column_5 "I tried this - Column_1*Column_5 As Column_6", then what is the possible solution for me ?
I have tried to present my problem in the simplest possible manner. The actual query has many original columns from Table_1 and many Calculated columns that are created by me. And now I have to do various calculations that involve making use of both these type of columns.
I run a query
select col1, col2, col3, col4
from Table
where col2=5
order by col1
I have a primary key on the column.The execution plan showing the clustered index scan cost 30% & sort cost 70%..When I run the query I got missing index hint on col2 with 95% impact.So I created the non clustered index on col2.The total executed time decreased by around 80ms but I didn't see any Index name that is using in the execution plan.After creating the index also I am seeing same execution plan
The execution plan showing the clustered index scan cost 30% & sort cost 70% but I can see the total time is reducing & Logical reads on that table is reducing.I am sure that index is useful but why there is no change in the execution plan?
We have a difficult requirement.
When records are initially created, they are given statuses of New, Pending, Closed
By default, the status is New.
If no action has been taken after 15 days since the records were created, users are sent a reminder.
I have this part working.
The problem is that as long as the status is not equal to closed, start sending reminders every 10 days.
Here is what I am working with:
SELECT [ID] , [Name], [Email],
Status
FROMmyTable
WHERE Status_Name != 'Closed' AND Status_Name IS NOT NULL and Status_Name = 'New' and Status='Pending'
AND CONVERT(Char,DateAdd(day,-15,getdate()),101) = CONVERT(Char,Date_Entered,101)
I am getting ready to start a project where I am charged with moving out old data from production into a newly created historical DB. We have about 8 tables that are internal audit tables, that are big and full of old data. These tables are barely used and are taking up way too much space and time for maintenance.
I would like to create a way (SSIS?) to look at the date field in each of the 8 tables and copy out anything older than two years into my newly created history DB. Then deleting the older records from the source DB.
I don't know if SSIS is the best method to use. If it is, what containers to use to move over data, then how to do delete from source?
Can I do the mass deletes on my audit source tables without impacting performance/indexes/fragmentation?
We 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.