I am by no means a "Database Expert" and have recently been asked to
assist with a SQL 2000 database move to a new server. I was wondering
if there was an easy way to reconfigure the ODBC pointers on the
workstations?
Thanks for the help in advance.
How do I get an accurate count of workstations connected to a database?
View 7 Replies View RelatedHello All,
Does anybody know how to achive that?
<add name="ConnectionString" connectionString="Dsn=dsn_name;uid=user;pwd=password" providerName="System.Data.Odbc" />
I have got this created by the wizard for my sqlDataSource. I am able to get data from tables in the default database specified with the DSN.
But I want to go to other database than the default.
Thanks a lot in advamce for your help!
Kazu
Can multiple instances of SQL 2005 Express attach to the same database files on a network share? I have seen this done before with MSDE where the database files are stored on the server, but instead of having a SQL server running on the network and then connecting to it, only the database files exist on the network share and the users connect through MSDE running on the local machine. Is this possible with SQL2005Express? I do not have the ability to share an SQL instance from one workstation to another nor do I have the ability to install an instance on the corporate server. Is it as simple as creating the database and storing the files on the share then attaching the database to the SQL Instance on each workstation?
View 3 Replies View RelatedHello,
I have a table with the column description. Lets imagine that I have the records:
"a"
"b"
"c"
If this was a List and I wanted to move the "c" to the first position, I would simply remove the "c" and insert it in the 0 index position.
How can I do this with sql ce?
I could create another column named position and every time I would want to change the position, I would increment every other positions below. Example, "c" goes to first position:
position - description
1 - "a"
2 - "b"
3 - "c"
->
1+1 - "a"
2+1 - "b"
1- "c"
->
1- "c"
2 - "a"
3- "b"
But if I have lets say 100 thousand rows and I want to move the last one to the first position I have to do the operation 100K times.
Is there a more effective way to do what I want?
Thank you,
ARtur
Our workstations (XP SP2) and development servers (2003 R2) are all
32-bit. Our Staging and Production SQL
Servers are 64-bit (2003 R2 x64). We
have some VB SQLCLR assemblies we've developed.
My question is: Do we have to do anything special when deploying a CLR
Assembly to a 64-bit box when it has been compiled on a 32-bit box? Are there any advantages to setting the
Target CPU from "AnyCPU" to "x64"? Documentation and testing seems to indicate that the
assembly will run in 64-bit mode if compiled to "AnyCPU".
Will it run "better" in 64-bit mode
with the "x64" Target CPU type?
Thanks!
Hello, I am working on a dll that is being loaded by browsers. I have to check if the hostname of a requested page is in a database containing sites that have to be blocked. I am using the following code to connect to the db:
Code Block
BOOL check(WCHAR *wszHost)
{
BOOL ret = FALSE;
_ConnectionPtr pConn = NULL;
_CommandPtr pCmd = NULL;
_RecordsetPtr pRs = NULL;
try
{
pConn.CreateInstance(__uuidof(Connection));
pCmd.CreateInstance(__uuidof(Command));
pRs.CreateInstance (__uuidof (Recordset));
pConn->Open(_bstr_t(L"Provider=MSDASQL.1;Data Source="C:\Deny.mdb"), _bstr_t(L""), _bstr_t(L""), adOpenUnspecified);
pCmd->ActiveConnection = pConn;
pCmd->CommandType = adCmdText;
wcscpy_s(query, 2*_MAX_PATH, L"SELECT site FROM Allow WHERE site='");
wcscat_s(query, 2*_MAX_PATH, wszHost);
wcscat_s(query, 2*_MAX_PATH, L"'");
pCmd->CommandText = (_bstr_t)query;
pRs->CursorLocation = adUseClient;
pRs->Open((IDispatch*)pCmd, vtMissing, adOpenStatic, adLockReadOnly, adCmdText);
if(pRs->RecordCount > 0)
ret = TRUE;
}
catch(_com_error error)
{
dbgprint(" Description = %s", (char*) error.Description());
dbgprint(" Source = %s", (char*) error.Source());
dbgprint(" Code = %08lx", error.Error());
dbgprint(" Code meaning = %s", error.ErrorMessage());
return FALSE;
}
catch(...)
{
dbgprint(" Unhandled error!");
return FALSE;
}
}
Hel
The strange thing is that this code is running fine with Internet Explorer but when the dll is loaded by Firefox pConn->Open() fails with Invalid pointer error. Any suggestions will be greatly appreciated. Thank you.Martin
I apologize if this is not the correct forum for this posting. Looking at the descriptions, it appeared to be the best choice.
I am running Windows XP Pro SP2. I have installed the SQL Native Client for
XP. However, when I try to add a new data source through ODBC Connection
Manager, SQL Native Client is not listed as an option. I have followed this procedure on three other systems with no problems. What would be causing the
SQL Native Client to not show up in the list of available ODBC data sources?
I searched the archives but couldn't find anything on this yet...
I am using the wizard to grab selected data from 6.5 Server1 to insert into a table on 6.5 Server2, no transformations necessary.
I've tried it two ways (actually more, but two ways will demonstrate the problem). The query I run to grab the data is search through approx 6.5 million records in poorly indexed tables, on a slow machine. Takes approximately 45 minutes to run the query alone, and right around the same to run the DTS package.
The difference between the two queries in the two different packages (all else equal), is that the first uses a variable to calculate a date to filter the where clause. The second hard codes the date in the where clause. The second works, but the first runs about 40 minutes before returning a Transfer Failed error that reads "Invalid Pointer". No error number, nothing in the Books online about the error.
The queries are as follows:
***************************************
QUERY 1 (calculates the date for 11:00 PM night before last)
Declare @DateLastReceived datetime
Declare @CharLastReceived varchar(25)
Select @DateLastReceived = DateAdd(dd,-1,getDate())
Select @CharLastReceived = Convert(varchar(25), @DateLastReceived, 101)
select @DateLastReceived = Convert(dateTime, @CharLastReceived)
select @DateLastReceived = DateAdd(hh,-1,@DateLastReceived)
select s.SerialNumber as iwSerialNumber, MAX(p.ReceiptTime) AS iwLastReceived,
p.PurchaseOrderNumber as iwPO, s.Revision as iwBomRev, s.PartNumber as iwPartNumber
From SLOCAZ s INNER JOIN PORDRZ p ON s.SerialNumber = p.SerialNumber
Where p.ReceiptTime > @DateLastReceived
And ( (p.PurchaseOrderNumber Like 'BD%')
OR (p.PurchaseOrderNumber Like 'TP%')
OR (p.PurchaseOrderNumber Like 'DM%') )
GROUP BY s.SerialNumber, p.PurchaseOrderNumber, s.Revision, s.PartNumber
***************************************
QUERY 2 (Hard codes the date)
select s.SerialNumber as iwSerialNumber, MAX(p.ReceiptTime) AS iwLastReceived,
p.PurchaseOrderNumber as iwPO, s.Revision as iwBomRev, s.PartNumber as iwPartNumber
From SLOCAZ s INNER JOIN PORDRZ p ON s.SerialNumber = p.SerialNumber
Where p.ReceiptTime > 'Aug 29 1999 11:00PM'
And ( (p.PurchaseOrderNumber Like 'BD%')
OR (p.PurchaseOrderNumber Like 'TP%')
OR (p.PurchaseOrderNumber Like 'DM%') )
GROUP BY s.SerialNumber, p.PurchaseOrderNumber, s.Revision, s.PartNumber
*****************************
I should also note that I thought maybe the hard coded date being used as a string was the difference, so I tried the following (which just converts the date back into a char variable and uses the char variable in the where clause)
Declare @DateLastReceived datetime
Declare @CharLastReceived varchar(25)
Select @DateLastReceived = DateAdd(dd,-1,getDate())
Select @CharLastReceived = Convert(varchar(25), @DateLastReceived, 101)
select @DateLastReceived = Convert(dateTime, @CharLastReceived)
select @DateLastReceived = DateAdd(hh,-1,@DateLastReceived)
select @CharLastReceived = Convert(varchar(25), @DateLastReceived, 100)
select s.SerialNumber as iwSerialNumber, MAX(p.ReceiptTime) AS iwLastReceived,
p.PurchaseOrderNumber as iwPO, s.Revision as iwBomRev, s.PartNumber as iwPartNumber
From SLOCAZ s INNER JOIN PORDRZ p ON s.SerialNumber = p.SerialNumber
Where p.ReceiptTime > @CharLastReceived
And ( (p.PurchaseOrderNumber Like 'BD%')
OR (p.PurchaseOrderNumber Like 'TP%')
OR (p.PurchaseOrderNumber Like 'DM%') )
GROUP BY s.SerialNumber, p.PurchaseOrderNumber, s.Revision, s.PartNumber
************************************
This still didn't work...
Any Ideas on what is happening and/or how to fix it???
Amy
i want to conncet to a ms sql server,but the debug info: "invalid pointer!"
is there any problems in my coding?
by debugging, i know the problem must lie in the variant strConnect.
i use ms sql server 2000+ winxp+ visual c++6.0
_ConnectionPtr m_pConnection ;
void ADOConn::OnInitADOConn()
{
::CoInitialize(NULL);
try
{
m_pConnection.CreateInstance("ADODB.Recordset");
_bstr_t strConnect="Provider=SQLOLEDB;
TRUSTED_CONNECTION=TRUE;//use windows log in
Initial Catalog=....../*database name*/;
Data Source=SJTU-3V87CA6SZP";//server name
m_pConnection->Open(strConnect,"","",adModeUnknown);
}
catch (_com_error e) {
_bstr_t bstrSource(e.Source());
_bstr_t bstrDescription(e.Description());
TRACE( "Exception thrown for classes generated by #import" );
TRACE( " Code = %08lx", e.Error());
TRACE( " Code meaning = %s", e.ErrorMessage());
TRACE( " Source = %s", (LPCTSTR) bstrSource);
TRACE( " Description = %s", (LPCTSTR) bstrDescription);
}
}
Hi,
While executing a task in DTS package I found an error as Invalid Pointer.
SQL statement in Source of Transform Data Task Properties is :
Delete From TableName Where column in (Select * from Table Where Condtion2)
Select * from Table
and in Description field as :
Copy Data From [DB].[dbo].[Table] to [DMIS].[dbo].[TableName] Task
Destination table is :
TableName
Pls let me know how to rectify this Error.
Regards,
Srinivas Alwala
I'm a beginner in ssis. Use of Pointer in Data Flow task (Transformations)Royal PS
View 11 Replies View Relatedguys,I have a project need to move more than 100,000 records from onedatabase table to another database table every week. Currently, usersinput date range from web UI, my store procedure will take those dateranges to INSERT records to a table in another database, then deletethe records, but it will take really long time to finish this action(up to 1 or 2 hours).My question is if there is some other way I should do to speed up theaction, I am thinking about use bcp to copy those records to datafileand then use bcp to insert it into SQL Server table. Is this the rightway to do it or should I consider other solution (then, what is thesolution.)Thanks a lot!
View 8 Replies View Relatedhi,
I have a access database. In this database there are 10 tables and this tables are related to each other.
I want to move these tables to another database according to a field in XYZ table (vertical fragmentation).
for example there is name coloumn in the XYZ table and I want to take only joe lines and other tables related to that from database A and move it to database B
how can I do this?
thanks..
I’m working with an msde database on my local machine, but need to take that database and move it to my remote host. This is problematic because I can not just do a back up and restore.
I was wondering if there were any asp.net applications that would generate lots of sql statements to reproduce the database on my remote host.
I had built a program to back up all the tables in that way, but, forgot that the membership stuff uses stored procedures, and I don't not know how to modify my program in order to back those up too.
Any help will be much appreciated, thank you very much for reading my post
Hello,
What is the easiest way to move a database from one server to another with the all table structures, view and stored procedures?
Thanks,
Using sp_detach and sp_attach we can move different databases to different locations.
how do we do the same for system databases such as master,model,msdb and tempdb.
As off i know we got to be in master database to runthis command, if not this how do we go about this.
Thanks,
sheena
I read the one of the replies to a question.
"Subject:
From:
Date: SQL 7 Restore (reply)
Manish (manish45@yahoo.com)
11/1/01 9:03:43 AM
If you want to move all the databases to new server. Stop the SQL server service on existing server. Copy mdf and ldf files of all databases to same location iin new server. Also take the backup of master and msdb database from existing server. Then restore master and msdb on new server.
"
My question is how to move my database to a DIFFEREN LOCATION on a different server. My two servers are different file layout (not my choice though.)
Thanks a lot in advance.
Wuchun
P.S. Should I use the DTS at all to perform this task?
Hi:
I need your suggestion or experience about how to move a database from SQL 6.5 to SQL 7.0. The server where SQL 6.5 resides locates in different network than SQL 7.0. DTS does not work for the database move because of two server can not talk directly.
We need to do this task very soon in production environment. I tried to backup database to a disk (backup device in SQL 6.5) but I failed to restore it in SQL 7.0 because the backup file was not detected by restore utility in SQL 7.0.
Please tell me what I should do. Besides BCP, are there any other methods to move the database from SQL 6.5 to SQL 7.0 in diffferent networks.
Thank you very much.
Joan
Hi
I am new to sql server 7 and dont know how to 1.make a copy of a database
and 2 to move a database i know there are sys stored procedures but the syntax looks crptic and i dont know where to type them in if any one could give me any really clear examples of how to do both of the above i would be really grateful, thanks!
i've got to move 37 SQL Server databases over to new server. My manager tells me that i have to conduct some "check totals" to make sure that the databases contained exactly the same data from what it was when it got moved.
Is there some stored_procedure to do this ? to make sure that all the stored procedures, views, tables got move across ?
Hi,
I'm new to SQL Server. How do I port the modifications of my table structures, store procedures, functions on the staging server to the procduction server without affect the data?
Can I make this modification to production database while user are still accessing the database or I have to bring down the database to make these changes?
Thanks in advance,
Is there a way to move database from developement to production servers along with the logins and their associated roles as well as passwords. I did backup and restore and used sp_change_users_login to fix the logins
I mean is there a way to copy logins and roles Thanks
Can anyone tell me if it is possible to move the data directory of SQLServerfrom the application server where SQL runs to another system (the file andprint server) in the network?regards Joop
View 1 Replies View RelatedI'm needing to move a database from SQL 7 to SQL 2000. What is thebest way to accomplish this and keep all tables, data, permissions, etcin place throughout the move.Should I use BCP, Replication or something else?Thanks for any help.
View 2 Replies View RelatedI have a web project in VS.NET 2005 on my development computer that uses an ASP.NET database (ASPNETDB.MDF) to manage user roles. Now I would like to move that database to a test computer with IIS. After copying the web project and database I am able to read from it but when I try to update I get the following error:
Failed to update database "C:INETPUBWWWROOTMyWebAppAPP_DATAASPNETDB.MDF" because the database is read-only.
I think this is because the user 'Network Service' that IIS uses does not have access to the database. So how do I give 'Network Service' access to the database using SQL commands?
I'm trying to move a database created on SQL Server 2005 Express Edition to the SQL 2000 Server (that's the version the company uses). I understand that this is done by making a script that, when executed, creates an exact copy of the database structure on the target server as well as copying the stored procedures.
After that I somehow need to export the data from SQL Server 2005 Express to SQL Server 2000. How can I do that. There's no export function in Management Studio Express.
I am using VWD 2005 Express, with SQL 2005 Express. My web hosting company supports ASP 2.0 and SQL Server 2005, but have put SQL on a database server, which is separate from the application server. I successfully published a "Hello world" test application, but my database application failed after displaying the logon page, presumably when it first tried to connect to the database.
VWD has created my database within the application folder (appApp_DataASPNETDB.MDF) and created the configuration string in web.config: -
<add name="ConnectionString" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|ASPNETDB.MDF;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient" />
This all works fine on my local development server, but naturally fails when the application is copied to the web host, and the database can no longer be run from an application sub-folder. With SQL 2000 the problem would have been simple to fix. I would have created a database in the host DB server. I would then have backed up the development database, and restored it into the target database. I would then have changed the connect string, and all would have been OK.
How do I get the development database into the target database with SQL 2005 express? I can't use backup/restore, since there is no backup within VWD 2005 Express, and although there is a backup within Management Studio Express (MSE) I would need to get the database into MSE first. I tried opening the MDF file, but MSE wouldn't recognise this. I can't find any way of exporting the database - the equivalent of SQL 2000's DTS is not available within MSE!!!!!!!!! I tried installing an eval. version of the full Management Studio, but the install failed because "there is no upgrade path".
Once all that's sorted (if it can be sorted - I may have to go back to SQL 2000), where can I find out what to write in the configuration string?
Thank you, Robert Barnes
I need to move two databases from my old PC to my new one. Both machines are running Sql Server 2005 Express. I have tried a backup on old, copy the files to the new but I can't get restore to work. I type in the name of the new database to be created when the restore is complete and then select "From Device" and point to the backup file. I have tried both backup of entire database and backup of files. The error says: "Operating system error. System cannot find the path specified" Well, it found the path and backup file in the previous step when I pointed it to the backup media.
I have also tried the "Copy database wizard". However, Express does not come with "Agent" and the two machines can't see each other without Agent even though I have configured Express to allow remote access.
Any other ideas?
HiAs i have read, there have been discussions before how to move a local MDF databas to MSsql server at a webhost.But i cant figure it out, i know the answere is out there but i cant find it.I have downloaded the Host Deployment tool that generates a .sql file from my database, it looks ok.The problem is that my host dont allow me to execute this. I have also tried to run a file called RunSql.aspx that should help me execute my .sql file.But i recieve errors
Opening url http://mydomainz1.com/mydbfile.mdf.sqlAn error occured: System.Net.WebException: The remote server returned an error: (404) Not Found. at System.Net.HttpWebRequest.GetResponse() at ASP.runsql_aspx.__Render__control1(HtmlTextWriter __w, Control parameterContainer) in d:hsadminaccoundidmydomainz1.comRunSQL.aspx:line 58The line 58 is empty
Is there anyway that a mdf file that i have build in VWD 2005 can work as a standalone database?I would like it to be as a .mdb database that is simple to move and so on.
Or do the mdf database have to have a MSsql Express or a MSsql Server?
Grateful for help!
/ mitmit
Hi,
Can someone suggest a good way to move the CSK database from ine instandc of MSDE200 to another on a different PC. I tried exporting using MS Web database administrator which moved the tables but stripped off the defaults and all stored proceedures.
Any better ideas?
Hal
hi, i have no problem to select all records from the table, but how to copy or move these selected records to other table which contain same field as the orriginal.thanks
View 5 Replies View RelatedCurrently we update database A everyday with tables from database B.
Database is India and has poor connection speed. What would be the best method of moving this data.
Currently it's about 100 tables and on average its about 900 mb.
Any input is greatly appreciated.
thanks,