Hi Guys
i have been trying so hard to get an answer for my problem but still havent found a solution
Well my problem is i have got two tables
table 1
Companyname id year volume_for_jan vol_for_feb.....vol_dec
abc 1 2000 34 333 ......555
2 2000 33 22 666
table 2
Companyname id year volume_for_jan vol_for_feb.....vol_dec
rrr 44 2001 55 66.............888
24 2001 22 35 454
each table has almost 800000 rows
I need to generate a report out of these tables which should be in the following format:
Company name Id Year Vol_for_jan Vol_for_feb.........
abc 1 2000 34 33
2 2000 33 22
rrr 44 2001 55 66
How shall i do that???
I dont have any indexes on the tables coz there is no unique field!
Environment: SQL 2005 Workgroup Ed. (part of SBS 2003 R2 Premium)
I have a database attached to SQL 2005 that is on my D: drive. I'm trying to run a full backup of this database to a backup device also on D:. The first time I ran the backup it worked fine, then I installed SQL 2005 SP1 and now it's failing. The error message is:
quote: Backup failed for Server 'MYSERVER'. (Microsoft.SqlServer.Smo) System.Data.SqlClient.SqlError: Write on "My Backup Device(D:BackupMyBackupDevice.bak)" failed: 112(There is not enough space on the disk.)(Microsoft.SqlServer.Smo)
In the event viewer, the following events are logged in the Application log:
quote: Source: MSSQLSERVER Category: (6) Event ID: 3041 Description: BACKUP failed to complete the command BACKUP DATABASE MyDatabase. Check the backup application log for detailed messages.
quote: Source: MSSQLSERVER Category: (2) Event ID: 18210 Description: BackupMedium::ReportIoError: write failure on backup device 'D:BackupMyBackupDevice.bak'. Operating system error 112 (There is not enough space on the disk.).
If I do the backup to the MSSQL default backup directory (on C: ), the backup completes successfully. If I try to back up the master database (on C: ) to the backup directory I created on D:, the backup completes successfully. If I try to back up the master database to the MSSQL default backup directory (on C: ), the backup completes successfully.
The SqlServer and SqlAgent processes are both running under a domain user account, and that account has full control of my backup directory on D:. I should also mention that my D: drive has over 100GB free, and the database is only 330MB. It is a simple database model.
I've searched all day today trying to find the solution to this issue, and I can't find anything relevant. Could someone please help me!!! I'm about at my wit's end!
Svr2: Windows 2003 R2 SP2 Standard IIS 6.0 SQL 2005 Standard SP2 WSUS 3.0 SP1 SCCM 2007 RTM
Svr2 query works fine, Srv1 query returns this error:
An error occurred when the report was run. The details are as follows: The query has been canceled because the estimated cost of this query (2147483647) exceeds the configured threshold of 100000. Contact the system administrator.
Error Number: -2147217900
Source: Microsoft OLE DB Provider for SQL Server
Native Error: 8649
This is the query language:
SELECT DISTINCT dbo.v_R_System.Netbios_Name0 AS [Machine Name], dbo.v_R_System.Resource_Domain_OR_Workgr0, dbo.v_GS_OPERATING_SYSTEM.Caption0 AS [Operating System Caption], dbo.v_R_System.Operating_System_Name_and0 AS [Operating System], dbo.v_GS_OPERATING_SYSTEM.Version0 AS [Operating System Version], dbo.v_GS_OPERATING_SYSTEM.CSDVersion0 AS [Service Pack Version], dbo.v_GS_COMPUTER_SYSTEM.Manufacturer0 AS [System Manufacturer], dbo.v_GS_COMPUTER_SYSTEM.Model0 AS [System Model], dbo.v_GS_PC_BIOS.SerialNumber0 AS [System Serial Number (if Available)], dbo.v_GS_PROCESSOR.MaxClockSpeed0 AS [Processor Speed (GHz)], dbo.v_GS_COMPUTER_SYSTEM.NumberOfProcessors0 AS [Number of Processors (or Cores)], dbo.v_GS_X86_PC_MEMORY.TotalPhysicalMemory0 AS [Memory (KBytes)], dbo.v_GS_LOGICAL_DISK.Size0 AS [Disk Space (MB)], dbo.v_GS_LOGICAL_DISK.FreeSpace0 AS [Free Disk Space (MB)], dbo.v_RA_System_IPAddresses.IP_Addresses0 AS [IP Address], dbo.v_RA_System_MACAddresses.MAC_Addresses0 AS [MAC Address], dbo.v_GS_PC_BIOS.Description0 AS [BIOS Description], dbo.v_GS_PC_BIOS.ReleaseDate0 AS [BIOS Release Date], dbo.v_GS_PC_BIOS.SMBIOSBIOSVersion0 AS [SM BIOS Version], dbo.v_GS_OPERATING_SYSTEM.WindowsDirectory0 AS [Windows Install Directory]
FROM dbo.v_R_System
INNER JOIN dbo.v_GS_OPERATING_SYSTEM ON dbo.v_GS_OPERATING_SYSTEM.ResourceID = dbo.v_R_System.ResourceID INNER JOIN dbo.v_GS_SYSTEM_ENCLOSURE ON dbo.v_GS_SYSTEM_ENCLOSURE.ResourceID = dbo.v_R_System.ResourceID INNER JOIN dbo.v_GS_COMPUTER_SYSTEM ON dbo.v_GS_COMPUTER_SYSTEM.ResourceID = dbo.v_R_System.ResourceID INNER JOIN dbo.v_RA_System_IPAddresses ON dbo.v_RA_System_IPAddresses.ResourceID = dbo.v_R_System.ResourceID INNER JOIN dbo.v_RA_System_MACAddresses ON dbo.v_RA_System_MACAddresses.ResourceID = dbo.v_R_System.ResourceID INNER JOIN dbo.v_GS_X86_PC_MEMORY ON dbo.v_GS_X86_PC_MEMORY.ResourceID = dbo.v_R_System.ResourceID INNER JOIN dbo.v_GS_PROCESSOR ON dbo.v_GS_PROCESSOR.ResourceID = dbo.v_R_System.ResourceID INNER JOIN dbo.v_GS_PC_BIOS ON dbo.v_GS_PC_BIOS.ResourceID = dbo.v_R_System.ResourceID INNER JOIN dbo.v_GS_LOGICAL_DISK ON dbo.v_GS_LOGICAL_DISK.ResourceID = dbo.v_R_System.ResourceID INNER JOIN dbo.v_FullCollectionMembership ON (dbo.v_FullCollectionMembership.ResourceID = v_R_System.ResourceID)
WHERE (dbo.v_GS_LOGICAL_DISK.DeviceID0 = 'C:') AND dbo.v_FullCollectionMembership.CollectionID = 'in00000f' Order by dbo.v_R_System.Netbios_Name0
So for the last week of my life, literally, I have been trying to import excel files, text files, etc through the bcp utility, link server and also trying bulk insert....I am really, really tired of this not working and I am starting to think there is a bug in express. I first thought these issues might have to do with Vista but I have even tried and failed with XP SP2. I am not new to SQL Server, nor am I a new developer - I have over five years experience so I am really pissed about it at this point! I know most professional developers use SQL Server 2005 Enterprise or something other than Express which is why I am probably not getting answers. btw, this is not the first time I have posted about these issues either. I have referenced multiple sources including forums, books on-line and I even resorted to getting a SQL Server 2005 EXPRESS book for dummies! I have enabled remote connections for both named pipes and tcp/ip. I have started the sql browser service. I have given the correct permissions to the file I want to import and to the table I want to import the data to. The file contains the int values 1,2,3,4,5,6,7,8,9 --> I have even tried saving this file as a .csv -- thanks a whole bunch sql server 2005 dev team for getting rid of the import/export wizard found in 2000, arrrrrgh!
BULK INSERT TestDB.dbo.tblTestData FROM 'C:datamyData.txt' WITH (DATAFILETYPE = 'char', FIELDTERMINATOR =','); GO
Msg 4864, Level 16, State 1, Line 1 Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 1 (columnValue).
Can ANYONE give me a real answer on h ow I can get this to actually work?
I got this erorr when trying to create my stored proc,
What do i need to fix, and how do i fix it?!!
Msg 457, Level 16, State 1, Procedure PROC_DAILY_ACTIVITY, Line 13
Implicit conversion of char value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict.
Code Block set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go
-- ============================================= -- Author: <Zaccheus,Tenchy> -- Create date: <NOVEMEBER,12,2007> -- Description: <Reporting stored procedure,DAILY ACTIVITY,> -- ============================================= CREATE PROCEDURE [dbo].[PROC_DAILY_ACTIVITY] (@Region_Key int=null) AS BEGIN SELECT Region_Key, Null as Customer_Code, Non_Customer_Activities.Question_code, Non_Customer_Activities.Description, Region AS Region, Name AS Territory_Name, Non_Customer_Activities.Que_Desc AS Store_Name, Non_Customer_Activities.Logged_Time AS TheDate, Non_Customer_Activities.response AS Response, Null as is_Visit_Fg FROM [FSSRC].[dbo].Qry_Sales_Group Sales_Group INNER JOIN (Select QH.[question_code] ,Question_Header.Description ,CONVERT(datetime,DATEADD(day, (qh.cycle_day-1), p.start_date),6) Logged_Time ,SUBSTRING([entity_code],1,5) SR_Code ,[response] ,Territory_Code SR_Territory_Code ,'Not Customer Related' Que_Desc From question_history QH INNER JOIN Period P ON p.period_code = qh.period_code INNER JOIN [RC_DWDB_INSTANCE_1].[dbo].[Tbl_Territory_In_Sales_Responsible] as Territory_In_Sales_Responsible ON Territory_In_Sales_Responsible.SalesPerson_Purchaser_Code=SUBSTRING([entity_code],1,5) COLLATE Latin1_General_CI_AS INNER JOIN dbo.questions Question_Header ON Question_Header.question_code = QH.question_code WHERE [entity_code] like '%.USER%' AND Question_Header.Question_Code IN('AME01','ASE01','ACO01','ALU01','AOS01','APH01','ATR01','ATE01','ACR06','ACR05','ACR02','ACR03','ACR08','ACR07') AND CONVERT(datetime,DATEADD(day, (qh.cycle_day-1), p.start_date),6) = '11/9/2007' ) Non_Customer_Activities ON Sales_Group.Code = Non_Customer_Activities.SR_Territory_Code UNION ALL SELECT Customer_Activities.Customer_Code, NULL, NULL, Region AS Region, Name AS Territory_Name, Customer_Activities.Customer_Name AS Store_Name, Customer_Activities.Logged_Time AS TheDate, NULL AS Response, is_Visit_Fg FROM [FSSRC].[dbo].Qry_Sales_Group Sales_Group INNER JOIN (Select distinct time_log Logged_Time ,[entity_code] Customer_Code ,[name] Customer_Name ,Territory_Code Cust_Territory_Code ,MAX(is_Visit_Fg) Is_Visit_Fg From question_history QH INNER JOIN Period P ON p.period_code = qh.period_code INNER JOIN dbo.questions Question_Header ON Question_Header.question_code = QH.question_code INNER JOIN [FSSRC].[dbo].[customer] ON Entity_Code = [customer_code] INNER JOIN [FSSRC].[dbo].[visit] V ON V.[customer_code] = QH.[entity_code] AND V.[period_code] = QH.[period_code] AND V.[cycle_day] = QH.[cycle_day] INNER JOIN [RC_DWDB_INSTANCE_1].[dbo].[Tbl_Territory_In_Sales_Responsible] as Territory_In_Sales_Responsible ON Territory_In_Sales_Responsible.SalesPerson_Purchaser_Code=[sales_person_code] COLLATE Latin1_General_CI_AS WHERE [entity_code] NOT like '%.USER%' AND Convert(datetime,convert(Varchar,time_log,110)) = '11/9/2007'
GROUP BY time_log ,[entity_code] ,[name] ,Territory_Code ) Customer_Activities ON Sales_Group.Code = Customer_Activities.Cust_Territory_Code WHERE @Region_Key=Region_Key order by 4 END
I'm still having this problem after hours of research. I've tried changing my connection string, changing the name of my instance, reconfiguring SQL Express, and even went so far as to take my firewall down completely when adding Sql Browser and opening port 1434 to the exceptions list didn't work.
I've read the blog at http://blogs.msdn.com/sql_protocols/archive/2007/05/13/sql-network-interfaces-error-26-error-locating-server-instance-specified.aspx by Xinwei Hong - it seemed to be a nice approach to this problem and has obviously helped numerous people, but sorry Xinwei, it's not working for me.
I get the following error message when I try to use the Login control (out-of-the-box):
Server Error in '/' Application. An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings, SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)
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.
SQLExpress database file auto-creation error:
The connection string specifies a local Sql Server Express instance using a database location within the applications App_Data directory. The provider attempted to automatically create the application services database because the provider determined that the database does not exist. The following configuration requirements are necessary to successfully check for existence of the application services database and automatically create the application services database:
If the applications App_Data directory does not already exist, the web server account must have read and write access to the applications directory. This is necessary because the web server account will automatically create the App_Data directory if it does not already exist. If the applications App_Data directory already exists, the web server account only requires read and write access to the applications App_Data directory. This is necessary because the web server account will attempt to verify that the Sql Server Express database already exists within the applications App_Data directory. Revoking read access on the App_Data directory from the web server account will prevent the provider from correctly determining if the Sql Server Express database already exists. This will cause an error when the provider attempts to create a duplicate of an already existing database. Write access is required because the web server accounts credentials are used when creating the new database. Sql Server Express must be installed on the machine. The process identity for the web server account must have a local user profile. See the readme document for details on how to create a local user profile for both machine and domain accounts.
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: (Edited to preserve page formatting) [SqlException (0x80131904): An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)] System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +800131 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +186 System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject) +737554 System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject) +114 System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart) +421 System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +181 System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +173 System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +133 System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup) +27 System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +47 System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105 System.Data.SqlClient.SqlConnection.Open() +111 System.Web.Management.SqlServices.GetSqlConnection(String server, String user, String password, Boolean trusted, String connectionString) +68
Version Information: Microsoft .NET Framework Version:2.0.50727.1433; ASP.NET Version:2.0.50727.1433
When I click the [>] arrow in the upper-right corner of the login control and choose "Administer Website", click the "Select a single provider for all site management data" only one provider is listed: AspNetSqlProvider (default, selected). When I click the "Test" link, it hesitates for a while, then finally gives me the following screen:
Provider Management Could not establish a connection to the database. If you have not yet created the SQL Server database, exit the Web Site Administration tool, use the aspnet_regsql command-line utility to create and configure the database, and then return to this tool to set the provider.
I've verified (reverified, and re-reverified - to the point of performing several copy-paste functions from SQL Server Management Studio Express) the server and instance names. They are correct. I can connect to the databases through the Server Management Studio, but not through my websites.
I've tried using ServerInstance as well as Server\Instance, Server/Instance, and ServerInstance, port# - ALL to no avail.
This is all being done through localhost, so I don't see that this should be a DNS issue. I've verified that my SQL Server Browser service is running (and have restarted it after various configuration changes). I've added Sql Server Browser to the list of exceptions as well as opened up port 1433 and 1434 on my firewall. I've even tried taking my firewall down completely to run tests. And still I get that error.
[Edit: Accidentally posted prematurely]
Continuing on... my CURRENT connection string is as follows:
I would like to be pulling my hair out right now, but I've just about ripped it all out, so I'm running short on that as of yet. Does anyone have a solution for this problem? There may be something larger going on, since when I use the Login Control and set the "VisibleWhenLoggedIn" property to "False", it hides the control even when there's noone logged in (and nothing in the aspnetdb database.
