SQL Server 2008 :: Replication Works From A Command Prompt But Not From SSMS?
Sep 30, 2015
SQL Server 2012 Replication. The command prompt instance of the replication is being run as the same user as the SSMS "version" using the runas option. With SSMS the error is that the client is missing a privilege. There is no error when it runs from the command prompt. All runs well.
We have reinstalled the replication feature.We have gone through several MSDN articles that indicate exactly what privileges (both within the DB and in Windows) that service account should have. None have worked Incidentally, when I create a new publication and indicate that the SQL Server Agent be used , the publication runs.
I have a dts that works from the command prompt, but hangs in Query Analyzer. Here's the code inside Query Analyzer:exec master..xp_cmdshell 'dtsrun /S BFHSQL4 /N vhl_dts_14144b /E'From the command prompt, it's just:dtsrun /S BFHSQL4 /N vhl_dts_14144b /EAny ideas what's wrong? Thanks for your help!
I have created linked servers and successfully run the following stored procedure.
exec [db-2001server].sales..sp_test
Can anyone give me the exact syntax for running the same from command promt(without using the query analyzer).I am new to isql/osql.So,please help me with the syntax. Thanks!!
I need to verify a csv file exists before I call a stored procedure to complete a bulk insert. The first step in the SP will be to verify the file exists (that particular "Job" step did not fail). I have only one step in the job that I have set the type as "Operating System (CmdExec)" and the Command is "Dir C:NAFCDMDataShipmentsCooperShipments.csv". When I execute the job manually to verify the proper operation I receive the error listed below. I know the path and file name are correct as I originally typed in the UNC, when it failed I copied it from the Address bar. I attempted to move the file to the root location without success. I attempted to use another file. I have attempted to rename the file. The DBAs will not allow me to use the SP_cmdshell stored procedure (that was my first thought).
I added 'Everyone' with Full Access to the root folder and propagated these permissions to all containing folders. This again did not allow the job to run. I changed the statment to "REN C:NAFCDMShipmentsShipment.csv Shipment1.csv". I continue to get the same error. Can someone Please point me in a direction to fix this problem?
"The process could not be created for step 1 of job 0x71D51027F920A140A2913234DB7FF509
(reason: The system cannot find the file specified). The step failed.
The job failed. The Job was invoked by User sa. The last step to run was step 1 (Verify the File's Existance)."
I need to verify a csv file exists before I call a stored procedure to complete a bulk insert. The first step in the SP will be to verify the file exists (that particular "Job" step did not fail). I have only one step in the job that I have set the type as "Operating System (CmdExec)" and the Command is "Dir C:NAFCDMDataShipmentsCooperShipments.csv". When I execute the job manually to verify the proper operation I receive the error listed below. I know the path and file name are correct as I originally typed in the UNC, when it failed I copied it from the Address bar. I attempted to move the file to the root location without success. I attempted to use another file. I have attempted to rename the file. The DBAs will not allow me to use the SP_cmdshell stored procedure (that was my first thought).
I added 'Everyone' with Full Access to the root folder and propagated these permissions to all containing folders. This again did not allow the job to run. I changed the statment to "REN C:NAFCDMShipmentsShipment.csv Shipment1.csv". I continue to get the same error. Can someone Please point me in a direction to fix this problem?
"The process could not be created for step 1 of job 0x71D51027F920A140A2913234DB7FF509
(reason: The system cannot find the file specified). The step failed.
The job failed. The Job was invoked by User sa. The last step to run was step 1 (Verify the File's Existance)."
How can I login to SQL Server Express instance(localhostSqlExpress) using command prompt so that I can query database. Is there any alternative GUI tool for SQL Server Express Manager? (I think Microsoft has discontinued SQL Server Express Manager). I prefer using Command Prompt. I am using WindowsXP Professional.
I want to install the SQL Server Express Edition silently at command prompt (but want the dialog boxes, I mean with /qb option), locally, with no network options.
I want to install it with following options
i) In the Feature Selection, I need a) Data file (Database Services) b) Shared tools (Database Services) c) Connectivity Components (Client Components) d) Management Studio Express (Client Components) ii) Instance name as €œDefault Instance€? iii) Service Account as €œUse Built-in service accountà Local System€? iv) Authentication Mode as €œMix mode€? with sa password as €˜sysadm€™ v) Collate Settings as €œLatin1_General_CS_AI
I just started using SQL 2005 and having a bit of struggle.
I'm having difficulty trying to use the .ini file to install sql express. This is the command I use in Command Prompt: start /wait expresssetup.exe /settings setup.ini /qn echo %errorlevel%
Then I get an error level of €˜-204€™, which I couldn€™t find the meaning on the internet or anywhere else.
But when I move the parameters from the .ini file to the batch file and the process worked. SQL started installing, until it comes to the SQL Server Database Services component. Then I get the following error message:
SQL Server Setup could not validate the service accounts. Either the service accounts have not been provided for all of the services being installed, or the specified username or password is incorrect. For each service, specify a valid username, password, and domain, or specify a built-in system account.
I would be very grateful if someone could take a look at my command line install and offer advice on if I am doing it correctly.
The reason I am asking is because I cannot seem to get the server installed without having errors during the installation: "The SQL Server (MSSQLSERVER) service terminated with service-specific error 17113 (0x42D9)"
Looking at the server error log indicates that master.mdf was not in the data directory (I have however specified a different data directory for ease of backup by our software and this is created during the install but does not have this database in the data directory - it is however in the template data directory!
I also get this error when the SQL Server service is started: "The SQL Server (SQLEXPRESS) service terminated with service-specific error 3417 (0xD59)."
All advice is welcome. Please see below my batch file. Also, if you think I should do this in a different way then please let me know. I basically want to install SQL server on a customer computer and automate the install so the custoemr does not need to enter any information.
The goal of this install is: [1] Deply a custom .Net application and SQL server at the same time on the same computer without user input. [2] Allow remote .Net applications (designed by myself also) to connect over a LAN (and later maybe over a WAN) to the database to the above database.
The command line is this (I have also included the batch file I am usign below). For ease of reading I have put the parameters on a new line below:
SET MODE=/qb SET SETUP_EXE=sqlfilessetup.exe %MODE% SET SQLDATADIR=c:SqlData
SET SET_USERNAME=username="CustomerUsername" SET SET_COMPANYNAME=companyname="OurCompany" SET SET_ADDLOCAL=addlocal="SQL_Engine,SQL_Data_Files,SQL_Replication,Connectivity,SDK" SET SET_DISABLENETWORKPROTOCOLS=disablenetworkprotocols="0" SET SET_INSTANCENAME=instancename="MSSQLSERVER"
SET SET_INSTALLSQLDATADIR=INSTALLSQLDATADIR="%SQLDATADIR%"
SET SET_SAVESYSDB=SAVESYSDB="1" SET SET_USESYSDB=USESYSDB="%SQLDATADIR%"
SET SET_SQLAUTOSTART=SQLAUTOSTART="1" SET SET_SECURITYMODE=SECURITYMODE="SQL"
@REM === RE-INSTALL PARAMS - NOT CURRENTLY BEING USED?? SHOULD I ADD THESE ALSO? === SET SET_REINSTALLMODE=REINSTALLMODE="omus" SET SET_REINSTALL=REINSTALL="SQL_Engine,SQL_Data_Files,SQL_Replication,Connectivity,SDK" SET SET_REBUILDDATABASE=REBUILDDATABASE="1"
I have an SSIS package that runs fine through command pormpt although when I try to run it from a SQL Servr Agent Job CmdExec step it bombs out. Please help this has me stumped...the SSIS package uses an XML connection string so certain key settings such as connection strings and email info can be changed easily. Currently this is all on the same machine. I have not moved it beyond where I am developing.
On the command line I am using the following command...
Microsoft (R) SQL Server Execute Package Utility Version 9.00.1399.06 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
Started: 6:59:40 PM Progress: 2006-08-16 18:59:41.29 Source: Data Flow Task Validating: 0% complete End Progress Progress: 2006-08-16 18:59:41.29 Source: Data Flow Task Validating: 33% complete End Progress Progress: 2006-08-16 18:59:41.71 Source: Data Flow Task Validating: 66% complete End Progress Progress: 2006-08-16 18:59:41.73 Source: Data Flow Task Validating: 100% complete End Progress Progress: 2006-08-16 18:59:41.77 Source: Data Flow Task Validating: 0% complete End Progress Progress: 2006-08-16 18:59:41.77 Source: Data Flow Task Validating: 33% complete End Progress Progress: 2006-08-16 18:59:41.77 Source: Data Flow Task Validating: 66% complete End Progress Progress: 2006-08-16 18:59:41.77 Source: Data Flow Task Validating: 100% complete End Progress Progress: 2006-08-16 18:59:41.79 Source: Data Flow Task Prepare for Execute: 0% complete End Progress Progress: 2006-08-16 18:59:41.79 Source: Data Flow Task Prepare for Execute: 33% complete End Progress Progress: 2006-08-16 18:59:41.79 Source: Data Flow Task Prepare for Execute: 66% complete End Progress Progress: 2006-08-16 18:59:41.79 Source: Data Flow Task Prepare for Execute: 100% complete End Progress Progress: 2006-08-16 18:59:41.81 Source: Data Flow Task Pre-Execute: 0% complete End Progress Progress: 2006-08-16 18:59:41.84 Source: Data Flow Task Pre-Execute: 33% complete End Progress Progress: 2006-08-16 18:59:41.90 Source: Data Flow Task Pre-Execute: 66% complete End Progress Progress: 2006-08-16 18:59:41.90 Source: Data Flow Task Pre-Execute: 100% complete End Progress Progress: 2006-08-16 18:59:41.92 Source: Data Flow Task Post Execute: 0% complete End Progress Progress: 2006-08-16 18:59:41.92 Source: Data Flow Task Post Execute: 33% complete End Progress Progress: 2006-08-16 18:59:41.92 Source: Data Flow Task Post Execute: 66% complete End Progress Progress: 2006-08-16 18:59:41.92 Source: Data Flow Task Post Execute: 100% complete End Progress Progress: 2006-08-16 18:59:41.92 Source: Data Flow Task Cleanup: 0% complete End Progress Progress: 2006-08-16 18:59:41.93 Source: Data Flow Task Cleanup: 33% complete End Progress Progress: 2006-08-16 18:59:41.93 Source: Data Flow Task Cleanup: 66% complete End Progress Progress: 2006-08-16 18:59:41.93 Source: Data Flow Task Cleanup: 100% complete End Progress Progress: 2006-08-16 18:59:41.95 Source: Send Mail Task The SendMail task is initiated.: 0% complete End Progress Progress: 2006-08-16 18:59:42.09 Source: Send Mail Task The SendMail task is completed.: 100% complete End Progress DTExec: The package execution returned DTSER_SUCCESS (0). Started: 6:59:40 PM Finished: 6:59:42 PM Elapsed: 1.984 seconds
When I try to use the same command within SQL Server Agent Job using a CmdExec step I get the following error...
Description: The package is attempting to configure from the XML file "S:connectionscontacts.dtsConfig". End Info Warning: 2006-08-16 18:40:03.15 Code: 0x80012012 Source: contactsPackage Description: The configuration file name "S:connectionscontacts.dtsConfig" is not valid. Check the configuration file name. End Warning Warning: 2006-08-16 18:40:03.15 Code: 0x80012059 Source: contactsPackage Description: Failed to load at least one of the configuration entries for the package. Check configurations entries and previous warnings to see descriptions of which configuration failed. End Warning Info: 2006-08-16 18:40:03.20 ... Process Exit Code 1. The step failed.
If you write a query that outputs XML, you'll have a long string in the Results window/tab that is also a link. Clicking on this link opens a new tab in SSMS with the formatted XML string easier to read.
My issue is that clicking on the link opens the "formatted XML string" tab ON THE SAME tab as my code and no way to go back to my code after that ... I need to reload the query from disk, if saved, to erase the "formatted XML" window.
I have to set up log shipping from Prod server "A" to 2 different DR servers ("B" and "C")...What do i have to do differently (or additional) using the GUI (ie not using Tsql Scripts) to accomplish this, in addition to the steps that are done to log ship to just one DR server?
On a windows 2003 server R2 x64, i had the Framework.net 3.5, Sql Server 2005 express (instance s423553 on NT SERVICENetwork) and Visual Web Developper 2008.
I installed Sql Server Express 2008 Express (install .SQLexpress with the local account sqlserv10). The first time (with the account NTSERVICENetwork), it failed. I uninstalled it and Sql Server Management Studio 2005. The second time, the install end properly. But, can't find SSMS. On a MsSql2008 microsoft page, i've found a link for SSMS 2005. So i installed it.
But, on this server, Sql Server Management Studio Express 2005 does not work with Sql Server 2008. I tried to use the 2008 Instance with Visual Web Developper 2008 but it didn't work (only compatible with sql server 2000, 2005....).
When I execute the following command, I get the output truncated to 79 characters, including three dots (as an ellipsis, I suppose).
EXEC master..xp_cmdshell 'powershell.exe "Get-ChildItem D:Databazepaleontologieprilohyverejneg -filter g417*.* -recurse | select Fullname | out-string -width 255"'When I execute the core command directly in Powershell, whether the text or ISE version, it works correctly, with or without the out-string -width command.
Get-ChildItem D:Databazepaleontologieprilohyverejneg -filter g417*.* -recurse | select Fullname | out-string -width 255What does it take to get SSMS to not truncate my output strings?
I have a specific requirement. I need to insert the DML statements executed from Management Studio into a SQL table. We have SQL Server 2008 R2 and 2012 instances.
is there a way to see the data of a table variable in the SSMS debugger? For example, if I set a breakpoint in SSMS and look at a populated table variable named @MyTable in the Locals tab at the bottom of the IDE, a value of "(table)" is displayed. There does not appear to be a way to expand or drill into this variable in the debugger to see the data. Do you know if there's a way to do this through the debugger or do you use an alternate approach when using the SSMS debugger?
Because of a performance problem, somebody has given me a script which came from a SSRS report.
The code as supplied does not work when multivalued parameters are used.
Testing/tuning/building in SSMS is far superiour than in SSRS. So that's why I like to use SSRS for building the code/script/sql-statement. Offcourse parameters have to be set correctly. (That is no problem). Splitting of the multivalued parameter is not a problem either.
When I go to open a file in SSMS, the Open File dialog box appears. In the left side of that box, there is a panel containing shortcuts to various locations: Desktop, My Projects, and My Computer. I would like to add some shortcuts to folder that I use in this area, but I haven't been able to figure out how. how to do this?
I've created an SSRS report in Visual Studio 2005 which runs fine in the preview. I have a list of server names which when clicked on expands to a list of databases on the chosen server. When I click on a database it then queries a table of information and displays information on that database. The query is something like:
SELECT * FROM MyDB.dbo.DB_info WHERE ServerName = @ServerName and DBname = @DBname
This works fine in preview mode. When I run it in SQL Management Studio via the Custom Reports menu it only works when the chosen server is the one that contains MyDB. I've run a trace and can see the query being issued when I use preview mode. However, when I run it from SSMS I don't see any query being issued to the database. This seems strange as it does return data in the instance described above and if I put MyDB into single-user mode it then complains that it can't access it so it is obviously trying to communicate with the database but not showing up on the trace. I've selected all events and only put a filter on the database name.
Seeing as all of the information is in the same table it seems odd that they query only works when the server name is the one where the database is held and that the query is not shown up in a trace. I'm running the report under the same credentials too.
I'm completely baffled by this so I'd be very grateful if someone could explain what might be happening here.
Can anyone tell me how to enable bcp utility. Because anytime I try to run BCP I get the following error messages: (BCP not enable). Also how can I get to SQL Server command prompt? I look forward to hearing from anyone.
I have a Microsoft SQL server that is setup, and inside it is a job. Inside the job is a bunch of lines of transact sql statements.
I would like to be able to start the job from the command prompt. Moreover, I do not want to have to create new files or modify anything that is existing. I just want to be able to have it set up so that a new user who installs the server and all of the job on their machine fresh, can just punch in the command to run the job.
I know I can copy the transact sql statements from the job, and run them through the command prompt, but that would be a poor way to do it, because if the statements in the job were ever modified, with this task being automated, the command prompt statements would be out of date.
So I just need a way to start the existing job from the command prompt. Is this possible? Maybe with OSQL?
I am have trouble configuring developer edition when trying to run a Bulk Copy through Command Prompt. I have configured remote connections within the surface area configuration and have created a firewall exception
I enter and run
bcp FileImportDB..Exam in "c:FileImportPracticeExamImportFile.txt" -T -c
then i get the following error message
Microsoft Windows [Version 6.0.6000] Copyright (c) 2006 Microsoft Corporation. All rights reserved.
C:UsersDineen>bcp FileImportDB.Exam in "c:FileImportPracticeExamImportFile.t xt" -T -c SQLState = 08001, NativeError = 2 Error = [Microsoft][SQL Native Client]Named Pipes Provider: Could not open a connection to SQL Server [2]. SQLState = HYT00, NativeError = 0 Error = [Microsoft][SQL Native Client]Login timeout expired SQLState = 08001, NativeError = 2 Error = [Microsoft][SQL Native Client]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.
I am running developer edition on vista and Service pack 2, iam connecting to my instance with no problem.
I've been working with Database Mail for some time but I haven't seen this one before. I have a maintenance plan that does the following:
1. Check database integrity On Success: 2. Perform backups On Success: 3. Perform a maintenance cleanup On Success: 4. Notify operator of success
Steps 1 - 3 are also linked (via On Failure arrows) to a singular Notify Operator of Failure task.
The maintenance plan does exactly what I want it to, and if everything goes correctly, it successfully sends an email to an operator.
If steps 1 - 3 fail, the job ends in an error state, but does not trigger the Notify Operator of Failure task.
I was able to recreate the problem by creating another maintenance plan on the same instance with the same steps. Its "Notify Operator" on failure task also doesn't work.
It occurs to me that maybe I'm missing something, so here's some of the details of my SQL server:
SQL 2008 + SP1 (10.0.2531) x64 on Windows 2008 R2 DB Mail profile is public and default
I do have a slightly unusual profile, in that it uses two accounts:
1. A connection to a SharePoint SMTP service (where it catches emails directed at document libraries)
2. A connection to a UNIX-based smtp server (which routes mail to regular mailboxes and my SQL DBA mailing list)
The Profile will attempt to send to the SharePoint server first. The SharePoint server does not relay. If the document library email address doesn't exist, SQL will raise a warning (in the Database Mail log) and the profile will use the second account on the list, which is a real mail server and can relay the message to any mailbox.
It works really well, actually. When the maintenance plan completes successfully, the message is sent to the drop folder on the SharePoint server, and SharePoint routes the email to the correct library, and we have a central archive of all DB Mail notifications.
But if the job fails (for example, if the backup disk is out of space), none of this happens. According to the log, the job doesn't even try to send a notification. Looking at the DBMail log, the Mail service does not start. No email is delivered to the drop folder of the receiving SMTP server. So I don't think my Database Mail configuration is the problem here. It is apparently something to do with the way the job itself handles errors.
The program shortcut "Visual Studio 2005 Command Prompt" seems to be mising from the "Visual Studio 2005 Command Prompt" Start menu added by the current Microsoft SQL Server 2005 Express Edition Toolkit. Where is it? How to workaround?
I am trying to do Download details SQL Server 2005 Samples and Sample Databases (April 2006) -> GettingStartedWithSQLSamples.htm which says "a. Open a Microsoft Visual Studio 2005 command prompt. Click Start, point to All Programs, point to Microsoft Visual Studio 2005, point to Visual Studio Tools, and then click Visual Studio 2005 Command Prompt." but I can find no such command prompt within "Visual Studio Tools", only "Visual Studio 2005 Remote Debugger{, Configuration Wizard}".
What's wrong? How to fix or workaround? I'd install .NET SDK 2.0 to get it's Command Prompt but that's about 570MB merely for a command prompt!
I'm trying to quantify the number of times folks use SQL Server Management Studio to change client data in one of our production databases. Does SQL Server keep this statistic? How do I get to this data?
I have a requirment, when a user logs in to the pc, I want to run some SQL scripts which is been saved as a stored procedure on another pc. Could this be possible. I know in oracle you could do it using SQLLDR. Is there any way to do it in SQL? Please advice. Any help is greatly appreciated.
Is there a way by which we can execute the backing up of database from the command prompt......
Suppose i need to do the below task. I can write a stored procedure and schedule it. But the situation demands to run from the command prompt. Is there a way out.
-- Create the backup device for the full MyNwind backup.
We have a development server that will only run SQL Server from a DOS command prompt. Each time I reboot the server the services don't autostart, and I am unable to start them manually using Service Manager. Does anyone know what I can do to fix this problem? Thanks