|
I have been tasked with moving all of the SQL Server databases from an old server to a new one.
I was hoping to simply do a sp_detach, copy the mdf & ldf files to the new server, then reattach them. However, we are using mix-mode authentication and the handful of sql user logins we use fail to login on the new server during testing. So, I attempted to create the users under the security/logins section prior to attaching the database but, that did not work either. Is there an easy/fast way to migrate the database to the new server with out using DTS?
Application Error Log File which probably isn't helpful:
SQL Errors:
SqlError: 1
Error #: 4060
Procedure:
Line # 0
Server: TheSqlServer
Source: .Net SqlClient Data Provider
Message: Cannot open database requested in login 'TheDatabase'. Login fails.
SqlError: 2
Error #: 18456
Procedure:
Line # 0
Server: TheSqlServer
Source: .Net SqlClient Data Provider
Message: Login failed for user 'SqlServerLogin'.
Thanks,
Michael
I firmly believe that any man's finest hour, the greatest fulfillment of all that he holds dear, is that moment when he has worked his heart out in a good cause and lies exhausted on the field of battle - victorious.
Vince Lombardi (1913-1970)
|
|
|
|
|
in the database list, right click -> all tasks -> generate sql script...
With this, you can select that Enterprise Manager creates scripts for the users using the database, try it.
daniero
|
|
|
|
|
I have a application that has is develop using c#
B4 this all my data processing is done thourgh the tsql statment, now i want to switch the thing to stored procedure..
1.) but my management wnt to know is they any way we can hide the code in stored from the database administrator like the 1 in the trigger where we can encrypt the trigger..
2.) one more thing can we populate the stored procedure from c#..
so every update i will create 1 EXE for them to run and i will update the stored procedure without using any sql util...
thanks...
Jalil
|
|
|
|
|
Abdul Jalil Mohamed wrote: is they any way we can hide the code in stored from the database administrator like the 1 in the trigger where we can encrypt the trigger..
Yes, you can encrypt stored procedures
Abdul Jalil Mohamed wrote: i will create 1 EXE for them to run and i will update the stored procedure without using any sql util
Yes, you can create an exe that runs SQL against the database. You can do this by using the SqlCommand object in a .NET application and then ExecuteNonQuery()
I have an example on my blog where the SQL Script is stored as a resource inside an EXE file then run against the database. You might find it useful: http://blogs.wdevs.com/colinangusmackay/archive/2004/10/15/857.aspx[^]
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
1.) use WITH ENCRYPTION statement before
AS
--Stored procedure statements
Do make sure to have a backup of the stored procedure somewhere since, you're not going to see that text once you applay the statement and close the editor.
2.) you can... create stored procedures from C#... theoretically yes, you can execute a SQL command with a CREATE PROCEDURE statement, and then use the SP.
daniero
|
|
|
|
|
daniero wrote: theoretically yes
It is not a theory, it is a fact. You can do this so long as you log on to the database as a user with ddl_admin, db_onwer or sysadmin privileges.
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
Thanks for clearing that up. I need to write it down   .
daniero
|
|
|
|
|
I have a vb.net application that:
Loops through a list of tables (could be up to 1000 tables)
Extracts all records in the table writes them to a datareader
Opens a connection to a different odbc data source and creates a dataset with a table with the same name as the tables in the loop
Uses newrow to create a new row
Updates data common to all tables ( a companyid)
Uses row.add to add the row to the table
At this stage all columns in the row appear to be populated correctly.
However when I do dataadapter.update() All rows are added to the source table but only the primary key is populated.
I've used commandbuilder.getinsertcommand to see what insert string is being used and the insert is only trying to insert the primary key (3 columns)
Datasource is a Progress database (Version 10b)
Using ODBCDataadapter to read and write the data.
|
|
|
|
|
DataAdapter and CommandBuilder are not the best objects to use for this. You need to learn how to write your own insert commands as .NET will not write efficient commands for you - most of the time I will create slow commands that guarantee the right result but only because they act by brute force.
Create your own command object giving it an INSERT command that you define. You can then call ExecuteNonQuery() to insert the row. This will also be faster.
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious
|
|
|
|
|
Colin, Thank you for the feedback
I was hoping to use the automatically generated insert command for the following reasons:
It's possible that it will processing over 1000 different tables
Some of these tables contain approx 140 columns
I don't have control over the database so I won't necessarily know when changes are made
I can't add stored procedures to the database to do the inserts
I suppose I could use code to build the command in another loop - but I was hoping to avoid this
|
|
|
|
|
Hi, i have have build an access database application. What i want to know is how to audit the user activities (open form, close form, print report...) in my database?
|
|
|
|
|
Roath Kanel wrote: What i want to know is how to audit the user activities (open form, close form, print report...) in my database?
Access does not have such capabilities.
You can however create a table and insert the actives into it which you would like to record.
ZeePain! wrote: This seems like one of those programs that started small, grew incrementally, building internal pressure, and finally barfed all over its source code sneakers. Or something.
thedailywtf.com[^]
|
|
|
|
|
Thank you very much for your comment. I also prepare to record user activities in a table as the second solution. Now already develop half of this task and this is my first time to design an application with auditing user activities.
|
|
|
|
|
Using SQLXML, Bulk Insert XML with IDENTITY Column ...
I have a program that will insert xml data into a table using the SQLXML Bulk Load COM Object. The bulk load is successful when I supply the RecordId in the xml. When I add an IDENTITY column to the table then the bulk load fails with the following error:
[Cannot insert the value NULL into column 'RecordId', table 'Alphanumericdata.dbo.MacgowanTestCust'; column does not allow nulls. INSERT fails.]
From the following article, XML Bulk Load ignores elements and attributes that are not mapped (either because they are not described in the schema, or because they are annotated in the XSD schema with sql:mapped="false"). All unmapped data goes into the overflow column, if such a column is specified by using sql:overflow-field.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqlxml3/htm/bulkload_9w9w.asp
I have attempted using sql:mapped="false" in the schema file and not using it .. both fail with the “nulls not allowed” error.
I have also set the KeepIdentity(true) to my pISQLXMLBulkLoad object.
Below is the table, xml, xsd and code ...
Any comments or answers are appreciated.
Thanks,
Chris
///////////////////////////////////////////////////
// The code
char progID[] = "SQLXMLBulkLoad.SQLXMLBulkload.3.0";
CLSID clsid;
wchar_t wide[80];
mbstowcs(wide, progID, 80);
CLSIDFromProgID(wide, &clsid);
ISQLXMLBulkLoad* pISQLXMLBulkLoad = NULL;
if(SUCCEEDED(CoCreateInstance(clsid, NULL, CLSCTX_ALL, IID_ISQLXMLBulkLoad, (void**)&pISQLXMLBulkLoad)))
{
hResult = pISQLXMLBulkLoad->put_ConnectionString(bstrConnect);
hResult = pISQLXMLBulkLoad->put_ErrorLogFile(bstrXmlErrorLogFile);
hResult = pISQLXMLBulkLoad->put_KeepIdentity((bool)TRUE);
hResult = pISQLXMLBulkLoad->Execute(bstrXmlSchemaFile, vXmlDataFile);
}
///////////////////////////////////////////////////
// xml data (successful) RecordId is included
<ROOT>
<Customers>
<RecordId>1</RecordId>
<CustomerID>1111</CustomerID>
<CompanyName>Sean Chai</CompanyName>
<City>NY</City>
</Customers>
<Customers>
<RecordId>2</RecordId>
<CustomerID>1112</CustomerID>
<CompanyName>Tom Johnston</CompanyName>
<City>LA</City>
</Customers>
<Customers>
<RecordId>3</RecordId>
<CustomerID>1113</CustomerID>
<CompanyName>Institute of Art</CompanyName>
</Customers>
</ROOT>
///////////////////////////////////////////////////
// xsd schema file (successful) RecordId is included
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="Customers" sql:relation="MacgowanTestCust" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="RecordId" type="xsd:integer" sql:field="RecordId" />
<xsd:element name="CustomerID" type="xsd:integer" sql:field="CustomerID" />
<xsd:element name="CompanyName" type="xsd:string" sql:field="CompanyName" />
<xsd:element name="City" type="xsd:string" sql:field="City" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
///////////////////////////////////////////////////
// xml data (fails) attempting to use identity column
<ROOT>
<Customers>
<CustomerID>1111</CustomerID>
<CompanyName>Sean Chai</CompanyName>
<City>NY</City>
</Customers>
<Customers>
<CustomerID>1112</CustomerID>
<CompanyName>Tom Johnston</CompanyName>
<City>LA</City>
</Customers>
<Customers>
<CustomerID>1113</CustomerID>
<CompanyName>Institute of Art</CompanyName>
</Customers>
</ROOT>
///////////////////////////////////////////////////
// xsd schema file (fails) attempting to use identity column
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="Customers" sql:relation="MacgowanTestCust" >
<xsd:complexType>
<xsd:sequence>
<xsd:element name="CustomerID" type="xsd:integer" sql:field="CustomerID" />
<xsd:element name="CompanyName" type="xsd:string" sql:field="CompanyName" />
<xsd:element name="City" type="xsd:string" sql:field="City" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
///////////////////////////////////////////////////
// table
CREATE TABLE [MacgowanTestCust] (
[RecordId] [int] IDENTITY (1, 1) NOT NULL ,
[CustomerID] [int] NOT NULL ,
[DataSourceId] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_MacgowanTestCust_DataSourceId] DEFAULT ('OH'),
[CompanyName] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[City] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
PRIMARY KEY CLUSTERED
(
[RecordId]
) ON [PRIMARY]
) ON [PRIMARY]
GO
///////////////////////////////////////////////////
// error message
<?xml version="1.0"?><Error><Record><HResult>0x80004005</HResult><SQLState>01000</SQLState><NativeError></NativeError><ErrorState>1</ErrorState><Severity>0</Severity><Source>Microsoft OLE DB Provider for SQL Server</Source><Description><![CDATA[The statement has been terminated.]]></Description></Record><Record><HResult>0x80004005</HResult><SQLState>23000</SQLState><NativeError></NativeError><ErrorState>2</ErrorState><Severity>16</Severity><Source>Microsoft OLE DB Provider for SQL Server</Source><Description><![CDATA[Cannot insert the value NULL into column 'RecordId', table 'Alphanumericdata.dbo.MacgowanTestCust'; column does not allow nulls. INSERT fails.]]></Description></Record></Error>
|
|
|
|
|
hi everyone,
In my company, 4 users are accessing the sql server with a username and a password, they are developers, they have rights to create tables, fields, insert rows, deleting rows,...
how can I trace which computer make an insert or delete.
for example if a user make an insert into a table from SQL Server enterprise manager by writing an insert query, how can i know which computer make this action?
is that possible?
thanks.
|
|
|
|
|
|
Thanks, but this give me the computer name when a row is added, when a row is updated or deleted nothing is logged.
I can't know who update a specific record.
any other idea.
thanks again.
|
|
|
|
|
I have solved this problem in a different way. When the user logs in to the application, I insert the kerberos user id and the hostname in a Activity table. But there is a limitation. One user cannot start two instances of the application from the same host.
So when there is a update or delete or insert, I pick up the value from this table.
Everytime the user logs in, I clear the Activity for that user in a host machine.
CREATE PROCEDURE ValidateUser (
@userid varchar(10) )
AS
DECLARE @cnt int
BEGIN
DELETE FROM Activity
WHERE Host = host_name()
INSERT INTO Activity VALUES(host_name(),@userid)
SELECT @cnt = count(*)
FROM Users
WHERE ID = @userid
IF @cnt = 1
SELECT 1
ELSE
SELECT 0
END
In the trigger I do something like
IF @@rowcount = 0
RETURN
SELECT @user = CurrentUser
FROM Activity
WHERE Host = host_name()
IF UPDATE(Column1)
INSERT INTO AuditTrail
SELECT @user,getdate(),'Table1','Column1' --blah blah
FROM deleted,inserted
WHERE -------------------
Does this help.
|
|
|
|
|
GetChanges() returns nothing here,
right after a row is deleted.
and there is actually 1 row removed from the dataset,
how is this possible?
dsCustomers.GetChanges() gives the same results.
dsCustomers.Tables(0).Rows.Remove(dsCustomers.Tables(0).Rows(cm.Position))<br />
<br />
Dim DataSetDeleted As DataSet = dsCustomers.GetChanges(DataRowState.Deleted)<br />
If Not DataSetDeleted Is Nothing Then<br />
daCustomers.Update(DataSetDeleted)<br />
dsCustomers.AcceptChanges()<br />
End If
|
|
|
|
|
From what i understand you need to call the delete method of the datarow.
Calling the remove method removes the row entirely (it is gone and the rowcount is reduced). When you call the getchanges method with the deleted parameter it looks for rows marked as deleted. as you have used remove there are no rows that fit this description. If you call the delete method it simply marks the row as deleted. GetChanges with then report that row as deleted and you can call an update.
Jonathan
|
|
|
|
|
Agreed.
Please call DataRow.Delete() instead.
|
|
|
|
|
hi,
how can i hold text of veeery big size? varchar max is 8000 bytes, and text type by default is text(16) in my mssql server.. maybe there are some algorithms or something else..
|
|
|
|
|
|
Actually... text(16) doesn't mean it only holds 16 characters... look it up, this type is REALLY big.
daniero
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
