Integrate R script into SELECT query

So I have the following integrated R code which I use to be able to match stuff via regex without too much complication (what I want to do eventually is more complex than this example so regex is pretty much required, this is just the first step): DECLARE @in_adcn nvarchar(500) DECLARE @out_ou nvarchar(500) SET @in_adcn […]

How to create SQL Server table from dplyr pipeline

Due to a bug in dbplyr, copy_to and compute are currently not working for SQL Server connections. connStr <- “driver=ODBC Driver 13 for SQL Server;server=localhost;…” db <- DBI::dbConnect(odbc::odbc(), .connection_string=connStr) copy_to(db, mtcars) #Error: <SQL> ‘CREATE TEMPORARY TABLE “mtcars” ( # “row_names” varchar(255), # “mpg” FLOAT, # … # nanodbc/nanodbc.cpp:1587: 42000: [Microsoft][ODBC Driver 13 for SQL Server][SQL […]

How do I ensure SQL Server replication is running?

I have two SQL Server 2005 instances that are geographically separated. Important databases are replicated from the primary location to the secondary using transactional replication. I’m looking for a way that I can monitor this replication and be alerted immediately if it fails. We’ve had occasions in the past where the network connection between the […]

Split SQL query into a nested query (subquery)

I am having difficulty converting the following into a sql statement with a subquery: SELECT “AM-Martin”.dbo.CpCore_Site.Number , “AM-Martin”.dbo.CpCore_Site.Latitude , “AM-Martin”.dbo.CpCore_Site.Longitude , “AM-Martin”.dbo.CpSm_Face.RetiredOn , “AM-Martin_bin”.dbo.CpCore_Image.Bytes , “AM-Martin”.dbo.CpCore_Site.Name , “AM-Martin”.dbo.CpCore_Site.Zipcode FROM “AM-Martin”.dbo.CpCore_Site INNER JOIN “AM-Martin”.dbo.CpSm_Face on “AM-Martin”.dbo.CpSm_Face.SiteId = “AM-Martin”.dbo.CpCore_Site.Oid WHERE “AM-Martin”.dbo.CpSm_Face.RetiredOn LIKE ‘%9999%’ AND “AM-Martin”.dbo.CpCore_Site.Number LIKE N’%LA%’ OR “AM-Martin”.dbo.CpCore_Site.Number LIKE N’%LC%’ OR “AM-Martin”.dbo.CpCore_Site.Number LIKE N’%BH%’ AND “AM-Martin”.dbo.CpCore_Site.Latitude […]

Send mail with parameters from stored procedure

I already know how to send emails using sql server mail, what I do for this is create a table and then I use something like this for the table body: set @Body = ( ColName as [TD], Col2Name as [TD] from tableName for xml raw (‘tr’), elements) That works fine but now I don’t […]

Hierarchical Database Driven Menu in MVC

I use the code below as an HTMLHelper which gets data from the database and loops over it to display a menu. This is fairly straightforward as you can see however, what if you have a database table using the adjacent model of hierarchies eg/ID, ParentID, OrderID. Easy to see whats going on but recursion […]

Best Approach to Brownfield Database Development in SQL Server 2000/2005

I’ve recently taken over development on a SQL Server 2000 database that needs some help. We’re planning on upgrading it to SQL Server 2005 soon. This database has no audit fields on the tables (CreatedBy, CreatedDate, etc.), no foreign keys, and terrible overall design. There are half a dozen programs that directly access the database […]

ASP.NET – SQL Process Design Connect to Multiple DBs

Scenario Web server running an ASP.NET site that’s connected to a sql server instance for all the web related DB needs (products, pricing, users, etc.)… The companies accounting, inventory control(FIFO, etc.) and whatnot are mainly done on another system which uses a different SQL server…much more complex, for obvious reasons. What I Need What I […]

Failed to open Crystal Reports

When I try to run crystal reports the following Error is appears: Transaction (Process ID 159) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction Note: Report is connect directly to stored procedure.

SQL Server 2005 Issue Column name or number of supplied values does not match table definition

I wish to DELETE the data from a table before performing an INSERT INTO, however I keep recieving an error stating: Insert Error: Column name or number of supplied values does not match table definition. I’ve also tried defining the columns the data should be entered into as part of the INSERT INTO statement, but […]

MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.