Microsoft SQL Server 2005

71K

Solutions

25K

Contributors

Microsoft SQL Server 2005 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. It includes support for managing XML data and allows a database server to be exposed over web services using Tabular Data Stream (TDS) packets encapsulated within SOAP (protocol) requests.

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

Sign up to Post

hi experts

can you share scripts for administration about block SQL SERVER 2016
0
CompTIA Network+
LVL 12
CompTIA Network+

Prepare for the CompTIA Network+ exam by learning how to troubleshoot, configure, and manage both wired and wireless networks.

Hi experts
1. When should I use IFI - instant file initialization?
2. Some URL that I can recommend for the implementation
3. Should I have it configured because it helps in the autogrowth?
0
Hi,

I need a query to return the average per day for each customer and total average of all customers per day. How can i do that?

I can calculate the average for each date/client but how can calculate (and add a row) with the average of the day .

select Avg(Value) as Med , Customername as customer, Datea as dateinvoice from AGH group by datea,Customer

Attached, image with example what i need.

best regards
Img14.png
0
HELLO...

We have a number of Tables, that need to be "migrated" from one database to another, in MSSQL-2012.  There are FK Dependencies on a good number of these tables.  For example:

PS_TKT_HIST_LIN                   (Table-# 1)
PS_TKT_HIST_DISC_COD       (Table-# 2)
PS_TKT_HIST                           (Table-# 3)
PS_TKT_HIST_LIN_CELL         (Table-# 4)
PS_TKT_HIST_LIN_CELL_EXT (Table-# 5)

...and so on.  In the example above, Table-# MUST exist FIRST...then...Table-# 2 MUST exist SECOND, then Table-#1, Table-#4, and Table-# 5.  I know we can use the built-in MSSQL EXPORT/IMPORT function...and that's great...but, because these need to be Exported/Imported IN SEQUENCE (due to the FK Dependencies), we figured we'd have to do that "Write a query to specify the data to transfer"...as opposed to..."Copy data from one or more tables or views".

Based on the above example, therefore, can someone please help us to develop the correct script with syntax, to accomplish this task?  It would be unbearable to do this 1-table-at-a-time, as there are over 200 tables, with FK Dependencies.  If we can get an example to provide a roadmap, that would be great.  And before you ask...the COMMAND LINE utilities are not an option...this MUST be done, within Management Studio :-)

Thank you in advance...Mark
0
I have a huge backup file the decomposed into 3 files test.bak1,Bak2 and bak3. How can I restore this DB in Standby mode and apply additional Tlog files?
0
I'm under a tight deadline (and have a moody manager who's not patient ) to remove some hardcoded .Net  code and create a table so we could read the data dynamically.

This is how the code looks like (there are 48 FieldIds...so 48 rows)
  Fields.Add(new ReportField() { FieldId = "1", TableName = _tbMasterLease, ColumnName = "LeaseNo", DisplayName = Normal.LeaseNo, DbType = DbType.String });

Open in new window



And this one (used for some boolean fields)
            Fields.Add(new ReportField
            {
                FieldId = "48",
                TableName = _tbUnit,
                ColumnName = "Returned",
                DisplayName = Normal.Returned,
                DbType = DbType.Boolean,
                Bool_ValueIfTrue = Normal.Yes,
                Bool_ValueIfFalse = string.Empty,
                Bool_TrueText = Normal.Yes,
                Bool_FalseText = Normal.No
            });

Open in new window


This is how I want to do it:


A table with columns that match each property in the code. So,
Identity field (this is the auto increment identity field)
FieldId (ex-developer hasn't used sequential Ids. I want to keep the same Id because we have data saved already with these Ids)

TableName
ColumnName
DisplayName
DBType
Bool_ValueIfTrue
Bool_ValueIfFalse
Bool_TrueText
Bool_FalseText

Should I add the Identity field? Any other ideas given that I have to get this done fast?
0
This is SQL 2014

We have a table with 3 Text fields. It holds data that has been compressed in .Net code. Not encrypted, it's compressed and .Net code decompresses the data to display on the screen.

It looks like this
r1.png
I don't know if we'll lose the data but can I change Text field to nvarchar(max) without losing data?
0
Hi there. We currently have SQL Server 2008 R2 running on a single server/DC (small office setting), and we just purchased a new server box, and want to move everything over to it (including SQL Server). Unfortunately, the installation media with the licence on it is missing - is there a way to find the licence that the old server with SQL 2008 R2 is using? To clarify, SQL was installed separately, so should have its own licence. I looked through the registry, but the "Productcode" registry key didn't have the correct format (wasen't aaaa-bbbbb-ccccc-ddddd-eeee).
Thanks.
0
I have a full backup every weekend, and every 30 min transaction log backup. Today I found out one transaction log backup fail last night but after 30 min, the transaction log backup runs again, it succeeded, then continue to run succeeded.

I want to know if I should go ahead to run a full backup to ignore the failed transaction log backup, no need to wait to weekend. My reason is I cannot restore all the logs if something happened now. I have to use last week's full backup + all the transaction logs before next weekend full backup. Since one of the transaction log failed, I am not able to restore to point in time, Am I right?
0
I have a text field in a table and I want to read them. I did this but I still see a value like this
4sIAAAAAAAEAE2PT0sDMRDFv0qZ8x52W//U3LZKRcQt2OJFPAz

 r1.png
0
Exploring ASP.NET Core: Fundamentals
LVL 12
Exploring ASP.NET Core: Fundamentals

Learn to build web apps and services, IoT apps, and mobile backends by covering the fundamentals of ASP.NET Core and  exploring the core foundations for app libraries.

Hi Guys,

select hashbytes('SHA2_256','123'+'456'+'1') -- Hashbytes will always give UNIQUE value for n number of rows.

Just wanted to know, if we convert the HASHBYTES function to BIGINT like:-

select convert(bigint, hashbytes('SHA2_256','123'+'456'+'1')) will this conversion will always give UNIQUE values if their are million or billions of rows?

Please help.
0
Good morning,

I am having a problem running SSIS package in SQL 2016. I can create and run it in separate Import/Export Data utilities, but when I execute it in SQL itself I am getting an error message "Parser NULL". I also can not run the Import/Export wizard withing the SQL database engine. I am getting an error: "The SSIS Data Flow Task could not be created. Verify that DTSPipeline.dll is available and registered."

Please advise. THanks
0
Hi All,

I have below code.

 UPDATE A
		SET A.NilaiAlokasi = ISNULL(B.TotalNilaiAlokasi, 0)
		FROM TDMONEYPAYDIST A
		INNER JOIN
		(
		SELECT
		KodePembayaran
		, SUM(NilaiAlokasi+NilaiLebih) AS TotalNilaiAlokasi
		FROM TDALCAP A
		WHERE EXISTS
		(
		SELECT
		NULL
		FROM TDALCAP B
		WHERE A.NoAlokasi = B.NoAlokasi
		AND B.NoAlokasi = @NoTransaksi
		)
		GROUP BY
		KodePembayaran
		) B

		ON A.ID = B.KodePembayaran

Open in new window


It is using INNER JOIN, but I think it is not correct, but if I change to LEFT JOIN, then it should not have correct data update.

What should I do ?

Thank you.
0
I have a SQL statement like this

SELECT DISTINCT SiteAddress,sitecity,SiteState,SiteZip,SiteCountryCode

 FROM table
WHERE (Deleted =0
AND SiteCountryCode = 'USA')
or (SiteAddress != NULL
and SiteAddress != 'NULL')

Open in new window


SiteAddress is nvarchar(256)

Howcome this SQL brings back NULL in siteaddress?

s1.png
0
I have a failed SQL backup and have no clue why this is happening? Can anyone help with this?

Failed:(-1073548784) Executing the query "BACKUP DATABASE [JDE910] TO  DISK = N'\\\\x.x.x.x..." failed with the following error: "Cannot open backup device '\\\\x.x.x.x\\JDE Images\\SQL Backup\\Daily\\JDE910_backup_2018_07_14_220001_8461617.bak'. Operating system error 1311(There are currently no logon servers available to service the logon request.).
BACKUP DATABASE is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.


X.X.X.X are the IP address of the webserver jde
x.x.x.x is the ip address of the NAS device where the backup has to be moved
0
Hi

This is how query looks like:
First query:

select FullName, SystemUserId from SystemUser
ORDER BY FullName

This query will show 100x systemuserID now to the next one:

update UserEntityUISettingsBase
set RecentlyViewedXml = null
where OwnerId = 'SystemuserID'

ok here comes the question, how do I do this easiest? Declare a var for 100 systemuserID? how do i do this so the 100x systemuserID updates easiest.

Thanks
0
Hello,
Can you please help,
I need to get the [Driver_In] Minimum Date Time/ [Driver_Out] Maximum Date Time between 2 tables (Per Driver) , then calculate the difference (Hours and Minutes),
This is What I have so far (But , it is wrong, I'm getting 2 lines per Driver

Select PickUpDriver AS [Driver], (Select Name From Drivers Where Drivers.DriverNumber = ActiveOrders.PickUpDriver) AS [Name],
CONVERT(date, OrderDate) AS [Order_Date],MIN(CAST(REPLACE(PickedupTime, ',', ' 01,') AS DATETIME)) AS [Driver_In],Max(CAST(REPLACE(DeliveredTime, ',', ' 01,') AS DATETIME)) AS [Driver_Out],
DATEDIFF(second, MIN(CAST(REPLACE(PickedupTime, ',', ' 01,') AS DATETIME)), Max(CAST(REPLACE(DeliveredTime, ',', ' 01,') AS DATETIME))) / 3600.00 AS [Hours_Worked],
DATEDIFF(minute, MIN(CAST(REPLACE(PickedupTime, ',', ' 01,') AS DATETIME)), Max(CAST(REPLACE(DeliveredTime, ',', ' 01,') AS DATETIME))) AS [Minutes_Worked]
From ActiveOrders
where (((OrderDate > = DATEADD(d,0,DATEDIFF(d,0,GetDate())))  And (OrderDate <= DATEADD(s, +86399, DATEADD(d,0,DATEDIFF(d,0,GetDate())))))) AND Cast(datepart(yyyy,PickedUpTime)as varchar) + '-' + RIGHT('0' + RTRIM(MONTH(PickedUpTime)), 2) + '-' + Right('0'+cast(datepart(DD,PickedUpTime)as varchar(2)),2) = Cast(datepart(yyyy,DeliveredTime)as varchar) + '-' + RIGHT('0' + RTRIM(MONTH(DeliveredTime)), 2) + '-' + Right('0'+cast(datepart(DD,DeliveredTime)as varchar(2)),2)
And ((Select Name From Drivers Where Drivers.DriverNumber = ActiveOrders.PickUpDriver)
0
Dear expert,

Simple query, I want MSSQL to exclude all the NULL result in columns from the table.

Any easy solution?
Thx
0
i have SQL data file which is 5 gb and sql log file is 71 gb..  in E Drive size is 100gb..
There a job full Daily Full backup are happening, .bak file is only 191 MB

After realizing the above I did one Transcational log backup to g drive(got temp space), it came around 18gb, after that I took again full backup the size of backup was same around 192MB

is some thing wrong with transaction log, there is no open transcations, i have verfied? is my full backup is 192 Mb only?

im shortage of space as well to take hourly transaction log on E drive, the db is in full recovery model

Version is sql server 2012 std
0
Python 3 Fundamentals
LVL 12
Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

We have a booking system, and have appointments. What I need to do is get a list of bookings and the duration, based on the difference between the 2 appointment times.

Is there a better way to write this SQL? I'm using MS SQL 2008.

Declare  @myTable TABLE(dID int, Doctor varchar(25),  AppDateTime datetime)

  Insert into @myTable Select 1,'Doctor 1', '2018-07-08 09:00:00'
  Insert into @myTable Select 2,'Doctor 1', '2018-07-08 09:10:00'
  Insert into @myTable Select 3,'Doctor 1', '2018-07-08 09:15:00'
  Insert into @myTable Select 4,'Doctor 1', '2018-07-08 09:30:00'
  Insert into @myTable Select 5,'Doctor 1', '2018-07-08 09:40:00'
  Insert into @myTable Select 6,'Doctor 2', '2018-07-08 09:00:00'
  Insert into @myTable Select 7,'Doctor 2', '2018-07-08 09:25:00'
  Insert into @myTable Select 8,'Doctor 2', '2018-07-08 09:35:00'
  Insert into @myTable Select 9,'Doctor 3', '2018-07-08 09:00:00'
  Insert into @myTable Select 10,'Doctor 3', '2018-07-08 09:05:00'
  Insert into @myTable Select 11,'Doctor 3', '2018-07-08 09:10:00'
  Insert into @myTable Select 12,'Doctor 3', '2018-07-08 09:25:00'
  Insert into @myTable Select 13,'Doctor 4', '2018-07-08 09:00:00'
  Insert into @myTable Select 14,'Doctor 4', '2018-07-08 09:30:00'
  Insert into @myTable Select 15,'Doctor 4', '2018-07-08 10:00:00'
  Insert into @myTable Select 16,'Doctor 5', '2018-07-08 09:00:00'
  Insert into @myTable Select 17,'Doctor 5', '2018-07-08 09:45:00'
  Insert into @myTable Select …
0
when I choose server name (local) for connection string to connect SQL server
if network disconnect the connection is also disconnected why!!!
what's the relation!!!
0
I want to query out all applications and jobs associated with sa account usage. Does someone provide a query for me? Thanks
0
Hi All,

I want to get current language id.
I have below code :

SELECT
    -- c => currency
    -- n => numeric
    FORMAT(987654321, N'N', C.culture) AS some_number
,   FORMAT(987654321, N'c', C.culture) AS some_currency
,   C.culture
FROM
    (
        -- Language culture names
        -- http://msdn.microsoft.com/en-us/library/ee825488(v=cs.20).aspx
        VALUES
            ('en-US')
        ,   ('en-GB')
        ,   ('ja-JP')
        ,   ('Ro-RO')
        ,   ('el-GR')
    ) C (culture);

Open in new window


I want to do something like :

SELECT
    -- c => currency
    -- n => numeric
    FORMAT(987654321, N'N', C.culture) AS some_number
,   FORMAT(987654321, N'c', C.culture) AS some_currency
,   C.culture
FROM
    (
        -- Language culture names
        -- http://msdn.microsoft.com/en-us/library/ee825488(v=cs.20).aspx
       SELECT ...... (get current system culture)
        
    ) C (culture);

Open in new window


How could I do it ?

Thank you.
0
Hi Experts,
Advance Thanks!
I need the great help to do subquery . Have attached the original script and attached screen shot.
Currently am  getting 9 rows.

Expected output is count 6
There are multiple fileids(10671,10672,10700) in the original output (count 9). i need to pick max(isApprove) for those mutiple unique fileids...

My expected out is attached..
Please help me to tune this query...


Kind regards,
Pooja
Current-Output.PNG
Expected-Output.PNG
query.txt
0
I just installed Visual Studio Community 2017 with the SSDT tool kit, connected to my SQL server opened a table  but Diagram, Criteria and Results panes are grayed out. In "Tools" | "Options" | "Database Tools" | "Query and View Designers" all the checkboxes for the Diagram Pane, Criteria Pane, SQL Pane and Results Pane are checked. I am extremely new to VS I normally do all my work in SSMS.
0

Microsoft SQL Server 2005

71K

Solutions

25K

Contributors

Microsoft SQL Server 2005 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. It includes support for managing XML data and allows a database server to be exposed over web services using Tabular Data Stream (TDS) packets encapsulated within SOAP (protocol) requests.