|
First of all, separate the serial number and the primary key. If you use a Guid as the key, you can "calculate" it (Guid.NewGuid() ) already on the client. In case of an autoincrement value, you can query it from the database with a SELECT @@ID .
Your serial number column should still have a unique index.
When you insert your data, omit the serial number first.
You can take two different approaches here:
(1) Create a trigger. In the trigger function, calculate your serial number. Take care that two calculations may happen at the same time in different threads. The trigger function then updates your data. Retrieve the inserted serial number with a select query.
(2) Calculate the serial number on the client. Do an update query. When the update fails due to a duplicate value, calculate again with adjusted parameters, and try again, till the update does not fail. Here, I'd use a transaction, and commit when the update was succesful. Looks bad, but with a low chance of duplicates, it will not cause bad performance.
|
|
|
|
|
Hi,
I am getting a different error:
1045 - Access denied for user "root@192.168.1.9" using password: YES
I am sure about the password because I am able to login to it on the server itself which has the IP 192.168.1.1 but I am unable to login from my PC which has the IP 192.168.1.9
Kindly help..
|
|
|
|
|
You need to allow access to the server from all locations. It looks like you have set access to the server from localhost only.
When I was a coder, we worked on algorithms. Today, we memorize APIs for countless libraries — those libraries have the algorithms - Eric Allman
|
|
|
|
|
You need to add additional access rights by executing the following command in Mysql console line:
GRANT ALL on <dbname>.* TO 'root'@'192.168.1.%'
This will allow all computers in the 192.168.1.x subnet to login to your MySQL server.
|
|
|
|
|
can I replace the
<dbname> with EVERYTHING so full access will be granted to all databases for everyone in that IP range?
|
|
|
|
|
Sure just replace it with a '*' and you will grant access to all the schema's in the database.
|
|
|
|
|
I did but still getting the same when using Navicat as well Visual Studio? How can I troubleshoot?
Access denied for user 'root'@'192.168.1.9' (using password: YES)
|
|
|
|
|
any idea what can I do to fix this?
FYI: MySQL is running on debian..
|
|
|
|
|
Is this a VPS or dedicated server, if so have you verified that MySQL is even listening to external IP addresses. Most hosting providers will limit access to MySQL from outside the server itself.
If you have a VPS or dedicated server look in the '/etc/mysql/my.cnf'
And make sure the rule below is not applied or change it to your network IP address for that server.
bind-address = 127.0.0.1
|
|
|
|
|
Problem solved by just changing the password of the root to another password although the old password was correct but I don't know why I was getting that error..
anyhow, thanks everyone..
|
|
|
|
|
how to create a scheduled job using query in sql2008. I need a sample!
Thanks in advance
|
|
|
|
|
|
|
|
Does anyone know the best way to join fields from several tables onto a single SSRS report for SSRS 2005 and SSRS 2008?
|
|
|
|
|
Just guessing as it has been a while sine I did any SSRS, either concatenate the fields in the cell definition if you are in a matrix or create an expression and use that for the textbox content.
There is also the option of creating a formula and using that but I can remember the entry point for the code editor.
[edit]
Reread you question - several tables. I always use a stored proc and return 1 table as a result set
[/edit]
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Will this work if you need to join several tables from different databases which would mean different data sources?
|
|
|
|
|
It is possible especially if the DBs are on the same server, if they are not then the hassle of linked servers is irritating. Investigate doing the join in code in the report. You can throw the values into variables an manipulte the vars.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
HI All,
I use import and export wizard in sql server 2008 R2 to transfer data from sql server 2008 R2 to sql azure, but when i transfer a tables that contains a identity column the option enable identity insert doesn't work right.
Please help me to solve this issue.
|
|
|
|
|
|
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
-- --------------------------------------------------
|
|
|
|