I have a serious problem with either SQL Server or SQL Reporting Services. I have 7 tables in a database that are relational. I have created serveral reports against this database. Last week when I pulled one of the reports, I was missing information on one of the cells. The reason why I know this is because my boss has us verify the report against the database. That took about an hour to complete. When we found that the report was not correct I repulled the report and the cell that was empty, was now populated. What gives? This is the same report I have pulled for the last month and had no problems until now. Is there a time issue involved between the last update to the database to the time I pull the report? Or maybe there is something wrong with the query?
SELECT Lease.[Lease #], MineralContacts.[Mineral Owner First Name], MineralContacts.[Mineral Owner Last Name], Tract.County, Tract.[Tract Number],
Tract.[Tract Description], Tract.Section, Tract.Block, Tract.Survey, Tract.Abstract, Lease.[Lease Date], Tract.[Title Check Though], Tract.[2ndTitleReview],
Tract.[2nd R/S Complete], Tract.[Title Agent], Tract.[Tract Gross Acres], SubTract.[Tract Net Acres], Draft.[Draft Status], Draft.[Draft Due Date],
Draft.[Draft Amount], Draft.[Draft #], Draft.[ANB Invoice #], Draft.[Lse File Sent to CP], Draft.[Money Wired from CP to EA],
Draft.[STA Approved Draft for pmt], Draft.[STA Recommend to Return Daft], Draft.[KE Advised ANB to Pay Draft], Draft.[KE Advised ANB to Return Draft],
Draft.[Paid Draft Recd], SubTract.Comments, SubTract.Hide
FROM Tract INNER JOIN
SubTract ON Tract.TractID = SubTract.TractID INNER JOIN
Mineral ON SubTract.SubTractID = Mineral.SubTractID INNER JOIN
Lease ON Mineral.MineralID = Lease.MineralID INNER JOIN
Draft ON Lease.LeaseID = Draft.LeaseID INNER JOIN
MineralContacts ON Mineral.MineralID = MineralContacts.MineralID
WHERE (Draft.[Draft Status] IS NOT NULL) AND (Draft.[Draft Due Date] IS NOT NULL)
missing witness server information and the fail-over is broken suddenly? 4:00am no maintenance job. I have one sql job on 10pm for backup on database transaction log only.
I can see the primary have problem then perform fail-over to mirror database, the auto fail-over was broken.
I re-build the sql mirror is OK , but i want to find the root cause.
Windows application event was full when there have many failed event, i have increase log size for application event. Â
hye everyone, when i browse my web application the eror as below display.. what should i do.. any suggestion or tips...
"Cannot start your application. The workgroup information file is missing or opened exclusively by another user. 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.OleDb.OleDbException: Cannot start your application. The workgroup information file is missing or opened exclusively by another user. "
Hi all, I'm kinda new at programming, and am currently attempting to create a web application. I have an error when I try to insert information into a sql server database, and can't find how to fix it no matter how hard I try. The error I get is: Cannot start your application. The workgroup information file is missing or opened exclusively by another user. I have it so a user is logged in and is shown a form to fill out, then submits it (which creates an insert statement and sends it to the database). My connection string is as follows: CnnString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "data source=serverdestination;" + "User id=user;" + "Password=pass;"; If you need any more information, just let me know. Any help would be greatly appreciated!
Hi I am stuck while doing synchronization between SQL Server 2000 and SQL Server CE 2.0. getting an Error "Header information is either corrupted or missing."
My Specification is follow : Windows XP with SP2 Visual Studio 2005 Enterprise edition SQL Server 2000 (SP3a) SQL Server CE 2.0 Application is Smart Device application (Pocket PC 2003) using C#. Microsoft ActiveSync 4.5 IIS 5.1
I followed steps as per given in this URL : http://msdn2.microsoft.com/en-us/library/ms839425.aspx#sql_serverce_replication_net_topic2I performed eeach steps successfully, My code is as per given below.
I am not getting whats going wrong. My Firewall is disable and i have not installed any antivirus softwares (including Norton internet Security....in many forums i got this suggestion but i think its not an issue,even though for solving this issue i performed new installation of Windows XP SP-2 )
I'am doing functionality test on DTS packages and saving my DTS packages to meta data services instead of saving them as local packages. We would like to see what information would be provided by saving them this way, but when we try to open the meta data browser (the 3rd icon under DTS) we get the following error:
An error occurred while trying to access the database information. The msdb database could not be opened.
I actually work in an organisation and we have to find a solution about the data consistancy in the database. our partners use to send details to the organisation and inserted directly in the database, so we want to create a new database as a buffer database to insert informations from the partners then make an update to the main database. is there a better solution instead of that?
I'm having a weird problem with an easy process. I have a section that allows people to enter their name and email address if they wish to be contacted. I have it set up so that when they enter that information into the text boxes, the info is then sent to my SQL database. The code looks right to me, but it never comes up. Here is the code I have for the button_click.
Dim NTAdatasource As New SqlDataSource() NTAdatasource.ConnectionString = ConfigurationManager.ConnectionStrings("DatabaseConnectionString1").ToString() NTAdatasource.InsertCommandType = SqlDataSourceCommandType.Text NTAdatasource.InsertCommand = "INSERT into ContactUs (YourName, EmailAddress, DateTimeStamp) VALUES (@YourName, @EmailAddress, @DateTimeStamp)" NTAdatasource.InsertParameters.Add("YourName", txtYourName.Text) NTAdatasource.InsertParameters.Add("EmailAddress", txtEmailAddress.Text) NTAdatasource.InsertParameters.Add("DateTimeStamp", DateTime.Now) Dim RowsAdded As Integer = 0 Try RowsAdded = NTAdatabase.Insert() Catch ex As Exception Server.Transfer("problem.aspx") End Try
If RowsAdded <> 1 Then Server.Transfer("problem.aspx") Else Server.Transfer("success.aspx") End If End Sub
The strange thing is that when I debug, the "rowsadded" value comes up to 1. The process runs through debugging just fine and redirects me to my "success.aspx" page. What am I doing wrong?
I work with multiple servers and I have system administrator rights on all server. My question is, why on some server when I click on a database it shows the General, Table & Index, Space Allocated tool bar, than if I go to a different server, click a database it does not show? I would like to see this toolbar on all my servers when I click a database. Thanks Reggie
Pardon the possibly senseless question - I have been an NT Administrator for some years, and have just gotten thrown into picking up some SQL DBA work and I'm still feeling my way around.
Is it possible to query the Master database for the setup information (etc) on the other databases? Several have been marked suspect due to a hard drive failure and I am trying to figure out what the original setup of the databases was.
Hello,I am trying to use the SQl Server database reporting tools to create areport on the database to find areas for optimization including tableformats, column formats, and sizes. Can anybody please help me withthis. If any body has used any other tool by which I can create abovementioned reports plz do let me know. I will be really thankful foryour help.ThanksSuneel
If I had a bunch of paragraphs stored in a database field, is there a way, when displaying that data, that I can write instructions to only retrieve the very first paragraph?
This is probably a very simple question but i am having problems with inserting information into database The function takes the values "FirstName" And "LastName" from A table Called "Customer" and the value "ProductID" from a table called "Products" and inserts them into a table called " NewOrder". Everything compiles ok but when I press the button the information is not uploaded to thedatabase. ( There is no error message) This is the stored procedure CREATE PROCEDURE SP_NewOrder(@CartID char (36), @CustomerID Varchar (50))AS INSERT INTO NewOrder (FirstName, LastName, ProductID) SELECT Customer.FirstName, Customer.LastName, Products.ProductID From Customer,Products Join ShoppingCart ON Products.ProductID =ShoppingCart.ProductIDWHERE ShoppingCart.CartID = @CartID AND Customer.CustomerID = @CustomerIDGO This is the Function Public Shared Function CreateOrder() AS String Dim customerID As integer Dim connection as New SqlConnection(connectionString) Dim command as New SqlCommand("SP_NewOrder",connection) command.CommandType = CommandType.StoredProcedure command.Parameters.Add("@CartID", SqlDbType.Char,36) command.Parameters("@CartID").Value = shoppingCartID command.Parameters.Add("@CustomerID", SqlDbType.Varchar,36) command.Parameters("@CustomerID").Value = customerID Try connection.Open() command.ExecuteNonQuery() Finally connection.Close() End TryEnd Function This is the page code Sub btn3_Click(sender As Object, e As EventArgs)Dim cart as New ShoppingCart()Dim shoppingCartID As IntegerDim customerID As Integer = Context.Session("worldshop_CustomerID")cart.CreateOrder()End Sub Can Anyone see where I am going wrong Many thanks martin
I would like to know how to (if it is at all possible) in SQL (or even ADO) to retrieve all the data concerning database X's
a) Tables b) Tables column names c) Tables column's data types
I don't want to use the doa.tabledefs object.. .i would prefer to do it in SQL, but using ADO objects (since I am developing stuff in VB) would be ok too.
Please Help.. i have been going crazy trying to find anything useful.. email me back please!
I ´d like to know if there is any way to access an Informix database from sqlserver 2005.
We are trying to build a data warehouse in SS2005, and I want to know If I could avoid the generation of hundreds of flats files from Informix and loading them into SS2005, directly recovering all information from SS2005 across any kind of migration or connection.
This script gets the file information for every database on a server, and inserts it into temp table #DB_INFO. #DB_INFO is queried multiple ways to give various levels of analysis of file space usage.
This script was tested on SQL Server 7.0, 2000, and 2005.
Edit 2007/9/7: Added FILEGROUP_TYPE and DISK columns.
Edit 2007/9/17: Modified to add various ways to analyze the output and format it to make it easier to understand space usage on the server: Show Files Details Total by Database and File Total by Database and Filegroup Total by Database and Filegroup Type Total by Disk, Database, and Filepath Total by Disk and Database Total by Database
Edit 2007/9/17: Modified to make the changes suggested by eyechart. I managed to defeat Snitz to get rid of the smiley face () and still have executable code by putting in some extra quotes around the database name.
use master go if exists ( select * from tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id( N'tempdb..#DB_FILE_INFO' )) drop table #DB_FILE_INFO go
if exists ( select * from tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id( N'tempdb..#DB_INFO' )) drop table #DB_INFO go set nocount on go create table #DB_FILE_INFO ( [ID]intnot null identity (1, 1) primary key clustered , [DATABASE_NAME]sysnamenot null , [FILEGROUP_TYPE]nvarchar(4)not null , [FILEGROUP_ID]smallintnot null , [FILEGROUP]sysnamenot null , [FILEID]smallintnot null , [FILENAME]sysnamenot null , [DISK]nvarchar(1)not null , [FILEPATH]nvarchar(260)not null , [MAX_FILE_SIZE]intnull , [FILE_SIZE]intnot null , [FILE_SIZE_USED]intnot null , [FILE_SIZE_UNUSED]intnot null , [DATA_SIZE]intnot null , [DATA_SIZE_USED]intnot null , [DATA_SIZE_UNUSED]intnot null , [LOG_SIZE]intnot null , [LOG_SIZE_USED]intnot null , [LOG_SIZE_UNUSED]intnot null , ) go
declare @sqlnvarchar(4000) set @sql = 'use ['+'?'+'] ; if db_name() <> N''?'' goto Error_Exit
insert into #DB_FILE_INFO ( [DATABASE_NAME], [FILEGROUP_TYPE], [FILEGROUP_ID], [FILEGROUP], [FILEID], [FILENAME], [DISK], [FILEPATH], [MAX_FILE_SIZE], [FILE_SIZE], [FILE_SIZE_USED], [FILE_SIZE_UNUSED], [DATA_SIZE], [DATA_SIZE_USED], [DATA_SIZE_UNUSED], [LOG_SIZE], [LOG_SIZE_USED], [LOG_SIZE_UNUSED] ) selecttop 100 percent [DATABASE_NAME] = db_name(), [FILEGROUP_TYPE]= case when a.groupid = 0 then ''Log'' else ''Data'' end, [FILEGROUP_ID]= a.groupid, a.[FILEGROUP], [FILEID]= a.fileid, [FILENAME]= a.name, [DISK]= upper(substring(a.filename,1,1)), [FILEPATH]= a.filename, [MAX_FILE_SIZE] = convert(int,round( (case a.maxsize when -1 then null else a.maxsize end*1.000)/128.000 ,0)), [FILE_SIZE]= a.[fl_size], [FILE_SIZE_USED] = a.[fl_used], [FILE_SIZE_UNUSED] = a.[fl_unused], [DATA_SIZE]= case when a.groupid <> 0 then a.[fl_size] else 0 end, [DATA_SIZE_USED]= case when a.groupid <> 0 then a.[fl_used] else 0 end, [DATA_SIZE_UNUSED] = case when a.groupid <> 0 then a.[fl_unused] else 0 end, [LOG_SIZE] = case when a.groupid = 0 then a.[fl_size] else 0 end, [LOG_SIZE_USED] = case when a.groupid = 0 then a.[fl_used] else 0 end, [LOG_SIZE_UNUSED] = case when a.groupid = 0 then a.[fl_unused] else 0 end from ( Select aa.*, [FILEGROUP]= isnull(bb.groupname,''''), -- All sizes are calculated in MB [fl_size]= convert(int,round((aa.size*1.000)/128.000,0)), [fl_used]= convert(int,round(fileproperty(aa.name,''SpaceUsed'')/128.000,0)), [fl_unused]= convert(int,round((aa.size-fileproperty(aa.name,''SpaceUsed''))/128.000,0)) from dbo.sysfiles aa left join dbo.sysfilegroups bb on ( aa.groupid = bb.groupid ) ) a order by case when a.groupid = 0 then 0 else 1 end, a.[FILEGROUP], a.name
if object_id('tempdb..##DB_Size_Info_D115CA380E2B4538B6CBBB51') is not null begin drop table ##DB_Size_Info_D115CA380E2B4538B6CBBB51 end
-- Setup code to reduce column sizes to max used set @sql = ' select [DATABASE_NAME]= convert(varchar('+@DATABASE_NAME_LEN+'), a.[DATABASE_NAME] ), a.[FILEGROUP_TYPE], [FILEGROUP_ID], [FILEGROUP]= convert(varchar('+@FILEGROUP_LEN+'), a.[FILEGROUP]), [FILEID], [FILENAME]= convert(varchar('+@FILENAME_LEN+'), a.[FILENAME] ), a.[DISK], [FILEPATH]= convert(varchar('+@FILEPATH_LEN+'), a.[FILEPATH] ), a.[MAX_FILE_SIZE], a.[FILE_SIZE], a.[FILE_SIZE_USED], a.[FILE_SIZE_UNUSED], FILE_USED_PCT= convert(numeric(5,1),round( case when a.[FILE_SIZE] is null or a.[FILE_SIZE] = 0 then NULL else (100.00000*a.[FILE_SIZE_USED])/(1.00000*a.[FILE_SIZE]) end ,1)) , a.[DATA_SIZE], a.[DATA_SIZE_USED], a.[DATA_SIZE_UNUSED], a.[LOG_SIZE], a.[LOG_SIZE_USED], a.[LOG_SIZE_UNUSED] into ##DB_Size_Info_D115CA380E2B4538B6CBBB51 from #DB_FILE_INFO a order by a.[DATABASE_NAME], case a.[FILEGROUP_ID] when 0 then 0 else 1 end, a.[FILENAME] '
--print @sql
exec ( @sql )
selecttop 100 percent * into #DB_INFO from ##DB_Size_Info_D115CA380E2B4538B6CBBB51 a order by a.[DATABASE_NAME], case a.[FILEGROUP_ID] when 0 then 0 else 1 end, a.[FILENAME]
drop table ##DB_Size_Info_D115CA380E2B4538B6CBBB51
set nocount off
print 'Show Details' select * from #DB_INFO
print 'Total by Database and File' select [DATABASE_NAME]= isnull([DATABASE_NAME],' All Databases'), [FILENAME]= isnull([FILENAME],''), FILE_SIZE= sum(FILE_SIZE), FILE_SIZE_USED= sum(FILE_SIZE_USED), FILE_SIZE_UNUSED= sum(FILE_SIZE_UNUSED), FILE_USED_PCT= convert(numeric(5,1),round( case when sum(a.[FILE_SIZE]) is null or sum(a.[FILE_SIZE]) = 0 then NULL else (100.00000*sum(a.[FILE_SIZE_USED]))/(1.00000*sum(a.[FILE_SIZE])) end ,1)) , DATA_SIZE= sum(DATA_SIZE), DATA_SIZE_USED= sum(DATA_SIZE_USED), DATA_SIZE_UNUSED= sum(DATA_SIZE_UNUSED), LOG_SIZE= sum(LOG_SIZE), LOG_SIZE_USED= sum(LOG_SIZE_USED), LOG_SIZE_UNUSED= sum(LOG_SIZE_UNUSED) from #DB_INFO a group by [DATABASE_NAME], [FILENAME] with rollup order by case when [DATABASE_NAME] is null then 1 else 0 end , [DATABASE_NAME], case when [FILENAME] is null then 1 else 0 end , [FILENAME]
print 'Total by Database and Filegroup'
select --[Server]= convert(varchar(15),@@servername), [DATABASE_NAME]= isnull([DATABASE_NAME],'** Total **'), [FILEGROUP]= case when [FILEGROUP] is null then '' when [FILEGROUP] = '' then 'LOG' else [FILEGROUP] end, FILE_SIZE= sum(FILE_SIZE), FILE_SIZE_USED= sum(FILE_SIZE_USED), FILE_SIZE_UNUSED= sum(FILE_SIZE_UNUSED), FILE_USED_PCT= convert(numeric(5,1),round( case when sum(a.[FILE_SIZE]) is null or sum(a.[FILE_SIZE]) = 0 then NULL else (100.00000*sum(a.[FILE_SIZE_USED]))/(1.00000*sum(a.[FILE_SIZE])) end ,1)) , --MAX_SIZE= SUM([MAX_FILE_SIZE]), DATA_SIZE= sum(DATA_SIZE), DATA_SIZE_USED= sum(DATA_SIZE_USED), DATA_SIZE_UNUSED= sum(DATA_SIZE_UNUSED), LOG_SIZE= sum(LOG_SIZE), LOG_SIZE_USED= sum(LOG_SIZE_USED), LOG_SIZE_USED= sum(LOG_SIZE_UNUSED) from #DB_INFO A group by [DATABASE_NAME], [FILEGROUP] with rollup order by case when [DATABASE_NAME] is null then 1 else 0 end , [DATABASE_NAME], case when [FILEGROUP] is null then 10 when [FILEGROUP] = '' then 0 else 1 end , [FILEGROUP]
print 'Total by Database and Filegroup Type'
select --[Server]= convert(varchar(15),@@servername), [DATABASE_NAME]= isnull([DATABASE_NAME],'** Total **'), [FILEGROUP_TYPE]= isnull([FILEGROUP_TYPE],''), FILE_SIZE= sum(FILE_SIZE), FILE_SIZE_USED= sum(FILE_SIZE_USED), FILE_SIZE_UNUSED= sum(FILE_SIZE_UNUSED), FILE_USED_PCT= convert(numeric(5,1),round( case when sum(a.[FILE_SIZE]) is null or sum(a.[FILE_SIZE]) = 0 then NULL else (100.00000*sum(a.[FILE_SIZE_USED]))/(1.00000*sum(a.[FILE_SIZE])) end ,1)) , DATA_SIZE= sum(DATA_SIZE), DATA_SIZE_USED= sum(DATA_SIZE_USED), DATA_SIZE_UNUSED= sum(DATA_SIZE_UNUSED), LOG_SIZE= sum(LOG_SIZE), LOG_SIZE_USED= sum(LOG_SIZE_USED), LOG_SIZE_USED= sum(LOG_SIZE_UNUSED) from #DB_INFO A group by [DATABASE_NAME], [FILEGROUP_TYPE] with rollup order by case when [DATABASE_NAME] is null then 1 else 0 end , [DATABASE_NAME], case when [FILEGROUP_TYPE] is null then 10 when [FILEGROUP_TYPE] = 'Log' then 0 else 1 end
print 'Total by Disk, Database, and Filepath' select [DISK]= isnull([DISK],''), [DATABASE_NAME]= isnull([DATABASE_NAME],''), [FILEPATH]= isnull([FILEPATH],''), FILE_SIZE= sum(FILE_SIZE), FILE_SIZE_USED= sum(FILE_SIZE_USED), FILE_SIZE_UNUSED= sum(FILE_SIZE_UNUSED), FILE_USED_PCT= convert(numeric(5,1),round( case when sum(a.[FILE_SIZE]) is null or sum(a.[FILE_SIZE]) = 0 then NULL else (100.00000*sum(a.[FILE_SIZE_USED]))/(1.00000*sum(a.[FILE_SIZE])) end ,1)) , DATA_SIZE= sum(DATA_SIZE), DATA_SIZE_USED= sum(DATA_SIZE_USED), DATA_SIZE_UNUSED= sum(DATA_SIZE_UNUSED), LOG_SIZE= sum(LOG_SIZE), LOG_SIZE_USED= sum(LOG_SIZE_USED), LOG_SIZE_UNUSED= sum(LOG_SIZE_UNUSED) from #DB_INFO a group by [DISK], [DATABASE_NAME], [FILEPATH] with rollup order by case when [DISK] is null then 1 else 0 end , [DISK], case when [DATABASE_NAME] is null then 1 else 0 end , [DATABASE_NAME], case when [FILEPATH] is null then 1 else 0 end , [FILEPATH]
print 'Total by Disk and Database' select [DISK]= isnull([DISK],''), [DATABASE_NAME]= isnull([DATABASE_NAME],''), FILE_SIZE= sum(FILE_SIZE), FILE_SIZE_USED= sum(FILE_SIZE_USED), FILE_SIZE_UNUSED= sum(FILE_SIZE_UNUSED), FILE_USED_PCT= convert(numeric(5,1),round( case when sum(a.[FILE_SIZE]) is null or sum(a.[FILE_SIZE]) = 0 then NULL else (100.00000*sum(a.[FILE_SIZE_USED]))/(1.00000*sum(a.[FILE_SIZE])) end ,1)) , DATA_SIZE= sum(DATA_SIZE), DATA_SIZE_USED= sum(DATA_SIZE_USED), DATA_SIZE_UNUSED= sum(DATA_SIZE_UNUSED), LOG_SIZE= sum(LOG_SIZE), LOG_SIZE_USED= sum(LOG_SIZE_USED), LOG_SIZE_USED= sum(LOG_SIZE_UNUSED) from #DB_INFO a group by [DISK], [DATABASE_NAME] with rollup order by case when [DISK] is null then 1 else 0 end , [DISK], case when [DATABASE_NAME] is null then 1 else 0 end , [DATABASE_NAME]
print 'Total by Disk' select [DISK]= isnull([DISK],''), FILE_SIZE= sum(FILE_SIZE), FILE_SIZE_USED= sum(FILE_SIZE_USED), FILE_SIZE_UNUSED= sum(FILE_SIZE_UNUSED), FILE_USED_PCT= convert(numeric(5,1),round( case when sum(a.[FILE_SIZE]) is null or sum(a.[FILE_SIZE]) = 0 then NULL else (100.00000*sum(a.[FILE_SIZE_USED]))/(1.00000*sum(a.[FILE_SIZE])) end ,1)) , DATA_SIZE= sum(DATA_SIZE), DATA_SIZE_USED= sum(DATA_SIZE_USED), DATA_SIZE_UNUSED= sum(DATA_SIZE_UNUSED), LOG_SIZE= sum(LOG_SIZE), LOG_SIZE_USED= sum(LOG_SIZE_USED), LOG_SIZE_USED= sum(LOG_SIZE_UNUSED) from #DB_INFO a group by [DISK] with rollup order by case when [DISK] is null then 1 else 0 end , [DISK]
print 'Total by Database' select --[Server]= convert(varchar(20),@@servername), [DATABASE_NAME]= isnull([DATABASE_NAME],'** Total **'), FILE_SIZE= sum(FILE_SIZE), FILE_SIZE_USED= sum(FILE_SIZE_USED), FILE_SIZE_UNUSED= sum(FILE_SIZE_UNUSED), FILE_USED_PCT= convert(numeric(5,1),round( case when sum(a.[FILE_SIZE]) is null or sum(a.[FILE_SIZE]) = 0 then NULL else (100.00000*sum(a.[FILE_SIZE_USED]))/(1.00000*sum(a.[FILE_SIZE])) end ,1)) , DATA_SIZE= sum(DATA_SIZE), DATA_SIZE_USED= sum(DATA_SIZE_USED), DATA_SIZE_UNUSED= sum(DATA_SIZE_UNUSED), LOG_SIZE= sum(LOG_SIZE), LOG_SIZE_USED= sum(LOG_SIZE_USED), LOG_SIZE_UNUSED= sum(LOG_SIZE_UNUSED) from #DB_INFO A group by [DATABASE_NAME] with rollup order by case when [DATABASE_NAME] is null then 1 else 0 end , [DATABASE_NAME]
I've written a scalar function in C# for the MS SQL Server 2005 that has to have access to two databases in one instance of a server ("database1" and "database2"). I got information from database1, calculate the name of the database2 and got information from user's tables in the database2. But when I'm trying to get informatin from "sys.tables", "sys.columns", "sysobjects" I always get an error "System.Data.SqlClient.SqlException: This statement has attempted to access data whose access is restricted by the assembly."
All databases are inside one server, I can run this quieries in SQL Server management Studio - could anybody help me what should I set/change to get metainformation from another database in the same server (in fact I need a list of fields of a curtain table).
Thanks,
Alex Gerasimov
P.S. Atrribute [SqlFunction(DataAccess = DataAccessKind.Read)] is in the applicaiton.
Hello, I'm building an ecommerce website which requires customers to create an account before they go ahead with a purchase. I have a createaccount.aspx page in Visual Web Developer 2005 with text boxes where users can enter their details (email, password, name and address). I'm trying to insert the information which users type into the text boxes into an SQL database table called Customers. I've dragged and dropped an SQL data source onto my page and have set it to operate on my AddCustomer stored procedure. I've confirgured my data source such that the parameter for each field in the database is set to the appropriate control on the webpage (for example the Email parameter source is "textboxEmail"). I've also placed a button onto my page so that the button click event can act as the trigger for sending the information in the text boxes to the database. I wasn't totally sure how to write code for the button click event such that when the button is clicked, the INSERT stored procedure runs. At the moment I'm using: Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSubmit.Click SqlDataSource1.Insert() End Sub When I try to run my application I'm getting an error which reads: Cannot insert the value NULL into column 'Email', table 'C:DOCUMENTS AND SETTINGSLUKE JACKSONMY DOCUMENTSVISUAL STUDIO 2005WEBSITESJACKSONSNURSERIESAPP_DATADATABASE.MDF.dbo.Customers'; column does not allow nulls. INSERT fails.The statement has been terminated. The error message implies that I haven't set the necessary parameters correctly but I really don't know where I'm going wrong! The code I'm using for my stored procedure is as follows: ALTER PROCEDURE AddCustomer ( @CustomerID int, @Email nvarchar(50), @Password nvarchar(MAX), @Name nvarchar(50), @Address1 nvarchar(50), @Address2 nvarchar(50), @Address3 nvarchar(50), @City nvarchar(50), @County nvarchar(50), @PostCode nvarchar(50) ) AS INSERT INTO Customers (Email, Password, Name, Address1, Address2, Address3, City, County, PostCode) VALUES (@Email, @Password, @Name, @Address1, @Address2, @Address3, @City, @County, @PostCode) I'd be really grateful if anyone could help me out with this. Thanks in advance, Luke p.s. just incase it helps, here's my createaccount.aspx page: <%@ Page Language="VB" MasterPageFile="~/Master.master" AutoEventWireup="false" CodeFile="createaccount.aspx.vb" Inherits="createaccount" title="Untitled Page" %> <%-- Add content controls here --%> <asp:Content ID="Content1" runat="server" ContentPlaceHolderID="ContentPlaceHolder1"> <span style="text-decoration: underline"><strong>Create Account<br /> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="AddCustomer" SelectCommandType="StoredProcedure" InsertCommand="INSERT INTO Customers 	(Email, Password, Name, Address1, Address2, Address3, City, County, PostCode) 	VALUES 	(@Email, @Password, @Name, @Address1, @Address2, @Address3, @City, @County, @PostCode)"> <SelectParameters> <asp:Parameter Name="CustomerID" Type="Int32" /> <asp:ControlParameter ControlID="textboxEmail" Name="Email" PropertyName="Text" Type="String" /> <asp:ControlParameter ControlID="textboxPassword" Name="Password" PropertyName="Text" Type="String" /> <asp:ControlParameter ControlID="textboxName" Name="Name" PropertyName="Text" Type="String" /> <asp:ControlParameter ControlID="textboxAddress" Name="Address1" PropertyName="Text" Type="String" /> <asp:ControlParameter ControlID="textboxAddress2" Name="Address2" PropertyName="Text" Type="String" /> <asp:ControlParameter ControlID="textboxAddress3" Name="Address3" PropertyName="Text" Type="String" /> <asp:ControlParameter ControlID="textboxCity" Name="City" PropertyName="Text" Type="String" /> <asp:ControlParameter ControlID="textboxCounty" Name="County" PropertyName="Text" Type="String" /> <asp:ControlParameter ControlID="textboxPostCode" Name="PostCode" PropertyName="Text" Type="String" /> </SelectParameters> <InsertParameters> <asp:Parameter Name="Email" Type="String" /> <asp:Parameter Name="Password" Type="String" /> <asp:Parameter Name="Name" Type="String" /> <asp:Parameter Name="Address1" Type="String" /> <asp:Parameter Name="Address2" Type="String" /> <asp:Parameter Name="Address3" Type="String" /> <asp:Parameter Name="City" Type="String" /> <asp:Parameter Name="County" Type="String" /> <asp:Parameter Name="PostCode" Type="String" /> </InsertParameters> </asp:SqlDataSource> <br /> </strong></span> <table style="font-weight: bold; width: 394px; text-decoration: underline"> <tr> <td style="width: 111px; height: 21px; text-align: left"> Email:</td> <td style="height: 21px"> <asp:TextBox ID="textboxEmail" runat="server" Width="147px"></asp:TextBox></td> </tr> <tr> <td style="width: 111px; height: 21px; text-align: left"> Password:</td> <td style="height: 21px"> <asp:TextBox ID="textboxPassword" runat="server"></asp:TextBox></td> </tr> <tr> <td style="width: 111px; height: 21px; text-align: left"> Name:</td> <td style="height: 21px"> <asp:TextBox ID="textboxName" runat="server"></asp:TextBox></td> </tr> <tr> <td style="width: 111px; text-align: left"> Address 1:</td> <td> <asp:TextBox ID="textboxAddress" runat="server"></asp:TextBox></td> </tr> <tr> <td style="width: 111px; height: 21px; text-align: left"> Address 2:</td> <td style="height: 21px"> <asp:TextBox ID="textboxAddress2" runat="server"></asp:TextBox></td> </tr> <tr> <td style="width: 111px; text-align: left"> Address 3:</td> <td> <asp:TextBox ID="textboxAddress3" runat="server"></asp:TextBox></td> </tr> <tr> <td style="width: 111px; text-align: left"> City:</td> <td> <asp:TextBox ID="textboxCity" runat="server"></asp:TextBox></td> </tr> <tr> <td style="width: 111px; height: 21px; text-align: left"> County:</td> <td style="height: 21px"> <asp:TextBox ID="textboxCounty" runat="server"></asp:TextBox></td> </tr> <tr> <td style="width: 111px; text-align: left"> Post Code:</td> <td> <asp:TextBox ID="textboxPostCode" runat="server"></asp:TextBox></td> </tr> </table> <asp:Button ID="btnSubmit" runat="server" Text="Submit" /> </asp:Content> Thanks again
Hello, I'm trying to create a web page that is protected by a user login. I want the user data to be stored in an access database file and not an SQL Server express database.
I was following this guide on msdn: http://msdn2.microsoft.com/en-us/library/879kf95c(VS.80).aspx
However, I was doing everything locally using a file system and not IIS. (Which seemed to work fine)
My main problem is that I cannot get the membership information stored in an access database. The only provider option was one for the SQL server express. I think I will have to make a custom provider? However, I read through some of that documentation and was equally confused. ( http://msdn2.microsoft.com/en-us/library/Aa479031.aspx )
I am attempting to drop a database (sales), however I receive this message: Error 3274 is "Cannot drop the database 'sales' because it is published for replication." Yet, I no longer have any publications in this database. It seems that there is some orphaned information related to a publication that existed. Any help would be great.
While cleaning up some code, I ran across the following statement in a stored proc - the purpose of which is to determine if a table exists in the local database: SELECT * FROM dbo.sysobjects where id = object_id(N'[dbo].[XML_PRINTDATE]') and OBJECTPROPERTY(id, N'IsUserTable') = 1of course I removed it from the IF just for testing purposes, but my quandry is this... Why chose that select (converting table name to object ID) rather than just doing THIS:SELECT * FROM dbo.sysobjects where name = N'XML_PRINTDATE' and OBJECTPROPERTY(id, N'IsUserTable') = 1
I first thought it was to gain access to the "id" column value (and that may yet be the purpose of it), but the second code seems to work just peachy (I assume because the id column is present in the sysobjects table itself).
A follow-on question is this: When I try to do the same check from another server (i.e.SELECT * FROM APRECEIVE1.DailyProd.dbo.sysobjects where name = N'XML_PRINTDATE' and OBJECTPROPERTY(id, N'IsUserTable') = 1) it of course fails because OBJECTPROPERTY only looks for the id on the local database.
So, do I CARE if it is a user table? (I am reasonably sure it is, of course) and if so, is there a way to check on the remote server for the object type?
Bottom line is I Think I can just simplify things and check for the object name on the remote server, but just don't want to take away any "warm fuzzy feeling" generated by the original stored proc, if such a warm fuzzy is of any benefit (though don't get me started on the relativity of warm fuzzies, I wrote my Thesis on that ;) )
I am using following sql to extract locking information in database. It only work on current selected database, how can I tune to work on all databases and not only currently selected?
SELECT DISTINCT ES.login_name AS LoginName, L.request_session_id AS BlockedBy_SPID, DATEDIFF(second,At.Transaction_begin_time, GETDATE()) AS Duration_Sec, DB_NAME(L.resource_database_id) AS DatabaseName,
Dear list,I am trying to get the names of the tables and the column names from thecode below for a database but it is not working. When I run the querybelow the column titles are delivered but there is no data. I think thismight be a premissions issue. Has anyone run into this before?Thanks in advance.Use Test_db/* Provides Table Name, Column Name, Extened Description */Select a.name as tbl_name, b.name as column_name, d.name as data_type,d.length as length, d.xprec as prec, d.scale as scale, b.usertype,b.scale, c.valuefrom sysobjects as a inner join syscolumns as b on a.id=b.id inner joinsysproperties as c on b.colid=c.smallid and a.id=c.idinner join systypes as d on b.xtype=d.xtype
I'm using Sql Server 2012 Enterprise and have a database with over 400 tables. I am looking at one diagram which contains about 20 (or so) tables and their relationships. When I right-click on a relationship line I only see information about which tables are connected on that line but not on which fields. Can I get that information from the Diagram or can I only get that from the sys tables?
Hi, I am working on inserting information into a DB and then retrieving the ID created for that Data to use elsewhere in my code. I have the code below but I do not know how to get toOutput parameter. Can anyone please help?
AS INSERT INTO PRODUCTION (DATEOUT,DATEREQUIRED, PREPAREDBY, COMMENTID, TOTALQUANTITY, VENDORID, WPO, TCAPONUMBER, APPROVEDBY) VALUES( @DATEOUT, @DATEREQUIRED, @PREPAREDBY, @COMMENTID, @TOTALQUANTITY, @VENDORID, @WPO, @TCAPONUMBER, @APPROVEDBY) SET @Identity = SCOPE_IDENTITY()
'collect all the information from the form and then apply all and then update 'Get a reference to the Production table. Dim dtProduction As DataTable = DS.Tables("Production") Dim dtLineItem As DataTable = DS.Tables("LineItems") ' Create the SqlCommand to execute the stored procedure. Production.InsertCommand = New SqlCommand("dbo.InsertProduction", connection) Production.InsertCommand.CommandType = CommandType.StoredProcedure ' Add the parameter for the CategoryName. Specifying the ' ParameterDirection for an input parameter is not required. 'Production.InsertCommand.Parameters.Add("@CategoryName", SqlDbType.NVarChar, 15, "CategoryName") Production.InsertCommand.Parameters.Add("@DATEOUT", SqlDbType.DateTime, 8, "CategoryName") Production.InsertCommand.Parameters.Add("@DATEREQUIRED", SqlDbType.DateTime, 8, "CategoryName") Production.InsertCommand.Parameters.Add("@PREPAREDBY", SqlDbType.VarChar, 50, "CategoryName") Production.InsertCommand.Parameters.Add("@COMMENTID", SqlDbType.Int, 4, "CategoryName") Production.InsertCommand.Parameters.Add("@TOTALQUANTITY", SqlDbType.Int, 4, "CategoryName") Production.InsertCommand.Parameters.Add("@VENDORID", SqlDbType.Int, 4, "CategoryName") Production.InsertCommand.Parameters.Add("@WPO", SqlDbType.VarChar, 50, "CategoryName") Production.InsertCommand.Parameters.Add("@TCAPONUMBER", SqlDbType.Int, 4, "CategoryName") Production.InsertCommand.Parameters.Add("@APPROVEDBY", SqlDbType.VarChar, 50, "CategoryName") ' Add the SqlParameter to retrieve the new identity value. ' Specify the ParameterDirection as Output. Dim parameter As SqlParameter = Production.InsertCommand.Parameters.Add("@Identity", SqlDbType.Int, 0, "ProductionID") parameter.Direction = ParameterDirection.Output ' Create a new row with the same schema. Dim dr As DataRow = dtProduction.NewRow() 'you need the ID from this to insert into the Production DB ' Set the value of all the columns. dr("DateOut") = CDate(DateTimePicker1.Text) dr("DateRequired") = CDate(DateTimePicker2.Text) dr("VendorID") = CInt(vendorbox.SelectedValue) dr("HomeAddress") = txtApproved.Text.ToString dr("ApprovedBy") = txtPrepared.Text.ToString dr("TCAPO") = CInt(txtTCAPO.Text.Trim) dr("CommentID") = CommentID dr("TotalCost") = CDec(txtTotals.Text) dr("TotalQuantity") = CInt(txtQtyTotal.Text) ' Add to the Rows collection or table . dtProduction.Rows.Add(dr) 'Update the Production Table and then retrieve the ID created in this case Production.Update(dtProduction)
Hello. We have a smaller system on one of our servers where a couple of users where beta-testing. This system used a SQLExpress 2005 database (databaseName_data.mdf).
But yesterday we saw that we couldn't use the system anymore, we got errors about the connection to the database. We open SQL Management Studio and connected to the SQL Server and we saw the name of the database in the list, but it was completly empty. Nothing. Not the "folder" for Tables, Programmability, Security... nothing.
We then browsed to the folder where the MDF file used to be, and there we only found the LDF file. The MDF file was gone.
We "know" that no one here have been shutting down the SQL Service and then deleted the DB, so we are trying to figure out what has happen.
It's not a major issue, because it's just a beta-test, but we don't want this to happen later on again...
Does anyone have a clue of what might be going on?
We are using three instances of SQL Express on this test machine btw... One for the public system (wich used this db), one for development and one for some random tests...
The public server and develop server used databases with the same name, but of course, different files on the hdd (and different instances of SQL Express).
//J
Edit: I might add that we hadn't backed this db up yet... Is there some way to use the LDF-file to restore some of the data?
We are getting a security audit for the company I work for and got this question recently, and while my answer would be "everything is possible", I know that DIFFs alone can't restore a SQL database.
Having said that (and don't want to read hypothetical comments) how can a hacker read confidential information from an unencrypted DIFF backup? Let's say he steals the DIFF backup alone.
In my couple of User database dbo user is missing. I am receiving following error when I click sa user in enterprise manager. Error 21776 The name dbo was not found in user collection.
