Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server






SuperbHosting.net & Arvixe.com have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for BigResource.com.







Putting Attribute On Root Node Using FOR XML PATH


I would like to know how to put an attribute on the root node of the
xml returned from a FOR XML PATH query. One thing I tried is this:

select
m.msgid '@msgID',
st.namelong 'set/@namelong',
st.nameshort 'set/@nameshort',
from
msgset m
inner join settable st on (st.setid = m.setid)
where m.msgID = 195
for xml path('set'), root('message')

but it gives me:

<message>
<set msgID="195">
<set namelong="STUFF HERE" nameshort="STUFF" />
</set>
<set msgID="195">
<set namelong="MORE STUFF" nameshort="M STUFF" />
</set>
<set msgID="195">
<set namelong="TESTING 123" nameshort="TEST" />
</set>
</message>

here is what I want:

<message msgID="195">
<set namelong="STUFF HERE" nameshort="STUFF" />
<set namelong="MORE STUFF" nameshort="M STUFF" />
<set namelong="TESTING 123" nameshort="TEST" />
</message>

I can't get it. If I use: root(''), then it tells me: "Row tag
omission (empty row tag name) cannot be used with attribute-centric FOR
XML serialization." I'm sure there is a trick to this-- any
suggestions?

Many thanks.
the chippster


View Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
I Want To Get The Root Appliation Path For Loading A File Which Is In My Root Folder Of Windows Application


hi
my application is applocation1 i kept a file in this folder i want to read it from application how can i get the path of that file in my application.

i want to read the file in my root folder excluding the bin path

Please any one know the answer let me know ..

thanks and regards
Ravishanker Maduri

View Replies !   View Related
Root Node From A FOR XML
Hello,

How can I get a XML with a root node from a FOR XML ?:
Now I have this:

The SQL:
SELECT * FROM Table_Projects Project
FOR XML AUTO

and the XML output:
<Project>
    <ItemID>1</ItemID>
    .....
</Project>
<Project>

    <ItemID>2</ItemID>

    .....

</Project>
...

and I want this:


<Projects>
    <Project>
        <ItemID>1</ItemID>
        .....
    </Project>
    <Project>
        <ItemID>2</ItemID>
        .....
    </Project>
    ...
</Projects>

thanks and regards,
Edu

View Replies !   View Related
Retrieve Value Of The Root Attribute In XML
Hello everyone,

I have an xml file that looks like this:

?xml version="1.0" encoding="UTF-8"?>

<WHOLE Date="*********">
<MAIN .......</MAIN>
</WHOLE>

What i need is to find a way to retrieve the value of the attribute belonging to the root element (value of @Date) in SSIS.
Tried it using Xpath but don't know how i should configure the xml task editor or if maybe there's any other way to perform such a task.

thank you

View Replies !   View Related
Converting Relational Data Into XML With ROOT NODE NAME In SQL 2000.
hi All,
I tried following piece of code in SQL 2005 , is working fine.
Select * from Table
FOR XML RAW('RECORDS')ELEMENTS,ROOT('MyTable').
But when i tried the same thing in SQL 2000 , it was not working .
Plz suggest a way in 2000 to get the XML output with custom RootNode Name in 2000 also like in 2005 ?
Thanks in advance.
Mohit

View Replies !   View Related
How To Map One Xml Node Attribute To Table Column And Other As Value
Hi All,

I have to do this work in ssis.
I have to read a xml and that xml have node like this

<Attribute Name="apDlBhUtil" SF="F" Value="5"/>

<Attribute Name="apDlBhUtilThresh" SF="F" Value="2"/>

<Attribute Name="apUlBhUtil" SF="F" Value="12"/>


I have a table which contaion column name are values of name attribute. there are 50 to 60 attribute in xml.

table script is

create table Attribute
(

apDlBhUtil int,
apDlBhUtilThresh int,
apUlBhUtil int
)

now would please tell me how i can insert related value in my table.

my result would be like

apDlBhUtil apDlBhUtilThresh apUlBhUtil

5 2 12

Thanks in advance.
Manish Jain

View Replies !   View Related
PMML: One Node In A Decision Tree Containing Two States Of An Attribute As The Rule For Splitting?
Hi,
is there a way to import a decision tree-model from pmml where a node contains two or more states of an attribute as the split-rule?


Example:

...
<Node recordCount="600">
<CompoundPredicate booleanOperator="or">
<SimplePredicate field="color" operator="equal" value="red" />
<SimplePredicate field="color" operator="equal" value="green" />
</CompoundPredicate>
<ScoreDistribution value="true" recordCount="200"/>
<ScoreDistribution value="false" recordCount="400"/>
</Node>
...

This node shoud contain all cases, whose color is red or green (The Microsoft DecisionTree-Algorithm would build a model with two steps like red/ not red and then green / not green). According to the DMG, this is valid PMML 2.1, but when trying to import the server complains about an unexpected value in the SimplePredicate-tag.

How can i import such a node in SqlServer 2005?

Thank you in advance for any help

Chris

View Replies !   View Related
How Do I Write A Query To Get The Path From Ancestor To Node In A Tree?
Hi all. Here's my problem: I have a tree linking macaddresses (bigints)in a tree structure. i want to get the path from node a to b.create table tree1(father bigint , child bigint);insert into tree1 (father,child) values (100,200);insert into tree1 (father,child) values (100,300);insert into tree1 (father,child) values (100,400);insert into tree1 (father,child) values (200,2000);insert into tree1 (father,child) values (200,3000);insert into tree1 (father,child) values (100,4000);insert into tree1 (father,child) values (2000,11111);you can see that 100 --> 200 --> 2000 --> 11111select * from tree1what i would like is a query that given two parameters returns the pathbetweenthem, in the case of 100,11111 i want to get100200200011111if possible as different rows, but columns will do to.of course i do not know the legnth of the path. it can be very bigthx in advanceTzvika

View Replies !   View Related
Measure Group Attribute Key Column Does Not Match Source Attribute


HI,


I had tochange the key columns of a dimension attributeto fix an error. I did this in BIDS. The change wasfrom a single key column to a composite key column. Now I am getting these error when I process the cube:

Measure group attribute key columnx does not match source attribute ..

I looked at the cube XMLA definitionunder mesaure groups and itstill shows a single key column with inherited binding. However, the BIDS does not give me an option correct this in any way. I have had to do this once before and the only option seems to beremoving the dimension from the cube and add it back in. Butthat is very error prone since I lose any specific settings at the cube dimension level not to mention aggregations no longer include the dimension, etc.

Not seeing an alternative, I went through each measure group (I have 7)and changed the key columns manually in the XMLA and saved the cube. This worked, but I don't understand why BIDS automatically doesn't do it.

Is this a flaw inthe BIDS orI should be missing something.

thanks
MJ

View Replies !   View Related
Do I Need To Install SQL Server To Second Node In Cluster Prior To Adding The Node?
I read these instructions:

http://msdn2.microsoft.com/en-us/library/ms191545(SQL.90).aspx



But I'm not sure if I have to install SQL Server first on node 2, then add it to the cluster. Or does adding it to the cluster also install the software?



Thanks

View Replies !   View Related
Transfering Cluster Resources From One Node To Another Node


I configure Windows 2003 R2 and SQL 2005 two nodes Cluster. When I move cluster resource from one node to anther node it takes around 30 seconds to become online. So in that time if any query is running it stops responding.



So please suggest in this regard

View Replies !   View Related
Subject: BCM Install Error - Logfile &&amp; SQL Path Path &&amp; MSSQL.1?
When trying to install Business Contact Manager (BCM) for Outlook 2007, the setup failed and I was refered to a log file in my Local Settings/Temp folder. The log actually says that Business Contact Manager was installed sucessfully! BCM is supposed to install SQL Express 2005 as an instance or as instance if SQL Express is already installed. There is an MSSMLBIZ instance in Services..

Who can I send the Log File to for analysis and the fix feedback?

When I first went into Computer Management and clicked on Services and Applications in the left panel, the error message appeared "Snap-in failed to intialize. Name: SQL ServerConfiguration Manager CLSID:{CA9F8727-31DF-41D2-975C-887D84903967} This message diappeared when I clicked on Services and Applications again. Under Services, there are 3 SQL services - one is an application that was uninstalled 3-4 weeks ago and I disabled this service. The other 2 are: SQL Server (MSSMLBIZ) and the other one is SQL Server (SQLEXPRESS) When I tried to start either of the last 2, the messageappeared: Services "Could not start the SQL Server (MSSMLBIZ) service on Local Computer. Error 3: The system cannot find the path specified. Under Program Files/Microsoft SQL Server/MSSGL.1 folder is mostly empty. So, it seems like the Path in the Registry is not valid and that nothing is being installed in the MSSQL.1 folder. If so, how do I fix this?

How do I get the BCM SQL instance to install and run properly? whatdo the messages in Services mean and how do I resolve these.

Thank you!

Gary

View Replies !   View Related
SQL Server Not Starting - Tempdb Path Updated To Wrong Path
After updating TempDB path to a wrong path (without file name only folder name) the service is not starting. How can i sovle this and start the service

thanks

Leena

View Replies !   View Related
How Do You Determine The &&"active&&" Node Inside A Stored Proc For An Active/passive 2-node Cluster?
Iinvoke xp_cmdshell proc from inside a stored procedure on a 2-node active/passive SQL 2005 SP2 Standard cluster. Depending on which server the xp_cmdshell gets executed onI need to pass different arguments in the shell command. I thought I could use host_name() function to get the runtime process server, however, I am finding that it's not behaving correctly. In one example Iknow my active node is server2, but the host_name() function is returning server1. The only thingthat I could possible explainthis is thatthe MSDTC cluster group is not always on the same active node as the SQL server group and in the case I am talking about the cluster groups are in this mode (differnet nodes). Does the xp_cmdshell get executed by the SQL active node or the MDTC active node? And what is the best way to find out which server is going to run my xp_cmdshell?

Thanks.

Edit:

Perhaps another by product of this is that if I run select host_name() from the Studio Management query window i get different results depending on which server I am running the Studio Management on. On server1 I get server1 and onserver 2 I get server 2, all the while server2 is the active node.I need a different function that will always let me determine the correct server that'll be running the xp_cmdshell...

Edit 2: I guess I could determine the running host inside the command shell itself, but I am curious to see if i can do it (cleaner) from SQL.

View Replies !   View Related
Drill Through Link Produces Error: The Path Of The Item &&"(null)&&" Is Not Valid. The Path Must Be Less Than 260 Characters Long An
I have a jump-to-report link in a text box that successfully goes to another report. The second report has a similar link to the first report. When it is clicked, the following error results..





The path of the item "(null)" is not valid. The path must be less than 260 characters long and must start with slash. Other restrictions apply. (rsInvalidItemPath)



I have not been able to find a difference in the way the links were defined, they look consistent. Both reports have the same permissions, etc.



Please advise

View Replies !   View Related
FOR XML Sub Root
Hi all
I am trying to create xml file from the data, everything works fine cept I can't get the format that I need.

This is my SP:

WITH XMLNAMESPACES (
'urn:http://base.google.com/cns/1.0' as "c"
, 'urn:http://base.google.com/ns/1.0' as "g"
)

SELECT top 2
TITLE "item/title",
description "item/description",
employer "item/g:employer",
job_type "item/g:job_type",
link "item/link",
location "item/g:location",
salary "item/g:salary",
education "item/g:education"


FROM JobExport
FOR XML PATH ('chanel'), ROOT('rss')

I need to get:

<rss version="2.0" xmlns:g="http://base.google.com/ns/1.0" xmlns:c="http://base.google.com/cns/1.0">
- <channel>
- <item>
<title></title>
<description></description>
<g:employer></g:employer>
<g:job_type />
<link></link>
<g:location></g:location>
<g:salary />
<g:education></g:education>
</item>
- <item>
<title></title>
<description></description>
<g:employer></g:employer>
<g:job_type />
<link></link>
<g:location></g:location>
<g:salary />
<g:education>High School</g:education>
</item>
</channel>
</rss>

but I get this:


<rss xmlns:g="urn:http://base.google.com/ns/1.0" xmlns:c="urn:http://base.google.com/cns/1.0">
<chanel>
<item></title>
<description></description>
<g:employer></g:employer>
<g:job_type></g:job_type>
<link></link>
<g:location></g:location>
<g:salary></g:salary>
<g:education></g:education>
</item>
</chanel>
<chanel>
<item>
<title></title>
<description></description>
<g:employer></g:employer>
<g:job_type></g:job_type>
<link></link>
<g:location></g:location>
<g:salary></g:salary>
</item>
</chanel>
</rss>

The problem is that the "chanel" need to be a subroot (not sure what else to call it)

View Replies !   View Related
Different Login && SQL Console Root .....
Logged on local Administrator on one of my sqlserver 2000 "ABC" and registered 5 sql groups and linked 50 sql2k servers from MSDE to Standare (all sp3).
Now, I need a different login on this machine "ABC", how could I get back all the SQL Enterprise Manager Console Root Setting and connections like before?

thanks
David

View Replies !   View Related
XML Source - What About The Root Element?
Greetings!

Why does XML Source in MS VS 2005 Integration Services Data Flow Sources XML Source not pick up the root element?

The problem is the following example:
<root createddate="2008-01-01">
<elements>
...
</elements
</root>

The XML Source picks up everything after the first element which is the root element but it does not seam to pick up the root element where there is a need for the createddate attribute.

Is there a soultion for this problem or a workaround?

Thanks for help.

View Replies !   View Related
How To Change The Root Directory?
Hello,



I was wondering if it were possible to change the Root Directory on an SQL Server 2005 install?



expl: current = C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQL



desired: D:Program FilesMicrosoft SQL ServerMSSQL.1MSSQL

View Replies !   View Related
Master Database On Root Drive??
I have a 'need an opinion' question. I've always installed the system databases (master, msdb, model, tempdb) on the root drive (c:) with the sql application. Then I was thinking, the only things that really should be on the root drive is the sql app itself (and any other required apps). So...my question is, do y'all install the master, etc on a separate drive other than the root, ie d: or e:? The reason I ask is that if the tempdb database has alot of activity, and the database resides on the root drive, could it affect sql performance?

Just wondering. I just started working in a 'virgin' sql environment (setting up sql servers from scratch), and want to have sql installed in the best possible way for performance.

Thanks in advance for any info y'all can provide.

View Replies !   View Related
Root Directory Of SSIS Package
Hi everyone!
I have a configuration file, I want to place it in somewhere of SSIS package to extract configuration information. But i dont know where is root directory. can you help me?
Thanks you very much

View Replies !   View Related
Does Anyone Know How To Change The Document Map Root Text?
hi, guys
Does anyone know how to change the Document Map root text? For example, i have report, the file name is sc.rdl, and then the root is sc. Apparently this is not good. I am thinking is there a way to change it.

View Replies !   View Related
Root Element Is Missing Problem
Configuration: SQL Server 2005 Enterprise edition on Microsoft Cluster. Machines named Node1, Node2 and NodeV (Virtual Node). Best practices require SSRS be deployeed on an IIS6.0 server. Reporting services was installed on the Web1 machine and pointing back to the database on the NodeV platform. Everything appears to run fine in Reporting Services. However, when I use the ReportViewer control from within a webpage, the basic control appears along with the message "root element is missing". Using the same code, I point the ReportViewer control to a different server Web2 where Sql server standard, IIS and RS are installed and the report renders properly. What am I missing in the configuration of the Web1 box as far as IIS or SSRS are concerned?



View Replies !   View Related
How Can I Directly Include A Square Root In A SQL Query
I have an ASP.NET application where I need to filter the records returned from a SQL query using a calculated distance to a point that is set by the user.   To do this directly, I would need to include a square root of an expression in the SQL query.  It seems like I should be able to do this by writing my SQL query something like:
SELECT SQRT(expression ....) AS distance, column2, column3 ... FROM mytable WHERE distance < 50
Unfortunately, SQL queries apparently don't like the SQRT function.  I am using .NET 2.0 with VS2005 and a MS Access backend (that will soon be moved to SQL/Server).
As an alternate, maybe I can query the data into a temporary record set with a extra field for distance and then step my way through the temporary record set and replace the values in the distance field with a calculated distance.  Once the temporary record set is prepared, then I would delete the records that don't pass the distance requirement or otherwise hide them and bind the data to a gridview control.
Thanks,
David

View Replies !   View Related
INTERNAL ERROR Com_check Root->right Before Compile
Could someone tell me what "[INTERNAL ERROR] com_check root->right before compile" means, I get it in the SQL error log. It comes right after "Unable to recognize network login packets" (also get lot if these on another server). I think I know what the "Unable to recognize login packets" error means, but I am unable to get any information on the INTERNAL Error problem. HELP! Oh by the way, we are running MSSQL Server 6.5 on NT 4.0, it has been upgraded with service pack 5. Also, the front end application is called MP2 for the maintenance department to track tasks. All of the users are either logged in through PC to server or intranet to server.

View Replies !   View Related
How To Move Server Root Directory To Another Partition?
I want to move the SQL Server 7.0 root directory from C:MSSQL7 to D:MSSQL7 (same host). I see the startup parameters under server properties could be changed to point to the new location, but Master has the paths to all the databases recorded in sysdatabases table, so at least that would have to change, and sysdevices table points to files on C:. And maybe the NT registry points to some files there too. Anyway, is there some established method to move it? If that isn't practical, at least I would want to move TEMPDB ( perhaps by using the SP_DETACH and SP_ATTACH method ). My main database is already on D: so I do not have to move it. Any informed advice would be appreciated. Thanks.

View Replies !   View Related
Using Application.ExistsOnDtsServer, Root Folder Unknown
Hi... I'm using an object of type Microsoft.SqlServer.Dts.Runtime.Application and I want to use the ExistsOnDtsServer method

http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.application.existsondtsserver.aspx

I am loading an MSDB-related SSIS package located in the root SSIS folder.
Originially, the root MSDB folder was named "MSDB", so this call would work...

Dim app As New Microsoft.SqlServer.Dts.Runtime.Application

blnReturn = app.ExistsOnDtsServer("MSDB" & Me.PackageFolderPath & "" & Me.PackageName_SSMS, Me.Datasource)

but now, the "MSDB" folder has been renamed to "MSDB - <ServerName>", which the same code above now bombs.

I could change the code above to insert the servername, but I'd like to find something more automated to work for a root folder of any pattern.

Is anyone aware of a way to pull the root folder name through T-SQL, or some sort of wildcard character that bypasses specifics?

blnReturn = app.ExistsOnDtsServer("*" & Me.PackageFolderPath & "" & Me.PackageName_SSMS, Me.Datasource)

View Replies !   View Related
Remove ROOT Tags From SQL Task XML Results
Hi All,

I'm currently using an Execute SQL Task to return XML data from a query into an SSIS string variable. In my FOR XML clause in SQL I'm specifying a certain name for my root tag, called "Accounts". This works great in Management Studio, however, the Execute SQL Task appends a <ROOT> and </ROOT> tag to the start and end of the string, so now it looks like:

<ROOT><Accounts>...all my elements...</Accounts></ROOT>

I'd like to remove the ROOT tags so that the <Accounts> tags are actually the root for this doc. What would be the best way to remove the ROOT tags from the SSIS string variable?

Thanks!

View Replies !   View Related
Need Help In Pulling Out A Date In The Root Portion Of An XML File
Below is a file that I have loaded into a Sql table:

<btb-root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" btb-num-trans="2" btb-date="2006-11-09" btb-time="22:40:03" btb-sender="BTB" btb-receipient="BPO-USR">

- <btb-request req-method="Asynchronous">

- <req-header>


<req-btb-id>68790</req-btb-id>

<req-client-id>1133</req-client-id>

<req-product>BPO-Exterior</req-product>

<req-loan-number>00000</req-loan-number>
</req-header>

- <req-property-address>


<addr1>1115 TEST DR</addr1>

<city>TEST</city>

<state>TEstate>

<zip>30044</zip>
</req-property-address>

- <req-borrowers borr-type="Borrower">


<first-name>Test</first-name>

<last-name>TE</last-name>
</req-borrowers>
</btb-request>

My goal is to take the btb-date and store it in the same table I loaded the seperate nodes to. Currently I am loading the req-header, req-property-address, and req-borrowers.

This date will be static in that it will remain the same for every record. My goal is to read it in and store it along with each record. Hope someone can give me some help.

Thanks.

View Replies !   View Related
How To Change Default Database 'root Directory' ?
Hi folks,

Can you please tell me how should I change the default root directory (used for db storage) in SQL 2005 Std. Edition. ?

Currently its configured to:
C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQL
and I want to change the drive letter here.

Thanks in advance.
Cyk

View Replies !   View Related
Can't Create File At The Root Level Of Mount Point
Dear all,

I was trying to create filegroup and files on Mount Point, e.g. C:MOUNTFG1D1
If I try this:
.......
FILENAME = 'C:MOUNTFG1D1FG1F1.ndf',

It doesn't work, no matter what ACL I set on C:, C:MOUNT, or C:MOUNTFG1D1

But if I do this:

......
FILENAME = 'C:MOUNTFG1D1DATAFG1F1.ndf',

It will work, no need to set any ACL at all.

Any ideas?

dong

View Replies !   View Related
Navigation From The Root Folder To Specific Folders For Users
Hi there, I have the following setup in the report manager:

Root
|_ Sales
|_ Sales Export
|_ Marketing
|_ Production
|_ etc...

Now, I know that I can access each folder (f.e. Sales) by directly putting it into the URL. Is there a possibility to enable every user to the root folder and then denying access on specific folders? F.e.: Our Head of Sales should be able to navigate to the root folder and then dcecide whether he wants to enter the Sales or the Sales Export folder.

Is this possible? I tried a few things, but nothing works.

Thanks in advance!

View Replies !   View Related
Root Element MIssing &&amp; For Security Reasons DTD Is Prohibited In This XML Document
This error comes up in the print preview and nothing prints.

We are using VS-2005, SQL 2005 and SSRS 2005.

We had the same problem yesterday , so we have reinstalled IIS, .Net Framework 2.0 and Reporting Services, then reconfigured RS security, uploaded all the reports and enabled remote errors. After doing everything this error was gone and we were operating pretty fine.

Suddenly just we started getting the same annoying message."ROOT ELEMENT MISSING" from Report Server and from Report Manager we have "
For security reasons DTD is prohibited in this XML document. To enable DTD processing set the ProhibitDtd property on XmlReaderSettings to false and pass the settings into XmlReader.Create method. "




Please Help. I need to fix this ASAP.


View Replies !   View Related
Putting A One To Many Relationship On One Row
Hi,

I'm pretty new to SQL and I've got a bit of a sticky problem. I've looked around on the net for a solution but possibly I just don't know what I should be searching on.
I'm trying to join tables together where there is a one to many releationship - but I'm trying to put the results for each relationship on one row (which kind of results in dynamic columns).

table one:

Number
1111
2222
3333

table two:

Number Code
1111 15
2222 18
2222 13
3333 22
2222 26

I want the resulting table to look like:

Number Code1 Code2 Code3
1111 15
2222 18 13 26
3333 22

Is this possible? There is no limit on how many rows in table 2 can be related to table 1.
Can anyone point me in the right direction for what I should be looking at?

The reason I'm trying this is for SQL reporting services if that makes a difference?
Thanks,
Kelvin.

View Replies !   View Related
Putting SQL SELECT Sum() Into A Variable
I just want to get the sum of a table's column into a variable, in a stored procedure. The best I can do is
SET @TotalBalance = SELECT SUM(Balance) FROM AccountDetails
Not good enough, of course.
 
 

View Replies !   View Related
How To Test Before Putting In Production
Hi all,
I have a asp .net 1.1 application running on the intranet which uses SQL Server 2000.
The application is in production and everytime I want to do some changes, i do the changes on my
development machine then I copy the application dll on the server.
The problem is that I'm using Stored Procedures for all my Select, Insert and Delete statements.
These stored procedures are live on the server so I can't do the modifications locally and test them then copy to the server.

How can I do modifications without affecting the production server and the users ???
thanks.

View Replies !   View Related
Putting Date Into SQL Server
I'm trying to put a date into a SQL Server table.  The database field type is "smalldatetime".  The variable dDate is type "date" and contains: 2/2/2006 (although I think Cdate actually converts it to: #2/2/2006#).  When I run the following code the date in the database is always ends up being: 1/1/1900.
Dim cmd As SqlCommand = New SqlCommand("INSERT INTO MyTable(MyDate) " & _                                 "VALUES (" & dDate & ")", SqlConn)daAppts.InsertCommand = cmddaAppts.InsertCommand.Connection = SqlConndaAppts.InsertCommand.ExecuteNonQuery()
Any other field types work fine, it's just dates that aren't working ????  Can someone please provide a code snippet showing me what I'm doing wrong??
 

View Replies !   View Related
Putting Dates Into Varchar
I've got 2 tables :

TABLE DateSqlServer
Date as DateTime

TABLE DateDb2
Date as VarChar(26)

I run these queries :

INSERT INTO DateSqlServer (Date) VALUES (GetDate())
INSERT INTO DateDb2 SELECT Date FROM DateSqlServer

Then, when I run :

SELECT Date FROM DateDb2

I get
"march 8 2004 3:45 PM"

instead of
"2004-03-08 03:45:12:000"

How can I transfer the date as I see it in table DateSQLServer
WITHOUT doing FORMATs on the Date column ?

Why does the INSERT transform the date format ?

View Replies !   View Related
Putting These Results Toghether
The two queries:

set dateformat 'dmy'
select count(c.id), e.name
from call c left outer join employee e
on c.req_id = e.id
where c.posted between '01/01/2002' and '30/11/2002'
group by e.name
order by count(c.id) desc

set dateformat 'dmy'
select count(ch.id), e.name
from call_hist ch left outer join employee e
on ch.req_id = e.id
where ch.posted between '01/01/2002' and '30/11/2002'
group by e.name
order by count(ch.id) desc

the results:

42NULL
34Dirk Deloof
13Annick Leirman
11Ronny Loosen
9Geert Benoot
9Nicole Ferrari
8FLOCK
8Mosselmans Christoph
7Geert Pets
7Mireille Dutrieue
6johan
6Laurent De Schrijver
5Jeanette De SChrijve
5Marc De Vlieger
5minerva
5Pascal Saesen
5Rik Haghebaert
5Sonja Van Kerckhove
4Bcatron
4Luc Willems
3Brigit Brocken
3euroadmin
3Francine Kopp
3Luc Steyaert
3Marie-Rose Buysse
3Marnix Van Steirtege
3Mattias Denys
3Pieter Frooninckx
3Reserve
3Rik De Scheemaecker
3Thierry Linard
2Carlos Van Alboom
2Dorine Sierens
2Els Poelman
2Jean Claude Vermeir
2Katrien Colman
2Kim Impens
2Kris Lejeune
2MEDreserve01
2Roger De Wilde
1Agnes Lebon
1Carla Van Den Broeck
1Eric Vlaeminck

and

118NULL
58Marie-Rose Buysse
47Dirk Deloof
45Ronny Loosen
43Annick Leirman
41Geert Pets
38FLOCK
38Pascal Saesen
28Teamleiders afwerkin
24Kim Impens
22Ilse Soetens
22Rik Haghebaert
22Severine Balduck
21Teamleiders print
20Mosselmans Christoph
20Jeanette De SChrijve
19Geert Benoot
19Francine Kopp
18Geert Meuleman
17Rik De Scheemaecker
16johan
16Katrien Colman
15Gaby Eloot
14Kris Lejeune
14Gilbert Callebaut
14Laurent De Schrijver
13Els Poelman
13Luc Steyaert
11Marnix Van Steirtege
10Frans Hoogewijs
10Sonja Van Kerckhove
10Dorine Sierens
9Eric Vlaeminck
9Thierry Linard
7Frederic Denis
7Michel Poppe
6Carla Van Den Broeck
6Pieter Frooninckx
5Katlijn Poleyn
5MEDreserve01
5Mireille Dutrieue
5Agnes Lebon
4Guido Antoin
4Onderhoud
4minerva
4Jeanette Van Brussel
3Roger De Wilde
3Sofie Gabriels
3Verf2
3euroadmin
3Marc De Vlieger
2Luc Willems
2MEDRESERVE07
2Regina Decoster
2Monique Kohl
2MEDRESERVE04
2Portier
2Bcatron
2Pierre Hanet
2Tgabriels
2Isabelle Torrelle
2Nicole Ferrari
1Robert Zwaak
1Carlos Van Alboom
1testuser
1Brigit Brocken
1Reserve
1Opleiding
1Verf

How do I put these results in one? I need not two but one Query.
Please help me. Thanks

View Replies !   View Related
Putting Dynamic SQL String Together
I want to fill a DBtemp table with data but want to check first if some data exists in
another history table (before filling DBtemp table).
The DBtemp table will then be further prcocessed.

EXEC ('INSERT INTO DBTemp (TableName, trxYearMonthStart, nmbrtrx)
@ProcessTable,
SELECT CONVERT(varchar(6), ' + @SelectedColumn + ' , 112)+ ''01'',
COUNT(*)
FROM ' + @ProcessTable + ' GROUP BY
CONVERT(varchar(6), ' + @SelectedColumn + ' , 112)+''01''
')

First problem, I want to write @ProcessTable (name of the table to be processed) into table DBtemp but having
syntax problems near @ProcessTable?
Second thing, how can I check if data (column TableName=Transactions and trxYearMonthStart=01/04/2002) exists in table history and therefore not load anymore this data into table DBTemp?

Thanks for any hints

mipo

View Replies !   View Related
Putting Variables In SQL Strings
Atm this is my Test SQL String

"INSERT INTO tblPDFFiles (fileType, PDFcontent) SELECT 'Test' AS Expr1, BulkColumn FROM OPENROWSET(BULK 'F:websitesTESTarchived est.pdf', SINGLE_BLOB) AS BLOB"

When I try to put in a variable as follws

"INSERT INTO tblPDFFiles (fileType, PDFcontent) SELECT '" + @[User::MyFileValue] + "' AS Expr1, BulkColumn FROM OPENROWSET(BULK 'F:websitesTestarchived est.pdf', SINGLE_BLOB) AS BLOB"

I keep getting errors Saying it contains an illegal escape sequence of w any ideas ?

Btw the above is used as an expression



View Replies !   View Related
Putting A Cursor Into A Fuction


Hi, I am trying to incorporate a cursor into a table function so that i can use the function to insert values inot a table. Everytime i add the INSERT INTO @MyTablesyntax then the cursor seems to start an endless loop.

Does anyone have any ideas for me?





Code Block
ALTER PROC csp_ICASTransaction_AskDoc
@ClientID INT
AS
DECLARE @ClientID INT
SET @ClientID = 1
DECLARE @cClientID INT
DECLARE @cMonth VARCHAR(20)
DECLARE @cOccurance INT
DECLARE @Counter INT
DECLARE @AskDoc CURSOR
SET @AskDoc = Cursor FOR
select @ClientID, Month_Year, SUM(AskDocs)
FROM zzenrolled$
WHERE ICASClientID = @ClientID
GROUP BY Month_Year
OPEN @AskDoc
FETCH NEXT FROM @AskDoc
INTO @cClientID, @cMonth, @cOccurance
/*
SELECT @@Cursor_Rows
PRINT @cClientID
PRINT @cMonth
PRINT @cOccurance
*/
DECLARE @MyTable TABLE (ClientID INT, Date DATETIME, Occurance INT)
WHILE (@@FETCH_Status = 0)
BEGIN
SET @Counter = 0
INSERT INTO @MyTable
VALUES (@cClientID, CASE @cMonth
WHEN 'Jan-03' THEN '20030101'
WHEN 'Feb-03' THEN '20030201'
WHEN 'Mar-03' THEN '20030301'
WHEN 'Apr-03' THEN '20030401'
WHEN 'May-03' THEN '20030501'
WHEN 'Jun-03' THEN '20030601'
WHEN 'Jul-03' THEN '20030701'
WHEN 'Aug-03' THEN '20030801'
WHEN 'Sep-03' THEN '20030901'
WHEN 'Oct-03' THEN '20031001'
WHEN 'Nov-03' THEN '20031101'
WHEN 'Dec-03' THEN '20031201'
WHEN 'Jan-04' THEN '20040101'
WHEN 'Feb-04' THEN '20040201'
WHEN 'Mar-04' THEN '20040301'
WHEN 'Apr-04' THEN '20040401'
WHEN 'May-04' THEN '20040501'
WHEN 'Jun-04' THEN '20040601'
WHEN 'Jul-04' THEN '20040701'
WHEN 'Aug-04' THEN '20040801'
WHEN 'Sep-04' THEN '20040901'
WHEN 'Oct-04' THEN '20041001'
WHEN 'Nov-04' THEN '20041101'
WHEN 'Dec-04' THEN '20041201'
WHEN 'Jan-05' THEN '20050101'
WHEN 'Feb-05' THEN '20050201'
WHEN 'Mar-05' THEN '20050301'
WHEN 'Apr-05' THEN '20050401'
WHEN 'May-05' THEN '20050501'
WHEN 'Jun-05' THEN '20050601'
WHEN 'Jul-05' THEN '20050701'
WHEN 'Aug-05' THEN '20050801'
WHEN 'Sep-05' THEN '20050901'
WHEN 'Oct-05' THEN '20051001'
WHEN 'Nov-05' THEN '20051101'
WHEN 'Dec-05' THEN '20051201'
WHEN 'Jan-06' THEN '20060101'
WHEN 'Feb-06' THEN '20060201'
WHEN 'Mar-06' THEN '20060301'
WHEN 'Apr-06' THEN '20060401'
WHEN 'May-06' THEN '20060501'
WHEN 'Jun-06' THEN '20060601'
WHEN 'Jul-06' THEN '20060701'
WHEN 'Aug-06' THEN '20060801'
WHEN 'Sep-06' THEN '20060901'
WHEN 'Oct-06' THEN '20061001'
WHEN 'Nov-06' THEN '20061101'
WHEN 'Dec-06' THEN '20061201'
WHEN 'Jan-07' THEN '20070101'
WHEN 'Feb-07' THEN '20070201'
WHEN 'Mar-07' THEN '20070301'
WHEN 'Apr-07' THEN '20070401'
WHEN 'May-07' THEN '20070501'
WHEN 'Jun-07' THEN '20070601'
WHEN 'Jul-07' THEN '20070701'
WHEN 'Aug-07' THEN '20070801'
WHEN 'Sep-07' THEN '20070901'
WHEN 'Oct-07' THEN '20071001'
WHEN 'Nov-07' THEN '20071101'
WHEN 'Dec-07' THEN '20071201'
END ,'3' )

SET @Counter = @Counter + 1
IF @cOccurance > @Counter
FETCH NEXT FROM @AskDoc
INTO @cClientID, @cMonth, @cOccurance
END
CLOSE @AskDoc
--INSERT INTO [Transactional$New] (ClientID, [ Date], [ Occurance])
SELECT * FROM @MyTable
DEALLOCATE @AskDoc






I want the Cursor to insert the values into the table that i commented out, but it doesn't seem to insert the values, it just loops.

Any help will be greatly appreaciated.

Kind Regards
Carel Greaves

View Replies !   View Related
Putting Breakpoint For Stored Procedures
Hi All,
How we breakpoint to the vb.net code to analyse the code. Is it possible to put breakpoint to stored proceudres so that analysis can be done.
 
Thanks
 Abdul
 
 
 
 

View Replies !   View Related
Putting The Like Keyword In My Select Statement
I have the following sql string in my asp.net, its meant to retreive a value based on a text box value being "like" a value from my database, however when i place a word in the textbox nothing happens, can someone please  take a look at the statement and see if its well formed,
String sql = "SELECT fName FROM Customers WHERE fName LIKE " + "'" + fName.Text + "/%' OR PostCode= " + "'" + Postcode.Text + "'";

View Replies !   View Related
Putting Encypted Values In A Database
hi  guys,
Could any one tell me whats the best way to put encypted data into a table.i have a textfield that a single integer is entered into it. It is then encrypted using DES encrption algorithim.It converts the value to byte(Convert2ByteArray method) I pass the data to a stored procedure which converts it to char before inserting it into the table. when i checked the database all it entered was System.Byte[]. Does anyone know where im goin wrong?
Mairtin

View Replies !   View Related
Putting @Parameters In Stored Procedures
I am creating a stored Procedure and I am getting an error which relates to DATENAME.
SELECT COUNT(*) AS calls, DATENAME(@varDate, CALLSTARTTIME) AS 'Total Calls'
FROM CALL_LOG_MASTER
WHERE (COMMERCIALS='1') AND (CALLSTARTTIME >= @StartDate) AND (CALLENDTIME <=@EndDatesql doesn't like: DATENAME( @varDate, CallStartTime)sql works fine if I change @varDate into 'yy', 'mm', 'dd', or 'wk'Since I do not want to make 5 unique Stored Proc just because of @varDate.....Is there any way to work around this problem?

View Replies !   View Related
Putting CSS And Web Settings In The DataBase Expensive?
I had an idea to put all my web design settings, css text and web content in the database..This way it would be easy for others to edit remotely. Do you guys think this would have an impact on performance if I do this?

View Replies !   View Related
Putting SQL Server 6.5 Data Into Informix
Hi there,

Query
-------
I have got my database in SQL Server. I wish to transfer data into informix.

Can anybody suggest a efficent way to go about it.

Can I use MS-Access to link the two. I am not exactly clear how to go about it.

Would be obliged if anyone can address the issue.

Thanking you in advance.

Best regards,

Himanshu

View Replies !   View Related
Putting A Stored Proc To Sleep.
is there a way to pause a stored proc for x amount of time and then continue. I rather not go through a loop x number of times if I had to.

thanks,
DMW

View Replies !   View Related
Putting Images In An Unpopulated Table.
Here's what I've got.

We have a table that needs to be populated with a Foreign Key and an image file that matches it. The image file is named the the person's SSN which matches the FK.

Is there a way I can use the name of the file (123456789.jpg) to import it into the database or is there a better way?

View Replies !   View Related
Putting Query Results In A File
Hi,

In 6.5 you could run a query and then make a flat file with whatever format
you wanted.

I cannot find that option in 7.0. Can someone please tell me where it is????


Thanks,
Dianne Watson

View Replies !   View Related

Copyright 2005-08 www.BigResource.com, All rights reserved