Batch Script To Create ODBC And Update Registry Datasource Key - Help!
Feb 21, 2008
I have create a batch file, that creates an ODBC then updates the application datasource key in the registry to the new system dsn name.
The problem is that the new DSN doesn't work when i try and connect the application...but if i had manually created the odbc source the app connects as expected... i have checked the registry and there is no difference between the two dsns created...but the application throws IM002[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified after i have also changed the applications registry key to point to the new database.
Now the interesting things to note are that when i re-create the odbc to the old server and database (sql 2000) by batch file it works fine. The new ODBC is linking to a sql 2005 database, but it is still using the 2000 drivers and when i manually created it, it worked also.
Another interesting thing is that if i go into the odbc dsn and click configure, go through and test the connection it works fine... after i close this i then retry the application and it opens correctly...
I need this to be automated with no manual intervention, as this will be added to a large group of users login scripts!
Any help greatly appreciated.
My batch file code is as below:
ODBCCONF.exe CONFIGSYSDSN "SQL Server" "DSN=RMS Live 2005 | SERVER=d-db99sql2005| Trusted_Connection=Yes | Database=RMS-Livedb"
View 4 Replies
ADVERTISEMENT
Jun 5, 2007
Hai,
I am using ADOX to create linked tables in a jet database from an ODBC datasource.
The tables in the ODBC data source does not have a primary key.
so I am only able to create read only linked tables.But I want to update the records also.
I tried adding a primary key column to the linked table while creating the link.
but I am getting an error while adding the table to the catalog.
The error message is "Invalid Argument".
I use the following code for creating the linked table
Sub CreateLinkedTable(ByVal strTargetDB As String, ByVal strProviderString As String, ByVal strSourceTbl As String, ByVal strLinkTblName As String)
Dim catDB As ADOX.Catalog
Dim tblLink As ADOX._Table
Dim ADOConnection As New ADODB.Connection
ADOConnection.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & strTargetDB & ";User Id=admin;Password=;")
catDB = New ADOX.Catalog
catDB.ActiveConnection = ADOConnection
tblLink = New ADOX.Table
With tblLink
' Name the new Table and set its ParentCatalog property
' to the open Catalog to allow access to the Properties
' collection.
.Name = strLinkTblName
.ParentCatalog = catDB
' Set the properties to create the link.
Dim adoxPro As ADOX.Property
adoxPro = .Properties("Jet OLEDB:Create Link")
adoxPro.Value = True
adoxPro = .Properties("Jet OLEDB:Link Provider String")
adoxPro.Value = strProviderString
adoxPro = .Properties("Jet OLEDB:Remote Table Name")
adoxPro.Value = strSourceTbl
End With
'Adding primary key,
'***** the source column name is "Code" ******
tblLink.Keys.Append("PrimaryKey", ADOX.KeyTypeEnum.adKeyPrimary, "Code")
'Append the table to the Tables collection.
'******The exception occurs on the following line***********
catDB.Tables.Append(tblLink)
'Append the primary index to table.
catDB = Nothing
End Sub
If I avoid the line for adding the primary key,everything works fine,but the table ctreated is readonly.
Thanks in advance
Sudeep T S
View 4 Replies
View Related
Oct 7, 2013
Is there a way to create a Batch file that will run an Update Statement and schedule it to run?I've used bcp to extract data to a txt file before, but i'm not sure if an Update can be performed.I'm using SQL Server 2008 R2 Express Edition so i don't have Server Agent available.
View 7 Replies
View Related
Feb 3, 2007
Hi im trying to install Sql Server 2005 but it says i need to Increment the Counter Registry Key for Setup in SQL Server 2005 before doing so. Im following the guide at http://msdn2.microsoft.com/en-us/library/ms143215.aspx but in my registry the folder HKEY_LOCAL_MACHINESOFTWAREMicrosoftWindows NTCurrentVersionPerflibdoesnt contain any keys"Last Counter"=dword:00000ed4 (5276)"LastHelp"=dword:00000ed5 (5277) or any key for that matter.How can i fix this?
View 1 Replies
View Related
Sep 10, 2012
We have a SQL database that uses Active Directory with Windows Authentication. Can users that are members of the Active Directory group that has read/write access to the SQL database create ODBC connections to access the database directly and update the data? They dont have individual logins on the server. They are only members of the Active Directory group that has a login?
View 1 Replies
View Related
May 13, 2007
Hi,
I am using VB.NET 2005 and set up an ODBC connection via ODBC.ODBCConnection to a MDB database. Therefor, I use the "Microsoft Access ODBC Driver (*.mdb)".
When I set up a ODBCCommand like "ALTER DATABASE..." or "CREATE TABLE..." and issue it with the com.ExecuteNonQuery() command, I get an error from ODBC driver, that a SQL statement has to begin with SELECT, INSERT, UPDATE or DELETE.
How can I use DDL statements via ODBC?
I would appreciate if you could help me to use ODBC for that - no OLE, no ADO.
Thanks for help!
Regards,
Stefan D.
View 14 Replies
View Related
Jan 16, 2008
I€™m facing a SQL batch insert issue on Windows 64 bit (IA-64) platform. I€™m using €˜Column-Wise Binding€™ of parameters. The example program hosted on Microsoft site (http://msdn2.microsoft.com/en-us/library/ms709287.aspx) returns error of SQL_NEED_DATA for batch insert on Windows 64 bit platform. The same code works fine on 32 bit platform. I€™m using SQL Server 2000 as the database and tried using both native SQL server ODBC driver and Merant 5.2 ODBC drivers. Any help on this will be greatly appreciated.
View 5 Replies
View Related
May 22, 2015
Data Source of the report is AS/400 and I need to create a report in visual studio 2013 with ODBC Connection type and deploy report onto share point 2013. How to create report, data source and deploy on share point.
My problem is how we will create ODBC connection with AS/400 and connect report with AS/400.
View 2 Replies
View Related
Apr 11, 2007
Hi all,
When I run the following T-SQL statement using SQLExecDirect with the SQL Server ODBC driver, it seems to indicate to me that the driver is somehow treating the SQL as a batch rather than a single statement.
Code Snippet
IF (1=1)
UPDATE MyTable
SET [Guid] = 'A663EC5D-A3AF-4391-8C45-9EBE87C9FD6C',
[Id] = 1
[FieldA] = 'Always run'
WHERE ([ID] = 1)
ELSE
INSERT INTO MyTable ([Guid],[Id],[FieldA]) VALUES (NEWID(), 2,'Never run')
Now the above statement is only a simplified statement to illustrate my problem, but I've also tested this stripped down version to ensure that I could replicate the problem with it.
Assuming I have the following table definition and data, I would expect that SQLExecDirect return SQL_ERROR due to the unique key constraint being violated on the [guid] column. However, I'm getting a SQL_SUCCESS_WITH_INFO error.
Code Snippet
CREATE TABLE MyTable
(
[Guid] uniqueidentifier,
[Id] int,
[FieldA] varchar(100)
)
INSERT INTO MyTable ([Guid],[Id],[FieldA]) VALUES (NEWID(), 1,'A')
INSERT INTO MyTable ([Guid],[Id],[FieldA]) VALUES (NEWID(), 1,'B')
According to a MS knowledge base article 195297:
In the version 3.6 SQL Server ODBC driver, SQLExecute, SQLExecDirect, or SQLParamData returns SQL_ERROR only if no other statements are executed after the first statement. If any other statements are executed after the first, even a simple RETURN statement with no return value, SQLExecute or SQLExecDirect returns SQL_SUCCESS_WITH_INFO
This makes me think that somehow the SQL Server driver is treating the T-SQL (with IF-ELSE) as a batch of statements (normally delimited with semi colon e.g. "statement1; statement2;").
I'm running MSDE, with SQL Server driver (SQLSRV32.DLL) v2000.85.1117.00 on Windows XP Professional SP2.
However, running the same SQL but changing the driver to the SQL Native Client (SQLNCLI.DLL) v2005.90.1399.00 returns SQL_ERROR as I expect.
Also the same SQL returns the desired error using both SQL Server and SQL Native Client drivers when connecting to SQL Server 2005.
So in summary:
Driver MSDE SQL Server 2005
{SQL Server} SQL_SUCCESS_WITH_INFO SQL_ERROR
{SQL Native Client} SQL_ERROR SQL_ERROR
Is there any settings that I need to set? Does anybody have any ideas?
Any help greatly appreciated!
View 3 Replies
View Related
Mar 13, 2007
Is it possible to create a datasource, which is not at al shared via vs 2005
View 1 Replies
View Related
Jun 8, 2007
I'm trying to use WSS3 & RS 2005 as Integration Mode.
But i cannot create a data source item on sharepoint site library.
If i go to Central Administration > Configure Reporting Services Integration,
I can see the settings, thats mean that the Report Server is available ( i think so )
And also i can create dataources on Central Administration Web site (//servername:12500)
But from my production server web site ( //servername/ ), i cannot create those items.
I received the message
An unexpected error occurred while connecting to the report server. Verify that the report server is available and configured for SharePoint integrated mode.
Can somebody help me to fix that strange behavior.
Thanks.
View 1 Replies
View Related
Jan 23, 2007
Hello, I Created a report with reportbuilder, after clicking on Run Report I got this message, Its very strange it seems that its trying to generate or create a datasource.
Para obtener más información acerca de este error, vaya al servidor de informes en el equipo del servidor local o habilite los errores remotos
----------------------------
No se puede crear una conexión al origen de datos 'dataSource1'.
----------------------------
Error al procesar el informe.
View 1 Replies
View Related
Aug 12, 2007
Hai, i would like to do Bulk update to avoid the round trip to the database.
Means, In my UI im dsiplaying all the employee details who are related to one particular dept. Now i want to update the bonus to all the employees based on their category. UI changes are refelected in the Datatable(.NET). Finally the datatable changes i would like to update in the Database.
how can i do that..
sample code pls.
im very very new to sql
View 3 Replies
View Related
Oct 5, 2007
Hi
I have written stored procedures to create a database with db name as input parameter
I need to create a batch file to run the stored procedure with input value given in the command prompt along with the batch file will be my dbname, which will be used by the stored procedure to create the databse.
and also few sql statements to insert data into those tables after creating
Plz could any one help me out to create code to create a batch file
thanks in advance
View 4 Replies
View Related
Jul 12, 2004
hi,
I have a dataadaptor which i use to fill a dataset. I can do this no problems. But if I delete a row from the dataset and update the adaptor again, nothing gets changed in my source database. Does anyone know why this is?
here's my code for my dataadaptor:
SqlConnection conn = new SqlConnection(connectionString);
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = new SqlCommand("select * from tblExtRef", conn);
adapter.SelectCommand.Connection = conn;
SqlParameter parm;
SqlCommand cmd;
cmd = new SqlCommand("DELETE FROM tblextref WHERE extrefid = @extrefid",conn);
parm = cmd.Parameters.Add("@extrefid", SqlDbType.Int , 40, "extrefid");
parm.SourceColumn = "ExtRefid";
//parm.SourceVersion = DataRowVersion.Original;
//parm.Direction = ParameterDirection.InputOutput;
//SourceVersion = DataRowVersion.Original;
adapter.DeleteCommand = cmd;
View 1 Replies
View Related
Sep 26, 2007
Hai All..
I have develop any report in reporting services on server Development. i deploy my report to server production.
i have connection to cube as data source. if open report manager in server production and open the report, its succesfully. the report is open. but if i open report manager in server development show error
An error has occurred during report processing. (rsProcessingAborted)
Cannot create a connection to data source 'PNL'. (rsErrorOpeningConnection)
For more information about this error navigate to the report server on the local server machine
i use windows authentication in my pnl datasource.
thanks
Yudhi
View 2 Replies
View Related
Apr 17, 2007
I have a collection of around 16000 records, and have been trying to find the best way to update the information in the DB. I have done alot of reading about both BulkCopy and Batch Update, but haven't come to any clear solutions as to which performs better. I am not doing any inserting, just getting a dataset from the DB, changing the values, them want to update the Db again. Thanks for any help. Mick
View 8 Replies
View Related
Jul 1, 2007
Can anyone help a beginner with some T-SQL which runs as a scheduled stored procedure to update a table with is then accessed via an ASP web application.
I have a table called Loans which contains a calculated column which will indicate in days if a loan item is late and also each row has a charges column to reflect a charge for late returns.
In a seperate table I have a charge per day for late returns. I read this into a variable @LateCharges
I'd like to consutruct some T-SQL to scan through the Loans table and for every row where Status is not 'Returned' I woule like it to update the charges column based on the DaysLate column*@Latecharges
Any help much appreciated.
Regards
Clive
View 3 Replies
View Related
Apr 15, 2004
Ok.. I thought I could do this but I can't get this to work.. I need to create a batch file to run an sql update statement.. This is simple but I can't get it to wortk.. How do I do it?
View 4 Replies
View Related
Jun 20, 2007
I have windows 2003 reporting services 2005 that has been working fine. Now all of a sudden when I go to http://localhost/reports I am unable to assign security to folders(the link is hidden) the New data source button is hidden, the new folder button is hidden, upload folder and report builder are also hidden. I am logged in as aministrator.
Also when I connect to reporting services through SQL Mgmt Studio I have the same issue, there are no options for permissions only a general option.
I'm sure it's a permissions issue but what do i do to resolve?
Thx
View 2 Replies
View Related
Mar 11, 2008
I'm trying to create report with datasource as a cube. I'm able to connect to datasource to pull data with a single parameter. But, I'm unable to succeed in creating a cascading parameter.
The following scenario:
I have a dimension "Period" as Paremeter.
The Period dimension is cascaded to year- quarter- month.
So, I would like to create a cascading parameter for period as year - quarter - month.
Please let me know, how to acheive this.
thanks,
Naveen.
View 5 Replies
View Related
Feb 26, 2004
The following code is a part of my stored procedure MySP. I would like to update Users table with input variable @userIDs which has the following format:
101, 102, 103
I got the error message:
Syntax error converting the varchar value '101, 102, 103' to a column of data type int.
Obviously, UserID has datatype int. How can I write this SP?
CREATE PROCEDURE dbo.MySP
@userIDs varchar(100)
AS
SET NOCOUNT ON
BEGIN TRANSACTION
UPDATE Users SET IsActive = 1 WHERE UserID IN (@userIDs)
IF @@ERROR <> 0
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION
SET NOCOUNT OFF
View 8 Replies
View Related
Apr 19, 2007
Can anyone help me understand what this means:
Code Snippet
java.sql.BatchUpdateException: com.microsoft.sqlserver.jdbc.SQLServerException: The IOBuffer.process operation returned an unknown packet type:0. Index:41. End:83.TDS_DONEINPROC(-1) TDS_DONEPROC(-2) TDS_DONE(-3) TDS_COLMETADATA(-127)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeBatch(Unknown Source)
I'm trying to do a very simple batch update.
View 7 Replies
View Related
Apr 24, 2014
I have about 1200 sql files in one of my folders. Almost all of these files do data inserts and updates, so they should be run only once. As and when required I have manually ran around 150 of them already. Whenever I ran any of these scripts, I log that file name into a log table in my sql server including the execution time. Since running 1000+ more files takes a lot of time, I want to automate running of these files through a batch file. But I also want to filter the files that are already run.My file list looks like follows.
InsertToOrderTypes.sql
UpdateClientAddress.sql
DeleteDuplicateOrders.sql
InsertToEmailAddress.sql
ConsolidateBrokerData.sql
UpdateInventory.sql
EliminateInvalidOfficeLocations.sql
My log table in the database looks like this.
select * from sqlfileexecutionlog
FileNameRunTimeResult
---------------------
DeleteDuplicateOrders.sql03/12/2014 14:23:45:091Success
UpdateInventory.sql04/06/2014 08:44:17:176Success
Now I want to create a batch file to run the remaining files from my directory to my sql server. I also want to wrap each of these sql file executions in a transaction and log success/failure along with the runtime and filename into sqlfileexecutionlog table. As I add new sql files into this directory, I should be able to run the same batch file and execute only the sql files that have not bee run.
View 9 Replies
View Related
Mar 24, 2008
does anybody know where I can learn how to add, update, delete using SQL datasource control? I have a couple of books that shows me how to SELECT but nothing shows me how to update, add new record or delete with the SQL data source control. Even this website doesn't have any tutorial. Does any body know where I can read up on how to use all features of this control? Or where that have a couple of examples on how to use it?
View 5 Replies
View Related
Jul 27, 2015
1. As per my current development SQL Sever Analysis Database consists of two Cube (Cube A and Cube B).  Cube A and Cube B share the same data source view (DSV1). The source for both these cubes has the same data source (DS1).
2. As per the requirement I need to create third Cube i.e. Cube C. Is it possible to create a second Data Source View (DSV2). The Source of second Data Source View (DSV2) will be the same data source(DS1).
I am thinking to create second Data Source View (DSV2) for Cube C because existing layout of the DSV1 has become complex. I wanted to know the pros and cons of creating a multiple data source view with same data source
View 3 Replies
View Related
Jan 28, 2004
hello .
i have a grid for a table that gets updated with recordset.updatebatch
for a multi-user application.
the problem is that some of the table fields have to be unique
now imagine the next situation.
user A opens the form
user B opens the form
user A writes '1000' in the unique field
user A saves the recordset with updatebatch
user B writes '1000' in the unique field
user B saves the recordset with updatebatch
now there will be two records with the field '1000' !
how can i avoid this ?
i cannot check for unique during the update event of the grid
because it should check during the time it is saving and not
when it is just entering data without having updated the recordset
thanks !
View 2 Replies
View Related
Oct 31, 2007
I used "OLE DB Command"(row by row) for update but because i found that very very slow i decided to try batch update. I check what is for insert and what is for update and those things that are for update i save to temp table. Then i run this:
update [ODS].[dbo].[Adres]
SET
[MutatieDatumEinde] = stAdres.MutatieDatumEinde,
[StraatNaam] = stAdres.StraatNaam,
[HuisNummer] = stAdres.HuisNummer,
[HuisNummerToevoeging] = stAdres.HuisNummerToevoeging,
[Postkode] = stAdres.Postkode,
[Plaats] = stAdres.Plaats,
[Land] = stAdres.Land,
[IndicatiePTTAdresStandaard] = stAdres.IndicatiePTTAdresStandaard,
[KodeHerkomstAdres] = stAdres.KodeHerkomstAdres,
[VervalDatum] = stAdres.VervalDatum,
[BronODS] = stAdres.BronODS
from
[ODS].[dbo].[Adres] oAdres
inner join [M2O_Stage].[dbo].[ODS_Adres] stAdres
on oAdres.InstNr = stAdres.InstNr and
oAdres.TypeAdres = stAdres.TypeAdres and
oAdres.MutatieDatumAanvang = stAdres.MutatieDatumAanvang
where stAdres.IsInsert = '0'
I run this as an "execute sql task". Everything is running as it should and speed is incomparable, but.. i have problem to make this generic... what i mean is.. db names will change.
How can i make db names dynamic here ?
p.s. i just got idea .. i could probably make dynamic sql statement in script component and then run it there... but does anyone have a better idea ?
View 3 Replies
View Related
Oct 20, 2005
I am making a rss script to deploy the reports and datasources.
View 2 Replies
View Related
Mar 12, 2007
I have a stored procedure that takes a date range and returns all the sales in that date range. I'm trying to create the report model for ad-hoc reporting. When I go to create the dataset view, it only lets me select tables or views.... how do I get around this?
View 2 Replies
View Related
Mar 23, 2008
I can select the tables and fields, but when I click on Advanced the Check Box to Create the Commands is not available. they are dimmed out.
Why is this happening, is it a setting that I have missed. Any help is appreciated.
View 1 Replies
View Related
May 10, 2006
I want to update two tables in one page. So I created two FormView bound on two SqlDataSource controls, and I create a Update button on the bottom of page. And I writen some codes as below:
btnUpate_Click(object sender, EventArgs e){
sqlDataSource1.Update();
sqlDateSource2.Update();}
But, the records haven't updated.
In SqlDataSource2_Updating() function, I found all the parameters is null.
So, how to modify my code to do it.
Zhang
View 2 Replies
View Related
Jul 20, 2005
I created a trigger in the "source table" that will "feed" and secondtable. The trigger is as follows:CREATE TRIGGER [FeedToP21] ON dbo.FromUPSFOR INSERTASDeclare @Count intSelect @Count = Count(*) from InsertedIf @Count > 0BeginInsert into ToP21Select i.* From Inserted iLeft Join ToP21 ton i.recnum = t.recnumWhere t.recnum is nullEndIf @@ERROR != 0Rollback TranA record was created in the "source table" via ODBC, however, thetrigger does not seem to have fired to create the record in the secondtable.If I create a record manually using SQL Server Enterprise Managerwithin the "tableview" the trigger fires and a duplicate record iscreated in the second table.Is there a fix for this problem?Thank you in advance.
View 1 Replies
View Related
