T-SQL (SS2K8) :: Conditional Multiple ODBC Connections

Feb 4, 2015

If I have 100 ODBC clients all submitting the following SQL at the same time how do I know that the SQL inside the conditional only gets executed once. It appear SQL Server is preventing it from happening but was curious how. Or do I need to add something to prevent it from happening.

IF NOT EXISTS (SELECT name FROM sysobjects WHERE type='U' AND name = 'mytable)
BEGIN
CREATE table mytable (task int NOT NULL, first_name varchar(128), last_name varchar(128))
END

View 1 Replies

Multiple Odbc Connections And Transactions

Jan 3, 2008

I am using ODBC to connect to SQL Server. The documentation says that ODBC transactions are managed on the connection level and cannot span connections.

Does this mean that two instances of my code using transactions (each with its own process and a single connection) in the same machine accessing the same database will not play nice together (violate transaction rules)?

Thanks

View 3 Replies View Related

Odbc Connections

Jan 13, 2004

A colleague of mine has a problem;
"Has anyone ever experienced the problem of creating a System ODBC and it doesn't display in the ODBC administrator applet?"
was hoping someone here could help...

View 2 Replies View Related

ODBC Connections

Oct 23, 2007

Hi.

I'm sorry of my english.

I have to servers.
Server A (local machine).
Server B (Remote machine with Integration Services withpackages after deployment).

In Server B i have some packages with ODBC connection..
In the Server A I have created the same ODBC connection.

I need to execute the packages that are in the Server B in the Server A.
I execute them throw dtexec in cmd in the Server A.

The problem is that it starts to execute but gives an execution error because it gets confused with what Server system should be used (both have the same ODBC connection).
If I run the packages throw cmd in the Server B everything goes OK.

Can anyone help me!?

Thanks.

View 7 Replies View Related

ODBC Connections Disappearing

Mar 12, 2008

I have set up 80 connections to the SQL Server - mostly through code. People have been successfully using the database, but then when they come in to use their computer and the database the next day, their connection is completely gone! What is that all about? So far this has happened to 2 or 3 different users. Any idea why?
Thanks.

View 1 Replies View Related

ODBC Connections And SSIS

Jun 15, 2006

so im trying to connect to an odbc source and use ado.net to pass some sql queries and then write back into this odbc connection. i am aware that ssis does not have direct capabilities to do this, but i wanted to see if anyone knew of generic help docs/url's that show how to do this? i am new to ssis, and am just trying to get general information.

View 4 Replies View Related

T-SQL (SS2K8) :: Selection With Conditional Insert Union

Apr 4, 2014

In this selection when there is particular segment found in the selection then need to add 2 records per segment otherwise just selected results

--Drop table #list
CREATE TABLE #LIST ( email varchar(20),Segment varchar(20), Addr1 varchar(20), City varchar(20),ST varchar(20), Code int)
--Drop table #subject
CREATE TABLE #Subject (Segment varchar(20), Fname varchar(20), LName varchar(20))

[Code] ....

---Selection results
SELECT L.Segment , l.email, l.Addr1 , l.City ,l.ST, l.code, s.Fname , s.LName into #expectedresult FROM #LIST L JOIN #Subject S ON l.Segment =s.Segment

--Now here what i am trying to get, union the below two record to the above "selection Results" for only segment ='VEC_BAL' or segment = 'PPC_BAL'

--2 records
SELECT l.segment , l.email , Addr1, City, ST, 999 as Code,'' fname, ''lname from #LIST l where email ='mmm@m.com'
SELECT l.segment , l.email , Addr1, City, ST, 888 as Code, '' fname, ''lname from #LIST l where email ='lrk@m.com'

--- expected results please execute below block ( per each segment 2 records will insert if there is segment ='VEC_BAL' or 'PPC_BAL' ). If there is no Vec_Bal or PPC BAL then no additional inserts, if there is only VEC_BAL no PPC_BAL then only 2 records insert
SELECT L.Segment , l.email, l.Addr1 , l.City ,l.ST, l.code, s.Fname , s.LName into #expctresults1 FROM #LIST L JOIN #Subject S ON l.Segment =s.Segment
union

[Code] .....

View 3 Replies View Related

Moving ODBC Connections Between Servers

Jun 28, 2004

Hello all,

I was wondering if there is a way to move ODBC connections between SQL servers? We are replacing our current server with newer hardware, and I have pretty much everything figured out except this.

Thanks for any help and/or hints.

View 10 Replies View Related

SSIS Is Pathetic With ODBC Connections ...

Nov 30, 2007

We have spent days trying to perform a proof of concept and I amdissappointed with SSIS to say the least. We are trying to connect andfetch data from a Double Byte Progress database and the "DataReaderSource" using a ADO .NET ODBC provider does not work! I understandthat SSIS is a totally rewritten version of DTS. What annoys me isthat this functionality used to work in DTS and does not in the muchtalked about SSIS!! Will someone in Microsoft start listening tocustomers instead of gloating on useless features. SSIS is a totalwaste if it cannot connect and fetch data from a wide variety ofsource databases!!

View 6 Replies View Related

Automatically Create ODBC Connections

Jul 20, 2005

Hello everybody!I'm using the 'Data Sources (ODBC)' program that comes with windows tocreate the odbc connections I need. Although that is quite fast and easy Iwould like to have it more automated, since I'm using the same parametersall the time.Can anybody tell me (or give pointers to) how that is done?regards--Johnny Ljunggren

View 2 Replies View Related

Preventing ODBC Connections To A SQL Database

Oct 4, 2007

I have a user AD account SOPUSJSmith for example. I have given this account security admin at the SQL Server level and dbo at the database level. Is there any way to prevent them from using ODBC to pull data into an Access DB or an Excel spreadsheet?

Thanks in Advance - SOX is killing me!

View 4 Replies View Related

Single SQL Query To Two Foxpro (ODBC) Connections

Nov 4, 2004

Dear All,

I like to know if anyone of you out there can help me solve this issue. I need to generate an SQL Query to access two different FOXPro (databases) connections.

Example,
Connection 1 - has a DSN setup of CMSBM and it sits in the path of D:/CMS/CMSBM/= and the table is bmboml15
Connection 2 - has a DSN setup of CMSMA and it sits in the path of
D:/CMS/CMSMA/= and the table is maprodl15

I am coding in ASP.NET using C#. I have tried the command like:

SELECT codeno, link, type FROM bmboml15 IN CMSBM INNER JOIN maprodl15 IN CMSMA ON bmboml15.codeno = maprodl15.codeno.

Obviously the above query is not a working one and a very poor construction.

Please help!

Thanks
Clo

View 1 Replies View Related

Connecting To ODBC Connections With Report Services

Sep 29, 2006

I have just migrated from SQL 2000 to SQL 2005 and in the process upgraded to new hardware. I am now running SQL2005 (64 Bit) on Windows 2003 R2 (64 Bit). The problem is that when i deploy some reports to the new server that use ODBC to connect to the data, reporting services is erroring with :

Data source name not found and no default driver specified

The ODBC connections are set up exactly the same on the old server and the new server. Is it because it is looking for a 64 bit ODBC driver and not the 32 Bit one I have installed. If i go into the SysWOW64 and run the odbcad32.exe i can see the drivers and the connection. The connection test works fine from here.

Thanks

View 6 Replies View Related

ODBC Connections Hang When Cluster Group Moves

Jan 1, 2000

When I move an SQL group to the other cluster member, it seems that the ODBC connections hang on the client web servers, and do not process any queries until they are rebooted. I'm using IIS/Cold fusion web servers with an SQL 7.0 Cluster (MSCS). Any ideas why this may be happening?

Thanks,

Steve Johnson

View 2 Replies View Related

Database Mirroring, Client Connections All ODBC So What Methods Are Recommended?

Jun 5, 2006

As stated in the subject I have a situation where if database mirroring is employed for either manual or automatic failover, all the client (including web connections) connections use ODBC not ADO, or OLEDB etc... so what methods are recommended? Client side redirect is not available so I could not employe the "Data Source =A; Failover Partner=B..." option.

Right now the method employed (pre database mirroring and basically employing log shipping on SQL 2000) is to have a DNS alias for the ODBC connection so that if the server were to change in a failover situation the DNS record would have to be altered, so that all the client connections would not have to be reconfigured.

Regards,

Dominic Baines

View 5 Replies View Related

T-SQL (SS2K8) :: Query Maximum Concurrent Connections (Participants)

Mar 4, 2015

I have a table called dbo.PhoneCalls with below columns

PhoneID |PhoneNumber| Callstarttime| CallEndtime|
1 |111-111-1111|2013-04-01 05:13:03.000|2013-04-01 05:13:03.000
1 |222-222-2222|2013-04-01 05:15:12.000|2013-04-01 05:16:52.000
2 |333-333-3333|2013-04-01 05:17:29.000|2013-04-01 05:24:08.000
2 |444-444-4444|2013-04-01 05:21:50.000|2013-04-01 05:22:31.000
2 |555-555-5555|2013-04-01 05:22:41.000|2013-04-01 05:23:11.000
2 |666-666-6666|2013-04-01 05:23:20.000|2013-04-01 05:23:46.000
..........

1. PhoneID is nothing but the participant in the call. PhoneID = 1 is twice from above. Which means 2 particpants (Same call )with 2 numbers with their callstarttime and callendtime. Similarly for PhoneID =2, there are 4 participants. And the list goes on for a day and then for a month.

2. For example a phone call P1 with 2 participants is going on for a particular day. We should not consider the same phone call having 2 participants involved. So that the concurrency would be 2. We have to ignore that here.

3. Only to be considered is other Phone calls for that day. Lets say P1 having call with 2 participants, P2 having some 4 participants which fall in the time period of P1. Then we should consider P1 and P2 the common period

4. In order to find number of concurrent calls happened for a day basing on callstarttime and callendtime. What would be the query?

5. Should consider the Timeperiod or the bucket with 1 hour as the period.

6. A Phone Call P1, Phone Call P2, should have matching (common) time ( keeping all the scenarios) is required for this query.

Result for Concurrent calls for a day should be like below. Should get all the concurrent connections happened for a particular day.

Date|TimePeriod/Bucket(hr part)|Concurrentconnections|
Jan-01-2015|01 to 02|3
Jan-01-2015|11 to 12|2
Jan-02-2015|04 to 05|5
Jan-02-2015|12 to 13|13
........

ii) So once the above is achieved.

Have to find the Maximum concurrent connections for day from the above.

For below Maximum Concurrent connections are '3'
Date|TimePeriod/Bucket(hr part)|Concurrentconnections|
Jan-01-2015|01 to 02|3
Jan-01-2015|11 to 12|2

Hence the Result for Maximum Concurrent Connections would be

Date|TimePeriod/Bucket(hr part)|MaxConcurrentconnections|
Jan-01-2015|01 to 02|3
Jan-02-2015|12 to 13|13
.............

View 3 Replies View Related

T-SQL (SS2K8) :: Selecting Data From Table With Multiple Conditions On Multiple Columns

Apr 15, 2014

I am facing a problem in writing the stored procedure for multiple search criteria.

I am trying to write the query in the Procedure as follows

Select * from Car
where Price=@Price1 or Price=@price2 or Price=@price=3
and
where Manufacture=@Manufacture1 or Manufacture=@Manufacture2 or Manufacture=@Manufacture3
and
where Model=@Model1 or Model=@Model2 or Model=@Model3
and
where City=@City1 or City=@City2 or City=@City3

I am Not sure of the query but am trying to get the list of cars that are to be filtered based on the user input.

View 4 Replies View Related

TableName Property In Conditional Multiple Recordset

Dec 29, 2007

Hi everyone,

I'm using a stored procedure in SQL Server to generate multiple recordsets. The recordset are however generated conditionally. create procedure GetData
(
@a bit
@b bit
)

Select * from Customers

IF(@a=1)
Select * from Products

IF(@b=1)
Select * from Details ------------Issue:The adapter returns the table named as Table, Table1(optional) and Table2(optional). It might be that Table1 is products or Table1 is Details.How can I map my tablename property in this scenario where the sequence of tables is not known.

View 3 Replies View Related

Conditional Case Calculations Multiple Tables?

Apr 20, 2014

I have 4 tables involved here. The priority table is TABLE1:

NAMEID TRANDATE TRANAMT RMPROPID TOTBAL
000001235 04/14/2014 335 A0A00 605
000001234 04/14/2014 243 A0A01 243
000001236 04/14/2014 425 A0A02 500

TRANAMT being the amount paid & TOTBAL being the balance due per the NAMEID & RMPROPID specified.

The other table includes a breakdown of the total balance, in a manner of speaking, by charge code (thru a SUM(OPENAMT) query of DISTINCT CHGCODE

TABLE2

NAMEID TRANDATE TRANAMT RMPROPID CHGCODE OPENAMT
000001234 04/01/2014 400 A0A01 ARC 0
000001234 04/05/2014 -142 A0A01 ARC 228
000001234 04/10/2014 15 A0A01 ALT 15
000001235 04/01/2014 400 A0A00 ARC 400
000001235 04/05/2014 50 A0A00 ALT 50
000001235 04/10/2014 105 A0A00 ACF 105
000001235 04/11/2014 50 A0A00 ADR 50
000001236 04/01/2014 500 A0A02 ARC 500

The other table stores the priority order of the charge codes

TABLE3

CHGCODE PRIORITY DESCRPTN
ACF 1 Court fee
ALT 2 Late fee
ANS 3 NSF fee
ARC 4 Rent
ADR 5 Repair
AUR 6 Utility

While the forth stores the customer data:

TABLE4

NAMEID RMPROPID FIRSTNAME LASTNAME NAMEGROUP
000001234 A0A01 Jane Doe 000001234
000001235 A0A00 John White 000001235
000001236 A0A02 John Smith 000001236
000001237 A0A02 Jennifer Smith 000001236

This table's importance comes by the inclusion of the NAMEGROUP. This way if an account has multiple NAMEIDs, it can be kept straight by their shared NAMEGROUP.

I am trying to create a report using queries that will:

A) calculate the sum of the OPENAMT per NAMEGROUP per DISTINCT CHGCODE B) count the number of records (DISTINCT CHGCODEs) per DISTINCT NAMEID in ORDER by the CHGCODE PRIORITY Then C) calculate a case query whereas:

CASE WHERE
TABLE1.TRANAMT=> the calculated sum of the highest priority CHGCODE THEN 'TABLE1.TRANAMT'
ELSE WHERE TABLE1.TRANAMT <= the calculated sum of the highest priority CHGCODE
THEN 'the calculated sum of the highest priority CHGCODE'
...then...
CASE WHERE

[Code] ....

The results should be something like:

NAMEID TRANDATE TRANAMT RMPROPID CHGCODE APPLAMT
000001235 04/14/2014 335 A0A00 ACF 105
000001235 04/14/2014 335 A0A00 ALT 15
000001235 04/14/2014 335 A0A00 ARC 215
000001234 04/14/2014 243 A0A01 ALT 15
000001234 04/14/2014 243 A0A01 ARC 228
000001236 04/14/2014 425 A0A02 ARC 425

Also with a remaining balance (per CHGCODE) column.I can't figure out how to word the queries.

View 1 Replies View Related

Multiple Ado Connections

Feb 19, 2004

Hi, I have a VB application running on SQL Enterprise Edition. Physically there are only 20 users loging in to the software at one time. However i can see more that 12 SPIDs for each user. The developers have been opening a new connection for each form in the application i guess. Is it normal or is there any specific requirement that foces them to do so? Shall i need to change the default WORKER-THREADS option on the server for peformance? Suggestions please!!!

Regards.

View 7 Replies View Related

Transact SQL :: Conditional Update A Field From Multiple Tables

Sep 9, 2015

Conditional Update of a field from multiple tables..I have a target table with two fields: Date and ID..There three source tables: S1, S2, S3, each of them has three fields: Date, ID, and Score...Now I want to update the target table: put the ID into the ID field which has the highest Score from the three tables on each day.

View 13 Replies View Related

Multiple Connections In A CLR Trigger

May 23, 2007

Hello

Is there nay way that it is possible to connect to another database within a trigger��

My scenario is that an update in a table in database X triggers several updates in a table in Database Y. The databases are on the same SQL Server

Thanks for any ideas, solutions or hints you can give me..!!!

View 5 Replies View Related

Multiple Connections In Managed Trigger

Apr 13, 2006

Hi All,

I am trying to open multiple connections in a Managed Trigger but encoutering an error as :

System.Data.SqlClient.SqlException: Transaction context in use by another session.

Below is the sample code:

public partial class Triggers
{

[Microsoft.SqlServer.Server.SqlTrigger (Name="TrgInsertContract", Target="Contracts", Event="FOR INSERT")]
public static void TrgInsertContract()
{
SqlTriggerContext triggContext = SqlContext.TriggerContext;
SqlConnection connection = new SqlConnection("context connection = true");
connection.Open();

SqlCommand command = connection.CreateCommand();
command.CommandText = "SELECT * from " + "inserted WHERE Active=1";
SqlDataReader reader;
reader = command.ExecuteReader(CommandBehavior.CloseConnection);
connection.Close();
SqlConnection connection1 = new SqlConnection("Initial Catalog=TestDB;Data Source=SHAIKDEV;User ID=sa;password=****");
connection1.Open();
}

Any help please ?????

Thanks...

View 5 Replies View Related

Help With Multiple Connections In A CLR Stored Procedure

Nov 3, 2005

Here's what I'm trying to accomplish:

View 5 Replies View Related

SQLExpress Problem With Multiple Connections

Feb 21, 2007

I have a SQLExpress Database that I can connect to from a C# application and seperately from an application via 'OBDC' with NO problems. Both applications are running on the Local PC running XPpro.

However, I cannot connect to the same database from both applications at the same time!

Is there a way around this ?, is it a restriction of the Express version ?

View 3 Replies View Related

Database Create ODBC Connections To Access Database Directly And Update Data?

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

Integration Services :: SSIS Execute Multiple Packages Based On Conditional Split

Sep 4, 2015

We are building a dataload application where parameters are store in a table. And there are multiple packages for each load.There is a column IsChecked column if it is 1 then only the child package should execute.Created a master package. In which i have taken execute SQL task in that storing a results in variable and based on the result the child package should execute. But In executesql task i selected result set as full result set. I am getting the below error.

[Execute SQL Task] Error: Executing the query "SELECT isnull(ID ,0) AS ID FROM DataLoadParameter..." failed with the following error: "The type of the value (DBNull) being assigned to variable "User::LoadValue" differs from the current variable type (Int32). Variables may not change type during execution. Variable types are strict, except for variables of type Object.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

View 3 Replies View Related

Best Way To Handle Data Connections For Multiple Users And Executions.

May 3, 2008

basically, is it inefficient to open and close a data connection everytime data needs to be retrieved or is there a way for a user to use the same conenction over multiple pages orfor multiple users to share one global data connection I just wanted to know this before I built a site where data could be constantly being pulled or put into the database.It would be easier to just keep opening and closing the connection since it just means pasting in the small chunk of code I use to do that where I need it. I hear speak of connections sometimes not closing etc and wonder if that is an issue then if you are opening and closing too many of them. I am using SqlConnection SqlCommand and SqlDatareader , my code works, just wondering if my approach lacked scalability before I find out too late and have to rewrite everything . Jim

View 11 Replies View Related

I Need To Distribute An ODBC To Multiple Computers.....

Nov 2, 2007

I have an ODBC setting on my computer that I need to distribute to numerouse other computers throughout the company. What is the best way to do this? I exported mine and did an import on another computer, it showed up in the HKLM registry key, but did not show up in the ODBC system settings in control panel. Am I missing something? Any ideas?

View 1 Replies View Related

T-SQL (SS2K8) :: Multiple Updates Using CTE?

Jul 23, 2014

Writing this code using a CTE or any other method which do not use a table variable or a temp table as the users do not permissions to run this code from the application because of their roles which are required for creating and dropping the temp tables. I replaced table variable in place of Temp tables however the query never completes, probably because of the number of rows.

SELECT A.[Entry No_], A.[G_L Account No_],A.[Gen_ Prod_ Posting Group],A.[Document No_],A.[Posting Date]
INTO temp1
FROM [G_L Entry] as A ,[G_L Account] as B
where A.[G_L Account No_]= B.[No_]
SELECT VE.[Entry No_],VE.[Document No_],VE.[Posting Date],VE.[Gen_ Prod_ Posting Group] , GLILER.[G_L Entry No_]
INTO temp2

[code]....

View 4 Replies View Related

T-SQL (SS2K8) :: Row Into Multiple Column

Sep 1, 2015

Create table control_Total ( Filename varchar(1000) )

insert into control_Total values('PCH123_TLNX.account.TUED.20150831.txt Bytes:645 Records:4')
insert into control_Total values('PCH123_TLNY.account.TWED.20150831.txt Bytes:1920 Records:12')

how can I get output like this :

FileName RecordCountBytes
PCH123_TLNX.account.TUED.20150831.txt4645
PCH123_TLNY.account.TWED.20150831.txt121920

View 7 Replies View Related

T-SQL (SS2K8) :: Selecting Top 1 From Multiple Ranges?

Mar 28, 2014

I have data like the following:

ID COUNTER DATA
1 10 BLAH
1 20 BLAH
2 10 BLAH
3 10 BLAH
2 20 BLAH
2 30 BLAH

What I want to return is:

1 20 BLAH
2 30 BLAH
3 10 BLAH

I want the top 1, having the highest counter from each ID. This is a highly simplified version of that I am pulling which also is between a date range, but same principle.

IE: SELECT * FROM Table WHERE ID in (SELECT DISTINCT ID FROM Table WHERE Date BETWEEN <date> AND <date>

I'd rather keep it in one statement if possible, but if I have to do it in multiple passes then so be it.

View 6 Replies View Related

T-SQL (SS2K8) :: How To Get One Row On A Table With Multiple Records

Apr 24, 2014

I have a table called TBLCataloghi

I have multiple records with colunms codpro and codcat equal

They differ only by a date called catalog.datfin

I'd like to select all rows but with the same codpro,codcat, obtaining ONLY the row with MIN () field datfin

Field datfin is a date..

Ex. codpro = 'PIPPO'
codcat = 'MK'
DATFIN = 01/01/2010

codpro = 'PIPPO'
codcat = 'MK'
DATFIN = 10/07/2014

I'd like to read both records but in SELECT obtain only the record with datfin MIN (01-10-2010)

I did the query but i was not able to do nothing of good. I obtain all times both records...

SELECT catalog.codpro AS CodProdotto,
catalog.codcat AS CodiceCatalogo,
MIN(catalog.datfin)

FROM pub.catalog

WHERE catalog.codcat = 'MK'

GROUP BY catalog.codpro,catalog.codcat ,catalog.datfin

View 2 Replies View Related