Data Access In A Variable
Sep 12, 2007
Hello Friends
i have one problem, i have to need fatch the data from database .
in the web form i take three grid view and i put the query "Select Top 1 coloum1 from tanlename order by newid()" .
when the data came from database there is no sequence series . so i want to take fatch the data from database in variable like int a , b , c and call the data in those variable
and put up in the feild
example :- welcome to Mr "Data call from data base (1) " how are you "Data call from data base (2)" bbye and "Data call from data base (3)"
Answer "- welcome to Mr "ASHWANI" how are you FINE OR NOT" bbye and "TAKE CARE"
there is all capslock on value came from databse and these value in the web page store in a variable
like int a ;
int b;
int c;
please help me please i have a huge problem
Ashwnai
View 1 Replies
ADVERTISEMENT
May 28, 2015
I am using C# in Visual Studio 2008 and remote database as sql server 2008 R2. I want to read remote database table's field value and i have to move that read value to string variable. how to do it.
And my code is :
string sql = "Select fldinput from tmessage_temp where fldTo=IDENT_CURRENT('tmessage_temp')";
SqlCommand exesql = new SqlCommand(sql, cn);
exesql.CommandType = CommandType.Text;
SqlDataReader rd1 = default(SqlDataReader);
rd1 = exesql.ExecuteReader();
View 6 Replies
View Related
Apr 3, 2007
Thanks for any one can give me a help.
I am try to transfer some tables data from one database server into another database server. I create a package in SSIS, and I use a variable to pass each table name. In Data flow, I use a OLEDB Source, but I cannot set the Data access mode to Table name or view name variable. Ever time, I will get this following error info "===================================
Error at Data Flow Task [OLE DB Source [31]]: A destination table name has not been provided.
(Microsoft Visual Studio)
===================================
Exception from HRESULT: 0xC0202042 (Microsoft.SqlServer.DTSPipelineWrap)
------------------------------
Program Location:
at Microsoft.SqlServer.Dts.Pipeline.Wrapper.CManagedComponentWrapperClass.ReinitializeMetaData()
at Microsoft.DataTransformationServices.DataFlowUI.DataFlowComponentUI.ReinitializeMetadata()
at Microsoft.DataTransformationServices.DataFlowUI.DataFlowAdapterUI.connectionPage_SaveConnectionAttributes(Object sender, ConnectionAttributesEventArgs args)".
Some one can tell me what is the reason, or give me some examples.
Thanks in advance.
View 7 Replies
View Related
Sep 22, 2015
I'm trying to re-write my database to de-couple the interface (MS Access) from the SQL Backend. As a result, I'm going to write a number of Stored Procedures to replace the MS Access code. My first attempt worked on a small sample, however, trying to move this on to a real table hasn't worked (I've amended the SP and code to try and get it to work on 2 fields, rather than the full 20 plus).It works in SQL Management console (supply a Client ID, it returns all the client details), but does not return anything (recordset closed) when trying to access via VBA code.The Stored procedure is:-
USE [VMSProd]
GO
/****** Object: StoredProcedure [Clients].[vms_Get_Specified_Client] Script Date: 22/09/2015 16:29:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
[code]....
View 4 Replies
View Related
Jun 30, 2015
I have a client who has SSMS installed on her laptop. She is able to connect to the SQL server via SSMS in the office and query data on the server.
She needs to be out of site often and doesn't have internet access. She asks if the data tables can be "backed up" or saved on her laptop, so she can look at them without worrying connecting to the server. I am not sure if this can be achieved, as SSMS is built for accessing a server, not a desktop. Myself never have this need. If I really need it, I would go to Microsoft Access and create an ODBC connection to the datatables. But this client thinks that Microsoft Access is beneath her.
View 4 Replies
View Related
Jul 20, 2015
HowTo: Import data to MS SQL 2008 from password protected Access DB ?
View 2 Replies
View Related
Mar 21, 2002
I have a VBscript below which works fine, which creates a unique file name for a text source and creates it just fine. What I need is to use that file name in an insert statement in TSQL to load a record to a transaction table. How do I utilize this variable to do this?
Thanks
Function Main()
Dim oConn, sFilename
' Filename format - exyymmdd.log
sFilename = "ex" & Right(Year(Now()), 2)
If Month(Now()) < 10 Then sFilename = sFilename & "0" & _
Month(Now()) Else sFilename = sFilename & Month(Now())
If Day(Now()) < 10 Then sFilename = sFilename & _
"0" & Day(Now()) Else sFilename = sFilename & Day(Now())
sFilename = DTSGlobalVariables("LogFilePath").Value & _
sFilename & ".log"
Set oConn = DTSGlobalVariables.Parent.Connections("Text File (Destination)")
oConn.DataSource = sFilename
' oConn.DataTarget = sFilename
Set oConn = Nothing
Main = DTSTaskExecResult_Success
End Function
View 3 Replies
View Related
Dec 2, 2007
I have a master package which calls two package one after an other.
1) I will be passing parameters to master package using dtexec with /set property to set the variables in the master package. I would like to know how can child package access these variables.
View 1 Replies
View Related
Aug 29, 2007
Hello,
I have a package variable called LatestTableFileDate that is typed to DateTime. I have an Execute SQL Task that executes the following SQL statement against an MS Access 2000 database table through the OLEDB Jet provider;
SELECT MAX(FileDate) AS MaxFileDate
FROM CollectionData_Access
The statement parses fine. At the moment, the returned value is NULL. I have 'Result Set' set to 'Single Row'. The FileDate column in the Access table is 'Date/Time', and the Format is 'Short Date'.
In the result set properties, I attempt to set the result to the LatestTableFileDate user variable. When I run the task, the task fails, and the following appears on the 'Progress' tab;
[Execute SQL Task] Error: An error occurred while assigning a value to variable "LatestTableFileDate": "Unsupported data type on result set binding 0.".
I searched the forum for this problem, and didn't find anything. Do I need to convert the date in MS Access to a string and set the package variable to a string type, or is there some other way to handle this?
Thank you for your help!
cdun2
View 16 Replies
View Related
Mar 13, 2008
Hi
How to use a global variable of a package inside oledb command
Scenario:
Glb_Rowcount Variable
I need to use this variable value inside oledb command.
P.S: No use of stored procedures and no script component
View 4 Replies
View Related
Sep 30, 2015
I am having trouble finding the correct syntax to access a variable. I have a variable defined in the Variables window: The variable name is formatedDate. The DataType is String.
I am successfully setting the value of the variable in a Execute SQL Task. The SQL is as follows:
SELECT LEFT(CONVERT(VARCHAR, MAX(ReportDate), 120), 10) as formatedDate
from DimReportDates
The Result Set is set to “Single Row” and properly set up.
No problem so far. I can see with a watch that the variable has the correct value, something like:
‘2015-09-30’
Now, in a subsequent step, a Data Flow Task, I want to access the variable. Actualy it is in the SQL statement of a OLE DB source in the Data Flow… I have the following:
Declare @sDate smalldatetime
SELECT @sDate = xxxxx
I have tried several things substituting xxxxx above, but nothing seems to work. One variation was
@[User::formatedDate])
Another was
((DT_WSTR, 10) @[User::formatedDate]).
I think I’m close, but just can’t get it. What is the correct syntax.
View 4 Replies
View Related
Apr 28, 2007
Hi!
I have an sproc that gives an xml result set. I want to save this to a file but by using ssis and script task. Now, I do exec usp_myProc in execute sql task and get the result as xml. (I can get it as result set too but niether has worked). Now I pass this variable to my script task User::XRset.
In the script task I create a file and then I want to write xml result of User::XRset to my file. I am at loss.
View 4 Replies
View Related
Sep 17, 2015
I have created some packages in visual studio and run the packages from there and they all run fine. I have then imported the THEM in SQL Integration services Catalogues and run the packages individually and they all run successfully.When I tried to create the job to run the packages in a scheduled job I get the following error messages: Backup database error: failed to lock variable "RunID" for access with error 0xc001001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the variable collection on a container during execution of the package and the variable is not there.....
Backup database error. The execute method on the task. Backup database returned code 0x80131904 (Login failed for user 'loginname' The execute method must succeed and indicate the result using an "out" parameter.
View 0 Replies
View Related
Dec 31, 2006
I have a raw file destination and am using a variable to store the filename. In an earlier task, I create the value in the variable. User:Filename ... set to C:Test.txt.
When I run the package, I get the "Error: 0xC0202070 at DFT Tekelec Call Events, RFD Tekelec [1365]: The file name property is not valid. The file name is a device or contains invalid characters". error. I then set a breakpoint to examine my variables on the DataFlow pre-execute event and found my variable showing the value "C:\Text.txt" ... so apparently XMLA is adding the escape character when it stores the value in the variable but not retracting it when it uses the value as the filename.
What am I missing? Can I not use pathing in the variable? And if that's the case, how do I specify a path. Went back through my Rational Guide to Scripting SSIS but did not find this addressed specifically ... my second option being build the fiilename by script and set the raw file destination property directly via script.
View 3 Replies
View Related
Sep 5, 2007
Has anyone encountered this before?
My colleague opens up an SSIS package with variables, many of which use expressions built with the Expression Builder. She cannot bring up the Expression Builder by clicking the ellipsis (...) in the "Expression" Property of the Property Window, regardless of whether "EvaluateAsExpression" is set to True or False.
I can open up this same package and use the Expression Builder just fine. Any ideas?
Apologies if this has been answered already, I did many searches to try to find the solution before posting here.
Thanks much!
Brian Pulliam
View 14 Replies
View Related
Jun 10, 2015
I have recently upgraded to SQL2014 on Win2012. The Access front end program works fine.
But, previously created Excel reports with built in MS Queries now fail with the above error for users with MS 2013. The queries still work for users still using MS 2007.
I also cannot create any new queries and get the same error message. If I log on as myself on the domain to another PC with 2007 installed it works fine, so I don't think it is anything to do with AD groups or permissions.
View 6 Replies
View Related
Nov 12, 2015
We need to insert data/rows from a SQL Server 2014 database into MS Access database. The problem is, there are so many columns (100+) in the table and there are so many insert transactions of this kind (from different tables) that it is not very easy to write the code in VB.NET that lists all column names.
Both the Access and SQL Server tables have the same number of columns and the equivalent data types, so inserting is not really the problem. It's just that is there a way to do an insert statement in T-SQL that does not name all the columns?
View 3 Replies
View Related
Oct 30, 2007
Hi guys,
I've been developing desktop client-server and web apps and have used Access and SQL Server Standard most of the time.
I'm looking into using SQL CE, and had a few questions that I can't seem to get a clear picture on:
- The documentation for CE says that it supports 256 simultaneous connections and offers the Isolation levels, Transactions, Locking, etc with a 4GB DB. But most people say that CE is strictly a single-user DB and should not be used as a DB Server.
Could CE be extended for use as a multi-user DB Server by creating a custom server such as a .NET Remoting Server hosted through a Windows Service (or any other custom host) on a machine whereby the CE DB would run in-process with this server on the machine which would then be accessed by multiple users from multiple machines??
Clients PCs -> Server PC hosting Remoting Service -> ADO.NET -> SQL CE
- and further more can we use Enterprise Services (Serviced Components) to connect to SQL CE and further extend this model to offer a pure high-quality DB Server?
Clients PCs -> Server PC hosting Remoting Service -> Enterprise Services -> ADO.NET -> SQL CE
Seems quite doable to me, but I may be wrong..please let me know either ways
Thanks,
CP
View 3 Replies
View Related
Nov 18, 2015
In my ssis 2012 package, I have a 'object' type variable with some table like records. I want to do some SQL operations like insert/update on the records in another table based on this 'Object' type variable records. Basically I want to use a MERGE statement with another physical table with the records in the 'Object' type variable.how to map/use the Object type variable in Execute sql task.I am not good in script task. How to utilize this Object variable in a Execute sql task?
View 9 Replies
View Related
Apr 22, 2015
When i am trying to start our hospital software based on SQL server 2000, it shows Following Error.Search Condition is not valid, (DBNETLIB) Connection Open (connect()). SQL server does not exist or excess denied. Due to Fetch data.I run our software in Windows 8.1, while it smothly runs in previous version of Windows XP and 7.
View 2 Replies
View Related
Feb 23, 2008
RE: XML Data source .. Expression? Variable? Connection? Error: unable to read the XML data.
I want my XML Data source to be an expression as i will be looping through a directory of xml files.
I don't see the expression property or the connection property??
I tried setting the XMLData property to @[User::filename], but that results in:
Information: 0x40043006 at Load XML Files, DTS.Pipeline: Prepare for Execute phase is beginning.
Error: 0xC02090D0 at Load XML Files, XML Source [108]: The component "XML Source" (108) was unable to read the XML data.
Error: 0xC0047019 at Load XML Files, DTS.Pipeline: component "XML Source" (108) failed the prepare phase and returned error code 0xC02090D0.
Information: 0x4004300B at Load XML Files, DTS.Pipeline: "component "OLE DB Destination" (341)" wrote 0 rows.
Task failed: Load XML Files
Information: 0xC002F30E at Bad, File System Task: File or directory "d:jcpxmlLoadjcp2.xml.bad" was deleted.
Warning: 0x80019002 at Package: The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "Package.dtsx" finished: Failure.
The program '[3312] Package.dtsx: DTS' has exited with code 0 (0x0).
Thanks for any help or information.
View 3 Replies
View Related
Feb 2, 2007
I simply made my script task (or any other task) fail
In my package error handler i have a Exec SQL task - for Stored Proc
SP statement is set in following expression (works fine in design time):
"EXEC [dbo].[us_sp_Insert_STG_FEED_EVENT_LOG] @FEED_ID= " + (DT_WSTR,10) @[User::FEED_ID] + ", @FEED_EVENT_LOG_TYPE_ID = 3, @STARTED_ON = '"+(DT_WSTR,30)@[System::StartTime] +"', @ENDED_ON = NULL, @message = 'Package failed. ErrorCode: "+(DT_WSTR,10)@[System::ErrorCode]+" ErrorMsg: "+@[System::ErrorDescription]+"', @FILES_PROCESSED = '" + @[User::t_ProcessedFiles] + "', @PKG_EXECUTION_ID = '" + @[System::ExecutionInstanceGUID] + "'"
From progress:
Error: The Script returned a failure result.
Task SCR REIL Data failed
OnError - Task SQL Insert Error Msg
Error: A deadlock was detected while trying to lock variable "System::ErrorCode, System::ErrorDescription, System::ExecutionInstanceGUID, System::StartTime, User::FEED_ID, User::t_ProcessedFiles" for read access. A lock could not be acquired after 16 attempts and timed out.
Error: The expression ""EXEC [dbo].[us_sp_Insert_STG_FEED_EVENT_LOG] @FEED_ID= " + (DT_WSTR,10) @[User::FEED_ID] + ", @FEED_EVENT_LOG_TYPE_ID = 3, @STARTED_ON = '"+(DT_WSTR,30)@[System::StartTime] +"', @ENDED_ON = NULL, @message = 'Package failed. ErrorCode: "+(DT_WSTR,10)@[System::ErrorCode]+" ErrorMsg: "+@[System::ErrorDescription]+"', @FILES_PROCESSED = '" + @[User::t_ProcessedFiles] + "', @PKG_EXECUTION_ID = '" + @[System::ExecutionInstanceGUID] + "'"" on property "SqlStatementSource" cannot be evaluated. Modify the expression to be valid.
Warning: The Execution method succeeded, but the number of errors raised (4) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
And how did I get 4 errors? - I only set my script task result to failure
View 11 Replies
View Related
Feb 26, 2015
I am writing a query to return some production data. Basically i need to insert either 1 or 2 rows into a Table variable based on a decision as to does the production part make 1 or 2 items ( The Raw data does not allow for this it comes from a look up in my database)
I can retrieve all the source data i need easily but when i come to insert it into the table variable i need to insert 1 record if its a single part or 2 records if its a twin part. I know could use a cursor but im sure there has to be an easier way !
Below is the code i have at the moment
declare @startdate as datetime
declare @enddate as datetime
declare @Line as Integer
DECLARE @count INT
set @startdate = '2015-01-01'
set @enddate = '2015-01-31'
[Code] .....
View 1 Replies
View Related
Jul 24, 2015
When I execute the below stored procedure I get the error that "Arithmetic overflow error converting expression to data type int".
USE [FileSharing]
GO
/****** Object: StoredProcedure [dbo].[xlaAFSsp_reports] Script Date: 24.07.2015 17:04:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
[Code] .....
Msg 8115, Level 16, State 2, Procedure xlaAFSsp_reports, Line 25
Arithmetic overflow error converting expression to data type int.
The statement has been terminated.
(1 row(s) affected)
View 10 Replies
View Related
Feb 5, 2007
is there a step by step paper to get there? here is what i need to consider. I Iwill have many customers that will need their own set of records and access pages "branded for their company" each customer will have many clients. I am hosting this application on a windows 2003 server with SQL 2005 server enterprise.
I am using windows authentication, I have created a username in windows, then i added the windows user in SQL management studio in security, granted "DB Read" and "DB write" and again under the database security tab. still from the web authentication fails. i must be nissing a step or two?
I expect to set up a username for each database as i setup new customers.
View 1 Replies
View Related
Jun 25, 2007
I have a customer-supplied stored procedure that returns a single row containing XML using the FOR XML EXPLICIT syntax against a SQL 2000 database.
When I run the proc in SQL Mgt console and save the XML to a file, I can read it in my SSIS package using the XML Source with no problem. I built the XSD from this file.
However, when I execute the proc within my package using the Exec SQL task using the XML Resultset option and saving the result to a String variable with package scope, and then try to read the XML into the XML Source from the variable, I get nothing. My data flow task runs to completion but it has no data.
The sproc seems to run fine. Is there a way I can look at the variable while the package is running to determine if it does have data? Anything else here I might be missing?
One other note, as a debugging measure, I put the SQL to execute the sproc in an OLE DB data source in my data flow task and tried to preview the data. When I did, all I got was "System.byte[]"...no XML. This is the same command I ran in the query window with no problems (copy/paste). I don't know if this is telling me I have a data-type issue or just if what I'm trying to do isn't allowed.
Any help would be appreciated.
View 3 Replies
View Related
Nov 1, 2006
HiI'm having problems following the tutorial on creating a data access layer - http://www.asp.net/learn/dataaccess/tutorial01cs.aspx?tabid=63 - when I try to compile in Visual Studio 2005 I get namespace could not be found. I followed exactly the tutorial - I created a dataset and added this code in my aspx page. <asp:GridView ID="GridView1" runat="server" CssClass="DataWebControlStyle"> <HeaderStyle CssClass="HeaderStyle" /> <AlternatingRowStyle CssClass="AlternatingRowStyle" />In my C# file I added these lines... using NorthwindTableAdapters; <<<<<this is the problem - where does this come from? protected void Page_Load(object sender, EventArgs e) { ProductsTableAdapter productsAdapter = new ProductsTableAdapter(); GridView1.DataSource = productsAdapter.GetProducts(); GridView1.DataBind(); }Thanks in advance
View 6 Replies
View Related
Apr 23, 2015
I'm currently working on a BI architecture for a customer, and consider to propose the Power BI data catalog as a data distribution layer. The customer will use Power BI, but also has other BI tools.
Are data sets in the data catalog available to other clients than Power Query alone? E.g. are there OData feed endpoints available? If not, what would be the best way to give other tools access to the data?
View 3 Replies
View Related
Jun 9, 2013
I have two database(MYDB1 , MYDB2) on two different server's(SERVER1 , SERVER2) . I want to create an store procedure in MYDB1 on SERVER1 and get some data from a table of MYDB2 on SERVER2. How can i do this?
View 5 Replies
View Related
Jul 13, 2015
I'm using a legacy application built using VB5 and SQL Server 7. After recompiling it, and putting the database in SQL Server 2012. I want to access the current login user using the SQL function SUSER_SNAME().
This is the code.
Set rdoRes = goDatabase.Connection.OpenResultset("select suser_sname()")
But I'm unable to get the current user login in the application. If I write any other SQL statement instead of this, then it runs. But only this statement is not running. Is there any security reasons for this?
View 8 Replies
View Related
Sep 9, 2015
I have a table that is returning rows from a table query. It seems I have done it before but I cannot seem to get the right procedure to obtain the values. I will paste in the code below in which you will see my bad attempts at accomplishing what I need.
Dim uid As String
Dim pw As String
Dim em As String, fn, ln, mi As String
Dim par As String
Dim Field, n, j As Integer
Dim JJ As Integer
[code]...
View 3 Replies
View Related
May 28, 2015
how I can load the CSV file data into the sql server table. I know there are ways like bulk insert and other to load the csv file data into the table. But in my case the table doesn't exist and has to be created at the run time. With simple insert in temp table we do like select * into #temp from tablename and that creates the temp table. So. I need something like that which create the temp table and load the data into it. because the CSV file would have different number of columns and names so I can not create the table structure in advance. I have to create the table at run time.
View 3 Replies
View Related
