Write In Database During DataReader Loop.

Mar 16, 2006

Hello,

I want to insert a value in a database table during a datareader loop. But I get an error saying that the connection (MyConnection) is already open. This is normal because it is open for processing the datareader loop. But how do I solve this? I just want to write a value for logging the process.

This is my code:

Dim iID as Integer = 1

Dim strSQLNewsLetter As String = "SELECT * FROM Members WHERE Members.Enabled=1"
Dim MyCommand=New MySqlCommand(strSQLNewsLetter, MyConnection)
MyConnection.Open()
Dim myReader as MySQLDataReader
 
myReader = MyCommand.ExecuteReader()
 
While myReader.Read()
 If MyReader.GetMySqlDateTime(3).ToString() = "0/00/0000 0:00:00" then
  WriteMyLog(iID, MyReader.GetInt32(0))
 End If
End While
 
myReader.Close()
myConnection.Close()

Function WriteMyLog(iID as Integer, iMemberID as Integer) 'Write Log after Newsletter has been sent
  Dim strSqlLog as String 'Create query
  strSqlLog = "INSERT INTO NewsLetterStats (ID, MemberID, TimeSent) VALUES ("
  strSqlLog &= "" & iID & "," 'NewsLetterID
  strSqlLog &= "" & iMemberID & "," 'MemberID
  strSqlLog &= "'" & MySQLTimeStamp() & "')" 'Timestamp
 
  Dim MyCmdLogNews As New MySqlCommand(strSqlLog, myConnection)
  MyCmdLogNews.Connection.Open()
  MyCmdLogNews.ExecuteNonQuery()
  MyCmdLogNews.Connection.Close()
End Function

View 9 Replies


ADVERTISEMENT

Foreach Loop Read Table Data And Write To File

Sep 21, 2007

Hi,

I want to do the following with a ssis package:

INPUT:
A table contains 2 columns with data i need. column A=Filename and column B=FileContent

PROCESS:
I need to loop through ea record in the table and retrieve columns A and B. Then for ea column i need to write the Content hold in column B into File hold in column A.

I so far found out, that i need a Execute SQL Task in Control Flow querying the table and get columns A and B into 2 variables, plus a 3rd var holding the object. Then the output goes into a Foreach Loop Container. From this point i don't know how to continue. I tried to put a Data Flow Task inside the Foreach Loop, but couldn't find out how i now get the 2 variables to the Data Flow Task and use them to for the file to be written and the content to be placed in the file.

Is there any example similiar to that so i could learn how to start on that?

Thanks
Danny

View 3 Replies View Related

SQL Server 2012 :: Write A Loop On Result Of First Query Inside A Stored Procedure

Jan 23, 2015

I have to write a Stired Procedure with the following functionality.

Write a simple select query say (Select * from tableA) result is

ProdName ProdID
----------------------
ProdA 1
ProdB 2
ProdC 3
ProdD 4

Now with the above result, On every record I have to fire a query Select SUM(sale), SUM(scrap), SUM(Production) from tableB where ProdID= ["ProdID from above query"].How to write this query in a Stored Procedure so that I can get the required SUM columns for all the ProdID's from first query?

View 2 Replies View Related

Datareader Can Not Open Connection To My Database For Login Myusername

Feb 1, 2007

  This is my page_loads event code and iam getting the Exception pasted below the code.
-----------------------------------------------------------------------------------------------------------------------------------------------------
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)
If Not Page.IsPostBack Then
Dim myconnection As New SqlConnection("Data Source=localhostSQLEXPRESS;initial catalog = safetydata.mdf;Integrated Security=True;User Instance=True")
Dim strSQL As String = "SELECT Incident_Id,Emp_No From Report_Incident"
Dim mycommand As New SqlCommand(strSQL, myconnection)
myconnection.Open()
Dim reader As SqlDataReader = mycommand.ExecuteReader()
 
Dim chart As New PieChart
chart.DataSource = reader
chart.DataXValueField = "Incident_id"
chart.DataYValueField = "Emp_No"
chart.DataBind()
 
chart.DataLabels.Visible = True
 
ChartControl1.Charts.Add(chart)
ChartControl1.RedrawChart()
myconnection.Open()
 
End If
End Sub
 -------------------------------------------------------------------------------------------------------------------------------
EXCEPTION IS BELOW
Cannot open database "mydatabase.mdf" requested by the login. The login failed.Login failed for user 'myusername'.
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.SqlClient.SqlException: Cannot open database "safetydata.mdf" requested by the login. The login failed.Login failed for user 'myusername'.Source Error:



Line 18: Dim strSQL As String = "SELECT Incident_Id,Emp_No From Report_Incident"
Line 19: Dim mycommand As New SqlCommand(strSQL, myconnection)
Line 20: myconnection.Open()
Line 21: Dim reader As SqlDataReader = mycommand.ExecuteReader()
Line 22: Source File: C:Incident Reporting System--Trial VersionWebChart.aspx    Line: 20 Stack Trace:



[SqlException (0x80131904): Cannot open database "safetydata.mdf" requested by the login. The login failed.
Login failed for user 'myusername'.]
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +171
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +199
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2305
System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +34
System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +606
System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +193
System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +501
System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28
System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +429
System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +70
System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +512
System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +85
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +89
System.Data.SqlClient.SqlConnection.Open() +160
ASP.webchart_aspx.Page_Load(Object sender, EventArgs e) in C:Incident Reporting System--Trial VersionWebChart.aspx:20
System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +13
System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +45
System.Web.UI.Control.OnLoad(EventArgs e) +80
System.Web.UI.Control.LoadRecursive() +49
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3745



Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.

View 1 Replies View Related

Write Only Database

Jul 23, 2005

Hi,Is there a way to make a database write only, like in SQL server youcan make a DB read only? There is sensitive data which we want dontwant to read.What other alternatives can anyone think?Thanks.

View 4 Replies View Related

How To Write To Mssql Database

Oct 23, 2006

I am very new to asp.net. I really mainly use PHP but want to migrate to asp.net, but it doesn't seem that easy.I am creating a page for peson to pay a subscription page where they will enter personal information an credit card information.I created the form for this with validation controls.I don't know however how to write the form data to the mssql database.I am using mssql 2005, but i would need a work out for the 2000 version as well.

View 1 Replies View Related

Write All Transactions In The Log To The Database

Jun 26, 2000

Hi
All,
I would like to know write all transcations in the log file to the
database with out doing full backup of the database..
Please let me know is there any dbcc statement or some other method to do this..

Thanks A lot,
VJ

View 1 Replies View Related

Can't Write To Database After Express SP2

Jan 9, 2008

I have a lot of databases under SQL Server Express 2005, reading an writing datas with VB6 (yes Visual Basic 6) and ADO (Provider SQLOLEDB). I do NOT install the SP2, but yesterday I install Silverlight and now I have SP2 on my SQL Server (9.0.3042) !?

Now I have a strange problem: I can read all datas, but I can't write any data to any tables.

Here a simple example:


I have a table "Professions", one column "RecNo" as int identity (1,1) with primary key and a column "Profession" as nvarchar(30).


Reading like this works:

Dim rcs As New Recordset

sTmp = "select * from Professions order by Profession"
rcs.Open sTmp, gcn, adOpenForwardOnly, adLockReadOnly
If rcs.RecordCount > 0 Then
Do
...
rcs.MoveNext
Loop Until rcs.EOF
End If
rcs.Close

Writing like this doesn't work anymore:

Dim lNewRecNo as long
Dim rcs As New Recordset

sTmp = "select * from Professions where RecNo = 42"
rcs.Open sTmp, gcn, adOpenKeyset, adLockOptimistic
If rcs.RecordCount < 1 Then rcs.AddNew
rcs!Profession = "XYZ"
rcs.Update
lNewRecNo = CNull2Val(rcs!RecNo)
rcs.Close


The rcs-object still give me the new record number after rcs.update, but the record is not in the database table. Adding a record in the Management Studio gives an error "row failed on retrieve on last operation". If I then click the button "Execute SQL" the new record is shown.
Another behaviour: Adding a row in the IDE like described above locks the Management Studio. From where I recieve a time out error then click the button "Execute SQL".
Any ideas whats wrong ?

View 1 Replies View Related

ADO Write Not Commiting To Database

Sep 6, 2007

This seems about the best place for my query however it does cross over a bit ... dont shoot me.

SQL 2000 sp4 on server 2003
dev env VB6
users XP Sp2
Mdac 2.8

I have a legacy app that required some simple ammendment, insert a single row of data into a simple table (Not rocket Science) Sample : insert into maintcon_mach (maintcon_id, mach_id, date) values (123, 456, getdate())

PK on maintcon_id, mach_id so that single relationship exists between maintcon_id and mach_id in the table.

The procedure above is followed by a verification procedure that checks to see if table maintcon_mach has a mach_id 456 against maintcon_id 123, this procedure returns true. So far so good, however if you step through the procedure till disconnecting from the database and then reconnect and do only the verification again, the data is missing.

The following steps to resolve have resulted in no improvement.

1. Verify MDAC in entire project and on users computers all 2.8
2 Remove transactions (just in case) and still the same.
3 Replace SQL insert with ADO (As below) and still the same.. tried this with and without transactions.
4 Tried referecing older MDACs
5 Installed SQLredist on sample machines and still the same.


rs.Open "maintcon_mach", conMach, adOpenForwardOnly, adLockOptimistic
With rs
.AddNew
!maintcon_id = lngmaint
!mach_id = lngMachID
!Date = Format(Now(), "yyyy-mm-dd")
.Update
End With

Is the problem in SQL or MDAC???
Perhaps I've missed something (Obviously have) but have run out of places to look... Any idea's

In advance ...thanx

View 3 Replies View Related

Loop Through Different Objects In A Database?

Dec 27, 2011

I have an ActiveX script which I need to convert to a T-SQL Stored Procedure. As a part of it, I need to loop through all the tables in a Database and check whether the particular Table/View exists? If doesn't exist, I need to create one with Primary Key, Foreign Keys, Indexes (Clustered & Non-Clustered), and Check Constraints.

Here is the ActiveX Script I have:

Code:
Dim sNextMonthTable
Dim oServerName
Dim oTables
Dim CheckDate
sNextMonthTable = "Jan_2012"

[Code] .....

View 12 Replies View Related

Write Excel Data To MS SQL Database

Dec 22, 2003

Dear expert, please show me some examples of how I can update an Microsoft SQL table data with an excel sheet. Meaning, when I upload an excel sheet using an ASPX page the data in the excel sheet will be transfer to a particular table in the database. Thank you in advance.

View 2 Replies View Related

Write Data Into Database From A Session?

Oct 8, 2005

I'm trying to write data from a session into database but it won't work.
Could someone help me what is wrong with this.

cobjDT = Session("cart")
For each cobjDR in cobjDT.rows
 Dim cmdInsert2 as New SQLCommand("order_details", loConn)
             cmdInsert2.CommandType = CommandType.StoredProcedure
            
cmdInsert2.Parameters.Add(New SqlParameter("@Orders", SqlDbType.int, 4,
"OrderID"))
            
cmdInsert2.Parameters("@Orders").Value = Request("OrderIDtext")
            
cmdInsert2.Parameters.Add(New SqlParameter("@PID", SqlDbType.int, 4,
"PID"))
            
cmdInsert2.Parameters("@PID").Value = Request(cobjDR("PID"))
            
cmdInsert2.Parameters.Add(New SqlParameter("@PakID", SqlDbType.int, 4,
"PakID"))
            
cmdInsert2.Parameters("@PakID").Value = Request(cobjDR("PAKID"))
            
cmdInsert2.Parameters.Add(New SqlParameter("@StyckPris",
SqlDbType.decimal, 9, "StyckPris"))
            
cmdInsert2.Parameters("@StyckPris").Value = Request(cobjDR("StyckPris"))
            
cmdInsert2.Parameters.Add(New SqlParameter("@Mangd", SqlDbType.int, 4,
"Mangd"))
            
cmdInsert2.Parameters("@Mangd").Value = Request(cobjDR("Quantity"))
             loConn.Open()
             cmdInsert2.ExecuteNonQuery()
             loConn.Close()
next            

When I execute this an error message comes

Server Error in '/examen' Application.

Input string was not in a correct format.



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.FormatException: Input string was not in a correct format.

Source Error:




Line 78: cmdInsert2.Parameters("@Mangd").Value = Request(cobjDR("Quantity"))Line 79: loConn.Open()Line 80: cmdInsert2.ExecuteNonQuery()Line 81: loConn.Close()Line 82: next







Source File: D:lofa.mine.nuexamenmembersTMP3thkwo1d0w.aspx    Line: 80


Stack Trace:




[FormatException: Input string was not in a correct format.] System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) +723 System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +194 ASP.TMP3thkwo1d0w_aspx.Page_Load(Object Sender, EventArgs E) in D:lofa.mine.nuexamenmembersTMP3thkwo1d0w.aspx:80 System.Web.UI.Control.OnLoad(EventArgs e) +67 System.Web.UI.Control.LoadRecursive() +35 System.Web.UI.Page.ProcessRequestMain() +731









Version Information: Microsoft .NET Framework Version:1.1.4322.573; ASP.NET Version:1.1.4322.573

I use same character types in storedProcedure what they are in the
database and when I call the SP on my page the char types are same as
they are in the SP

I don't see what the problem is. Please Help

View 1 Replies View Related

How To Write Script To Update The Database?

Apr 30, 2004

Hi folks,
Do you guys know how to write the Script to update the SQL database? please help me out? For example, the script will update SQL database at 1:00 am every day...some like that?

Thanks,
Vu

View 4 Replies View Related

Datareader Destination As Source For Other Datareader Source ?

Aug 30, 2006

HI!

as far as I know from docs and forum datareader is for .NET data in memory. So if a use a complex dataflow to build up some data and want to use this in other dataflow componens - could i use data datareader source in the fist dataflow and then use a datareader souce in the second dataflow do read the inmemoty data from fist transform to do fursther cals ?

how to pass in memory data from one dataflow to the next one (i do not want to rebuild the logic in each dataflow to build up data data ?

Is there a way to do this ? and is the datareader the proper component ? (because its the one and only inmemory i guess, utherwise i need to write to temp table and read from temp table in next step) (I have only found examples fro .NET VB or C# programms to read a datareader, but how to do this in SSIS directly in the next dataflow?

THANKS, HANNES

View 7 Replies View Related

How To Write Query In Vwd2005 To Connect To Database?

Jan 31, 2008

can anyone help me here/i want to write connect to my sqldatabase and then perform insert query ,select query, update query from my default.aspx.cs file.but i dont know how to do this.can anyone explain me this in detail.?i want to my data in my webform in the sqldatabase, select it , and update it.how can i do this writing a code in c#.? plz explain in detail.i m using vwd2005 express and sql express.thanks.     

View 1 Replies View Related

Should I Add Lock Statements To My Database Write Querys

Jun 5, 2008

Hello,Could some clarify for me? I’ve been told that I should add  lock statements  to my database write querys (update, delete, insert). I’m not sure If it’s necessary though. The only documentation I’ve found so far is how to implement a lock statement with threading but I’m not using the threading namespace. What best practice and how would I implement this without threading or should I be using threading?Thanks in advance!
 

View 4 Replies View Related

How To Write Portable Database Access Code?

Jul 20, 2005

I can access Microsoft Access database via ODBC on Windows 2000 usingC++. Now, I would like to use the SAME piece of code to access MS SQLon Windows. Can I achieve this without any modification to my existingcodes?Then, I would like to use the SAME piece of codes to access Oracleon Windows? Is it possible?Thanks in advance!

View 1 Replies View Related

Transact SQL :: How To Write Query From Two Different Database Engines

Jul 13, 2015

Is it possible to write query from different database engines?

One database A is in local server database engine and another database B is in  another server located in different system.

Now , I need to write a query in database A  based on table A and table B (another database engine).

View 2 Replies View Related

SQL Server 2008 :: Difference Between FOR LOOP And FOREACH LOOP?

May 28, 2010

difference between FOR LOOP and FOREACH LOOP with example(if possible) in SSIS.

View 4 Replies View Related

Added A Column To My SQL Database And Now Text Field Will Not Write To It. Why???

Aug 28, 2006

I have a web form that is an interface for a database.  The code was working fine until a field needed to be added.  So I added the new field, updated the data adapter and data set, and when i enter the data into the field, all of the old fields are getting updated, but the new ones aren't.  I am at wits end as to why.  Can someone please help!!! I am using VS Studio 2000 ASP.NET and C#.  The code is below.  The bold items are the new fields.using System;using System.Collections;using System.ComponentModel;using System.Data;using System.Drawing;using System.Web;using System.Web.SessionState;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.HtmlControls;namespace TrafficDept{/// <summary>/// Summary description for AddOwner./// </summary>public class AddOwner : System.Web.UI.Page{protected System.Web.UI.WebControls.Button Button1;protected System.Web.UI.WebControls.RequiredFieldValidator RequiredFieldValidator3;protected System.Data.SqlClient.SqlConnection sqlConnection1;protected System.Web.UI.WebControls.TextBox TextBox1;protected System.Web.UI.WebControls.TextBox TextBox2;protected System.Data.SqlClient.SqlDataAdapter daAddOwner;protected System.Web.UI.WebControls.TextBox tbOtherOwnerLName;protected System.Web.UI.WebControls.Panel namePanel;protected System.Web.UI.WebControls.TextBox tbOtherOwnerMI;protected System.Web.UI.WebControls.TextBox tbOtherOwnerFName;protected System.Web.UI.WebControls.TextBox tbSecondOwnerLName;protected System.Web.UI.WebControls.TextBox tbSecondOwnerMI;protected System.Web.UI.WebControls.TextBox tbSecondOwnerFName;protected System.Web.UI.WebControls.Button Button4;protected SiteCubed.EditWorksProfessional tbNote;protected System.Web.UI.WebControls.Panel notePanel;protected System.Web.UI.WebControls.DropDownList ddlStatus;protected PeterBlum.PetersDatePackage.DateTextBox tbRegDate;protected System.Web.UI.WebControls.RequiredFieldValidator RequiredFieldValidator2;protected System.Web.UI.WebControls.TextBox tbOwner;protected System.Web.UI.WebControls.RequiredFieldValidator RequiredFieldValidator1;protected System.Web.UI.WebControls.TextBox tbOwnerMI;protected System.Web.UI.WebControls.TextBox tbOwnerLName;protected System.Web.UI.WebControls.RequiredFieldValidator RequiredFieldValidator4;protected System.Web.UI.WebControls.TextBox tbBusinessName;protected System.Web.UI.WebControls.TextBox tbAddress;protected System.Web.UI.WebControls.TextBox tbRegNo;protected System.Web.UI.WebControls.TextBox tbPreviousOwner;protected System.Web.UI.WebControls.TextBox tbPOwnerAddress;protected System.Web.UI.WebControls.TextBox tbPORegNo;protected PeterBlum.PetersDatePackage.DateTextBox tbTransferDate;protected System.Web.UI.WebControls.DropDownList ddlMeans;protected System.Web.UI.WebControls.Button Button3;protected System.Web.UI.WebControls.Button Button2;protected System.Data.SqlClient.SqlCommand sqlSelectCommand1;protected System.Data.SqlClient.SqlCommand sqlInsertCommand1;protected System.Data.SqlClient.SqlCommand sqlUpdateCommand1;protected System.Data.SqlClient.SqlCommand sqlDeleteCommand1;protected TrafficDept.dsAddOwner dsAddOwner1;  private void Page_Load(object sender, System.EventArgs e){// Put user code to initialize the page here}#region Web Form Designer generated codeprivate void Button2_Click(object sender, System.EventArgs e){Response.Redirect("AddOthers.aspx");}private void Button1_Click(object sender, System.EventArgs e){daAddOwner.Fill(dsAddOwner1);dsAddOwner.OwnershipRow drOwner = dsAddOwner1.Ownership.NewOwnershipRow();drOwner.Plates_ID = int.Parse(Session["PlatesID"].ToString());drOwner.Status = ddlStatus.SelectedItem.Text;drOwner.Current_Legal_Owner = tbOwner.Text;drOwner.Owner_CurrentLegalOwnerMI = tbOwnerMI.Text.ToString();drOwner.Owner_CurrentLegalOwnerLName = tbOwnerLName.Text;drOwner.Owner_CurrentBusinessName = tbBusinessName.Text;if (!tbRegDate.xIsEmpty)drOwner.Date_of_Registration = DateTime.Parse(tbRegDate.Text.ToString());drOwner.Address_of_Current_Owner = tbAddress.Text;drOwner.Reg_No_for_Current_Owner = tbRegNo.Text;drOwner.Previous_Owner = tbPreviousOwner.Text;drOwner.Previous_Owner_Address = tbPOwnerAddress.Text;drOwner.Reg_No_for_Previous_Owner = tbPORegNo.Text;if (!tbTransferDate.xIsEmpty)drOwner.Date_of_Transfer = DateTime.Parse(tbTransferDate.Text.ToString());drOwner.By_Means_of = ddlMeans.SelectedItem.Text;drOwner.Owner_SecondaryLegalOwnerFName = tbSecondOwnerFName.Text;drOwner.Owner_SecondaryLegalOwnerMI = tbSecondOwnerMI.Text;drOwner.Owner_SecondaryLegalOwnerLName = tbSecondOwnerLName.Text;drOwner.Owner_OtherLegalOwnerFName = tbOtherOwnerFName.Text;drOwner.Owner_OtherLegalOwnerMI = tbOtherOwnerMI.Text;drOwner.Owner_OtherLegalOwnerLName = tbOtherOwnerLName.Text;drOwner.Owner_Notes = tbNote.Text;dsAddOwner1.Ownership.Rows.Add(drOwner);daAddOwner.Update(dsAddOwner1);Response.Redirect("AddOthersTP.aspx");}private void Button3_Click(object sender, System.EventArgs e){//daAddOwner.Fill(dsAddOwner1);//dsAddOwner.OwnershipRow drAddOwner = dsAddOwner1.Ownership.NewOwnershipRow();namePanel.Visible = true; }private void Button4_Click(object sender, System.EventArgs e){notePanel.Visible = true;}}}

View 1 Replies View Related

How To Write Database Scripts Using Sql Server Managment Studio

Apr 25, 2008

Hello All,
My boss told me that we can write migration scripts to database using Sql server management studio. can someone guide me or point to a website which explains in detail how to do this please. i want to learn this. or even a tutorial also would be great. i am interested in writing or executing scripts by using sql server management studio( in sql server 2005). i think its called sql enterprise manager in sql server 2000.
Thanks a lot.

View 5 Replies View Related

Application Locks Up When Trying To Write To Db After A Replication Where I Create The Database.

Jan 3, 2008

Hello all,

When I create a new database and replicate to it using BeginMonitoredBackgroundSync :







Code Block

public void BeginMonitoredBackgroundSync(string User)
{
CreateReplicationInstance(User);
repl.BeginSynchronize(
OnSimplifiedSynchronizeComplete,
SqlCeReplication_OnStartTableUpload,
SqlCeReplication_OnStartTableDownload,
SqlCeReplication_OnSynchronization,
repl);
}




private void CreateReplicationInstance(string User)
{
repl = new SqlCeReplication();
string host = repl.HostName;
repl.HostName = User;
string dbFilePath = "";


dbFilePath = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase) +
"\" + repl.HostName + ".sdf";
string myConnectionString = string.Format("Data Source = {0};PWD = {1}", dbFilePath, sqlSettings.Items["SqlPassword"]);

repl.InternetUrl = dynamicsReplicationSettings.ReplicationSettingsItems["InternetUrl"];
repl.PublisherSecurityMode = SecurityType.DBAuthentication;
repl.Publisher = dynamicsReplicationSettings.ReplicationSettingsItems["Publisher"];
repl.PublisherDatabase = dynamicsReplicationSettings.ReplicationSettingsItems["PublisherDatabase"];
repl.PublisherLogin = dynamicsReplicationSettings.ReplicationSettingsItems["PublisherLogin"];
repl.PublisherPassword = dynamicsReplicationSettings.ReplicationSettingsItems["PublisherPassword"];
repl.Publication = dynamicsReplicationSettings.ReplicationSettingsItems["Publication"];

repl.Subscriber = "RemoteSubscription" + repl.HostName;
repl.SubscriberConnectionString = myConnectionString;

repl.ConnectionRetryTimeout = 120;
repl.LoginTimeout = 120;
repl.CompressionLevel = 6;

if (File.Exists(dbFilePath))
{
FileInfo info = new FileInfo(dbFilePath);
if (info.Length <= 20480)
{
File.Delete(dbFilePath);
repl.AddSubscription(System.Data.SqlServerCe.AddOption.CreateDatabase);
}
}
else
{
repl.AddSubscription(System.Data.SqlServerCe.AddOption.CreateDatabase);
}

primeConnection();

}
After the replication finishes, I dispose the replication object like so:





Code Block

void OnAsyncSynchronizeComplete(IAsyncResult asyncResult)
{

try
{
repl.EndSynchronize(asyncResult);
if (repl != null)
{
repl.Dispose();
repl = null;

}
if (ReplicationComplete != null) ReplicationComplete(this, true);
}
catch(Exception ex)
{
if (ReplicationComplete != null) ReplicationComplete(ex, false);
}

}

Then later, if I try to update, insert or delete to the database, the application will hang. I can read from it, but I cannot write. If I close the application down and open it back up without replicating, I will not get any lockups. It also will not hang up after any replications prior to the create replication. I think I am doing something wrong in the initial replication that is holding on to some connection to the DB causing it to lock up. Has anyone seen anything like this before?

View 1 Replies View Related

Way To Determine Which Logins Have Write Permissions To Database On Sever

Nov 15, 2007

Hi there,

Does anyone know a way (a query perhaps?) to determine which logins have write permissions to a specific database on SQL Server 2005? Ive thought about joining sys.syslogins & sys.sysusers but looking at the columns, not sure which one would render me that info.

Any help is appreciated. Many thanks!

View 1 Replies View Related

Sql Server Express Loses Ability To Write To Database

Jun 17, 2006

After a period of use (typically 1-2 hours), an application using SQL Server Express stops writing to the database, whilst still continuing to read from it. As far as I can tell, no error message is generated by any operation. The application is written in VB Express and is executed on several machines, with SQL Server Express on a central machine. Re-starting the application on the affected machine restores normal operation.

The problem on one machine does not cause a similar problem elsewhere at the same time (hence it appears to be an issue with the relationship between the application and SQL Server)

This does not appear to relate to the response to Q asked by sgcook on 9 may as we are not using user instances, but only a bare minimum connection string.

View 15 Replies View Related

Want To Access SQL Database To Run Loop To Check Robots.txt Remotely

Jan 22, 2008

One of my tables in the db contains the websites and the location of their link directory. i.e. domain.com/linkdirectory
I want to run a check against every website's robots.txt file to ensure that it doesn't look like this:
user-agent: *
Disallow: /linkdirectory/
so I need a program that does the following:
 For each website in database (start of loop)
  Does robots.txt exist on their site ?(run a remote check)
     if it does
        is their link directory blocked in robots.txt?
          if so, then log their user id and website name into an array for later use
          end if
       endif
end loop
How would I do this in ASP.net 2.0? 

View 3 Replies View Related

Loop Though Table Using RowID, Not Cursor (was Loop)

Feb 22, 2006

I have a table with RowID(identity). I need to loop though the table using RowID(not using a cursor). Please help me.
Thanks

View 6 Replies View Related

HELP To Write Stored Procedure Whose Values Are Calculated Automatically In Database

May 12, 2007

 Hi frdz,    I m creating my web-application in asp.net with C# 2005 and using sql server 2005.    I have created the stored procedure for the insert,update.    I want to know how to write the mathematical calculations in the stored procedure..    Pls tell me from the below  stored procedure  were i m making the mistake  ??    As the discount and the total amount are not calculated by itself....and stored in the database   How to convert the @discpercent numeric(5,2) to@discpercent ="NoDiscount" should be displayed when no discount is being given to the customers....     ALTER PROCEDURE CalculationStoredProcedure

@accountid int output,
@accountname varchar(20),
@opbal numeric(10, 2),
@opbalcode char(2),
@total numeric(10, 2),
@clbal numeric(10, 2),
@clbalcode char(2),
@discpercent numeric(5,2),
@discamt numeric(10, 2)


as


begin
set nocount on



if @opbal IS NULL OR @opbal = 0

begin
select @opbal=0
select @opbalcode= ' '
select @clbal= 0
select @total= 0
select @clbalcode= ' '
@discpercent ="NoDiscount"
@discamt=0
end




select @accountid = isnull(max(accountid),0) + 1 from accountmaster



select @total=@opbal - @clbal from accountmaster
select @discamt=@total* @discpercent/100 from accountmaster

begin
insert into accountmaster
(
accountname,opbal,opbalcode,clbal,clbalcode

)
values
(
@accountname,@opbal,@opbalcode,@clbal,@clbalcode
)

end

set nocount off
end

      Thanxs in adv... 

View 7 Replies View Related

Spin Loop Attaching Database In 2000 =&&> 2005 Upgrade

Apr 21, 2008

I just upgraded a large SQL 2000 database server to SQL 2005, and I have 2 databases that won't attach to the 2005 Server. They are both very small, about 90MB in size, and when I attach them to 2005, the process alternates between running and spinloop status. It can sit forever, and will never complete. I restored backups before the upgrade to a SQL 2000 Server, and reran DBCC CheckDB on both, and got no consistency errors, Updated Stats and indexes, then detached and moved the data files to 2005, and same thing.

View 3 Replies View Related

SQL Server 2012 :: Determine Read / Write Frequency On Database Table

Oct 6, 2015

How do I determine the read/write frequency on a database table? I am trying to do this on a 2012 and 2008 R2 servers.

View 2 Replies View Related

Concurrency Issue On A Single Database User For An Online Read/write Application

Aug 3, 2006

Hi to all DBAs,

I would like to ask if there will be a write and concurrency issue if i would create an online application with just one user connecting to the database, just like most open source php/mysql that can be downloaded

i.e
Setup 1. I will grant a single user that will connect to a database and will be set to a config.inc.php file and then create a table users (userid, username, password) and this table will be used for the application authentication and access control.

Setup 2. or is it much better to grant users that will connect to the database and have the table users for access control of the application?

to further illustrate my query:

Setup 1 would be:
###################Table: Users
user a@ipadd --> db1 --> userid mary --> myOnlineApp
user a@ipadd --> db1 --> userid john --> myOnlineApp
user a@ipadd --> db1 --> userid paul --> myOnlineApp


and Setup 2 would be:
#####################Table: Users
user mary@ipadd --> db1 --> userid mary --> myOnlineApp
user john@ipadd --> db1 --> userid john --> myOnlineApp
user paul@ipadd --> db1 --> userid paul --> myOnlineApp

will Setup 1 be enough if I will use SQL Server as database?

or Setup 2 is better user/database architecture?

This application will be online enrollment for a school with 16,000 students

very much appreciated for the feedbacks and suggestions

^_^x

View 3 Replies View Related

SQL 2012 :: Blank Database Created On Two Separate Disks - Write To Multiple Files

Mar 17, 2014

I am testing out a blank database created over two physical files on two separate disks with one table called data which has one column called values nvarchar(max).

I filled the table up with a whole load of data and ran a select * against it. If I run Permon at the same time I can see that the read load has been spread over multiple disks as each of these disks is getting read from in parallel. If I create the same database on a single file and run the same select * again it takes much longer, proving that the read load has been distributed across multiple disks.

Now moving onto writes, this is where the confusion lies. I understand that SQL server fills files evenly until they need growing, after which it will then fill files individually until they are full in a round robin fashion unless you have trace 1117 turned on. What I don't understand is why the writes aren't distributed out whilst it is filling these file groups.

I ran an continual insert into my table with go 1000000 to monitor how the files are being filled up. I monitored where SQL server was physically placing the files as they were being inserted by running the following query:

;WITH CTE AS
(SELECT
sys.fn_PhysLocFormatter (%%physloc%%) col1,
RIGHT(LEFT(sys.fn_PhysLocFormatter (%%physloc%%),2),1) AS [Physical RID],
DATAID

[Code] ....

I could see that it would a thousand or so records into file 1, then a thousand or so into file 2, then a thousand or so into file 1 etc etc. In another words it would hit one disk, then another disk, then back to disk one to fill the file evenly. Is there any way to make SQL Server distribute the writes out in parallel so that both disks are writing in tandem?

By the looks of it, multiple disks only scale reads, as with writes only one disk is ever written to at once which is annoying. Any way to harness the write power of multiple disks?

View 6 Replies View Related

Backup Master Key, Cannot Write Into File 'c: Empmaster'. Verify That You Have Write Permissions, That The File Path Is Valid.

Jul 12, 2006

Hi,



I tried to backup the master key by the following syntax :

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'mypassword'

BACKUP MASTER KEY TO FILE = 'c: empmaster' ENCRYPTION BY PASSWORD = 'mypassword'

but it failed and i got the following message:

Cannot write into file 'c: empmaster'. Verify that you have write permissions, that the file path is valid, and that the file does not already exist.

NB: I am using the "sa" user to execute this command.

I know that we have a security permission issue , but where and how ?



Regards,

Tarek Ghazali

SQL Server MVP

View 12 Replies View Related

Foreach Loop Doesn't Loop

Mar 3, 2006

I have a foreach loop that is supposed to loop through a recordset, however it doesn't loop. It just repeats the same row, row after row.

I would like to look into the recordset variable but I can't because it is a COM object and the ADODB namespace is not available in the script task.

Any solution to this? anyone experienced anything similar

View 1 Replies View Related







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