Microsoft SQL Server

159K

Solutions

49K

Contributors

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

Share tech news, updates, or what's on your mind.

Sign up to Post

I have a table with two columns containing a varchar(max) column (`vertices`) and a geography datatype column (`geography`).

in the varchar(max), i have a saved google maps polygon JSON string:

[{"lng":-82.54841995396418,"lat":27.36429736498091},{"lng":-82.54730415501399,"lat":27.35995237468737},{"lng":-82.54224014439387,"lat":27.360257291850118},{"lng":-82.53760528721614,"lat":27.363611325218592},{"lng":-82.54352760472102,"lat":27.365135852250216},{"lng":-82.54841995396418,"lat":27.36429736498091}]

I'm trying to convert this into a SQL Server geography data type using an update script:

   
 UPDATE [AOI]
    SET [geography] = geography::STGeomFromText('POLYGON(('+vertices+'))', 4326)
    END

Open in new window


This isn't working.
How do I make the conversion?

Thanks
0
Technology Partners: We Want Your Opinion!
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Step : 1  - I am doing bulk  insert    -- This works

step 2 : I need to retrieve BillId


CREATE TABLE #T (IntCol int, XmlCol xml); 

INSERT INTO #T(XmlCol)  
SELECT * FROM OPENROWSET(  
   BULK 'c:\Testing\Test.xml',  
   SINGLE_BLOB) AS x;  
   
      select * from #t   

<filename>
<Transaction>
<Billid>100</BillId>
</Transaction>
<Transaction>
<Billid>200</BillId>
</Transaction>
</filename>

Open in new window

0
I have the following query to retrieve data based on the newest date.  I now need to do the same thing, however I need the second newest date.  I have been hacking around with no luck so far.

Thanks

SELECT        TOP (100) PERCENT Company_Code, Well_Code, Main_Account, Sub_Account, MAX(Journal_Date) AS Newest_Journal_Date
FROM            PPPCo.dbo.GL_Detail
WHERE        (LTRIM(RTRIM(Main_Account)) = 510) OR
                         (LTRIM(RTRIM(Main_Account)) = 830)
GROUP BY Company_Code, Well_Code, Main_Account, Sub_Account
0
how do I join two queries, join them HORIZONTALLY, i.e. extra columns, second columns query 2 to right of first query



--query 1 OUTPUTS
SELECT a.timeStampKey, t.timeStamp,MAX(CASE WHEN a.PIE2_O_ID = 1 THEN value END) AS 'K_DESIGN_SG_A_AVERAGE_A', MAX(CASE WHEN a.PIE2_O_ID = 2 THEN value END) AS 'K_DESIGN_SG_B_AVERAGE_B'FROM   tblOutputs as a INNER JOIN       tblTimeStamp as t ON a.timeStampKey = t.timeStampKey GROUP BY a.timeStampKey, t.timeStamp
--query 2 INPUTS
SELECT a.timeStampKey, t.timeStamp,MAX(CASE WHEN a.PIE2_I_ID = 10104 THEN value END) AS Flow, MAX(CASE WHEN a.PIE2_I_ID = 10006 THEN value END) AS Head FROM   tblInputs as a INNER JOIN       tblTimeStamp as t ON a.timeStampKey = t.timeStampKey GROUP BY a.timeStampKey, t.timeStamp
0
Hi all.

I have a 2003 AD forest, all Domains are 2008 R2. Forest has 2 domains -  one.two.com
I have SQL servers in domain two.com. My account mike@two.com has access to SQL servers.

Here's my question. If I migrate my user account to domain 'one', would I by default still have access to my sql server?

Thank you for looking
0
Using SQL SERVER 2008 R2

I have a script that does some table renames so I need "GO" between the Renames.  I do some checks at the top to verify the table content appears normal before I allow the script to proceed.  However, this will not run because the "GO" cancels out the label at the end that I want to jump to and causes an error.

What is the best way to write this to accomplish this?

Thanks,


DECLARE @RecCount Int

SET @RecCount = (SELECT COUNT(*) FROM TEST_SAPbacklogInputTabDlm)

IF (@RecCount = 0)  
	BEGIN
		PRINT 'ERROR - ZERO RECORDS in SAPbacklogInput'
		GOTO EXIT_ME
    END;

-- Check if the file size is less than 80% of previous days file
DECLARE @OldCount Int
DECLARE @NewCount Int

SET @OldCount = (SELECT Count(*) AS OldCount FROM TEST_DashboardData) 
SET @NewCount = (SELECT Count(*) AS NewCount FROM TEST_TheNewDashboardData) 

IF (@NewCount / @OldCount) < 0.8  
	BEGIN
		PRINT 'New File Size Less Than 80% Of Old File'
	    UPDATE FileCheck  
        SET ErrorFlag = 'Y',  
            JobEndFlag = 'N', 
            Comments = 'New File Less Than 80% Of Old File'  
        WHERE FileLocation LIKE '%PeruOpenOrderExtract.xls'
        GOTO EXIT_ME
    END;


-- Looks like we are good to rename tables.

 
UPDATE FileCheck 
SET LastStepCompleted = 'Renaming Files', ErrorFlag = 'N', JobEndFlag = 'Y', RenameFlag = 'Y'
WHERE FileLocation LIKE '%OpenOrderExtract.xls' 
	   
	  
Print 'DashboardData table Renames...'
GO

execute sp_rename 

Open in new window

0
I have installed SQL Server 2016 with  all the features, but when I open the management studio I am unable to connect to SSIS, all other services are availble and the SSIS  service is running. I read an article which suggested using Dcomcnfg.exe to add users to the permission for integration services which i have done. still unable to connect though

SQL server connector - Browsing for servers
0
Hi

I have to generate a report in PDF from a table in SQL Server in a VB.Net app. I have defined a report (.rdlc file), but I don't know how to generate the report. I have only found a MS article on LocalReport, but the code shown in it does not work, I'm using VisualSudio 2013.

Can someone guide me through the process ? Or should I make a WinForms app instead ?

Thnaks for help
Bernard
0
Hi Experts,

I would like to transfer over a DB to someone.
1-What is the command to execute to make a bak of that file to a given destination?
2- Will that person be able to restore that db without having my login/pwd?
3- In order to create that backup I had to run the following (as all views were converted by the import utility as tables with the view's names..)
select 'sp_rename ''['+name+']'', '''+replace(name,'view_','')+''';'+ char(10)+CHAR(13)+ 'go' + char(13) from sys.tables where type='U';

Open in new window

However the I had to go to every single line and click enter before the GO, how can I avoid that?

Thanks in advance
0
I Need to find all the Wordpress Post that do not have a specific meta data value

select p.`ID`,m.meta_key,m.meta_value 
from wp_posts p, wp_postmeta m 
where p.post_status ='publish' and 
p.`ID`= m,post_id in (select post_id from wp_postmeta where  meta_key = 'DocID');

Open in new window


gives all the published posts with a DocID.
I need to filter it further on another m.meta_key that is missing from some posts

in sudo code it would look something like
select p.`ID`,m.meta_key,m.meta_value 
from wp_posts p, wp_postmeta m 
where p.post_status ='publish' and 
p.`ID`= m,post_id in (select post_id from wp_postmeta where  meta_key = 'DocID')
and not exists (m.meta_key = 'wanted')

Open in new window


Hoping this makes sense and someone  can show me a better way
0
On Demand Webinar: Networking for the Cloud Era
LVL 9
On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Hi Guys,

I have got two databases on a server. Both of them have got the same amount of data, structure and stored procedures are also the same. One is for my internal testing and the other is for the dev testing.

For some reasons the same query on dev testing database calle (Spaces_SBDeployment) is very slow as compared to my own dev/test database (Spaces).

Please find below the images of the query execution results and time taken from both the databases using the same stored procedure.

This is the shared dev database which is very slow takes 21 seconds to bring back results
Shared dev database
This is the database for my own dev/test  takes 3 seconds to bring back the results for the same query and from the same amount of data
Internal dev/test database
Kind regards
0
Hi there.

I moved a web database app from a standard webhotel with classic asp and MySQL to a dedicated Microsoft server with SQL Server. There is a login page that check the user credentials in a database table in order to proceed. That used to work fine on the standard webhotel.
Now it won't work. I created a user that works fine in SQL Server Management. I used a standard connection string and included it in the login page. The page loads fine, but then the user credentials is submitted, then I just gives a blank page. Nothing to see in F12. I suspect that there may be an issue with some port permissions.
Anyone out there who knows what port that should be open and how to open it? Any  other suggestion to solve this problem?

Best regards

Ulrich
0
hi,

I am studying diff in Oracle and MS SQL, do they have any connection limit?
0
I am trying to create backpac on sql server 2012 db.
I get this error.
I am confused by this, as I don't understand what is wrong that I am getting these errors. It is a simple db.

TITLE: Microsoft SQL Server Management Studio
------------------------------

One or more unsupported elements were found in the schema used as part of a data package.
Error SQL71564: The element Extended Property: [dbo].[InvoiceHeader].[PFCustomerOrderNumber].[MS_Description] is not supported when used as part of a data package (bacpac).
Error SQL71564: The element Extended Property: [dbo].[OrderHeader].[AmazonPO].[MS_Description] is not supported when used as part of a data package (bacpac).
Error SQL71564: The element Extended Property: [dbo].[OrderLine].[ASIN].[MS_Description] is not supported when used as part of a data package (bacpac).
Error SQL71564: The element Sequence: [dbo].[RC01_TransactionID] is not supported when used as part of a data package (bacpac).
Error SQL71564: Table Table: [dbo].[RC01_Sheet1] does not have a clustered index.  Clustered indexes are required for inserting data in this version of SQL Server.
Error SQL71564: Table Table: [dbo].[PF_Sales_Orders] does not have a clustered index.  Clustered indexes are required for inserting data in this version of SQL Server.
Error SQL71564: Table Table: [dbo].[OrderImport_1] does not have a clustered index.  Clustered indexes are required for inserting data in this version of SQL Server.
Error SQL71564: Table …
0
Hey all I have the following SQL Query that I am using in order to get comma separated values from a cell:

WITH cte AS(
    SELECT        
        uT.id AS UID, 
		uT.employeeID, 
        uP.type, 
		pP.name 
    FROM 
		usersTbl AS uT
    CROSS APPLY 
		dbo.DelimitedSplit8K(uT.userPerms,',') AS uPcommaItems
    INNER JOIN 
		usersPermissions uP ON uP.id = uPcommaItems.Item
	CROSS APPLY 
		dbo.DelimitedSplit8K(uT.userPermPages,',') AS pPcommaItems
    INNER JOIN 
		pagePermissions pP ON pP.id = pPcommaItems.Item
)
SELECT DISTINCT 
	UID, 
    employeeID,
    STUFF(
			(
				SELECT 
					',' + stuff1.type
				FROM 
					cte AS stuff1
				WHERE 
					t.UID = stuff1.UID 
				FOR XML PATH(''), TYPE
			).value('.', 'NVARCHAR(MAX)'), 1, 1, ''
		  ) AS userPermissions,
	STUFF(
			(
				SELECT 
					',' + stuff2.type
				FROM 
					cte AS stuff2
				WHERE 
					t.UID = stuff2.UID
				FOR XML PATH(''), TYPE
			).value('.', 'NVARCHAR(MAX)'), 1, 1, ''
		  ) AS pagePermissions
FROM 
	cte AS t

Open in new window


The userTbl table looks like this:
------------------------------------------------
ID    | employeeID | userPerms | pagePermPAges
------------------------------------------------
15    | 3FdFieu9I  | 1,3       | 1,4,5,6

Open in new window


The userPermissions table looks like this:
----------------
ID | type
----------------
1  | Read
2  | Write
3  | Upload
4  | Admin

Open in new window


And this is my pagePermissions table looks like:
----------------
ID | name
----------------
1  | bindex
2  | flight
3  | submit
4  | form
5  | information
6  | myPage
7  | register

Open in new window


My current output of the query above is this:
-------------------------------------------------------------------------------------------------------
ID    | employeeID | userPermissions                         | pagePermissions
-------------------------------------------------------------------------------------------------------
15    | 3FdFieu9I  | Read,Read,Read...upload,upload,upload...| Read,Read,Read...upload,upload,upload...

Open in new window


It should read this though:
-------------------------------------------------------------------------
ID    | employeeID | userPermissions | pagePermissions
-------------------------------------------------------------------------
15    | 3FdFieu9I  | Read,Upload     | bindex,form,information,myPage

Open in new window


Any MS SQL guru out there that can help me out?
0
I have a sql statement that subtracts two numbers:
CASE ALLOCATED WHEN NULL  THEN QTY_ON_HAND - ALLOCATED ELSE QTY_ON_HAND END AS On_Hand_Less_Allocated

Qty on Hand    Allocated   On_Hand_Less_Allocated
10                        6                       4
4                          6                       2  (should be -2)

What am i doing wrong?
0
Hi EEE:


I have a dataset of units being bought and/or sold. The deal ID is determined by column D and you may buy and sell units under the same deal. Column E is just some descriptive for the deal.  

Original dataset

D  E  BS B_VAL  S_VAL

d1 e1 B  200  null
d1 e1 S  null 100
d2 e2 B  500  null


Evaluate if B_VAL > S_VAL given same D then return one row only but with BS as B and keep both the B_val and S_val on the same row.

d1 e1  B 200 100
d2 e2  B 500 null
0
Hi out there.

I have a - supposedly - basic issue.  I have a SQL/web-server with a general login for myself. If I use that in a connection string, it doesn't work. I wonder if I need to create a login/user via the SQL Server Management Studio?
Can anyone please give me a quick guide how to create a user/login I can use in a connection string like this:

"Driver={SQL Server};Server=SERVERNAME\SQLEXPRESS;Database=databasename;Uid=username;Pwd=password"

Thank you.

Best regards

Ulrich
0
Hello,
How can i add a field in a table  at particular position.

Is it possible to avoid dropping the table and recreating it.

Regards
0
New benefit for Premium Members - Upgrade now!
LVL 9
New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Hi ,

Please provide detailed procedure for In-Place Up-gradation from MS SQL Server 2012 to MS SQL Server 2014 include MSBI Components like SSIS Packages, Cubes/OLAP Databases and SSRS Reports.

Please consider this high priority.

Thanks,
Chandra
0
I need the strings below to build properly...

As
'Name1','name2','name3'

This is not building correctly

IF @strTourStatus = ''
    BEGIN
        SELECT @strTourStatus = COALESCE(@strTourStatus + ',', '') + '''' + TourStatus + ''''
        FROM   (   SELECT DISTINCT TourStatus
                   FROM   everyware_t_tour WITH ( NOLOCK )
               ) pc;
    END;
0
I have a simple  .Net web form that submits to a SQL Server Stored Procedure

In the stored procedure I insert a persons data into the Individuals table

I am currently setting the following
DECLARE @IndividualID BIGINT
.... Insert form data to table.....

SET @IndividualID = @@Identity()

I then use that Variable to insert into additional sub tables
IndividualsToUsers
IntividualToAddress
etc...

Is @@Identity correct?
Or should it use Scope_Identity?

It also returns @IndividualID to the Form.
That is not used elsewhere but just with a If CInt(dR("IndividualID").ToString()) > 0
TO let us know the data was inserted with no error
0
Every day we set up several company codes.  I want to find out when we will run out.
The company codes are 6 digit numbers, so will run out when we get to 999999.
The table has the company code and the createdate in YYYYMMDD format.
Therefore, it's easy to see how many codes are created each day.
How do I see how many have been set up in a week or a month.
I'm using SQL Server 2012.
0
Hi Experts, I have two tables named, Invoices and Establishment. I'm using codes below to obtain the data but unfortunately the codes is not working. How to correct the codes? Thank you!

Set rs = cn.Execute("Select i.INVOICENO, i.DATEPURCHASE, i.EncodedBy from Invoices i inner join Supplier s on i.VNDRID=s.Establishment")

Open in new window

0
I am trying to create a function, but Intellesense keeps telling me there are errors. Can you help me to fix these errors?
The issue is in this section: SELECT display_term FROM sys.dm_fts_parser(N'FORMSOF(FREETEXT, "' + @searchTerm + '")', 1033, 0, 1)

For some reason Intellesense  does not like the @searchTerm variable.

This code is from a sample code in a blog:

CREATE FUNCTION [dbo].[HighLightSearch](@contents NVARCHAR(MAX),
  @searchTerm NVARCHAR(4000), @style NVARCHAR(4000), @maxLen INT)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @c NCHAR(1)
    DECLARE @len INT = 0
    DECLARE @l INT = 0
    DECLARE @p INT = 0
    DECLARE @prevPos INT = 0
    DECLARE @margin INT
    DECLARE @term NVARCHAR(4000)
    DECLARE @retval NVARCHAR(MAX) = ''
   
    DECLARE @positions TABLE
    (
        S INT,
        L INT
    )

    -- find all occurrences of the search term

    DECLARE cur1 CURSOR FOR
    SELECT display_term FROM sys.dm_fts_parser(N'FORMSOF(FREETEXT, "' + @searchTerm + '")', 1033, 0, 1)
   
    OPEN cur1
    FETCH NEXT FROM cur1 INTO @term

    WHILE @@FETCH_STATUS = 0
    BEGIN
        WHILE 1 = 1
        BEGIN
            SET @p = CHARINDEX(@term, @contents, @p)
            IF @p <= 0 BREAK
           
            SET @l = LEN(@term)
           
            IF @p > 0 BEGIN
                SET @c = SUBSTRING(@contents, @p - 1, 1)
                IF @c <> ' ' AND @c <> NCHAR(9) AND
                   @c <> NCHAR(13) AND…
0

Microsoft SQL Server

159K

Solutions

49K

Contributors

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.