|
|
Hi,
I am getting the above error when trying to connect to one database on mysql.
I don't get the same error when connecting to other databases on the same reason.
could you please help..
|
|
|
|
|
MySQL times out the connection if it is not used for a long time. The best thing to do is to make the connection only when you want to do the query, and then close it.
The difficult we do right away...
...the impossible takes slightly longer.
|
|
|
|
|
I am trying to create a database and it's tables programmatically. I am following the Instructions from the link below but i keep getting this error ExecuteNonQuery failure. Please can someone look at the code? especially the table generation part because the Database creates but there are no tables in it.
<a href="http://www.codeproject.com/Articles/23823/Create-a-SQL-Database-Programmatically">Create a SQL Database Programmatically</a>[<a href="http://www.codeproject.com/Articles/23823/Create-a-SQL-Database-Programmatically" target="_blank" title="New Window">^</a>]
|
|
|
|
|
You should indicate what database server and programming language you are using, show (the relevant part of) the actual code, and the exact error message(s) you are getting; without any detailed information, all we can give you is a wild guess: maybe the table name you have chosen clashes with an SQL keyword.
|
|
|
|
|
I am using SQL Server 2008 R2 with C# running VS2010
The Code that gives the Error is this:
StringBuilder sb = new StringBuilder();
try
{
sb.AppendLine(string.Format("Starting to create {0} with login to {1}", this.tbDBName.Text, this.tbBTSAppUser.Text));
sb.AppendLine("Connecting to DB Server");
this.tbProgress.Text = sb.ToString();
this.tbProgress.ScrollToCaret();
//Connect to the local, default instance of SQL Server.
string srvname = this.cbServers.SelectedItem as string;
Server srv;
if (srvname == null)
{
srv = new Server();
sb.AppendLine("Connected to local SQL server");
}
else
{
srv = new Server(srvname);
sb.AppendLine(string.Format("Connected to {0}", srvname));
}
this.tbProgress.Text = sb.ToString();
this.tbProgress.ScrollToCaret();
//Define a Database object variable by supplying the server and the database name arguments in the constructor.
Database db = srv.Databases[this.tbDBName.Text.Trim()];
if (db != null)
{
if (MessageBox.Show(string.Format("The '{0}' already exists do you want to drop it?", this.tbDBName.Text), "Warning", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
{
db.Drop();
}
else
{
if (MessageBox.Show(string.Format("Create the Tables and Stored Procedures for BT Error Manager on '{0}'?", this.tbDBName.Text), "Warning", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
{
sb.AppendLine("Creating the Tables and Stored Procedures.");
this.tbProgress.Text = sb.ToString();
db.ExecuteNonQuery(dbstring);
string ConnectionString = "Integrated Security=SSPI;" +
sb.AppendLine(string.Format("Created the Tables and Stored Procedures for BT Error Manager on '{0}'", this.tbDBName.Text));
this.tbProgress.Text = sb.ToString();
this.tbProgress.ScrollToCaret();
}
sb.AppendLine("Proceed or select another database");
this.tbProgress.Text = sb.ToString();
this.tbProgress.ScrollToCaret();
return;
}
}
sb.AppendLine("Creating the database.....");
db = new Database(srv, this.tbDBName.Text);
this.tbProgress.Text = sb.ToString();
this.tbProgress.ScrollToCaret();
//Create the database on the instance of SQL Server.
db.Create();
sb.AppendLine("Created the database.");
sb.AppendLine("Creating the Tables and Stored Procedures.");
this.tbProgress.Text = sb.ToString();
this.tbProgress.ScrollToCaret();
//'Reference the database and display the date when it was created.
db.ExecuteNonQuery(dbstring);
sb.AppendLine("Success!");
this.tbProgress.Text = sb.ToString();
this.tbProgress.ScrollToCaret();
}
catch (Exception ex)
{
Debug.WriteLine(ex.ToString());
sb.AppendLine("Failuer:" + ex.Message);
this.tbProgress.Text = sb.ToString();
this.tbProgress.ScrollToCaret();
}
sb.AppendLine("Proceed or select another database");
this.tbProgress.Text = sb.ToString();
this.tbProgress.ScrollToCaret();
}
dbstring is read from a text file it's content is:
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 03/11/2012 05:12:44
-- Generated from EDMX file: C:\Users\SOLO\Desktop\Second\Anoda Code Project\CreateDB\CreateDB\Model1.edmx
-- --------------------------------------------------
SET QUOTED_IDENTIFIER OFF;
GO
USE [mydb];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO
-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------
-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------
-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------
-- Creating table 'Students'
CREATE TABLE [dbo].[Students] (
[Id] int IDENTITY(1,1) NOT NULL,
[FirstName] nvarchar(max) NOT NULL,
[LastName] nvarchar(max) NOT NULL,
[RegID] nvarchar(max) NOT NULL
);
GO
-- Creating table 'Courses'
CREATE TABLE [dbo].[Courses] (
[Id] int IDENTITY(1,1) NOT NULL,
[Code] nvarchar(max) NOT NULL,
[Units] nvarchar(max) NOT NULL,
[Title] nvarchar(max) NOT NULL
);
GO
-- Creating table 'StudentRegs'
CREATE TABLE [dbo].[StudentRegs] (
[Id] int IDENTITY(1,1) NOT NULL,
[Student_Id] int NOT NULL,
[Course_Id] int NOT NULL
);
GO
-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------
-- Creating primary key on [Id] in table 'Students'
ALTER TABLE [dbo].[Students]
ADD CONSTRAINT [PK_Students]
PRIMARY KEY CLUSTERED ([Id] ASC);
GO
-- Creating primary key on [Id] in table 'Courses'
ALTER TABLE [dbo].[Courses]
ADD CONSTRAINT [PK_Courses]
PRIMARY KEY CLUSTERED ([Id] ASC);
GO
-- Creating primary key on [Id] in table 'StudentRegs'
ALTER TABLE [dbo].[StudentRegs]
ADD CONSTRAINT [PK_StudentRegs]
PRIMARY KEY CLUSTERED ([Id] ASC);
GO
-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------
-- Creating foreign key on [Student_Id] in table 'StudentRegs'
ALTER TABLE [dbo].[StudentRegs]
ADD CONSTRAINT [FK_StudentStudentReg]
FOREIGN KEY ([Student_Id])
REFERENCES [dbo].[Students]
([Id])
ON DELETE NO ACTION ON UPDATE NO ACTION;
-- Creating non-clustered index for FOREIGN KEY 'FK_StudentStudentReg'
CREATE INDEX [IX_FK_StudentStudentReg]
ON [dbo].[StudentRegs]
([Student_Id]);
GO
-- Creating foreign key on [Course_Id] in table 'StudentRegs'
ALTER TABLE [dbo].[StudentRegs]
ADD CONSTRAINT [FK_CourseStudentReg]
FOREIGN KEY ([Course_Id])
REFERENCES [dbo].[Courses]
([Id])
ON DELETE NO ACTION ON UPDATE NO ACTION;
-- Creating non-clustered index for FOREIGN KEY 'FK_CourseStudentReg'
CREATE INDEX [IX_FK_CourseStudentReg]
ON [dbo].[StudentRegs]
([Course_Id]);
GO
-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------
|
|
|
|
|
Please edit this message and put your code between <pre> tags so it is readable. You also need to indicate the line that gives the error and the exact error message that you see.
solomon201 wrote: The Code that gives the Error is this:
StringBuilder sb = new StringBuilder();
That is not possible.
Unrequited desire is character building. OriginalGriff
I'm sitting here giving you a standing ovation - Len Goodman
|
|
|
|
|
Is is if you don't have system.Text referenced!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
True; my comment was rather flippant.
Unrequited desire is character building. OriginalGriff
I'm sitting here giving you a standing ovation - Len Goodman
|
|
|
|
|
Off-hand I'd guess that it is the GO lines.
|
|
|
|
|
I am using ROLLUP build in function
SELECT Product,COUNT(Total),
FROM myTable
Group BY TOTAL with ROLLUP
Percent - is calculated %.
when I run it for product I am getting NULL in ROLLUP row.
How to I replace with word?
so it looks like this
TOTAL 23 100 instead of NULL 23 100
|
|
|
|
|
SELECT
case when (Grouping(Product) = 1) then 'Total'
else Product
end Product,
COUNT(Product) as Counter
FROM myTable
Group BY Product with ROLLUP
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
nils illegitimus carborundum
me, me, me
|
|
|
|
|
Well I'll be buggered, I did not know the keyword rollup, thank you have 5.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I'll take the 5, thank you, but pass on the buggery, if you don't mind.
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
nils illegitimus carborundum
me, me, me
|
|
|
|
|
Hi All,
I try to transfer my database from sql server 2008 to sql azure but the all ways failed until this time.
Please help me.. How I can transfer a database (Schema script + Data) from a local sql server 2008 to sql azure.
Thanks all
|
|
|
|
|
Doesn't the backup-database-on-MSSQL2008-and-restore-it-on-azure work? Honestly, I have not use Azure yet, but because it worked on 2000-2005-2008, and I guess, it would work on Azure as well.
hth,
foxyland
|
|
|
|
|
when i try to restore it the following error shown:
Statement 'RESTORE DATABASE' is not supported in this version of SQL Server.
|
|
|
|
|
|
I think there is edition mismatch between both the database instances where you are trying to backup and restore. Please check the edition of your source instance and destination of target instance. you cannot restore from a higher edition to a lower edition. For Ex- Restoration from standard edition backup to workgroup edition is not possible .
sheela
DBA
|
|
|
|
|
Have a read of this
Migrating database to SQL Azure[^]
Lobster Thermidor aux crevettes with a Mornay sauce, served in a Provençale manner with shallots and aubergines, garnished with truffle pate, brandy and a fried egg on top and Spam - Monty Python Spam Sketch
|
|
|
|
|
I wrote a query that gets the counts of which has different statuses.
The query goes like
SELECT COUNT(rec.rmaNum) as Created
FROM RMARecords rec
LEFT JOIN RMAUsers ucr on ucr.id = rec.userCreated
WHERE rec.rmaStatus = 0
UNION
SELECT COUNT(rec.rmaNum) as Received
FROM RMARecords rec
LEFT JOIN RMAUsers ure on ure.id = rec.userCreated
WHERE (rec.rmaStatus = 1 OR rec.rmaStatus = 2)
UNION
SELECT COUNT(rec.rmaNum) as Closed
FROM RMARecords rec
LEFT JOIN RMAUsers ucl on ucl.id = rec.userCreated
WHERE rec.rmaStatus = 3
UNION
SELECT COUNT(rec.rmaNum) as Voided
FROM RMARecords rec
LEFT JOIN RMAUsers ucl on ucl.id = rec.userCreated
WHERE rec.rmaStatus = 4
The output of this query is;
Created
-------
0
1
3
6
I think because of the union the result set comes like this. What I want as output is different
Created Received Closed Voided
--------/----------/-------/--------
--0-----/-----1----/--3--/----6---
How can I get as a result set like the upper result? Thanks in advance.
|
|
|
|
|
I'd do it this way.
select sum(created_count), sum(received_count), sum(closed_count), sum(voided_count) from (
SELECT COUNT(rec.rmaNum) created_count
,0 received_count
,0 closed_count
,0 voided_count
FROM RMARecords rec
LEFT JOIN RMAUsers ucr on ucr.id = rec.userCreated
WHERE rec.rmaStatus = 0
UNION
SELECT 0 created_count
,COUNT(rec.rmaNum) received_count
,0 closed_count
,0 voided_count
FROM RMARecords rec
LEFT JOIN RMAUsers ure on ure.id = rec.userCreated
WHERE (rec.rmaStatus = 1 OR rec.rmaStatus = 2)
UNION
SELECT 0 created_count
,0 received_count
,COUNT(rec.rmaNum) closed_count
,0 voided_count
FROM RMARecords rec
LEFT JOIN RMAUsers ucl on ucl.id = rec.userCreated
WHERE rec.rmaStatus = 3
UNION
SELECT 0 created_count
,0 received_count
,0 closed_count
,COUNT(rec.rmaNum) voided_count
FROM RMARecords rec
LEFT JOIN RMAUsers ucl on ucl.id = rec.userCreated
WHERE rec.rmaStatus = 4
);
You could also look into creating a view for those joins. 
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
|
|
|
|
|
Hello Chris,
In your first line
select sum(created_count), sum(received_count), sum(closed_count), sum(voided_count) from (
I am having errors in this line for the column names which you placed inside the brackets. For; created_count, received_count, closed_count, voided_count.
The error is; invalid column name.
In my sql statement I am only looking to the column name rmaNum.
|
|
|
|
|
Your original post included the 'as' keyword for aliasing. Perhaps your DB requires that. Try the following
...
SELECT COUNT(rec.rmaNum) as created_count
,0 as received_count
,0 as closed_count
,0 as voided_count
FROM RMARecords rec
...
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
|
|
|
|
|
You might try:
SELECT
SUM(CASE WHEN rec.rmaStatus = 0 THEN 1 ELSE 0 END) as Created
SUM(CASE WHEN (rec.rmaStatus = 1 OR rec.rmaStatus = 2) THEN 1 ELSE 0 END) AS Received
SUM(CASE WHEN rec.rmaStatus = 3 THEN 1 ELSE 0 END) AS Closed
SUM(CASE WHEN rec.rmaStatus = 4 THEN 1 ELSE 0 END) AS Voided
FROM RMARecords rec
LEFT JOIN RMAUsers ucr on ucr.id = rec.userCreated
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
