I have a XML file (download XML File below at URL) stored in a SQL Column of XML type. The column with the XML file is named CurrentPackageXML in a table called dbo.
View 19 Replies
Dear ppl, I want to shred the following XML so that i can get all the Warranty(s) with id and text.
Required Output
id WarrantyText
1 1 year parts and labor
2 2 year parts and labor
XML
declare @myDoc xml
set @myDoc =
'<ProductDescription ProductID="1" ProductName="Road Bike">
<Features >
<Warranty id="1">1 year parts and labor</Warranty>
<Warranty id="2">2 year parts and labor</Warranty>
<Maintenance>3 year parts and labor extended maintenance is available</Maintenance>
</Features>
</ProductDescription>'
I am using the following query but it doesn't seem to work
select C.value('@id', 'nvarchar(max)' ) as ID,
C.value('Warranty', 'nvarchar(max)' ) as WarrantyText
from @myDoc.nodes('//Warranty') T(C)
Two servers are configured with Windows 2008 / SQL server 2012 utililizing Always-On for HA. We need to upgrade both servers to Windows 2012 / SQL Server 2014 with minimum downtime(Time for Always-On failover). The upgrade to SQL 2014 is straight forward with for minimum downtime.The Windows upgrade(2008 -> 2012) is the problem. From what I have observed and read in blogs.The Windows node to be upgraded must be removed from the Windows cluster before the node can be upgraded to Win 2012.A Win 2008 and Win 2012 node can not reside in the same cluster. If this is true then the only option I can think of is to dump the DB on WIN 2008 server and restore on Win 2012. This is an outage(time it takes to dump and restore).Is there any other method to upgrade these two nodes utilizing Always-On of some other method without downtime?
View 2 Replies View RelatedHi all,
In my SQL Server Management Studio Express, I have the following Database "ChemAveRpd", Table "dbo.LabTests", and Content of the Table:
dbo.LabTests Column_name Type Length Prec Scale
AnalyteID int 4 10 0 (Primary Key)
AnalyteName nvarch 510
CasNumber nvarch 510
Result numeric 5 8 2
Unit nvarch 510
SampleID int 4 10 0 (Foreign Key)
AnalyteID AnalyteName CasNumber Result Unit SampleID
1 Acetone 123-456-9 134.0 ug/L 1
2 Bezene 666-12-8 2.0 ug/L 1
3 Cobalt 421-008-7 10.0 ug/L 1
4 Acetone 123-456-9 201.0 ug/Kg 2
5 Bezene 666-12-8 1.0 ug/Kg 2
6 Cobalt 421-008-7 22.0 ug/Kg 2
7 Acetone 123-456-9 357.0 ug/L 3
8 Bezene 666-12-8 9.0 ug/L 3
9 Cobalt 421-008-7 56.0 ug/L 3
When I ran the following T-SQL code:
USE ChemAveRpd
GO
SELECT *
FROM dbo.LabTests as LabResults
Where AnalyteName = Acetone
GO
I got the following error:
Msg 207, Level 16, State 1, Line 3
Invalid column name 'Acetone'.
Please help and tell me what right syntax I should write for [Where AnalyteName = Acetone] to generate a listing of LabResults for Acetone.
Thanks in advance,
Scott Chang
I have a column with XML data stored in it. I need to update that column several times with new values for different nodes. I've written a CLR function to update the XML quickly but the update is always based on the initial value of the xmlData column. I was hoping that the subsequent updates would be based on the new data from the prior update (each xmlTable has several newData rows). Do I have to make this a table valued function and use cross apply?
UPDATE xmlTable
SET xmlTable.xmlData = Underwriting.UpdateByDynamicValue(xmlTable.xmlData,newData.NodeID,newData.NewValue)
FROM xmlTable
JOIN newData
ON xmlTable.ID = newData.fkXmlTableID
Hi, I was wondering if there is a method (other than BULK INSERT) to insert a (C++) application level array into the database, I have a variant type array populated with values that I want to insert, perhaps using ADO objects in quick time!
I am trying to get the CLR user-defined aggregate functions sample working (VB version).
I've saved the sample (from Invoking CLR User-Defined Aggregate Functions ) to a .vb file and compiled it like this:
C:dataVStudioprojectsSQLutilsSQLutils>c:WINDOWSMicrosoft.NETFrameworkv2
.0.50727vbc /t:library /out: SQLutils.dll Class1.vb
Microsoft (R) Visual Basic Compiler versie 8.0.50727.42
voor Microsoft (R) .NET Framework versie 2.0.50727.42
Copyright (c) Microsoft Corporation. Alle rechten voorbehouden.
The SQLutils.dll file is created without warnings or errors.
I've created the assembly
create assembly sqlutildll from 'D:SQLdatadllSQLutils.dll'
and then the function:
CREATE AGGREGATE Concat (@input nvarchar(200)) RETURNS nvarchar(max)
EXTERNAL NAME sqlutildll.Concatenate
and then I got this error:
Msg 6558, Level 16, State 1, Line 1
CREATE AGGREGATE failed because type 'Concatenate' does not conform to UDAGG specification due to method 'Init'.
Msg 6597, Level 16, State 2, Line 1
CREATE AGGREGATE failed.
What is this UDAGG specification ?
How can I make this function working?
The VB code:
Code Snippet
Imports System
Imports System.Data
Imports Microsoft.SqlServer.Server
Imports System.Data.SqlTypes
Imports System.IO
Imports System.Text
<Serializable(), SqlUserDefinedAggregate(Format.UserDefined, IsInvariantToNulls:=True, IsInvariantToDuplicates:=False, IsInvariantToOrder:=False, MaxByteSize:=8000)> <Microsoft.VisualBasic.ComClass()> _
Public Class Concatenate
Implements IBinarySerialize
''' <summary>
''' The variable that holds the intermediate result of the concatenation
''' </summary>
Private intermediateResult As StringBuilder
''' <summary>
''' Initialize the internal data structures
''' </summary>
Public Sub Init()
Me.intermediateResult = New StringBuilder()
End Sub
''' <summary>
''' Accumulate the next value, not if the value is null
''' </summary>
''' <param name="value"></param>
Public Sub Accumulate(ByVal value As SqlString)
If value.IsNull Then
Return
End If
Me.intermediateResult.Append(value.Value).Append(","c)
End Sub
''' <summary>
''' Merge the partially computed aggregate with this aggregate.
''' </summary>
''' <param name="other"></param>
Public Sub Merge(ByVal other As Concatenate)
Me.intermediateResult.Append(other.intermediateResult)
End Sub
''' <summary>
''' Called at the end of aggregation, to return the results of the aggregation.
''' </summary>
''' <returns></returns>
Public Function Terminate() As SqlString
Dim output As String = String.Empty
'delete the trailing comma, if any
If Not (Me.intermediateResult Is Nothing) AndAlso Me.intermediateResult.Length > 0 Then
output = Me.intermediateResult.ToString(0, Me.intermediateResult.Length - 1)
End If
Return New SqlString(output)
End Function
Public Sub Read(ByVal r As BinaryReader) Implements IBinarySerialize.Read
intermediateResult = New StringBuilder(r.ReadString())
End Sub
Public Sub Write(ByVal w As BinaryWriter) Implements IBinarySerialize.Write
w.Write(Me.intermediateResult.ToString())
End Sub
End Class
I am trying to use the Bulk Insert Task to load from a csv file. My final column is a bit that is nullable. My file is an ID column that is int, a date column that is mm/dd/yyy, then 20 columns that are real, and a final column that is bit. I've tried various combinations of codepage and datafiletype on my task component. When I have RAW with Char, I get the error included below. If I change to RAW/Native or codepage 1252, I don't have an issue with the bit; however, errors start generating on the ID and date columns.
I have tried various data type settings on my flat file connection, too. I have tried DT_BOOL and the integer datatypes. Nothing seems to work.
I hope someone can help me work through this.
Thanks in advance,
SK
SSIS package "Package3.dtsx" starting.
Error: 0xC002F304 at Bulk Insert Task, Bulk Insert Task: An error occurred with the following error message: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.The bulk load failed. The column is too long in the data file for row 1, column 24. Verify that the field terminator and row terminator are specified correctly.Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 23 (cancelled).".
Error: 0xC002F304 at Bulk Insert Task 1, Bulk Insert Task: An error occurred with the following error message: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.The bulk load failed. The column is too long in the data file for row 1, column 24. Verify that the field terminator and row terminator are specified correctly.Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 23 (cancelled).".
Task failed: Bulk Insert Task 1
Task failed: Bulk Insert Task
Warning: 0x80019002 at Package3: The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "Package3.dtsx" finished: Failure.
Hello
Is it possible to navigate by clicking on a graph to another report. If so how?
Thanks
Inder
Is anyone using SQL server to navigate their public web site?
We are concerned about having a database hit every time the user chooses a different page from our navigation bar.
Any advice? Experience with this?
Hi all,
I have done 2 charts.while clicking one chart it is navigating to another chart. it is working in Report Server.
But using this graph in the UI page(putting Iframe and calling reports) it is not navigating to another chart.
Can u guys help me to solve the problem
Thanks in advance.
Hello All,
I am viewing the report in Reporting Manager. It has four tabs at the top viz. View, Properties History and Subscription. When I click any one of Properties, History or Subscription and click the View tab again, I loose my dataset that was there before I started clicking the tabs. It prompts for the parameters again.
Is that by design? I find this a nuisance to enter the parameter again and run the report just because I navigated to a different tabs.
Any suggestions to alter this behavior to make the data sticky in the report?
Thanks
Phewa
I work with databases and some of my backup files are 30-100 gigs insize. I currently use the FastCopy utility to copy files from oneserver to another. (This is needed when I need to transfer a DB to adifferent server). FastCopy says its 30 to 50% faster than Windowsfile copy. I read somewhere that the windows kernel defaults to4mb/sec throughput for copying.Is this true? Is there a faster way or some utility to copy files?I'm aware of RoboCopy but the documentation didn't mention anythingabout faster performance. All my servers are windows 2000 NTFS.Thanks
View 3 Replies View RelatedHI,
I am creating a custom control flow component in SSIS. The work flow of this component is to connect to File conection manager and after selecting the file it set properties of the file to variables. When i am runnning this component in SSIS , one method is calling i.e SAVETOXML. Here i am saving selected properties of the file to XMLby using XMLELEMent attribute. Its running fine. Here my question is where i can get that XML file. Where it is saving that XML file. From where i can Load that XML file. What is the use of LOADXML method.
Thanks in advance
ashok mohanty
Hi I have a matrix table with
Status as the row heading and month as the column heading. The detail is a count off the records which fall into these statuses and the month columns.
The detail also contains a navigation link to a subreport which returns the records which make up the count.
For example if the detail contains a 7 , when i click on the 7 it returns 7 records with details off these records. The subreport accepts 2 parameters which is passed over from the navigation link, the parameters are - status id and month.
My question is - I have put a total on the matrix for the month columns, when i click on the total value the subreport appears (as the total is treated as part off the details) but it does not return the correct number off records. Im unsure as to why the total would not return all records , but it seems it is not passing the month parameter correctly, it seems to not to know to pass 'all' months which appear on the matrix as it is the 'total'. It seems to get confused and returns records only for a particular month. I thought the matrix would be smart enough to know that when a total has a navigation to return all records.
Can someone please help
thanks
Hi All,
I am working on a column chart type (stacked column sub-type) report.
Our customer requires us that the space(padding) between the columns should be a constant(including the space between the Y-axis and the first column). I know how to set the width of the columns, but I really don't know how to set the width of the space between them. The columns just varies the space between them automatically according to the number of the columns (the number of the columns is not certain).
Thanks a lot in advance!
Danny
I have made a small asp.net project which uses a local database file as a part of the project. The project is running fine om my local machine, but when I upload it to the remote server, the login fails for the server.
I suspect this is can be solved by using sqlserver authentication. But I have now spent a lot of time trying to configure the database file to use this authentication mode. As I see it there are three possible solutions to the problem.
use management studio express to configure the local mdf file (Ecxept that I cant find out how to connect to the mdf-file) and from here change the authentication method to sqlserver authentication.
use Visual Web developer to change the authentication method (but how???)
make the windows authentication work on the server (this would probably require that mannamgement studio express connects to the remote database. (Same problem as no 1)
Help will be higly appreciated.
Bjarke
Not sure this is the correct forum, but I'll give it a go. I am in the process of deploying a series of databases from a development environment to a production environment. I've done some searching around best practices, but haven't found anything specifically calls out what is best. I am looking to find out the best approach for moving the code/tables/views/SP's, well everything from my dev environment to the production environment. Due to the complexity and large number of objects to be created, would a backup/restore to the production server be more prudent than creating a batch file type thing that creates all the objects through a series of scripts?
Hi guys,
If I have a temporary table called #CTE
With the columns
[Account]
[Name]
[RowID Table Level]
[RowID Data Level]
and I need to change the column type for the columns:
[RowID Table Level]
[RowID Data Level]
to integer, and set the column [RowID Table Level] as Identity (index) starting from 1, incrementing 1 each time.
What will be the right syntax using SQL SERVER 2000?
I am trying to solve the question in the link below:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2093921&SiteID=1
Thanks in advance,
Aldo.
I have tried the code below, but getting syntax error...
ALTER TABLE #CTE
ALTER COLUMN
[RowID Table Level] INT IDENTITY(1,1),
[RowID Data Level] INT;
I have also tried:
ALTER TABLE #CTE
MODIFY
[RowID Table Level] INT IDENTITY(1,1),
[RowID Data Level] INT;
Hi,I am trying to write a method which needs to call a stored procedure and then needs to get the response of the stored procedure back to the variable i declared in the method. private string GetFromCode(string strWebVersionFromCode, string strWebVersionString) { //call stored procedure } strWebVersionFromCode = GetFromCode(strFromCode, "web_version"); // is the var which will store the response.how should I do this?Please assist.
View 3 Replies View RelatedHi,
can I assign a NAS(Network Attached Storage) server to store the database file(readable and writable) and assign other several MS SQL database servers which will use the same database file in NAS to achieve the objective of high availability?
If it can, how can I set it up in MS SQL Server or it requires another 3rd patry software to set it up?
Thanks.
Hi,
I just have a Dataset with my tables and thats it
I have a grid view with several datas on it
no problem to get the data or insert but as soon as I try to delete or update some records the local machine through the same error
Unable to find nongeneric method...
I've try to create an Update query into my table adapters but still not working with this one
Also, try to remove the original_{0} and got the same error...
Please help if anyone has a solution
Thanks
Hi,
I'm having problems navigating from one report to another one if the second report has a multi-valued report parameter. When I navigate to the second report, I don't pass any parameters, but I get an error "parameter is missing a value" for the multi-valued report parameter. I have it setup as allow "multi-value" and "blank value". Any idea what the issue might be?
Thanks.
I am putting together a SQL table which will hold a latitude andlongitude for each record. An example of a latitude is: 47 05 36.5Which column type would best represent this and retain the spacesbetween degrees, minutes, seconds? Text, varchar, char?ThanksJeffJoin Bytes!
View 3 Replies View RelatedHello,In my web application I am listing steps of action:STEP ACTION10 10.1 10.210.910.1010.203030.1etcHow should I set up my columns in my database? I tryed decimal but the problem is that 10.1 and 10.10 is in theory the same while in my case it is absolutely not. I also tryed nvarchar but does not sort correctly.Should I create 2 int column, one for the main step and another one for the sub-step (one would contain the part before the "." and the other would contain the part after the ".")?Thanks
View 4 Replies View Relatedhi
i am trying to modify a column type from smallint to nvarchar(10), i am using the following script :
ALTER TABLE Addresses MODIFY [Floor] NVARCHAR(10)
i am getting :
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'Floor'.
what is the problem with that?
Hello, I would like to get the columns in a table, this works fine:SELECT syscolumns.* FROM sysobjects INNER JOIN syscolumns ON sysobjects.id = syscolumns.id WHERE sysobjects.name = 'Customers'ORDER BY syscolumns.colidHowever, is there a way to get Column_Data_Type as for example "nchar" or "varchar" instead of having it as numbers.Can anybody help ?thanks
View 7 Replies View RelatedI need to stored lots of unsigned 32-bit values. (I'm actually storing IPv4 addresses). These values will frequently exceed 2^31 but never exceed 2^32
My options are:
Use bigint. This is undesirable since this wastes 32-bits of space for every value.
Use int and use the negative value range to get full 32-bits worth of data. In C, it is easy and fast to cast a signed -1 to an unsigned 2^32-1. In SQL, it will be more expensive: Must cast to 64-bit and if val < 0 then val = 2^32 + val.
Is there a better alternative?
Is there a fast (binary) way to cast a value > 2^31 to a negative signed value?
DECLARE @value BIGINT
SET @value = 3000000000
SELECT CONVERT(INT, @value) -- causes error
SELECT CAST(@value AS INT) -- causes error
This is an easy one , I need to know the way to check programatically for an SQL server 2000 column data type , if the result is a code , which codes are for the diferent datatypes ( varchar , text , smallint , etc ) , if you send me an url is ok.
thank you
I have a column in my table that is an Image (blob). The data storedin this blob is basically pdf files. I need a query to determine thetotal size of these blob's in the database. Here is what ishappening, if I run it as below I get 23736000 for a result.select sum(datalength (cast(document AS binary(8000)))) fromplan_reportIf I run it like this select sum(datalength (cast(document ASvarbinary(8000)))) from plan_reportI get 23736000 also as a resultIf I run it like this select sum(datalength (cast(document ASvarbinary))) from plan_reportI get 89010 this as a resultIf I run this: select datalength(document) from plan_report then Iget a result for each row and when I sum those rows I get 5584452854this as a result.I do not believe the first 3 queries are returning correct result setsdue to the limit on the fields (binary/varbinary) and since this is animage field I cannot convert to anything else.....I am out of ideas, and any help you can give me will be greatlyappreciated...
View 4 Replies View RelatedHello,Easy one for the SQL experts.I have a simple table. For the example let's say it looks like this:CREATE TABLE doc_exb ( column_a INT, column_b VARCHAR(20) NULL)Now I want to alter this table and make the column column_a a floatinstead of an INT.How do I do that? I cannot DROP and ADD the column I would lose somevery precious data.Thanks a lot
View 1 Replies View Relatedhi
is it possible to find a column's type inside stored procedure?? I am going to get the name of the table and then work with the columns and I just need the type of the column if I have had the name of it
regards
Hi,
I'm trying to figure out the best way to determine the data_type of a column, given the table name and column name. Once I've determined it's a charactor string I can get the length using COL_LENGTH, but I can't fund a command or procedure that will return the data type.
Thank you.