Microsoft SQL Server

158K

Solutions

187

Articles & Videos

48K

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

Hi all.

I have an Excel macro template that used to pull data from our old SQL Server 2000 and populate the Excel file. It would complete this task within 3 seconds.

Now, we have switched the data source to be our SQL Server 2008 server and now the same Excel file takes close to 35 seconds to populate the Excel file. The only line that has changed is the connection string line:

OLD SERVER:
strConn1 = "DRIVER=SQL Server;SERVER=myOLDServer;UID=me;APP=Microsoft Office 2007;WSID=myWork;DATABASE=myOLDDB;Trusted_Connection=Yes"

Open in new window


NEW SERVER:
strConn1 = "Provider=SQLOLEDB;Data Source=myServer;Initial Catalog=myDB;Integrated Security=SSPI;"

Open in new window


What do I have to do to make it run at the same speed as when it was pulling data from SQL Server 2000? The file only populates about 5-10 lines.

Below is the code for populating the Excel file, the connection string is for the SQL Server 2008 data source
Private Sub PopulateExcel()

Dim cnPubs1 As ADODB.Connection
Set cnPubs1 = New ADODB.Connection


Dim strConn1 As String

strConn1 = "Provider=SQLOLEDB;Data Source=myServer;Initial Catalog=myDB;Integrated Security=SSPI;"

cnPubs1.Open strConn1

Dim rsPubs1 As ADODB.Recordset
Set rsPubs1 = New ADODB.Recordset

With rsPubs1
.ActiveConnection = cnPubs1

    Dim I1
    Dim rng1 As Range
    Set rng1 = Range("B8")
    Dim strSQL1 As String

strSQL1 = "SELECT  PO2_PurchaseOrderEntryLine.ItemNumber, PO2_PurchaseOrderEntryLine.Vendor_AliasItemNumber,  

Open in new window

0
Independent Software Vendors: We Want Your Opinion
Independent Software Vendors: 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!

I have a SQL Server table with column FullName VARCHAR(300)

I have a .net import from file process that runs fine except...
Where there are accent characters
It substitutes a ? as it goes into SQL.
Exemple   'PIñERO RODRIGUEZ' goes in as 'PI?ERO RODRIGUEZ'

However..if I run a simple update...
Things update correctly in SQL and can be selected with no issues

UPDATE dbo.Individuals
SET LASTNAME = UPPER('PIñERO RODRIGUEZ')
WHERE IndividualID = 204140


Or I do something simple in SQL like...
INSERT INTO Individuals
VALUES ( UPPER('PIñERO RODRIGUEZ'))   - (Abbreviated code of course...)

Inserts fine

So...

Do I need to change my import Sub (VB.NET) ?

Possibly the "AddWithValue" should actually be this type of format???
oCom2.Parameters.Add(New SqlParameter("@" + header(k), SqlDbType.VarChar, 300)).Value = returnRecords.Rows(i).Item(k).ToString()

This is how its sendingthe insert right now
Dim returnRecords As DataTable = Session("sheetData")
Dim test As String = hdJsonData.Value
 Dim header As String() = test.Split(",")
Dim columnNames As String() = returnRecords.Columns.Cast(Of DataColumn)().[Select](Function(x) x.ColumnName).ToArray()

Using... blah...blah...blah...
oCom2.CommandText = "InsertImportContactStaging"
 oCom2.CommandType = CommandType.StoredProcedure
oCom2.Parameters.AddWithValue("@ImportID", GUID)
oCom2.Parameters.AddWithValue("@ImportRecordType", Session("ImportFileType").ToString() + "")

             …
0
Hello:

Below is my code.  As you'll notice, I have hard-coded the YEAR as '2017'.

The KBUK..GL10110 table contains the YEAR as the field YEAR1, and I have aliased the table as gl.  But, when I try to have gl.YEAR1 represent the YEAR (instead of hard-coding the year), most of the results return NULL.

Why is that, and how can I overcome it?

Thanks!

John


select gl2.ACTIVE, Accts.ACTINDX, gl3.ACTNUMST as [Account], gl2.ACTDESCR as [Account Description], 
'2017' AS [YEAR], Period.PeriodNumber, 
case when Period.[PeriodNumber] = 0 then 0 ELSE ISNULL(SUM(-gl.CRDTAMNT + gl.DEBITAMT + PREV.PERDBLNC-gl.PERDBLNC)-
(gl.DEBITAMT-gl.CRDTAMNT), 0) END as [Opening Balance], ISNULL(gl.DEBITAMT,0) as Debit, ISNULL(gl.CRDTAMNT,0) as Credit, 
ISNULL(gl.DEBITAMT - gl.CRDTAMNT,0) as [Net Change], ISNULL(CASE WHEN gl.PERIODID = 0 THEN gl.PERDBLNC ELSE
SUM(-gl.CRDTAMNT + gl.DEBITAMT + PREV.PERDBLNC-gl.PERDBLNC) END,0) AS [Ending Balance] 
FROM (select distinct ACTINDX from KBUK..GL00105) as Accts
CROSS JOIN (select 0 as PeriodNumber union all select 1 as PeriodNumber union all select 2 as PeriodNumber union all
select 3 as PeriodNumber union all select 4 as PeriodNumber union all select 5 as PeriodNumber union all
select 6 as PeriodNumber union all select 7 as PeriodNumber union all select 8 as PeriodNumber union all
select 9 as PeriodNumber union all select 10 as PeriodNumber union all select 11 as PeriodNumber union all
select 12 as PeriodNumber) as Period
LEFT OUTER JOIN

Open in new window

0
I am importing a file into SQL Server table

Some of the Nast names are Hispanic with the ~ over the n?  

In file it is fine
Once it is in SQL it gets replaced with a ?

AQnt ideas?
0
Hi

Is it possible to drop any key that may exist on a table in SQL using a SQL statement (not manually), or do you have to specifically know what that primary key is called?

Thanks
0
I have Microsoft SQL Server 2012 installed in a production environment.
The TempDB has grown to tak up the size of the C Drive (20GB in growth).
How can I shrink the TempDB in the production environment without impacting live users?
0
Hi

I am trying to drop a primary key in my SQL table [Machines]. The following isn't working

ALTER TABLE Machines DROP CONSTRAINT pk_Machine_Number

This is strange because with SELECT column_name FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
I can see "Machine Number" so I am not sure what I am doing wrong
0
Hi,
Is it better to install Microsoft SQL Server on Azure box or to completely migrate to Azure SQL Server.
Migrating to Azure SQL Server might take long, so will getting used to it.
What are the benefits of moving to Azure SQL Server and are they worth the effort?
Are there any disadvantages of installing and using Microsoft SQL Server on the box instead?
Thanks!
0
Message
The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2017-06-21T11:42:47). Exception Message: Could not connect to mail server. (An attempt was made to access a socket in a way forbidden by its access permissions 10.155.16.253:25)
0
I have a SQL Server 2012 DTS package that receives data from AS400. Before these three days, everything was going well.

But suddenly when I tried to run these error messages appeared:
1. SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on AS400 returned error code 0xC02090F5.  The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.  There may be error messages posted before this with more information about the failure.
2. The AS400 was unable to process the data. CWBCO1054 - A user-specified time-out occurred while sending or receiving data.

Sometimes when I run it manually, it ran perfectly, but sometimes the error messages appeared. I need to run it several times till the error didn't show up. Different when I ran through batch file (task scheduler/sql job agent), I always got fail.

I already checked the connection by test the credentials, ping and telnet to the server. It's okay.

Any idea why this error is coming up? I appreciate any assistance from everyone :)

Thanks a lot
0
Major Incident Management Communications
Major Incident Management Communications

Major incidents and IT service outages cost companies millions. Often the solution to minimizing damage is automated communication. Find out more in our Major Incident Management Communications infographic.

I'm following along with a video by Ben-Gan called Creative Uses of Apply Operator and he shows this code

Select orderiid, orderdate, nextyear
from sales.order
  cross apply(values(year(orderdate))) as a1(orderyear)
    cross apply(values(year(orderyear +))) as a2(nextyear)
where orderyear > 2007

---end code----

He comes up with a result set where the nextyear column is one year later than the orderdate, i.e., if orderdate is 2008-01-01, nextyear is 2009
you can see it video place...

 https://youtu.be/-m426WYclz8?t=1291

However when I try using it with this code on two different databases 2012 AdventureWorks or AdventureworksLT  I use the same code on different column names..


select SalesOrderID,  OrderDate, NextOrderYear
from SalesLT.SalesOrderHeader
      cross apply (values(year(OrderDate))) as a1(orderYear)
            cross apply (values(year(orderYear + 1))) as a2(NextOrderYear)
            where orderYear > 2001

I get the

 NextOrderYear as being 1905

Obviously something is not right. Just not sure what?
0
here is some sample data from a sql server 2008 r2 table named keyinfo

KeyLocation                                KeyDescription
001 EGD Door Access Key            25246 Voucher
001 EGD Door Access Key            298A Door Access
001 EGD Door Access Key            357A Door Access
002 EGD Door Access Key            2341Device Reset
002 EGD Door Access Key            298A Door Access

Trying to write a query that will list each key location once and concatenate all the key descriptions for a given key location into a single column with each one separated by a dash (with space in front and behind).  given the sample data here is the output I'm looking for

KeyLocation                                KeyDescription
001 EGD Door Access Key            25246 Voucher  -  298A Door Access - 357A Door Access
002 EGD Door Access Key            2341Device Reset - 298A Door Access
0
My C# application is showing ominous behaviour.

The application is working perfectly fine for a specific Installation. But it's giving me this exception in another..
- SqlDataAdapter.Fill - Timeout expired.  The timeout period elapsed Prior to executing the query or the Server is not responding.

I've been searching all day for a solution and cant find anything helpful.

The c# Code is

            DataTable dt = new DataTable(tableName);
            try
            {
                using (SqlDataAdapter da = new SqlDataAdapter(SqlQuery, conn))
                {
                    Console.Write(SqlQuery);
                    da.Fill(dt);
                }
            }
            catch (Exception ex)
            {
                throw new Exception(string.Format("Foo String there is nothing good Happening {0}", ex.ToString()));
            }

Open in new window


The SQL is : SELECT * FROM FooTable WHERE ColumnA=0 and ColumnB='15'

Every Google post I've been looking at is about "increase your command timeout". Which makes me almost give up hope.

The same SQL in SSMS Returns only 1 row and I've seriously no idea what is going on.

I've attached a Debugger to the process and can definitely say the code is reaching the Console.Write as a breakpoint.
The exception is thrown at the fill method line.

I'm highly thankful for any responses.
0
Hello experts,


I have a table with id, k, v (id is is, k is key, and v is value)

like :

id           k            v                        <--- columns in a select * fron tempTable
------------------------
A            k1           1
A            k2           2
A            k3           3
B            k1           1
B            k2           2
B            k3           3

SO what I want to do is 

Return the data as a crosstable/PIVOR/something else:

id         k1       k2       k3
-------------------------------------     
A          1         2       3
B          1         2       3

Open in new window


PIVOT not working on earlier versions of oracle database and I want a simple still reliable solution.
I really don't want to use ms access to make a crosstable for this :D
0
I am trying to import a file that is using a "Unit Separator" ... ASCII Value 31 ... as its delimiter.  I am struggling with how to specify that value in a format file.  I have tried many different values but none seem to work.  Anyone run into this before and have a solution?  My last attempt is shown below:


11.0
10
1       SQLCHAR             2       50      "\0x1F"   1     ContextID                                        SQL_Latin1_General_CP1_CI_AS
2       SQLCHAR             2       50      "\0x1F"   2     ContextName                                      SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR             2       50      "\0x1F"   3     ContextParentContextID                           SQL_Latin1_General_CP1_CI_AS
4       SQLCHAR             2       50      "\0x1F"   4     ClientRecordNumberID                             SQL_Latin1_General_CP1_CI_AS
5       SQLCHAR             2       50      "\0x1F"   5     PatientID                                        SQL_Latin1_General_CP1_CI_AS
6       SQLCHAR             2       75      "\0x1F"   6     CustomFieldName                                  SQL_Latin1_General_CP1_CI_AS
7       SQLCHAR             2       50      "\0x1F"   7     CustomFieldType                                  SQL_Latin1_General_CP1_CI_AS
8       SQLCHAR             2       125     "\0x1F"   8     CustomFieldValue                                 SQL_Latin1_General_CP1_CI_AS
9       SQLCHAR             2       50      "\0x1F"   9     …
0
I have been asked to migrate/update an Access/SQL Application so it can run in a VM Environment on a true SQL Server; I have a general understanding to get started, but this is my first VM project, so step one is to plan for the unexpected. I request that you treat me as a novice when making suggestions.

Rather than write a novel about the function of this Access/SQL Application and to minimize suppositions, I would rather respond to questions about it.

To start this off, here is the current configuration ...
Standalone Workstation
Windows 10 64 Bit
Access Pro Plus 2013 (Front End)
SQL 2016 Express (Back End)

What would be the best approach and what pitfalls should be anticipated?
0
Hi,

I've just been told I'm now in charge of and managing two SQL databases and have never used SQL before.

One of them we can remove as it is a backup and is no longer required. How do I safely back this up and then delete it so we can, if required, restore it?

Also: The db has a total size of 6,927Mb. 6021Mb of which is transaction log space. How do I go about reducing this log space? We are nowhere near using up the space allocated to logs but we are short on disk space on the server.

I've tried backing it up by doing a full backup and also a transaction log backup but no reduction in transaction log space.

So I'm looking for 3 things:

1) Reducing transaction log space
2) How to create a full backup that we can restore from
3) How to take the db offline and then delete it from Studio (assume Tasks > Offline, then delete)

Thanks and apologies for the noob questions but this has literally landed on my desk.
0
can any one write a code for me in php... for file uploding and files uploading
0
Dear Experts,

Based on your experience could you please advise in which scenarios worth to use this "SELECT in JOIN" technique:
SELECT
	t1.LIFNR AS VendorNumber,
	t1.NAME1 AS VendorName,
	SubQuery.BUKRS AS CompanyCode
FROM
	AP.LFA1 t1
INNER JOIN
	(SELECT AP.LFB1.LIFNR, AP.LFB1.BUKRS FROM AP.LFB1 WHERE AP.LFB1.BUKRS = 'SX1') SubQuery ON t1.LIFNR = SubQuery.LIFNR

Open in new window


instead of doing the same filtering in WHERE section?
SELECT
	AP.LFA1.LIFNR AS VendorNumber,
	AP.LFA1.NAME1 AS VendorName,
	AP.LFB1.BUKRS AS CompanyCode
FROM
	AP.LFA1
INNER JOIN
	AP.LFB1 ON AP.LFA1.LIFNR = AP.LFB1.LIFNR
WHERE
	AP.LFB1.BUKRS = 'SX1'

Open in new window


Thanks in advance,
0
Migrating Your Company's PCs
LVL 4
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

I want to return the Top 5 vCount for/in each vGroup in this query, but I'm having trouble figuring out where to put it ?

		/****** Region ******/		
		SELECT	vGroup, vType, count(*) AS vCount
		FROM (
		SELECT        'Region visits' AS vGroup, Districts.name AS vType
		FROM             (v_Visits INNER JOIN Customers ON v_Visits.customerId = Customers.id) INNER JOIN Districts ON Customers.districtId = Districts.id
		WHERE        visitTime  between @periodFrom and dateadd(ms, -3, dateadd(day, 1, cast(@periodTo as datetime))) AND v_Visits.userRole like @uRole AND v_Visits.channelName like @channels
		) resSub
		GROUP BY  vGroup, vType

		/****** Brand ******/
		UNION ALL
		SELECT	vGroup, vType, count(*) AS vCount
		FROM(
		SELECT        'Brand visits' AS VGroup, Products.brandName as vType
		FROM            v_Visits INNER JOIN Visits_r_Products ON v_Visits.id = Visits_r_Products.visitId INNER JOIN Products ON Visits_r_Products.productId = Products.id
		WHERE        visitTime  between @periodFrom and dateadd(ms, -3, dateadd(day, 1, cast(@periodTo as datetime))) AND v_Visits.userRole like @uRole AND v_Visits.channelName like @channels
		) resSub
		GROUP BY  vGroup, vType

Open in new window

0
Hi EE,

I am sure this question would have been asked and answered here. But here is my requirements.

Drop all foreign key constraints in my db
Truncate all tables

Recreate all foreign key constraints. I have been trying to use https://social.technet.microsoft.com/wiki/contents/articles/2958.script-to-create-all-foreign-keys.aspx but somehow i am unable to make it work.

Can you please help?

regards
0
Hello,
How can I modify this query to have filter like' starts with '
  IF  @PatientName <> '' 	       
			 SET @SQL = @SQL + '  AND PatientName LIKE  ''%' + CAST(@PatientName AS  Nvarchar(MAX)) +  '%'''
   

Open in new window



Regards
0
Since we upgraded to SQL 2008 from SQL 2000 i have faced the following problem: I can run the VB Script that runs a DTS from the SQL Server, however running the same VB Script to to execute the DTS from a client PC has the above error message. I have tried, installing MS SQL studio on the client connecting to the DB instance and tried running the VB Script but it still wont work. im lost, please help

Regards,
Grand
0
Hi All,

I have query below:

DECLARE @intMaxLength AS INTEGER
SET @intMaxLength  = (SELECT MAX(LEN(RTRIM(Name))) FROM TMJENIS)
SELECT
RTRIM(JenisCode) As JenisCode
 
,LEFT(Name, @intMaxLength) + SPACE(@intMaxLength  - LEN(RTRIM(Name))) + ' - ' + RTRIM(JenisCode) As Name
 
, LEN(Name) as lenname
, @intMaxLength as MAXLEN
FROM TMJENIS
WHERE 1=1
ORDER BY Name

Adding the SPACE(@intMaxLength  - LEN(RTRIM(Name)))  does not help.

What's wrong ?

Thank you.
0
Are there any list of requirements that must be in place for SSIS to show up in the program application list below. I would assume either a database must be present or is it a stand alone product in the list provide that I can point to a specific database to work on.

One of the members said Development licenses. I cannot afford one at the moment is there a trial or a way to get a free temporary license?
I think I just need as simple as I can get. I have weeks before I would start if I do get this opportunity. I think it will be time enough for me to catch up and get back into the swing of things. Reporting is what I do anyway. This is just a tool to clean up the data enhance performance and produce a result set to report off of.
SQL-Server-2016.jpg
0

Microsoft SQL Server

158K

Solutions

187

Articles & Videos

48K

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.