Hello
I have a desktop in which i have installed perl. I have another machine with OS as windows server 2003 and install ms sql server 2005. My problem is that i want to send data from my desktop to the window server how do i do that.
And help please.
we have ODBC drivers to connect to SQL Server from a unix box.I looked at cspan.org for Perl DBD/DBI with sql server and Idon't see one. Is there a place from where I can downloadperl DBD/DBI for SQLServer.Thanks.
View 3 Replies View RelatedPlease help. I am at my wits end.
I have SQL Server 2000 on a Windows 2003 server. I have my database on a Nmade Instance . Named Pipes is disabled and only TCP/IP is enabled. Authentication is set to Windows only. The server is in Active Directory.
My web site is on a Windows 2000 server with IIS 5. I have installed the Client Network Utilities and set the alias to TCP/IP also. I can connect to the SQL server using the Query Analyzer.
I have downloaded and installed ActiveState's ActivePerl 5.6.1.638-MSWin32-x86.msi on both machines. I have also installed DBI and DBD::ODBC on both machine. Both machine have MDAC 2.8 installed.
The code I am using to connect is as follows:
my $user = 'user';
my $pass = 'password';
my $db = "database";
my $server = "servername\instancename";
my $ipadd = "xxx.xx.xx.xxx,xxxx";
my $driver = '{SQL Server}';
Option #1
$DSN = 'driver=$driver;Server=$server;uid=$user;pwd=$pass;database=$db;network=DBMSSOCN;address=$ipadd;';
$dbh = DBI->connect("dbi:ODBC:$DSN") or die 'Cannot Open SQL Server Database $DBI::errstr';
Option #2
$DSN = 'driver=$driver;Server=$server;uid=$user;pwd=$pass;database=$db';
$dbh = DBI->connect("dbi:ODBC:$DSN") or die 'Cannot Open SQL Server Database $DBI::errstr';
Option #3
$dbh = DBI->connect('dbi:ODBC:driver=$driver:dsn',$user,$pass) or die 'Cannot Open SQL Server Database $DBI::errstr';
Option #4
$dbh = DBI->connect("dbi:ODBC:database=$db;server=$server",$user,$pass) or die '$DBI::errstr';
From all that I have read and from previous scripts I have written, any of these should work, but I am getting the following error.
DBI connect('driver=$driver:dsn','user',...) failed: at C:cgi-binCGI.pl line xxx Cannot Open SQL Server Database $DBI::errstr at C:cgi-binCGI.pl line xxx.
Where xxx = the line number of the connection call.
All of my previous scripts have been written for forms where the web server(Apache), database(MySQL), and script(Perl CGI) are all on the same machine. I am not at all familiar with SQL Server, and am struggling to learn quickly. PLease any ideas would be greatly appreciated.
Thank you
LANAC
Please help. I am at my wits end. :confused:
I have SQL Server 2000 on a Windows 2003 server. I have my database on a Nmade Instance . Named Pipes is disabled and only TCP/IP is enabled. Authentication is set to Windows only. The server is in Active Directory.
My web site is on a Windows 2000 server with IIS 5. I have installed the Client Network Utilities and set the alias to TCP/IP also. I can connect to the SQL server using the Query Analyzer.
I have downloaded and installed ActiveState's ActivePerl 5.6.1.638-MSWin32-x86.msi on both machines. I have also installed DBI and DBD::ODBC on both machine. Both machine have MDAC 2.8 installed.
The code I am using to connect is as follows:
my $user = 'user';
my $pass = 'password';
my $db = "database";
my $server = "servername\instancename";
my $ipadd = "xxx.xx.xx.xxx,xxxx";
my $driver = '{SQL Server}';
Option #1
$DSN = 'driver=$driver;Server=$server;uid=$user;pwd=$pass ;database=$db;network=DBMSSOCN;address=$ipadd;';
$dbh = DBI->connect("dbi:ODBC:$DSN") or die 'Cannot Open SQL Server Database $DBI::errstr';
Option #2
$DSN = 'driver=$driver;Server=$server;uid=$user;pwd=$pass ;database=$db';
$dbh = DBI->connect("dbi:ODBC:$DSN") or die 'Cannot Open SQL Server Database $DBI::errstr';
Option #3
$dbh = DBI->connect('dbi:ODBC:driver=$driver:dsn',$user,$pass) or die 'Cannot Open SQL Server Database $DBI::errstr';
Option #4
$dbh = DBI->connect("dbi:ODBC:database=$db;server=$server",$user,$pass) or die '$DBI::errstr';
From all that I have read and from previous scripts I have written, any of these should work, but I am getting the following error.
DBI connect('driver=$driver:dsn','user',...) failed: at C:cgi-binCGI.pl line xxx Cannot Open SQL Server Database $DBI::errstr at C:cgi-binCGI.pl line xxx.
Where xxx = the line number of the connection call.
All of my previous scripts have been written for forms where the web server(Apache), database(MySQL), and script(Perl CGI) are all on the same machine. I am not at all familiar with SQL Server, and am struggling to learn quickly. PLease and ideas would be greatly appreciated.
Thank you
LANAC
Hello to all:I want to get the output from xp_msver throughExecuteWithResultsAndMessage2 SQL-DMO and perl. This is the scriptI've got so far. Can anyone give me a few more pointers?use strict;use Win32::OLE 'in';use Win32::OLE::Const 'Microsoft SQLDMO Object Library';my ($server, $obj, $job, $jobs, $pathname, $c, $qr, $m );my $srv;my @servers = qw( KORTRIGHT );$server = Win32::OLE->new('SQLDMO.SQLServer2')or die "Could not create SQLDMO object.";$server->{LoginSecure} = 1;foreach $srv (@servers) {$server->connect($srv, '', '');if (Win32::OLE->LastError()) {die "***Err: Could not connect to $srv.";}printf qq[%10.10s %-50.50s %10.10s %s], "Hostname:",$server->HostName, "Server:", $srv;my $dbs = $server->Databases();if (Win32::OLE->LastError()) {die "**Err: Could not get the alerts collection in $srv.";}foreach $obj (in($dbs)) {printf qq[%10.10s %-50.50s %10.10s %s], "Database:",$obj->Name, "Size:", $obj->Size." Mb";}$jobs = $server->Jobserver;foreach $obj (in($jobs->Jobs() ) ) {if( $obj->Name =~ /'(.*)'/ ) {$job = $1;}foreach my $step ( in($obj->Jobsteps()) ) {if( $step->command =~ /-BkUpDB "(S+)"/ ) {$pathname = $1;printf qq[%10.10s %s], "Job:", $job;printf qq[%10.10s %s], "pathname:", $pathname;#print $step->command.qq[];}}}$c = "xp_msver";$qr = $server->ExecuteWithResultsAndMessages2( $c, $m );$server->DisConnect;}
View 1 Replies View RelatedI have a question regarding a SQL statement that I have in my perl program.
I am using the following code:
# SQL scalar
my ($SQL);
# Scalars for database columns
my ($userid, $username, $useremail, $userpwd, $useroffice);
# Initialise the values for an example request
$userid = "5";
$username = "alice";
$useremail = "alice@yahoo.com";
$userpwd = "blah2000";
$useroffice = "san antonio";
$SQL = "INSERT INTO users (userid, username, useremail, userpwd, useroffice) VALUES ('$userid', '$username', '$useremail', '$userpwd', '$useroffice')";
# Run the SQL query
if ($db->Sql($SQL))
{
print "Error on SQL INSERT";
Win32::ODBC::DumpError();
} # End if
print "<p>Running: $SQL<br>";
print "Note: $username has been created";
if you notice the useremail section includes a '@' character. This is not going to display the result that I want. How do I get perl to print the full email address correctly.
Right now, the result looks like this:
5 alice alice.com blah2000 san antonio
but I want it to display alice@yahoo.com
Any suggestions?
Thank You.
-Laura
#!perl
I am having difficulty converting my CGI(perl module) connection from mySQL to SQLexpress. Does anyone know what the connection string should look like?
Listed here is my current connection string. I'm trying to get this done this week. Please email me if you have an answer in addition to posting.
$dbh = DBI->connect("DBI:mysql:database=$var{database};host=$var{server}","$var{root_username}", "$var{root_password}",{'RaiseError' => 0, 'PrintError' => 0});
Much appreciated,
JOhn
I am having trouble finishing my query.
This is what I have:
IF EXISTS(Select ApplicationID from Application Where Application = '&_')
Insert Into PCApp(ApplicationID, SystemNetName)
Values( , $HoH->{Host}{SystemNetName})
I am not sure what to put in the blank within the Values parenthesis. I need to obtain the ApplicationID that is checked in the IF EXISTS section. But I cannot put a select statement into the Values() section.
Any suggestions would be appreciated.
Thanks,
Laura
I am just starting to learn about stored procs and how great they can make my world. I have been able to create a pro that does some data checking and inserts based on the results but what I have not been able to figure out is how would I pass a message from the proc to my Perl script that calls it? If anyone could point me in the right direction on what commands I should be looking into I would appreciate it.
--
If I get used to envying others...
Those things about my self I pride will slowly fade away.
-Stellvia
I need to connect to Express edition of sql server installed on my machine, from perl (solaris platform). How should the connection string containing server, port, database etc look like ??
I need to parse some text files and load them to database - these files are mostly CSV files or fix width columns format and the column names (first line) may vary in text (e.g. different abbreviation), order and extra columns, etc.
Is it a good idea have this done using script task of SSIS? How it compare to Perl on performance? Or any other tools good for this?
I have a requirement to implement CDC for 50+ tables to implement incremental data changes warehouse/reporting rather than exporting the whole table data. The largest table is having more than half a billion records.
The warehouse use a daily copy of OLTP db (daily DB refresh). How can I accomplish this. Is there a downside in implementing CDC just for the sake of taking incremental changes on the tables?
Is there any performance impact if we enable CDC on OLTP db?
Can we make use of the CDC tables on the environment we do daily db refresh so that the queries don't hit OLTP database?
What is the best way to implement CDC to take incremental changes for reporting.
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.
Hello,
I am wondering what conversion rules apply, when a string, which contains a number, is saved to a SQL Server 2005 into a column of type decimal.
This is the code I€™m using (C++):
CString cValue = "0.75"
_variant_t vtFieldValue;
vtFieldValue = _variant_t(cValue)
pRecordSet->Fields->Item["MyColumn"]->Value = vtFieldValue;
"pRecordSet" is an ADO recordset. The database column "MyColumn" is of type "decimal(19,10)".
The most important question for me is, if the regional settings of the database server or the regional settings of the client PC are considered during the conversion from the string to the decimal value. For example in standard French regional settings the "." would not be recognized as decimal separator.
I am also wondering if the language of the database instance, in which this data is saved, is considered during this conversion or any other settings of this database instance.
So my general question is: Does anybody know exactly what rules apply during the above mentioned conversion?
Thank you for your help.
Regards,
Volker
My vendor requires data to be sent in Excel format. Some of my tables have rows over 65,536 so I need to use Excel 2007 (Max of 1,048,576). Right now my data sits in SQL 2000. I am using MS SQL Enterprise Manager 8.0 to prepare the data. Is there some kind of add on or selection I am missing to use DTS to export from SQL to Excel 2007?Thanks in advance.
View 3 Replies View RelatedI think I am definitely thrashing and am not getting anywhere on something I think should be pretty simple to accomplish: I need to pull the total amounts for compartments with different products which are under the same manifest and the same document number conditionally based on if the document types are "Starting" or "Ending" but the values come from the "Adjust" records.
So here is the DDL, sample data, and the ideal return rows
CREATE TABLE #InvLogData
(
Id BIGINT, --is actually an identity column
Manifest_Id BIGINT,
Doc_Num BIGINT,
Doc_Type CHAR(1), -- S = Starting, E = Ending, A = Adjust
Compart_Id TINYINT,
[Code] ....
I have tried a combination of the below statements but I keep coming back to not being able to actually grab the correct rows.
SELECT DISTINCT(column X)
FROM #InvLogData
GROUP BY X
HAVING COUNT(DISTINCT X) > 1
One further minor problem: I need to make this a set-based solution. This table grows by a couple hundred thousand rows a week, a co-worker suggested using a <shudder/> cursor to do the work but it would never be performant.
I need to create a function that replaces the data in a column with an 'X' based on the LEN of the data in the column. I created one that does a replacement, but it fills the column based on the max data length, and not the current length of the string or integer. An example of what I'm trying to accomplish.
Original data in a varchar(30) column:
thisisavalue
thisisanothervalue
thisisanothervalueagain
shortval
replaced with
xxxxxxxxxx
xxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxx
xxxxxxx
My current function is replacing the data like this:
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Does anyone know of any cross-references between SQL Server data types and the new data types introduced with SQL Server Integration Services?
View 6 Replies View RelatedI 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 RelatedHi All,
I want to export data from SQL Server2005 to an Excel spreadsheet thru "Data Flow Task". I am using OLE DB for SQL Server for the source connection and a Connection To Excel as my destination source. The Excel spreadsheet (2003) exists and has the first row with column names. I don't have any warnings before trying to execute.
The SQL datable fileds are
i) ID - Int
ii) RefID
iii) txtRemarks - nvarchar(MAX)
iv) ddlWaterLevel - nvarchar(50)
While executing the tasks, I got the error
Error: 0xC0202025 at Data Flow Task, Excel Destination [427]: Cannot create an OLE DB accessor. Verify that the column metadata is valid.
Error: 0xC004701A at Data Flow Task, DTS.Pipeline: component "Excel Destination" (427) failed the pre-execute phase and returned error code 0xC0202025.
After analysing I found in the DataFlow --> Excel destination --> Advanced Editor for Excel Destination, the default data type for txtRemarks shows as "Unicode string [DT_WSTR]". But this is supposed to be "Unicode text stream [DT_NTEXT]". Even if I change the data type in the design time, It doesn't accept.
Please do help me out.
thanks
Sanra
Hi all,
I have a column in SQL server which is of type ntext. Selecting the specific column to view it in report builder, an error message appears with the following description:
- Cannot run this report. The grouping expression 'nameofcolumn' returns the datatype binary. The Grouping Expression cannot return binary data.
Report Builder recognises this as if it was an image...
Thanks in advance!
I need to periodically import a (HUGE) table of data from an external data source (not SQL Server) into SQL Server, with the following scenarios:
Some of the records in the external data source may not exist in SQL.Some of the records in the external data source may have a different value at different imports, but this records are identified univocally by the same primary key in the external datasource and in SQL Server.Some of the records in the external data source may be the same in SQL.
Due to the massive volume of the import, I would like to import only the records which are different from what I have in SQL Server (cases 1 and 2 above). In fact case 2 is the most critical.
I thought of making a query with a left outer join between the data in the external data source table (SOURCE) and the data in the SQL Server table (DESTIN). The join is done on the respective primary keys (composed keys of up to 10 columns) and one of the WHERE conditions will be that the value in SOURCE is different from the value in DESTIN.
The result of this query would be exactly what I need to import.
How to do this in SSIS??? I couldn't figure out how to join tables in different data sources yet.
In fact I cannot write a stored procedure to do that, since one of the sources is in a datasources not SQL Server.
I have seen the Lookup transformation in this article http://www.sqlis.com/default.aspx?311 but this is not exacltly what I want to do.
Another possibility is to use the merge join, but due to the sorting I believe its performances would be terrible!
Thanks in advance for your suggestions!
I'm in the middle of developing a Database for a hospital that measures its audits, inhouse operations, and finance. What we currently have and do everyday is collect data from a large database that is real time with patient data, progess, infomation, etc and dump it into a data warehouse that runs on TSI/Eclipsys. We run reports using a number of programs and dump it into Excel sheets that have charts, reports, etc. This Database for which I'm developing won't come soley from the TSI/Eclypsys source, but this is the only source thats updated regularly. I don't want to have in sync with TSI/Eclipysys in fear that every day when it updates data may be lost, not read, or worse won't be up date if there is a problem. My question is is it possible to run a query from Sequel Server 2005 that will take that data upon request using the reporing features on Sequel Server 2005. i.e. What if I need to run a report on measure B in department 12 from Jan 1-Feb 1, instead of being in sync, can I just write queries to take that information rather than double the data and take up twice the space and trouble. FYI, these datatypes rarely change in the TSI/Eclipsys data warehouse. This sure was long question and didn't intended it to be . Thanks for listening and hope to hear back.
Stephen
Hi all,
It looks like these options are only available in the SQL Server Management Studio? I installed SQL Server Management Express Studio and I can't even find the DTSWizard.exe on my machine.
Can you please help how I can import data from excel or where can I download the SQL Server Management Studio?
Your prompt response is greatly appreciated.
Thanks!!
Tram
I need to call a stored procedure to insert data into a table in SQL Server from SSIS data flow task.
I am currently trying to use OLe Db Destination, but I am not sure how to map inputs to OLE DB Destination to my stored procedure insert.
Thanks
String or binary data would be truncated. I get this error when entering data using sql server management studio express.
I am not running a sql to insert or update the table. This is through the EDI.
The data type is varchar(100). I enter one character and it errors on me. So this isn't a string being too long problem.
Any ideas?
I am using the following query to export data from sql server to ms access in export data wizard:
SELECT * FROM myView where myID = 123
Order by varcharColumnName1,varcharColumnName2 ,intColumnName3
This query will fetch about 7, 00,000 records.
SQL server 2005 shows the correct order, but Data in access table shows Incorrect data.
Please give me the solutions.
I have one column in SQL Server 2005 of data type VARCHAR(4000).
I have imported sql Server 2005 database data into one mdb file.After importing a data into the mdb file, above column
data type converted into the memo type in the Access database.
now when I am trying to import a data from this MS Access File(db1.mdb) into the another SQL Server 2005 database, got the error of Unicode Converting a memo data type conversion in Export/Import data wizard.
Could you please let me know what is the reason?
I know that memo data type does not supported into the SQl Server 2005.
I am with SQL Server 2005 Standard Edition with SP2.
Please help me to understans this issue correctly?
Hi I have written a piece of code for Login form which reads the user id and password from db. It works fine with the Sql server 2000 but I get a error with Sql server 2005. SqlConnection conn = new SqlConnection("Data Source=D\SQLEXPRESS;Initial Catalog=model;Integrated Security=True"); SqlCommand cmd = new SqlCommand("Select * from JsLoginDetails", conn); conn.Open(); SqlDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { if ((Login1.UserName == dr.GetValue(0).ToString()) && Login1.Password == dr.GetValue(1).ToString()) { Response.Redirect("MainJs.aspx"); } else { Login1.FailureText = "Invalid Userid Or Password"; } } dr.Dispose(); conn.Close(); } I get and error Invalid object name 'JsLoginDetails'. pls help thnksdiv
View 1 Replies View RelatedI want to select weekly data from daily data.lets say Today's date-10/23/2014(Thursday) My data is in date time but i want to see only date
output should be from last week Thursday to this week Wednesday. similar for previous dates
Weekly sum(profit)
10/16 - 10/21 - $1000
10/9 - 10/15 - $4100
10/2 - 10/8 - $ 8038
--
--
--
Example of data in CSV are as follows:
"XXX","0001",-990039739 ,0 ,0 ,0 ,0 ,0 ,0
"ABC"," ",-3422054702 ,0 ,481385 ,0 ,0 ,0 ,0
"JJZ","0001",0 ,0 ,0 ,0 ,0 ,0 ,0Here's my format:
12.0
10
1 SQLCHAR 0 0 """ 0 "" ""
2 SQLCHAR 0 5 "","" 1 OKCCY SQL_Latin1_General_CP1_CI_AS
[Code] ....
Hello,
I took our SYBASE DDL and created the tables on an SQL Server Express datbase on a PC and then downloaded our data from UNIIX using the WinSCP utility and BULK Inserted the data into the SQL Server database and everything uploaded fine apparently, but when we go to use the data we are getting some data differences.
Do you know of any data conversion issues that may be coming into play here maybe?
Thanks very much!
BobK
I had a function like below :Public Sub Getdata2(ByVal query, ByVal db, ByVal name)
Dim selectSQL As StringDim con As SqlConnection
Dim cmd As SqlCommand
Dim reader As SqlDataReader
Trycon = New SqlConnection("User ID=xxx;password=xxx;persist security info=True;Initial Catalog=database1;Data Source=xxx.xxx.xxx.xxx.xxx,xxxxx;")
Dim username As String
username = Request.QueryString("username")
selectSQL = "SET DATEFORMAT DMY;Insert into table1(hse_num, st_name, proj_name, unit_num, postal, n_postal, flr_area, flr_sf, flr_rate, flr_ra_sf, land_area, land_sf, land_rate, lnd_ra_sf, prop_code, cont_date, title, sisv_ref, r_date, rec_num, source, username, DGP, Remarks, Sub_Code, caveat, consider, age) select hse_num, st_name, proj_name, unit_num, postal, n_postal, flr_area, flr_sf, flr_rate, flr_ra_sf, land_area, land_sf, land_rate, lnd_ra_sf, prop_code, cont_date, title, sisv_ref, r_date, rec_num, source, '" & username & "', DGP, Remarks, Sub_Code, caveat, consider, age from [yyy.yyy.yyy.yyy,yyyy].database2.dbo.table2 where " & querycmd = New SqlCommand(selectSQL, con)
con.Open()
reader = cmd.ExecuteReader()
lbl.Text = selectSQLCatch ex As Exception
lbl.Text = ex.Message
Finally
If (Not con Is Nothing) Then
con.Close()
con.Dispose()
End If
End Try
End Sub
'-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
May i know that how do i retrieve data from [yyy.yyy.yyy.yyy,yyyy].database2.dbo.table2 due to diffrent server, diffrent UID and Password