I need to write a stored procedure or SSIS to export data to client's desktop. What is the best way to achieve it?
The requirement is to develop a web application the client can click on the button and the file will be saved to the clients' local C drive.
How should I configure the destination path here?
I connect to a compute via Remote Desktop Connection that is running SQL Server 2003. That part works. However, once I'm connected, and running SQL Server, I cannot see my user name in SQL, when I use the query, "SELECT user_name as ()". What I see instead is "DBO". Nor does it show me as being in a particlar group. I also cannot see my Windows Authentication remote computer name.
I think it has something to do with using my PC to do the Remote Desktop Connection and then from there, running SQL Server 2003, but am lost on this one.
Can anyone help? I sure would appreciate it! Thanks in advance!
*********************************************************************
This forum is best viewed with a computer. Questions asked freely. Confusion happily shared.
Hi,
I've experienced a problem (bug?) when deploying reports to the report server. My users kept complaining about losing authentication to reports when I deploy a new report. I found the problem but don't understand why this is happening. My collegue developped the earliest reports and loggs in with a different user on Remote Desktop to develop the reports. When the report is deployed, the datasources are deployed also.
Now when I log in on Remote Desktop with another user then my collegue and open the same report project, and check out the datasources, the credentials are gone ... ?!? So when I deploy a report the datasource with the empty credentials are deployed too, wich causes all the authentication problems.
My Question: How comes I can't see the user credentials my collegue filled in? When I open the report project the credentials are gone. I log out, my collegue loggs in, and the credentials are back
I suppose this has something to do with Remote Desktop and the users connecting to it?
Thanks in advance!
My question is, is it possible to connect to an SQL database using the MSDE 2000 SP3a edition over the internet? And if so, how do I set it up? Thank you in advance.
View 1 Replies View RelatedHello.
Has anyone attempted and successfully installed SQL Server 2000 SP4
via remote desktop on the cluster.
The reason I am asking is that SP4 readme file has this:
"...
4.3 Systems Management Server Distributed Installation
You cannot install Database Components SP4 from a remote location.
..."
Currently SQL Server has SP3a installed.
Thank you in advance.
Hi,
Does anyone know if it's at all possible to install SQL Server Express 2005 over a Remote Desktop on a Windows XP machine.
I know there are problems running SQL over Remote Desktop, in that it won't start up a User Instance, but I wasn't sure if we can install over Remote Desktop.
If it's not possible, can anyone point me to an official document stating this fact?
Thanks
Sam
I was able to do this on a Windows XP machine but I am not able to get this to work on a Windows 2003 Server machine.I am connecting to the Windows 2003 Server and MS SQL Express via remote desktop on the lan.This is what worked for me before:EXEC sp_grantlogin '4800X2ASPNET'USE MyDBEXEC sp_grantdbaccess '4800X2ASPNET'EXEC sp_addrolemember 'db_owner', '4800X2ASPNET'But for some reason now I am getting this error:Msg 15401, Level 11, State 1, Procedure sp_grantlogin, Line 49Windows NT user or group '4800X2ASPNET' not found. Check the name again.Msg 15401, Level 16, State 1, Line 1Windows NT user or group '4800X2ASPNET' not found. Check the name again.Msg 15410, Level 11, State 1, Procedure sp_addrolemember, Line 75User or role '4800X2ASPNET' does not exist in this database.I thought what I needed to add was the name of this pc that will connect remotely, but that doesn't seem to be working.What
weird is the old copy of this query I used had the local pc as the
computer name. If I exchange the computer name from the remote pc name
to the local 2003 Server machine name, the query works fine. Although
that still doesn't let me add connections via Web Developer Express
without error. It doesn't make sense to me anyhows, how could I have
used the local machine's name and not the remote machine's name to get
this to work previously. So confused.Thank you in advance for help.
ProblemWhen Remote Desktop to my main office machine, and when trying toconnect to SQL Express, I was becoming increasingly frustrated with thefollowing error:Error: 'Failed to generate a user instance of SQL Server due to afailure in starting the process for the user instance. The connectionwill be closed.'What is the solution?Solutions: ( pick any of the 3)1. Obtain and apply the patch http://support.microsoft.com/?id=8966132. Wait for the next Windows Service pack3.Of course if you can not get the patch, you can always do this (Note:You must have WinVnc server running in your remote computer)1. In the remote desktop session, reboot your computer.Click Start->Windows Security->Shutdown->Restart.Wait for some time to get machine up.2. Use your vncviewer to login to your remote computer. You can chooseto do all work via VNC or go to next step.3. Use remote desktop to login (this automatically closes the winvncsession).There you go, You did not create a remote session, but reused yourlocal session.
View 1 Replies View RelatedI was able to do this on a Windows XP machine but I am not able to get this to work on a Windows 2003 Server machine.
I am connecting to the Windows 2003 Server and MS SQL Express via remote desktop on the lan.
This is what worked for me before:
EXEC sp_grantlogin '4800X2ASPNET'
USE MyDB
EXEC sp_grantdbaccess '4800X2ASPNET'
EXEC sp_addrolemember 'db_owner', '4800X2ASPNET'
But for some reason now I am getting this error:
Msg 15401, Level 11, State 1, Procedure sp_grantlogin, Line 49
Windows NT user or group '4800X2ASPNET' not found. Check the name again.
Msg 15401, Level 16, State 1, Line 1
Windows NT user or group '4800X2ASPNET' not found. Check the name again.
Msg 15410, Level 11, State 1, Procedure sp_addrolemember, Line 75
User or role '4800X2ASPNET' does not exist in this database.
I thought what I needed to add was the name of this pc that will connect remotely, but that doesn't seem to be working.
What weird is the old copy of this query I used had the local pc as the computer name. If I exchange the computer name from the remote pc name to the local 2003 Server machine name, the query works fine. Although that still doesn't let me add connections via Web Developer Express without error. It doesn't make sense to me anyhows, how could I have used the local machine's name and not the remote machine's name to get this to work previously. So confused.
Thank you in advance for help.
I'm using ssms 2014, but got the same problem with 2012. I use ssms almost entirely on remote desktop sessions ( Windows 7, Server 2008R2, Server 2012 ). It may be related to having filtered job activity monitor windows open for hours, but about once per day ssms fails, and has to restart. Upon resuming usually only one of several queries is restored.
View 8 Replies View RelatedI have a C# windows app trying to access database based the connection string stored in teh app.config file
Here is my app.config parameters
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<connectionStrings>
<add name="con1" connectionString="data Source=127.0.0.1sinclair;initial catalog=kingsroad;uid=balrock;pwd=123456;integrated security=True" />
</connectionStrings>
</configuration>
The reason I want to put an ip address on connection string i just want to create connection strings as people do in their work. When the app tried access the connection string it gives the error in the attachment.
Because I haven't used this user/password for a long time I'm pretty sure the user name and password is correct but i have the following questions
1. Assume the user and password is incorrect, is there way to reset this?
2. what other configuration that i need to do in order to make teh C# app access this Database?
I have a client application written from C#/winforms app that needs access SQL DB on SQL server 2008 R2 developer. So I need to create a Login for this app with a user/password. But when i created it and put it on the client's connection string, the DB could not be accessed by the client windows forms app and the error was "Login failed for User App1".
Then I tried to login to SQL server from SSMS using the credentials of App1 but it also gave same error "Login failed for user App1". I have following the following steps of creation of the login for App1. i could login from "sa" account and other Admin accounts but not from "App1" account
1. created a server level Login
2. created a DB level user
3. created a Role (a DB role), i even tried creating an App Role
4. given permission to execute stored procedures that handle login
But didn't work. Maybe i made some mistakes in those step. So, how to create an account for user "App1" so it can be accessed by the client windows app and also from SSMS? i would prefer to use account creation from SSMS GUI.
How do i install SQL server 2008R2 in local machine with network accessibility features?
hey i created a client app.. i want to connect it to a remote db.. but i am not able to connect through the internet. but its working for LAN.
is there anything that i must change in the settings of the enterprise manager??
i am connecting through an sa password and i allowed other sql servers to connect remotely to this sql server using rpc...
i am using this code to connect . i am using vb.net
"Data Source=x.x.x.x;Network Library=DBMSSOCN;Initial Catalog=test;User ID=sa;Password="
Hi all,
We installed RS on a non default website on our IIS server. When targeting the Reports page from the server itself (http://site:8015/Reports) we can see the welcome page. However if we try to access that same page from another machine we get a generic IE message: IE cannot open the specified page check your internet connection etc. Because the error is so generic I have no idea as to what can be the cause: IIS or RS config ? I suspect IIS because no RS error is raised but then I don't see what I could've missed on that level...
Any ideas of what could be the cause of this ?
thanks a lot
Notes:
- the DB and the RS instance are on the same server
- other sites hosted on the same server are accessible from the client machine
- Anonymous access is enabled on the site level, and on the Reports / ReportServer level. I tried unsetting it but it didn't change anything for the remote client.
- I tried changing the account RS is running under: it used to be LocalSystem, I now set it to NT AuthorityNetworkService but no difference.
Environment: SQL Express Advanced & Windows Server 2003
Hi,
I am developing a windows application that needs to communicate with a remote SQL server 2005 database. Server allows remote connections and MSDTC service also running. Do I need to run MSDTC service on the client machine where I use desktop application ? any ideas ? It's throwing some error like
An error has occurred while establishing a connection to the server.
When connecting to SQL Server 2005, this failure may be caused by the fact
that under the default settings SQL Server does not allow remote connections.
But my SQL Server allows remote connection, and I am able to do a select statement.
But when I insert/update anything, it's throwing this error. I guess some problem with MSDCT. Anybody have any idea ?
I would like to be able to change the configuration of a remote machine`s SQL client config.
Can anyone provide me with the registry location of where it stores the protocol and libraries?
Can`t always depend on having someone at remote to run the SQL client app.
TIA Sam Janney
I was told that the authentication is a process that concerns about security. So the authentication process should be hidden from the client as possible and the client should receive a flag value (1= password is correct and authenticated, -1= incorrect password) and/or user name of the authenticated user. So it should be a SQL operation that should occur in the back end. So the process in detail should be, client form sends user name and hashed /encrypted password to SQL, SQL authenticates and it sends 1/-1 with the user name to the client as the return value and the output.
But when i do research that was not the case, all the threads i have seen on web, either the app is windows or web, developers do two operations:
1. the salt value of the user is searched by user name then return the salt value along with the PasswordHash to the client form / or to the business layer
2. Then use the entered password and the returned salt value to generate a PasswordHash and then compare this passwordHash with the returned passwordHash. if match password is correct if not password is incorrect.
Is this actually how developers write code to authenticate a user in a login event? If so basically the password comparison happens at the client side(either in the form or in the business layer) then wouldn't this be huge security risk by exposing the actual salt and passwordHash to the client?
Hello all,I know that this is a long shot, but I have a problem which someonereading this group *may* just be able to shed some light on.We have a new internal personnel planner/attendance system in place. Ituses a web interface to allow members of staff to select their sitelocation for any week, request leave and record absences.The server-side scripting is composed of VB/ASP and Javascript whichultimately queries & writes to an MS SQL Server 2000 database via anODBC connection between webserver (IIS6.0).Here is the problem and I *know* that it sounds unlikely/impossible butwe carried out exhaustive tests.-> I open a web browser on my local PC (XP Pro SP2)-> Login to the web-based planning system-> Update information-> Submit ChangesAt this point I get a script error:---error message---Microsoft OLE DB Provider for ODBC Drivers error '80040e14'[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntaxnear ')'./staff/wfp/Whereabouts/writeAbsence_v2_5.asp, line 108-------------------Which is fair enough as it probably points to a problem with the SQLstatement being passed by the .asp page.***BUT*** I _only_ get this message when I access the page via a browserfrom certain PCs!I have tried 3 browsers: MSIE6, Firefox1.5, Opera8.5.I have obviously ensured that it isn't any kind of browser caching issue.I have no issues using scripted-based web pages in general from the PCin question.I have checked the regional settings to ensure that I am using astandard character set.I *can* submit database changes via web browsers from most other PCs onour network, without incurring the error message.Even when I get the error message, the values are written to the db andthe information is updated.The other PCs from which I am experiencing the same difficulty areservers running Win2000 & Win2003 respectively.Is there any kind of OS/User Profile setting which would mean thatscripts running on a remote webserver would fall over when trying toperform an SQL write?Could it be some kind of character encoding issue, which means that theparameters that are received by the .asp script and then written via theSQL statement are mangled in any way?I can't see that there is any kind of local setting that would influencewhether remote scripts would or would not cause an error such as this.If anyone has any ideas, has encountered anything similar or can shedlight on a system-specific setting or feature that may cause this,please post your thoughts.Yours GratefullyGuy
View 3 Replies View RelatedHi,
I am a VB6 programmer and I'm really new to SQL Express, I have always used ACCESS because it was so easy to deploy. I've now written a VB6 program which uses SQL Express and I want to know how to deploy it. Basically my PC will host the SQL database as it has SQL Express loaded onto it. I now want to install my EXE onto other Client's PC and have them read/write to the database on my machine.
Besides the EXE program, what software do I need to load onto the Client's PC so they can access my SQLExpress database...? (eg: Net Framework, SQLExpress, etc).
Also, if my machine is called ABC123, in my connectionstring do I just put in
ABC123sqlexpress as the datasource..?. Will this be enough for the other PCs to connect to mine ?
Thanks
Good afternoon,
I currently develop/maintain VB 05' apps for PDA's and desktops. I've got a scanner app that scans labels to build/transmit orders via an HTTP byte stream. I'm currently building an inventory app that resides at the remote client (windows not web) and can eventually connect to my SQL Server @ corp. At this point SQL 2005 endpoints work on the internal network but fail to resolve remotely. I've read about connecting to a SQL Server via HTTP with ip specs embedded in the connect string but havnt been able to find an example. Outside of creating a VPN for connectivity, can anyone recommend connection ideas for my scenario? Is it true that endpoints are only recommended for intranet consumption?
Thanks,
ChrisG
Hi,
I think this is a taff problem for me when I connect to remote sql express from .net application
I use Connection String:
Data Source=MYREMOTESVRSQLEXPRESS;Initial Catalog=microDB;Integrated Security=SSPI;Persist Security Info=True;User ID=micro;Password=micro
OR
Data Source=MYREMOTESVRSQLEXPRESS;Initial Catalog=microdb;Integrated Security=True;Persist Security Info=True;User ID=micro;Password=micro
And found EROR message when running aplication from client comp/application
"... Login failed foruser MYCOMPGuest... "
How to setup a user in the sql expss server to support Client/Server Application?
Anyone can help me?
regards.
md5
If I look under SQL native client configuration | Client protocols, I have no entries and no ability to look at the properties of SQL Native client Configuration. Something is missing which I think is related to an error when connecting to my local server.
sqlcmd -E -S .devmain, 1064
I get error :
Encryption not support
sqlcmd: Error: microsoft SQL native Client : Client unable to establish connection
An error has occurred while establish a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections..
In comparison to other machines here at work, it appears my remote connections are configured. But I have no Client Protocols under sql native client configuration, how can I get them in there?
Thanks, in advance.
Hello,
Was wondering if anyone might have some info in regards to this issue. I am using SSRS report viewer client in a asp.net page. I would like to restrict the export types in the export type dropdown list to 'pdf' only. Any info on this will be greatly appreciated!
Thanks,
Bill
Here is what I knew: usr/pwd, Domain, Server and Database.
How to set Enterprise Manager, then can I connect to DB ?
I have table1 with col1 varchar,col2 int , col3 xml , col4 bit ...best way to fetch the col3 into file (.txt or .sql) into seprate file for each col3 record . I want to export this in different files for each record if possible with date and time stamp .
View 5 Replies View RelatedI have a de-normalized table that I need to export to XML using For XML, but put all of the related rows under the same node.The table is alot more complicated than the example below, but for proof of concept purposes, i'll keep it really simple:
Campaign, Price
C1, 4.00
C1, 6.00
C1, 10.00
C2, 1.00
C2, 13.00
C3, 20.00
If I have a table of campaigns and prices, I would like to output it as XML like the following:
<Campaign name="C1">
<Price value="4.00"></Price>
<Price value="6.00"></Price>
<Price value="10.00"></Price>
</Campaign>
<Campaign name="C2">
<Price value="1.00"></Price>
etc
The default behaviour gives me this:
<Campaign name="C1" price="4.00"/>
<Campaign name="C1" price="6.00"/>
<Campaign name="C1" price="10.00"/>
Is there an existing option that deals with this automatically, or do I essentially need to do a group by to output the campaign element, and then union an ungrouped select to output the price element?
I'm trying to run this code, which worked on SQL 2000, on 2K8:
SELECT [Business Unit]
,[Department]
,[Ext :]
,[F9]
,[First Name :]
,[Last Name :]
,[Mobex :]
,[Mobile :]
,[Sub Department]
FROM MyCompanyStaffPhoneList...Sheet1$
However it just returns this message..Msg 7308, Level 16, State 1, Line 1 OLE DB provider 'Microsoft.Jet.OLEDB.4.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.
I have written a VB component to execute a SSIS package stored in SQL server.
I am using the Microsoft.sqlserver.dts.dtsclient, dtsconnection/dtscommand
and executereader and everything works perfect on the local machine. This is descibed in a msdn site .
But my customer have a remote SQL server and want no extra BI clients/SQL server clients etc on the client machine, only an ordinary windows client and network connection to the SQL server.
My question is: Can you still use dtsclient or do you have to do in some other way?
rose-marie
I need to export the records of a table in xml format.
create table ##prova
( Valuta varchar(2),
Misura float
)
insert into ##prova values ('EU',1000)
insert into ##prova values ('$',2000)
The final result must be something like this:
<root>
<obs id=”0”>
<dim name=”Valuta” value=”EU” />
<dim name=”Misura” value=”1000” />
</obs>
<obs id=”0”>
<dim name=”Valuta” value=”$” />
<dim name=”Misura” value=”2000” />
</obs>
</root>
I have a procedure that generates some XML from a bunch of tables.Then i use BCP to export it to a file. This works just fine.Here's the sample code:
CREATE TABLE dbo.t_test (i INT, z VARCHAR(30) COLLATE DATABASE_DEFAULT)
INSERT INTO t_test (i, z)
SELECT1, 'Test'
GO
[code]....
But recently, i wanted to add a test that calls this procedure. So, Test procedure uses INSERT / EXECUTE thingy to put XML data into a temp table to compare things. But then you get following error: "The FOR XML clause is not allowed in a INSERT statement."
DECLARE@T TABLE (x XML)
INSERT INTO @t (x)
EXECSPRC_EXPORT -- crashes here
So, i thought, fine, i'll wrap the FOR XML inside a sub-SQL:
CREATE PROCEDURE SPRC_EXPORT
AS
SELECT(
SELECT*
FROMt_test
FOR XML PATH('root')
)
And BCP call still works, BUT, now the file generated becomes 64kb instead of 1kb :) When i look into the file, it displays same XML, but the string is right-padded with a LOT of spaces.how BCP uses SET FMTONLY, OR that the "type" of result somehow gets changed when i do the wrapping.
How can I Export Database with foreing Key and primary key.
Operation is that
SQL2005 Management Studio/Database/Tasks/Export Data
Before Version is SQL2000 we can Selected Copy Object and data between server and then Use Default Options click checked and Select Copy Index, Copy Foreing Primary key vs vs
But this options is not found in the SQL2005 Management Studio/Database/Tasks/Export Data wizard or I can't found it.
How can I export foreing Key and primary key with SQL2005 Management Studio/Database/Tasks/Export Data wizard.
Best Regards,
Athena.
Hi!
What is the fastest whay to take the resultset
from an temporary table (# table)
and write it to an remote destination (mapped network drive)
initiated from stored proc?
Similar question if I take the resultset and convert it to xml (using FOR XML clause)
and want it written to disk.
Thank you for your ideas ;-)
I'm trying to write a function that will retrieve the last backup date/time of a particular database on a remote server (i.e. by querying msdb.dbo.backupset). Unfortunately, you can't use sp_executesql in a function, so I can't figure out a way to pass the server name to the query and still be able to return the datetime value back to the calling TSQL code (so that rules out using EXEC().
View 3 Replies View Related