I am designing a SSIS package (source=Flat files,, destination =OLEDB) which is executing fine on my system but when I try to execute it on Server 2003 (x64)
it says Error: The AcquireConenction method call to connection manager "name of conn" failed with error code 0xC0014019 and
it says can't Aquire Connections because OfflineMode is true, while as I checked several times while saving the package it is set to false...
Hi I'm trying to have 2 datareaders open at the same time with MultipleActiveResultSets=True but still getting an error saying There is already an open DataReader associated with this Command which must be closed first. Do you see what I might be doing wrong? Thanks.
<configuration> <connectionStrings> <add connectionString="user id=username;data source=servername;persist security info=True;initial catalog=corporate_mel;password=password;MultipleActiveResultSets=True;" name="corporate_mel" providerName="System.Data.SqlClient" />
Dim strConnection As String Dim sqlConn As SqlConnection strConnection = System.Configuration.ConfigurationManager.ConnectionStrings("corporate_mel").ConnectionString sqlConn = New SqlConnection(strConnection) sqlConn.Open()
This is the actual code where I'm trying to use 2 datareaders:
' Retrieve Data from database based on selections chosen in ListBox
Dim cmdCommittee As New SqlCommand("Select * from committees_tbl where committee_id in" & _
"(" & strCommitteesRemoveLast & ") order by committee_name", sqlConn)
' setup a datareader
Dim drCommittee As SqlDataReader = cmdCommittee.ExecuteReader()
' Loop through datareader and insert rows
' into the xref_person_committees_tbl
While drCommittee.Read()
Dim strCommitteeName As String = drCommittee("committee_name") 'retrieve committee_name from datareader
' Create a sql string
Dim strAddCommittee As String = String.Empty
strAddCommittee = "Insert into xref_person_committees_tbl (committee_name) values ('" & strCommitteeName & "')"
'Response.Write(strAddCommittee & "<br>")
' Create a sql command to process the insert
Dim sqlAddCommittee As New SqlCommand(strAddCommittee, sqlConn)
Dim drNewCommittee As SqlDataReader = sqlAddCommittee.ExecuteReader()
End While
I have a cube that has a Dimension set up with several values some of which are bools. While Browsing in Excel or SSMS, two new values, when used as a filter shows (All) (Blank) and (True) for selections instead of (All) (True) and (False).
View 2 Replies View RelatedI have created a SSIS package to import data from DBF files using a OLE DB Jet 4 driver, which works very well in Visual Studio, however doesn't work via a SQL Job. It states the the path isn't valid.
The file path in the connection manage is \servernamedbf (dbf being a shared folder I have permissions and security to)
The server is on another domain, which is trusted. I added the server's IP address to the host file and through windows explorer can navigate to the dbf folder with out a password prompt.
Why can't I get this to work???
Cheers
Hi Everyone,
I am looking for some help, I have created a simple SSIS package that takes the contents of a view on the Server and outputs it to a delimited flat file. This flat file is moved from its location on the server to our file print server. This package uses a config file that contains the necessary passwords. When run in the visual studio it runs without error.
However when run in a SQL Server Agent Job it fails on this step -
Code: 0xC020801C Source: Data Flow Task GET DATA FROM LIVE [1] Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "ServerName.DBName.SQLServerAuth" fail.
I have researched the error and I have found that it doenst have the necessary permissions, however I have another package doing the same thing using the same config file that runs with error on a SQL Job. I do not get why it would differ when the jobs are identical?
Any help would be appreciated.
Paul
I have a package that uses a for loop to iterate through an unknown amount of excel files and pull their data into a table. However, there will be cases when the file is corrupted or has some sort of problem so that either the transformation will fail or the excel data source will fail with an oledb connection error.
Could anyone suggest a clean way to trap these errors? Specifically, the "Cannot Aquire Connection from Connection Manager", which is the excel connection.
Thanks,
John T
Here is the situation i am stuck with, see the example first and below explained the problem:
-- 'SESSION A
create table foo (
id integer,
pid integer,
data varchar(10)
);
begin transaction
insert into foo values ( 1, 1, 'foo' )
insert into foo values ( 2, 1, 'bar' )
insert into foo values ( 3, 1, 'bozo' )
insert into foo values ( 4, 2, 'snafu' )
insert into foo values ( 5, 2, 'rimrom' )
insert into foo values ( 6, 2, 'blark' )
insert into foo values ( 7, 3, 'smeg' )
commit transaction
create index foo_id_idx on foo ( id )
create index foo_pid_idx on foo ( pid )
begin transaction
insert into foo values ( 9, 3, 'blamo' )
-- 'SESSION B
begin transaction
select id, data from foo with ( updlock, rowlock ) where id = 5;
-- Problem:
-- Uncommitted transaction in session A, with insert into table FOO, aquires lock on index foo_pid_idx which BLOCKS select with ( updlock, rowlock ) in session B.
-- Insert should aquire only exclusive rowlock. Why does insert block select with ( updlock, rowlock )?
Appreciate your help,
Rajesh.
Hi there,
Here we have got a
asp.net application that was developed when database was
sitting on SQL server 6.5. Now client has moved all of their databases
to SQL server 2000. When the database was on 6.5 the previous
development team has used oledb connections all over. As the databases
have been moved to SQL server 2000 now i am in process of changing the
database connection part. As part of the process i have a login
authorization code.
Private Function Authenticate(ByVal username As String, ByVal password As String, ByRef results As NorisSetupLib.AuthorizationResult) As Boolean
Dim conn As IDbConnection = GetConnection()
Try
Dim cmd As IDbCommand = conn.CreateCommand()
Dim sql As String = "EDSConfirmUpdate" '"EDSConfirmUpdate""PswdConfirmation"
'Dim cmd As SqlCommand = New SqlCommand("sql", conn)
cmd.CommandText = sql
cmd.CommandType = CommandType.StoredProcedure
NorisHelpers.DBHelpers.AddParam(cmd, "@logon", username)
NorisHelpers.DBHelpers.AddParam(cmd, "@password", password)
conn.Open()
'Get string for return values
Dim ReturnValue As String = cmd.ExecuteScalar.ToString
'Split string into array
Dim Values() As String = ReturnValue.Split(";~".ToCharArray)
'If the return code is CONTINUE, all is well. Otherwise, collect the
'reason why the result failed and let the user know
If Values(0) = "CONTINUE" Then
Return True
Else
results.Result = Values(0)
'Make sure there is a message being returned
If Values.Length > 1 Then
results.Message = Values(2)
End If
Return False
End If
Catch ex As Exception
Throw ex
Finally
If (Not conn Is Nothing AndAlso conn.State = ConnectionState.Open) Then
conn.Close()
End If
End Try
End Function
''' -----------------------------------------------------------------------------
''' <summary>
''' Getting the Connection from the config file
''' </summary>
''' <returns>A connection object</returns>
''' <remarks>
''' This is the same for all of the data classes.
''' Reads a specific
connection string from the web.config file for the service, creates a
connection object and returns it as an IDbConnection.
''' </remarks>
''' -----------------------------------------------------------------------------
Private Function GetConnection() As IDbConnection
'Dim conn As IDbConnection = New System.Data.OleDb.OleDbConnection
Dim conn As IDbConnection = New System.Data.SqlClient.SqlConnection
conn.ConnectionString = NorisHelpers.DBHelpers.GetConnectionString(NorisHelpers.DBHelpers.COMMON)
Return conn
End Function
in the above GetConnection() method i
have commented out the .net dataprovider for oledb and changed it to
.net dataprovider for SQLconnection. this function works fine. But in
the authenticate method above at the line
Dim ReturnValue As String = cmd.ExecuteScalar.ToString
for some reason its throwing the below error.
Run-time exception thrown : System.Data.SqlClient.SqlException - @password is not a parameter for procedure EDSConfirmUpdate.
If i comment out the
Dim conn As IDbConnection = New System.Data.SqlClient.SqlConnection
and uncomment the .net oledb provider,
Dim conn As IDbConnection = New System.Data.OleDb.OleDbConnection
then it works fine.
I also have changed the webconfig file as below.
<!--<add
key="Common" value='User ID=**secret**;pwd=**secret**;Data
Source="ESMALLDB2K";Initial Catalog=cj_common;Auto
Translate=True;Persist Security Info=False;Provider="SQLOLEDB.1";'
/>-->
<add key="Common" value='User ID=**secret**;pwd=**secret**;Data Source="ESMALLDB2K";Initial Catalog=cj_common;' />
Please help. Thanks in advance.
Microsoft E-Learning products are currently available for purchase only within North America at this time
Jezz, bad luck I am currently living in Spain...
I have a stored procedure that has a boolean (bit) field passed to it (@emailcontract). If a user checks the check box on the webform I would like my where to return only the records where the email_contract column is true. If they don't check the check box I would like it to return records where email_contracts is true or false.
What would my where cluse look lile for this?
I am designing a package performing some data imports from a text file to some tables, passing by a temporary table.
My specific requirements are:
initially the package checks if the input file exists, if it doesn't it will not continue.I have implemented this with a Script Task, and if the
file doesn't exist I fail the all package (Dts.TaskResult =
Dts.Results.Failure). Is this the right way?
after copying the data into a temporary table, it checks via SQL if some conditions are verified, if not it will not continue.I would have liked to do it via an SQL Task, but I don't know how to stop the package if the conditions are not what expected. Anybody can help?
Thank you.
All
Can I ask what data type i use for a true false response (Boolean) in my table?
Thanks
Gibbo
This is stupid, I used to be able to do this all the time by mistake now I can't do it on purpose
I want to be able to return a full list of matching records when only one is true
LikeRow 1, ID_1, falseRow 2, ID_1, falseRow 3, ID_1, trueRow 4, ID_2, falseRow 5, ID_2, trueRow 6, ID_2, false
I currently getRow 3, ID_1, trueRow 5, ID_2, true
Hi all, I am trying to access a sql database and if the userid exists in the database to the let me query another statement within an IF block to check another statement within the data, however I cannot seem to get it to work. I need something like sql.row.count != 0 within the 2nd IF statement below. What can I do?Thanks in advance.Jason using System;using System.Configuration;using System.Data;using System.Data.SqlClient;using System.Linq;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.HtmlControls;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Xml.Linq;public partial class _Default : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { } protected void btnStart_Click(object sender, ImageClickEventArgs e) { if (Session["userid"] == null) { Response.Redirect("accessdenied.aspx"); } else { //Response.Redirect("page1.aspx"); SqlConnection objConnect = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["MyConnection"].ConnectionString); objConnect.Open(); SqlCommand cmd = new SqlCommand("SELECT user_id FROM users WHERE user_id = '" + Session["userid"] + "'"); if (cmd == true) { Response.Redirect("page5.aspx"); } objConnect.Close(); } }}
View 7 Replies View Relatedwhat does this imply Trusted_Connection=true parameter in the Connection String signify?
Secondly how can I make users with Windows Authentication Login to work in SQL Server Connection String?
Thanks in advance,
Neeraj.
hi
I just created a DB , & ran sp_dboption , it showed me that the trunc. log on chkpt. is true .....
What if i set the recovery model of this DB to full , would I be able to recover the DB to a specific point in time ,
or since trunc. log on chkpt. is true.. I would not be able to collec the transaction log backups ???
Cant understand this concept , need some link to read this stuff ....
Thanks
Hi,
I need to check the existence of a row in a table.
So i am using an if condition
like
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[CheckNOAStages]
@NOAID int,
@StageCode nchar(20)
AS
BEGIN
SET NOCOUNT ON;
Declare @Count int
Select @Count =Count(NOAId) from NOAStages where NOAID=@NOAID and StageCode=@StageCode
if (@Count>0)
Begin
return 1
end
else
begin
return 0
end
END
The stored proc is executing but on the Data Access Layer
I have this
Boolean exists = Convert.ToBoolean (Execute.ExecuteReader(spCollection, dbSQL));
Some how I am always getting false . How can I fix this?
Thanks
Hi,
we've got this problem with some particular jobs: they look as they
ran correctly, but actually they didn't made it all through their
duties.
The problem is that this job is calling a sequence of DTS, where there
is a DTS with an ActiveX control which modifies another DTS before
launching and some other tasks: the error happens there, .
Launched from the DTS we get the error, from the job no...any idea how
we could get the correct job information?
Thank you
Daniele
Hello
I am exporting an SQL Server table to a comma delimited text file. The values of Columns defined as Bit are exported as "True" or "False", but I would like that in the file appear 1 or 0 instead (with no surrounding double quotes). How can I acomplish that?
I tried using a Transformation and convert to single byte unsigned integer, but True values are exported as "255" and False values as "0". Why?
Thanks a lot.
Hello all,
I am trying to migrate date from Oracle 10g to SQL serve 2005 during the data transformation I get the following error
Messages
Warning 0x80202066: Source - SERVICE [1]: Cannot retrieve the column code page info from the OLE DB provider. If the component supports the "DefaultCodePage" property, the code page from that property will be used. Change the value of the property if the current string code page values are incorrect. If the component does not support the property, the code page from the component's locale ID will be used.
(SQL Server Import and Export Wizard)
I searched internet and solution seems to be setting AlwaysUseDefaultCodePath="TRUE"
But where do you do this ?
I found this too: It's on the Properties tab of the OLE DB Source in Custom Properties section.
But still do not know where to go to set this parameter
Please help
Hi there, I've tried googling this (and looking at the many questions on the forum :) but I've not managed to find a decent tutorial / guide that describes a method for using checkboxs to insert true/false flags in a MS SQL db. The db field i'm setting has type set to "bit", is this correct? And secondly (its been a long day!) I just cant figure out the code to assign the bit 1 or 0 / true or false. This is what I've got so far but it's not working........Function InsertProduct(ByVal prod_code As String, ByVal prod_name As String, ByVal prod_desc As String, ByVal prod_size As String, ByVal prod_price As String, ByVal prod_category As String, ByVal aspnet As Boolean) As Integer Dim connectionString As String = "server='server'; user id='sa'; password='msde'; Database='dbLD'" Dim sqlConnection As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection(connectionString) Dim queryString As String = "INSERT INTO [tbl_LdAllProduct] ([prod_code], [prod_name], [prod_desc], [prod_size], [prod_price], [prod_category],[aspnet]) VALUES (@prod_code, @prod_name, @prod_desc, @prod_size, @prod_price, @prod_category, @aspnet)" Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection) sqlCommand.Parameters.Add("@prod_code", System.Data.SqlDbType.VarChar).Value = prod_code sqlCommand.Parameters.Add("@prod_name", System.Data.SqlDbType.VarChar).Value = prod_name sqlCommand.Parameters.Add("@prod_desc", System.Data.SqlDbType.VarChar).Value = prod_desc sqlCommand.Parameters.Add("@prod_size", System.Data.SqlDbType.VarChar).Value = prod_size sqlCommand.Parameters.Add("@prod_price", System.Data.SqlDbType.VarChar).Value = prod_price sqlCommand.Parameters.Add("@prod_category", System.Data.SqlDbType.VarChar).Value = prod_category If chkAspnet.Checked = True Then sqlCommand.Parameters.Add("@aspnet","1") Else sqlCommand.Parameters.Add("@aspnet","0") End If Dim rowsAffected As Integer = 0 sqlConnection.Open Try rowsAffected = sqlCommand.ExecuteNonQuery Finally sqlConnection.Close End Try Return rowsAffected End Function Sub SubmitBtn_Click(sender As Object, e As EventArgs) If Page.IsValid then InsertProduct(txtCode.Text, txtName.Text, txtDesc.Text, ddSize.SelectedItem.value, ddPrice.SelectedItem.value, ddCategory.SelectedItem.value, aspnet.value) Response.Redirect("ListAllProducts.aspx") End If End SubAny help would be appreciated or links to tutorials.ThanksBen
View 2 Replies View RelatedWhen accessing a web application from an intranet....
And the web app tries to make a connection to a 'SQL Server' using the sqlconnection where does ASP.NET grab user credentials?
My webpage displays web security.principal.windows.getcurrent = domainUser (displays the correct information).
But the connection to sql says Null. Where is ASP.NET grabbing (retrieving) this information from?
Thanks,
I have this OR in a simple query:
Code:
DECLARE @searchString nvarchar(100)
SET @searchString = 'sample'
SELECT TOP(1) * FROM user
WHERE (user.identity LIKE @searchString OR
CHARINDEX(@searchString, user.firstname + ' ' + user.lastname) > 0)
PS: Handwritten...
If I pass in a searchString that matches the Identity and a different users lastname, this query will return the user with the lastname-match (which is wrong in my eyes, it should have matched the Identity first, then returned that row [Identity is a Primary key, indexed non-clustered]).
I've tried various things:
Removing LastName: (CHARINDEX(@searchString, user.firstname + ' ') > 0), then the returned row is from a matching identity, due to the lastname of a user was a match, but lastname was removed...so :P
Flipping the conditions around
Adding/removing paranthesis... without any luck.
Is there some option somewhere, to force the OR statement to return on the first true condition. I've always thought OR-statements (in a computer that is) breaked and returned true on the first true condition it found (from left to right, not bother to check the rest of the conditions...)?Or do I have to rewrite the query, with an IF, checking if the @searchString is a valid identity-format, if it is, query on the identity, if not query on the username...?
Hi all,
I have a database filled with contracts, suppliers and administrators who administrates those contracts.
I want to make a sproc that checks the difference between the expiration_date and the current date(in months). the sproc compares this output with a given period in the contracts-table. when the output <= the given period ---> send mail to the administrator with info about the contract. and that the contract will be expired in X months.
Having a Sproc that only gets the info from the tables and compares this info is no problem, but to let the sproc send an email to the admin whose email-adress also comes from a table is a little bit to tricky for me.
I have searched the internet but i can't figure it out.
If anyone knows where I can get more info about this subject please be so kind and let me know, or anything that could help me around for the moment.
PS. I'm using SQL server 2000
Thnx in advance
StylizIt
Sir
I want to Return 1 and 0 after update , delete , Insert statement
IF Records Effected Return 1 else return 0
Pls help me out .........Sir
Yaman
Hello SQL Server Experts,
I'm upgrading an application that uses a VBScript/ASP front-end and MS Access Backend. The application has many points expecting fields to be true or false..
E.g. SELECT * FROM MyTable WHERE Active=True
However, since SQL Server requires 0 or 1 for bit fields, this query keeps failing. SQL Server (2005 Express) thinks True is a column name and is not automatically converting "True" to 1.
It would be so difficult to re-write the application to do this at the VB level, is there a way to make SQL server do this conversion automatically ?
Thanks for any assistance available.
Sunny
We hear about catalog table in documentation but is this the same asdatabase schema?What is the definiation of catalog table?What does it pertain to?Thanks--Message posted via http://www.sqlmonster.com
View 1 Replies View RelatedI have multiple viewers on a web page and initialy would like AsyncRendering = true on all viewers, so I can have the loading message. Once the user drill-through, I hide all other viewers and change to AsyncRendering = false, so the autosizing will work.
But, with AsyncRendering = true (in code behind):
private void SetDominateViewerProperties(ReportViewer rptViewer)
{
rptViewer.AsyncRendering = false;
rptViewer.ShowToolBar = true;
rptViewer.ZoomPercent = 100;
rptViewer.Visible = true;
}
private void SetDefaultViewerProperties(ReportViewer rptViewer)
{
rptViewer.AsyncRendering = true;
rptViewer.ShowToolBar = true;
rptViewer.ZoomPercent = 100;
rptViewer.Visible = true;
}
I have only one viewer showing, even though, I can see in debug that all viewers are set to visible. In SetDefaultViewerProperties, I change rendering to false, I get multiple viewers. Are there combinations of property values I need to set?
I have a package that creates a recordset in a variable (Type=Object, Name=CountryTable). The recordset is then picked up in a Script Task and loaded into a table using this code:
Dim adp As New OleDb.OleDbDataAdapter
dt = New DataTable
adp.Fill(dt, Dts.Variables("CountryTable").Value)
It was working fine until I turned SaveCheckpoints ON. Now it does not load any rows into the dt table. The dataflow task with the recordset destination ('CountryTable' variable) reports 10 rows in the pipeline. If I turn SaveCheckpoints OFF, it fills the dt table OK. If it cannot fill the dt table because of SaveCheckpoints being ON, shouldn't it give an error message? Thanks.
Note: I have SP1 installed.
Requirement: Auto-sized report with toolbar, rendered in a ReportViewer in an ASP .NET 2 page.
As we all know the trick is SizeToReportContent = True AND AsynchRendering = False.
With these settings (and ShowToolBar = True) I am still getting scroll bars and no resizing!
I tried setting ShowToolBar=False and it worked! (but I have now lost the export capability).
When ShowToolBar=True the ReportViewer does not resize correctly (it seems to resize based on the width of the toolbar). It looks like the toolbar is rendered first and this width is used as the max. width for the SizeToReportContent.
Anyway my options are:
1) Toolbar "on" with weird resizing (ie scrollbars). This looks sloppy.
2) Toolbar off (no ability to export, which I need). Export is a requirement.
3) Hard code the size to something big enough to hold the report (welcome to 1986)
Anyone else see the same behavior or am I missing something?
I want to pass a single char to a query and use that to set two flags.
(
@ID int,
@AssessedID int,
@CompetencyID int,
@Status char,
@Creator int
)
AS
UPDATE P4_Assessment
SET P4_Cancelled_f = (@Status = ('C')),
P4_Competent_f = (@Status = ('P')),
P4_Date = getdate(),
P4_Creator = @Creator
WHERE P4_ID = @ID
I want to set the P4_Cancelled_f to true (if @Status = 'C') or false if it doesn't.
This sort of syntax is fine in C#, but fails in a query. I also tried using IN (@Status IN ('C'))
Is this sort of logic possible in TSQL or should I use two parameters and set them in my code as 1 or 0?
TIA
Hi all,I use the following connectionstring connect to sqlservserver=(local); database=mydata;Integrated Security=trueIf I tried browse the website in VS Web Developer 2005 environment, it's ok. After I compiled the site and access through IIS server, can not connect to sqlserver , I need to use login and password to connect. How can I connect wihtout using LogID and password.Thanks
View 2 Replies View Relatedhi, Can REPLACE( ) takes more takes more than 3 parameters ? if yes can you give me some example query ?
View 3 Replies View Related