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!
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
' 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
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.
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?
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?
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.
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.
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.
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.
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.
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.
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
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
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;
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.
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
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
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?
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?
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.
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
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 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.
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.
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?
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
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
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 ?
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?
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.
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.
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.
