Hi,
Is it possible to insert JSON data to SQL Server 2005 directly using stored procedure?
Somthing like for XML we are using sp_xml_preparedocument and openxml. For JSON ???
Thanks,
Tried loading JSON data to Sql Server  ? Sample format is given below..Don't see any easy way doing it except writing some C# code deserialize it.
Â
[ {
 "name" : "peter_2.jpg",
 "createdDate" : 1259728960000,
 "lastModifiedDate" : 1308174976000,
 "Secondary" : [ {
  "Id" : 106275817,
  "Sid" : 1
 Â
[code]...
We have a gaming application which generates transactional data in MongoDB which eventually sends the data to SQL Server and it is in JSON format. This data needs to be used for reporting tool but visualizing this data in forms of a table is proving to be difficult. One example of a column we receive is:
{responseCode:0 transactionId:null amount:200.00 message:account balance }
We need to build a sort of ETL or batch job but need to interpret this in a form which SQL Server can understand.
Hi,
Is it possible to insert JSON data to SQL Server directly using stored procedure?
Somthing like for XML we are using sp_xml_preparedocument and openxml. For JSON ???
Hi,
I have 2 tables. MASTER table as M and TRAN table as T. I need to insert 1 row in table M and with the foreign key need to insert multiple rows in the table T. SQL SERVER used is 2005. Application is in .NET 1.x
I am able to insert one record in the table in M but how do i insert the multiple records in table T?
Help....
Thanks,
Rahul Jha
HiI have a VB.net web page which generates a datatable of values (3 columns and on average about 1000-3000 rows).What is the best way to get this data table into an SQL Server? I can create a table on SQL Server no problem but I've found simply looping through the datatable and doing 1000-3000 insert statements is slow (a few seconds). I'd like to make this as streamlined as possible so was wondering is there is a native way to insert all records in a batch via ADO.net or something.Any ideas?ThanksEd
View 1 Replies View RelatedI have a business need to create a report by query data from a MS SQL 2008 database and display the result to the users on a web page. The report initially has 6 columns of data and 2 out of 6 have JSON data so the users request to have those 2 JSON columns parse into 15 additional columns (first JSON column has 8 key/value pairs and the second JSON column has 7 key/value pairs). Here what I have done so far:
I found a table value function (fnSplitJson2) from this link [URL]. Using this function I can parse a column of JSON data into a table. So when I use the function above against the first column (with JSON data) in my query (with CROSS APPLY) I got the right data back the but I got 8 additional rows of each of the row in my table. The reason for this side effect is because the function returned a table of 8 row (8 key/value pairs) for each json string data that it parsed.
1. First question: How do I modify my current query (see below) so that for each row in my table i got back one row with 19 columns.
SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.*
FROM PRODUCT A
CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B
If updated my query (see below)Â and call the function twice within the CROSS APPLY clause I got this error: "The multi-part identifier "A.ITEM6" could be be bound.
2. My second question: How to i get around this error?
SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.*, C.*
FROM PRODUCT A
CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B, Â fnSplitJson2(A.ITEM6,NULL) C
I am using Microsoft SQL Server 2008 R2 version. Windows 7 desktop.
I am writing a query to return some production data. Basically i need to insert either 1 or 2 rows into a Table variable based on a decision as to does the production part make 1 or 2 items ( The Raw data does not allow for this it comes from a look up in my database)
I can retrieve all the source data i need easily but when i come to insert it into the table variable i need to insert 1 record if its a single part or 2 records if its a twin part. I know could use a cursor but im sure there has to be an easier way !
Below is the code i have at the moment
declare @startdate as datetime
declare @enddate as datetime
declare @Line as Integer
DECLARE @count INT
set @startdate = '2015-01-01'
set @enddate = '2015-01-31'
[Code] .....
Any knowledge of an SSIS source pipeline component which reads the JSON, a data interchage format.
JSON looks pretty tempting for heavy data interchange (somewhat human read-able, name/value pairs + arrays, nesting, lighter weight than most xml serializiers), and if its gaining momentum, I should think a source component would follow on (most likely third party)
Is there a good, common way to migrate data from relational tables into hierarchical json?
I am asking this because, basically, I prefer not to write a lot of procedural code.
I am currently working with MS SQL Server db and I’ll soon need to do the same with My SQL db. I use java and java db connectivity for access. It would be great if any of these two db’s can somehow assemble the data server-side.
I have a below JSON script. I need to run the below script, fetch the json files and parse them in SSIS.
var client = new Keen({
 projectId: "<PROJECT_ID>",
 readKey: "<READ_KEY>"
[code]....
I tried using script component, but unable to find how to capture these JSON.
Hi all,I have an issue to insert data into SQL2005 from an idb2connection.I explain:We need to retrieve data from an AS400 and insert it into a SQL database. Actually the idea is to use an idb2connection (which is supposed to be much faster than a DST or SSIS package; maybe I am wrong...)Connecting to the AS400 is not an issue at all. I can retrieve data into a data reader (I try to use a data adapter but there was some issue of character conversion).So basically I have the data reader with all the data I need and I want to insert this data into SQL. My problem is that I would like to know which method is the best to do the insert: 1- Read each line of the data reader and create an insert statement that I can send to my SQL Stored procedure to perform the insert 2- Read each line of the data reader and concatenate it into an xml format in order to pass the Xml string to the stored procedure and perform the insert 3-?? Using solution 1, in case of huge amount of data it will call the stored procedure for each row and may crash the SQL server (cause of having multi call to SQL server)?Using solution 2 I have issue when I have more than 2000 lines. So please can someone tell me which method should be the more appropriate to perform this transfer of data from AS400 to SQL considering that I may have a large amount of data. All your ideas are welcome!!!!
Many thanks,
I know parsing json data has been discussed lots but what I want is probably a little simpler or different:
I have a URL where I can open and get the Json data.
I need to parse and load the Json data into a SQL table, and I want to use it in SSIS, not using C# or VB.NET coding.
I am on Visual Studio.NET 2008 and SQL 2008R2
I need to call a stored procedure to insert data into a table in SQL Server from SSIS data flow task.
I am currently trying to use OLe Db Destination, but I am not sure how to map inputs to OLE DB Destination to my stored procedure insert.
Thanks
I have the text file parsed into an array. How to insert that into SQLServer Database? (VB 2005)
Thanks,
Siby
Example of data in CSV are as follows:
"XXX","0001",-990039739 ,0 ,0 ,0 ,0 ,0 ,0
"ABC"," ",-3422054702 ,0 ,481385 ,0 ,0 ,0 ,0
"JJZ","0001",0 ,0 ,0 ,0 ,0 ,0 ,0Here's my format:
12.0
10
1 SQLCHAR 0 0 """ 0 "" ""
2 SQLCHAR 0 5 "","" 1 OKCCY SQL_Latin1_General_CP1_CI_AS
[Code] ....
I had a function like below :Public Sub Getdata2(ByVal query, ByVal db, ByVal name)
Dim selectSQL As StringDim con As SqlConnection
Dim cmd As SqlCommand
Dim reader As SqlDataReader
Trycon = New SqlConnection("User ID=xxx;password=xxx;persist security info=True;Initial Catalog=database1;Data Source=xxx.xxx.xxx.xxx.xxx,xxxxx;")
Dim username As String
username = Request.QueryString("username")
selectSQL = "SET DATEFORMAT DMY;Insert into table1(hse_num, st_name, proj_name, unit_num, postal, n_postal, flr_area, flr_sf, flr_rate, flr_ra_sf, land_area, land_sf, land_rate, lnd_ra_sf, prop_code, cont_date, title, sisv_ref, r_date, rec_num, source, username, DGP, Remarks, Sub_Code, caveat, consider, age) select hse_num, st_name, proj_name, unit_num, postal, n_postal, flr_area, flr_sf, flr_rate, flr_ra_sf, land_area, land_sf, land_rate, lnd_ra_sf, prop_code, cont_date, title, sisv_ref, r_date, rec_num, source, '" & username & "', DGP, Remarks, Sub_Code, caveat, consider, age from [yyy.yyy.yyy.yyy,yyyy].database2.dbo.table2 where " & querycmd = New SqlCommand(selectSQL, con)
con.Open()
reader = cmd.ExecuteReader()
lbl.Text = selectSQLCatch ex As Exception
lbl.Text = ex.Message
Finally
If (Not con Is Nothing) Then
con.Close()
con.Dispose()
End If
End Try
End Sub
'-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
May i know that how do i retrieve data from [yyy.yyy.yyy.yyy,yyyy].database2.dbo.table2 due to diffrent server, diffrent UID and Password
Hi All
I have a table in my database called YM_WEB_APPLICATIONS in this table I have a field called ApplicationID everytime an insert happens this increments by one. Inserts work fine if I don't set this ApplicationID as a primary key, but I set it as a primary key inserts will not happen, there is no error so I'm a bit stumped. Any ideas?
Here is an extract of my SQL statement: -
strsqlinsert = "INSERT INTO YM_WEB_APPLICATIONS ( "strsqlinsert += "Surname, Forenames, Title, DOB, EmailAddress, Gender, EmergencyContactName, EmergencyContactNumber, "strsqlinsert += "Convictions, ConvictionsList, NationalIdentity, NationalIdentityOther, Ethnicity, EthnicityOther, FirstLanguage, FirstLanguageOther, PreferredLanguage, SpeakWelsh,"strsqlinsert += "HouseNoStreet, Locality, Town, County, Postcode, HomeTelephoneNumber, MobileTelephoneNumber,"strsqlinsert += "Qualifications, SchoolCollege1, From1, To1, SchoolCollege2, From2, To2, Employment,"strsqlinsert += "Disabilities, MedicalCondition, SpecialArrangements,"strsqlinsert += "Marketing, MarketingOther,"strsqlinsert += "InterviewSlot"strsqlinsert += ")"strsqlinsert += " VALUES ("strsqlinsert += "@Surname,@Forenames,@Title,@DOB,@EmailAddress,@Gender,@EmergencyContactName,@EmergencyContactNumber,"strsqlinsert += "@Convictions,@ConvictionList,@NationalIdentity,@NationalIdentityOther,@Ethnicity,@EthnicityOther,@FirstLanguage,@FirstLanguageOther,@PreferredLanguage,@SpeakWelsh,"strsqlinsert += "@HouseNoStreet,@Locality,@Town,@County,@Postcode,@HomeTelephoneNumber,@MobileTelephoneNumber,"strsqlinsert += "@Qualifications,@SchoolCollege1,@From1,@To1,@SchoolCollege2,@From2,@To2,@Employment,"strsqlinsert += "@Disabilities,@MedicalCondition,@SpecialArrangements,"strsqlinsert += "@Marketing,@MarketingOther,"strsqlinsert += "@InterviewSlot"strsqlinsert += ")"strsqlinsert += "; SELECT SCOPE_IDENTITY() ; "
Hello,I am learning SQL Server 2005.I need to create a trigger which increments number of book'spublications:CREATE TRIGGER InsertPublicationON PublicationsAFTER INSERTASBEGINSET NOCOUNT ON;DECLARE @Num smallintSET @Num = SELECT NumPublications FROM Books WHERE ISBN IN(SELECT ISBN FROM inserted);UPDATE BooksSET NumPublications = @Num + 1WHERE ISBN IN(SELECT ISBN FROM inserted);ENDUnfortunately I receive a message:Incorrect syntax near the keyword 'SELECT'.Could you explain me please how to correct the code?I am new to SQL Server.Thank you very much./RAM/
View 2 Replies View RelatedI'm trying to import data from flat file in table and have fewproblems.1.Field Delimiter is ',' (comma). If ',' occurs in quotedstring it is still treated as field delimiter. This is BUG or ?2.In table I have datetime field that can be null, but bulkinsert reports error if in flat file is null or ''. It's OK only whenreal date is specified.Table:create table AttachmentList (Code integer not null,ClassID integer null,Description varchar(200) null,ValidUntil datetime null,constraint PK_ATTACHMENTLIST primary key (Code))flat file.1,13,'Naputak, CU 261098', ''Thanks in advanceDavor
View 3 Replies View RelatedI have a web page that prompts a user to select a csv file. Using a Bulk Insert the data is loaded into a SQL Server 2005 table.
I have been using the Bulk Insert with SQL Server 200 with no problems, but with 2005 I am getting the error "You do not have permission to use the bulk load statement".
My web.config file has the following connection string:
[code]
<add key="connectionString" value="Server=(local);Database=BroadCastOne;trusted_connection=true" />
[/code]
I've given bulkAdmin role to the ASPNET user. It's still not working. What am I doing wrong?
Any help is greatly appreciated,
Ninel
I'm really new to the whole database deal (as well as VB.net) - specifically with the capabilities surrounding VB and SQL Server 2005. My question is open to any recommendations...
What I have is an application that a user uses to create 'new' products. They are presented a form to enter the information regarding the product they wish to create. They enter the details of the product and also locate an image that represents the product, too. Currently, this application saves the product information (including binary image data) into a SQL Server 2005 Express Edition database. This application and database reside on a client pc. What I need to do is to be able save updated and newly created product data into a file of some sort. That file will make its way to a memory stick (USB) and then be transported to a 'field' machine. Quite simply, what is the best way to do this? Are there walk-throughs on this sort of thing? The target database is also SQL Server 2005. I thought I'd post this question on here to get the best design ideas... Any help would be greatly appreciated.
~javasource
I have a table tblImage with column ImageName as varchar(50) and picture as Image
I am trying to Insert picture I have in C: drive into table using the following code
Insert into tblImage (ImageName, Picture)
Select 'Dog' as ImageName, Bulkcolumn from OPENROWSET(BULK N'C:dog1.jpg', SINGLE_BLOB) as picture
I am getting error message.
Can I insert image into table through query.
Thanks in advance.
I am inserting a lengthy query through the source code here it contains one big record called description which has 3 paragraph length.....its showing error also.Is there any way to break the query in to two and execute?
View 6 Replies View RelatedIn SQL SERVER 2005, how can I get the ID of the record I just insert to table?
I defined a table MyTable, and I insert a record into the table using the SQL below
Insert into MyTable (Name) values ("User Name")
You know the field ID is IDENTITY, so it can not be in Insert SQL, and SQL SERVER will pass a value to it automatically.How can I know the ID of the record I just insert to table?
CREATE TABLE [dbo].[MyTable]( [ID] [int] IDENTITY(1,1) NOT NULL, [Name] [nchar](10) NOT NULL,CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED ( [ID] ASC)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]
The following function I am trying to design is to INSERT data into a SQL Server 2005 database. It is called from a different function, parse_file();, which is looping through a .CSV file and returning each record as an array to recordInsert();. Here is the function code and SQL syntax
PHP Code:
function recordInsert($rs)
{
$host = 'Driver={SQL Server}; SERVER=SERVER; DATABASE=DATABASE';
$user = 'USER';
$pass = 'PASS';
$conn = odbc_connect($host, $user, $pass);
$query = "INSERT INTO db.table VALUES ( {$rs['MODEL']}, {$rs['PID']}, {$rs['YEAR']}, {$rs['TOTAL_COST']},
{$rs['PARTS_COST']}, {$rs['LABOR_COST']}, {$rs['PROBCD']}, {$rs['FAIL_PART']},
{$rs['FAIL_PART_NAME']}, {$rs['MILES']}, {$rs['PROBLEM']}, {$rs['CAUSE']},
{$rs['REMEDY']}, {$rs['CLAIM_DATE']}, {$rs['APPRV_DATE']}, {$rs['FAIL_DATE']},
{$rs['SOLD_DATE']}, {$rs['UNIT_AGE']}, {$rs['DEALER']}, {$rs['DEALER_NAME']},
{$rs['ST']}, {$rs['ZIP']}, {$rs['CLAIM']}, {$rs['BTC']}, {$rs['PART_2']},
{$rs['PART_3']}, {$rs['PART_4']}, {$rs['PART_4']}, {$rs['PART_5']},
{$rs['PART_6']}, {$rs['JOBCD1']}, {$rs['JOBCD2']}, {$rs['JOBCD3']},
{$rs['JOBCD4']}, {$rs['PRLN']}, {$rs['RUN_DATE']} );";
$exec = odbc_exec($conn, $query);
if(!$exec)
{
echo "Error: " . odbc_error($exec) . "<br />
" . odbc_errormsg() . "<br />
";
}
else
{
echo "Executed.<br />
";
}
}
parse_file("file.csv", csv);
I am receiving the following error when attempting to INSERT into the database.
Code:
[Microsoft][ODBC SQL Server Driver][SQL Server]The name "YFM700RVL" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
I have tried googling the error and since it is a long error, it has been hard trying to find an answer. One solution I found (which wasn't a solution) was to add the '@' infront of the variables. It did not work.
Kick in the right direction?
Thanks.
Hi,
I need to insert and update data from one server to another. I have been searching in the internet and I have found some articles about OPENQUERY, OPENDATASOURCE and OPENROWSET.
To use OPENDATASOURCE and OPENROWSET I have to set the Ad Hoc Remote Queries €œon€?, in my pc this is fine because I have sql server 2005 and I just need to go to surface area configuration and set it to €œon€?. But for the company I works for the development server have SQL Server 2000. How do I turn this on in sql server 2000?
If there is a better way of doing this please tell me.
I wonder if it possible to move data from tables on a linked server to a "normal database"?
Name linked server: Covas
Name table on linked server: tblCountries
Name field: cntCountryName
Name "normal" database: CovasCopy
Name "normal" table: Countries (or dbo.Countries)
Name "normal" field: Country
This is just a test setup. I figure that if I get this working the rest will be easier.
My current query:
select * from openquery(COVAS,'
INSERT INTO CovasCopy.dbo.Countries(Country)
SELECT cntCountryName FROM db_covas.tblCountries;')
hi all,
In sql server 2005 i had created 2 tables,table 1 and table 2. Here is the detail of the table.
table 1:
tid--> int,identity,primary key
tname-->varchar(200)
table 2:
sid-->int,identity,primary key
tid-->fk (this tid is set as foreign key for the tid in table1)
now when i'm inserting values into tname i have to insert the value of tid from table 1 into the tid of table 2 both at the same time. any one know how this is possible? if so please send me the code..
pls help me..
thanks
swapna
Hi,
Good morning to all.My table: User_Group_Map(UserID UNIQUEIDENTIFIER,GroupID UNIQUEIDENTIFIER)
Now, I want to write one stored procedure that can insert rows into the above table, but more number of rows at-once.
Means, the program should allow multiple insertions without the need to call the stored procedure from front-end more number of times.
Can anyone please help me on this...
Thanks in advance...Ashok kumar.
Hi friends,
when trying to save a diagram I got an error:
The sp_creatediagram procedure attempted to return a status of NULL, which is not allowed.
Whats with this????
Hello everybody, I'm not completly aware of the SQL server 2005 possibilities so I'd need an hints from somebody with a wide knowledge to understand the direction to take!
This is what I have to do.
I insert into a table XML message. the messages are pushed automatically by an application I have no ""control" on and I get several messages "at the same time".
Everytime the message is inserted into the database I need to trasform the XML data into the correspondent relational value and I know already that in some cases it could take a while (1 second can be considered a while..)
My worry is that in the moment I process one message I loose the other one inserted after ,,,
There is some tool that helps me to handte the process as I would..
I was looking into SQL service broker?
It can be the right choice?
Thank you for any help!!
Marina B.
Hello,
In a web application of VS.Net 2005 I am able to insert records in both the database SQL 2000 & 2005
In a windows application of VS.Net 2005 I am able to insert record in SQl 2000 but I am not able to insert record in SQL 2005 Express Edition.
Please help Me I tried very Much but not succeded yet.
Thanks in Advance