USE Database Statement

Dec 4, 1998

I'm having a problem with the following script:
use master
declare database_cursor CURSOR for
SELECT name from sysdatabases
OPEN database_cursor
DECLARE @databasename varchar (30)
FETCH next from database_cursor into @databasename
while (@@fetch_status <> -1)

BEGIN
use @databasename
exec sp_spaceused
FETCH next from database_cursor into @databasename
END

The substitution of database name in the USE statement does not work correctly. It substitutes in the length, '30', from the declare variable statement. I've had one suggestion to try declaring the variable as DECLARE @DATABASENAME SYSNAME but that has not worked. Any ideas?

View 1 Replies


ADVERTISEMENT

Help Me Pls - With Database And Insert Statement

Aug 27, 2006

I Have an error:  Server Error in '/quanlythietbi' Application. INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'FK_yeucau_nhanvien'. The conflict occurred in database 'equipment', table 'nhanvien', column 'manv'. The statement has been terminated. Source Error: Line 129:mycommand.Parameters.Add(new SqlParameter("@noidung_yc1",System.Data.SqlDbType.Text));
Line 130:mycommand.Parameters["@noidung_yc1"].Value = TextBox1.Text;
Line 131:int i = mycommand.ExecuteNonQuery();
Line 132:if (i>0)
Line 133:{and this is my code:string sqlstring = "Select * from yeucau where ngayGiaiQuyetxong='"+ Label8.Text +"' and date_yc='" + Label7.Text + "' and manv_yc='"+ TextBox2.Text + "' and noidung_yc='"+ TextBox1.Text+ "'";myconnection = new SqlConnection(stringconn);mycommand = new SqlCommand(sqlstring,myconnection);myconnection.Close();myconnection.Open();mycommand = new SqlCommand(insertquery,myconnection); mycommand.Parameters.Add(new SqlParameter("@ngayGiaiQuyetxong1",System.Data.SqlDbType.Char,10));mycommand.Parameters["@ngayGiaiQuyetxong1"].Value = Label8.Text;mycommand.Parameters.Add(new SqlParameter("@date_yc1",System.Data.SqlDbType.SmallDateTime));mycommand.Parameters["@date_yc1"].Value = Label7.Text;mycommand.Parameters.Add("@manv_yc1",System.Data.SqlDbType.Char,10);mycommand.Parameters["@manv_yc1"].Value = TextBox2.Text;mycommand.Parameters.Add(new SqlParameter("@noidung_yc1",System.Data.SqlDbType.Text));mycommand.Parameters["@noidung_yc1"].Value = TextBox1.Text;int i = mycommand.ExecuteNonQuery();if (i>0){lbcheck.Text = "Ä?ã Cập Nhật Yêu Cầu.";} --------------------------------------------------------------------------------------------------------------------------------I don't know what I must do to repair it :(

View 2 Replies View Related

Cannot Update Database Using Sql Statement... Help Please

Apr 19, 2008

Hi All,
 I have watched one of the video tutorials under SQL Server section called "Connecting your web application to SQL server 2005 express edition" (number 8).
It was about creating a database with single table and then updating it by inserting new records in a text box an clicking on submit button. In code section there is a sql insert statement and connection string is via configurationManager (name of the connection string is taken from web.config and is spelled properly)
Anyway, my problem is that updating that database by filling the textbox and clicking submit button is not working.
I have no error message (nothing happens) - database is not updated.
If I add to the page a data grid view with updating and inserting selected then it all works. I was trying to use debbuging with break point setup but couldn't find any errors.I have done everything the same way as on the video.... I am beginner in C# so your help will be much appreciated.
Here is the code:
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;public partial class _Default : System.Web.UI.Page
{protected void Page_Load(object sender, EventArgs e)
{
}protected void Submitbutton_Click(object sender, EventArgs e)
{
 
 SqlDataSource dashDataSource = new SqlDataSource();dashDataSource.ConnectionString = ConfigurationManager.ConnectionStrings["dashConnectionString1"].ToString();
 
dashDataSource.InsertCommandType = SqlDataSourceCommandType.Text;dashDataSource.InsertCommand = "INSERT INTO Email(EmailAddress, IPAddress, DateTimeStamp) VALUES (@EmailAddress, @IPAddress, @DateTimeStamp)";
dashDataSource.InsertParameters.Add("EmailAddress", EmailAddressTextBox.Text);dashDataSource.InsertParameters.Add("IPAddress", Request.UserHostAddress.ToString());
dashDataSource.InsertParameters.Add("DateTimeStamp", DateTime.Now.ToString());int rowsaffected = 0;
try
{
rowsaffected = dashDataSource.Insert();
}catch (Exception ex)
{Server.Transfer("Newsletter_problem.aspx");
}
finally
{dashDataSource = null;
}if (rowsaffected != 1)
{Server.Transfer("Newsletter_problem.aspx");
}
else
{Server.Transfer("Newsletter_confirm.aspx");
}
 
 
}
}

View 4 Replies View Related

Use Database Statement In Script

Nov 24, 1998

I'm having a problem getting the database name to substitute properly in the
USE database statement when used in a SQL script. Essentially, I have a
variable defined as follows:
declare @databasename varchar(30)
use @databasename
After the variable is initialized with a database name, the USE statement
does not substitute the correct value, instead it uses 30, the length of its
description in the 'declare' statement. Any ideas to force a correct value
substitution?

View 2 Replies View Related

Produce SQL Statement For A Database

Oct 20, 2004

hi
is there any feature in MSSql that produce SQL statement for a database(include CREATE TABLE, INSERT Records, ...)

View 2 Replies View Related

Truncate Database Statement Help

Jul 23, 2005

Hello,I hope you can help me.We have a SQL Server 2000 database at work, (which works with a VB6frontend) which grew to a considerable size, so one of my past colleaguessent me this truncate statement to use on the database.I ran it like this, and all appeared to be well, it shrank the database,Shortly afterwards, after I ran this I noticed something not quite right.for example, one of the tables we keep is a User_Admin table which has 50records in there, when I accessed this using the frontend it only showed 1record, and this happened with every table in the database accessed by thefrontend.The data in the tables is still there, but only shows one record through thefrontend.Can anyone help me, and check this statement I ran, as its weird that allthis happened after I ran this statement.Thanks,Jayne Hegersp_dboption PBTDEV,'trunc. log on chkpt.', truecheckpoint--sp_dboption sadev,'trunc. log on chkpt.', falsesp_dboption PBTDEV,'autoshrink', true--sp_dboption sadev,'autoshrink', falseDBCC SHRINKDATABASE (PBTDEV, NOTRUNCATE)

View 2 Replies View Related

SQL Between Dates Statement In Access Database

Apr 8, 2005

I have following SQL statement which I run on an Access database. ( Program written in VB.NET )

'---------
SELECT c.*
FROM Communications AS c
WHERE CommDate Between #1/4/2005# And #5/4/2005#;
'------------

I've put dummy values in the database for dates between 1st and 8th of april... And running this query suprisingly gives back ALL rows in the table... Also the entries for the 6th, 7th and 8th of april... In fact it just gives back the WHOLE table, no matter which values I add...

If I use '1/4/2005' (not with #) then I get an error back.

Anybody an Idea how to make a BETWEEN statement work in an access database?

Thanks,
Frederik

View 2 Replies View Related

How To Specify Which Database To Use For A Select Statement Within A Cursor?

Nov 9, 2007

Hi everyone,

I have been trying to perform the following task:

Using the sys.databases & sys.sysindexes views to display all the columns with a clustered index for all tables and all databases in a given server. So the end result will have 3 columns:

Database name
Table name
Column name from that table with a clustered index

I have already created the following script which displays all the databases for a given server:

declare @DBname nvarchar(128)
declare testCursorForDB cursor
for
select name from sys.databases with (nolock)
where name not in ('master','tempdb','model','msdb')
order by name

open testCursorForDB
fetch next from testCursorForDB
into @DBname

while @@fetch_status = 0
begin
print @DBname
fetch next from testCursorForDB
into @DBname
end

close testCursorForDB
deallocate testCursorForDB

I also have created the following query which will display all the table and column names which have a clustered index for a given database:

select object_name(i.id) as TableName,

i.name as IndexName
from sys.sysindexes as i with (nolock)
where i.indid = '1'

However, what I need help/advice on is how do I combine these two together into one working script (either using nested cursors or a better way). In other words, how can I specify which database to use (ie. using the "use database_name") so that my query above will be applied to each database found within the cursor.

Any help is greatly appreciated

Thanks!

View 7 Replies View Related

How Can I Use The Use Statement With Database-name Contains Spaces In Sql2000

Jan 20, 2006

example :

i cann't write :

use [my db]

what can i do with this problem ?

View 1 Replies View Related

FILENAME Option In CREATE DATABASE Statement

Nov 9, 2004

Hi,

I have a problem with CREATE DATABASE statement, since it needs to specify the absolut path for a file in the FILENAME= option. I would need instead a relative path, i.e. only the name of the file because the script that launch this SQL command is dependant on the installation path decided by the user, during the installation.
Is there a way to pass to the FILENAME only the name of the file?
Hope to have been clear :-)

Thanks in advance

View 4 Replies View Related

Generate SQL Insert Statement Fro SQL Server Database

Jun 20, 2006

Good day,

I have seen in Enterprise manager there is a toll that can script the all tables in a database, but nothing that can generate the insert statements for all the rows in each table in a secified database.

Does any one know of a application, plug in, script that can generate the insert statments for all the tables in a database?

Please someone help, this is driving me insane.

Thanks

View 5 Replies View Related

Transact SQL :: Transaction On Alter Database Statement

Apr 20, 2015

Im working on Partition purge process, where I need to specify following statement:

SET @cmd = 'ALTER PARTITION FUNCTION ' + @function_name + '() MERGE RANGE (@range)'
EXEC (@cmd);
SET @cmd1 = 'ALTER DATABASE '+ db_name()+ ' REMOVE FILE ' + @partition_file
EXEC (cmd1);

I want to put this statement in Begin Tran /Commit statement but getting error that it is not allowed.  "ALTER DATABASE statement not allowed within multi-statement transaction"..what are my options to rollback in case there is a failure. 

View 4 Replies View Related

Can't Rollback Alter Database Statement DDL Trigger

Feb 26, 2008

Recently I created a DDL Server-scope trigger using the following:

create trigger stop_alter_database on all server
for ALTER_DATABASE
as
rollback;
print 'database change stopped by stop_alter_database';
go

Then I ran the following script:

alter database [test] modify file (name=test', maxsize=2028mb);


The result was:


Msg 3609, Level 16, State 2, Line 1

The transaction ended in the trigger. The batch has been aborted.

database change stopped by stop_alter_database

The problem is that when I checked the max size of the data file it had changed. So, the statement was never rolled back. Is there something I'm missing because I can't find any documentation or articles that state the inability to rollback alter database statements. Whats going on?

View 2 Replies View Related

Error Running Insert Statement In A Database

Aug 15, 2007

When I run this query against PFGDSMRISSQLQ1 in Server manager 2005:use <database>INSERT INTO dbo.<table> (<column1>, <column2>, <column3>)VALUES (12598,2900,1.00)I get this error:Msg 8624, Level 16, State 1, Line 5Internal Query Processor Error: The query processor could not produce a query plan. For more information, contact Customer Support Services.Can someone please help. Thanks!

View 1 Replies View Related

SQL Select Statement (Textbox Used To Grab Data From Database)

Sep 21, 2007

I have a problem....SOMEONE PLEASE HELP!


Here is the setup.
Text Box: User enters in customer transaction number
Button: User clicks button to display information about the customer

Now the database has a lot of unique customer numbers. What I am trying to do is take what the user enters so it can search the database and pull out that customers information. I am having a hard time getting that information from the textbox. Any suggestions! Here is what I have so far.



Private Sub btnViewFlow_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnViewFlow.Click

Try



Me.SqlConnection1 = New System.Data.SqlClient.SqlConnection

Me.SqlDataAdapter1 = New System.Data.SqlClient.SqlDataAdapter

Me.SqlSelectCommand1 = New System.Data.SqlClient.SqlCommand

Me.DataSet11 = New links.DataSet1

CType(Me.DataSet11, System.ComponentModel.ISupportInitialize).BeginInit()

'

'SqlConnection1

'

Me.SqlConnection1.ConnectionString = "workstation id=<14852>;packet size=4096;user id=<userID>;password=<Strong Password>;data source=ZRTPD0WB;p" & _

"ersist security info=False;initial catalog=DTR"

'

'SqlDataAdapter1

'

Me.SqlDataAdapter1.SelectCommand = Me.SqlSelectCommand1

Me.SqlDataAdapter1.TableMappings.AddRange(New System.Data.Common.DataTableMapping() {New System.Data.Common.DataTableMapping("Table", "DTR_Document_Summary", New System.Data.Common.DataColumnMapping() {New System.Data.Common.DataColumnMapping("DocumentId", "DocumentId"), New System.Data.Common.DataColumnMapping("PartnerId", "PartnerId"), New System.Data.Common.DataColumnMapping("PartnerName", "PartnerName"), New System.Data.Common.DataColumnMapping("Direction", "Direction"), New System.Data.Common.DataColumnMapping("TranSet", "TranSet")})})

'

'SqlSelectCommand1

'

Me.SqlSelectCommand1.CommandText = "SELECT DocumentId, PartnerId, PartnerName, Direction, TranSet FROM DTR_Document_S" & _

"ummary "

'WHERE (DTR_Document_Summary.PartnerId = 'txtPartnerId.text.toString')"
'THE STATEMENT ABOVE DOESNT WORK

Me.SqlSelectCommand1.Connection = Me.SqlConnection1

'

'DataSet11

'

Me.DataSet11.DataSetName = "DataSet1"

Me.DataSet11.Locale = New System.Globalization.CultureInfo("en-US")

CType(Me.DataSet11, System.ComponentModel.ISupportInitialize).EndInit()



'Open the connection

SqlConnection1.Open()

TextBox1.Text = "Connection Open"




'Populate DataSet11

SqlDataAdapter1.Fill(DataSet11)

TextBox1.Text = "DataSet11 has been filled!"


'Display Data

DataGrid1.DataBind()

TextBox1.Text = "Here is your requested information"



Catch ex As Exception

TextBox1.Text = ex.Message


End Try
'Close the connection

SqlConnection1.Close()


End Sub

View 6 Replies View Related

Verify Multiple Database Backups Using Single Statement

Oct 25, 2007

Hi all,


I have taken more than one database backups and i have an idea that we can verify single backup using "restore verifyonly from disk='<path>'" statement.
Now my question is Could we verify all(More than one backup) backups with single statement?
Any suggestion would be helpful to me:-)


Thanks in advance,

View 3 Replies View Related

How We Run “SELECT� Statement (in SQL SERVER Query Analyzer) For A Table In Different Database.

Sep 11, 2006

Hi All, Please let me know how we can need to run “SELECT” statement (in SQL SERVER query analyzer) for a table in different database without going/loading DATABASE B (i.e. without running “USE B”) e.g. we are in a database “A” and we need to run “SELECT” command for a table in Database say “B” without going/loading (i.e. without running “USE B”) the Database “B” Thanks in Advance J

View 3 Replies View Related

SQL Server 2008 :: Can Suppress Messages After The Backup Database Statement Is Run

Apr 22, 2015

Wanted to check if there's any option to suppress message after the database backup statement is run like we have in dbcc checkdb by using NO_INFOMSGS?

Processed 448 pages for database 'master', file 'master' on file 1.
Processed 2 pages for database 'master', file 'mastlog' on file 1.
BACKUP DATABASE successfully processed 450 pages in 0.100 seconds (35.151 MB/sec).
Processed 280 pages for database 'model', file 'modeldev' on file 1.
Processed 2 pages for database 'model', file 'modellog' on file 1.
BACKUP DATABASE successfully processed 282 pages in 0.036 seconds (60.994 MB/sec).
Processed 1992 pages for database 'msdb', file 'MSDBData' on file 1.
Processed 1 pages for database 'msdb', file 'MSDBLog' on file 1.

View 0 Replies View Related

The Select Statement Was Denied On The Object 'table Name' , Database 'db Name', Schema 'dbo'

Nov 12, 2007



Hi,

I have a select statement running on the client machine linking to different tables in 1 database. All with the same schema. When I ran it, i had this error. I had trial and error, removing 1 table at a time until i hit the one which is causing it. when i removed it, everything's ok. i just wonder if all the tables were using dbo schema what is causing this particular table to throw this error?

cherriesh

View 1 Replies View Related

DB Engine :: Alter Database With Rollback Immediate Statement Doesn't Work

Nov 9, 2015

Primary platofrm: Sql12k, 7.0 Ultimate Pro OS

I'm launching the aforementioned statement from one MASTER session windows and I get this message, I am stuck, I though ROLLBACK INMEDIATE go throught any already session open.

Msg 5064, Level 16, State 1, Line 1
Changes to the state or options of database 'GFSYSTEM' cannot be made at this time. The database is in single-user mode, and a user is currently connected to it.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.

View 4 Replies View Related

1 Log File Can Contain More Records After Performing Backup Database Statement, 2 Why Can't Insert Data When Is Log Is Not Full

Feb 14, 2008

question 1:

i found that database log file can contain more records after performing backup database statement.

for example:

i create a database and limit the log file to 2mb. then i create a table and insert data.

If i backup the database before i insert data , the database file can contain 192 records unitl the log file is full.


If i don't perform the 'backup database' statement.
The 'dbcc sqlperf(logspace)' indicate the utilization ratio is less than 40% after inserting 192 records

why?

I list my code:



Code Snippet
create database db_test
on primary
(
name=db_test,
filename='C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDatadb_test.mdf'
)
log on
(
name=db_test_log,
filename='C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDatadb_test_log.ldf',
maxsize=2mb
)
go
backup database db_test to disk='db_test.bak' --- if i don't execute this line, log file can contain a lot of record
go
create table db_test..table1(col char(8000))
--insert data to fill up the database log
declare @n int
set @n=0
while @n<192
begin
insert into db_test..table1 values(replicate('a',8000))
set @n=@n+1
end








question 2:
i create a database and limit the log file to 2mb. Then i create a table and insert data in an endless loop.

After the inserting operation executing for a while, the 9002 error occurs, indicate the log file for the database is full.
But the 'dbcc sqlperf(logspace)' command indicate the unilization ratio is low, and log_reuse_wait_desc in sys.database is 'CHECKPOINT'
And I can insert data , and i'm sure the state of log_use_wait_desc is 'CHECKPOINT'.


As i known, the checkpoint can't truncate log under full recovery model. Only the back log operation can truncate the transaction log.
So log is not full, why 9002 error is encounterd. and why the log_reuse_wait_desc return 'CHECKPOINT'?


I list my code:



Code Snippet
create database db_test
on primary
(
name=db_test,
filename='C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDatadb_test.mdf'
)
log on
(
name=db_test_log,
filename='C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDatadb_test_log.ldf',
maxsize=2mb
)
go
create table db_test..table1(col char(8000))

--insert data to fill up the database log
declare @n int
set @n=0
while @n<>-1
begin
insert into db_test..table1 values(replicate('a',8000))
end








any suggestions?


thanks in advance.

View 5 Replies View Related

Multiple Tables Used In Select Statement Makes My Update Statement Not Work?

Aug 29, 2006

I am currently having this problem with gridview and detailview. When I drag either onto the page and set my select statement to pick from one table and then update that data through the gridview (lets say), the update works perfectly.  My problem is that the table I am pulling data from is mainly foreign keys.  So in order to hide the number values of the foreign keys, I select the string value columns from the tables that contain the primary keys.  I then use INNER JOIN in my SELECT so that I only get the data that pertains to the user I am looking to list and edit.  I run the "test query" and everything I need shows up as I want it.  I then go back to the gridview and change the fields which are foreign keys to templates.  When I edit the templates I bind the field that contains the string value of the given foreign key to the template.  This works great, because now the user will see string representation instead of the ID numbers that coinside with the string value.  So I run my webpage and everything show up as I want it to, all the data is correct and I get no errors.  I then click edit (as I have checked the "enable editing" box) and the gridview changes to edit mode.  I make my changes and then select "update."  When the page refreshes, and the gridview returns, the data is not updated and the original data is shown. I am sorry for so much typing, but I want to be as clear as possible with what I am doing.  The only thing I can see being the issue is that when I setup my SELECT and FROM to contain fields from multiple tables, the UPDATE then does not work.  When I remove all of my JOIN's and go back to foreign keys and one table the update works again.  Below is what I have for my SQL statements:------------------------------------------------------------------------------------------------------------------------------------- SELECT:SELECT People.FirstName, People.LastName, People.FullName, People.PropertyID, People.InviteTypeID, People.RSVP, People.Wheelchair, Property.[House/Day Hab], InviteType.InviteTypeName FROM (InviteType INNER JOIN (Property INNER JOIN People ON Property.PropertyID = People.PropertyID) ON InviteType.InviteTypeID = People.InviteTypeID) WHERE (People.PersonID = ?)UPDATE:UPDATE [People] SET [FirstName] = ?, [LastName] = ?, [FullName] = ?, [PropertyID] = ?, [InviteTypeID] = ?, [RSVP] = ?, [Wheelchair] = ? WHERE [PersonID] = ? ---------------------------------------------------------------------------------------------------------------------------------------The only fields I want to update are in [People].  My WHERE is based on a control that I use to select a person from a drop down list.  If I run the test query for the update while setting up my data source the query will update the record in the database.  It is when I try to make the update from the gridview that the data is not changed.  If anything is not clear please let me know and I will clarify as much as I can.  This is my first project using ASP and working with databases so I am completely learning as I go.  I took some database courses in college but I have never interacted with them with a web based front end.  Any help will be greatly appreciated.Thank you in advance for any time, help, and/or advice you can give.Brian 

View 5 Replies View Related

SQL Server 2012 :: Create Dynamic Update Statement Based On Return Values In Select Statement

Jan 9, 2015

Ok I have a query "SELECT ColumnNames FROM tbl1" let's say the values returned are "age,sex,race".

Now I want to be able to create an "update" statement like "UPATE tbl2 SET Col2 = age + sex + race" dynamically and execute this UPDATE statement. So, if the next select statement returns "age, sex, race, gender" then the script should create "UPDATE tbl2 SET Col2 = age + sex + race + gender" and execute it.

View 4 Replies View Related

Using Conditional Statement In Stored Prcodure To Build Select Statement

Jul 20, 2005

hiI need to write a stored procedure that takes input parameters,andaccording to these parameters the retrieved fields in a selectstatement are chosen.what i need to know is how to make the fields of the select statementconditional,taking in consideration that it is more than one fieldaddedfor exampleSQLStmt="select"if param1 thenSQLStmt=SQLStmt+ field1end ifif param2 thenSQLStmt=SQLStmt+ field2end if

View 2 Replies View Related

TSQL - Use ORDER BY Statement Without Insertin The Field Name Into The SELECT Statement

Oct 29, 2007

Hi guys,
I have the query below (running okay):



Code Block
SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02'
FROM myTables
WHERE Conditions are true
ORDER BY Field01

The results are just as I need:


Field01 Field02

------------- ----------------------

192473 8461760

192474 22810



Because other reasons. I need to modify that query to:



Code Block
SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02'
INTO AuxiliaryTable
FROM myTables
WHERE Conditions are true
ORDER BY Field01
SELECT DISTINCT [Field02] FROM AuxTable
The the results are:

Field02

----------------------

22810
8461760

And what I need is (without showing any other field):

Field02

----------------------

8461760
22810


Is there any good suggestion?
Thanks in advance for any help,
Aldo.

View 3 Replies View Related

DB Engine :: Can't Use The MERGE Statement / How To Design WHERE Condition For Insert Statement

Nov 5, 2015

I've have a need with SQL Server 2005 (so I've no MERGE statement), I have to merge 2 tables, the target table has 10 fields, the first 4 are the clustered index and primary key, the source table has the same fields and index.Since I can't use the MERGE statement (I'm in SQL 2005) I have to make a double step operation, and INSERT and an UPDATE, I can't figure how to design the WHERE condition for the insert statement.

View 2 Replies View Related

SQL Server 2012 :: Update Statement With CASE Statement?

Aug 13, 2014

i was tasked to created an UPDATE statement for 6 tables , i would like to update 4 columns within the 6 tables , they all contains the same column names. the table gets its information from the source table, however the data that is transferd to the 6 tables are sometimes incorrect , i need to write a UPDATE statement that will automatically correct the data. the Update statement should also contact a where clause

the columns are [No] , [Salesperson Code], [Country Code] and [Country Name]

i was thinking of doing

Update [tablename]
SET [No] =
CASE
WHEN [No] ='AF01' THEN 'Country Code' = 'ZA7' AND 'Country Name' = 'South Africa'
ELSE 'Null'
END

What is the best way to script this

View 1 Replies View Related

How To Write Select Statement Inside CASE Statement ?

Jul 4, 2006

Hello friends,
I want to use select statement in a CASE inside procedure.
can I do it? of yes then how can i do it ?

following part of the procedure clears my requirement.

SELECT E.EmployeeID,
CASE E.EmployeeType
WHEN 1 THEN
select * from Tbl1
WHEN 2 THEN
select * from Tbl2
WHEN 3 THEN
select * from Tbl3
END
FROM EMPLOYEE E

can any one help me in this?
please give me a sample query.

Thanks and Regards,
Kiran Suthar

View 7 Replies View Related

Transact SQL :: Update Statement In Select Case Statement

May 5, 2015

I am attempting to run update statements within a SELECT CASE statement.

Select case x.field
WHEN 'XXX' THEN
  UPDATE TABLE1
   SET TABLE1.FIELD2 = 1
  ELSE
   UPDATE TABLE2
   SET TABLE2.FIELD1 = 2
END
FROM OuterTable x

I get incorrect syntax near the keyword 'update'.

View 7 Replies View Related

Database Schemas And This Statement Has Attempted To Access Data Whose Access Is Restricted By The Assembly.

Jul 14, 2005

Hello.

View 5 Replies View Related

UPDATE SQL Statement In Excel VBA Editor To Update Access Database - ADO - SQL

Jul 23, 2005

Hello,I am trying to update records in my database from excel data using vbaeditor within excel.In order to launch a query, I use SQL langage in ADO as follwing:------------------------------------------------------------Dim adoConn As ADODB.ConnectionDim adoRs As ADODB.RecordsetDim sConn As StringDim sSql As StringDim sOutput As StringsConn = "DSN=MS Access Database;" & _"DBQ=MyDatabasePath;" & _"DefaultDir=MyPathDirectory;" & _"DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" &_"PWD=xxxxxx;UID=admin;"ID, A, B C.. are my table fieldssSql = "SELECT ID, `A`, B, `C being a date`, D, E, `F`, `H`, I, J,`K`, L" & _" FROM MyTblName" & _" WHERE (`A`='MyA')" & _" AND (`C`>{ts '" & Format(Date, "yyyy-mm-dd hh:mm:ss") & "'})"& _" ORDER BY `C` DESC"Set adoConn = New ADODB.ConnectionadoConn.Open sConnSet adoRs = New ADODB.RecordsetadoRs.Open Source:=sSql, _ActiveConnection:=adoConnadoRs.MoveFirstSheets("Sheet1").Range("a2").CopyFromRecordset adoRsSet adoRs = NothingSet adoConn = Nothing---------------------------------------------------------------Does Anyone know How I can use the UPDATE, DELETE INSERT SQL statementsin this environement? Copying SQL statements from access does not workas I would have to reference Access Object in my project which I do notwant if I can avoid. Ideally I would like to use only ADO system andSQL approach.Thank you very muchNono

View 1 Replies View Related

Compiler Is Not Recognizing My Using Statement For SglConnection Statement

Feb 4, 2006

I am using ASP.NET 2.0, and am attempting to write some code to connect to the database and query a data table. The compiler is not recognizing my SqlConnection statement. It does recognize other commands. And just to make sure, I created other sql objects such as ObjectDataSource and SqlDataSource. The compiler does not find a problem with that code.
Basically the compiler is telling me that I am missing a "using" directive. The compiler is wrong though, because I am including the statement "usingSystemData" Can someone please take a look at my code below and to see if you notice what the problem might be?  Note that I numbered the lines of code below. Note that I also tried putting lines 3 trhough 6 before line 2(The page directive) but that did not fix the problem The compiler still gives me the same compiler message.
Compilation Error
Description: An error occurred during the compilation of a resource required to service this request.Please review the following specific error details and modify your source code appropriately.
Compiler Error Message: CS0246: The type or namespace name 'SqlConnection' could not be found (are you missing a using directive or an assembly reference?)Source Error:
Line 21: SqlConnection sqlConn = new SqlConnection("server=localhost;uid=sa;pwd=password;database=master;");
1 <asp:sqldatasource runat="server"></asp:sqldatasource>
2 <%@ Page Language="C#"%>
3 using System;
4 using System.Data;
5 using System.Collections;
6 using System.Data.SqlClient;
7
8 <script runat=server>
9
10 protected void Page_Load(object o, EventArgs e)
11 {
12 ObjectDataSource dsa; // This works no problems from the compiler here
13 SqlDataSource ds; // This works no problems from the compiler
14
15 if (IsPostBack)
16 {
17 if (AuthenticateUser(txtUsername.Text,txtPassword.Text))
18 {
19 instructions.Text = "Congratulations, your authenticated!";
20 instructions.ForeColor = System.Drawing.Color.Red;
21 SqlConnection sqlConn = new SqlConnection("server=localhost;uid=sa;pwd=password;database=master;");
22 String sqlStmt = "Select UserName from LogIn where UserName='" + txtUsername.Text + "' and password='" + sHashedPassword + "'";
23 }
24 else
25 {
26 instructions.Text = "Please try again!";
27 instructions.ForeColor = System.Drawing.Color.Red;
28 }
29 }
30
31 }
32
33 bool AuthenticateUser(string username, string password)
34 {
35 // Authentication code goes here
36
37 }

View 1 Replies View Related

Case Statement Error In An Insert Statement

May 26, 2006

Hi All,
I've looked through the forum hoping I'm not the only one with this issue but alas, I have found nothing so I'm hoping someone out there will give me some assistance.
My problem is the case statement in my Insert Statement. My overall goal is to insert records from one table to another. But I need to be able to assign a specific value to the incoming data and thought the case statement would be the best way of doing it. I must be doing something wrong but I can't seem to see it.

Here is my code:
Insert into myTblA
(TblA_ID,
mycasefield =
case
when mycasefield = 1 then 99861
when mycasefield = 2 then 99862
when mycasefield = 3 then 99863
when mycasefield = 4 then 99864
when mycasefield = 5 then 99865
when mycasefield = 6 then 99866
when mycasefield = 7 then 99867
when mycasefield = 8 then 99868
when mycasefield = 9 then 99855
when mycasefield = 10 then 99839
end,
alt_min,
alt_max,
longitude,
latitude
(
Select MTB.LocationID
MTB.model_ID
MTB.elevation, --alt min
null, --alt max
MTB.longitude, --longitude
MTB.latitude --latitude
from MyTblB MTB
);

The error I'm getting is:
Incorrect syntax near '='.

I have tried various versions of the case statement based on examples I have found but nothing works.
I would greatly appreciate any assistance with this one. I've been smacking my head against the wall for awhile trying to find a solution.

View 10 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved