天道酬勤,学无止境

sql-server

How do I resolve this "Access is denied" error which occurs when using xp_cmdshell in Microsoft SQL?

Question This is my entire routine: Declare @AttFileType as char(5), @HQCo as int, @FormName as Varchar(15), @KeyID as VarChar(10), @UniqueID as uniqueidentifier, @FilePath as Varchar(100), @StringCommand as Varchar(200) Declare @AttID as int DECLARE @cmd as VARCHAR(500) DECLARE @cmd2 as VARCHAR(500) CREATE TABLE #tmp(eFileName VARCHAR(100)); INSERT INTO #tmp EXEC xp_cmdshell 'dir /B C:\Users\*****\Desktop\Test_Images'; Declare @FileName varchar(100) Set @UniqueID = NewID() While (Select Count(*) From #tmp where eFileName is not null) > 0 Begin Select Top 1 @FileName = eFileName From #tmp Set

2022-07-11 09:54:59    分类:技术分享    sql   sql-server   xp-cmdshell

SQL Server 2014 Password Won't Change

Question I recently set up SQL Server 2014 Express on my computer. During the installation process I merely selected Windows authentication, assuming I would be able to create other accounts later. I tested several things with C# using integrated authentication. The problem arose when I attempted to use an account. I logged in using Windows authentication to change the sa password in SQL Server Management Studio. But when I attempt to so, and close the dialog, open it again, then the password is back to the 15 character default which I do not know. Enforce Policy is not on and I have no idea

2022-07-11 09:50:07    分类:技术分享    sql-server

Trigger - is it necessary BEGIN / COMMIT TRAN

Question I want to make INSTEAD OF trigger like this: CREATE TRIGGER [dbo].[DeleteCompany] ON [dbo].[Company] INSTEAD OF DELETE AS DECLARE @CompanyID int SELECT @CompanyID = deleted.CompanyID FROM deleted BEGIN TRAN DELETE FROM Project WHERE CompanyID = @CompanyID DELETE FROM CompanyPerson WHERE CompanyID = @CompanyID UPDATE PersonCompany SET CompanyID = null WHERE CompanyID = @CompanyID DELETE [Company] FROM DELETED D INNER JOIN [Company] T ON T.CompanyID = D.CompanyID COMMIT TRAN So, I can be sure, that these actions is one atomic action. But it make sense or TRIGGER always execute inside

2022-07-11 09:41:37    分类:技术分享    sql-server   triggers   azure-sql-database   sql-server-2014

Substring after a space

Question I want to split a string and get the value after the space. I can get the value before space but how to get the string after space. DECLARE @string varchar(20) SELECT @string = 'Test Name' SELECT SUBSTRING(@string, 0, CHARINDEX(' ', @string)) Edit: I am using SQLServer 2012 Answer1 Here's a version using SUBSTRING and CHARINDEX for versions of SQL SERVER prior to SQL Server 2016 when STRING_SPLIT was introduced. Either of the following works: SELECT SUBSTRING(@string, CHARINDEX(' ', @string) +1, 20) SELECT SUBSTRING(@string, CHARINDEX(' ', @string) +1, DATALENGTH(@string) - CHARINDEX(

2022-05-17 05:19:05    分类:技术分享    sql-server   string   sql-server-2012   substring

EntityCommandExecutionException Timeout Expired Only Sometimes

Question I'm using Entity Framework 5 to connect to SQL Server 2008. I've imported a stored procedure into my project with no issues. The stored procedure has executed in the past with no issues at all up until I switched from using .GroupBy().First() in LINQ to get distinct results to getting unique results by using an OUTER APPLY in the stored procedure. I switched to using an OUTER APPLY on SQL Server from the .GroupBy() in LINQ because, of course SQL is much faster. Here is my SQL: @USERID VARCHAR(MAX) = NULL, @ITEMTYPE VARCHAR(MAX) = NULL, @ITEMSUBTYPE VARCHAR(MAX) = NULL, @ITEMGROUP

2022-05-17 04:50:14    分类:技术分享    c#   sql-server   entity-framework   linq

SqlDependency vs SQLCLR call to WebService

Question I have a desktop application which should be notified on any table change. So, I found only two solutions which fits well for my case: SqlDependency and SQLCLR. (I would like to know if there is better in .NET stack) I have built the both structure and made them work. I only able to compare the duration of a s̲i̲n̲gl̲e̲ response from SQL Server to the client. SqlDependency Duration: from 100ms to 4 secs SQLCLR Duration: from 10ms to 150ms I would like this structure to be able to deal with high rate notifications*, I have read a few SO and blog posts (eg: here) and also am warned from

2022-05-17 03:02:16    分类:技术分享    sql-server   signalr   sqlclr   sqldependency   database-trigger

Laravel connection MSSqL server works with cli, but not in browser

Question After installing the dll's to use with MSSqL server, the command php artisan migrate ran correctly and the tables were made in the db. So I thought the connection would work for this application. I then used the command php artisan make:auth (which doesn't use the db connection) to scaffold the authentication files. Now when I try to register anyone I get errors: PDOExeption (1/2) Could not find driver and PDOExeption (2/2) Could not find driver (select count(*) from ..... Now I tried to see if using the cli commands still work and both php artisan migrate:rollback and php artisan

2022-05-17 03:01:29    分类:技术分享    php   sql-server   driver   laravel-5.4

There is already an object named '#tmptable' in the database

Question I´m trying to execute stored procedure but I get an issue of an existing temporal table, but I just create one time and use into another part of code SELECT ... INTO #tmpUnidadesPresupuestadas FROM proce.table1 --Insertar in table src.. INSERT INTO table ( ....) SELECT .... FROM #tmpUnidadesPresupuestadas I get this message: There is already an object named '#tmpUnidadesPresupuestadas' in the database. How can I solve it? Regards Answer1 Without seeing more of the code, it's not possible to know if the following situation is your problem, but it could be. When you have mutually

2022-05-16 23:19:04    分类:技术分享    sql   sql-server   stored-procedures

Microsoft SQL Server: Query to see list of all databases, and their corresponding tables, and columns

Question What I have found seems to only show this for one database at a time (e.g., select * from information_schema.columns). Is there something like select * from sys.databases which includes not just all of the database names, but also their own table and column names? Would like to see all of these for the server: database names, table names, column names. I'm trying to get familiar with a server at a new company, and want to just explore by browsing the table names and columns in every database. Also, I do not have write access, and am doing this in Tableau custom SQL. Answer1 Are you

2022-05-16 22:56:05    分类:技术分享    sql   sql-server

Creating a new column based on another column in SQL Server

Question Quick question - I want to create another column in SQL Server such as: Original Table Type Amount LocNum Chocolate 15 WC-10202-01 Banana 10 WC-35209-22 Vanilla 5 WC-15815-15 Ideal Table Type Amount LocNum LocNum2 Chocolate 15 WC-10202-01 WC-10202 Banana 10 WC-35209-22 WC-35209 Vanilla 5 WC-15815-15 WC-15815 Answer1 If you really need to, you can create a computed column. ALTER TABLE yourTable ADD LocNum2 AS ( <your calculation here> ); However, unless you need to index on this calculation for some reason, I'd suggest just adding it as a part of your select output. Answer2 You can use

2022-05-16 22:43:05    分类:技术分享    sql-server